mysql索引常见面试题

建表

create table test1(
	id int not null PRIMARY KEY auto_increment,
	c1 char(10),
	c2 char(10),
	c3 char(10),
	c4 char(10),
	c5 char(10)
);

INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('a1','a2','a3','a4','a5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('e1','e2','e3','e4','e5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('b1','b2','b3','b4','b5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('c1','c2','c3','c4','c5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('d1','d2','d3','d4','d5');

create index idx_c1234 on test1(c1,c2,c3,c4);

场景分析

• 说明：不同的版本结果可能不一样

索引有查找和排序两大功能

• 用到了索引c1,c2使用查找,c3使用排序,c3使用到是因为mysql下面有查询优化器进行优化将sql语句调整到最优情况最好按照索引创建的顺序来写SQL避免mysql底部优化器优化:explain SELECT * from test1 where c1='a1' and c2='a2' and c4='a4' order by c3;
  
• 用到了c1c2c3c4:explain SELECT * from test1 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
  
• c3用作排序：explain SELECT * from test1 where c1='a1' and c2='a2' order by c3;
  
• c4未用到且是filesort：explain SELECT * from test1 where c1='a1' and c2='a2' order by c4;
  
• 只有c1用到索引c2,c3用于排序：explain SELECT * from test1 where c1='a1' and c5='a5' order by c2,c3;
  
• 出现了filesort，索引顺序是c2、c3，检索顺序是c3、c2颠倒了没走索引所以是filesort：explain SELECT * from test1 where c1='a1' and c5='a5' order by c3,c2;
  
• c2=a2相当于有常量，order by一个(c3,常量)的时候就相当于是order by c3，此时c1c2走索引，c3走排序不会filesort：explain SELECT * from test1 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
  
• c1c2用于索引，c2c3用于排序：explain SELECT * from test1 where c1='a1' and c2='a2' order by c2,c3;
  
• 只使用到了c1索引，c2c3查的索引没有扫全表：EXPLAIN select c2,c3 from test1 where c1='a1' and c4='a4' group by c2,c3;
  
• EXPLAIN select c2,c3 from test1 where c1='a1' and c4='a4' group by c3,c2;
  
• 索引分析总结
  • 定制查找是常量
  • 范围后面是失效
  • 最终看排序，一般order by都会给个范围
  • group by基本上都需要进行排序，会有临时表产生
• 建议
  • 单值索引尽量选择针对当前查询过滤性更好的索引
  • 组合索引选择时，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好；尽量选择可能包含当前查询中的where字句中包含更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
• 优化总结口诀
  • 全值匹配我最爱，最左前缀要遵守
  • 带头大哥不能死，中间兄弟不能断
  • 索引列上少计算，范围之后全失效
  • like百分写右边，覆盖索引不写星
  • 不等空值还有or,索引失效要少用
  • str引号不可丢，SQL高级也不难