MySQL 慢日志分析实战：long_query_time 参数取舍、pt-query-digest 聚合与扫描行数比值定位

Author： zz1984
发布时间：November 11, 2026
12 views
No comments
6193 字数
Categories：网站性能调优

几乎所有教程都会告诉你「MySQL 慢日志打开就有用了」,但真正打开之后,你得到的往往是一个几百 MB 的文件,里面混着扫描 3 行的查询和扫描 300 万行的查询,加上一堆来自备份脚本和监控探针的干扰项。这篇讲的是:如何把慢日志从「一堆看不懂的记录」变成「一张能排优先级的工作清单」。

一、参数怎么设:别照抄 long_query_time=0

网上流传的「把 long_query_time 设成 0 抓全量」在生产上是灾难,它会写爆磁盘并且让 MySQL 自身的 I/O 被日志吃掉。合理的起点:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = OFF
log_slow_admin_statements = ON
log_slow_replica_statements = ON
min_examined_row_limit = 100

min_examined_row_limit = 100 是最被低估的一个参数:它把「扫了 3 行但正好花了 0.6 秒」这类因为锁等待产生的噪声过滤掉,只留下真正做了大量行扫描的查询。而 log_queries_not_using_indexes 默认关闭是对的,因为打开它会把所有小表全表扫描都记下来,几十万条记录里 99% 是无关的。

在 MySQL 8.0 上这些都可以在线改,不必重启:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
-- 生效范围是新连接,注意变更时间点方便对齐日志

二、先聚合,再看单条

直接看原始慢日志是效率最低的方式。第一步应该做聚合:同一类查询(把字面量替换成占位符后相同)出现了多少次、总耗时多少、平均耗时多少。mysqldumpslow 自带这个能力:

# 按总耗时排序取前 20
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按平均耗时排序,只看出现超过 50 次的
mysqldumpslow -s at -t 20 -n 50 /var/log/mysql/slow.log

# 只看 SELECT,且把数字和字符串归一化
mysqldumpslow -s c -t 30 /var/log/mysql/slow.log | head -100

更好的选择是 pt-query-digest(Percona Toolkit)。它给出的 Profile 表包含 Calls、R/C(相对总耗时比)、Response time、Rows examine 等列,可以一眼看出「哪几个查询吃掉了 80% 的数据库时间」。

pt-query-digest --limit 100% --order-by Query_time:sum \
  /var/log/mysql/slow.log > /tmp/slow-report.txt

# 只关心某个库
pt-query-digest --filter '$event->{db} eq "blog"' /var/log/mysql/slow.log

聚合报告里最该看的不是 Response time 绝对值,而是三个派生判断:

Calls 很高 + 平均耗时中等:典型的 N+1 或循环里查库,优化点在应用层(批量查、加缓存),不在 SQL 本身。
Calls 很低 + 单次极慢:通常是离线统计或后台报表,可以考虑挪到从库、或者加汇总表。
Calls 高 + Rows examine 远大于 Rows sent:索引问题,这是最值得立刻改的一类。

三、从 Rows examine / Rows sent 比值定位索引缺失

慢日志的每条记录都带 Rows_examined 和 Rows_sent。两者的比值直接反映选择性:

-- 取一条具体查询出来看
mysql> EXPLAIN SELECT id,title FROM posts WHERE slug='abc';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 284517 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

type=ALL 且 key=NULL 就是没有任何索引可用。rows 是优化器估算的扫描行数,284517 意味着每次请求都在翻 28 万行。这种查询在单条时不显眼(可能 80ms),但 QPS 一上来就会把 buffer pool 冲垮。

加索引时注意几个实务细节:

-- 正确:给等值查询字段建索引,注意字符集与列的实际定义一致
ALTER TABLE posts ADD INDEX idx_slug (slug), ALGORITHM=INPLACE, LOCK=NONE;

-- 复合索引遵循最左前缀:等值列在前,范围列在后
ALTER TABLE posts ADD INDEX idx_cat_status_time (category_id, status, created_at);

-- 验证:看 key 是否命中,以及 rows 是否下降
EXPLAIN SELECT id FROM posts WHERE category_id=3 AND status='publish' ORDER BY created_at DESC LIMIT 20;

ALGORITHM=INPLACE, LOCK=NONE 在 InnoDB 上加普通索引通常可以在线完成、不阻塞读写,但前提是表不要太老、不要有全文索引之类的特殊结构。加索引前务必在从库或影子库上先跑一次,记录耗时,因为 ALTER 阶段虽然不锁写,但会消耗大量 I/O,可能把同时段的正常查询拖慢。

四、那些「不是索引问题」的慢查询

把索引补齐之后仍然慢的查询,通常属于下面几类,改法完全不同:

1. 排序与临时表

