MySQL 慢查询日志:从开参数到定位根因

慢日志这件事,很多人停在"把参数打开"这一步。参数打开只是拿到了数据,真正难的是从几万行日志里判断出"该动哪一条 SQL、动哪里"。工具就是干这个的。

但在讲工具之前,得先说清楚慢日志到底记了什么、哪些字段有用。不然工具输出一堆数字,你还是不知道看哪个。

一、参数:先拿到干净的数据

一个能用于分析的配置大致是这样:

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/data/mysql/slow.log';
SET GLOBAL long_query_time = 1;              -- 单位秒,支持小数如 0.1
SET GLOBAL min_examined_row_limit = 100;     -- 扫描行数不足 100 的不记
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_throttle_queries_not_using_indexes = 10;  -- 同一类未走索引 SQL 每分钟最多记 10 条
SET GLOBAL log_slow_admin_statements = ON;   -- 记录 ALTER / OPTIMIZE / ANALYZE
SET GLOBAL log_slow_extra = ON;              -- 8.0.14+,关键

几个容易踩的点:

long_query_time 只对新连接生效。 改完之后当前 session 还是旧值,用 SHOW GLOBAL VARIABLES LIKE 'long_query_time' 看到的是全局值,但你自己这个连接跑 SQL 仍按旧的算。验证要新开一个窗口。

long_query_time 最小值是 0,设成 0 会记录所有语句(包括 SELECT 1),线上绝不要这么干,日志几分钟就能吃掉几个 G。

log_queries_not_using_indexes 单独打开很危险。 它会记录所有没走索引的语句,包括那种"表只有 200 行、全表扫描比走索引还快"的查询。业务一有定时任务扫小表,日志立刻暴涨。所以它必须和 log_throttle_queries_not_using_indexes 配对使用,再加 min_examined_row_limit 过滤掉小表。

log_output 可以是 FILE、TABLE 或 FILE,TABLE。TABLE 模式下写入 mysql.slow_log(CSV 引擎),可以用 SQL 直接查,但 CSV 引擎没有索引、并发写入有锁竞争,高并发下会拖慢实例。线上建议只写 FILE,需要分析时再导入。

轮转需要 FLUSH SLOW LOGS(或 mysqladmin flush-logs),否则 logrotate 之后 mysqld 还握着旧文件句柄,新日志继续写进被重命名的文件里。

二、一条记录的字段,以及哪些字段真正有用

开了 log_slow_extra=ON 之后,8.0 的一条慢日志长这样:

# Time: 2026-11-19T21:03:12.481920+08:00
# User@Host: app[app] @ 10.0.3.21 []  Id: 88213
# Query_time: 2.184203  Lock_time: 0.000091 Rows_sent: 20  Rows_examined: 98432
# Bytes_received: 312  Bytes_sent: 2480
# Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 7864320
# Sort_scan: 1  Sort_rows: 98432  Sort_merge_passes: 2
# Innodb_rows_read: 98432  Innodb_buffer_pool_reads: 9410
SET timestamp=1763557392;
SELECT id, order_no, amount FROM orders
 WHERE shop_id = 1024 AND status = 3
 ORDER BY created_at DESC LIMIT 20;

绝大多数人只看 Query_time。真正决定怎么优化的是后面这几个:

字段 判断什么
Rows_examined / Rows_sent 扫描放大倍数,索引好坏的核心指标
Lock_time 慢在锁等待还是慢在 SQL 本身
Tmp_disk_tables 排序/分组溢出到磁盘,需要调 sort_buffer_size 或改 SQL
Sort_merge_passes 归并排序轮次,>0 说明排序缓冲区不够
Innodb_buffer_pool_reads 物理读次数,占比高说明缓存命中差或数据量超内存
Bytes_sent 返回给客户端的数据量,网络慢/大结果集的问题

上面这条:Query_time 2.18 秒,Lock_time 0.00009 秒——不是锁的问题。Rows_examined 98432,Rows_sent 只有 20,放大 4921 倍。Tmp_disk_tables=1、Sort_merge_passes=2——order by 走了磁盘临时文件。结论很明确:(shop_id, status, created_at) 复合索引缺失,加完这个索引两个问题一起解决。

