引言

復(fù)合索引是指包含多個(gè)數(shù)據(jù)列的索引，與之概念相對(duì)的是單列索引，僅包含一個(gè)數(shù)據(jù)列。在大多數(shù)情況下，建立多列索引的好處都要多于單列索引。另外，復(fù)合索引最多支持16個(gè)列，但請(qǐng)一定不要讓復(fù)合索引包含太多的列，這會(huì)導(dǎo)致索引空間的浪費(fèi)。

索引是一種有序的數(shù)列，復(fù)合索引也是如此。

相對(duì)于單一索引，復(fù)合索引有一些必須注意的使用細(xì)節(jié)，否則很容易造成索引失效，降低查詢速度。而要了解這些注意細(xì)節(jié)，就必須從復(fù)合索引的作用方式入手。

一、復(fù)合索引的作用方式

所謂“復(fù)合”，那一定是包含有多個(gè)，然而人們更愿意說(shuō)“復(fù)合索引”勝于“組合索引”（實(shí)際上復(fù)合索引也叫組合索引），是因?yàn)閺?fù)合更有?1 + 1 > 2 的感覺(jué)。換句話說(shuō)，復(fù)合索引并不是多個(gè)單一索引的組合。

為什么要強(qiáng)調(diào)這一點(diǎn)，是因?yàn)閺?fù)合索引的作用方式在于不斷地圈定范圍，從左到右（左前綴匹配原則，下面會(huì)介紹），在前面索引查找的基礎(chǔ)之上再進(jìn)行索引查找，這就是復(fù)合索引的作用本質(zhì)。

復(fù)合索引的性能要優(yōu)于多個(gè)單一索引，即便是復(fù)合索引與多個(gè)單一索引都用到了相同的列。

二、左前綴原則及復(fù)合索引的生效情況

這一節(jié)我們假設(shè)有一個(gè)包含三個(gè)列（c1, c2, c3）的復(fù)合索引，來(lái)討論一下復(fù)合索引的 “左前綴原則” 。

如果有一個(gè)復(fù)合索引，包含了三個(gè)列，想要在SQL查詢中使用該索引，必須使用復(fù)合索引靠左側(cè)的列?？梢詢H使用 c1，也可以使用 c1 和 c2 ，當(dāng)然也可以使用 c1，c2，c3。這就是復(fù)合索引的左前綴匹配原則。

左前綴原則的索引生效，是由存儲(chǔ)引擎對(duì)應(yīng)的索引結(jié)構(gòu)所決定的，在我們熟悉的 InnoDB 和 MyISAM 中，索引的數(shù)據(jù)類(lèi)型是 BTree，正因?yàn)槿绱?#xff0c;我們才（一般）說(shuō)索引是有序的，相對(duì)于其他的索引，比如哈希索引，我們同樣可以通過(guò)哈希算法的特性了解到，這是一種隨機(jī)的，無(wú)序的索引結(jié)構(gòu)，它的作用在于存儲(chǔ)更快。當(dāng)然這些稍微有點(diǎn)題外話。

左前綴原則還有一些需要討論的應(yīng)用細(xì)節(jié)，僅僅通過(guò) “必須使用靠左側(cè)的列” 還是會(huì)在實(shí)際操作中存在疑惑。

如果只要求讓復(fù)合索引生效，那只要使用第一個(gè)列，我們就可以說(shuō)這個(gè)復(fù)合索引生效了。但生效并不意味著最快，復(fù)合索引有其本身的復(fù)雜度，如果想發(fā)揮出復(fù)合索引做大的性能效果，就需要盡可能的（在實(shí)際需求范圍內(nèi)）使用復(fù)合索引中更多的列。

在 WHERE 子句中，我們對(duì)一個(gè)查詢進(jìn)行篩選，如何能充分發(fā)揮復(fù)合索引的作用呢？

案例1：復(fù)合索引的最大化

WHERE c1 = x AND c2 = y AND c3 = z;

上述條件子句可以讓包含 c1，c2，c3 的復(fù)合索引發(fā)揮最大的效果，當(dāng)然，他們?cè)?WHERE 子句中的書(shū)寫(xiě)順序無(wú)關(guān)緊要，你也可以這樣寫(xiě)：

