Mysql多列索引經典案例

標籤：from   logs   body   索引   image   sort   png   分析   等價   

一個經典的多列索引案例，如題：

假設某個表有一個聯合索引（c1,c2,c3,c4）一下——只能使用該聯合索引的 c1,c2,c3 部分Awhere c1=x and c2=x and c4>x and c3=xB where c1=x and c2=x and c4=x order by c3C where c1=x and c4= x group by c3,c2D where c1=x and c5=x order by c2,c3E where c1=x and c2=x and c5=? order by c2,c3

建立一張測試表

create table test (    c1 tinyint(1) not null default 0,    c2 tinyint(1) not null default 0,    c3 tinyint(1) not null default 0,    c4 tinyint(1) not null default 0,    c5 tinyint(1) not null default 0,    index c1234(c1,c2,c3,c4));//插入兩條資料insert into test values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);insert into test values (2,4,5,8,7),(1,3,5,8,4),(3,4,2,9,6);

分析A => c1=x and c2=x and c4>x and c3=x <==等價==> c1=x and c2=x and c3=x and c4>x
c1,c2,c3,c4 都能用上

分析B => select * from test where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2 索引用上了,在 c2 用到索引的基礎上,c3 是排好序的,因此不用額外排序,而 c4 沒發揮作用

分析C => select * from test where c1=1 and c4=2 group by c3,c2
只用到 c1 索引,因為 group by c3,c2 的順序無法利用 c2,c3 索引

分析D => select * from test where c1=1 and c5=2 order by c2,c3
C1 確定的基礎上,c2 是有序的,C2 之下 C3 是有序的,因此 c2,c3 發揮的排序的作用. 因此,沒用到 filesort

分析E => select * from test where c1=1 and c2=3 and c5=2 order by c3;
因為 c2 的值既是固定的,參與排序時並不考慮

Mysql多列索引經典案例