MySQL 慢查询日志实战：阈值设置、mysqldumpslow 汇总、EXPLAIN 关键列与四种索引失效排查

Author： zz1984
发布时间：October 18, 2026
3 views
No comments
8359 字数
Categories：技术教程

慢查询日志不是打开就完事

MySQL 的慢查询日志大概是所有站长最早听说、最少认真用的功能之一。很多人打开它，等了几天，发现文件里要么空空如也，要么塞满几千条一模一样的语句，最后干脆关掉了事。问题不在日志本身，而在于阈值设置和读取方式：long_query_time 默认是 10 秒，可一个正常的网页查询超过 200 毫秒就已经值得关注；而如果不做汇总直接用 tail 看，你看到的永远是最新那几十条，根本没有宏观视角。

这篇文章讲一套在个人站上真正跑得通的做法：怎么设阈值、怎么用 mysqldumpslow 和 performance_schema 做汇总、怎么读懂 EXPLAIN 的关键列、以及最常遇到的几种索引失效场景怎么修。所有示例基于 MySQL 8.0，运行环境假设是一台 2 核 4G 的云服务器，这也是个人站最常见的配置。

第一步：把慢查询日志参数配对

在 /etc/mysql/mysql.conf.d/mysqld.cnf 的 [mysqld] 段里加：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0
min_examined_row_limit = 1000
log_slow_admin_statements = 1
log_slow_replica_statements = 1

几个参数的选择理由：

long_query_time = 0.5：半秒是个实用起点。10 秒的默认值在网站场景里意味着用户已经关掉页面了。如果日志量太大，可以再放宽到 1 秒，但不要一上来就设成 5 秒。
log_queries_not_using_indexes = 0：新手常把它打开，结果小表全表扫描也记进日志，一天几万条噪音，真正的问题被淹没。只有在专门排查索引缺失时才临时打开。
min_examined_row_limit = 1000：扫描行数少于 1000 的查询不记录。这一条能过滤掉绝大多数无意义的小表全扫。

改完配置要重启而不是 reload，因为这个参数不支持动态修改：

systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';"

如果是线上不方便重启，也可以用运行时变量打开，但重启后会失效，只适合临时排查：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'FILE';

第二步：不要 tail，要汇总

慢查询日志的格式是每条记录带 Query_time、Lock_time、Rows_sent、Rows_examined，语句本身可能跨多行。直接看几乎无法分析，官方自带的 mysqldumpslow 是最好的起点：

# 按总耗时排序，看最烧时间的前 20 条
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按平均耗时排序
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log

# 按扫描行数排序，找扫描量最大的
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

mysqldumpslow 会自动把参数替换成 N 和 'S'，所以上面这些「同一个 SQL 不同参数」的记录会被聚合成一条，这才是你需要的视角。输出里重点看三列：

Count：出现次数。次数高且单次不慢的，往往比次数少但很慢的更有优化价值。
Time：总耗时。这是优先级排序最直接的指标。
Rows：平均扫描行数 / 平均返回行数。这两个数的比值是最重要的信号——如果扫描 10 万行只返回 10 行，说明索引非常低效。

示例输出：

Count: 1843  Time=2.31s (4257s)  Lock=0.00s  Rows=12.4 (22853), 网站用户[www]@localhost
  SELECT * FROM wp_postmeta WHERE meta_key='N' AND meta_value='S'

这条记录一眼就能定性：1843 次，平均扫描 22853 行只返回 12 行。wp_postmeta 上 meta_value 没有索引，这是 WordPress 最经典的问题。

第三步：用 performance_schema 看实时汇总

mysqldumpslow 是离线的，需要等日志积累。如果想看「现在正在发生什么」，用 performance_schema 的语句摘要表：

SELECT DIGEST_TEXT,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms,
       SUM_ROWS_EXAMINED AS rows_examined,
       SUM_ROWS_SENT AS rows_sent,
       SUM_NO_INDEX_USED AS no_idx
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = '你的数据库名'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

这张表的好处是已经按 SQL 指纹聚合过，而且带 SUM_NO_INDEX_USED——直接告诉你哪些语句没走索引。把 ORDER BY 换成 SUM_ROWS_EXAMINED DESC 就是另一份视角，专门找「读了很多但没返回」的语句。

还有一个非常实用的实时视图，能抓到「此刻正在执行且已经跑了很久」的查询：

SELECT ID, USER, TIME, STATE, LEFT(INFO,120) AS sql_text
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 3
ORDER BY TIME DESC;