这三个字段摆在一起,比看十遍 Query_time 有用。

三、从慢日志到根因的判定路径

工具输出再花哨,判断逻辑是固定的。拿到一条 TOP SQL,按这个顺序过一遍:

先分流"等得慢"和"算得慢",再按扫描放大倍数分级。 只有两个判断点,但顺序不能反——顺序反了会去优化一条压根不缺索引、只是在等锁的 SQL。

这张图画的就是这两个判断点,点任意一张卡片可以展开那一类该怎么查:

→ 打开交互图谱:从一条 SQL 到根因

第一步永远是分流:这条 SQL 是"算得慢"还是"等得慢"。Lock_time 占 Query_time 超过 30%,就不该在 SQL 优化上耗时间,去看 information_schema.innodb_trx、performance_schema.data_lock_waits,是事务没提交、还是并发更新同一行。

确认是"算得慢"之后,看扫描放大倍数。经验阈值:

  • < 10:索引基本没问题,慢在数据量或计算(聚合、临时表、函数运算)
  • 10 ~ 100:可疑,检查是否有索引但选择性差、或者用了范围条件导致后续列失效
  • > 100:几乎必然是索引缺失或索引失效
  • > 1000:灾难级,通常伴随全表扫描 + 排序

Rows_examined 还有一个隐藏信息:如果它恰好等于表的行数(或非常接近),那就是全表扫描,不用再 EXPLAIN 确认了。

四、工具:四层,从够用到好用

第一层:mysqldumpslow(MySQL 自带)

零依赖,装完 MySQL 就有。作用是聚合——把参数不同但结构相同的 SQL 归成一类。

mysqldumpslow -s t -t 10 /data/mysql/slow.log
Count: 1200  Time=2.34s (2808s)  Lock=0.00s (0s)  Rows=10.0 (12000), app[app]@localhost
  SELECT id, order_no, amount FROM orders WHERE shop_id=N AND status=N ORDER BY created_at DESC LIMIT N
  • -s t 按总耗时排(at 平均耗时、c 次数、r 总返回行数、l 锁时间)
  • -t 10 只显示前 10
  • -g "pattern" 只处理匹配的语句
  • -a 不把数字抽象成 N(看具体参数用)

缺点很明显:它的"聚合"只是把数字替换成 N,正则粗糙,IN (1,2,3) 和 IN (1,2) 会被当成两条;不支持按时间窗口过滤;不支持同比。应急时够用,日常分析不够。

第二层:pt-query-digest(Percona Toolkit,事实标准)

这是真正能干活的工具。安装:

yum install percona-toolkit        # CentOS/RHEL
apt install percona-toolkit        # Debian/Ubuntu

基本用法:

pt-query-digest /data/mysql/slow.log > report.txt

输出分三段,每一段都有用。

第一段:总体画像

# Overall: 1.15k total, 23 unique, 0.00 QPS, 0.01x concurrency ______________
# Time range: 2026-11-19T20:00:00 to 2026-11-19T21:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1204s    120ms     32s    1.02s    4.10s   1.80s   680ms
# Lock time            12s       0     2.10s     10ms     60ms    80ms     1ms
# Rows sent          1.20M       0     1.00k   1.04k   2.00k   1.20k   800
# Rows examine      98.50M       0    98.45k  85.62k  98.00k   9.10k  96.00k
# Query size        12.30M      60     1.20k  10.94k  12.00k   0.80k  11.00k

1.15k total, 23 unique —— 1150 次执行,只有 23 类 SQL。这个比值是排查效率的入口:unique 数量少,说明问题集中,逐个解决就行;unique 上千,说明是广泛性的缺索引,得先看业务代码。

0.01x concurrency 说明几乎没并发,慢是纯 SQL 慢,不是排队。

第二段:Profile 排行