WHERE c3 = x AND c2 = y AND c1 = z;

MySQL會(huì)自動(dòng)對(duì)SQL語(yǔ)句進(jìn)行優(yōu)化，存儲(chǔ)引擎依然會(huì)按照先查找 c1 再查找 c2 最后查找 c3 的順序來(lái)執(zhí)行，保證索引生效。

案例2：左前綴匹配的生效情況

WHERE c1 = x AND c3 = z;

上述條件中針對(duì) c1 和 c3 進(jìn)行了篩選，但根據(jù)左前綴匹配原則，上述子句并沒(méi)有“靠左使用索引列”，中間跳過(guò)了 c2 ，因此這個(gè)篩選條件只用到了復(fù)合索引中的 c1 列，雖然復(fù)合索引生效，但是性能并不是最好的。

案例3：范圍查詢（包括 >、<=、<>、like等）中復(fù)合索引的生效情況

WHERE c1 > x AND c2 = y AND c3 = z;

上述條件中，c1 是范圍查詢，而 c2、c3 都是等值查詢，這種情況依然只使用了復(fù)合索引的 c1 索引列。而c2、c3 的索引列并未生效。

WHERE c1 = x AND c2 < y AND c3 = z;

上述條件和前一種比較類(lèi)似，在篩選條件中都用到了范圍查詢，但不同的是 c2 是范圍查詢，這時(shí)復(fù)合索引生效，但只用到了 c1 和 c2 索引列，并未使用 c3 索引列。

通過(guò)這兩個(gè)例子，我們也可以發(fā)現(xiàn)，復(fù)合索引的各個(gè)索引列應(yīng)該是一種從左到右逐級(jí)篩選的關(guān)系，因此，在建立復(fù)合索引的時(shí)候也要充分考慮存儲(chǔ)數(shù)據(jù)的列與列的關(guān)系，找到當(dāng)列等于某個(gè)值時(shí)，數(shù)據(jù)范圍依次縮小的列，將這樣的列共同組成復(fù)合索引。而且，在查找不確定的列值時(shí)，會(huì)導(dǎo)致復(fù)合索引中后定義的（索引定義中右側(cè)的，不是WHERE 子句中的右側(cè)）索引列失效。不過(guò)，失效并不意味著錯(cuò)誤，根據(jù)具體的情況來(lái)講，如果某個(gè)列真的是無(wú)法篩選等值的話，那么失效也是在所難免，應(yīng)該辯證的去分析問(wèn)題，一定不能為了追求索引的最大化，而導(dǎo)致業(yè)務(wù)邏輯錯(cuò)誤！?

另外，IN() 函數(shù)也可以使索引正常生效，可以看做是一種多等值判斷的情況。

案例4：order by 子句參與下的復(fù)合索引生效情況

WHERE c1 = x AND c3 = z ORDER BY c2 ;

這條語(yǔ)句對(duì) c2 列進(jìn)行了排序，同時(shí)對(duì) c1 和 c3 進(jìn)行了等值篩選，通過(guò)左前綴匹配規(guī)則，復(fù)合索引中的 c1 索引列肯定是生效的，c3 索引列肯定是未生效的，而 c2 索引列有沒(méi)有生效呢？答案是否定的，也就是說(shuō)，此條件中的復(fù)合索引僅有 c1 列生效，而 c2 和 c3 列都失效了。

關(guān)于order by 子句對(duì)索引的使用，情況比較復(fù)雜，我會(huì)在后面的學(xué)習(xí)中單獨(dú)對(duì)其進(jìn)行總結(jié)，因此最有效的方式還是結(jié)合 explain 來(lái)進(jìn)行分析。

三、復(fù)合索引的創(chuàng)建語(yǔ)句

CREATE INDEX index_name ON table_name(col1, col2, col3) ;

例如，在 student 表中對(duì)年級(jí)、班級(jí)建立復(fù)合索引：

CREATE INDEX idx_grade_class ON student(grade_id, class_id);

四、使用執(zhí)行報(bào)告查看復(fù)合索引的使用情況

