MySQL 慢查询排查与优化实战

问题背景

“数据库变慢了”是运维和 DBA 最常收到的一类反馈,但这句话本身信息量几乎为零——是所有查询都慢,还是某几条特定的查询慢?是从什么时候开始变慢的?变慢之前是否有过变更?如果接手排查时手里没有慢查询日志、没有执行计划分析习惯、没有关键指标监控,只能对着业务代码猜哪一段 SQL 有问题,排查效率会非常低,而且容易在生产环境做出没有依据的”优化”,结果引发新的问题。

慢查询排查不是一门玄学,而是一套有章可循的方法:先用慢查询日志把问题 SQL 找出来,再用 EXPLAIN 看执行计划,再结合索引设计原理判断问题所在,最后验证优化效果。这篇文章按照完整的排查闭环梳理这套方法,并给出可以直接在生产环境审慎使用的命令和配置示例。

需要说明的是,MySQL 不同版本(5.7 与 8.0)在部分系统表结构、部分功能特性上存在差异,文中涉及版本差异的地方会明确指出,不确定的字段请以实际线上版本的官方文档为准,不要把某个版本的特性想当然地套用到另一个版本上。

适用场景

• 业务反馈接口响应变慢,怀疑或已确认是数据库层面的问题
• 需要对上线前的新功能 SQL 做例行的性能评估
• 数据库 CPU、IO 或连接数出现异常,需要定位是否由某些慢查询导致
• 需要建立慢查询的常态化监控和治理机制,而不是每次都被动救火
• 数据量随业务增长逐渐变大后,原来正常的查询开始出现性能劣化,需要重新评估索引设计

核心知识点

存储引擎层面的基础前提

本文的排查方法主要基于 InnoDB 存储引擎展开(目前绝大多数 MySQL 生产环境的默认和主流选择),部分细节(比如行锁机制、在线 DDL 支持)是 InnoDB 特有的特性,如果环境中还存在使用 MyISAM 等其他存储引擎的历史表,相关的锁机制(MyISAM 是表锁而非行锁)和 DDL 行为会有明显不同,排查前建议先确认目标表的存储引擎:  

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'shop';

慢查询日志的工作机制

MySQL 的慢查询日志(slow query log)会记录执行时间超过 long_query_time 阈值的 SQL 语句。默认情况下这个功能通常是关闭的,需要手动开启。同时注意:

• long_query_time 单位是秒,支持小数(比如 0.5 表示 500 毫秒)
• 默认情况下,没有使用索引的查询不一定会被记录,是否记录未使用索引的查询由 log_queries_not_using_indexes 参数单独控制
• 慢查询日志本身会有一定的写入开销,阈值设置过低(比如 0 秒记录所有查询)会在高并发场景下产生大量日志,需要评估磁盘和 IO 影响

EXPLAIN 执行计划的核心字段

EXPLAIN 是判断一条 SQL 性能问题根因最重要的工具,核心关注这几个字段:

• type:访问类型,从好到差大致是 system > const > eq_ref > ref > range > index > ALL。生产环境里看到 ALL(全表扫描)基本就是重点排查对象,但也不能一概而论,小表全表扫描有时反而比走索引更快
• key:实际使用的索引,如果为 NULL 说明没有走任何索引
• rows:MySQL 预估需要扫描的行数,是估算值不是精确值,但足够用来判断量级是否合理
• Extra:额外信息,常见的 Using filesort(需要额外排序)、Using temporary(使用了临时表)都是需要关注的性能信号,Using index(覆盖索引,不需要回表)则是好的信号

索引失效的常见原因

以下是实践中最常遇到的索引失效场景(以 InnoDB 存储引擎为例,不同版本优化器行为可能有细微差异,以实际 EXPLAIN 结果为准而不是死记硬背规则):

1. 在索引列上使用了函数或运算,比如 WHERE YEAR(create_time) = 2026 会导致索引失效,而 WHERE create_time >= ‘2026-01-01’ AND create_time < ‘2027-01-01’ 可以正常走索引
2. 字符串和数字类型不匹配导致的隐式类型转换,比如字段是 VARCHAR 类型但查询条件写的是数字,可能触发隐式转换导致索引失效
3. 使用 LIKE ‘%关键字’ 这种前缀模糊匹配,索引无法利用(LIKE ‘关键字%’ 后缀模糊匹配通常可以走索引)
4. 联合索引没有遵循最左前缀原则,跳过了索引的第一列直接使用后面的列作为条件
5. 使用了 OR 连接了没有分别建立索引的条件,或者优化器判断走索引的成本比全表扫描还高

联合索引的最左前缀原则

如果建立了联合索引 (a, b, c),查询条件是 a,或者 a AND b,或者 a AND b AND c 都可以利用这个联合索引,但如果查询条件只有 b 或者只有 c,联合索引通常无法被有效利用(某些场景下 MySQL 8.0 的优化器可能有跳跃扫描等特殊处理,具体以实际执行计划为准,不要假设一定不能用)。

慢查询的几个典型模式分类

• 缺失索引型:该走索引的地方没有索引,EXPLAIN 的 type 通常是 ALL
• 索引失效型:有索引但因为写法问题没有被利用
• 数据量突增型:之前索引设计合理,但业务数据量增长后,原来的索引选择性下降,或者原来可以接受的全表扫描现在变得不可接受
• 锁等待型:SQL 本身执行很快,但因为等待锁(行锁、表锁、元数据锁)导致响应时间变长,这种情况慢查询日志记录的时间包含了等待时间,容易被误判为 SQL 本身慢
• 资源竞争型:SQL 本身没问题,但数据库整体负载过高(CPU、IO、连接数打满)导致所有查询都变慢

整体排查或实施思路

现象

业务方反馈接口响应变慢,或者监控告警显示数据库响应时间上升、CPU 或 IO 使用率异常。

初步判断

先确认是全局性的变慢(所有查询普遍变慢),还是局部性的变慢(特定几个接口或几类查询变慢)。这个判断直接决定后续排查方向:全局性问题优先看资源层面(CPU、IO、连接数、锁等待),局部性问题优先看具体 SQL 的执行计划。

命令检查、关键指标、根因定位、修复方案、验证结果、回滚预案、复盘总结

这几个环节在”实战步骤”和”排查路径”章节中详细展开,这里先说明整体顺序:先用慢查询日志和 SHOW PROCESSLIST 锁定问题 SQL,再用 EXPLAIN 分析执行计划找到具体原因,再评估索引优化方案并在测试环境验证,最后在生产环境审慎实施并持续观察效果,同时准备好回滚预案应对优化引入新问题的情况。

实战步骤

第一步:确认慢查询日志是否已开启,如未开启则开启

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

预期输出示例:

