【MySQL数据库高级】索引面试分析

建立索引：

create index id_c1234 on test03(c1,c2,c3,c4):

---

分析索引使用情况：

• 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 = 'c3';
• explain select * from test03 where c1='a1' and c2 = 'a2' and c3 = 'c3' and c4 ='c4';

---

• explain select * from test03 where c1='a1' and c2 = 'a2' and c4 = 'c4' and c3 = 'c3';
  四个都用到了
• explain select * from test03 where c4='a4' and c3 = 'a3' and c2 = 'c2' and c1 = 'c1';
  四个都用到了
• explain select * from test03 where c1='a1' and c2 = 'a2' and c3 > 'c3' and c4 ='c4'
  用到了3个，a3勉强用了一部分，范围之后全失效。
• explain select * from test03 where c1='a1' and c2 = 'a2' and c4 > 'c4' and c3 ='c3'
  使用了4个
• explain select * from test03 where c1='a1' and c2 = 'a2' and c4 ='c4' order by c3
  用到了两个，c3勉强排序用到了
• explain select * from test03 where c1='a1' and c2 = 'a2' order by c3
  用到了两个，c3勉强排序用到了
• explain select * from test03 where c1='a1' and c2 = 'a2' order by c4
  用到了两个，多了个filesort
• explain select * from test03 where c1='a1' and c5 = 'a5' order by c2，c3
  查找用到了一个，c2,c3用于排序，无filesort
• explain select * from test03 where c1='a1' and c5 = 'a5' order by c3，c2
  查找用到了一个，c3用于排序，有filesort
• 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 ='c5' order by c2，c3
  和上一个相同
• explain select * from test03 where c1='a1' and c2= 'a2' and c5 ='c5' order by c3，c2
  和上一个相同
• explain select * from test03 where c1='a1' and c5 ='c5' order by c3，c2
  使用了一个查找索引，出现了filesort
• 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
  查找用到了一个，出现了filesort，temporay

---

• group by 基本上都需要先进行排序，如果错乱，会有临时表产生
•