EXPLAIN 里出现 Using temporary; Using filesort,说明排序无法利用索引。此时要么调整索引让 ORDER BY 能被索引顺序满足,要么接受它但想办法减少参与排序的行数(先过滤再排序)。深分页 LIMIT 100000, 20 是重灾区:

-- 慢:先取出 10 万行再丢弃
SELECT * FROM posts ORDER BY id DESC LIMIT 100000, 20;

-- 快:用上一页最后一条的 id 做游标
SELECT * FROM posts WHERE id < 881234 ORDER BY id DESC LIMIT 20;

2. 锁等待

慢日志里会出现 Lock_time 明显大于 Query_time 的十分之一的情况。这不是 SQL 写得不好,而是有长事务持着行锁。去看:

SELECT * FROM performance_schema.data_lock_waits\G
SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

如果一个事务开了 30 秒还没提交,先查应用层:是不是在事务里做了 HTTP 请求、发了邮件、或者跑了长循环。

3. 表膨胀与统计信息过期

优化器选错索引,往往因为它以为的基数不对。可以先看:

SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables WHERE table_schema='blog';
ANALYZE TABLE posts;

对于有大量删除的表,OPTIMIZE TABLE(或 ALTER TABLE ... ENGINE=InnoDB)能回收空间并整理页,但会锁表或消耗大量 I/O,安排在低峰期。

五、把慢日志变成可持续的流程

一次性分析解决不了问题,你需要的是一个轻量流程:

每日切割:用 logrotate 或 FLUSH SLOW LOGS; 每天切一个文件,避免单文件过大。切割后 pt-query-digest 更容易处理。
每日取 Top 10:固定跑一次聚合,把 Top 10 存成文本,便于跨天对比。这样能区分「新出现的慢查询」和「一直都在的慢查询」。
建立基线:记录当前的 QPS、平均响应、慢查询条数。改索引或改代码之后,用同样的口径复测,否则你无法证明优化有效。
清理噪声源:备份脚本、监控探针、批处理任务应该在慢日志里被识别并排除,比如给这些连接打上 SET SESSION long_query_time=5;,或者用 --filter 排除特定 user。

# 每日快照示例
#!/bin/sh
D=$(date +%F)
pt-query-digest --limit 1 --order-by Query_time:sum \
  /var/log/mysql/slow.log > /var/log/mysql/reports/$D.txt
grep -E '^# Query [0-9]+' -A2 /var/log/mysql/reports/$D.txt | head -60

六、一个常见的误判

很多人看到慢日志里有查询耗时 5 秒就立刻去改 SQL,但实际上这条查询可能一天只跑一次,是凌晨的统计任务。相反,真正拖垮站点的是那种每条 60ms、但每天跑 200 万次的查询——它在慢日志里只占很小的比例,却是 buffer pool 命中率下降的元凶。

所以在排优先级时,顺序应该是:总耗时贡献 > 单次耗时。用 pt-query-digest 的 Response time 排序(而不是 Query_time),先解决「吃掉 80% 时间的那 20% 查询」,再谈微优化。这个顺序搞反了,常见的结果是花了两周优化一堆冷查询,站点该慢还是慢。

Last modification：November 11th, 2026 at 12:25 pm

MySQL 慢日志分析实战：long_query_time 参数取舍、pt-query-digest 聚合与扫描行数比值定位

zz1984 • 2026 年 11 月 11 日

一、参数怎么设:别照抄 long_query_time=0

网上流传的「把 long_query_time 设成 0 抓全量」在生产上是灾难,它会写爆磁盘并且让 MySQL 自身的 I/O 被日志吃掉。合理的起点:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = OFF
log_slow_admin_statements = ON
log_slow_replica_statements = ON
min_examined_row_limit = 100

在 MySQL 8.0 上这些都可以在线改,不必重启:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
-- 生效范围是新连接,注意变更时间点方便对齐日志

二、先聚合,再看单条

# 按总耗时排序取前 20
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按平均耗时排序,只看出现超过 50 次的
mysqldumpslow -s at -t 20 -n 50 /var/log/mysql/slow.log

# 只看 SELECT,且把数字和字符串归一化
mysqldumpslow -s c -t 30 /var/log/mysql/slow.log | head -100

pt-query-digest --limit 100% --order-by Query_time:sum \
  /var/log/mysql/slow.log > /tmp/slow-report.txt

# 只关心某个库
pt-query-digest --filter '$event->{db} eq "blog"' /var/log/mysql/slow.log

聚合报告里最该看的不是 Response time 绝对值,而是三个派生判断:

Calls 很高 + 平均耗时中等:典型的 N+1 或循环里查库,优化点在应用层(批量查、加缓存),不在 SQL 本身。
Calls 很低 + 单次极慢:通常是离线统计或后台报表,可以考虑挪到从库、或者加汇总表。
Calls 高 + Rows examine 远大于 Rows sent:索引问题,这是最值得立刻改的一类。