+------------------------------+----------------------------------+
| Variable_name                | Value                              |
+------------------------------+----------------------------------+
| slow_query_log                | OFF                                |
| slow_query_log_file           | /var/lib/mysql/localhost-slow.log |
| long_query_time               | 10.000000                          |
+------------------------------+----------------------------------+

如果 slow_query_log 是 OFF,说明当前完全没有记录慢查询,需要先开启。可以在线动态开启(无需重启实例):  

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

风险提醒:SET GLOBAL 只对新建立的会话生效,已有的连接不受影响,而且这种在线设置在实例重启后会丢失,需要同步写入配置文件持久化:  

# /etc/my.cnf 或 /etc/mysql/my.cnf 的 [mysqld] 段
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

判断逻辑:long_query_time 设置为 1 秒是常见的排查起点,如果业务对响应时间要求更严格(比如要求 200 毫秒内),可以临时调低到 0.2 秒做针对性排查,但排查结束后建议评估是否要长期维持这么低的阈值,避免日志量过大。

第二步:分析慢查询日志,定位高频或高耗时的 SQL

慢查询日志本身是纯文本格式,直接看原始日志效率很低,推荐用 mysqldumpslow 或 pt-query-digest(Percona Toolkit 提供的工具,如果环境中已安装)做汇总分析。   

# 使用 MySQL 自带的 mysqldumpslow,按平均执行时间排序,查看前 10 条
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

预期输出示例:

Count: 245  Time=2.34s (573s)  Lock=0.00s (0s)  Rows=1.0 (245), root[root]@[192.168.1.50]
  SELECT * FROM orders WHERE customer_id = N AND status = 'S'

判断逻辑:重点关注两类 SQL——单次耗时特别长的(可能是复杂查询或者缺失索引),以及执行频次特别高即使单次耗时不算长但累计影响大的(高频接口的轻微低效会被流量放大)。Count 字段体现频次,Time 字段体现累计耗时,两者结合判断优化的优先级。

如果环境中安装了 pt-query-digest,分析维度更丰富:   

pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_report.txt

输出会包含按耗时占比排序的 SQL 指纹汇总,以及每类 SQL 的执行次数分布、耗时分布,排查复杂场景时信息更全面。

第三步:确认当前是否有正在执行的慢查询或锁等待(实时排查场景)

如果问题正在发生(比如业务方正在反馈”现在就很慢”),不需要等慢查询日志落盘,直接查看当前会话状态。 

SHOW PROCESSLIST;

预期输出示例:

+----+------+-----------+------+---------+------+----------------------------+-----------------------------+
| Id | User | Host      | db   | Command | Time | State                      | Info                         |
+----+------+-----------+------+---------+------+----------------------------+-----------------------------+
| 12 | app  | 10.0.0.5  | shop | Query   | 45   | Waiting for table metadata lock | ALTER TABLE orders ADD COLUMN ... |
| 15 | app  | 10.0.0.6  | shop | Query   | 42   | Sending data                | SELECT * FROM orders WHERE ... |
+----+------+-----------+------+---------+------+----------------------------+-----------------------------+

判断逻辑:重点看 Time 列(该会话已经执行的秒数)和 State 列。如果大量会话的 State 都是 Waiting for table metadata lock 或者 Waiting for lock,说明是锁等待问题,根因通常是有一个长事务或者 DDL 操作占着锁没释放,而不是 SQL 本身效率问题;如果 State 是 Sending data 且 Time 很长,通常说明确实在进行大量数据扫描或计算。

如果是怀疑锁等待问题,MySQL 5.7 及以上可以进一步查询 information_schema 或 performance_schema 相关的锁信息表(具体表名和字段在不同版本间有一定差异,以下以常见的 performance_schema 方式为例,实际字段请对照线上版本文档核实):   

SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

判断逻辑:通过锁等待关系表,可以找到”谁在等谁”的具体阻塞链条,定位到那个持有锁不释放的事务(通常对应一个具体的应用连接或者未提交的手动事务),这是解决锁等待问题的关键线索。

第四步:对定位到的问题 SQL 执行 EXPLAIN 分析

假设通过前面步骤锁定了一条慢 SQL:  

SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';  

EXPLAIN SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

预期输出示例(问题情况):

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 890234 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

判断逻辑:type 是 ALL,key 是 NULL,rows 预估扫描 89 万行,基本确认这是一次全表扫描,是典型的缺失索引场景。下一步应该检查该表当前的索引情况。 

SHOW INDEX FROM orders;

如果发现 customer_id 和 status 都没有相关索引,或者只有单列索引但查询条件是两列组合,需要评估补充合适的联合索引。

MySQL 8.0 版本还可以使用 EXPLAIN ANALYZE 得到实际执行的详细信息(不只是预估),对排查更精确但会真实执行这条 SQL,如果 SQL 本身是写操作或者数据量巨大的查询,在生产环境使用 EXPLAIN ANALYZE 前要谨慎评估执行成本:   

-- 该语句会真实执行查询,而不仅仅是给出执行计划预估,生产环境对大表或高频表要谨慎使用
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

第五步:设计并验证索引优化方案

基于前一步的分析,评估补充联合索引:  

-- 在测试环境先执行,不要直接在生产环境创建索引
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

判断逻辑:联合索引的字段顺序需要结合实际查询模式设计,通常建议把等值查询频率高、选择性好(区分度高)的字段放在前面。这里 customer_id 区分度远高于 status(状态字段通常只有几个可能值),放在前面更合理。

创建索引后重新执行 EXPLAIN 验证效果:  

EXPLAIN SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

预期输出示例(优化后):

+----+-------------+--------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys            | key                      | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_customer_status      | idx_customer_status      | 264     | const,const |    3 | NULL        |
+----+-------------+--------+------+--------------------------+--------------------------+---------+-------------+------+-------------+

判断逻辑:type 从 ALL 变为 ref,rows 从 89 万降到 3,说明索引生效且效果显著。这种量级的改善通常能直接反映到实际查询耗时的下降,但最终结论仍然要用实际执行时间和线上观察数据确认,而不是只看执行计划就下结论。

第六步:在测试环境验证实际执行时间的改善

-- 开启 profiling 观察实际耗时(MySQL 5.7 及以上可用,8.0 里 profiling 也依然可用但官方更推荐用 performance_schema)
SET profiling = 1;
SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';
SHOW PROFILES;

或者更直接的方式,用客户端计时:   

SELECT NOW(3);
SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';
SELECT NOW(3);

判断逻辑:测试环境的数据量和索引统计信息(通过 ANALYZE TABLE 更新)如果和生产环境差异较大,测出的性能提升幅度可能不完全代表生产环境的真实效果,理想情况下测试环境的数据规模应该尽量接近生产环境,或者至少保证数据分布特征相似。

第七步:评估在生产环境创建索引的执行方式

生产环境创建索引是典型的高风险操作,需要评估:

