MySQL uses covering index to avoid table return and optimize query

Preface

Before talking about covering index, we must first understand its data structure: B+ tree.

First create a table for demonstration (for simplicity, id is created in order):

The B+ tree index constructed with column values ​​other than the primary key as key values ​​is called a non-clustered index.

The difference between a non-clustered index and a clustered index is that the leaf nodes of a non-clustered index do not store the data in the table, but store the primary key corresponding to the column. If we want to find data, we also need to search in the clustered index based on the primary key. This process of searching for data based on the clustered index is called table return.

B+ Tree

B+ tree and B tree are commonly used data structures for MySQL indexes. B+ tree is a further optimization of B tree. Let's convert the above table into a graph for analysis:

Characteristics of B+ tree:

1. Non-leaf nodes of the B+ tree do not store data, only key values

2. The data of leaf nodes are arranged in order

3. The pages in the B+ tree are connected by a bidirectional linked list

Clustered and non-clustered indexes

B+ tree indexes are divided into clustered indexes and non-clustered indexes according to different storage methods.

Clustered index:

For tables that use InnoDB as the storage engine, all data in the table will have a primary key. Even if you do not create a primary key, the system will create an implicit primary key for you.

This is because InnoDB stores data in a B+ tree, and the key value of the B+ tree is the primary key. All the data in the table is stored in the leaf nodes of the B+ tree.

This kind of B+ tree index that uses the primary key as the key value of the B+ tree index is called a clustered index.

Nonclustered index:

The B+ tree index constructed with column values ​​other than the primary key as key values ​​is called a non-clustered index.

The difference between a non-clustered index and a clustered index is that the leaf nodes of a non-clustered index do not store the data in the table, but store the primary key corresponding to the column. If we want to find data, we also need to search in the clustered index based on the primary key. This process of searching for data based on the clustered index is called table return.

How to use covering index to avoid table back

Why does the table return even though a non-primary key index is used? Simply put, the non-primary key index is a non-clustered index. Only the primary key and the non-primary key index are saved in the tree child nodes of the B+ tree. Only these two fields can be found in one query. If you want to find three fields, you must search the clustered index again. This is a table return.

For example, a new field age is added to the table, and we create an index (non-clustered index) using name.

select id,name from user where name = 'zs';

The name index can be hit. The index leaf node stores the primary key id. The id and name can be obtained through the name index tree without returning to the table. This meets the index coverage requirement and is highly efficient.

select id,name,age from user where name = 'zs';

The name index can be hit. The index leaf node stores the primary key id, but the age field can only be obtained by returning to the table for query, which does not meet the index coverage. It is necessary to scan the code through the id value again to obtain the age field, which will reduce efficiency.

Conclusion: So how can we avoid table return? It's simple, upgrade the single column index (name) to a joint index (name, age).

Summarize

This is the end of this article about MySQL using covering index to avoid table return to optimize query. For more relevant MySQL covering index to avoid table return to optimize query content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!