Detailed explanation of table return and index coverage examples in MySQL

Index Type

Clustered index: Leaf nodes store row records, and each table must have at least one clustered index. Using a clustered index query will be very fast because you can directly locate the row record. Ordinary index: secondary index, index other than the clustered index, that is, non-clustered index. The leaf nodes of ordinary indexes store the values ​​of the primary key (clustered index).

Clustered index recursion rules:

• If the table has a primary key, the primary key is the clustered index.
• If the table does not have a primary key, the first NOT NULL and unique (UNIQUE) column is used as the clustered index by default.
• If none of the above is specified, a hidden row_id will be created as a clustered index by default.

Index structure

id is the primary key, so it is a clustered index, and its leaf nodes store the data of the corresponding row records.

Non-Clustered Index

Clustered index query

If the query condition is the primary key (clustered index), you only need to scan the B+ tree once to locate the row record data to be found through the clustered index.

For example: select * from user where id = 1;

Nonclustered index query

If the query condition is a common index (non-clustered index), the B+ tree needs to be scanned twice. The first scan locates the value of the clustered index through the common index, and then the second scan locates the row record data to be found through the value of the clustered index.

For example: select * from user where age = 30;

1. First locate the primary key value id=1 through the common index age=30
2. Then locate the row record data through the clustered index id=1

First, locate the clustered index value through the value of the ordinary index, and then locate the row record data through the value of the clustered index. It is necessary to scan the index B+ tree twice, and its performance is lower than scanning the index tree once.

Index Covering

All column data required by SQL can be obtained on one index tree without returning to the table, which is faster.

For example: select id,age from user where age = 10;

Query using id, age, name:

select id,age,name,salary from user where age = 10;
Explain analysis: age is a common index, but the name column is not in the index tree. Therefore, after querying the values ​​of id and age through the age index, it is necessary to go back to the table and query the value of name. The Using where in the Extra column indicates that a table query is performed.

Type: all, indicating full table scan

Add a joint index to the table: CREATE INDEX idx_user_name_age_salary ON mydb.user (name, age, salary);

Explain analysis: At this time, the fields age and name are the composite index idx_age_name. The values ​​of the queried fields id, age, and name are just in the index tree. It is only necessary to scan the composite index B+ tree once. This is how index coverage is achieved. At this time, the Extra field is Using index, which means that index coverage is used.

Pagination query (not using index):

After adding an index, you can use it to quickly search.

Summarize

This is the end of this article about table return and index coverage in MySQL. For more relevant MySQL table return and index coverage content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!