1. 表的大小和创建索引预计耗时:表越大,创建索引耗时越长,期间可能对表产生锁的影响(取决于 MySQL 版本和存储引擎的在线 DDL 支持情况)

-- 查看表的大概行数和数据大小,评估创建索引的成本
SELECT table_name, table_rows,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name = 'orders';

2. InnoDB 的在线 DDL 能力:MySQL 5.6 之后的 InnoDB 对大多数索引操作支持 ALGORITHM=INPLACE 和并发 DML(具体支持范围以实际版本文档为准,不是所有 DDL 操作都能做到完全不锁表),生产环境创建索引建议显式指定算法和锁模式,便于观察和控制:

ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status), ALGORITHM=INPLACE, LOCK=NONE;

风险提醒:如果指定的 LOCK=NONE 无法满足(比如某些操作类型不支持完全不锁表),这条语句会直接报错而不是降级执行,报错后需要重新评估执行方式,而不是去掉 LOCK 限定后不清楚实际影响就直接执行。

3. 是否使用 pt-online-schema-change 等第三方工具:对于超大表或者对锁敏感度极高的场景,如果环境中有 Percona Toolkit,pt-online-schema-change 通过创建影子表、逐步复制数据的方式实现近乎无锁的 DDL,是生产环境大表变更的常见选择,但操作前需要仔细阅读工具文档确认其对触发器、外键等特性的支持情况(某些场景有限制)。
4. 选择低峰期执行,并提前通知业务方本次操作的预期耗时和风险。

第八步:排查 Using filesort 和 Using temporary 相关的慢查询

这两个 Extra 信号在 ORDER BY、GROUP BY、多表关联等场景中很常见,但性能影响差异很大,需要结合具体场景判断严重程度。

EXPLAIN SELECT customer_id, COUNT(*) AS cnt
FROM orders
WHERE create_time >= '2026-09-01'
GROUP BY customer_id
ORDER BY cnt DESC;

预期输出示例:

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+---------------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | orders | range | idx_create_time | idx_create_time | 6     | NULL | 120450 | Using where; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+---------------------------------+

判断逻辑:Using temporary 说明 MySQL 需要借助临时表完成分组或去重,Using filesort 说明排序无法直接利用索引顺序,需要额外一次排序运算。两者同时出现且预估扫描行数(rows)较大时,查询耗时通常会明显增加。

排查方向:

1. 确认排序字段(cnt,即聚合结果)本身是计算得出的,无法通过索引直接排序,这种场景下 Using filesort 往往难以完全避免,优化空间更多在于减少参与聚合的数据量(比如先用更精确的 WHERE 条件缩小范围)
2. 如果是简单的 ORDER BY 索引列,却仍然出现 Using filesort,检查该字段是否真的建立了索引,以及排序方向和索引方向是否匹配(升序索引配合降序排序在部分版本和场景下也可能无法直接利用,需要看具体执行计划确认,不要凭经验假设)
3. 评估调整 sort_buffer_size 是否能缓解(临时提升该参数,让排序尽量在内存中完成而不落盘),但这类参数调整属于全局资源类调整,需要评估内存总量影响,不宜无限调大

SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

判断逻辑:如果临时表大小超过 tmp_table_size 和 max_heap_table_size 中较小的一个,临时表会从内存转为磁盘临时表,性能会有明显下降,可以通过状态变量观察这种情况发生的频率: 

SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

判断逻辑:如果 Created_tmp_disk_tables 相对 Created_tmp_tables 的比例持续偏高,说明大量查询的临时表都落到了磁盘,是一个值得关注的性能信号,但调大相关内存参数前要评估整机可用内存,避免因为调整这类参数导致内存整体超卖。

第九步:分页查询的深度分页性能问题排查

深度分页(比如 LIMIT 100000, 20 这种越往后翻页越慢的场景)是电商、列表类业务里极常见的慢查询模式,值得单独展开。   

EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

判断逻辑:即使 id 是主键且走了索引,MySQL 仍然需要先扫描并跳过前 100000 行,再取后面 20 行,rows 预估值会随着偏移量增大而线性增长,偏移量越大越慢,这不是索引缺失问题,是分页方式本身的固有开销。

常见优化方式(游标分页/延迟关联):   

-- 优化前:偏移量越大越慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化方式一:记录上一页最后一条记录的 id,用条件过滤代替偏移量(要求排序字段单调且有索引)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 优化方式二:延迟关联,先只用主键定位目标区间再回表取完整字段
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) AS t
ON o.id = t.id;

判断逻辑:优化方式一(游标分页)性能最好,但要求业务场景能接受”不能直接跳转到任意页码”这种交互方式变化(常见于无限滚动、下拉加载场景);优化方式二(延迟关联)不改变分页交互方式,通过减少子查询阶段需要扫描的字段宽度来降低整体开销,但仍然存在偏移量增大变慢的固有问题,只是相对优化前有所改善,不是根本性解决方案。选择哪种方式需要和业务方确认交互形式是否可以调整。

第十步:排查涉及多表关联(JOIN)的慢查询

EXPLAIN SELECT o.*, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';

判断逻辑关注点:

1. 驱动表的选择:EXPLAIN 结果中第一行通常是优化器选择的驱动表,确认驱动表是否为经过 WHERE 条件过滤后数据量更小的一方,如果优化器选择不理想,可以通过调整 SQL 写法或者补充统计信息(ANALYZE TABLE)引导优化器做出更好的选择
2. 被驱动表的连接字段是否有索引:customers.id 通常是主键天然有索引,但如果是非主键字段做连接条件,务必确认该字段已建立索引,否则每次关联都相当于对被驱动表做一次全表扫描
3. 关注 Extra 是否出现 Using join buffer:出现这个提示通常说明某一侧关联字段缺少可用索引,MySQL 需要借助连接缓冲区(join_buffer_size)完成关联,大表场景下性能会明显下降

SHOW VARIABLES LIKE 'join_buffer_size';

第十一步:子查询与 JOIN 的等价改写评估

某些历史遗留的子查询写法在特定 MySQL 版本或者特定数据分布下,优化器给出的执行计划可能不理想,可以尝试等价改写为 JOIN 进行对比。  

-- 子查询写法
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'east');

-- 等价改写为 JOIN
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'east';

判断逻辑:MySQL 优化器(尤其是较新版本)对很多子查询场景已经能做出较好的内部优化(比如子查询物化、半连接优化),不能一概而论地认为子查询一定比 JOIN 慢,两种写法都应该通过实际 EXPLAIN 对比,以实际执行计划和执行耗时为准,不要凭过时的经验规则直接下结论。

常用命令

-- 查看当前慢查询相关配置
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看当前所有连接和执行状态
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看某个表的索引情况
SHOW INDEX FROM orders;

-- 查看表的行数、数据量、索引占用估算
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables WHERE table_schema = 'shop';

