Detailed explanation of MySQL indexes

The index of mysql is through B+tree. B+tree is a variant of balanced binary tree, so the query speed is very fast.

Indexes are mainly divided into clustered indexes and auxiliary indexes:

clustered index: The data in mysql is stored through the clustered index of the primary key, and the leaf nodes store Each row of data, so we use the primary key to query the speed

The reason why it is as fast as before is that the primary key is a clustered index, but in actual use only one such B+tree will be built, so this can explain why the primary key The only one.

Quote from the picture on the Internet:

The search at each layer is an IO operation, and generally the number of B+tree layers is 2-4. So in the worst case, only 4 IO operations are required.

Auxiliary index: The difference between the auxiliary index and the clustered index is that not all the data is stored in the leaf nodes, but the location of the data is stored. It is equivalent to using the

auxiliary index to find the data, and then we need to find detailed information through the clustered index tree.

Quote from the diagram on the Internet:

This diagram is a logical diagram, but the bottom layer points to the clustered index through the leaf nodes, that is Say, you still need to go through the

logic of the first type of diagram.

So the final result is that multiple auxiliary index trees point to a clustered index tree

(The painting is really ugly)

About when to create an index

Because this is a tree, it is retrieved through binary search, so it is applicable when used as the condition behind where, and this The values ​​are in a wide range, suitable for index creation. It is not suitable for those with a small range (is_delete, sex, etc. enumerations).

For specific situations, we can analyze it through show index:

show index from company_related_person

Result:

Then calculate by cardinality

select 105/(select count(*) from company_related_person) from DUAL

The result obtained here is 0.913 (this value is related to the storage capacity, it is best to have a certain amount of data). The closer this value is to 1, the higher the efficiency of the index. If the value obtained is very small, it is recommended not to create it. Index

We can also check the usage of the index through explain

EXPLAIN select * from company_related_person where company_id='2'

Output

key represents the currently used index column . The last extra indicates which method is used. Here, Using index indicates that the index is used. If Using filesort indicates that the disk is read directly.

For those complex SQL statements with slow queries, you can use this ways to analyze.

The goal of SQL performance optimization: at least reach the range level, the requirement is ref level, if it can be consts, it is best.

1) Consts There is at most one matching row (primary key or unique index) in a single table, and the data can be read during the optimization phase.

2) ref refers to using a normal index.

3) range performs range retrieval on the index

4) index means reading directly from the disk

You can also see from the above figure that we use The ref

About the difference between index and key:

When we create an index, we often have this question, what is the difference between index and key? . Key is a key value, which is part of the relational model theory, such as primary key (Primary Key), foreign key (Foreign Key), etc., which are used for data integrity checking and uniqueness constraints. Index is at the implementation level. For example, you can index any column of a table. Then when the indexed column is in the Where condition in the SQL statement, you can get fast data location and thus fast retrieval. As for Unique Index, it is just one type of Index. The establishment of Unique Index means that the data in this column cannot be repeated

The above is the detailed content of Detailed explanation of MySQL indexes. For more information, please follow other related articles on the PHP Chinese website!