聯(lián)合索引也叫多列索引, 其常見的實現(xiàn)方式為連接索引(concatenated index), 它是通過將一列的值追加的一列后面形成的, 其連接的順序由創(chuàng)建索引是指定, MySQL便是使用的這種方式. 另一種方式成為多維索引(multi-dimensional index), 這種方式比較復(fù)雜, 有興趣的同學(xué)可自行搜索相關(guān)資料.
聯(lián)合索引的創(chuàng)建
- 隨表創(chuàng)建
CREATE TABLE `t_index_explain` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`a` varchar(20) DEFAULT NULL,
`b` varchar(20) DEFAULT NULL,
`c` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- 單獨創(chuàng)建
ALTER TABLE `t_index_explain`
ADD INDEX `index_a_b_c`(`a`, `b`, `c`) USING BTREE;
如上兩種方式, 都是創(chuàng)建了一個a, b, c 的聯(lián)合索引, 其定義的順序是abc,故在實際索引的中的每一個值為(a, b, c)這樣一個連接組合形式, 甚至可以簡單粗暴的理解為索引列的值就是a+b+c(以字符串的方式連接在一起). 如此就把多列索引變成了我們熟悉的單列索引. 其工作原理也是一致的.
用法與注意事項
聯(lián)合索引遵循最左前綴原則, 原因是其索引值連接順序為定義的從左至右連接, 因此如下SQL是可以使用index_a_b_c索引的:
SELECT * FROM t_index_explain WHERE a = "1" and b = "1";
如下是該SQL的解釋結(jié)果:
其中Extra 說明了我們確實使用了index, possible_keys說明了可能的index只有 index_a_b_c.
常見誤區(qū): 很多人認(rèn)為, 所謂的最左前綴原則, 需要我們編寫的SQL條件順序與定義順序一致, 其實不是的(查詢優(yōu)化器沒那么傻), 如下佐證:
SELECT * FROM t_index_explain WHERE b = "2" and a = "1";
把a, b 條件的順序調(diào)換一下, 也會得到同樣的解釋結(jié)果. 其成立的條件應(yīng)該是條件中是否包含index定義最左邊的字段. 如下的sql與執(zhí)行結(jié)果可證明這一點.
SELECT * FROM t_index_explain WHERE c = "2" and a = "1";
不能使用索引的情況, 在本例中只要不包含a字段的查詢, 都不能使用該聯(lián)合索引(除非索引覆蓋).
SELECT * FROM t_index_explain WHERE c = "2";
SELECT * FROM t_index_explain WHERE b = "2";
SELECT * FROM t_index_explain WHERE c = "2" and b = "1";
以上SQL解釋執(zhí)行結(jié)果:
其中Extra提示了Using Index但是possible_keys為NULL, key 中出現(xiàn)了我們創(chuàng)建的索引index_a_b_c, 其原因是發(fā)生了索引覆蓋. 如果表中的字段/數(shù)據(jù)豐富一些, 多一些可以觀察的不一樣的執(zhí)行結(jié)果, 有興趣的同學(xué)可自證.
索引與排序
眾所周知, 索引是有序的, 但是實際開發(fā)中索引字段, 與排序字段往往不是一個比如訂單表中我們需要查詢某個用戶的數(shù)據(jù), 但是按支付時間排序, SQL如下:
表:
CREATE TABLE `t_order` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`userid` bigint(0) NULL,
`pay_time` datetime(0) NULL,
PRIMARY KEY (`id`)
KEY `index_userid` (`userid`) USING BETREE
);
select * from t_order where userid = 1 order by pay_time
如果創(chuàng)建的是userid的單列索引, 那么如上查詢語句解釋結(jié)果
可以看到Extra中出現(xiàn)了 Using filesort 表明該操作需要單獨進(jìn)行一次排序操作.
修改索引為聯(lián)合索引:
ALTER TABLE `micro_stories`.`t_order`
DROP INDEX `index_userid`,
ADD INDEX `index_userid_pay_time`(`userid`, `pay_time`) USING BTREE;
如上解釋結(jié)果可以看出, 沒有filesort了. 所以索引不只可以來做查詢條件的, 也可以利用索引的有序性來做排序優(yōu)化.
總結(jié)一下
- 聯(lián)合索引的每個索引值是以索引定義中字段的順序, 連接在一起組成的, 其索引的基本結(jié)構(gòu)仍然是B+樹
- 聯(lián)合索引生效需要滿足條件中存在索引定義最左邊的字段(最左前綴原則)
- 聯(lián)合所以可用于輔助排序, 提升查詢效率