-- 分析执行计划
EXPLAIN SELECT ...;

-- MySQL 8.0 可用,查看更详细的实际执行信息(会真实执行 SQL,谨慎使用)
EXPLAIN ANALYZE SELECT ...;

-- 更新表的统计信息,让优化器的估算更准确
ANALYZE TABLE orders;

-- 查看当前锁等待情况(需要 performance_schema 已启用,MySQL 5.7+ 默认开启)
SELECT * FROM performance_schema.data_lock_waits;

-- 查看InnoDB引擎的整体状态,包含锁、事务等信息
SHOW ENGINE INNODB STATUS;

-- 杀掉某个异常会话(高风险操作,谨慎使用,详见风险提醒章节)
KILL 12;

配合 pt-query-digest 生成定期报告的脚本示例

#!/usr/bin/env bash
# /opt/scripts/slow_query_daily_report.sh
# 每日定时对昨天的慢查询日志做汇总分析,产出报告并归档旧日志

set -euo pipefail

SLOW_LOG="/var/lib/mysql/slow.log"
REPORT_DIR="/var/log/mysql_slow_reports"
DATE_TAG=$(date -d yesterday '+%Y%m%d')

mkdir -p "$REPORT_DIR"

# 生成汇总报告,不对原始日志做任何修改性操作
pt-query-digest "$SLOW_LOG" > "${REPORT_DIR}/slow_report_${DATE_TAG}.txt"

echo "报告已生成: ${REPORT_DIR}/slow_report_${DATE_TAG}.txt"   

chmod +x /opt/scripts/slow_query_daily_report.sh   

# 每天凌晨 1 点执行(避免整点造成资源使用叠加)
7 1 * * * /opt/scripts/slow_query_daily_report.sh >> /var/log/slow_query_report_cron.log 2>&1

说明:这个脚本只做只读的分析和报告生成,不涉及日志清理或轮转,日志本身的轮转清理需要结合”生产环境注意事项”中提到的 mysqladmin flush-logs 单独处理,两者职责分开,避免脚本逻辑过于复杂导致排查报告生成失败时又意外影响了日志文件本身。

配置示例

my.cnf 中慢查询相关的完整配置片段

[mysqld]
# 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log

# 记录执行超过 1 秒的查询,可根据业务需要调整
long_query_time = 1

# 记录未使用索引的查询,排查阶段建议开启,常态化运行时视日志量评估是否保持开启
log_queries_not_using_indexes = 1

# 记录管理类语句(如 OPTIMIZE、ANALYZE)执行情况,默认可能关闭
log_slow_admin_statements = 1

修改配置文件后需要重启 MySQL 服务生效,或者用 SET GLOBAL 在线调整(注意在线调整重启后会丢失,需要配置文件和在线设置保持一致):   

systemctl restart mysqld

风险提醒:重启 MySQL 服务会导致所有现有连接断开,生产环境的服务重启操作必须在维护窗口内执行,并提前评估业务侵入性,不建议为了单纯调整慢查询相关参数就重启服务,优先使用 SET GLOBAL 在线调整,配置文件同步修改仅用于保证重启后配置不丢失。

索引创建的规范写法示例

-- 明确指定算法和锁模式,便于评估影响和及时发现不符合预期的情况
ALTER TABLE orders
  ADD INDEX idx_customer_status (customer_id, status),
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- 删除不再使用的冗余索引(删除前务必确认没有查询依赖,建议先观察一段时间的慢查询日志和 EXPLAIN 使用情况确认)
ALTER TABLE orders DROP INDEX idx_old_unused, ALGORITHM=INPLACE, LOCK=NONE;

主从复制场景下的慢查询排查注意事项

如果数据库存在主从复制架构,慢查询排查还需要额外关注复制延迟维度,因为从库上的查询压力和复制回放是两件会互相影响的事情。   

-- 在从库上查看复制状态(MySQL 8.0.22 之后官方推荐使用 SHOW REPLICA STATUS,
-- 早期版本使用 SHOW SLAVE STATUS,两者字段基本一致,具体以实际版本支持情况为准)
SHOW REPLICA STATUS\G

关注字段(不同版本字段命名可能存在差异,以实际输出为准):

• Seconds_Behind_Source(新版本命名)或 Seconds_Behind_Master(旧版本命名):复制延迟秒数
• Replica_SQL_Running(新版本命名)或 Slave_SQL_Running:复制回放线程是否正常运行

判断逻辑:如果从库上出现大量慢查询,同时观察到复制延迟持续增长,可能是从库上的慢查询占用了资源,导致复制回放线程得不到足够资源及时同步,这种情况下需要优先解决从库上的慢查询问题,同时评估是否有必要把大查询、报表类查询迁移到专门的只读分析节点,避免和承担实时复制任务的从库产生资源竞争。

结合慢查询问题评估是否需要读写分离或分库分表

排查到一定阶段,如果发现单纯的索引优化已经无法解决问题(比如数据量已经达到单表优化的天花板,或者读写压力本身已经超出单机承载能力),需要评估更大的架构调整,这类调整不属于”慢查询优化”范畴,而是架构层面的容量规划决策,不应该草率决定。

评估的基本信号(需要综合判断,不是任意单一条件满足就应该立即启动架构调整):

• 核心表的数据量已经达到千万级甚至更高,即使索引设计合理,单表查询和维护成本仍然明显偏高
• 读写压力的比例失衡明显,大量只读的报表、统计类查询和核心业务的写入操作产生资源竞争
• 慢查询问题反复出现在同一批核心大表上,且已经排除了索引和 SQL 写法层面的优化空间

判断逻辑:架构层面的调整(读写分离、分库分表)涉及应用层改造成本高,不是单纯运维侧可以独立决定和实施的事情,发现类似信号后应该整理清楚具体的数据和现象(表大小、查询压力分布、已经尝试过的优化及效果),提交给架构评审,而不是运维自行推进。

日志或指标观察方法

慢查询日志的持续观察

# 实时观察新写入的慢查询(排查进行中的问题时使用)
tail -f /var/lib/mysql/slow.log

关键状态变量的观察

-- 查看当前连接数和历史峰值
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看查询缓存命中情况(MySQL 8.0 已移除查询缓存功能,此项仅适用于 5.7 及更早版本)
SHOW STATUS LIKE 'Qcache%';

-- 查看 InnoDB 缓冲池命中率相关状态,命中率过低说明缓冲池可能不足或者存在大量随机 IO
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- 查看当前锁等待和死锁的历史统计
SHOW STATUS LIKE 'Innodb_row_lock%';

判断逻辑:Innodb_buffer_pool_reads 相对 Innodb_buffer_pool_read_requests 的比例如果偏高,说明大量请求没有命中缓冲池,需要从磁盘读取,这通常和缓冲池大小配置(innodb_buffer_pool_size)不足或者查询模式导致的大量随机访问相关。

