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

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

1. First locate the query conditions of the sql, which are equivalent, and those are the conditions of the range.

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

Analysis and explanation

1. The index of mysql is divided into clustered index and non-clustered index, and the table of mysql is organized by clustered index.

The clustering rule is: if there is a primary key, the primary key index is defined as the clustered index; without the primary key, the first unique index that is not allowed to be NULL is selected; and the built-in rowid of innodb is not used as the clustered index.

A nonclustered index is also called a secondary index, or a secondary index.

2. The index of mysql is a B + tree structure, whether it is clustered index or nonclustered index. The leaf node of a clustered index stores data, while the leaf node of a nonclustered index stores the key and primary key values of the nonclustered index. The height of the B+ tree is the height of the index.

3. The height of the index

The height of the clustered index determines the theoretical IO times of fetching data based on the primary key. According to the theory that the nonclustered index reads data, the IO times plus the total number of IO times accessing the clustered index. In fact, you may not be able to get so much IO. Because the Page where the branch node of the index is located will cache in mysql memory because of multiple reads.

One block size for mysql is 16K by default, 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 can be used only in the first Search Path of the index (traversing along the root node of the index B + tree to the correct leaf node position of the index).

Index Last Key, which is used to locate the termination range of the index, so for each index record read after the starting range, it is necessary to determine whether it has exceeded the range of Index Last Key. If so, the current query ends.

Index Filter, which is used to filter records in the index query range that do not meet the query conditions, so each record in the index range needs to be compared with 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, which is the defense line of the last where condition, is used to filter the records that have passed the tests of the previous index. At this time, the record already meets the range of Index First Key and Index Last Key, and meets the condition of Index Filter. The return table reads the complete record to determine whether the complete record meets the query conditions in Table Filter. Similarly, if not, skip the current record and continue to read the next record of the index. Returns a record that meets all the conditions of where and can be returned to the front-end user

Analysis.

What kind of process does it take for a sql statement to be executed?

You need to go through the following steps when a sql statement is submitted to the mysql database for query:

1. Decompose the query condition in the current query statement into each independent condition unit in the step of where parsing.

2. Mysql will split and reassemble sql automatically.

3. Then the where condition will match the index in the part of B-tree index. If the index is hit, it will be located to the specified table records location. If you don't hit, you can only scan it all.

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

As shown below:

These are the details of the mysql index hit rules, please pay attention to other related articles!

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.