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

Scheduling problem

Recently watched the geek time on the "MySQL actual combat 45", corrected for a long time on the InnoDB secondary index of an understanding is not in place, just to summarize the relevant content.

PS: all tests in this article are based on MySQL 8.0.13.

To throw the question first, the table created by SQL below has two query statements. Which index is optional?

CREATE TABLE `geek` (`a` int (11) NOT NULL, `b` int (11) NOT NULL, `c` int (11) NOT NULL, `d` int (11) NOT NULL, PRIMARY KEY (`a`, `b`), KEY `c` (`c`), KEY `ca` (`c`, `a`), KEY `cb` (`c`, `b`) ENGINE=InnoDB;select * from geek where cantin order by a limit 1

The author's answer is that the data model of index c is the same as that of ca, so ca is redundant. Why??

We know that what is stored in the secondary index is not the position of the row, but the value of the primary key, and that the index is ordered.

If c is the same as ca's data model, then the leaf node of the secondary index is required to sort not only by the index column, but also by the associated primary key value.

My previous understanding was that secondary indexes are sorted only by index columns, and primary key values are not sorted.

Asked the columnist, the answer is: index c is sorted according to cab, (secondary index)) there is a guarantee that the primary key is counted and ordered. (PS: not the original words, asked three times before and after getting).

In line with the idea of asking first whether or not, and then asking why, carry on some exploration.

Isn't that right?

If you can look directly at the InnoDB data file, you can directly see whether such a collation is followed. Unfortunately, it is a binary file, and there is no convenient tool to view and give up.

Later, we found the handler statement of MySQL, which supports tables for both MyISAM/InnoDB engines. The handler statement provides direct access to the table storage engine.

The following syntax indicates that the first / previous / next / last record of the specified index of the specified table is read.

Handler table_name/table_name_alias read index_name first/pre/next/last

To verify with the handler statement, first build a simple table and insert a few pieces of data:

Create table t_simple (id int primary key, v int, key Kenzv (v)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;insert into t_simple values (1,5); insert into t_simple values (10,5); insert into t_simple values (4,5)

In the above insert statement, the values of the secondary index columns are all the same, and the primary key is not in order, so you can see whether the traversal is stored in the primary key order.

Mysql > handler t_simple open as ts;Query OK, 0 rows affected (0.00 sec) mysql > handler ts read KFV next;+----+-+ | id | v | +-+-+ | 1 | 5 | +-+-+ 1 row in set (0.00 sec) mysql > handler ts read KFV next +-+-- +-+ | id | v | +-+-+ | 4 | 5 | +-+-+ 1 row in set (0.00 sec) mysql > handler ts read Kenzv next;+----+-+ | id | v | +-+ | 10 | 5 | +-+-+ 1 row in set (0.00 sec)

As can be seen from the results, when the traversal of the secondary index is equal, it can be determined that the secondary index is sorted not only by the index column, but also by the primary key value.

Why?

It has not been said that MySQL has such a mechanism before. I asked the DBA of the former company and the first company not to know about this.

Finally, DBA colleagues found the index extension, Index Extensions, which has the following description:

InnoDB automatically extends each secondary index by appending the primary key columns to it. Consider this table definition:

CREATE TABLE T1 (i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT NULL, PRIMARY KEY (i1, i2), INDEX krypd (d)) ENGINE = InnoDB

InnoDB automatically extends each secondary index, appends the primary key value to the index column, and uses the expanded combined column as the index column of the index. For the above id v index of the t_simple table, it is extended to the (v, index) column.

The optimizer decides how and whether to use that index based on the primary key column of the extended secondary index. The optimizer can use extended secondary indexes for ref,range,index_merge and other types of index access, loose index scanning, join and sort optimization, and min () / max () optimization.

You can use show variables like'% optimizer_switch%'; to see if the index extension is on; turn it on or off with SET optimizer_switch = 'use_index_extensions=on/off';, which only affects the current session.

It has been tested that even if the index extension for the current session is turned off, it still has the effect of sorting by primary key when accessed with handler.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.