Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "the use strategy and optimization of MySQL index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Fifth, the strategy of using the index.

When should I use the index?

The primary key automatically establishes a unique index

Columns that often appear as query conditions in WHERE or ORDER BY statements are indexed

As a sorted column, an index is established.

The fields associated with other tables in the query are indexed by foreign key relationships

Inclined combinatorial index under the condition of high concurrency

Columns used for aggregate functions can be indexed, for example, column_1 with max (column_1) or count (column_1) needs to be indexed

When should I not use the index?

Do not index columns that are often added, deleted and modified.

Columns with a large number of duplicates are not indexed

Do not index the table with too few records. Only when there is enough test data in the database, its performance test results have practical reference value. If there are only a few hundred data records in the test database, they are often all loaded into memory after the first query command is executed, which makes subsequent query commands execute very quickly-with or without indexes. Database performance test results are meaningful only when there are more than 1000 records in the database and the total amount of data exceeds the total memory on the MySQL server.

Case of index failure:

You cannot have a column with a value of NULL in a composite index, and if so, this column is not valid for a combined index.

Indexes can only be used once in a SELECT statement, but if they are used in WHERE, do not use them in ORDER BY.

In the LIKE operation,'% aaa%' will not use the index, that is, the index will fail, but 'aaa%' can use the index.

Using expressions or functions on the columns of an index invalidates the index, for example: select * from users where YEAR (adddate)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.