MySQL慢查询定位实战从explain到performance_schema全工具链解析

那天深夜两点，生产环境的监控警报把我和团队从睡梦中惊醒。核心订单查询接口的响应时间从平时的200毫秒飙升到了8秒，P99延迟直接拉满。用户端开始疯狂投诉，客服群里全是“为什么我的订单查不到”的消息。

这种时候，慌是没有用的。作为 DBA 和后端开发的“救火队员”，我经历过太多次这样的场景。今天我想和你聊聊，当我们面对 MySQL 慢查询这个“老大难”问题时，应该如何构建一套完整、系统的排查工具链。这不是一篇枯燥的教科书，而是我踩过的坑、熬过的夜换来的实战经验。

第一步：先看见问题——慢查询日志与日志分析工具

很多同学在排查问题时，第一步就错了。他们直接登录到数据库，开始 SHOW PROCESSLIST，然后盯着那些长事务发呆。但问题是，等我们发现查询慢的时候，请求可能已经处理完了，或者根本不知道是哪个 SQL 慢。

所以，先记录，再分析，这是铁律。

开启慢查询日志

MySQL 自带的慢查询日志（slow query log）是最基础也最重要的工具。你需要确认它是否开启，以及配置是否合理。

-- 查看当前慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

这里的 long_query_time 设置很关键。我见过很多同学设为 0 或 1，结果日志量大到爆炸，根本没法分析。我的建议是：

开发/测试环境：设为 0.1 或 0.5，尽量多捕获，方便开发阶段发现问题
生产环境：设为 1-3 秒，只记录真正影响用户体验的慢查询
核心链路：可以单独配置一个慢查询日志文件，便于监控和告警

分析慢查询日志——不要用手读

慢查询日志是纯文本文件，随着时间推移会变得非常大。用手去读？别开玩笑了。你需要的是工具。

mysqldumpslow 是 MySQL 自带的分析工具，虽然简陋但够基础：

# 按查询时间排序，查看最慢的20条
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 20 /var/log/mysql/slow.log

# 按返回记录数排序
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

但说实话，mysqldumpslow 的输出格式不太友好。我更推荐 pt-query-digest，它是 Percona Toolkit 的一员，功能强大得多：

# 分析慢查询日志，生成详细报告
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 只看最近1小时的慢查询
pt-query-digest --since 1h /var/log/mysql/slow.log

# 分析特定用户或库的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ /.*order.*/' /var/log/mysql/slow.log

# 输出到文件并按响应时间分组
pt-query-digest --output file --group-by query_time slow.log

pt-query-digest 会给你一个非常清晰的报告，包括：

Top 10 最耗时的查询
查询执行的频次分布
查询的平均、最小、最大执行时间
查询的指纹（标准化后的 SQL）
是否使用索引
扫描的行数

记得有一次，我们通过 pt-query-digest 发现某个 SQL 虽然只执行了10次，但每次都要扫描100万行数据，总响应时间占了整个慢查询日志的 80%。这就是它的威力——让你快速定位到那个“罪魁祸首”。

第二步：看懂执行计划——EXPLAIN 的艺术

找到了慢查询，下一步就是分析为什么慢。这时候，EXPLAIN 就是你的 X 光机。

基础用法

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 2 ORDER BY create_time DESC LIMIT 10;

输出结果有十几个字段，但作为实战派，你只需要关注几个核心字段：

字段	作用	重点关注
`id`	查询识别符	相同id表示同时执行，不同id有执行顺序
`select_type`	查询类型	SIMPLE/PRIMARY/SUBQUERY/UNION 等
`table`	表名	被查询的表
`partitions`	匹配的分区	分区表时关注
`type`	访问类型	最关键！从好到差：system > const > eq_ref > ref > range > index > ALL
`possible_keys`	可能的索引	优化器认为可能使用的索引
`key`	实际使用的索引	如果为NULL，说明没用到索引
`key_len`	索引使用的字节数	判断复合索引用了哪些列
`ref`	索引引用	显示哪一列或常量被用于索引
`rows`	预估扫描行数	越小说明效率越高
`filtered`	过滤比例	表示储存引擎返回的记录中，能满足条件的比例
`Extra`	额外信息	Using filesort、Using temporary 等需要警惕

