MySQL 慢查询怎么排查？

面试考察点

此题主要考察你系统性解决问题的能力和对数据库性能调优的知识储备。核心考察点包括：

你是否具备一套清晰的、标准化的排查思路：面试官想看到你面对问题时的逻辑性和条理性，而不是零散的知识点。
你对 MySQL 核心工具和特性的理解深度：是否真正理解并会使用慢查询日志、执行计划 (EXPLAIN)、性能模式 (Performance Schema) 等核心诊断工具。
你能否解读执行计划并定位性能瓶颈：这是关键能力。能否从 EXPLAIN 的输出中，精准判断出是全表扫描、索引使用不当、还是连接方式有问题。
你是否能将理论应用于实践，并提出有效的优化建议：发现问题后，能否给出具体的、可行的优化方案（如索引优化、SQL 重写）。
你是否了解生产环境的最佳实践：例如，如何安全地在生产环境开启慢查询日志，如何使用专业分析工具等。

核心答案

排查 MySQL 慢查询，一个标准、高效的流程可以概括为以下四步：

开启与收集：在 MySQL 中配置并开启 慢查询日志，让 MySQL 自动记录所有执行时间超过指定阈值的 SQL 语句。
分析与定位：使用工具（如 mysqldumpslow, pt-query-digest）分析慢查询日志，找出执行最慢、最频繁的 “罪魁祸首” SQL。
诊断与剖析：对目标 SQL 使用 EXPLAIN 命令（或 EXPLAIN FORMAT=JSON）分析其 执行计划，核心关注 type, key, rows, Extra 等字段，定位瓶颈所在（如是否全表扫描、索引是否失效）。
优化与验证：根据诊断结果进行针对性优化（如优化索引、重写 SQL、调整表结构），然后再次使用 EXPLAIN 或实际执行来验证优化效果。

深度解析

原理/机制

慢查询日志：MySQL 内置的功能。当一条 SQL 的执行时间超过 long_query_time 参数设定的值（默认 10 秒），并且满足其他条件（如 min_examined_row_limit）时，MySQL 会将该 SQL 的相关信息（执行时间、锁等待时间、扫描行数等）记录到指定日志文件中。它是发现问题的 “雷达”。
执行计划 (EXPLAIN)：EXPLAIN 展示了 MySQL 优化器 打算如何执行一条 SQL 语句。它并不真正执行 SQL，而是通过解析和优化，输出一个访问路径的蓝图。读懂这个蓝图，就能预判 SQL 的性能。核心字段含义如下表：

字段	关键解读
`type`	访问类型，性能核心。从优到劣常见的有：`const` > `eq_ref` > `ref` > `range` > `index` > `ALL`（全表扫描，需重点优化）。
`key`	实际使用的索引。如果为 `NULL`，则未使用索引。
`rows`	MySQL 估算需要扫描的行数。值越小越好。
`Extra`	额外信息，富含优化线索。如出现 `Using filesort`（文件排序）、`Using temporary`（使用临时表），通常意味着性能开销大。

最佳实践与排查步骤详解

步骤一：开启慢查询日志

-- 1. 查看当前慢查询配置（通常动态设置，重启失效）
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 2. 在会话或全局动态开启（生产环境慎用，对性能有轻微影响）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 将慢查询阈值设为2秒，可根据业务调整
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log'; -- 指定日志文件路径

-- 3. 更推荐在配置文件 my.cnf/my.ini 中永久配置
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/lib/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1 -- （可选）记录未使用索引的查询

步骤二：使用工具分析慢日志 直接阅读原始日志效率低下。

内置工具：mysqldumpslow（MySQL 自带，基础统计）

# 得到返回记录集最多的10条SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
# 得到访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

推荐专业工具：pt-query-digest（Percona Toolkit 组件）。它提供极其详细的分析报告，包括执行时间分布、表扫描统计等，是 事实上的行业标准。
```
pt-query-digest /var/lib/mysql/slow.log > slow_analysis_report.txt
```

步骤三：解读执行计划 (EXPLAIN) 示例 假设通过日志找到一条慢 SQL：

SELECT * FROM `user` WHERE `age` BETWEEN 20 AND 25 ORDER BY `create_time` DESC;

使用 EXPLAIN 诊断：

EXPLAIN SELECT * FROM `user` WHERE `age` BETWEEN 20 AND 25 ORDER BY `create_time` DESC;

可能输出（简化）：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	user	ALL	idx_age	NULL	10000	Using where; Using filesort

诊断：

type: ALL：最差情况，进行了全表扫描。
key: NULL：虽然可能有 idx_age 索引，但优化器未使用。
Extra: Using filesort：在磁盘或内存中进行了昂贵的排序操作。
rows: 10000：预估扫描了 1 万行。

步骤四：优化与验证 基于诊断，优化方案可能是创建一个复合索引来同时满足查询和排序：

CREATE INDEX idx_age_create_time ON `user`(`age`, `create_time` DESC);

再次使用 EXPLAIN 验证：

EXPLAIN SELECT * FROM `user` WHERE `age` BETWEEN 20 AND 25 ORDER BY `create_time` DESC;

输出可能变为：

type	key	rows	Extra
range	idx_age_create_time	500	Using index condition

优化成功！访问类型变为高效的 range（范围扫描），使用了新索引，并消除了 Using filesort。

常见误区

盲目添加索引：索引不是越多越好。索引会降低写操作（INSERT/UPDATE/DELETE）速度并占用空间。优化前必须通过 EXPLAIN 确认瓶颈。
只关注 long_query_time：一些执行很快但调用极其频繁的 SQL，也可能拖垮系统。需要结合 pt-query-digest 分析总耗时（Query_time distribution）。
忽视 EXPLAIN 的局限性：EXPLAIN 是“估算”，其 rows 字段可能与实际偏差较大。在优化后，一定要用真实数据测试执行速度。
忘记联系业务上下文：最好的优化有时是业务逻辑优化。例如，是否真的需要 SELECT *？查询频率能否降低？数据能否归档？

总结

排查 MySQL 慢查询，本质是遵循 “监控收集 -> 分析定位 -> 深度诊断 -> 优化验证” 的系统化方法，核心利器是 慢查询日志 和 EXPLAIN 执行计划分析，而 pt-query-digest 这样的专业工具能极大提升排查效率。

发布于2025年12月28日|22:04:21