# Profile
# Rank Query ID           Response time    Calls R/Call  V/M   Item
# ==== ================== ================ ===== ======= ===== ==================
#    1 0x8A3F2C1D4E5B6A7  600.0000  49.8%  1000  0.6000  0.10 SELECT orders
#    2 0x1B9E4D7F2A8C3E5  380.0000  31.6%   120  3.1667  0.92 SELECT users
#    3 0x5C2A8F1E3D9B4A6  120.0000   9.9%     4 30.0000  0.05 UPDATE inventory

这一栏要看的不是"谁最慢",而是 Response time 的总量占比。

排名第 1 的 SQL 单次只有 0.6 秒,看起来毫无威胁,但它跑了 1000 次,总共吃掉 600 秒,占总响应时间的一半。排名第 3 的单次 30 秒,看着很吓人,但只有 4 次,总共 120 秒。

优化 ROI 在第 1 条,不在第 3 条。 这是慢日志分析最反直觉、也最容易搞错的一点——人本能地盯着最慢的那条。

V/M(方差/均值)是 pt-query-digest 最有价值的独有指标:

  • < 0.5:耗时很稳定,问题在 SQL 结构本身
  • 0.5 ~ 1.5:有一定波动,通常和传入参数、数据分布有关
  • > 1.5:剧烈波动,八成是执行计划漂移——同一类 SQL 有时走索引、有时走全表

V/M 大是最难查的情况,因为 EXPLAIN 一次只能看到一个计划。要复现得用具体参数去 EXPLAIN,或者去看 optimizer_trace。

第三段:单条详情

# Query 1: 0.28 QPS, 0.00x concurrency, ID 0x8A3F2C1D4E5B6A7 at byte 12483
# Scores: V/M = 0.10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ ==== ======= ======= ======= ======= ======= ======= =======
# Count         87    1000
# Exec time     49    600s    400ms      2s    600ms    900ms   100ms   580ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      1   1.20k       0       3   1.23k       3       0       1
# Rows examine  80  98.50M  90.00k  99.00k  98.50k  98.50k      0  98.50k
# Query size     1  12.30M  12.30k  12.30k  12.30k  12.30k      0  12.30k
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ##################################################################
#    1s   #############################
#  10s+

Query_time distribution 的 ASCII 直方图,一眼看出耗时分布形状。这里全挤在 100ms~2s,是典型的大结果集扫描;如果分布是"绝大多数很快、极少数极慢"的一根长尾,那问题性质完全不同(通常是缓存击穿或锁)。

底部还会给出 SHOW CREATE TABLE 和 EXPLAIN 的提示行,直接复制出来执行即可:

# Tables
#    SHOW TABLE STATUS LIKE 'orders'\G
#    SHOW CREATE TABLE `shop`.`orders`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, order_no, amount FROM orders WHERE ...\G

常用参数:

# 只看某个时间窗口(故障复盘)
pt-query-digest --since '2026-11-19 20:00:00' --until '2026-11-19 21:00:00' slow.log

# 只看某张表的 SQL
pt-query-digest --filter '$event->{arg} =~ m/orders/' slow.log

# 只看某类操作
pt-query-digest --filter '$event->{arg} =~ m/^UPDATE/i' slow.log

# 存进结果表,用于版本间对比
pt-query-digest --review h=localhost,D=perf,t=query_review slow.log

# 看历史,对比发布前后
pt-query-digest --history h=localhost,D=perf,t=query_review_history slow.log

--review 和 --history 是很少有人用但极其有用的功能。给一次索引优化做前后对比:优化前跑一次存进库,优化后再跑一次,差异一目了然,不用拿两份 txt 肉眼比对。

第三层:performance_schema + sys(比慢日志更强)

慢日志有个天生的缺陷:它按阈值取样。long_query_time=1 意味着所有 900ms 的查询都被丢掉。但一个 QPS 200、单次 900ms 的查询,危害远大于一天跑一次的 30 秒报表。

performance_schema 不看阈值,它在内存里对所有语句做 digest 聚合。8.0 上这是首选的日常巡检手段。

