MySQL index and mysql Index

MySQL index and mysql Index

Mysql indexes use B + tree. B + tree is a variant of the balanced binary tree, so the query speed is very fast. (B + tree: https://zh.wikipedia.org/zh-hans/ B %2B%E6%A0%91)

Indexes mainly include clustered indexes and secondary indexes:

Clustered Index: Data in mysql is stored through the clustered index of the primary key, and each row of data is stored in the leaf node. Therefore, we query the speed through the primary key.

For example, the primary key is a clustered index, but in actual use, only such a B + tree will be built, so this can explain why the primary key is unique.

Reference the online graph:

The search for each layer is an I/O operation. Generally, the layers of B + tree are 2-4 layers. Therefore, in the worst case, only four I/O operations are required.

Secondary Index: The secondary index and clustered index are different in that not all data is stored in the leaf node and the data is stored. This is equivalent

After secondary indexes find data, you also need to find detailed information through the clustered index tree.

Reference the online graph:

This graph is a logical graph, but the bottom layer points to the clustered index where the leaf node is located. That is to say, you need to go through the first graph

Logic.

Therefore, the final result is that multiple secondary index trees direct to one clustered index tree.

 

(The image is really ugly)

When to create an index

Because this is a tree that uses the Binary Search Method for retrieval, it is applicable when it is used as a condition after the where clause and the value is within a large range and is suitable for index creation. It is not suitable for enumerations with small ranges (is_delete, sex, and so on.

For specific situations, we can use show index for analysis:

show index from company_related_person

Result:

Then it is calculated using cardinality.

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

The result here is 0.913 (this value is related to the storage space, it is better to have a certain amount of data). The closer this value is to 1 index, the higher the efficiency. If the obtained value is very small, we recommend that you do not create an index.

At the same time, we can use explain to view the index usage.

EXPLAIN select * from company_related_person where company_id='2'

Output

Key indicates the currently used index column. The final extra indicates the method used. Here, Using index indicates that the index is used. If Using filesort indicates that the disk is read directly.

Complex SQL statements with slow query can be analyzed in this way.

 

Differences between index and key:

When we create an index, we often have this question: what is the difference between index and key ?. A Key is a part of the relational model theory, such as a Primary Key or a Foreign Key, used for data integrity check and uniqueness constraints. The Index is at the implementation level. For example, you can create an Index on any column of the table. When the column to be indexed is in the Where condition of the SQL statement, you can quickly locate the data, to quickly search. As for Unique Index, it only belongs to one type of Index. If Unique Index is set up, the data in this column cannot be duplicated.