Shulou(Shulou.com)06/03 Report--

This article mainly explains "the explanation of mysql index hit rules". the explanation in this article is simple and clear, easy to learn and understand. please follow the ideas of Xiaobian and go deep into it slowly to study and learn "the explanation of mysql index hit rules" together.

First, let's look at the leftmost matching principle, as follows:

1, first locate the sql query conditions, which are equivalent, which are the range of conditions.

2. The equivalent condition hits the leftmost field of the index, and then hits from left to right, and the range is placed at the end.

explain the

1, MySQL index is divided into clustered index and non-clustered index, MySQL table is clustered index organization table.

Aggregation 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 is not allowed to be NULL; if there is no innodb, use the built-in rowid as a clustered index.

(Free learning video tutorial recommended: mysql video tutorial)

A nonclustered index is also known as a secondary index, or secondary index.

MySQL indexes, whether clustered or non-clustered, are B+ tree structures. The leaf nodes of a clustered index store data, while the leaf nodes of a nonclustered index store keys and primary keys of a nonclustered index. The height of the B+ tree is the height of the index.

3. Height of index

The height of the clustered index determines the theoretical number of IO fetches based on the primary key. The theoretical number of IO reads from a nonclustered index is added to the sum of IO accesses to a clustered index. In fact, you probably don't need that much IO. Because the Page where the branch node of the index is located will be cached in mysql memory due to multiple reads.

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

SQL optimization basis

The where condition in the SQL statement, using the above extraction rules, will eventually be extracted into Index Key (First Key & Last Key), Index Filter and Table Filter.

Index First Key is only used to locate the starting range of the index, so it is only used when the index is searched for the first time (traversing along the root node of the index B+ tree to the correct leaf node position of the index).

Index Last Key, used to locate the end range of the index. Therefore, for each index record read after the start range, it is necessary to determine whether it has exceeded the range of Index Last Key. If it exceeds the range, the current query ends.

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

Table Filter, this is the last defense line of where condition, used to filter the records that pass the previous layers of index tests. At this time, the records have met the range composed of Index First Key and Index Last Key, and meet the conditions of Index Filter. After reading the complete records back to the table, judge whether the complete records meet the query conditions in Table Filter. Similarly, if they do not meet the requirements, skip the current record and continue to read the next record in the index. If they meet the requirements, return the records. This record satisfies all the conditions of where and can be returned to the front-end user.

analysis

What does it take to execute a sql statement?

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

1. First decompose the query condition in the current query statement into each independent condition unit in the where parsing step;

mysql will automatically split sql reassembly;

3. Then the where condition will perform index matching in the B-tree index section. If the index hits, it will locate the specified table records position. If it doesn't hit, it can only use the full scan method;

4. Return the corresponding data value according to the current query field.

Thank you for reading, the above is the "mysql index hit rules description" of the content, after the study of this article, I believe we have a deeper understanding of the mysql index hit rules description of this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.