MySQL索引优化二

索引优化实践

前提条件

• 上篇MySQL索引优化一介绍了索引中Explain的使用，下要介绍在日常开发中的使用。
• 先要准备表格数据，本文章讨论案例的表结构如下，读者可以自行插入10条以上的数据来模拟下面的各种情况，数据太少有些情况出不来预期结果。

drop table if exists test;
create table test(
	id int auto_increment primary key,
	a varchar(32),
    b varchar(32),
    c varchar(32),
	key idx_abc(a,b,c)
) engine=innodb charset=utf8mb4;

多场景实践

等值查询交换位置

• 在进行等值查询的时候，改变查询顺序不能改变Explain的执行计划，因为后台优化器会优化执行，但是推荐还是按照索引的顺序来查询

mysql> -- 执行等值查询
mysql> explain select * from test where a = '1' and b = '1' and c = '1';
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref               | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | idx_abcd      | idx_abcd | 393     | const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)

mysql> -- 交换位置查询
mysql> explain select * from test where a = '1' and c = '1' and b = '1';
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref               | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | idx_abcd      | idx_abcd | 393     | const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)

比较列右边的索引全失效

• 从下图的执行情况可以看出比较列后面的列失效，因为key_len明显减少了一个列的长度。但是如果比较列在最后是没有影响的。
• 画外音：单列的key_len=4n+2+1，其中n是字段的长度，因为是varchar类型长度+2，因为可以为null，长度+1
  - 调整列的顺序，比较在最后的索引列可以用到所有的索引列
  

最佳左前缀

• 下图只用了一个索引列，从Extra信息可以看出排序的时候使用了索引Using index，c没有用上。说明了中间列如果断开之后后面的索引列就用不上了(中间兄弟不能断)
  - 如果按照顺序来执行，从key_len明显可以看出前面两个索引用在查询上，第三个索引用在了排序上
  - 如果是跳过中间索引来执行排序，从下图可以看出出现了Using filesort，排序的索引失效了。
  

排序中使用索引

• 顺序使用索引列，从下图可以看出key_len中使用了一个索引列，而其它的两列b、c出现在排序中，但是并没有出现Using filesort。说明排序也要满足最佳左前缀原则
  - 交换排序字段为order by c,b，查询索引没有改变，但是出现了Using filesort，不符合最佳做前缀原则
  - 索引列同时出现在查询和排序时。查询列使用了索引，排序的时候使用了索引，并没有产生Using filesort
  - 排序方式不一致导致索引失效，日常使用中需要保证索引的排列方式一致
  - in查询：由于有多个等值条件也相当于范围查询，这个时候后面的索引失效。
  

分组中索引的使用

• 查询的key_len为两个字段的长度，没有出现排序。说明分组索引生效。
  - 如果分组的时候不是按照索引的顺序来进行分组，如下图的第二种场景，会出现Using filesort，而第一种是按照索引的顺序来查询没有出现Using filesort
  

总结

• MySQL支持两种方式的排序Using filesort和Using index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
• order by满足两种情况会使用Using index。
  • order by语句使用索引最左前列。
  • 使用where子句与order by子句条件列组合满足索引最左前列。
• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。
• 如果order by的条件不在索引列上，就会产生Using filesort。
• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则
• where优先过滤 能写在where中的限定条件就不要去having限定。

欢迎可以关注spring-cloud系列 openfeign hystrix，打怪升级系列，性能优化系列
你的点赞和关注是我创作的最大动力，有什么不足和错误的地方欢迎留言！可以微信搜索关注【小二说码】