Mysql多列索引经典案例


原文链接: Mysql多列索引经典案例
一个经典的多列索引案例,如题:

假设某个表有一个联合索引(c1,c2,c3,c4)以下——只能使用该联合索引的 c1,c2,c3 部分
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=x and c5=x order by c2,c3
E 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这条sql的结果,与索引有关的主要是possible_keys,key,key_len这三项,
possible_keys是指可能会用到的索引,key是当前sql使用到的索引,key_len是索引的使用字节数
key的值是c1234表示联合索引用上了,那是不是c1,c2,c3,c4全用上了咧,我们得从key_len分析一下
因为字段类型是char(1),key_len现在等于4表示c1,c2,c3,c4这四个字段都用上了索引

再接着看B这条sql语句

我们看到key=c1234,表示B使用了联合索引,key_len=2表示有两个字段使用了索引,这两个字段就是C1和c2,这个sql里面有一个order by c3,order by不能使用索引,但是却利用了索引,为什么这么说咧,先看C这条sql

key=c1234,表示B使用了联合索引,key_len=1表示有1个字段使用了索引,这个字段就是C1,与B语句不一样的是 Extra的值
C语句里面使用了临时表(Using temporary) 和 排序(filesort),
因为组合索引是需要按顺序执行的,比如c1234组合索引,要想在c2上使用索引,必须先在c1上使用索引,要想在c3上使用索引,必须先在c2上使用索引,依此。。
回到B语句中,因为c2字段已经使用了索引,所以在order by c3的时候 c3其实在索引表里面已经是排好序的了,不需要建临时表,不需要再排序,所以说其实他利用上了索引
而C语句中,group by 的顺序是先c3,再c2,在对c3进行group by的时候,c2字段上的索引并没用使用,所以索引在这里就断了,只用上了c1一个字段的索引
D语句c1字段使用了索引,c2,c3字段在order by中是顺序执行 所以也利用了索引

E语句c1和c2使用了索引,c3在order by中利用了索引

分析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 的值既是固定的,参与排序时并不考虑
`