三、从 Rows examine / Rows sent 比值定位索引缺失

慢日志的每条记录都带 Rows_examined 和 Rows_sent。两者的比值直接反映选择性:

-- 取一条具体查询出来看
mysql> EXPLAIN SELECT id,title FROM posts WHERE slug='abc';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 284517 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

加索引时注意几个实务细节:

-- 正确:给等值查询字段建索引,注意字符集与列的实际定义一致
ALTER TABLE posts ADD INDEX idx_slug (slug), ALGORITHM=INPLACE, LOCK=NONE;

-- 复合索引遵循最左前缀:等值列在前,范围列在后
ALTER TABLE posts ADD INDEX idx_cat_status_time (category_id, status, created_at);

-- 验证:看 key 是否命中,以及 rows 是否下降
EXPLAIN SELECT id FROM posts WHERE category_id=3 AND status='publish' ORDER BY created_at DESC LIMIT 20;

四、那些「不是索引问题」的慢查询

把索引补齐之后仍然慢的查询,通常属于下面几类,改法完全不同:

1. 排序与临时表

-- 慢:先取出 10 万行再丢弃
SELECT * FROM posts ORDER BY id DESC LIMIT 100000, 20;

-- 快:用上一页最后一条的 id 做游标
SELECT * FROM posts WHERE id < 881234 ORDER BY id DESC LIMIT 20;

2. 锁等待

慢日志里会出现 Lock_time 明显大于 Query_time 的十分之一的情况。这不是 SQL 写得不好,而是有长事务持着行锁。去看:

SELECT * FROM performance_schema.data_lock_waits\G
SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

如果一个事务开了 30 秒还没提交,先查应用层:是不是在事务里做了 HTTP 请求、发了邮件、或者跑了长循环。

3. 表膨胀与统计信息过期

优化器选错索引,往往因为它以为的基数不对。可以先看:

SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables WHERE table_schema='blog';
ANALYZE TABLE posts;

对于有大量删除的表,OPTIMIZE TABLE(或 ALTER TABLE ... ENGINE=InnoDB)能回收空间并整理页,但会锁表或消耗大量 I/O,安排在低峰期。

五、把慢日志变成可持续的流程

一次性分析解决不了问题,你需要的是一个轻量流程:

每日切割:用 logrotate 或 FLUSH SLOW LOGS; 每天切一个文件,避免单文件过大。切割后 pt-query-digest 更容易处理。
每日取 Top 10:固定跑一次聚合,把 Top 10 存成文本,便于跨天对比。这样能区分「新出现的慢查询」和「一直都在的慢查询」。
建立基线:记录当前的 QPS、平均响应、慢查询条数。改索引或改代码之后,用同样的口径复测,否则你无法证明优化有效。
清理噪声源:备份脚本、监控探针、批处理任务应该在慢日志里被识别并排除,比如给这些连接打上 SET SESSION long_query_time=5;,或者用 --filter 排除特定 user。

# 每日快照示例
#!/bin/sh
D=$(date +%F)
pt-query-digest --limit 1 --order-by Query_time:sum \
  /var/log/mysql/slow.log > /var/log/mysql/reports/$D.txt
grep -E '^# Query [0-9]+' -A2 /var/log/mysql/reports/$D.txt | head -60

MySQL 慢日志分析实战：long_query_time 参数取舍、pt-query-digest 聚合与扫描行数比值定位

一、参数怎么设:别照抄 long_query_time=0

二、先聚合,再看单条

三、从 Rows examine / Rows sent 比值定位索引缺失

四、那些「不是索引问题」的慢查询

1. 排序与临时表

2. 锁等待

3. 表膨胀与统计信息过期

五、把慢日志变成可持续的流程

六、一个常见的误判

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

Linux 服务器时间同步实战：chrony 与 NTP 配置从入门到排错

Linux服务器安全加固实战：从SSH到Fail2ban的完整防护体系

网站多语言国际化实战：子目录 URL 结构、hreflang 标注、Nginx 伪静态与多语言 SEO 全流程

MySQL 数据库性能优化实战：从慢查询分析到高并发架构设计

XDP 与 eBPF 高性能网络实战：内核旁路丢包抗 DDoS、bpftool 观测与 AF_XDP 零拷贝全流程

MySQL 慢日志分析实战：long_query_time 参数取舍、pt-query-digest 聚合与扫描行数比值定位

一、参数怎么设:别照抄 long_query_time=0

二、先聚合,再看单条

三、从 Rows examine / Rows sent 比值定位索引缺失

四、那些「不是索引问题」的慢查询

1. 排序与临时表

2. 锁等待

3. 表膨胀与统计信息过期

五、把慢日志变成可持续的流程

六、一个常见的误判