突然出现大量慢 SQL，从哪里开始排查？(系统层 → MySQL 层 → SQL 层)

考察要点

这道题面试官想看你是否具备体系化的故障排查能力、自顶向下的分析思维以及对数据库内外监控工具的熟练掌握。面试官期待的不是一个单一的答案（比如“看慢查询日志”），而是一个从系统层、到数据库实例层、再到具体 SQL 层的完整、有序的诊断流程，这能体现出你作为资深工程师处理线上复杂问题的综合能力。

核心概念

排查慢 SQL 问题前，需要先理解以下几个核心概念，它们是诊断问题的关键工具和信息来源。

常见低分回答

1. 直接跳入细节：“我马上用 EXPLAIN 分析一下慢 SQL”。—— 错误在于没有全局观。如果问题是系统 I/O 满了或者有锁，分析单条 SQL 的执行计划是无效的。
2. 提出破坏性操作：“我会先重启一下 MySQL 实例看看会不会好”。—— 这是最糟糕的回答。重启会丢失现场（如 processlist、锁信息、内存中的统计数据），导致问题无法根治，并且会造成业务中断。
3. 归因模糊：“可能是索引没建好”。—— 太过笼统。是哪个表的哪个字段没建索引？是索引失效了还是根本没有？没有数据支撑的猜测没有价值。
4. 工具使用单一：“我就看慢查询日志”。—— 慢查询日志是被动记录，有延迟。对于“突然出现”的“大量”慢 SQL，很可能是锁、热点更新或系统资源问题，需要 SHOW FULL PROCESSLIST 或 Performance Schema 这样的实时工具来捕捉现场。
5. 忽略应用层变化：“只在数据库层面找问题”。—— 突然出现的大量慢 SQL，很多时候是由于应用层逻辑变更、新功能上线、或者流量突增导致的。忽略了外部变量，可能永远找不到根因。

工程实践与优化

面对“突然出现大量慢 SQL”的场景，我会遵循一个清晰的、自顶向下的排查流程：

第一步：稳住局面，评估影响（1-5 分钟）

1. 确认影响范围：是影响了核心业务还是边缘业务？是全站慢还是部分接口慢？
2. 快速止损：如果核心业务受损严重，与业务方沟通，考虑是否可以执行降级、限流或熔断等临时方案，为排查争取时间。永远把服务可用性放在第一位。

第二步：系统层排查（Is the server on fire?）

目标是快速判断是否为服务器资源瓶颈。登录到数据库服务器，使用系统命令检查：

1. CPU：执行 top 或 htop。
   • %wa (I/O wait) 过高：说明 CPU 在等待磁盘 I/O，瓶颈在磁盘。可能是大量无索引查询导致的全表扫描，或磁盘本身故障。
   • %us (user space) 过高：说明是 MySQL 进程自身消耗了大量 CPU，通常是复杂的计算、排序或不优的 SQL 逻辑导致。
2. 磁盘 I/O：执行 iostat -x 1。
   • %util 接近 100%：说明磁盘设备已经饱和，I/O 请求在排队。
   • await (平均每次 I/O 操作的等待时间) 过高：说明磁盘读写非常慢。
3. 内存：执行 free -h。
   • swap 被大量使用：致命信号。说明物理内存严重不足，操作系统正在使用磁盘作为虚拟内存，性能会急剧下降。需要检查 MySQL 的 innodb_buffer_pool_size 是否设置过大，或是否有其他进程消耗内存。
4. 网络：执行 netstat -anp | grep ESTABLISHED | wc -l。
   • 连接数暴增：检查应用层的连接池配置是否合理，或者是否有大量短连接攻击。

如果系统层发现明显瓶颈，应优先解决资源问题（如磁盘问题），否则优化 SQL 意义不大。

第三步：MySQL 实例层排查（Is the database engine healthy?）

如果系统资源看起来正常，则深入到 MySQL 实例内部。

1. 查看当前活动线程：

   sql

   ⧉⛶

    1SHOW FULL PROCESSLIST;

   • 关注 State 列：寻找 Locked（等待表锁）、sending data（可能在传输大量数据或全表扫描）、Copying to tmp table（临时表操作）、Sorting result（文件排序）等状态的线程。
   • 关注 Time 列：找到执行时间非常长的线程，记录其 Info 列的 SQL 语句。
   • 关注 Command 列：如果大量线程处于 Connect 状态，可能是应用无法成功连接数据库。

2. 检查锁信息（InnoDB）：

   sql

   ⧉⛶

    1-- MySQL 8.0+
    2SELECT * FROM performance_schema.data_lock_waits;
    3
    4-- MySQL 5.7
    5SELECT * FROM information_schema.INNODB_LOCK_WAITS;

   • 如果发现有长时间的锁等待，就能找到阻塞的事务（blocking_trx_id）和被阻塞的事务。通过 information_schema.INNODB_TRX 表可以找到阻塞事务的具体 SQL，考虑是否需要 KILL 掉它。

3. 使用 Sys Schema 快速诊断 (MySQL 5.7+)：

   • 查找消耗最高的 SQL：
     sql

     ⧉⛶

      1-- 按总耗时排序，查找热点 SQL
      2SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

   • 查找全表扫描的 SQL：
     sql

     ⧉⛶

      1SELECT * FROM sys.statements_with_full_table_scans;

第四步：SQL 与 Schema 层分析（Fix the code）

通过以上步骤定位到具体的慢 SQL 后，进行深度分析和优化。

1. 获取并分析慢查询日志：

   • 使用 pt-query-digest 工具对慢查询日志文件进行归并和分析，它能生成非常专业的报告，清晰地列出“罪魁祸首”。
   bash

   ⧉⛶

    1pt-query-digest /path/to/slow-query.log > slow_query_report.txt