使用 Performance Schema 做持续的 SQL 性能画像(MySQL 5.7+)

-- 查看按 SQL 归类后的执行统计,可以持续观察哪类 SQL 的累计耗时占比最高
SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms,
       sum_timer_wait/1000000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

说明:performance_schema 需要在配置中启用相关 consumer 和 instrument 才能采集到完整数据,不同版本默认启用的采集项范围有差异,以实际查询到的数据是否完整为准,如果发现数据缺失,检查相关配置项而不是假设功能不可用。

使用慢查询相关的 Prometheus Exporter 做常态化监控

如果团队已经有 Prometheus 监控体系,建议把数据库层面的关键指标也纳入统一监控,而不是只靠人工登录数据库查看状态变量。常见方案是使用 mysqld_exporter。   

docker run -d --name mysqld-exporter --restart unless-stopped \
  -p 9104:9104 \
  -e DATA_SOURCE_NAME="exporter_user:exporter_password@(192.168.1.10:3306)/" \
  prom/mysqld-exporter:latest

风险提醒:DATA_SOURCE_NAME 中包含数据库账号密码,不要以明文形式写入脚本或直接暴露在命令行历史中,生产环境建议通过环境变量文件(并设置严格的文件权限)或者密钥管理服务注入,同时该账号应遵循最小权限原则,仅授予 PROCESS、REPLICATION CLIENT 等监控所需的权限,不要使用具备写权限或管理权限的账号。   

# prometheus.yml 中追加抓取配置
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['192.168.1.10:9104']

常见可监控指标(具体指标名以实际 exporter 版本输出为准):   

mqlmysql_global_status_slow_queries
mysql_global_status_threads_connected
mysql_global_status_innodb_buffer_pool_reads
mysql_global_status_innodb_row_lock_waits

告警规则参考(阈值需要结合业务基线调整,不是固定标准):   

groups:
  - name: mysql_alerts
    rules:
      - alert: MySQLSlowQueryRateHigh
        expr: rate(mysql_global_status_slow_queries[5m]) > 5
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL 慢查询速率异常升高"
          description: "近 5 分钟慢查询产生速率超过每秒 5 条,需要结合当前 pt-query-digest 报告定位具体是哪类 SQL 导致。"

      - alert: MySQLConnectionsNearLimit
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "MySQL 连接数接近上限"
          description: "当前连接数已经超过最大连接数的 80%,需要排查是否有连接泄漏或者突发流量。"

排查路径

现象一:某个具体接口响应变慢,其他接口正常

1. 初步判断:定位到该接口对应的具体 SQL(通过应用日志、慢查询日志或者 SHOW PROCESSLIST 现场抓取)
2. 命令检查:

EXPLAIN <具体SQL>;
SHOW INDEX FROM <相关表>;

3. 关键指标:EXPLAIN 的 type、key、rows、Extra 字段
4. 根因定位:根据前面”索引失效的常见原因”逐项排查,确认是缺失索引、索引失效写法、还是数据量增长导致的选择性下降
5. 修复方案:补充或调整索引、改写 SQL 避免索引失效写法、或者评估是否需要分表分区等更大的架构调整(数据量极大场景)
6. 验证结果:测试环境验证 EXPLAIN 结果和实际执行时间改善,生产环境审慎实施后持续观察该接口的响应时间指标
7. 回滚预案:详见”回滚方案”章节
8. 复盘总结:记录本次问题的具体 SQL 模式、根因、解决方案,补充到团队的 SQL 审查规范或者索引设计检查清单中,避免同类问题在其他表上重复出现

现象二:数据库整体 CPU 或 IO 持续高位,多数查询都变慢

1. 初步判断:先确认是否是某几条高频 SQL 导致的资源消耗集中,而不是所有 SQL 均匀变慢

pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_report.txt

看报告中耗时占比最高的前几类 SQL 指纹,通常少数几类 SQL 就占据了大部分累计耗时(符合常见的二八分布规律)

2. 命令检查:

SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;

结合操作系统层面的资源监控(top、iostat)确认瓶颈具体在 CPU 计算还是磁盘 IO

3. 关键指标:CPU 使用率、磁盘 IO 等待、Innodb_buffer_pool_reads 相对读请求的比例、当前活跃连接数
4. 根因定位:如果是集中在少数几类 SQL,回到”现象一”的排查路径逐条分析;如果是缓冲池命中率整体偏低,考虑是否 innodb_buffer_pool_size 配置不足以覆盖热数据集;如果是连接数异常暴涌,考虑是否有应用层的连接泄漏或者突发流量
5. 修复方案:针对具体根因对应处理,资源类问题(如缓冲池不足)的调整通常涉及实例参数变更,需要评估是否需要重启,优先选择支持在线调整的参数
6. 验证结果:持续观察 CPU、IO、缓冲池命中率等指标是否回落到正常范围
7. 回滚预案:如果是参数调整导致的新问题,恢复到调整前的参数值
8. 复盘总结:评估是否需要建立更完善的资源监控告警(比如缓冲池命中率、连接数)提前发现类似问题,而不是等到明显影响业务才发现

现象三:偶发性的响应变慢,过一段时间自行恢复

1. 初步判断:这类问题往往是慢查询日志按落盘时间才能捕获到,现场排查(比如 SHOW PROCESSLIST)很可能错过窗口,需要依赖历史日志和监控回溯
2. 命令检查:

grep -B5 -A20 "$(date -d '10 minutes ago' '+%y%m%d')" /var/lib/mysql/slow.log | less

结合监控系统回看故障发生时间段的 CPU、连接数、慢查询数量等历史指标曲线

3. 关键指标:故障时间点附近的连接数突增、是否有批量任务(定时任务、报表统计)同期执行
4. 根因定位:偶发性问题很多时候和特定时间点触发的批量任务(比如凌晨的数据同步、报表生成)有关,需要核对任务调度时间和故障时间是否吻合
5. 修复方案:如果确认是批量任务导致的资源争抢,评估是否可以调整任务执行时间到业务低峰期,或者对批量任务本身做限流/分批处理
6. 验证结果:后续观察同一时间点是否还有类似的响应波动
7. 回滚预案:如果任务调度时间调整后引发其他问题,恢复原调度时间,重新评估方案
8. 复盘总结:建立批量任务和数据库资源指标的关联视图,后续新增批量任务前评估其对数据库的影响

现象四:新功能上线后新增了一批慢查询,需要快速定位是哪部分代码引入的

1. 初步判断:对照发布时间点和慢查询日志中新出现的 SQL 模式,确认是否和本次发布内容相关

grep -A5 "$(date -d '1 hour ago' '+%y%m%d %H')" /var/lib/mysql/slow.log | head -50

