MySQL InnoDB 二級索引的排序示例詳解

排序問題

最近看了極客時間上 《MySQL實戰45講》，糾正了一直以來對 InnoDB 二級索引的一個理解不到位，正好把相關內容總結下。

PS：本文的所有測試基于 MySQL 8.0.13 。

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

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 的數據模型是一樣的，因此 ca 是多余的。為啥？？

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

如果 c 與 ca 的數據模型一樣，那么就要求二級索引的葉子節點不僅是按索引列排序、而且還按關聯的主鍵值進行排序。

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

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

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

是不是？

如果能直接看 InnoDB 的數據文件，那就可以直接看出是不是遵循了這樣的排序規則。可惜那是二進制文件，又沒有順手的工具可以方便查看，放棄。

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

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

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

就用 handler 語句來驗證下，先建一個簡單的表，插入幾條數據：

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

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

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)

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

為什么？

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

最后 DBA 同事找到了 索引擴展, 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 自動擴展每個二級索引，把主鍵值追加到索引列后面，把擴展后的組合列作為該索引的索引列。對于上面 t_simple 表的 k_v 索引，擴展后是 (v, id)列。

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

可以用 show variables like '%optimizer_switch%'; 查看索引擴展是否開啟；用 SET optimizer_switch = 'use_index_extensions=on/off'; 進行開啟或關閉，這個只影響當前會話。

經測試，哪怕關閉了當前會話的索引擴展，用 handler 訪問時仍然有按主鍵排序的效果。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對億速云的支持。