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;

     • 

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

原文：https://www.cnblogs.com/zzhAylm/p/14774676.html