-- 按总耗时排 TOP 10(sys 库已经帮你格式化了)
SELECT query, exec_count, total_latency,
       avg_latency, max_latency,
       rows_examined_avg, rows_sent_avg,
       tmp_disk_tables
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

几个 sys 视图,覆盖了慢日志的几大维度:

视图 回答什么问题
sys.statement_analysis 全局排行:谁最耗时间
sys.statements_with_full_table_scans 谁在全表扫描,以及 no_index_used_pct
sys.statements_with_temp_tables 谁在用磁盘临时表
sys.statements_with_sorting 谁的排序开销大
sys.statements_with_errors_or_warnings 哪些语句在报错/警告
-- 全表扫描排行,直接给出了未走索引的比例
SELECT query, db, exec_count, total_latency,
       no_index_used_count, no_index_used_pct,
       rows_sent_avg, rows_examined_avg
FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 10;

但 sys.statement_analysis 只有 avg 和 max,没有分位数。 平均值会被长尾骗——一次 30 秒的慢查询能把平均值拉高到看不出中位数。真正的 P95/P99 要去 performance_schema.events_statements_histogram_by_digest(8.0 引入):

SELECT DIGEST, BUCKET_TIMER_HIGH,
       COUNT_BUCKET, BUCKET_QUANTILE
FROM performance_schema.events_statements_histogram_by_digest
WHERE SCHEMA_NAME = 'shop'
ORDER BY DIGEST, BUCKET_NUMBER;

BUCKET_QUANTILE 是累积分位,找到它第一次超过 0.99 的那个桶,BUCKET_TIMER_HIGH 就是 P99 的上界(单位皮秒,除以 1e12 得秒)。报性能 SLA 应该用这个数,不是平均值。

还有两个必须知道的配置:

-- 默认只保留 10000 条 digest,超出后被合并进一行 DIGEST 为 NULL 的"其他"
SHOW VARIABLES LIKE 'performance_schema_digests_size';    -- 建议调到 50000+
SHOW VARIABLES LIKE 'performance_schema_max_digest_length'; -- 默认 1024,长 SQL 会被截断

performance_schema_digests_size 不够时,问题症状很隐蔽:statement_analysis 里出现一条 query 为空的记录,占着很大比例。看到这个就要立刻调大。

reset 一台实例的统计基线:

TRUNCATE performance_schema.events_statements_summary_by_digest;

第四层:可视化与平台

PMM(Percona Monitoring and Management) —— 开源里最完整的一套。Query Analytics 模块就是 pt-query-digest 的 Web 版,带时间轴、执行计划、explain 对比。部署是 Docker 一条命令,但组件多(Grafana + VictoriaMetrics + QAN),小团队维护成本不低。

Anemometer(Box 出品) —— 老牌方案,把慢日志用 pt-query-digest 灌进 MySQL,PHP 界面按 SQL 聚合展示、支持历史趋势。优点是很轻,缺点也明显:最后活跃版本停在 2016 年左右,PHP 5 时代的东西,新环境装起来一堆兼容问题。现在更多是"知道有这么个东西、在老公司内网里见过"。

ELK / ClickHouse —— 日志量特别大(每天几十 G)时的常规做法。pt-query-digest --output json 或 --output=json 解析后灌进 ES/ClickHouse,自己在 Kibana/Grafana 里搭面板。灵活但要写不少管线代码。

云厂商 DAS —— 阿里云 DAS、腾讯云 DBbrain。自动采集慢日志、自带 SQL 诊断和索引建议,能直接给出"加这个索引预计提升 X%"。代价是绑定,且建议质量参差不齐,需要自己判断。

选型建议:单机/中小规模,pt-query-digest 定时跑 + 存 --history 表就够;有十几套实例,上 PMM;云上直接用厂商 DAS,别再自建。

五、一次完整的分析实操

场景:电商库,晚高峰订单接口超时。手上有一小时的慢日志。

第一步,摸总量。

pt-query-digest --since '2026-11-19 20:00' --until '2026-11-19 21:00' slow.log > r1.txt
head -30 r1.txt

