MySQL慢查询治理最佳实践：定位到优化上线完整方案

2026-06-11阅读 0热度 0

最佳实践

今日关键词：MySQL慢查询、EXPLAIN、pt-query-digest、索引优化、执行计划

之前讲过慢查询的诊断方法，也拆解过EXPLAIN执行计划，不少朋友反馈说：“工具我都会用了，可真遇到数据库变慢的时候，还是不知道第一步该干什么。”

这个问题其实很普遍。单独学工具和在真实的生产环境里扛住压力排查，完全是两码事。想象一下，数据库的CPU突然飙到90%，开发群里催着“接口全超时了”，而你手里攥着一堆命令却不知道先跑哪一个——这种手忙脚乱的场景，绝非个例。

那今天就从“发现数据库变慢”这个起点出发，完整地走一遍排查和优化流程。不单独讲某个工具怎么用，而是重点聊一聊“碰到问题时，如何把这些工具串联起来”。

一、数据库变慢了，第一步看什么

收到告警或者开发反馈，先别急着上工具。首要任务是判断慢在哪里。

-- 看当前连接和查询状态
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

重点关注三个指标：

Threads_running 是否过高——也就是正在执行SQL的连接数
是否存在执行了几十秒甚至几百秒的长时间查询
是否有大量连接卡在 Sending data 或 Sorting result 状态

SHOW STATUS LIKE 'Threads%';

Threads_connected 代表总连接数（包含Sleep状态），而 Threads_running 才是真正在干活的线程。如果 Threads_running 平时只是个位数，突然飙到几十，那基本可以确定有SQL在“作妖”。

同时，也别忽略慢查询日志：

# 看最近的慢查询
tail -50 /var/log/mysql/slow.log | grep -i "Query_time"

如果慢查询日志还没开启，先把它打开：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

这三个参数的含义依次是：开启慢查询日志、记录超过1秒的SQL、记录没有使用索引的查询。建议生产环境一直保持开启，long_query_time 设为1秒或者0.5秒都可以。

二、找到最该优化的SQL

慢查询日志开启后，会收到一大堆SQL，但不可能挨个去优化。需要做的就是排个优先级。

mysqldumpslow 快速看概览

# 按总耗时排序，看最耗时的10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按平均耗时排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
# 按执行次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

mysqldumpslow 能快速看出哪类SQL最耗时，但信息相对粗糙。

pt-query-digest 深入分析

生产环境更推荐用 pt-query-digest，它提供的信息量要大得多：

# 分析最近24小时的慢查询
pt-query-digest --since=24h /var/log/mysql/slow.log

查看输出时，重点留意这几列：

Query_time max — 最长执行时间
Rows_examined a vg — 平均扫描行数
Rows_sent a vg — 平均返回行数

如果 Rows_examined 是50万，而 Rows_sent 只有100，说明扫描了50万行才返回100行——这通常是典型的、需要加索引的场景。

pt-query-digest 的输出会按SQL指纹（fingerprint）聚合，同一种SQL只出现一次，但会明确告诉你它执行了多少次、总耗时是多少。优先优化总耗时最高的那条SQL。

performance_schema 看实时数据

如果不想翻阅日志，也可以直接从内存里查：

SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT / 1000000000000 AS sum_sec,
  A VG_TIMER_WAIT / 1000000000000 AS a vg_sec,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY sum_sec DESC
LIMIT 10;

这个视图按SQL指纹汇总了从MySQL启动以来的执行统计，即使不开慢查询日志也能直接看到。

三、EXPLAIN 看执行计划

找到需要优化的SQL后，下一步就是搞清楚它为什么慢。只需在SQL前面加上 EXPLAIN：

EXPLAIN SELECT * FROM orders
WHERE user_id = 1000 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;

输出里有12个字段，新手容易看花眼。实际上，只要盯住其中三个关键字段就够了。

type 字段——判断是否走索引

按性能从好到差排列：

type值	含义	性能评价
const	主键或唯一索引精确匹配	最优
eq_ref	JOIN时主键/唯一索引匹配	很好
ref	普通索引匹配	好
range	索引范围查询	尚可
index	全索引扫描	一般
ALL	全表扫描	极差

一旦看到 ALL，就需要引起警惕——全表扫描在大表上是真正的性能杀手。

Extra 字段——隐藏的关键信息

Extra值	含义	是否需要优化
Using index	覆盖索引，不回表	无须优化，这是好事
Using index condition	索引下推	通常不需要
Using filesort	额外排序	需要优化
Using temporary	使用了临时表	需要优化
Using where	回表后过滤	视情况而定

如果 Using filesort 和 Using temporary 同时出现，那这条SQL基本逃不掉要被优化。

rows 字段——扫描了多少行