2. 分析执行计划：

   • 对报告中排名最前的 SQL 执行 EXPLAIN 或 EXPLAIN ANALYZE (MySQL 8.0+)。
   sql

   ⧉⛶

    1EXPLAIN SELECT ...;

   • 重点分析 EXPLAIN 输出：
     • type: 必须避免 ALL（全表扫描）。理想情况是 const, eq_ref, ref, range。
     • key: NULL 表示没有使用索引。检查是否应该使用但没用上。
     • rows: 预估扫描行数。如果此数值远大于实际返回行数，说明索引选择性差。
     • Extra: 警惕 Using filesort（无法利用索引完成排序）和 Using temporary（使用了临时表），它们是严重的性能瓶颈。Using index 是好事，表示命中了覆盖索引。

3. 优化方案：

   • 加索引：根据 WHERE、JOIN、ORDER BY、GROUP BY 的列，创建最合适的单列或联合索引。
   • 改写 SQL：避免在索引列上使用函数、类型转换；优化 JOIN 顺序；用 UNION ALL 代替 OR 等。
   • 修改表结构或业务逻辑：如果优化有限，可能需要与业务方讨论，是否可以修改业务逻辑，或者对表进行分库分表等架构调整。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，你刚才的优化方案还适用吗？性能会怎样？

   • 回答要点 1 (索引)：基于索引的优化（type 为 ref, range 等）其复杂度通常为 $O(\log N)$ 或 $O(K)$（K为结果集大小），性能下降相对平缓，仍然是有效的。而全表扫描（type 为 ALL）的复杂度为 $O(N)$，性能会线性恶化，变得完全不可接受。
   • 回答要点 2 (深度分页)：类似 LIMIT 1000000, 10 这样的深度分页问题会变得极其严重，因为它需要扫描 1000010 行数据然后丢弃前面的。优化方案需要改为基于游标的查询（WHERE id > last_id LIMIT 10），这要求前端或业务逻辑配合改造。
   • 回答要点 3 (架构)：当单表数据达到数十亿级别，即使有索引，B+树层级变深，I/O 次数增加，性能也会下降。此时需要考虑分区表（Partitioning）来分散 I/O，或者最终走向分库分表（Sharding）的分布式架构，从根本上解决单机容量和性能瓶颈。

2. 追问：你提到 EXPLAIN 看到索引失效了，一般有哪些典型场景会导致索引失效？怎么修复？

   • 回答要点 1 (函数或计算)：对索引列使用函数或进行计算。反例：WHERE DATE(create_time) = '2023-01-01'。修复：改写为范围查询 WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00'。
   • 回答要点 2 (类型不匹配)：索引列是字符串，但查询条件用了数字。反例：phone 字段是 varchar，查询 WHERE phone = 12345678901。MySQL 会将索引列进行隐式类型转换，导致索引失效。修复：WHERE phone = '12345678901'。
   • 回答要点 3 (LIKE 左模糊)：使用 LIKE '%keyword'。索引 B+ 树是按前缀有序的，无法处理左模糊。修复：如果业务允许，使用右模糊 LIKE 'keyword%'；或者使用全文索引（Full-Text Index）或引入 Elasticsearch 等外部搜索引擎。
   • 回答要-点 4 (OR 条件)：OR 连接的条件中，只要有一个列没有索引，整个查询就可能退化为全表扫描。修复：为 OR 两边的列都创建索引，或者考虑使用 UNION ALL 分别查询再合并。
   • 回答要点 5 (优化器选择)：当 MySQL 优化器估算全表扫描比走索引成本更低时（比如表很小，或者索引选择性很差），它会主动放弃索引。修复：使用 ANALYZE TABLE 更新统计信息，或者用 FORCE INDEX 强制使用指定索引（慎用）。

3. 追问：如果这套系统用的是 PostgreSQL 或者 TiDB，你的排查思路有什么不同？

   • PostgreSQL:
     • 思路共性：自顶向下（系统 -> 实例 -> SQL）的思路是通用的。
     • 工具差异：EXPLAIN (更详细，有 cost 估算) 和 EXPLAIN ANALYZE (实际执行) 是标配。慢查询日志通过 log_min_duration_statement 配置。实时诊断主要靠 pg_stat_activity 视图（类似 processlist）和 pg_stat_statements 扩展（类似 Performance Schema 的聚合报告）。锁的排查使用 pg_locks 视图。
     • 特性差异：PG 的锁机制、MVCC 实现与 MySQL 不同，排查锁问题时关注的锁类型和视图也不同。PG 拥有更丰富的索引类型（如 GIN, GiST），在排查时需要考虑是否用对了索引类型。
   • TiDB (分布式数据库):
     • 思路扩展：排查范围从单机扩展到整个集群。系统层排查需要检查所有 TiDB、TiKV、PD 节点的资源使用情况。
     • 新增排查点：
       • 网络延迟：TiDB 各组件间的网络是关键路径，需要排查网络延迟和丢包。
       • PD 调度：Placement Driver (PD) 负责集群调度，需要检查 PD 的日志和 Grafana 监控，看是否存在调度瓶颈或异常。
       • TiKV 热点：最核心的区别是排查“热点”问题。某个 TiKV 节点的 CPU 或 I/O 远高于其他节点，说明数据读写集中在该节点。需要通过 TiDB Dashboard 或 Grafana 监控定位热点表和热点 Region，并进行 SPLIT REGION 等调度优化。
     • 工具差异：EXPLAIN 的输出会包含分布式执行的信息，如 cop[tikv] 表示算子下推到 TiKV 执行。排查主要依赖官方提供的 TiDB Dashboard 和 Grafana 监控平台，它们提供了从全局到细节的图形化诊断能力，比手动敲命令更高效。