MySQL索引一些常用案例

索引案例分析

• 先创建一些假数据：

create table test(
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 test(c1,c2,c3,c4,c5)values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5)values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5)values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5)values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5)values('e1','e2','e3','e4','e5');

• 创建索引：

create index idx_test_c1234 on test(c1,c2,c3,c4);

• 根据索引进行判断索引是否失效
• order by

explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='a4';
# and连接每个索引字段，顺序是c1,c2,c3,c4.当前最佳匹配，所有索引都用上，并遵循最左前缀，当然精度越高，key_len越高，代价越大。

explain select * from testwhere c1='a1' and c2='a2' and c4='a4' and c3='a3';
# 虽然查询条件顺序发生改变，但c1,c2,c3,c4都能用到索引查询。因mysql内部会做优化。

explain select * from testwhere c4='a4' and c3='a3' and c2='a2' and c1='a1';
# 与上例一样结论，and连接可忽略顺序。sql内部会做优化

explain select * from testwhere c1='a1' and c2='a2' and c3>'a3' and c4='a4';
# c1,c2能用到索引进行查找，因c3是范围条件，索引只用到排序，导致在c3处断裂，这样c4用不到索引

explain select * from testwhere c1='a1' and c2='a2' and c4>'a4' and c3='a3';
# 都能用到索引，首先mysql 根据and连接，内部优化顺序，只不过是c1,c2,c3用索引是用来查找， 而c4用索引进行排序

explain select * from test where c1='a1' and c2='a2' and c4='a4' order by c3;
# 用到c1,c2索引进行查找，c3也用到了索引只不过是用来排序，并c3没有统计到执行计划中，c4没有用到索引

explain select * from testwhere c1='a1' and c2='a2' order by c3;
# 与上例一样。根据跟上例对比，可以发现c4的索引并没有起效。原因c3索引用于范围查找，导致c4无法利用索引。

explain select * from testwhere c1='a1' and c2='a2' order by c4;
# c1,c2使用索引进行查找，但是中间跳过c3直接使用c4进行排序，导致sql内部使用filesort进行排序。

explain select * from test where c1='a1' and c5='a5' order by c2,c3;
# 因为c5没有创建索引，所以c5没有用到索引。而c1使用索引进行查找，c2,c3使用索引进行排序

explain select * from test where c1='a1' and c5='a5' order by c3,c2;
# 与上例不同Extra出现filesort ，c3,c2顺序颠倒，sql内部无法识别，所以sql内部使用filesort进行排序

explain select * from test where c1='a1' and c2='a2' order by c2,c3;
# c1,c2用到索引查询。c2,c3用来排序。

explain select * from test where c1='a1' and c2='a2' and c5='c5' order by c2,c3;
# 与上例结果一样， c5并不会影响结果

explain select * from test where c1='a1' and c2='a2' and c5='c5' order by c3,c2;
# 通过这么多order by案例，一般情况order by没有按照索引顺序排序，会出现filesort。但是，c2在前面索引查找已经是const常量，索引不会出现filesort

• group by

explain select * from test where c1='a1' and c4='a4' group by c2,c3;
explain select * from test where c1='a1' and c4='a4' group by c3,c2;
# 首先知道的是分组之前必须进行排序。
# 第一个例子：使用了c1索引，索引未完全失效。
# 第二个例子：group by 后索引顺序颠倒，出现了filesort和temporary，产生临时表。

• 小结：索引有查找和排序两个功能，一般order by 是进行范围排序，group by基本上分组之前必进行排序，会有临时表产生。

• like

explain select * from test where c1='a1' and c2 like 'kk%' and c3='c3';
# 用到了c1,c2,c3，有人会疑问c2使用索引为范围排序，会导致c3失效，但是c2以常量开头，这样不会导致c3索引失效。

explain select * from test where c1='a1' and c2 like '%kk' and c3='c3';
# 只用到了c1索引。c2以%开头失效，c3根本没有被轮到

explain select * from test where c1='a1' and c2 like 'k%kk%' and c3='c3';
# 用到了c1,c2,c3 与案例一样。

• 案例总结：
  • 对于单键索引，尽量选择针对当前查询过滤性更好的索引。
  • 在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，越靠前越好。
  • 在选择组合索引时候，尽量选择可以能够包含当前查询中where字句中更多字段的索引。
  • 尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的