Reference: https://www.cnblogs.com/myseries/p/11265849.html
InnoDB has two types of indexes: clustered index (clustered index) index) and ordinary index (secondary index)
The leaf node of nnoDBclustered index stores row records. Therefore, InnoDB must have one and only one Clustered index:
(1) If the table defines a PK, the PK is a clustered index;
(2) If the table does not define a PK, the first not NULL unique column is Clustered index;
(3) Otherwise, InnoDB will create a hidden row-id as a clustered index;
InnoDBnormal index
The leaf nodes of strong> store the primary key value.
The two B+ tree indexes are as shown in the figure above:
(1) id is PK, clustered index, leaf node storage Line record;
(2) name is KEY, common index, leaf node stores PK value, that is id;
Since the normal index cannot be directly located Row records, what about the query process of the common index?
Usually, you need to scan the index tree twice.
This is the so-called back-to-table query, which first locates the primary key value, and then locates the row record, its performance is lower than scanning the index tree .
Summary: Back to the table query is to locate the primary key value first, then locate the row record, and query the index twice Tree.
|
How to achieve index coverage?
The common method is: The field to be queried is created in the joint index.
If you add sex in the second query below to the joint index, just No need to return to the table.
select id,name from user where name= ‘shenjian’;
Able to hit the name index, the index leaf node stores the primary key id, and the id and name can be obtained through the index tree of the name, without returning to the table, conforming to the index Coverage, higher efficiency.
explain sql displays Extra:Using index. Explain that there is no return to the table
select id,name,sex from user where name=’shenjian’;
Able to hit the name index, the index leaf node stores the primary key id,but the sex field must be returned to the table to query to get it, does not meet the index coverage, you need to scan the clustered index through the id value to obtain the sex field again, the efficiency will be reduced.
explain sql displayExtra:Using index condition em>. Explain that you need to return to the form
For a detailed description, please refer to the link quoted in the first line of this article
select id, name from user where name = 'shenjian' ; |
< div id="highlighter_61378" class="syntaxhighlighter sql">
|
How to achieve index coverage ?
The common method is: The field to be queried is created in the joint index.
If you add sex in the second query below to the joint index, just No need to return to the table.
select id,name from user where name= ‘shenjian’;