慢查询优化

发现一篇有关慢查询优化的宝藏推文，在这里收藏记录起来，以免作者删除
copy自程序员鱼皮–https://mp.weixin.qq.com/s/inf0SCxOcCMI2DOyrqfn2A

什么是慢 SQL？

先解释一下，慢 SQL 指的是执行时间较长的 SQL 查询或操作。它不是一个固定的时间定义，而是根据具体的应用场景和性能要求来决定。

比如你要从几万条数据中查出某一条数据，超过几百毫秒就已经算是慢 SQL 了；但如果你要向数据库插入几万条数据，可能几十秒都不算是慢 SQL。慢是相对的，没有固定的标准。

某种情况下，也可以将消耗数据库资源过多的 SQL 归纳为慢 SQL，因为它们的危害是一样的：都会增加数据的负载、降低数据库的响应速度，有些慢 SQL 还可能导致数据库的锁被占用，从而影响到整个数据库的性能。

所以一般情况下，我们要定期检查数据库中的慢 SQL。

“如何检测分析慢 SQL” 也是一道经典的面试题了，对于 MySQL 来说，可以分析 MySQL 自带的慢查询日志、通过 Explain 查看 SQL 的执行计划、通过 Profiling 分析 MySQL 查询的详细执行信息等。

不过俗话说得好，面试造火箭，工作拧螺丝。

一般有些规模的公司都会有自己的慢 SQL 收集分析平台，哪还需要自己敲命令呢？像我们鱼厂虽然是小公司，也可以用大公司提供的云服务呀！慢日志监控、告警、甚至是优化建议，都帮忙做好了，直接使用美滋滋~

下面就以这句最大执行了 600 多秒的坑爹小 SQL 为例，简单聊一下怎么优化。

这条 SQL 的作用是查询指定权限的用户在某段时间内发送的消息记录。由于消息记录表（message_record）和用户表（user）的数据量都非常大，再加上有关联查询，导致查询缓慢。

如图，执行 600 多秒的 SQL 扫描了 600 多万行数据：

这句 SQL 怎么优化呢？

先看这句 SQL 的写法，其实没有什么大问题。虽然确实可以做一些小的改动，比如把 left join 改成 inner join、或者用用子查询之类的，但是优化效果并不明显。

那下一步，就是掏出数据库优化大杀器 —— 索引，从相关面试题的数量，就已经能感受到索引的重要性了。

其实我用的数据库监控平台已经给出了优化建议，正是添加索引：

添加索引最简单的原则就是：需要根据什么字段查询 / 排序 / 连接 / 分组，就可以考虑增加索引。当然，具体情况具体分析，索引的选择需要根据查询条件、表的大小、数据分布等多方面考虑，比如字段值是唯一的、或者值比较分散的字段更适合加索引，而不建议给性别这种值区分度不高的字段增加索引。

所以对于这句慢 SQL，我选择给 message_record 表的 createTime 字段增加索引，然后使用 Explain 语句查看执行计划，会利用到新增的索引：

该加的索引已经加了，但因为主表和子表的数据量就是比较大，查询还是巨慢，怎么办呢？这种情况下，可以先思考能否 先通过业务手段解决 。既然优化不了它，那就干掉它！思考：这句 SQL 能不能不执行？为什么要同时查出这么多用户的消息记录？是不是需求本身就不合理？

如果你把产品经理说服了，OK，愉快摸鱼。

但如果产品经理把你说服了，好吧，那就再考虑下 能不能通过程序优化 ？比如将查询进行拆分，按照更精确的日期（比如 “天”）多次查询消息、或者指定用户 id 来避免联表查询，并且通过多线程同时并发执行查询。当然这样是否有效还是需要验证的。

还有其他的方法：

1）如果查询是需要频繁（或实时）触发的，可以使用 Redis 或内存来缓存数据，避免重复查库。

2）如果查询是定时触发的，可以考虑降低定时任务的执行频率，并且尽量错峰查询，避免这种慢 SQL 在用户使用高峰期执行。

大家看图也能够发现，我们的慢 SQL 主要就是在凌晨出现的，这是由于凌晨用户比较少，我们选择在这个时间点执行一些比较重的定时任务。

如果无法通过程序优化，那么就从数据库下手吧，比如通过分库分表，将消息记录表按天拆分成多个表，提高单表查询效率；或者将已经被逻辑删除的消息移动到另一张表中进行备份，而不是继续存在于主表中，跟清空电脑上的回收站是类似的道理。但分库分表的代价就是有额外的实现成本和维护成本，建议还是慎用。

还有其他的优化方法，比如升级数据库的配置，甚至是更换一个查询性能更高的数据库（比如适用于大数据分析的 ClickHouse），不过成本有点高，反正对于我们小公司百万量级的数据量来说，还没必要考虑。