线上出现慢 SQL 怎么办？仅使用 EXPLAIN 就能解决所有慢 SQL？

引言

生产环境出现慢 SQL 的危害呢？

• 吞吐量下降，延时变大。长时间占用连接池，导致客户端和 MySQL 吞吐量下降，客户端迟迟等不到响应结果
• 风险扩散。慢 SQL 将导致 CPU 飙升，影响其它所有 SQL 的执行性能

而很多时候解决线上慢 SQL 并不是一个 EXPLAIN 就能解决的。本文将从事前、事中、事后介绍，该如何解决慢 SQL

感知线上问题

监控

需要对 SQL 的执行时间进行监控，一般的方式有：

• 云产商售卖的云 MySQL，开箱即用
• Prometheus + Grafana + mysqld\_exporter + MySQL 开启慢日志配置

还需要对硬件资源进行监控，如 CPU、内存、网络、I/O

扩展：为什么需要监控？

• 对系统负载的把控。根据当前的负载情况，决定是否要扩容机器
• 对风险的感知。比如 CPU 飙升，那说明出问题了

报警

以下方面达到阈值时就报警：

• SQL 执行时间达到 x 秒。具体时间根据业务平常的 SQL 耗时决定，一般可以先设置为 1s，因为互联网场景的 SQL 最慢也是几百毫秒就能返回
• 硬件资源预警。尤其是 CPU，一般 MySQL 的 CPU 利用率不会特别高，如果 CPU 飙升到 80% 以上，极大可能是因为慢 SQL 导致的

及时止血

对慢 SQL 进行限流或拉黑，从而：

• 避免大量慢 SQL 长时间持有连接，导致吞吐量下降
• 避免进一步风险扩散，影响到其它正常的 SQL 执行

分析与解决

一条 SQL 慢还是所有 SQL 都慢

• 只是极小一部分 SQL 慢，可以从以下角度分析：

  • 索引

    • 没有索引，可以考虑加索引。不过需要注意，线上加索引，可能会导致的锁表。索引不经常被使用，也没必要加，因为会影响写入的性能
    • 加了索引，使用 EXPLAIN 查看是否有走索引

  • 深分页。特征：limit a， b。其中，a 非常的大，比如 10000。解决方案：

    • 子查询。只需修改 SQL，不需要修改业务代码
    • 游标查询。SQL 和业务代码都需要修改，但理论上讲性能最好，因为可以充分利用索引来过滤
    • 产品设计。比如，使用瀑布流评论区更好地利用游标查询；只允许查看前 5 页的内容
  • 查询的数据量大。比如做报表，或者查询用户历史记录时没用 limit 兜底。一次性查太多元素，导致全表扫描
  • 文件排序。没有索引或内存不足，就可能会使用文件排序

• 所有 SQL 都慢。考虑 QPS 压力超过当前实例的承载上限

  • 查看 CPU、内存、带宽、磁盘等资源的使用情况，对瓶颈部分升级
  • 分库分表，读写分离，扩容

EXPLAIN

查看 SQL 的执行计划，比较重要的字段有：

• type：访问类型（根据这个字段判断索引的性能），又性能排序从好到差为：

  • system：仅有一条数据（系统表）
  • const：主键或唯一索引
  • eq\_ref：唯一索引或主键连表查询
  • ref：普通索引查询（非唯一索引查询）
  • range：范围查询。如 BETWEEN 或 IN
  • index：扫描索引，一般在覆盖索引出现（也就是不需要回表直接返回索引的数据）
  • all：表示走了全表查询，未命中索引或索引失效
• possible\_keys：优化器可能会使用的索引
• key：实际使用的索引。如果为 NULL，表示没有使用到索引
• key\_len：索引字段使用的字节数（在联合索引中，可以通过 key\_len 判断使用了联合索引的几个字段）
• rows：预估要扫描的行数
• filtered：查询的条件过滤后，剩余的记录百分比。值越大越好

• extra：额外信息，描述优化器的行为

  • using index：索引覆盖。不用回表查询
  • using index condition：索引条件下推。在 server 层中，根据联合索引进一步过滤，减少回表
  • using where：在 server 层使用 where 进一步过滤数据（一般是有多个 where 条件的使用）
  • using temporary：使用了临时表处理查询结果。常见在 group by，distinct
  • using filesort：无法利用索引完成排序操作，通过文件对结果来排序。常见在 order by 使用一个非索引字段
  • using index for skip scan：跳跃扫描

加了索引，却没走索引的原因

• 索引失效（使用函数、模糊查询、最左匹配失效、涉及计算、隐式类型转换等等）

• 优化器问题。可能是只有一个索引，但就是没走；或者查询条件存在多个索引，走了其他索引

  • 如果「索引区分度低」，可以考虑删除该索引
  • 如果「索引区分度高」，但因为优化器使用的是「采样计数」，得到的「索引区分度」是不是绝对准确的，导致了优化器误判。可以 ANALYZE TABLE 手动更新统计信息，或者 FORCE INDEX 强制走某个索引
