Detailed explanation of MySQL InnoDB secondary index sorting example

Sorting Problem

I recently read "45 Lectures on MySQL Practice" on Geek Time, which corrected my inadequate understanding of InnoDB secondary indexes. It is a good opportunity to summarize the relevant content.

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

Let me first ask the question. The table created by the following SQL has two query statements. Which index is not required?

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 c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

The answer given by the author is that the data models of indexes c and ca are the same, so ca is redundant. Why? ?

We know that the secondary index does not store the position of the row, but the value of the primary key, and we also know that the index is ordered.

If c has the same data model as ca, then the leaf nodes of the secondary index are required to be sorted not only by the index columns, but also by the associated primary key values.

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

I asked the columnist and got the answer: Index c is sorted like cab, (secondary index)) to ensure that the primary key is included and is in order. (PS: This is not the original words, I got the answer after asking three times).

Based on the idea of ​​first asking if, then asking why, we conducted some exploration.

Yes or no?

If you can directly view the InnoDB data file, you can directly see whether this sorting rule is followed. Unfortunately, it is a binary file and there is no convenient tool to view it, so I gave up.

Later, I found the MySQL handler statement, which supports tables of both MyISAM and InnoDB engines. The handler statement provides an interface for directly accessing the table storage engine.

The following syntax indicates reading the first/previous/next/last record of the specified index in the specified table.

handler table_name/table_name_alias read index_name first/pre/next/last;

Let's use the handler statement to verify it. First, create a simple table and insert some data:

create table t_simple (
 id int primary key,
 v int,
 key k_v (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 statements, the values ​​of the secondary index columns are the same, and the primary keys are not in order. This way, you can see whether they are stored in the order of the primary keys during traversal.

mysql> handler t_simple open as ts;
Query OK, 0 rows affected (0.00 sec)

mysql> handler ts read k_v next;
+----+------+
| id | v |
+----+------+
| 1 | 5 |
+----+------+
1 row in set (0.00 sec)

mysql> handler ts read k_v next;
+----+------+
| id | v |
+----+------+
| 4 | 5 |
+----+------+
1 row in set (0.00 sec)

mysql> handler ts read k_v next;
+----+------+
| id | v |
+----+------+
| 10 | 5 |
+----+------+
1 row in set (0.00 sec)

From the results, we can see that when the values ​​of the traversed secondary indexes are equal, we traverse them in the order of the primary key. It can be basically determined that the secondary indexes are sorted not only by the index columns, but also by the primary key values.

Why?

I have never seen any mention of such a mechanism in MySQL before, and the DBAs at my previous company and previous company were not aware of this.

Finally, the DBA colleague found Index Extensions, which contained 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 k_d (d)
)ENGINE = InnoDB;

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

The optimizer will decide how and whether to use that index based on the primary key columns of the extended secondary index. The optimizer can use extended secondary indexes for index access types such as ref, range, and index_merge, loose index scans, join and sort optimizations, and min()/max() optimizations.

You can use show variables like '%optimizer_switch%'; to check whether index extensions are enabled; use SET optimizer_switch = 'use_index_extensions=on/off'; to enable or disable it. This only affects the current session.

After testing, even if the index extension of the current session is turned off, the effect of sorting by primary key can still be achieved when accessing with the handler.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.