type 字段——访问类型的等级制度

这是 EXPLAIN 中最核心的部分。我来给你详细拆解一下各种 type 的含义，以及它们代表的性能差异：

system > const > eq_ref > ref > range > index > ALL

system：表只有一行记录（系统表），这是最快的情况，几乎不会在生产环境出现。
const：表最多有一行匹配记录，常用于主键或唯一索引查询。例如 SELECT * FROM users WHERE id = 1。
eq_ref：唯一索引扫描，对于每个索引键，表中只有一行记录匹配。常见于 JOIN 查询中，被驱动表使用主键或唯一索引。
ref：非唯一索引扫描，返回匹配某个单独值的所有行。例如 SELECT * FROM orders WHERE user_id = 123。
range：索引范围扫描，常见于 BETWEEN、>、<、IN 等查询。
index：全索引树扫描，比 ALL 快，但依然慢。
ALL：全表扫描，最慢的情况，必须避免。

记得有一次排查，一个 SQL 的 type 是 ALL，扫描了 500 万行数据，加了索引后变成 range，只扫描了几百行，查询时间从 15 秒降到 0.05 秒。这个案例经常被我在面试中用来考察候选人对索引的理解。

Extra 字段——隐藏的信息

Extra 字段经常包含关键信息，其中有一些是“危险信号”：

Using filesort：需要额外的排序操作，MySQL 无法利用索引完成排序。这是性能杀手之一。
Using temporary：使用了临时表，通常出现在 GROUP BY 或 DISTINCT 查询中。
Using index：覆盖索引，非常好的信号，说明查询完全通过索引完成，不需要回表。
Using where：在存储引擎层进行了过滤，而不是在服务器层。
Using index condition：索引下推（ICP），MySQL 5.6+ 的特性，可以优化性能。

-- 示例：查看 Extra 字段的含义
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND create_time > '2024-01-01';

输出中如果看到 Using where; Using index，说明查询使用了索引下推，并且可能使用了覆盖索引，这是非常好的情况。

深入理解 rows 和 filtered

rows 是优化器估算的需要扫描的行数，filtered 是 WHERE 条件过滤的比例。两者的乘积大致等于实际需要处理的行数。

实际处理行数 ≈ rows × filtered%

如果 rows 很大但 filtered 很小，说明索引选择可能有问题，优化器选了一个不合适的索引。这时候你需要考虑是否需要调整索引，或者使用 FORCE INDEX 来强制使用某个索引。

第三步：深入细节——EXPLAIN ANALYZE 和追踪工具

传统的 EXPLAIN 只能告诉你优化器的“想法”，但实际执行可能有所不同。MySQL 8.0 引入了 EXPLAIN ANALYZE，它能让你看到查询的实际执行过程。

EXPLAIN ANALYZE——看见真实的执行路径

EXPLAIN ANALYZE SELECT * FROM orders 
WHERE user_id = 123 
AND status IN (1, 2, 3) 
ORDER BY create_time DESC 
LIMIT 10;

输出会包含实际执行的详细信息：

每个节点实际扫描的行数
实际花费的时间
实际返回的行数
循环次数

这能帮你发现优化器的估算误差。比如优化器认为只扫描 100 行，但实际扫描了 100 万行，这说明统计信息可能过期了，需要 ANALYZE TABLE 更新统计信息。

Trace 工具——深入优化器内部

MySQL 提供了一个非常强大的 trace 工具，可以详细记录优化器的决策过程：

-- 启用 trace
SET SESSION optimizer_trace="enabled=on";
SET SESSION optimizer_trace_max_mem_size=1048576;

-- 执行查询
SELECT * FROM orders WHERE user_id = 123 AND status = 2;

-- 查看 trace 结果
SELECT * FROM information_schema.optimizer_trace;

-- 关闭 trace
SET SESSION optimizer_trace="enabled=off";

trace 输出是一个 JSON 文件，详细记录了：

优化器考虑的每个索引
每个索引的成本估算
最终选择的执行计划及原因
各种改写策略的评估