2. 命令检查:对新出现的可疑 SQL 逐条执行 EXPLAIN,同时对照发布内容中涉及的表和查询逻辑
3. 关键指标:新 SQL 的执行频次(是否为高频接口)和单次耗时
4. 根因定位:常见于新功能引入了新的查询模式,但没有配套设计相应的索引,或者新增的查询条件字段恰好命中了某个索引失效场景
5. 修复方案:短期可以先通过索引补充缓解(如果能够快速评估清楚不会引发负面影响),中长期应该推动上线前的 SQL 评审机制落地,避免同类问题重复发生
6. 验证结果:确认新增索引后 EXPLAIN 执行计划改善,且该功能的实际接口响应时间恢复正常
7. 回滚预案:如果问题紧急且短期内无法通过数据库层面调整解决,评估是否需要联系业务方回滚本次功能发布,数据库层面的调整和应用层面的回滚可以并行推进,不需要互相等待
8. 复盘总结:把这类问题反馈到研发流程中,建议在新功能设计阶段就同步评估涉及表的索引设计,而不是上线后再被动补救

风险提醒

1. 生产环境直接执行 KILL 存在业务影响风险

风险场景:发现某个长时间运行的查询,直接 KILL 掉,但该查询可能是一个正在写入数据的事务,强制终止可能导致事务回滚,进而影响业务数据一致性,或者在应用层触发未预期的错误。

预防措施:执行 KILL 前先通过 SHOW PROCESSLIST 和 Info 字段确认该会话具体在执行什么操作,是否是只读查询还是包含写操作的事务;如果是写操作相关的长事务,要评估终止后应用层是否有妥善的错误处理和重试机制,必要时先联系相关业务方确认。

2. 生产环境创建索引可能引发短暂锁等待或额外资源消耗

风险场景:即便使用了在线 DDL(ALGORITHM=INPLACE, LOCK=NONE),创建索引期间仍然会消耗额外的 CPU 和 IO 资源,大表场景下如果和业务高峰期重叠,可能间接影响业务性能,同时索引创建过程本身也需要时间,期间如果实例发生重启,索引创建操作会失败需要重新执行。

预防措施:选择业务低峰期执行,提前评估预计耗时(可以在数据量相近的测试环境先做一次演练),执行期间持续观察实例整体负载。

3. 隐式类型转换导致索引失效,容易被忽视

风险场景:某个字段是字符串类型,但应用代码传入的查询参数是数字类型,导致隐式类型转换,索引悄无声息地失效,而这类问题往往不会引发报错,只是查询变慢,容易被忽视很长时间才被发现。

预防措施:代码审查阶段留意查询条件的字段类型和实际参数类型是否一致,定期通过慢查询日志分析发现类似的隐性问题。

4. 全局 SET 命令的作用范围容易被误解

风险场景:执行 SET GLOBAL long_query_time = 1 之后,以为所有连接都立即按新阈值记录慢查询,但实际上已经建立的连接会话仍然沿用旧的会话级参数值,这类误解可能导致排查时”为什么日志里没有记录”的困惑。

预防措施:理解 SET GLOBAL 只影响新建立的连接这一机制,排查时如果需要立即生效,考虑重新建立连接或者结合 SET SESSION 针对当前排查用的连接单独设置。

5. DROP INDEX 或 DROP TABLE 是不可逆的破坏性操作

风险场景:清理”看起来没用”的索引或表时,如果判断有误(比如某个低频但关键的报表任务依赖这个索引),删除后可能在业务低频使用场景下才暴露问题,而这类操作一旦执行通常没有简单的撤销方式。

预防措施:删除索引前先通过 performance_schema 或者慢查询日志长期观察确认没有查询路径依赖它;删除表这类更严重的操作,执行前必须先完成数据备份,并明确保留备份的时间窗口。

6. 大批量数据操作(批量更新/删除)引发的慢查询和锁风险

风险场景:一次性对大表执行不带 LIMIT 的批量 UPDATE 或 DELETE,不仅本身执行慢,还可能长时间持有锁,阻塞其他正常业务查询和写入,极端情况下还会产生大量 undo log 导致磁盘空间快速增长。

预防措施:批量操作应该分批次执行,每批处理有限数量的记录并适当间隔,同时评估是否需要在业务低峰期执行:   

-- 分批删除示例思路,实际字段和范围条件需要按业务场景调整
DELETE FROM order_logs WHERE create_time < '2025-01-01' LIMIT 1000;

配合应用层或脚本循环执行,并在每批之间加入短暂间隔,同时持续观察 SHOW PROCESSLIST 和主从复制延迟情况,发现异常及时暂停。

7. ANALYZE TABLE 和 OPTIMIZE TABLE 的执行时机需要谨慎评估

风险场景:ANALYZE TABLE 通常影响较小,但 OPTIMIZE TABLE 对于 InnoDB 表实际上是通过重建表来实现的,大表执行期间会消耗大量 IO 和临时空间,同时可能有锁的影响(具体行为因版本和配置而异)。

预防措施:ANALYZE TABLE 可以在必要时(比如数据分布发生较大变化后)相对放心地执行,但 OPTIMIZE TABLE 这类涉及表重建的操作,大表场景务必安排在低峰期,并提前评估所需的磁盘空间和预计耗时。

8. 修改字符集或排序规则(collation)引发的隐性索引失效

风险场景:表和表之间、或者查询条件的字面值和字段本身的字符集/排序规则不一致(比如一个 utf8mb4_general_ci,另一个 utf8mb4_0900_ai_ci),在多表关联或者条件比较时可能导致索引无法正常利用,这类问题报错信息不一定明显,容易被误判为普通的索引失效问题而忽视了字符集这一层原因。

预防措施:排查索引失效问题时,如果前面提到的常见原因都排除了,补充检查相关字段的字符集和排序规则是否一致:   

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'shop' AND table_name IN ('orders', 'customers');

如果发现不一致,统一字符集和排序规则本身也是一次表结构变更,同样需要按照生产环境 DDL 变更的谨慎流程执行,评估变更成本和必要性。

验证方式

验证索引优化是否生效

EXPLAIN <优化后的SQL>;

对比优化前后的 type、key、rows 字段变化,确认执行计划确实按预期使用了新索引。

验证实际响应时间是否改善

# 在慢查询日志中持续观察该类 SQL 是否还会被记录
tail -f /var/lib/mysql/slow.log | grep "orders"

结合应用层的接口响应时间监控,确认端到端的用户体验是否有实际改善,而不是只停留在 EXPLAIN 层面的理论分析。

验证优化没有引入新的负面影响

-- 确认新增索引没有对其他依赖该表的查询造成负面影响(比如写入性能下降)
SHOW INDEX FROM orders;

-- 观察写入相关的性能指标变化
SHOW STATUS LIKE 'Innodb_rows_inserted';

