📖 本篇简介：慢查询（Slow Query）是数据库性能问题最常见的入口，也是 DBA 与后端工程师必须掌握的基本功。本篇在前 18 章索引、执行计划的基础上，系统讲解如何开启慢查询日志、用 mysqldumpslow 等工具定位真正拖慢业务的 SQL，以及拿到慢 SQL 之后常见的改写思路与优化策略。学完本篇，你将能够独立搭建一套慢查询监控闭环，并把典型慢 SQL 优化到毫秒级。本系列示例基于 MySQL 8.0 语法。
目录
- 1 慢查询日志概述与开启
- 2 慢查询日志关键参数
- 3 使用 mysqldumpslow 分析慢日志
- 4 定位慢 SQL 的其他手段
- 5 常见慢查询原因诊断
- 6 SQL 改写与优化策略
- 7 慢查询优化速查表
- 8 常见坑与注意事项
- 9 练习题与参考答案
1 慢查询日志概述与开启
慢查询日志（Slow Query Log）是 MySQL 内置的一种诊断日志，它会把"执行时间超过阈值、并且没有命中索引"的 SQL 语句原样记录下来，供后续分析。它本质上是数据库送给运维团队的"案件卷宗"——你只能在它记下来之后去倒查，而不能指望它主动告诉你哪条 SQL 慢。
慢查询日志默认是关闭的，因为开启后会带来少量 I/O 开销（尤其是高并发写入场景），但生产环境几乎必须开启，否则一旦出现接口超时，你将完全无迹可寻。
下面是开启慢查询日志的标准 SQL（本示例基于 MySQL 8.0，需具备 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限）：
-- 开启慢查询日志（全局生效，立即作用于新连接）
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值为 1 秒（单位：秒）
SET GLOBAL long_query_time = 1;
-- 把未使用索引的查询也记录下来（即使执行时间很短）
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- 查看当前慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 查看慢查询是否已开启
SHOW VARIABLES LIKE 'slow_query_log';
需要注意的是，SET GLOBAL 只对运行中的实例生效，MySQL 重启后会丢失。要让配置永久生效，必须写入 my.cnf（Linux）或 my.ini（Windows）的 [mysqld] 段：
[mysqld]
# 永久开启慢查询日志
slow_query_log = 1
# 慢查询阈值 1 秒
long_query_time = 1
# 慢日志文件路径（建议放在独立磁盘，避免与数据盘争抢 IO）
slow_query_log_file = /var/log/mysql/slow.log
# 把没走索引的 SQL 也记下来
log_queries_not_using_indexes = 1
# 每分钟最多记录多少条没走索引的 SQL，防止日志被这类 SQL 刷爆
log_throttle_queries_not_using_indexes = 60
一个常见的误区是把 long_query_time 设得特别小（比如 0.01 秒），以为这样能"抓所有慢 SQL"。实际上在高并发系统里，0.01 秒阈值会让慢日志瞬间被海量 SQL 占满，磁盘写满反而拖垮主库。合理的阈值一般是 0.5~2 秒，再配合应用层 APM（如 SkyWalking、Arms）补充毫秒级视角。
2 慢查询日志关键参数
慢查询日志由一组系统变量共同控制，理解它们的含义，比记住一个命令更重要。下表列出生产环境最常调整的 8 个参数：
| 参数名 | 作用 | 推荐值 | 说明 |
|---|---|---|---|
| slow_query_log | 是否开启慢日志 | ON | 开关，ON/OFF |
| long_query_time | 慢查询阈值（秒） | 1 | 可设为小数，如 0.5 |
| log_queries_not_using_indexes | 是否记录未走索引的 SQL | ON | 注意与阈值无关 |
| log_throttle_queries_not_using_indexes | 未走索引 SQL 的限流条数/分钟 | 60 | 防止日志被刷爆 |
| log_slow_admin_statements | 是否记录 ALTER/OPTIMIZE 等管理语句 | ON | 大表 DDL 也会拖库 |
| log_slow_slave_statements | 从库是否记录慢 SQL | OFF | 一般从库默认关闭 |
| min_examined_row_limit | 扫描行数低于该值不记录 | 0 | 过滤掉"扫得少但慢"的噪声 |
| slow_query_log_file | 慢日志文件路径 | 独立磁盘 | 避免与 ibdata 争抢 |
除了上表，慢查询日志的输出格式也很重要。MySQL 8.0 支持两种输出方式：
-- 慢日志输出到文件（默认，性能最好）
SET GLOBAL log_output = 'FILE';
-- 慢日志输出到系统表 mysql.slow_log（便于 SQL 查询，但性能差）
SET GLOBAL log_output = 'TABLE';
-- 同时输出到文件和表
SET GLOBAL log_output = 'FILE,TABLE';
输出到 mysql.slow_log 表的好处是可以直接用 SQL 聚合统计：
-- 统计慢日志表里按用户聚合的慢查询次数
SELECT user_host, COUNT(*) AS cnt
FROM mysql.slow_log
GROUP BY user_host
ORDER BY cnt DESC
LIMIT 10;
但要注意，mysql.slow_log 默认是 CSV 引擎，写入开销远高于文件，生产环境长期使用会拖慢主库。它更适合"临时打开、抓一批、再关掉"的排查场景，不建议作为常态方案。
2.1 慢日志文件的基本格式
理解慢日志文件长什么样，能让你在没有工具的情况下直接用 grep 应急排查。一条典型的慢日志记录格式如下：
# Time: 2026-10-07T10:15:30.123456Z
# User@Host: root[root] @ localhost [] Id: 1234
# Query_time: 2.501000 Lock_time: 0.010000 Rows_sent: 20 Rows_examined: 100020
SET timestamp=1728296130;
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;
四行头部字段分别表示：发生时间、执行账号与连接 ID、耗时与扫描行数、以及 SQL 正文。其中最值得关注的是 Query_time、Rows_examined 两个字段：
Query_time：SQL 端到端耗时，包含锁等待；Lock_time：在 InnoDB 引擎层等待锁的时间；Rows_sent：返回给客户端的行数；Rows_examined：存储引擎实际扫描的行数。
如果 Rows_examined 远大于 Rows_sent（比如本例扫了 10 万行只返回 20 行），基本可以断定是索引问题，优化收益最大。
下面是一个用 Python 直接解析慢日志、按 SQL 模板聚合的小例子，方便你理解 mysqldumpslow 的工作原理（本示例用 Python 3 实跑，不依赖 MySQL）：
# 解析慢日志文件，按 SQL 模板（把数字替换为 N）聚合耗时
import re
from collections import defaultdict
# 慢日志样本（实际使用时替换成真实路径）
sample = """# Time: 2026-10-07T10:15:30.123456Z
# Query_time: 2.501000 Lock_time: 0.010000 Rows_sent: 20 Rows_examined: 100020
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;
# Time: 2026-10-07T10:16:00.123456Z
# Query_time: 3.100000 Lock_time: 0.005000 Rows_sent: 20 Rows_examined: 98000
SELECT * FROM orders WHERE user_id = 2002 ORDER BY create_time DESC LIMIT 20;
"""
# 把 SQL 中的数字替换成 N，得到模板
def normalize(sql):
return re.sub(r'\b\d+\b', 'N', sql)
stats = defaultdict(lambda: [0, 0.0]) # [次数, 总耗时]
current_time = None
for line in sample.splitlines():
line = line.strip()
if line.startswith('# Query_time:'):
# 提取 Query_time 后面的秒数
qt = float(line.split('Query_time:')[1].split()[0])
pending_time = qt
elif line.upper().startswith('SELECT'):
tpl = normalize(line)
stats[tpl][0] += 1
stats[tpl][1] += pending_time
# 按总耗时倒序输出
for sql, (cnt, total) in sorted(stats.items(), key=lambda x: -x[1][1]):
print(f"count={cnt} total={total:.2f}s {sql}")
这段代码在本机的实跑输出是：
count=2 total=5.60s SELECT * FROM orders WHERE user_id = N ORDER BY create_time DESC LIMIT N;
mysqldumpslow 本质上就是这套思路，只是它的归一化规则更复杂（会去掉字符串字面量、注释等）。
3 使用 mysqldumpslow 分析慢日志
mysqldumpslow 是 MySQL 官方自带的慢日志分析工具，它会把相似的 SQL 归一化（把具体值替换成 N），再按次数、总耗时排序，让你一眼看出"哪类 SQL 最慢、调用最频繁"。
它的常用参数如下表：
| 参数 | 含义 |
|---|---|
| -s t | 按总耗时排序（top） |
| -s c | 按调用次数排序（count） |
| -s r | 按返回行数排序 |
| -s a | 不做 SQL 归一化，显示完整 SQL |
| -n N | 只显示包含至少 N 个数字的 GROUP BY 名称 |
| -t N | 只输出前 N 条 |
| -g pattern | 只匹配包含 pattern 的 SQL |
下面是生产环境最常用的几个命令示例（Linux shell）：
# 按总耗时倒序，看最慢的前 10 类 SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按调用次数倒序，看最频繁的前 20 类 SQL
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log
# 只看包含 "order by" 的慢 SQL，按耗时排序
mysqldumpslow -s t -t 10 -g "order by" /var/log/mysql/slow.log
# 按扫描行数排序，找出"扫了很多行才返回几行"的 SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log
一条典型的 mysqldumpslow 输出长这样：
Reading mysql slow query log from /var/log/mysql/slow.log
Count: 1234 Time=2.50s (3085s) Lock=0.01s (12s) Rows=10.0 (12340), root[root]@localhost
SELECT * FROM orders WHERE user_id=N ORDER BY create_time DESC LIMIT N
含义拆解：这条 SQL 一共出现了 1234 次，平均每次耗时 2.50 秒，累计耗时 3085 秒（约 51 分钟），平均锁等待 0.01 秒，平均返回 10 行。"累计耗时"是最值得关注的指标——单次慢但调用少的 SQL，远不如"每次 0.5 秒但每天被调 100 万次"的 SQL 伤害大。
如果觉得 mysqldumpslow 输出不够直观，业界更常用的是 pt-query-digest（Percona Toolkit 工具集），它能生成更详细的报告，包括直方图、执行计划对比等。但 mysqldumpslow 是零依赖的，应急排查时最快。
4 定位慢 SQL 的其他手段
慢查询日志是事后分析，但在问题正在发生时，你需要的是"现在谁在拖库"。这就要靠实时视图。
4.1 查看当前正在执行的会话
-- MySQL 8.0 推荐写法：查看所有正在执行的线程
SELECT
id, -- 连接 ID
user, -- 用户名
host, -- 来源 IP
db, -- 当前库
command, -- 正在执行的命令（Query/Sleep）
time, -- 已执行秒数
state, -- 当前状态
info -- 正在执行的 SQL
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;
如果发现某条 SQL 已经跑了 60 秒还没结束，可以用 KILL 强行终止：
-- 杀掉指定连接（id 来自上一步查询结果）
KILL QUERY 12345; -- 只终止该连接正在执行的 SQL，连接保留
-- 或者
KILL 12345; -- 直接断掉连接
4.2 性能 Schema：events_statements_summary_by_digest
MySQL 5.6 之后引入的 performance_schema 是更强大的实时统计视图。其中 events_statements_summary_by_digest 会按 SQL 指纹（digest）聚合历史执行情况，非常适合"看最近一周哪类 SQL 最慢"：
-- 按平均执行时间倒序，找出最慢的 10 类 SQL
SELECT
digest, -- SQL 指纹（归一化模板）
digest_text, -- 完整 SQL 模板
count_star, -- 执行次数
round(sum_timer_wait/1e12, 2) AS total_sec, -- 总耗时（秒）
round(avg_timer_wait/1e12, 2) AS avg_sec, -- 平均耗时（秒）
round(sum_rows_examined/count_star, 0) AS avg_rows_examined -- 平均扫描行数
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;
注意 timer_wait 的单位是皮秒（1e12 皮秒 = 1 秒），查询时一定要换算，否则数字大得看不懂。
4.3 开启 Profiling
对单条 SQL 做逐阶段耗时分解，可以用 profiling（MySQL 8.0 中已废弃，但仍可用，推荐改用 performance_schema 的 events_stages）：
-- 开启 profiling
SET profiling = 1;
-- 执行你要分析的 SQL
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;
-- 查看刚才所有查询的耗时列表
SHOW PROFILES;
-- 查看上一条 SQL 每个阶段的耗时（Sending data、Sorting result 等）
SHOW PROFILE FOR QUERY 1;
Profiling 能告诉你这条时间到底花在"锁等待"、"发送数据"还是"排序"上，是单 SQL 深度调优的利器。
5 常见慢查询原因诊断
拿到一条慢 SQL，不要上来就加索引。先问自己：它为什么慢？常见原因有以下几类：
| 原因类型 | 典型表现 | 排查手段 |
|---|---|---|
| 没走索引 | Rows_examined 远大于 Rows_sent | EXPLAIN 看 type=ALL |
| 索引失效 | 本来该走索引却没走 | 看 key 字段、函数/隐式转换 |
| 回表过多 | 扫描行数少但回表多 | 看 Extra 是否用了覆盖索引 |
| 排序 filesort | 大结果集排序 | Extra 出现 Using filesort |
| 临时表 | GROUP BY / DISTINCT 超内存 | Extra 出现 Using temporary |
| 锁等待 | SQL 本身快但 state 是 Lock wait | 看 InnoDB 锁等待视图 |
| 网络/客户端慢 | Sending data 阶段长 | 看返回行数与数据包大小 |
下面这条 SQL 是经典的"索引失效"反例：
-- 订单表有索引 idx_user_id(create_time, user_id)，但下面这种写法走不到索引
SELECT * FROM orders
WHERE DATE(create_time) = '2026-10-01' -- 对索引列用了函数
AND user_id = 1001;
DATE(create_time) 让索引列上包了一层函数，MySQL 无法使用 B+ 树的有序性，只能退化为全表扫描。改写方式是把函数挪到右边：
-- 改写为范围查询，能命中索引
SELECT * FROM orders
WHERE create_time >= '2026-10-01 00:00:00'
AND create_time < '2026-10-02 00:00:00'
AND user_id = 1001;
另一个高频坑是隐式类型转换：字段是 varchar，SQL 里却用数字比较，MySQL 会自动把字段转成数字，同样导致索引失效。
5.1 用 EXPLAIN 现场取证
当你怀疑某条 SQL 慢，最快的取证方式是 EXPLAIN。关键看四列：
| EXPLAIN 列 | 关注什么 | 健康值 |
|---|---|---|
| type | 访问类型 | 至少 range，最好 ref/const |
| key | 实际用到的索引 | 不应为 NULL |
| rows | 预估扫描行数 | 与返回行数同数量级 |
| Extra | 附加信息 | 不要有 Using filesort / Using temporary |
-- 对疑似慢 SQL 做现场取证
EXPLAIN
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;
一次典型的 EXPLAIN 输出（节选）：
+----+-------------+--------+------+---------------+------+----------+-----------------------------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+-------------+--------+------+---------------+------+----------+-----------------------------+
| 1 | SIMPLE | orders | ALL | NULL |1000W | 1.00 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+----------+-----------------------------+
type=ALL、key=NULL、Extra 里还有 filesort——这三个信号叠加，基本就是"该加联合索引"的铁证。建完索引再 EXPLAIN，type 应变成 ref、rows 骤降、filesort 消失。
5.2 区分"SQL 慢"和"数据库慢"
慢 SQL 不等于数据库有问题。下面这张对比表帮你快速定位方向：
| 现象 | SQL 本身慢 | 数据库整体慢 |
|---|---|---|
| 单条 SQL 耗时高 | ✅ | 可能 |
| 所有 SQL 都慢 | ❌ | ✅ |
| CPU 高 | 可能（计算多） | 是（锁/IO） |
| IO 高 | 可能（扫表多） | 是 |
| 连接数打满 | ❌ | 是 |
如果是"所有 SQL 都慢"，优先排查系统层：磁盘 IO 是否打满、是否有长事务持锁、是否有大批量 DDL 在跑、备份任务是否挤占 IO。
6 SQL 改写与优化策略
拿到慢 SQL 后，常见的优化手段可以按"成本从低到高"排列：
1. 加索引：成本最低，收益最直接，但索引不是越多越好（影响写入）。
2. 改写 SQL：避免 SELECT *、避免子查询、改写 JOIN 顺序、用覆盖索引。
3. 业务层优化：分页深翻页、冷热分离、冗余字段、异步化。
4. 架构层：读写分离、分库分表、引入 ES 等搜索引擎。
下面给几个典型改写示例。
6.1 避免 SELECT *
-- 慢：查了 20 个字段，但页面只用 3 个
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;
-- 快：只查需要的字段，且可能命中覆盖索引
SELECT order_id, status, amount FROM orders
WHERE user_id = 1001
ORDER BY create_time DESC
LIMIT 20;
6.2 深分页优化
LIMIT 100000, 20 会让 MySQL 扫描 100020 行再丢弃前 100000 行。改用"游标分页"：
-- 慢：OFFSET 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 快：记住上一页最后一条的 id，下次从它之后接着扫
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;
6.3 用 JOIN 替代子查询
-- 慢：相关子查询，每行都要执行一次
SELECT o.order_id,
(SELECT user_name FROM users WHERE users.id = o.user_id) AS user_name
FROM orders o
WHERE o.create_time > '2026-10-01';
-- 快：一次 JOIN 搞定
SELECT o.order_id, u.user_name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.create_time > '2026-10-01';
6.4 批量 IN 替代多次单查
-- 慢：应用循环发 100 次 SQL
-- SELECT * FROM orders WHERE id = 1; ... SELECT * FROM orders WHERE id = 100;
-- 快：一次 IN 查完
SELECT * FROM orders WHERE id IN (1,2,3,...,100);
IN 的元素数量建议控制在 500 以内，过多会让优化器选择执行计划时变迟钝。
7 慢查询优化速查表
| 场景 | 症状 | 第一步排查 | 常用手段 |
|---|---|---|---|
| 单接口慢 | APM 显示 SQL 耗时高 | 抓 SQL 跑 EXPLAIN | 加索引/改写 |
| 批量任务慢 | 夜间批处理超时 | 看扫描行数 | 分批、加 WHERE 限制 |
| 列表页慢 | 翻到后面越来越慢 | EXPLAIN 看 type | 游标分页、覆盖索引 |
| 报表慢 | 跑一次几十秒 | 看是否 filesort/temporary | 汇总表、预聚合 |
| 突发慢 | 突然所有接口变慢 | SHOW PROCESSLIST | 杀会话、看锁等待 |
| 锁等待 | SQL state 是 lock wait | information_schema.INNODB_TRX | 查阻塞源、KILL |
| 索引失效 | 本来该走却没走 | 看函数/类型转换 | 改写 WHERE 条件 |
| 全表扫描 | type=ALL | 看 WHERE 字段 | 建联合索引 |
配套的常用命令速查：
-- 一键看当前最耗时的实时 SQL
SELECT id, time, state, info FROM information_schema.PROCESSLIST
WHERE command != 'Sleep' ORDER BY time DESC LIMIT 20;
-- 一键看慢日志是否开启
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 一键按指纹聚合慢 SQL
SELECT digest_text, count_star, avg_timer_wait/1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;
8 常见坑与注意事项
坑 1：long_query_time 设成 0，以为能抓所有慢 SQL。
阈值为 0 意味着所有 SQL 都进慢日志，高并发下磁盘瞬间被刷爆。建议 0.5~1 秒，配合应用层监控补充毫秒级视角。
坑 2：开了慢日志却忘了看。
慢日志不是开了就完事，必须有定期（每天/每周）的分析动作，否则日志躺在磁盘上毫无价值。成熟团队会用 Piwik/pt-query-digest 把慢日志自动归档、聚合、告警。
坑 3：慢 SQL 一定是 SQL 本身的问题。
很多时候 SQL 慢是因为锁等待、磁盘 IO 打满、网络抖动。拿到慢 SQL 先看 state 字段是 Sending data 还是 Waiting for lock，再决定方向。
坑 4：盲目加索引。
加索引前先看 EXPLAIN，确认确实是索引问题。否则可能加了 5 个索引，写入性能掉一半，查询却没快多少。
坑 5：mysqldumpslow -s t 只看平均耗时。
平均耗时容易被个别离群值带偏。务必同时看 count（调用次数）和 sum（总耗时），总耗时高的 SQL 才是优化优先级最高的。
坑 6：忘记 log_queries_not_using_indexes。
生产环境一定要打开这个开关，否则那些"执行时间不长但全表扫描"的 SQL 永远不会被发现，等数据量涨上来就晚了。
9 练习题与参考答案
练习 1：某电商订单表 orders 有 1000 万行，用户反馈"我的订单"页面加载要 5 秒。该页面 SQL 形如 SELECT * FROM orders WHERE user_id=? ORDER BY create_time DESC LIMIT 20。请说出你会怎么排查和优化？
<details>
<summary>参考答案</summary>
1. 先抓慢 SQL，用 EXPLAIN 看是否走索引；
2. 如果 user_id 没索引，建联合索引 idx_user_create(user_id, create_time)，让排序也走索引；
3. 把 SELECT * 改成只查页面需要的字段，尽量命中覆盖索引；
4. 确认回表次数：EXPLAIN 中 rows 与 filtered 字段；
5. 若仍慢，考虑分页改为游标式（基于 create_time + id）。
</details>
练习 2：mysqldumpslow 输出某条 SQL 的 Count: 1000, Time=0.2s, Rows=100.0，另一条 Count: 2, Time=20s, Rows=10000.0。你会优先优化哪条？为什么？
<details>
<summary>参考答案</summary>
优先优化第一条。第一条总耗时 = 1000 × 0.2s = 200 秒，第二条总耗时 = 2 × 20s = 40 秒。优化要先看"累计伤害"，调用频次高的 SQL 哪怕单次快，总影响也更大。
</details>
练习 3：为什么对索引列 create_time 用 DATE(create_time) = '2026-10-01' 会导致索引失效？怎么改写？
<details>
<summary>参考答案</summary>
B+ 树索引是按 create_time 的原始值有序存储的。对列用函数后，MySQL 无法用索引的有序性快速定位，只能逐行计算函数值，退化为全表扫描。改写为范围查询：create_time >= '2026-10-01 00:00:00' AND create_time < '2026-10-02 00:00:00'。
</details>
练习 4：生产环境突然所有接口都慢，你第一时间会执行哪几条 SQL？
<details>
<summary>参考答案</summary>
1. SHOW FULL PROCESSLIST 按 time 倒序，看是否有大 SQL 拖库；
2. 查 information_schema.INNODB_TRX 看是否有长事务持锁；
3. 查 performance_schema 看是否有突发的连接数或 IO 飙升；
4. 必要时 KILL 阻塞的会话。
</details>
下一篇预告：第 20 章 锁机制与并发控制
文章回复
0 条公开回复