发现长查询之后，先别急着 kill。kill 一个正在做大量写入的事务可能触发回滚，回滚时间往往比原查询还长。正确顺序是先 EXPLAIN 看它为什么慢，判断能不能等它自己跑完。

第四步：读懂 EXPLAIN 的几个关键列

找到目标语句后，EXPLAIN 是下一步。MySQL 8 的 EXPLAIN ANALYZE 会实际执行并给出真实耗时，比传统 EXPLAIN 更准，但只在能安全执行的查询上用：

EXPLAIN ANALYZE SELECT * FROM wp_postmeta
WHERE meta_key = '_thumbnail_id' AND post_id = 12345;

传统 EXPLAIN 输出里，只需要盯死三列：

type 列：访问类型

从好到坏大致是：system > const > eq_ref > ref > range > index > ALL。

const：主键或唯一索引等值查询，最多一行，最理想。
ref：普通索引等值查询，正常业务查询的常见形态。
range：索引范围扫描，BETWEEN、>、IN 之类。可接受。
index：扫描整棵索引树，比全表扫稍好但通常也要优化。
ALL：全表扫描。小表无所谓，十万行以上的表出现在这里就要处理。

rows 列：预估扫描行数

这是优化器估计要读多少行。如果 rows 很大而实际返回很少，说明索引选择性差或者统计信息过期。统计信息过期的典型症状是：明明建了索引，优化器却选全表扫描。这时执行 ANALYZE TABLE 表名; 往往能让执行计划立刻改变。

Extra 列：额外信息

这一列信息量最大，几个关键词必须认识：

Using index：覆盖索引，只读索引不回表，非常好。
Using where：server 层在拿到数据后又过滤了一遍，配合 rows 大就要注意。
Using filesort：需要额外排序，无法利用索引顺序。数据量大时非常慢。
Using temporary：用了临时表，常见于 GROUP BY 和 DISTINCT，数据量大时会写磁盘。
Using index condition：索引下推，通常是好事。

Using filesort 和 Using temporary 同时出现，基本可以判定这条 SQL 在数据量涨上来之后一定会出问题。

最常见的四种索引失效场景

1. 复合索引的最左前缀被破坏

建了 idx(a, b, c)，查询写成 WHERE b = 1 AND c = 2，索引完全用不上。复合索引遵循最左前缀原则：只有从 a 开始连续使用的列才能利用索引。所以复合索引的列顺序要按「查询中使用频率 + 选择性」来排，最常用的等值条件放最左。

另外，WHERE a = 1 AND c = 2 这种跳过 b 的写法，a 能用上索引，c 不行（MySQL 8 的索引下推能让 c 在索引层过滤，但依然是扫描）。

2. 对索引列做函数运算或隐式转换

-- 失效：索引列被函数包裹
SELECT * FROM posts WHERE DATE(created_at) = '2026-10-18';

-- 改写：范围查询，可用索引
SELECT * FROM posts WHERE created_at >= '2026-10-18 00:00:00'
  AND created_at < '2026-10-19 00:00:00';

隐式转换更隐蔽。user_id 是 varchar，查询写 WHERE user_id = 123，MySQL 会把列转成数字比较，索引失效。反过来，如果列是 int 而传字符串 '123'，MySQL 会把字符串转成数字，索引仍然可用。所以规则是：参数类型必须和列类型一致，尤其是 varchar 列千万不要传数字。

3. LIKE 以通配符开头

WHERE title LIKE '%关键词%'   -- 索引失效
WHERE title LIKE '关键词%'    -- 可以使用索引

前缀通配符无法利用 B+ 树的顺序性。真要做全文搜索，MySQL 8 的 FULLTEXT 索引（需要 ngram 分词器支持中文）或者外接 Meilisearch 都比 LIKE '%x%' 靠谱得多。

4. 隐式排序导致 filesort

分页查询最常见的写法 ORDER BY created_at DESC LIMIT 20，如果 created_at 没索引，每次都要把整表读出来排序。加上索引之后，如果 WHERE 条件和 ORDER BY 列能组成一个复合索引，排序就能直接利用索引顺序。这也是为什么 idx(status, created_at) 这样的组合索引比单独两个索引更好用。

一个真实案例：WordPress 的 wp_postmeta

前面那条 1843 次、平均扫描两万行的查询，是很典型的 WordPress 慢查询。修复方式不是加索引（meta_value 是 longtext，加 B-Tree 索引无效），而是改查询结构或者加针对性索引：