rows 是MySQL估算的扫描行数。扫描500万行返回20行，一看就知道有问题。

这里说一个之前踩过的坑：EXPLAIN显示走了索引（type=ref），但rows还是几十万。原因在于索引的区分度太低——一个 status 字段只有5个值，索引建在上面就跟没建差不多。后来改成复合索引，问题才解决。

四、六个高频慢查询场景

了解分析方法之后，来看六个生产环境中最常见的慢查询场景，每个都附上改写方案。

场景一：SELECT * 拖慢性能

-- 问题写法
SELECT * FROM orders WHERE user_id = 1000;
-- 优化：只查询需要的字段
SELECT id, order_no, status, amount, created_at
FROM orders WHERE user_id = 1000;

SELECT * 的问题不仅仅是多传了数据。在InnoDB里，如果查询的字段全都在索引中（覆盖索引），MySQL可以直接从索引返回数据，而不需要回表。SELECT * 会迫使MySQL去聚簇索引里捞所有字段，白白增加一次IO开销。

场景二：深分页

-- 问题写法：跳过100万行取20行
SELECT * FROM orders
WHERE status = 'completed'
ORDER BY id DESC
LIMIT 1000000, 20;

MySQL会扫描100万+20行，然后丢弃前100万行，只返回最后20行。offset越大，速度越慢。

这里提供三种改写方式：

-- 方法一：游标分页（推荐，前提是知道上一页最后一条的id）
SELECT * FROM orders
WHERE status = 'completed' AND id < 1000020
ORDER BY id DESC
LIMIT 20;

-- 方法二：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders
            WHERE status = 'completed'
            ORDER BY id DESC
            LIMIT 1000000, 20) t ON o.id = t.id;

-- 方法三：覆盖索引 + 子查询（介于两者之间）

游标分页的性能是最好的，但前端需要配合改成“加载更多”模式。如果产品执意要支持“跳到第5000页”的功能，那就用延迟关联。

场景三：COUNT(*) 慢

-- 问题写法
SELECT COUNT(*) FROM orders WHERE status = 'pending';

大表上如果 status 没有索引，那就是全表扫描几百万行。

-- 加索引
ALTER TABLE orders ADD INDEX idx_status (status);

-- 对精度要求不高时，直接查元数据
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';

如果业务允许几秒的延迟，使用汇总表定时刷新也是个可行的方案。

场景四：JOIN 太多表

四张表以上的JOIN，执行计划就开始变得不可控。关键点在于确保JOIN字段有索引，并且遵循小表驱动大表的原则。

-- 确保连接字段有索引
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

如果JOIN性能实在扛不住，可以考虑拆分成多次查询，在应用层进行组装。MySQL优化器并不总是能选出最优的JOIN顺序。

场景五：OR 条件导致索引失效

-- 问题写法
SELECT * FROM users
WHERE name = '张三' OR email = 'zhangsan@test.com' OR phone = '13800138000';

OR条件可能导致MySQL放弃索引，直接进行全表扫描。可以改写成UNION：

SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'zhangsan@test.com'
UNION
SELECT * FROM users WHERE phone = '13800138000';

这样每个分支都能走各自的索引，效率会高很多。前提是三个字段都建有独立的索引。

场景六：IN 里面套子查询

-- 问题写法
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE created_at > '2026-01-01');

在老版本MySQL中，处理IN子查询的效率很差，外层每取一行都要跑一次子查询。建议改成JOIN：

SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.created_at > '2026-01-01';

MySQL 8.0 对IN子查询做了半连接优化，很多时候会自动转换。但写成JOIN的形式更保险，行为也更可控。

五、配置参数：改几个关键的就够了

SQL优化完成后，如果还有提升空间，可以考虑调整配置参数。但切忌一上来就改几十个参数，先从影响最大的几个入手。

innodb_buffer_pool_size

这是InnoDB最重要的参数，控制数据和索引在内存中的缓存大小。设置太小，会频繁读取磁盘；设置太大，内存又不够用。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

经验值是物理内存的60%~80%。比如16G内存的机器，可以设为10G~12G。

innodb_flush_log_at_trx_commit

该参数控制redo log的刷盘策略：

值	行为	安全性	性能
1	每次commit刷盘	最安全	最慢
2	每秒刷盘，commit只写OS缓存	可能丢1秒数据	快
0	由master thread控制	可能丢更多数据	最快

非金融场景下，设置为2就足够了。金融场景则必须是1。

tmp_table_size 和 max_heap_table_size

这两个参数控制SQL执行过程中使用的内存临时表大小。设置得太小，临时表会落磁盘（Created_tmp_disk_tables 飙升），设置太大又怕内存不够用。