使用復(fù)合索引后我們?nèi)绾慰吹叫Ч?#xff1f;我認(rèn)為可以通過(guò)兩種方式。第一種是直接觀察SQL的執(zhí)行速度，增加復(fù)合索引前和之后執(zhí)行的時(shí)間可以為我們提供參考依據(jù)。

那么第二種方式就是通過(guò) explain 執(zhí)行報(bào)告，分析復(fù)合索引的使用情況。

還是以 student 表為例，來(lái)觀察不同的SQL語(yǔ)句的執(zhí)行報(bào)告，MySQL版本：5.7.27-log

表結(jié)構(gòu)如下：

CREATE TABLE `student` (`stu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)生id',`grade_id` int(11) DEFAULT NULL COMMENT '年級(jí)id',`class_id` int(11) DEFAULT NULL COMMENT '班級(jí)id',`stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',`stu_gender` char(1) DEFAULT NULL COMMENT '學(xué)生性別，1:男。2:女',`stu_age` int(11) DEFAULT NULL COMMENT '學(xué)生年齡',`address` varchar(50) DEFAULT NULL COMMENT '家庭住址',`enrollment_time` date DEFAULT NULL COMMENT '入學(xué)日期',PRIMARY KEY (`stu_id`),KEY `idx_grade_class` (`grade_id`,`class_id`) ) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8 EXPLAIN SELECT * FROM student WHERE grade_id = 1 ; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- --------------- ------- ------ ------ -------- -----1 SIMPLE student (NULL) ref idx_grade_class idx_grade_class 5 const 28 100.00 (NULL) EXPLAIN SELECT * FROM student WHERE grade_id = 1 AND class_id = 3 ; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- --------------- ------- ----------- ------ -------- -----1 SIMPLE student (NULL) ref idx_grade_class idx_grade_class 10 const,const 4 100.00 (NULL) EXPLAIN SELECT * FROM student WHERE class_id = 3 ; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- -------------1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 78 10.00 Using where EXPLAIN SELECT * FROM student WHERE grade_id IN(1, 2) AND class_id = 3 ; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- --------------- ------- ------ ------ -------- -----------------------1 SIMPLE student (NULL) range idx_grade_class idx_grade_class 10 (NULL) 5 100.00 Using index condition EXPLAIN SELECT * FROM student ORDER BY grade_id, class_id ; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- ----------------1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 78 100.00 Using filesort

還有最后一個(gè)，范圍查找，比較有意思：

EXPLAIN SELECT * FROM student WHERE grade_id > 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- ------ ------- ------ ------ -------- -------------1 SIMPLE student (NULL) ALL idx_grade_class (NULL) (NULL) (NULL) 78 32.05 Using where EXPLAIN SELECT * FROM student WHERE grade_id > 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- --------------- ------- ------ ------ -------- -----------------------1 SIMPLE student (NULL) range idx_grade_class idx_grade_class 5 (NULL) 1 100.00 Using index condition EXPLAIN SELECT * FROM student WHERE grade_id >= 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- ------ ------- ------ ------ -------- -------------1 SIMPLE student (NULL) ALL idx_grade_class (NULL) (NULL) (NULL) 78 32.05 Using where EXPLAIN SELECT * FROM student WHERE grade_id < 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -- ----------- ------- ---------- ------ --------------- ------ ------- ------ ------ -------- -------------1 SIMPLE student (NULL) ALL idx_grade_class (NULL) (NULL) (NULL) 78 67.95 Using where

范圍查找所用索引的情況很令人費(fèi)解，我暫時(shí)還沒(méi)找到合理的解釋，這種現(xiàn)象可能和表中數(shù)據(jù)太少有關(guān)系？所以關(guān)于SQL優(yōu)化的理論知識(shí)，一定要配合 explain 執(zhí)行計(jì)劃來(lái)學(xué)習(xí)，有時(shí)候板上釘釘?shù)慕Y(jié)論依然可能是錯(cuò)誤的。

?

總結(jié)

以上是生活随笔為你收集整理的MySQL 高级 —— 复合索引简介（多列索引）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。