MySQL 慢查询日志怎么开、怎么看:从聚合排序到加对索引的完整闭环

站点的数据库慢,最常见的处理方式是"重启一下 MySQL",重启之后确实会快几分钟,然后照旧变慢。真正的问题在于你根本不知道哪条 SQL 在拖后腿。MySQL 自带的慢查询日志就是回答这个问题的工具,但大多数站长要么没开,要么开了不去看,要么看了之后被几千条记录淹没。这篇把从开启、采样、聚合到真正改掉一条 SQL 的完整链路写清楚,都是我一个人维护十几个站时反复用到的做法。

慢查询日志的开关不止一个参数

很多人以为把 slow_query_log 设成 ON 就完事了,实际上决定"什么算慢"和"记什么内容"的是四个参数,缺一个日志就没有分析价值。

第一个是 long_query_time,单位是秒,可以写小数。默认是 10 秒,这个值对内容站来说完全没有意义——页面上一条 3 秒的查询已经把用户体验毁掉了,但它不会被记录。我一般先设成 1,跑一天看看量级,再决定要不要降到 0.5。间隔设置太激进会让日志本身成为 I/O 负担,这一点后面有具体判据。

mysql -uroot -p -e "
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = OFF;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';"

第二个是 log_queries_not_using_indexes。名字很诱人,但它是新手最大的陷阱:一个只有 20 行的小表全表扫描,在业务上完全无害,却会被记进慢日志。一旦打开,日志里九成记录都是这种噪音。我的做法是保持 OFF,只在专门排查索引缺失的那一天临时打开半小时。

第三个是 min_examined_row_limit,只有扫描行数超过这个值才记录。设成 100 意味着"扫了 20 行但很快"的查询不会进来。它和上一个参数配合使用,能过滤掉绝大部分伪慢查询。

第四个容易被忽略的是 log_slow_extra(MySQL 8.0.14 之后才有),打开后日志里会多出 Rows_sent、Rows_examined、Query_time、Lock_time 这些结构化字段,后面做聚合统计全靠它。5.7 上没有这个参数,只能靠日志行尾的 Rows_sent: X Rows_examined: Y 文本解析。

SET GLOBAL 只活到下次重启

上面那串 SET GLOBAL 在 MySQL 重启后会全部失效。我见过至少三次"慢日志明明开了怎么又没了",最后都是因为改完只在运行时生效,几个月后一次服务器重启就悄悄退回默认值。

正确做法是写进配置文件 /etc/mysql/my.cnf 或者 /etc/mysql/mysql.conf.d/mysqld.cnf 的 [mysqld] 段:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
min_examined_row_limit = 100
log_queries_not_using_indexes = 0
log_slow_extra = 1

改完不要直接 systemctl restart mysql,生产环境凌晨重启也得担风险。用 systemctl reload mysql 或者登录后执行 SET GLOBAL 加配置文件双写,让配置和运行时状态一致,下次重启自然继承。验证方法是改完后用 SHOW VARIABLES 核对,再 reload 一次确认值没变回去——有些发行版的配置文件里其他地方还有一处 long_query_time,后读的会覆盖前面的。

还有一个权限细节:日志文件所在的目录必须让 mysql 用户可写。/var/log/mysql 通常已经是 mysql:adm 750,如果你为了自己方便 chown root,MySQL 会启动失败并在 error log 里报 cannot create file。查 error log 的位置是 SHOW VARIABLES LIKE 'log_error'。

把日志聚合成"谁最贵",不是"谁最慢"

慢日志的行格式是每条 SQL 前面带一段以 # 开头的注释:时间戳、Query_time、Lock_time、Rows_sent、Rows_examined,然后才是 SQL 本体。一条记录跨三到五行,肉眼翻一千条根本看不出规律。

关键指标不是单条最慢的那次,而是"总耗时 = 单次平均耗时 × 调用次数"。一条 0.2 秒的查询被调用两万次,总耗时 4000 秒,比一条偶发的 3 秒查询重要得多。这就是所谓的按总耗时排序(total time),也是 mysqldumpslow 和 pt-query-digest 默认的排序方式。

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

-s t 是按总耗时排,-s c 按出现次数,-s at 按平均耗时。三个结果经常完全不同:按次数排出来的第一名往往是某条列表页的 COUNT 查询,按平均耗时排出来的第一名则可能是后台报表。先修总耗时榜的前三名,收益最大。

mysqldumpslow 会把数字和字符串归一化成 N 和 S,所以 WHERE id = 123 和 WHERE id = 456 会被合并成同一条,这正是我们要的聚合效果。缺点是它对长 SQL 的截断比较粗暴,看不出具体字段。

要更细的分析就上 pt-query-digest(percona-toolkit 包),它输出的 Profile 段会给出每条查询的 pct(占总耗时百分比)、exec count、以及响应时间分布 p95/p99。判据是:前三名的 pct 加起来超过 60%,就只修这三条,其余的等它们自然掉出榜单。试图一次优化二十条查询的结果通常是一条都没改完。

采样一天还是采样一周

慢日志的采样窗口直接决定结论对不对。只跑一小时的日志,抓到的多半是后台任务或者你自己在测的东西;跑一周又会产生几百兆文件。

我的做法是按业务周期取:内容站有明确的"白天写稿、晚上被爬"的节奏,所以至少覆盖 24 小时,包含一次凌晨的备份和一次晚高峰。如果站上有定时任务,还要确保跨过一次任务的执行点。

日志文件本身要防膨胀。MySQL 5.7 起支持 logrotate,但直接 rotate 会因为文件句柄问题让 MySQL 继续往被改名后的文件里写。正确做法是配置里加 FLUSH LOGS 的后置动作:

