记录一次简单的SQL调优引发的学习和思考

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《记录一次简单的SQL调优引发的学习和思考》，主要介绍了MySQL、数据库，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

现在有一张会员表，表的结构如下：

可以看到，platform_id 一列是存在索引的。现在分析以下SQL语句：

从第五列type字段可以看到，这次分析的结果是走了全表扫描。在第一时间，我还没反应过来是怎么回事，按道理说platform_id字段有索引，是应该走索引的，虽然后面的deleted_at并没有索引，但也只是最多造成无法覆盖索引，deleted_at字段需要回表查询而已。
然后，我又做了一次尝试，如图：

我把deleted_at字段去掉之后，发现可以用上索引了。此时的我并没有注意到type字段所对应的使用的访问类型，于是陷入了沉思。
为什么查询条件添加deleted_at后会导致索引失效？这不应该啊。于是，我又做了一次尝试，我在platform_id的索引后追加了deleted_at字段：

然后重复执行了上面的分析语句：

不出所料，索引的确是用上了，难道，我要在每个索引字段后面都加上deleted_at才可以？一时间我产生了这样的想法，但是马上否定了，因为平时工作中大家并没有这种习惯，肯定是别的地方出现了问题，导致索引失效了。我重新把platform_id索引后的deleted_at字段去掉，恢复到原来的索引。然后开启了OPTIMIZER_TRACE用于跟踪mysql优化器分析SQL语句的优化过程：

set optimizer_trace="enabled=on";
show variables like 'optimizer_trace';

执行结果如图：

然后执行以下语句查询跟踪结果树：

select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;

将json树复制到线上网站后解析发现：

好家伙，优化器在分析可选索引时最终并没有选择我想要的索引，但是原因是unknown，并没有获得什么有用的信息。

后来，我在网上复习了一下导致索引失效的情况总结，突然间注意到这个：

然后猛地回头一看数据库字段，果然，我大意了！我的platform_id属于varchar类型，这次应该是破案了。马上重新加上引号后重新分析：

总算用上了索引。虽然问题是解决了，但是还是要反思一下这次学习到的东西，于是，我把不同情况的SQL分析计划放在一起进行对比：

通过进行对比，还是能够发现不少问题的，以下为结论部分：

1. 第一条语句，访问类型为ALL，走的是全表扫描；Extra信息是Using where，意味着Mysql服务器在存储引擎检索后再进行过滤，此过滤发生在Mysql Server层。
2. 第二条语句，访问类型为index，把索引树扫描了一遍；Extra信息是Using where，Using index，表示做到了覆盖索引但需要进行过滤。
3. 第三条语句，访问类型为ref，用到了索引查找，它返回所有匹配某个单个值的行；Extra信息是Using index，表示做到了覆盖索引。
4. 第四条语句，访问类型为ref，同上；Extra信息是Using where，表示需要进行过滤，但很明显不一样的是，此处没有做到覆盖索引。

要完全理解上述四种情况的差别，需要先熟悉不同访问类型的区别，比如索引查找和索引扫描的区别；要理解Extra信息具体表示的意思，还需要理解覆盖索引、回表查询、索引下推等概念，不清楚的可以看看文章最后的参考文章。
总结这次之所以没有第一时间发现真正的问题所在，是因为没有注意到在执行第二条语句时，访问类型为index，也就是说仅仅扫描了整个索引树，但是没有利用到索引查找，在性能上是远低于ref访问类型的。

拓展延申：那么，如果我单独给deleted_at字段加上索引？数据库结构如下图，会发生什么情况呢？

先看下面两段SQL分析：

1. 第一种情况，也即导致platform_id索引失效的情况，访问类型属于ref，走的是deleted_at索引，Extra信息包含Using where，但没有Using index，证明没法做到覆盖索引，需要回表查询进行过滤，此处需要过滤的字段就是platform_id字段。（注：这里的Extra同时还出现了Using index condition，由于是伴随着Using where出现的所以也不仅仅是ICP，属于一种比较特殊的情况，能力和篇幅有限所以就先不讨论这种特例了）
2. 第二种情况，也即platform_id索引没有失效的情况，访问类型属于ref，注意并不是index_merge，有人可能会认为这里mysql优化器会采用索引合并的方法，但事实上mysql优化器选择了走platform_id索引并通过回表查询出deleted_at进行过滤。因此Extra信息显示的是Using where。置于这里为什么没有用到index_merge，那是因为优化器觉得不用更快一点，在数据量不一样的情况下，优化器也有可能会采用index_merge技术。

关于什么情况下才会用到index_merge技术，可以参考下面这篇文章的案例：

https://www.cnblogs.com/Jiang...

最后需要注意的是，不建议单独给deleted_at字段添加索引，因为可能会导致查询性能下降，具体案例可以参考这篇文章：

https://www.jianshu.com/p/aef...

其它参考文章推荐：
Mysql优化之explain详解：https://www.jianshu.com/p/73f...
访问类型type级别分析：https://www.cnblogs.com/sweet...
执行计划Extra对比分析：https://www.cnblogs.com/wy123...
常见索引失效场景：https://blog.csdn.net/weixin_...
Mysql优化器跟踪：https://blog.csdn.net/hehuyi_...
InnoDB与MyISAM 聚簇索引与非聚簇索引索引：https://www.cnblogs.com/jiawe...
Mysql索引ICP：https://www.jianshu.com/p/c64...
索引覆盖、回表查询以及索引下推：https://www.cnblogs.com/youzh...
MySQL的server层和存储引擎层交互： https://juejin.cn/post/684490...

书籍推荐：
《高性能Mysql》：https://book.douban.com/subje...

文中关于mysql的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《记录一次简单的SQL调优引发的学习和思考》文章吧，也可关注golang学习网公众号了解相关技术文章。