排序問題

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

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

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

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 是多余的。為啥？？

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

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

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

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

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

是不是？

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

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

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

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

就用 handler 語句來驗證下，先建一個簡單的表，插入幾條數(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);

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

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é)果可以看到，遍歷的二級索引，值相等時，按主鍵的順序遍歷，基本可以確定二級索引不僅按索引列排序，還按主鍵值排序了。

為什么？

之前一直沒看到說 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)列。

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

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

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

總結(jié)

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