/var/log/mysql/slow.log {
    daily
    rotate 14
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
    postrotate
        mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
    endscript
}

postrotate 里那条 flush-logs 是必须的:它让 MySQL 关闭旧句柄、重新打开新文件。少了这一行,你看到的 slow.log 永远是 0 字节,而磁盘上却躺着一堆 slow.log.1 在长肉。这个现象和日志轮转的其他坑形态一样,判据是 lsof | grep slow.log 里如果还挂着 .1 或 .gz 的文件名,就说明没 flush。

用 EXPLAIN 把一条慢 SQL 拆开

聚合出 top 3 之后,下一步是逐条看执行计划。EXPLAIN 的输出里我最关注四列:type、key、rows、Extra。

type 从好到坏大致是 const → eq_ref → ref → range → index → ALL。ALL 就是全表扫描,index 是全索引扫描,这两个在一张几万行的表上可能还扛得住,在百万行表上基本必死。但要注意:type=ALL 且 rows 只有 30 的查询根本不值得优化。

EXPLAIN SELECT id, title FROM typecho_contents
WHERE type = 'post' AND status = 'publish' AND created < 1700000000
ORDER BY created DESC LIMIT 20;

Extra 里出现 Using filesort 意味着排序没有用上索引,出现 Using temporary 意味着建了临时表。这两个是内容站最常见的性能杀手,因为它们出现在分页查询上:一个 ORDER BY created DESC LIMIT 20 如果 created 上没有合适索引,就要把符合条件的行全部取出来排序再截断。

还有一种隐蔽情况:type 显示 range、rows 也不大,但实际很慢。这通常是 Using where 后面还跟着大量回表。判据是看 Rows_examined 和 Rows_sent 的比值,一百比一以上说明索引选择率差,该考虑复合索引把过滤字段一起包进去。

加索引之前先问三个问题

看到全表扫描就想加索引,是另一种常见失误。索引是有代价的:每个索引都要在写入时维护,占空间,还会影响优化器的选择。加之前先问三件事。

第一,这条 SQL 的调用频率是多少。一天跑一次的报表,加索引不如直接接受它慢。第二,这张表的写入量有多大。如果是一张每秒写入几百行的日志表,每加一个索引都是给写入路径增负。第三,现有的索引能不能改造成覆盖。很多时候不是缺索引,而是索引列的顺序错了——(created) 改成 (type, status, created) 就让同一条 SQL 走 ref 加有序扫描。

内容站最典型的一个复合索引是"列表页 + 排序"这个组合。Typecho 的首页查询长这样:按 type 和 status 过滤,按 created 倒序取 10 条。复合索引的列顺序必须是"等值条件在前、范围/排序列在后",也就是 (type, status, created)。如果写成 (created, type, status),排序能用上,但过滤要靠回表,效果差很多。

加索引用 ALTER TABLE ... ADD INDEX,MySQL 5.6 之后支持在线 DDL(ALGORITHM=INPLACE, LOCK=NONE),但"在线"不等于零成本:它仍要扫描全表建索引,期间 I/O 会明显上升。几十万行的表放在业务低峰做,加完用 SHOW INDEX FROM 表名 确认,再用原来的 EXPLAIN 对比 key 和 rows 两列是否真的变了。

改完之后怎么确认真的变快了

改索引、改 SQL 之后必须回到慢日志验证,不能凭感觉。最直接的判据是同一时段慢日志条数的变化:改之前一天 300 条,改之后一天 20 条,这就是有效。如果条数没变,很可能是优化器根本没选你的新索引。

优化器不选新索引有两种原因:统计信息过期,或者它算出来的成本确实更高。ANALYZE TABLE 会刷新统计信息;如果刷新后还是不选,可以用 EXPLAIN 加 FORCE INDEX 对比两种计划的 rows,差别巨大时才考虑用 USE INDEX 提示。绝大多数情况下正确的做法是调整索引定义,而不是在 SQL 里写死索引名——写死之后表结构一变就是新的故障源。

另一个必须核对的数字是 Innodb_buffer_pool_read_requests 与 Innodb_buffer_pool_reads 的比值,前者是逻辑读,后者是真实磁盘读。命中率低于 99% 说明 buffer pool 偏小,这时候再优化 SQL 收益也有限——数据根本缓存不住。这个数字和慢查询日志一起看,才能分清是"SQL 写得烂"还是"内存给得少"。

什么时候该关掉慢查询日志

慢查询日志不是永远该开着。它每次记录都要写磁盘,如果站点本身 I/O 就紧张(比如和数据库同机跑着容器、日志、备份),慢日志可能成为新的负担。判据是看 SHOW GLOBAL STATUS LIKE 'Slow_queries' 的每秒增量:一天几十条完全无所谓,每秒几十条就说明日志文件在疯狂增长。

我的策略是分阶段:排查期开 1 秒阈值、保留 14 天;稳定期改成 2 秒、只留 7 天;如果站点规模和访问模式几个月都没变化,甚至可以只在季度体检时开三天。工具的价值在于回答具体问题,而不是永远挂在生产环境上跑。

最后提醒一个容易忽略的点:慢日志只记录"执行完了"的查询。一条把数据库连接卡死的锁等待,如果最后超时,会记在 error log 而不是慢日志里;如果它一直等着没超时,两个日志里都不会有它。所以看到"页面很慢但慢日志干干净净",下一步该查的是 SHOW PROCESSLIST 里的 State 列,以及 information_schema.innodb_trx 里的长事务,而不是继续翻慢日志。

Last modification:November 17th, 2026 at 10:27 pm

Leave a Comment