Explanation of mysql index hit rules

First of all, let’s understand the leftmost matching principle, as follows:

1. First locate the query conditions of the sql, what are they, which ones are equivalent, which ones is a condition of scope.

2. Use equal-value conditions to hit the leftmost field of the index, and then hit the field from left to right in sequence, with the range at the end.

Analysis and explanation

1. The index of mysql is divided into clustered index and non-clustered index. The table of mysql is a clustered index organized table.

The clustering rules are: if there is a primary key, define the primary key index as a clustered index; if there is no primary key, select the first unique index that does not allow NULL; if not, use innodb's built-in rowid as the clustered index.

(Free learning video tutorial recommendation: mysql video tutorial)

Non-clustered index is also called secondary index, or auxiliary index.

2. MySQL’s index, whether it is a clustered index or a non-clustered index, is a B-tree structure. The leaf nodes of the clustered index store data, and the leaf nodes of the non-clustered index store the key and primary key values ​​of the non-clustered index. The height of the B-tree is the height of the index.

3. Index height

The height of the clustered index determines the theoretical number of IOs to retrieve data based on the primary key. The theoretical IO times for reading data based on the non-clustered index should be added to the total number of IO times for accessing the clustered index. In fact, it may not require so much IO. Because the Page where the branch node of the index is located will be cached in the mysql memory due to multiple reads.

The default block size of mysql is 16K. The height of the index can be roughly estimated based on the length of the index column.

sql optimization is based on the where condition in the

SQL statement. Using the above extraction rules, it will eventually be extracted to Index Key (First Key & Last Key), Index Among Filter and Table Filter.

Index First Key is only used to locate the starting range of the index, so it is only used when indexing the first Search Path (traversing all the way along the root node of the index B tree to the correct leaf node position of the index) Use it and judge it once;

Index Last Key is used to locate the ending range of the index. Therefore, for every index record read after the starting range, you need to judge whether it has exceeded the Index Last Key. Range, if exceeded, the current query ends;

Index Filter is used to filter records in the index query range that do not meet the query conditions. Therefore, each record in the index range needs to be compared with the Index Filter. , if it does not meet the Index Filter, it will be discarded directly and continue to read the next record in the index;

Table Filter, this is the last line of defense for the where condition, used to filter records that have passed the previous index test. This The record at that time has satisfied the range formed by Index First Key and Index Last Key, and satisfied the conditions of Index Filter. The complete record is read back to the table and it is judged whether the complete record satisfies the query condition in Table Filter. Similarly, if not If satisfied, skip the current record and continue reading the next record in the index. If satisfied, return the record. This record meets all the conditions of where and can be returned to the front-end user

Analysis

What kind of process does a SQL statement need to go through to be executed?

When a sql statement is submitted to the mysql database for query, it needs to go through the following steps:

1. First, in the where parsing step, the query conditions in the current query statement are decomposed into each An independent condition unit;

2. Mysql will automatically split and reorganize the sql;

3. Then the where condition will perform index matching in the B-tree index part. If the index is hit, The specified table records location will be located. If there is no hit, all scans can only be used;

4. Return the corresponding data value based on the current query field.

As shown below:

Recommended related articles and tutorials: mysql tutorial

The above is the detailed content of Explanation of mysql index hit rules. For more information, please follow other related articles on the PHP Chinese website!