这个工具非常强大，但输出也很复杂。我建议你在遇到难以理解的优化器行为时使用它。比如，为什么优化器选择了错误的索引？为什么没有使用索引？这些问题可以通过 trace 找到答案。

记得有一次，一个查询在开发环境跑得很快，到生产环境突然变慢。通过 trace，我发现生产环境的统计信息过期了，优化器估算的行数和实际相差了 100 倍。更新统计信息后，问题解决了。

第四步：实时监控——Performance Schema 的力量

前面的工具都是事后分析，Performance Schema 是 MySQL 提供的实时监控工具。它可以让你在问题发生时，捕获详细的性能数据。

Performance Schema 的核心对象

Performance Schema 提供了大量的度量指标，主要集中在以下几个方面：

事件等待：查询、锁、IO、网络等等待事件
阶段：查询执行的各个阶段
行事件：表的行操作（SELECT、INSERT、UPDATE、DELETE）
内存使用：各线程和语句的内存分配
事务：事务的执行情况

启用 Performance Schema

-- 查看 Performance Schema 是否启用
SHOW VARIABLES LIKE 'performance_schema';

-- 如果需要启用，需要在 my.cnf 中配置
-- performance_schema = ON

-- 查看当前的 instrumentation 配置
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%statement%';

-- 启用 statement 相关的 instrumentation
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement%';

查询当前正在执行的 SQL

-- 查看当前正在执行的 SQL
SELECT 
    THREAD_ID,
    EVENT_ID,
    START_TIME,
    STATE,
    TIMER_WAIT/1000000000000 AS duration_sec,
    SQL_TEXT
FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL
ORDER BY START_TIME;

这个查询能告诉你此刻有哪些 SQL 在执行，执行了多久，状态是什么。对于定位实时慢查询非常有用。

历史查询统计

-- 查看过去一段时间内的语句统计
SELECT 
    DIGEST_TEXT AS query_sample,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
    AVG_TIMER_WAIT/1000000000000 AS avg_time_sec,
    MAX_TIMER_WAIT/1000000000000 AS max_time_sec,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_time_sec DESC
LIMIT 20;

这个查询按总执行时间排序，能帮你找到过去一段时间内最耗资源的 SQL。注意 DIGEST_TEXT 是 SQL 的指纹，相同模式的 SQL 会被聚合在一起。

锁等待分析

锁问题往往是慢查询的元凶之一。Performance Schema 可以帮助你分析锁等待：

-- 查看当前的锁等待
SELECT 
    WAITING_ENGINE_EVENT_ID,
    WAITING_THREAD_ID,
    WAITING_EVENT_NAME,
    WAITING_SEXUALITY,
    BLOCKING_ENGINE_EVENT_ID,
    BLOCKING_THREAD_ID,
    BLOCKING_EVENT_NAME
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'WAITING';

-- 或者使用 sys  schema 的视图（更友好）
SELECT * FROM sys.innodb_lock_waits;

sys schema——Performance Schema 的友好封装

直接查询 Performance Schema 的表非常痛苦，表结构复杂，字段命名不直观。MySQL 提供了 sys schema，它是一组视图和存储过程的集合，专门用于简化 Performance Schema 数据的查询。

-- 查看最耗时的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer DESC
LIMIT 10;

-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_sorting
ORDER BY sort_rows DESC
LIMIT 10;

-- 查看使用临时表的 SQL
SELECT * FROM sys.statements_with_temp_tables
ORDER BY temp_tables DESC
LIMIT 10;

sys schema 的视图非常易用，推荐你将它作为 Performance Schema 的主要交互接口。

第五步：高级工具——mk-query-digest 和 pt-summary

除了前面提到的 pt-query-digest，Percona Toolkit 还提供了一些其他非常有用的工具。

pt-summary——系统性能概览

pt-summary

这个工具会输出服务器的 CPU、内存、磁盘、网络、MySQL 等关键指标的快照。当你登录一台服务器，不知道从哪里开始时，先运行 pt-summary，它能给你一个全局视角。

mk-query-digest——慢查询日志的深度分析

# 按查询类型分组
mk-query-digest --group-by fingerprint slow.log

