I believe you have read a lot of MYSQL index optimization articles, there are a lot of optimization methods, such as the best left prefix, overwrite index methods, but you really understand why to use the best left prefix, why overwrite index will improve the efficiency of the query?

This article will take a look at the internal structure of MYSQL to see why overwriting indexes improves efficiency.

InnoDB index model

In InnoDB, tables are stored as indexes according to the order of primary keys, which are called indexed organized tables. As mentioned earlier, InnoDB uses a B+ tree index model, so data is stored in a B+ tree. Each index in InnoDB corresponds to a B+ tree.

The difference between a primary key index and a non-primary key index

A primary key index is also called a clustered index, and a non-primary key index is also called a normal index. What is the difference between the two? Leaf nodes of primary key indexes hold entire rows of data, while leaf nodes of non-primary key indexes hold primary key values. Suppose you have a User table (ID,age,name,address) with two columns id and age, where ID is the primary key and age is the normal index. Rows with (id,age) values of u1-u5 are (100,1), (200,2), (300,3), (500,5), and (600,6), as shown in the following example:

As you can see from the figure above, the leaf node of the tree based on the primary key index stores the entire row of User data, while the leaf node based on the normal index age stores the value of ID (the primary key).

What is a return table?

Suppose you have a query statement like this:

select * from user where age=3;

Copy the code

The SQL statement is executed as follows:

Age = 300; age = 300; age = 300; The age index tree does not store all User data. Therefore, the age index tree needs to search for the primary key ID 300, and query u3. 3. Return the result. In the above execution, the query from the AGE index tree to the ID index tree is called a back table (back to the primary key index tree search). That is, queries through non-primary key indexes need to scan one more index tree, so use primary key indexes whenever possible.

Why use an overwrite index?

With the concepts mentioned above, it is clear why overwriting an index can improve query efficiency, because there is one less return to the table. Suppose we use an overwrite index query with the following statement:

select id from user where age=3;
Copy the code

Select * from age; select * from age; select * from age;

conclusion

The use of overwrite index can reduce the number of tree searches, avoid back to the table, and significantly improve query performance. Therefore, overwrite index is a common performance optimization method.

The above content hopes to help you, more free PHP factory PDF, PHP advanced architecture video materials, PHP wonderful good article can be wechat search concerns: PHP open source community

2021 Jinsanyin four big factory interview real questions collection, must see!

Four years of PHP technical articles collation collection – PHP framework

A collection of four years’ worth of PHP technical articles – Microservices Architecture

Distributed Architecture is a four-year collection of PHP technical articles

Four years of PHP technical essays – High Concurrency scenarios

Four years of elite PHP technical article collation collection – database