MySQL InnoDB 二級(jí)索引的排序示例詳解

排序問題

最近看了極客時(shí)間上 《MySQL實(shí)戰(zhàn)45講》，糾正了一直以來對(duì) InnoDB 二級(jí)索引的一個(gè)理解不到位，正好把相關(guān)內(nèi)容總結(jié)下。

PS：本文的所有測(cè)試基于 MySQL 8.0.13 。

先把問題拋出來，下面的 SQL 所創(chuàng)建的表，有兩個(gè)查詢語句，哪個(gè)索引是非必須的？

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;

作者給的答案是索引 c 和 ca 的數(shù)據(jù)模型是一樣的，因此 ca 是多余的。為啥？？

我們知道，二級(jí)索引里存放的不是行的位置，而是主鍵的值，也知道索引是有序的。

如果 c 與 ca 的數(shù)據(jù)模型一樣，那么就要求二級(jí)索引的葉子節(jié)點(diǎn)不僅是按索引列排序、而且還按關(guān)聯(lián)的主鍵值進(jìn)行排序。

我以前的理解是 二級(jí)索引只按索引列進(jìn)行排序，主鍵值是不排序的。

問了專欄作者，得到的答復(fù)是：索引 c 就是按照 cab 這樣排序，（二級(jí)索引)）有保證主鍵算進(jìn)去、還是有序的。（PS：非原話，前后問了三次得到）。

本著 先問是不是，再問為什么 的思路，進(jìn)行一番探究。

是不是？

如果能直接看 InnoDB 的數(shù)據(jù)文件，那就可以直接看出是不是遵循了這樣的排序規(guī)則?？上鞘嵌M(jìn)制文件，又沒有順手的工具可以方便查看，放棄。

后來找到了 MySQL 的 handler 語句，它支持 MyISAM/InnoDB 兩種引擎的表。handler 語句提供了直接訪問表存儲(chǔ)引擎的接口。

下面的語法表示讀取指定表指定索引的 第一條/前一條/下一條/最后一條 記錄。

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

就用 handler 語句來驗(yàn)證下，先建一個(gè)簡(jiǎn)單的表，插入幾條數(shù)據(jù)：

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);

上面的插入語句，二級(jí)索引列的值都是一樣的，主鍵不是按順序的，這樣就可以看遍歷時(shí)是不是按主鍵順序存放的。

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)

從結(jié)果可以看到，遍歷的二級(jí)索引，值相等時(shí)，按主鍵的順序遍歷，基本可以確定二級(jí)索引不僅按索引列排序，還按主鍵值排序了。

為什么？

之前一直沒看到說 MySQL 有這樣的機(jī)制，問了前公司和先公司的 DBA 都沒了解過這個(gè)。

最后 DBA 同事找到了 索引擴(kuò)展, Index Extensions ，里面有這么段描述做了說明：

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 自動(dòng)擴(kuò)展每個(gè)二級(jí)索引，把主鍵值追加到索引列后面，把擴(kuò)展后的組合列作為該索引的索引列。對(duì)于上面 t_simple 表的 k_v 索引，擴(kuò)展后是 (v, id)列。

優(yōu)化器會(huì)根據(jù)擴(kuò)展后的二級(jí)索引的主鍵列來決定如何和是否使用那個(gè)索引。優(yōu)化器可以用擴(kuò)展的二級(jí)索引來進(jìn)行 ref,range,index_merge 等類型的索引訪問、松散的索引掃描、連接和排序優(yōu)化，以及 min()/max() 優(yōu)化。

可以用 show variables like '%optimizer_switch%'; 查看索引擴(kuò)展是否開啟；用 SET optimizer_switch = 'use_index_extensions=on/off'; 進(jìn)行開啟或關(guān)閉，這個(gè)只影響當(dāng)前會(huì)話。

經(jīng)測(cè)試，哪怕關(guān)閉了當(dāng)前會(huì)話的索引擴(kuò)展，用 handler 訪問時(shí)仍然有按主鍵排序的效果。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。