SHOW STATUS LIKE 'Created_tmp%';

如果 Created_tmp_disk_tables 的值很高，可以考虑适当调大这两个参数，一般从256M起步。

sort_buffer_size 和 join_buffer_size

这两个参数分别控制排序和JOIN操作的缓冲区。需要注意，它们是每个连接独立分配的，不能设得太大（比如1000个连接 × 4M = 4G内存就没了）。

一般情况下，4M就够用了。如果EXPLAIN中频繁出现 Using filesort，可以临时调大试试效果。

六、生产环境操作规范

优化方案找到了，接下来要考虑的是如何安全上线。

加索引：大表别直接ALTER

-- 小表可以直接加
ALTER TABLE small_table ADD INDEX idx_name (name);

-- 大表推荐用pt-online-schema-change
pt-online-schema-change --alter "ADD INDEX idx_user_status (user_id, status)" --user=root --password=xxx D=mydb,t=orders --execute --print

-- 或者用gh-ost
gh-ost --host=localhost --port=3306 --database=mydb --table=orders --alter="ADD INDEX idx_user_status (user_id, status)" --execute

在MySQL 5.6之前，大表直接 ALTER TABLE 会锁表，5.6虽然支持Online DDL，但大表加索引依然会占用不少IO。使用 pt-osc 或 gh-ost 更安全，对线上流量基本无感。

上线前必须做的事

# 1. 备份表结构
mysqldump -u root -p --no-data mydb orders > orders_schema.sql

# 2. 在测试环境跑EXPLAIN，确认执行计划变好了
EXPLAIN SELECT ...;

# 3. 选择低峰期执行
# 凌晨2-5点是大多数业务的低峰

# 4. 改完之后验证
SHOW INDEX FROM orders;
EXPLAIN SELECT ...;
-- 再跑一次，确认无误

危险操作红线

这里有几条血的教训，值得牢记：

DELETE和UPDATE必须带WHERE，而且先用SELECT确认影响范围
生产环境禁止执行无LIMIT的全表查询
DDL操作前务必备份，修改配置前备份my.cnf
innodb_buffer_pool_size 在8.0之前不是动态参数，改了要重启

# 修改配置前先备份
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak.$(date +%Y%m%d%H%M%S)

# 检查配置语法
mysqld --validate-config

# 重启后查看error log，确认没有问题
tail -f /var/log/mysql/error.log

七、排查流程速查

数据库变慢了，可以按以下顺序快速排查：

SHOW PROCESSLIST — 看有没有卡住的查询
SHOW STATUS LIKE 'Threads%' — 看活跃线程数
查看慢查询日志 → 用 pt-query-digest — 找到最耗时的SQL
EXPLAIN — 分析执行计划
加索引 / 改写SQL — 实施优化
在测试环境验证 — 确认执行计划变好
低峰期上线 — 改完再跑一遍EXPLAIN
持续观察 — 看CPU、IO、慢查询数是否降下来

如果在过程中发现CPU打满、IO打满或连接打满，可以参考之前的笔记进行处理。

八、避坑清单

序号	坑点	后果	正确做法
1	不开慢查询日志	慢SQL来了都不知道	生产环境一直开启，long_query_time设为1秒
2	只用mysqldumpslow，不用pt-query-digest	信息粗糙，分不清轻重缓急	用pt-query-digest按指纹聚合，优先优化总耗时最高的
3	EXPLAIN只看type不看Extra	以为走了索引就没问题，实际存在filesort	type和Extra都要看
4	SELECT * 写习惯了	多传数据、无法覆盖索引、浪费IO	只查询需要的字段
5	大表直接ALTER TABLE加索引	锁表或IO飙升影响业务	使用pt-online-schema-change或gh-ost
6	上线前不跑EXPLAIN验证	加了索引但优化器没用上	测试环境先跑EXPLAIN确认
7	索引加在区分度低的字段上	加了跟没加差不多	看基数（Cardinality），低于10%的字段不适合单独建索引
8	一次改几十个参数	出问题不知道是哪个改坏的	一次改一两个，观察后再改下一个
9	深分页用大offset硬扛	越到后面越慢	游标分页或延迟关联
10	改完配置不看error log	配置错误导致MySQL起不来	重启后立即tail error.log

总结

慢查询优化的本质，不是某个工具的使用问题，而是一套完整的流程。

工具只是手段。mysqldumpslow 和 pt-query-digest 帮你找到目标，EXPLAIN 帮你理解为什么慢，而索引和SQL改写则是具体的落地动作。把这套流程跑熟了，数据库的性能问题自然就不会慌。

记住一个原则：先理解，再动手。在没看EXPLAIN之前就贸然加索引，跟不看地图就出门一样——方向错了，越努力，离目标越远。