MySQL高级--索引面试题分析

1. 建表插入数据：

   • 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');
   • 
2. 建立索引:alter table test03 add index idx_test03_c1234 (c1,c2,c3,c4);
3. sql语句分析：

   • explain select * from test03 where c1='a1‘;

   • explain select * from test03 where c1='a1' and c2='a2'; 

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

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

     • 

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

     •  

   • explain select * from test03 where C1='a1' and c2='a2' and C3>'a3' and c4='a4';

     • 

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

     • 

   • explain select * from test03 where C1='a1' and c2='a2' and c4='a4' order by c3;
     c3作用在排序而不是查找

     • 

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

     • 

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

     • 

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

     只用c1一个字段索引，但是c2、c3用于排序，无filesort
     
     

     • 　　

   • explain select * from test03 where C1='a1' and c5='a5' order by c3,c2;
     出现了filesort,我们建的索引是1234， 它没有按照顺序来,T 32颠倒了

     • 

   • 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;
     用c1、c2两个字段索引，但是c2、c3用于排序,无filesort

     • 　　

   • explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
     本例有常量c2的情况，和8.2对比

     • 　　

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

      

     • 

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

     • 

        

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

     • 

   • 总结：

      

     • 定值，范围之后索引失效，order by都是排序给个范围，group by分组之前必排序，排序方式和order by的不一样则会产文件内排序，如果排序和order by 的顺序不一样则会产生临时表
     • 一般性建议：

       • 对于单键索引，尽量选择针对当前query过滤性更好的索引

       • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

       • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

       • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的