索引面试题目分析

一、.建表语句

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');

建索引

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

1.题目一

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

索引使用上的原因？

mysql对查询语句优化，特别是常量，因此才会对内部进行了顺序的调整而用上了索引

2、题目二

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

c1c2使用上了索引，从c3开始就是range,c4就没办法用索引了

3.题目三

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

mysql自己会调整顺序，c1,c2,c3,c4都用到了索引，c4被调整到了最后，key_len说明了c4被调整到了最后

4.题目四

用上了order by，本来c2,c4中间断了c3，如果order by c3会怎样？

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

原因：索引两大功能：查找和排序。c1,c2用到了索引，用于查找。c3也用到了索引用于排序。

5.题目五

去掉c4，结果一样

6、题目六

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

用到了索引，但出现了Using filesort

7、题目七

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

c1用到了索引，c2,c3用在了排序

8、题目八

顺序调整了

出现了filesort，我们建的索引是1234，他没有按照顺序来，32颠倒了

9、题目九

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

10、题目十

多了个c5，但是对接过没有影响

用c1,c2两个字段索引，但是c2,c3用于排序无filesort

11、题目十一

c3,c2排序却没有filesort

order by c3,c2不出现filesort的特殊原因就是因为排序字段c2已经是一个常量(c2='c2')了

所以相当于order by c3,'a2常量'

12、题目十二

https://blog.csdn.net/u011409644/article/details/73611102

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;

记住分组之前，肯定是先排序，对于索引优化来说，其实都差不多

13.题目十三

b>4和 b like 'kk%' 都是放哪位有什么区别呢？

分析如下

explain select * from test03 where c1='a1' and c2 > 'a2' and c3='a3';
explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';

like 'kk%' 和 like '%kk'的区别

 

explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';

 

explain select * from test03 where c1='a1' and c2 like '%kk' and c3='a3';

explain select * from test03 where c1='a1' and c2 like '%kk%' and c3='a3';

explain select * from test03 where c1='a1' and c2 like 'k%kk%' and c3='a3';

二、优化口诀总结