一次搞清楚Mysql聯合索引，以及聯合索引究竟用了多少

一群DBA朋友聊天，突然拋出一個某公司聯合索引的面試題，當時好多人都蒙了，這次針對這個問題，做了個簡單的實驗，把聯合索引的作用一次搞清楚

問題大概是這樣的，聯合索引（a,b,c,d）下面這類的查詢分別用到聯合索引的哪部分？

select * from t where a=1 and b=1 and d>1 and c=1;

select * from t where a=1 and b>1 and c>1 order by b,c;

select * from t where a=1 and b=1 and c=1 and d=1 order by b;

select * from t where a=1 and b>1 and d=1 order by c;

?

下面建立測試表，字段都用int not null，并插入測試數據

CREATE TABLE `NewTable` (
`id` int NOT NULL ,
`a` int NOT NULL ,
`b` int NOT NULL ,
`c` int NOT NULL ,
`d` int NOT NULL ,
PRIMARY KEY (`id`)
)
;

建立聯合索引

ALTER TABLE `t`
ADD INDEX `idx_a_b_c_d` (`a`, `b`, `c`, `d`) ;

?

1.explain select?* from t where a=1 and b=1 and d>1 and c=1\G;

id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: idx_a_b_c_d
key: idx_a_b_c_d
key_len: 16
ref: NULL
rows: 1
Extra: Using where; Using index

key_len=16說明索引走了4個字段 4*4字節，聯合索引用到全部字段

2.explain?select * from t where a=1 and b>1 and c>1 order by b,c\G;

id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: idx_a_b_c_d
key: idx_a_b_c_d
key_len: 8
ref: NULL
rows: 1
Extra: Using where; Using index

key_len=8 說明where過濾用了兩個字段 ab, Extra沒有file sort，說明排序用到全部索引bc，所以這個查詢用到了索引的abc部分

3.explain?select * from t where a=1 and b=1 and c=1 and d=1 order by b\G;

id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx_a_b_c_d
key: idx_a_b_c_d
key_len: 16
ref: const,const,const,const
rows: 3
Extra: Using index

key_len=16 說明where過濾用了4個字段abcd，Extra沒出現file sort，說明排序用到了索引b。

4.explain?select * from t where a=1 and b>1 and d=1 order by c\G;

id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: idx_a_b_c_d
key: idx_a_b_c_d
key_len: 8
ref: NULL
rows: 1
Extra: Using where; Using index; Using filesort

key_len=8說明where過濾用到了兩個字段ab， Extra出現filesort，說明排序字段沒用到索引

?

這類的查詢就不一一測試了，總結一下看這類執行計劃應該注意的點

?

key_len

1）key_len單位是字節，比如int，一個字段是4，兩個字段是8

（如果字段可以為空，那么key_len會+1，也就是int會顯示5，兩個字段顯示10）

2）key_len顯示的是where條件用到索引長度，order by 和group by之后的字段不顯示在key_len里面

?

Extra

兩種情況會出現file sort

1）where 條件后面沒有這個字段，但是order by這個字段，會出現file sort

2）order by的字段沒走索引，會出現file sort

還有比較重要的是，聯合索引遇到范圍查詢會停止繼續檢索，比如where a=1 and b=1 and c>1 and d=1那么索引只會檢索到abc

?

當然，排序會延續使用where條件后面的索引，比如下面的情況

?

mysql> explain select * from t where a=1 and b>1 order by b,c\G ;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: idx_a_b_c_d
key: idx_a_b_c_d
key_len: 8
ref: NULL
rows: 1
Extra: Using where; Using index

排序用到了ab，但是排序用到了bcd，也就是這整條查詢用到了abcd全部聯合索引

?