判断逻辑:索引会加快查询但会增加写入(INSERT/UPDATE/DELETE)时维护索引结构的开销,如果该表是写密集型表,新增索引后需要额外观察写入性能是否出现明显下降,不能只关注查询侧的改善。

验证长期效果:建立索引效果的跟踪机制

一次性的验证只能说明当下有效,索引优化的长期效果同样需要跟踪,尤其是数据量还在持续增长的表。  

-- 定期(比如每月)重新检查关键 SQL 的执行计划,确认索引选择性是否随数据增长发生变化
EXPLAIN SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

判断逻辑:索引的选择性会随着数据分布变化而改变,比如某个状态值的数据量占比大幅增加后,即使索引仍然存在,优化器也可能判断走索引不如全表扫描划算,转而放弃使用该索引。这种情况不是索引失效的 bug,而是数据分布变化带来的合理调整,发现类似变化后,应该重新评估索引设计是否仍然适合当前的数据特征,而不是假设一次优化能长期有效。

回滚方案

• 索引变更回滚:如果新增索引后发现负面影响超出预期(比如写入性能明显下降),可以删除该索引恢复原状:

ALTER TABLE orders DROP INDEX idx_customer_status, ALGORITHM=INPLACE, LOCK=NONE;

• 参数调整回滚:如果调整了 innodb_buffer_pool_size 等实例参数后出现异常(比如内存占用过高影响其他服务),恢复到调整前的数值,涉及需要重启才能生效的参数,回滚同样需要评估重启的业务影响:

 SET GLOBAL long_query_time = 10;

对应静态参数需要修改配置文件后重启:   

# 恢复配置文件中的参数值后
systemctl restart mysqld

• SQL 改写回滚:如果应用层为优化查询修改了 SQL 写法后出现业务逻辑问题(比如改写后返回结果和预期不一致),优先通过应用发布流程回滚代码版本,而不是在数据库层面做补偿性调整。

常见误区

1. 认为索引越多查询越快:忽视了索引本身的维护成本,写密集型表上过多的冗余索引会拖慢写入性能,也会增加存储空间占用,索引设计需要在读写两侧的性能之间找到平衡。
2. 看到 EXPLAIN 里 type 是 ALL 就认定一定要加索引:小表的全表扫描有时候比维护和使用索引的开销更低,是否需要加索引要结合表的数据量级、增长趋势、查询频率综合判断,不是看到 ALL 就机械地加索引。
3. 把慢查询日志的记录时间等同于 SQL 本身的执行时间:慢查询日志记录的时间包含了等待锁的时间,如果不排查清楚等待和真实执行的占比,容易把锁等待问题误判为 SQL 性能问题,进而选错优化方向。
4. 优化了 SQL 却没有配套验证写入性能的影响:只关注查询侧的执行计划改善,忽视了新增索引对写密集场景的影响,导致优化查询的同时悄悄拖慢了写入,整体收益可能被抵消。
5. 把测试环境的性能测试结果直接当作生产环境的预期效果:测试环境的数据量、并发压力、硬件配置往往和生产环境有差异,同样的索引优化在两个环境下的收益幅度可能不同,不能直接照搬测试结论作为生产环境效果的保证。

附:排查工具速查

工具/命令	用途	使用场景
SHOW PROCESSLIST	查看当前活跃会话	现场排查正在发生的问题
EXPLAIN	查看执行计划	分析单条 SQL 的性能问题根因
EXPLAIN ANALYZE(MySQL 8.0+)	查看实际执行的详细信息	需要比预估更精确的分析,注意会真实执行
mysqldumpslow	慢查询日志汇总	快速找到高频或高耗时 SQL
pt-query-digest	更全面的慢查询日志分析	需要更详细的耗时分布和 SQL 指纹归类
SHOW ENGINE INNODB STATUS	查看 InnoDB 引擎内部状态	排查锁、事务、缓冲池相关问题
performance_schema 相关表	细粒度的锁和 SQL 执行统计	需要精确定位锁等待链条或 SQL 性能画像
mysqld_exporter + Prometheus	常态化指标监控	建立长期趋势观察和告警能力

生产环境注意事项

1. 任何 DDL 操作前先确认表的当前状态:执行索引变更前,先用 information_schema.tables 确认表的行数量级,大表和小表的操作策略应该不同
2. 变更前进行数据备份:涉及删除索引、删除表这类不可逆操作前,确保有可用的备份,并验证过备份的可恢复性,不能只是”执行了备份命令”就认为足够安全
3. 避免在业务高峰期执行有资源消耗的排查或变更操作:包括 EXPLAIN ANALYZE(会真实执行)、创建索引、ANALYZE TABLE 等操作,都建议安排在低峰期
4. 区分只读排查命令和有副作用的命令:SHOW PROCESSLIST、EXPLAIN(不带 ANALYZE)、SHOW INDEX 等是安全的只读操作,可以随时执行;KILL、ALTER TABLE、EXPLAIN ANALYZE 等有副作用或者会真实执行 SQL 的命令,需要更谨慎的评估
5. 慢查询日志本身要纳入日常维护:日志文件会持续增长,需要有轮转清理机制,避免占满磁盘影响数据库服务本身

# 简单的日志轮转示例,实际生产环境建议用 logrotate 统一管理
mv /var/lib/mysql/slow.log /var/lib/mysql/slow.log.$(date +%Y%m%d)
mysqladmin flush-logs

6. 索引不是越多越好:每个索引都有维护成本,定期(比如每季度)审视是否存在冗余或很少被使用的索引,而不是持续叠加新索引却不清理旧索引
7. 建立 SQL 上线前的评审机制:新功能上线前,对涉及的核心 SQL 做 EXPLAIN 评审,把问题挡在上线之前,比线上救火成本低得多
8. 权限最小化:排查和优化过程中使用的账号,权限应该按需授予,避免用具备高危操作权限(比如 DROP、SUPER)的账号做日常排查工作,降低误操作的影响范围
9. 变更需要留痕:每一次索引调整、参数变更都应该记录清楚时间、原因、执行人、预期效果,方便后续排查时能快速关联”这次异常是否和某次变更相关”,可以简单地用一份变更记录文档或者工单系统承载,不需要复杂的工具
10. 不要在业务代码之外偷偷做数据修复:排查过程中如果发现数据本身存在异常(比如脏数据导致某些查询条件命中了大量不该匹配的记录),不要直接手动修改生产数据,应该走正规的数据修复流程(评估影响范围、备份、业务方确认、审批),避免绕过流程的数据变更引发新的一致性问题
11. 建立慢查询问题的知识库:把排查过程中遇到的典型问题模式、根因、解决方案整理成团队内部可查阅的文档,新人接手排查工作时可以先查阅历史案例,而不是每次都从零开始摸索

附:与应用层配合的建议

数据库层面的优化只能解决一部分问题,很多慢查询根源实际上在应用层的设计,比如:

• N+1 查询问题:应用层循环中对每条记录单独发起一次数据库查询,而不是批量一次查询,这类问题在数据库层面很难通过索引优化根本解决,需要应用层改写为批量查询(如使用 IN 条件一次性获取,或者引入合适的数据加载器模式)
• 不必要的 SELECT *:只需要少数字段却查询全部字段,增加了网络传输和内存开销,尤其是在有大字段(如 TEXT、BLOB 类型)的表上影响更明显,建议按需选择具体字段
• 缺乏合理的缓存层:对于变化不频繁但访问量很大的数据(比如商品基础信息),如果每次都直接查询数据库而没有缓存层(如 Redis)承担,数据库承受的压力会远超实际必要的水平

判断逻辑:如果排查后发现问题的根源在应用层设计而不是数据库配置或索引,数据库侧的优化空间有限,这时候应该把问题和具体分析结果反馈给研发团队,推动应用层的改造,而不是试图仅仅通过数据库层面的调整”硬扛”设计缺陷带来的压力。

附:不同 MySQL 版本间需要留意的差异提醒

这篇文章中提到的命令和字段,大部分在 MySQL 5.7 和 8.0 中都可用,但以下几点差异需要额外留意,实际操作前建议核对线上环境的具体版本:

• SHOW SLAVE STATUS 与 SHOW REPLICA STATUS:8.0.22 及之后的版本官方开始推荐使用新的 REPLICA 相关术语和语句,旧版本沿用 SLAVE 相关命名,两者字段基本对应,但不能假设所有历史版本都同时支持新语法
• 查询缓存(Query Cache):MySQL 8.0 已经完全移除该功能,相关的 Qcache 状态变量在 8.0 上不存在,仅在 5.7 及更早版本适用
• EXPLAIN ANALYZE:是 MySQL 8.0.18 引入的新功能,5.7 版本不支持,5.7 环境下需要用其他方式(比如结合 profiling 或者应用层计时)间接评估实际执行开销
• performance_schema 的默认启用范围和默认采集项,不同版本、不同发行版(社区版/企业版/云厂商托管版本)可能有差异,具体可用的统计维度以实际查询结果为准,不要假设所有环境都开放了相同粒度的统计数据

版本差异不是排查中最核心的部分,但如果忽视这些差异直接照搬命令,可能得到语法错误或者数据缺失的结果,进而误判为”排查方法本身有问题”,实际上只是版本不匹配导致的正常现象。

附:一次完整排查的复盘案例(示例场景,细节已做脱敏简化)

为了把前面分散的排查方法串成一条完整链路,下面用一个简化后的完整案例走一遍全流程。

现象:某电商系统的”我的订单”列表接口,在晚上 8 点到 10 点的业务高峰期响应时间从平时的 200 毫秒左右上升到 3-5 秒,白天其他时段基本正常。

初步判断:先确认是否为局部性问题——检查同期其他接口(如商品详情、购物车)的响应时间是否也同步上升。查询监控发现只有订单列表接口受影响,其他接口正常,判断为局部性问题,优先排查该接口对应的具体 SQL,而不是整体资源问题。

命令检查:  

SHOW PROCESSLIST;

发现晚高峰时段有多个会话执行类似 SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC LIMIT 20 的查询,Time 字段普遍在 3-5 秒。   

EXPLAIN SELECT * FROM orders WHERE user_id = 88123 ORDER BY create_time DESC LIMIT 20;

输出显示 type 为 ref,使用了 idx_user_id 索引,但 Extra 出现 Using filesort,rows 预估值达到 4500。

关键指标:rows 预估扫描行数偏高,Using filesort 说明排序无法利用索引顺序完成。

根因定位:该表已有的索引是单列索引 idx_user_id (user_id),只能定位到某个用户的订单,但排序字段 create_time 不在索引中,MySQL 需要先用索引找到该用户的所有订单(某些活跃用户订单量可能达到数千条),再对结果做一次额外的文件排序才能返回前 20 条。这个操作本身在低峰期(并发少、数据集较小的活跃用户查询)问题不明显,但在晚高峰并发量放大后,大量并发的排序操作叠加导致整体资源消耗上升,形成局部性能瓶颈,同时和”数据量增长导致原索引选择性下降”这一模式吻合(该表随着业务运营时间增长,单用户订单量的分布也在缓慢变化)。

修复方案:将单列索引调整为联合索引,把排序字段纳入索引:   

-- 测试环境先验证
ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time);

重新执行 EXPLAIN:  

EXPLAIN SELECT * FROM orders WHERE user_id = 88123 ORDER BY create_time DESC LIMIT 20;

优化后 Extra 不再出现 Using filesort,rows 降至 20 左右,执行计划显示可以直接利用索引顺序完成排序,无需额外排序步骤。

验证结果:在测试环境模拟高峰期并发压力(用近似的历史订单量数据),对比优化前后的平均响应时间,从约 3.2 秒降至 80 毫秒左右。生产环境低峰期执行索引变更(ALGORITHM=INPLACE, LOCK=NONE),变更后观察当晚高峰期该接口的响应时间监控曲线,确认恢复到 200 毫秒左右的正常水平,且持续观察 3 天确认没有反复。

回滚预案:变更前已确认如果新增索引导致写入性能明显下降(该表写入频率相对读取频率较低,提前评估认为可以接受索引增加带来的写入开销),如果实际观察到异常,可执行 DROP INDEX 回退。

复盘总结:该问题的核心教训是索引设计只覆盖了等值查询条件,没有考虑到排序字段同样需要纳入索引设计。补充到团队的索引设计检查清单:“涉及 ORDER BY 的查询,评估是否需要把排序字段纳入联合索引,而不是只关注 WHERE 条件字段”。同时补充一条监控告警,当该接口响应时间超过 1 秒且持续 3 分钟以上时触发告警,避免类似问题再次通过用户投诉才被发现,而是能提前通过监控主动发现。

总结

慢查询排查的核心方法论是:用慢查询日志和实时会话状态定位问题 SQL,用 EXPLAIN 分析执行计划找到根因,基于索引设计原理评估优化方案,在测试环境验证后再审慎地在生产环境实施,并全程配合监控指标验证效果。索引优化不是一次性工作,而是需要随着数据量增长、查询模式变化持续跟进的常态化工作。

排查过程中最容易踩的坑,往往不是不会用 EXPLAIN,而是忽视了操作本身的风险——比如误杀正在写入的事务、在业务高峰期执行有资源消耗的 DDL、或者删除了看似冗余实际仍被依赖的索引。把每一次排查和优化都当作生产环境变更来对待,配合备份、验证和回滚预案,才能把数据库优化真正做成一件可控、可复用的工程活动,而不是每次都是一次性的紧急救火。

 

本文链接：https://www.yunweipai.com/archives/49407