-- 针对 meta_key + post_id 的组合查询
ALTER TABLE wp_postmeta ADD INDEX idx_key_post (meta_key(32), post_id);

-- 检查索引效果
EXPLAIN SELECT * FROM wp_postmeta
WHERE meta_key = '_thumbnail_id' AND post_id = 12345;

注意 meta_key(32) 是前缀索引：只索引前 32 个字符。对于 key 名普遍很短的情况足够用，而且能显著减小索引体积。前提是前 32 字符的选择性足够，可以先用这个语句验证：

SELECT COUNT(DISTINCT LEFT(meta_key,32)) / COUNT(*) AS selectivity FROM wp_postmeta;

结果越接近 1 越好。低于 0.9 就说明前缀不够长。

优化之后怎么确认真的变快了

改完索引不要凭感觉。清空统计、跑一段时间、再看同一张摘要表：

TRUNCATE performance_schema.events_statements_summary_by_digest;

-- 等一段时间业务跑过之后
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(AVG_TIMER_WAIT/1e9,2) AS avg_ms,
       SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

对比优化前后的 avg_ms 与 avg_rows，两个数都应该明显下降。如果只有时间下降而行数没变，说明改善来自缓存而不是索引，不能算真优化。

小结

慢查询治理的完整链条是：设对阈值 → 用 mysqldumpslow 或 performance_schema 做汇总找出 TOP 语句 → 用 EXPLAIN 定位 type/rows/Extra 的问题 → 针对性改索引或改查询写法 → 数字化验证效果。

对个人站来说，真正需要优化的 SQL 永远不会超过十条。把这几条找出来、修掉，网站的后台响应速度和数据库压力就能有一个数量级的改善，而这往往比升级服务器配置划算得多。慢查询日志的价值不在于「开着」，而在于你有没有把它当成一个每周固定读一遍的运维材料。

Last modification：October 18th, 2026 at 01:26 pm

MySQL 慢查询日志实战：阈值设置、mysqldumpslow 汇总、EXPLAIN 关键列与四种索引失效排查

zz1984 • 2026 年 10 月 18 日

慢查询日志不是打开就完事

第一步：把慢查询日志参数配对

在 /etc/mysql/mysql.conf.d/mysqld.cnf 的 [mysqld] 段里加：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0
min_examined_row_limit = 1000
log_slow_admin_statements = 1
log_slow_replica_statements = 1

几个参数的选择理由：

long_query_time = 0.5：半秒是个实用起点。10 秒的默认值在网站场景里意味着用户已经关掉页面了。如果日志量太大，可以再放宽到 1 秒，但不要一上来就设成 5 秒。
log_queries_not_using_indexes = 0：新手常把它打开，结果小表全表扫描也记进日志，一天几万条噪音，真正的问题被淹没。只有在专门排查索引缺失时才临时打开。
min_examined_row_limit = 1000：扫描行数少于 1000 的查询不记录。这一条能过滤掉绝大多数无意义的小表全扫。

改完配置要重启而不是 reload，因为这个参数不支持动态修改：

systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';"

如果是线上不方便重启，也可以用运行时变量打开，但重启后会失效，只适合临时排查：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'FILE';

第二步：不要 tail，要汇总

# 按总耗时排序，看最烧时间的前 20 条
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按平均耗时排序
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log

# 按扫描行数排序，找扫描量最大的
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

mysqldumpslow 会自动把参数替换成 N 和 'S'，所以上面这些「同一个 SQL 不同参数」的记录会被聚合成一条，这才是你需要的视角。输出里重点看三列：

Count：出现次数。次数高且单次不慢的，往往比次数少但很慢的更有优化价值。
Time：总耗时。这是优先级排序最直接的指标。
Rows：平均扫描行数 / 平均返回行数。这两个数的比值是最重要的信号——如果扫描 10 万行只返回 10 行，说明索引非常低效。

示例输出：

Count: 1843  Time=2.31s (4257s)  Lock=0.00s  Rows=12.4 (22853), 网站用户[www]@localhost
  SELECT * FROM wp_postmeta WHERE meta_key='N' AND meta_value='S'

这条记录一眼就能定性：1843 次，平均扫描 22853 行只返回 12 行。wp_postmeta 上 meta_value 没有索引，这是 WordPress 最经典的问题。

第三步：用 performance_schema 看实时汇总

mysqldumpslow 是离线的，需要等日志积累。如果想看「现在正在发生什么」，用 performance_schema 的语句摘要表：