看到 1.15k total, 23 unique,问题集中。Exec time total 1204s。

第二步,按总耗时找 ROI。

Profile 里第 1 名占 49.8%,第 2 名占 31.6%。先解决这两条,能覆盖 81% 的响应时间。第 3 名单次 30 秒但只跑了 4 次,先放着。

第三步,看 V/M 决定排查方向。

第 1 名 V/M=0.10,稳定,直接 EXPLAIN。
第 2 名 V/M=0.92,偏大,不能只 EXPLAIN 一次,得怀疑参数敏感。

第四步,看扫描放大倍数。

第 1 名:Rows_examined 98.50M / Rows sent 1.20k,平均每次扫 98k 行返回 1.2 行,放大 8 万倍。不用 EXPLAIN 也知道是索引问题。

第五步,验证并落地。

EXPLAIN SELECT id, order_no, amount FROM orders
 WHERE shop_id = 1024 AND status = 3
 ORDER BY created_at DESC LIMIT 20;
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 98432 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+

type=ALL、key=NULL、Using filesort —— 全表扫 + 额外排序,和慢日志推断完全吻合。

ALTER TABLE orders ADD INDEX idx_shop_status_created (shop_id, status, created_at);

索引顺序的依据:shop_id 和 status 是等值条件放前面,created_at 是范围/排序放最后,这样 ORDER BY created_at DESC LIMIT 20 能直接用索引顺序取前 20 行,filesort 消失。

第六步,用数据确认收益。

pt-query-digest --history h=localhost,D=perf,t=query_review_history slow_after.log

对比优化前后的 Response time 和 Rows examine,拿数字说话,而不是"感觉快了"。

六、慢日志的盲区

工具再好,慢日志本身有覆盖不到的地方,得知道边界在哪:

1. 阈值以下是黑盒。 单次 50ms 但每天跑 500 万次的查询,总耗时 70 小时,慢日志一条都不会记。这类问题只能靠 performance_schema 或 sys.statement_analysis 发现。

2. 漏掉网络传输。 MySQL 侧 Query_time 从收到完整 SQL 开始算,到最后一个包放进 socket 缓冲结束。客户端读得慢、网络差、返回 100MB 结果集,服务端可能毫秒级完成,客户端却等了几十秒。看 Bytes_sent 能发现线索。

3. 锁等待被算进 Query_time。 所以 Lock_time 那个字段一定要看,不然会把"锁冲突"误判成"SQL 写得烂",加了一堆索引毫无改善。

4. 慢日志记的是执行,不是计划。 同一类 SQL 因为参数不同走了不同计划(V/M 大的典型症状),慢日志只告诉你"这类 SQL 慢",具体是哪个参数导致的,得自己去 EXPLAIN 试,或者开 optimizer_trace 看。

5. 8.0 之前 Rows_examined 不含临时表内部行数。 5.7 上做 GROUP BY 的查询,Rows_examined 可能远小于实际工作量,别被这个数字骗了。

七、落地清单

按这个顺序做,能覆盖 90% 的场景:

  1. slow_query_log=ON + long_query_time=1 + log_slow_extra=ON,先跑一周积累数据
  2. log_queries_not_using_indexes 只在排查期开,必须配 log_throttle_queries_not_using_indexes 和 min_examined_row_limit
  3. 每天定时 pt-query-digest --history 入库,保留 30 天
  4. 排障时第一步看 Response time 占比,不看单次最慢
  5. 每条 TOP SQL 按"Lock_time 占比 → Rows_examined/Rows_sent → 临时表/排序"三步过一遍
  6. performance_schema_digests_size 调到 50000 以上,用 statement_analysis 补慢日志的阈值盲区
  7. 报 SLA 用 events_statements_histogram_by_digest 的 P99,不用平均值
  8. 每次优化后用 --history 前后对比,留数字

慢日志分析真正的门槛不在工具,在于忍住不去看最慢的那条 SQL,而是看总耗时占比最高的那条。这个判断做对了,剩下的都是执行。