mysql-热身case

create table test03( 
 id int primary key not null auto_increment, 
 c1 char(10), 
 c2 char(10), 
 c3 char(10), 
 c4 char(10), 
 c5 char(10) 
); 

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5'); 
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5'); 
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5'); 
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5'); 
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5'); 
 
select * from test03; 

create index idx_test03_c1234 on test03(c1,c2,c3,c4); 
show index from test03; 

index.png

问题：我们创建了复合索引idx_test03_c1234 ,根据以下SQL分析下索引使用情况？

#使用c1索引
explain select * from test03 where c1='a1'; 
#使用c1 c2  索引
explain select * from test03 where c1='a1' and c2='a2'; 
#使用c1 c2 c3 索引
explain select * from test03 where c1='a1' and c2='a2' and c3='a3'; 
#使用c1 c2 c3 c4索引
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

比对.png

• 案例1

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';

c4倒置.png

顺序不一样也能查到，因为mysql底层会自动调优转换，假如是4321，mysql底层也会
转化成1234，但是最好是一样的顺序。

• 案例2

explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';

分析结果.png

索引用于范围查找 索引后面全失效，索引三个。

• 案例3

explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

分析结果.png

变化了顺序，mysql底层自动转换，所以c1='a1' and c2='a2' and c3='a3' and c4>'a4' 所以这里是四个。

• 案例4

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;

分析结果.png

只有两个关联的索引c1 c2，索引的功能是查找或者排序，c3的作用是排序不是查找，但是不会被统计到key中，key和ref中只有c1和c2。

• 案例5

explain select * from test03 where c1='a1' and c2='a2' order by c3;

分析结果.png

和上面的结果一样，说明这里和c4没什么关系。

• 案例6

explain select * from test03 where c1='a1' and c2='a2' order by c4; 

分析结果.png

这里没有c3参与，直接使用c4排序，失效。

• 案例7

#顺序索引排序
explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; 
#倒序索引排序
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2; 

分析结果.png

出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了，没有自动转换。

• 案例8

explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2; 

分析结果.png

注意在案例7中，32排序，会出现filesort，可是这里没有出现，因为这里有c2，排序字段已经是一个常量了，所以相当于roder by c3 ，一个常量order by 1，有和没有无关。

• 案例9

explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;

explain select * from test03 where c1='a1' and c4='a4' group by c3,c2; 

分析结果.png

group by表面上是分组，实质上是排序，分组之前必排序，唯一的不同group by有having
定值、范围还是排序，一般order by是给个范围
group by基本上需要进行排序，会有临时表的产生，为了得到数据，先建一张临时表。
5.7下，因为排序的顺序发现不对，需要通过filesort，8.0下没有。