mysql組合索引，abc索引命中

bgs_fly發表於2020-09-23
MySql索引

mysql聯合索引，abc的爭議實踐

原因：

在一次和同事討論mysql聯合索引的面試題時出現了爭議。主要問題是：a、b、c三個欄位作為聯合索引，b、c；和a、c情況到底會不會命中索引？

網上查閱相關部落格發現很多答案不一樣，於是我乾脆親手操作實驗一下 ，我使用的mysql版本是5.6

一:建立表

為了更直接貼合面試題，欄位直接用AA,BB,CC表示

create table IF NOT EXISTS TEST_COMPOSITE_INDEX 
(
`TID` BIGINT  NOT NULL AUTO_INCREMENT,
`AA` VARCHAR(50) NOT NULL DEFAULT '' ,
`BB` VARCHAR(50) NOT NULL DEFAULT '',
`CC` VARCHAR(50) NOT NULL DEFAULT '',
`DD` VARCHAR(50) NOT NULL DEFAULT '',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`TID`),
KEY `index_comp` (`AA`,`BB`,`CC`)
)ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

sql中我們有兩個索引，一：主鍵TID，二：AA、BB、CC組成的聯合索引：index_comp

二:插入資料

insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A1','B1','C1','D1');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A2','B2','C2','D2');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A3','B3','C3','D3');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A4','B4','C4','D4');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A5','B5','C5','D5');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A6','B6','C6','D6');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A7','B7','C7','D7');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A8','B8','C8','D8');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A9','B9','C9','D9');
insert into TEST_COMPOSITE_INDEX (AA,BB,CC,DD)VALUE('A10','B10','C10','D10');

三:檢視執行計劃
我們分別檢視以下執行計劃：

EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.tid='3' ;
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1';
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.BB='B1';
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.CC='C1' ;
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1' AND tci.CC='C1';
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.BB='B1' AND tci.CC='C1';
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1' AND tci.BB='B1';

以下結果按順序展示，我們一個個看：

第一個：
執行計劃一
首先第一個使用主鍵查詢，可以看到執行計劃中說明使用了主鍵，掃描行數是1，非常高效

第二個：

EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1';

執行計劃二
可以看到同樣使用了索引，這次使用的是我們建立的聯合索引索引：index_comp。注意key_len:索引使用的位元組數，這個後面會用作對比

第三、四個：

EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.BB='B1';
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.CC='C1' ;

執行計劃三
執行計劃四
只是使用BB或者CC欄位，並不會使用索引，掃描行數10

第五個：

EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1' AND tci.CC='C1';

在這裡插入圖片描述

這裡是我們有爭議的一個點，可以看到的確使用了索引，但是key_len索引使用的位元組數是152和第二個語句只用A查詢，使用的位元組數是一致的，所以這句的結論是：A、C查詢的時候雖然使用了索引，實際其實只用了A，而不是AC

第六個：

EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.BB='B1' AND tci.CC='C1';

在這裡插入圖片描述
第七個，顯而易見，B、C查詢的時候並不會使用索引。

EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1' AND tci.BB='B1';

在這裡插入圖片描述

最後是正常使用的聯合索引，遵循最左匹配，索引使用的位元組數：304，正好是之前命中單個欄位的雙倍

最後補充說明面試中可能碰到的問題：

如果是組合索引，且遵循最左匹配，如果其中有欄位是範圍查詢，那麼：命中的欄位只會到範圍查詢那個欄位，比如：
sql EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA>'A1' AND tci.BB='B1';
那麼這次查詢使用的索引欄位只有：AA,而不會使用BB

相關文章