• 查询数据量太大，走索引的效率不如全表扫描

文件排序

当无法使用索引进行排序，或者数据量太大内存放不下时，MySQL 必须通过文件对结果集进行排序的操作

比如以下场景，就可能存在文件排序：

分析：created\_at 和 id 都是有索引的。我们通过 created\_at 过滤数据，但是却用 id 排序，这样无法利用到 created\_at 的索引有序性

虽然我们之前插入数据的时候，可以保证 created\_at 大的 id 一定大，但 MySQL 没这么智能。当 ORDER BY 使用的是不同索引时，MySQL 只会认为是完全无序的

解决方案

• ORDER BY id DESC 换成 ORDER BY created\_at DESC
• 加大临时表内存，确保在内存中完成排序，而不是利用磁盘文件
• 不在 SQL 中使用 ORDER BY，在 JVM 中进行排序

写入 SQL 慢还是读取 SQL 慢

• 写入 SQL，考虑「锁」的竞争。一般考虑 减少锁的使用、降低锁的持有时间、降低锁的粒度、避免死锁

  • 减少「大事务」。锁的生命周期与事务是绑定的。避免在一个事务中执行太多的写入操作，减少锁的持有时间
  • 先执行「锁竞争小」的 SQL，再执行「锁竞争大」的 SQL。这样「锁竞争大」的 SQL 锁的持有时间能稍微降低一点
• 读取 SQL 慢。考虑索引、深分页、数据量大、文件排序等问题

扩展：为什么「读已提交」的性能会比「可重复读」更好？

其实就是因为「读已提交」的「锁的粒度」更小，因为「读已提交」写入数据，一般只对该条数据加「行锁」

而对于「可重复读」，除了「行锁」，还有 gap lock 和 next-key lock，锁的是范围，这导致锁竞争更加频繁

「读已提交」追求性能会出现「不可重复读」和「幻读」问题，「可重复读」为了追求一致性导致性能差。本质还是性能和一致性之间的 trade-off

大多数情况快但有时就是会慢

• 连接池连接数小，吞吐量上不去，请求多了处理不过来。所以可以稍微加大连接池连接数

• 刷脏页，需要等待脏页刷新完毕。比如数据页刷盘、redo log 刷盘、bin log 刷盘等等

  • 加大 Buffer Pool 容量
  • 修改 redo log 和 bin log 刷盘配置

redo log 与 bin log 的“双 1”刷盘策略

“双 1”配置情况下，每次事务提交后，会有两次 IO，分别用于持久化 redo log 和 bin log 到磁盘。容易导致磁盘 IO 的瓶颈。所以可以修改 redo log 和 bin log 的配置：

• innodb\_flush\_log\_at\_trx\_commit=2，表示 redo log 写入到 page cache，由操作系统决定刷盘
• sync\_binlog=N （N 为 500 或 1000） ，表示积攒到 N 个 bin log 的时候，再统一刷盘

部署 MySQL 的机器必须配备 UPS，防止操作系统突然断电导致数据丢失

无法避免的慢 SQL

假设现在要做报表，或者运营要拉取数据分析用户行为，此时数据量一定会很大，SQL 执行一定会慢，但又无法避免。不过好在它们特点就是执行次数非常少，一天就查几次，只在内部使用，对数据实时性要求不高。所以对于这种「无法避免的慢 SQL」，可以这样优化：

• 缓存数据。正常业务我们肯定都有做缓存，不过运营查询条件可能比较刁钻，而且可能会对很久之前的历史记录分析，不一定在缓存中全部覆盖，也就是缓存命中率低，不可能避免查库
• 从业务层面做改造，比如只做简单的单表查询，在 JVM 层中 JOIN 表。不过数据量大的话，需要注意 OOM 问题。同时，查询的数据量多的话，慢 SQL 还是无法避免
• MySQL 做读写分离，甚至单独做个从库给做报表用。这样可以对正常业务几乎没有影响，缺点就是成本很高
• 定时任务在低谷期查询，做 T+1 的数据。选择条件查询后，并不是立即执行的，延后到低谷期再去执行

同时，需要注意给这类的慢 SQL，添加白名单，避免没必要的报警

结语

• 感知线上问题：监控+报警
• 及时止血：拉黑+限流

• 分析并解决：

  • 一条 SQL 慢还是所有 SQL 都慢
  • 写入慢还是读取慢
  • 偶尔会慢

其实一般线上出现慢 SQL，主要还是字段「没加索引」或者「深分页」问题，可以先从这两个角度入手

参考

https://www.nowcoder.com/discuss/730039197121155072

https://juejin.cn/post/7075548925012213790

https://juejin.cn/post/7126357291456004110

封面：anny