SELECT DIGEST_TEXT,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms,
       SUM_ROWS_EXAMINED AS rows_examined,
       SUM_ROWS_SENT AS rows_sent,
       SUM_NO_INDEX_USED AS no_idx
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = '你的数据库名'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

还有一个非常实用的实时视图，能抓到「此刻正在执行且已经跑了很久」的查询：

SELECT ID, USER, TIME, STATE, LEFT(INFO,120) AS sql_text
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 3
ORDER BY TIME DESC;

第四步：读懂 EXPLAIN 的几个关键列

找到目标语句后，EXPLAIN 是下一步。MySQL 8 的 EXPLAIN ANALYZE 会实际执行并给出真实耗时，比传统 EXPLAIN 更准，但只在能安全执行的查询上用：

EXPLAIN ANALYZE SELECT * FROM wp_postmeta
WHERE meta_key = '_thumbnail_id' AND post_id = 12345;

传统 EXPLAIN 输出里，只需要盯死三列：

type 列：访问类型

从好到坏大致是：system > const > eq_ref > ref > range > index > ALL。

const：主键或唯一索引等值查询，最多一行，最理想。
ref：普通索引等值查询，正常业务查询的常见形态。
range：索引范围扫描，BETWEEN、>、IN 之类。可接受。
index：扫描整棵索引树，比全表扫稍好但通常也要优化。
ALL：全表扫描。小表无所谓，十万行以上的表出现在这里就要处理。

rows 列：预估扫描行数

Extra 列：额外信息

这一列信息量最大，几个关键词必须认识：

Using index：覆盖索引，只读索引不回表，非常好。
Using where：server 层在拿到数据后又过滤了一遍，配合 rows 大就要注意。
Using filesort：需要额外排序，无法利用索引顺序。数据量大时非常慢。
Using temporary：用了临时表，常见于 GROUP BY 和 DISTINCT，数据量大时会写磁盘。
Using index condition：索引下推，通常是好事。

Using filesort 和 Using temporary 同时出现，基本可以判定这条 SQL 在数据量涨上来之后一定会出问题。

最常见的四种索引失效场景

1. 复合索引的最左前缀被破坏

另外，WHERE a = 1 AND c = 2 这种跳过 b 的写法，a 能用上索引，c 不行（MySQL 8 的索引下推能让 c 在索引层过滤，但依然是扫描）。

2. 对索引列做函数运算或隐式转换

-- 失效：索引列被函数包裹
SELECT * FROM posts WHERE DATE(created_at) = '2026-10-18';

-- 改写：范围查询，可用索引
SELECT * FROM posts WHERE created_at >= '2026-10-18 00:00:00'
  AND created_at < '2026-10-19 00:00:00';

3. LIKE 以通配符开头

WHERE title LIKE '%关键词%'   -- 索引失效
WHERE title LIKE '关键词%'    -- 可以使用索引

4. 隐式排序导致 filesort

一个真实案例：WordPress 的 wp_postmeta

-- 针对 meta_key + post_id 的组合查询
ALTER TABLE wp_postmeta ADD INDEX idx_key_post (meta_key(32), post_id);

-- 检查索引效果
EXPLAIN SELECT * FROM wp_postmeta
WHERE meta_key = '_thumbnail_id' AND post_id = 12345;

SELECT COUNT(DISTINCT LEFT(meta_key,32)) / COUNT(*) AS selectivity FROM wp_postmeta;

结果越接近 1 越好。低于 0.9 就说明前缀不够长。

优化之后怎么确认真的变快了

改完索引不要凭感觉。清空统计、跑一段时间、再看同一张摘要表：

TRUNCATE performance_schema.events_statements_summary_by_digest;

-- 等一段时间业务跑过之后
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(AVG_TIMER_WAIT/1e9,2) AS avg_ms,
       SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

对比优化前后的 avg_ms 与 avg_rows，两个数都应该明显下降。如果只有时间下降而行数没变，说明改善来自缓存而不是索引，不能算真优化。

慢查询日志不是打开就完事

第一步：把慢查询日志参数配对

第二步：不要 tail，要汇总

第三步：用 performance_schema 看实时汇总

第四步：读懂 EXPLAIN 的几个关键列

type 列：访问类型

rows 列：预估扫描行数

Extra 列：额外信息

最常见的四种索引失效场景

1. 复合索引的最左前缀被破坏

2. 对索引列做函数运算或隐式转换

3. LIKE 以通配符开头

4. 隐式排序导致 filesort

一个真实案例：WordPress 的 wp_postmeta

优化之后怎么确认真的变快了

小结

相关文章