# 按 URL 分组（针对 Web 应用）
mk-query-digest --group-by url slow.log

# 生成 HTML 报告
mk-query-digest --output=html slow.log > report.html

mk-query-digest 和 pt-query-digest 功能类似，但 mk-query-digest 更灵活，支持更多的分组和分析维度。

实战案例：一次完整的慢查询排查

让我给你一个真实的案例，把前面讲的所有工具串联起来。

背景

某电商平台的商品搜索接口在高峰时段响应缓慢，P99 延迟超过 5 秒。用户反馈搜索体验很差，客服压力巨大。

排查过程

第一步：确认问题

我们首先通过监控系统确认了问题的范围。发现只有特定条件下的搜索查询慢，其他查询正常。这排除了整体性能问题的可能性。

第二步：捕获慢查询

-- 检查慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query%';

-- 开启慢查询日志，阈值设为 2 秒
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

等待了 10 分钟，收集到了一批慢查询日志。

第三步：分析慢查询日志

# 使用 pt-query-digest 分析
pt-query-digest --since 10m /var/log/mysql/slow.log > analysis.txt

分析结果显示，慢查询主要集中在一条 SQL：

SELECT p.*, c.name as category_name, b.name as brand_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
WHERE p.status = 1
AND (p.name LIKE '%手机%' OR p.description LIKE '%手机%')
AND p.price BETWEEN 1000 AND 5000
ORDER BY p.sales DESC
LIMIT 20 OFFSET 0;

第四步：EXPLAIN 分析

EXPLAIN SELECT p.*, c.name as category_name, b.name as brand_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
WHERE p.status = 1
AND (p.name LIKE '%手机%' OR p.description LIKE '%手机%')
AND p.price BETWEEN 1000 AND 5000
ORDER BY p.sales DESC
LIMIT 20 OFFSET 0;

EXPLAIN 结果显示：

type 为 ALL，全表扫描 products 表（500 万行）
key 为 NULL，没有使用索引
Extra 为 Using where; Using filesort

问题很明显：LIKE ‘%手机%’ 这种通配符在前面的查询无法使用索引，导致全表扫描。

第五步：使用 Performance Schema 确认

”`sql – 查看该查询的实际执行情况 SELECT

DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT/1000000000000 AS total_seconds,
AVG_TIMER_WAIT/10000

正文

MySQL慢查询定位实战从explain到performance_schema全工具链解析

第一步：先看见问题——慢查询日志与日志分析工具

开启慢查询日志

分析慢查询日志——不要用手读

第二步：看懂执行计划——EXPLAIN 的艺术

基础用法

type 字段——访问类型的等级制度

Extra 字段——隐藏的信息

深入理解 rows 和 filtered

第三步：深入细节——EXPLAIN ANALYZE 和追踪工具

EXPLAIN ANALYZE——看见真实的执行路径

Trace 工具——深入优化器内部

第四步：实时监控——Performance Schema 的力量

Performance Schema 的核心对象

启用 Performance Schema

查询当前正在执行的 SQL

历史查询统计

锁等待分析

sys schema——Performance Schema 的友好封装

第五步：高级工具——mk-query-digest 和 pt-summary

pt-summary——系统性能概览

mk-query-digest——慢查询日志的深度分析

实战案例：一次完整的慢查询排查

背景

排查过程

相关阅读

揭秘：全球热门二手进口工具批发市场，教你如何挑选性价比之王

鹿邑租借工具，便捷生活必备，周边哪里找？

雪佛兰车主必备：盘点那些实用又好用的配套工具及使用技巧

雪佛兰车主必看：车辆工具存放指南，轻松找到实用工具

雪佛兰赛欧车主必备：全面工具盘点，轻松应对日常维修

告别卡顿！揭秘MySQL数据库性能监控，告别卡顿，轻松提升效率

轻松管理团队文档，这些Markdown协作工具让你工作效率翻倍

电脑加速大揭秘：五大实用Windows性能监控工具推荐

MongoDB数据库监控：全方位守护数据安全与性能优化指南

SQLite数据库迁移工具：轻松实现数据库版本控制与升级指南