文章总结： 本文介绍了MySQL慢查询治理的系统方法，包括打开慢日志、定位高消耗SQL、索引和SQL改造、BufferPool和IO参数调整、建立监控与自动化回归。通过5个步骤，P99延迟从4.8s降至380ms，慢查询从日均30万降至1.5万。适合DBA和应用开发者参考。 综合评分： 85 文章分类： 实战经验,安全建设
MySQL 慢查询把系统拖垮后，我做了这 5 件事
点击关注 👉 点击关注 👉
马哥Linux运维
2026年7月1日 18:31 广东
在小说阅读器读本章
去阅读
MySQL 慢查询把系统拖垮后，我做了这 5 件事
一、问题背景
我记得那个晚上，新订单服务上线第二天凌晨 0 点 30 分，告警群先炸出来”DB CPU 90%+”，2 分钟后打到 100%，3 分钟后调用方全量超时。值班的我连上第一台 DB 实例，SHOW PROCESSLIST 拉出来就看得呆住了：单实例上挂着 2000+ 状态为 Sending data、Opening tables、Sorting result、Writing to net 的会话，EXPLAIN 几条”出问题”的 SQL，几乎每条都带 Using filesort、Using temporary，有些甚至直接出 ALL 全表扫。备份从库延迟超过 600 秒，binlog 写入打满，业务方把订单创建接口的回包时间全部超时。
事后复盘，那次故障里 MySQL 慢查询治理犯了 5 个明显错误：没有把 slow log 打开到能被业务侧使用的程度、定位慢查询纯靠猜、改造 SQL 只看 SQL 不看 Schema、把 Buffer Pool 当字段改、慢查询治理完没有自动回归。事后我们连着干了一周，按”定位 → 索引 → 改造 → 资源 → 监控预防”5 件事做下来，系统的 P99 延迟从 4.8s 降到了 380ms，慢查询数量从日均 30 万条降到 1.5 万条，DB CPU 从 90% 回到了 45%。
这篇文章就从那 5 件事开始讲，每个动作都给出”现象 → 命令 → 步骤 → 验证 → 风险 → 回滚”，版本差异（MySQL 5.7 与 MySQL 8.0）单独标出。文中所有的阈值、配置项、SQL 写法都核对过源码、官方手册与社区多年经验，不写 MySQL 不支持的语法，禁止混用 PostgreSQL/Oracle/SQL Server 的语法。
二、适用场景
本文适合以下读者和场景：
-
MySQL 5.7 / 8.0
维护者（单机、主从、MHA、Group Replication 都不限制，但以 InnoDB 引擎为例）。
-
DBA 或上游应用开发者
，希望”在不改应用代码的情况下”治理慢查询。
-
刚接手了一个慢查询从没被治理过的 MySQL 实例，需要从零起步。
-
准备从 5.7 升级到 8.0，希望把慢查询治理一起做掉。
-
业务正面临规模增长，原本 1ms 的 SQL 突然到 500ms。
不适合：
- 把 MySQL 当 KV 用的 NoSQL 场景，本文专注于 SQL 引擎下慢查询。
- 不写 SQL 也不维护的纯 SRE，本文重点在 SQL 层和 Schema。
三、核心知识点
慢查询治理是一个分层工程。下面把它分成 7 个层面，每一层都有自己的定位和责任。
3.1 引擎层：InnoDB
- Buffer Pool 与磁盘 IO 的边界。
innodb_buffer_pool_size是数据库性能最敏感的一档。 - Redo Log、Undo Log、Binlog 与”事务一致性”的关系。
- 索引 B+Tree 与 Buffer Pool 的关系：访问页 → Buffer Pool 命中 → 没有则磁盘 IO。
3.2 索引层：聚簇索引与二级索引
- 聚簇索引决定数据物理顺序；二级索引叶子节点存的是 PK。
- 复合索引的最左前缀、覆盖索引、索引下推 ICP（Index Condition Pushdown）。
- 不可见索引（MySQL 8.0
INVISIBLE INDEX）、降序索引（MySQL 8.0DESC）。 - 函数/表达式上的”索引”（MySQL 8.0.13 后支持函数索引）。
3.3 SQL 层：解析器与优化器
- 解析器把 SQL 拆成 AST，优化器再生成执行计划。
- 优化器不是免费的：复杂 join、IN 子查询、
SELECT *、ORDER BY RAND()等会显著拖慢优化器。 - 优化器成本估算依赖统计信息（
mysql.innodb_table_stats/mysql.innodb_index_stats），统计信息过期会让优化器做出”用错索引”的选择。
3.4 锁层：行锁、间隙锁、死锁
- 行锁看
information_schema.innodb_trx、innodb_locks、innodb_lock_waits。 - 长事务是慢查询的最大帮凶之一：它把 undo 撑大，让 purge 跟不上，让 Buffer Pool 老数据不能淘汰。
- 死锁由 InnoDB 自动检测，遇到错误码
1213，应用层要做事务重试。
3.5 复制层：主从同步
-
异步复制：写入主库即可返回，从库单线程回放（5.7 默认），重做效率受限于
slave_parallel_workers。 -
半同步：
rpl_semi_sync_master_wait_for_slave_count与rpl_semi_sync_master_timeout。 -
Seconds_Behind_Master：注意从 MySQL 8.0.22 开始，
SHOW SLAVE STATUS输出字段已逐步弃用，开始用SHOW REPLICA STATUS，输出字段命名也发生变化（如Seconds_Behind_Source）。
风险提醒：5.7 与 8.0 在复制相关指令上命名差异较大：5.7 用
MASTER/SLAVE，8.0 用SOURCE/REPLICA，旧字段保留但新建主从流程推荐新名字。
3.6 监控层：performance_schema / sys schema
-
performance_schema是 MySQL 5.6 起逐步引入的运行时自观测系统，5.7 起步已成熟。
-
sysschema 是基于
performance_schema的”好查”视图（5.7.9 起步）。 -
慢查询主战场：
events_statements_summary_by_digest/events_statements_history_long/file_summary_by_event_name/events_waits_summary_global_by_event_name。
3.7 业务层：索引心智与 SQL 心智
- 业务方要会用最左前缀、覆盖索引；DBA 要会 EXPLAIN。
- ORM 生成的 SQL 常常劣化：自动加
IS NOT NULL、自动加LIMIT，复杂 where 变成 OR。 - 应用连接池参数（如 Druid/HikariCP）：
maxWait、validationQuery、preparedStatementCacheSize，都和 SQL 不带参数执行计划稳定性有关。
四、整体排查或实施思路
把慢查询治理总结成”5 件事 + 1 个闭环”。以下是操作顺序。
4.1 5 件事概述
第 1 件：打开、收集、解析慢日志。 把 slow_query_log=ON、long_query_time=0.5（业务可接受的情况下）、log_output=FILE/TABLE 打开，用 mysqldumpslow、pt-query-digest 解析。
第 2 件：定位高消耗 SQL，看 EXPLAIN，找出根因。 用 SHOW PROCESSLIST 看活跃会话、events_statements_summary_by_digest 看历史共识，对每条 SQL 都做 EXPLAIN，理解是否走索引、走了什么索引、回表/临时表/排序成本。
第 3 件：索引改造 + SQL 改造 + 锁治理。 加复合索引、覆盖索引；重写 OR 为 UNION ALL；避免 SELECT *；避免深分页；批量插入分批；事务短小可控。
第 4 件：Buffer Pool、redo log、IO 参数治理。innodb_buffer_pool_size、innodb_log_file_size、innodb_io_capacity、innodb_flush_neighbors、innodb_change_buffering。
第 5 件：把治理沉淀为监控与自动化回归。 接慢查询告警、接 pt-query-digest 周报、接 Schema 审查、接 SQL Review、写回 PR Template。
4.2 治理闭环
+--------------------------+
| 1. 打开慢日志 |
| slow_query_log |
| long_query_time |
+-----------+--------------+
|
v
+--------------------------+
| 2. pt-query-digest 解析 |
| + events_statements |
+-----------+--------------+
|
v
+--------------------------+
| 3. EXPLAIN 三件套 |
| 索引 / 临时表 / 排序 |
+-----------+--------------+
|
v
+--------------------------+
| 4. 索引 + SQL 改造 |
| 索引/重写/锁治理 |
+-----------+--------------+
|
v
+--------------------------+
| 5. Buffer Pool/IO 参数 |
| + 重做日志/flush |
+-----------+--------------+
|
v
+--------------------------+
| 6. 监控告警 + 自动化 |
| PR Review/Schema |
+-----------+--------------+
|
v
+--------------------------+
| 7. 持续回归与压测 |
| 每周慢查询报告 |
+--------------------------+
4.3 关键判断逻辑
每一步骤都要明确”判断逻辑”：
- 看到 P99 突然上涨，先看
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free'是否非线性增长，判断是不是 Buffer Pool 不够。 - 看
Threads_running是否 > 50（5.7/8.0 经验值，需结合业务调整），判断是不是并发堵塞。 - 看
Com_select / Questions与Innodb_rows_read的比例，判断是不是全表扫描多。 - 看
Created_tmp_disk_tables / Created_tmp_tables的比例，判断是不是临时表落到磁盘。 - 看
Innodb_log_waits是否非线性，判断是不是 redo log 不够。
五、实战步骤（5 件事）
下面按顺序展开每一件事，每一件事都给出”命令 + 步骤 + 验证 + 风险 + 回滚”。
5.1 第 1 件：打开、收集、解析慢日志
5.1.1 打开慢日志
sql
-- 全局打开
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- 单位秒，业务可接受的下界
SET GLOBAL log_output = 'FILE,TABLE'; -- 同时写文件和 mysql.slow_log 表
-- 5.7 与 8.0 差异：
-- 5.7：long_query_time 是精度 1ms 级别，min_examined_row_limit 阈值可以限制只记大查询
-- 8.0：同 5.7，并新增 log_slow_extra，可记录更多信息（如物理读、平均 IO 等）
SET GLOBAL log_slow_extra = 'ON'; -- MySQL 8.0.14+ 才有
-- 改 my.cnf 让参数持久化
-- /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 0.5
log_output = FILE
log_slow_extra = ON -- 8.0.14+
log_timestamps = SYSTEM -- 避免 UTC 困惑
min_examined_row_limit = 1000
风险提醒：
SET GLOBAL是临时生效，必须改 my.cnf 否则重启丢失。
long_query_time调到 0.5s 看着不大，但高 QPS 业务下日均慢日志会达到 GB 级别，要确认磁盘余量。
mysql.slow_log（当 log_output=TABLE）默认走 CSV 引擎，不要让它膨胀，最好用 FILE 模式转
pt-query-digest。
5.1.2 解析慢日志
bash
# 先确认日志存在
ls -lh /var/lib/mysql/slow.log
# 用 mysqldumpslow 排序 Top SQL
mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log
# 用 pt-query-digest 详细分析
pt-query-digest --limit=200 \
--order-by="Query_time:sum" \
/var/lib/mysql/slow.log > /tmp/slow_report.txt
# 看报告
less /tmp/slow_report.txt
报告的结构（pt-query-digest）：
- Overall：总查询数、总时间、平均时间、P95。
- Top N queries：每条 SQL 的总次数、平均时间、占比、锁时间、Lock time、Rows sent、Rows examined。
这一步的目的：把 SQL 按”总耗时”或”平均耗时”排序，找到 Top SQL；不要被”出现次数最多但平均很短的”骗到。
风险提醒：
pt-query-digest默认会从网络读取SHOW PROCESSLIST，要确认能连上主库；如果要--no-connect仅分析日志文件，避免对生产有副作用。
5.1.3 对 Top SQL 做三步记录
bash
# 1) 抽取 SQL 指纹
pt-query-digest --print --no-tee \
--filter='$event->{arg} =~ m/SELECT.*WHERE/s' \
/var/lib/mysql/slow.log > /tmp/top_select.txt
# 2) 收集到仓库
mv /tmp/slow_report.txt /var/lib/mysql/digest/digest-$(date +%Y%m%d-%H%M).txt
5.1.4 验证与回滚
-
验证
：
SELECT @@slow_query_log, @@long_query_time, @@log_output, @@log_slow_extra;应该看到自己设置的值。 -
回滚
：直接
SET GLOBAL slow_query_log = 'OFF';，改my.cnf即可。回滚后数据库正常工作，无业务影响。
5.2 第 2 件：定位高消耗 SQL，看 EXPLAIN，找出根因
5.2.1 实时观察活跃会话
sql
SHOW FULL PROCESSLIST;
或者更精确：
sql
SELECT id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 50;
5.2.2 拉历史共识（推荐）
sql
-- MySQL 8.0 +
SELECT digest, schema_name, digest_text,
count_star AS execs,
sum_timer_wait/1e9 AS total_ms,
avg_timer_wait/1e9 AS avg_ms,
sum_lock_time/1e9 AS total_lock_ms,
sum_rows_examined AS rows_examined,
sum_rows_sent AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_db'
ORDER BY sum_timer_wait DESC
LIMIT 50;
sql
-- MySQL 5.7 同上，但字段名可能略有差异，注意 sum_timer_wait 单位是皮秒
风险提醒：
performance_schema.events_statements_summary_by_digest默认是INSTRUMENTED=YES，要确认setup_instruments中statement/sql/*全部 ENABLED。
5.2.3 EXPLAIN 三件套
sql
EXPLAIN SELECT ...
EXPLAIN FORMAT=JSON SELECT ... -- 8.0 起的 JSON 形式
EXPLAIN FORMAT=TREE SELECT ... -- 8.0.16+ 树形输出
最常用的列：
| 列 | 含义 | 关注点 | | — | — | — | | id | SELECT 序号 | 越大优先级越低 | | select_type | 类型 | SIMPLE/PRIMARY/SUBQUERY/UNION/DEPENDENT SUBQUERY | | table | 物理表 | 驱动表通常是 id 最小那行 | | partitions | 命中分区 | 没分区时 NULL | | type | 连接类型 | 从优到差：system/const/eq_ref/ref/range/index/ALL | | possible_keys | 可选索引 | 评估覆盖程度 | | key | 实际使用 | NULL 即坏味道 | | key_len | 索引使用长度 | 与联合索引最左前缀匹配 | | ref | 索引比较对象 | const / db.tbl.col | | rows | 扫描行数 | 估算值 | | filtered | 过滤比例 | 5.7 后才有 | | Extra | 额外信息 | 是否 using filesort、temporary、index condition pushdown |
注意：
EXPLAIN的
rows是估算值，实际行数要看EXPLAIN ANALYZE（8.0 起步）或EXPLAIN FORMAT=JSON的query_block.iteration_cost。
type=ALL全表扫描在百万级以上表里几乎肯定是问题；一千行小表有时无所谓。
Extra=Using filesort与
Using temporary是常见慢查询特征，但不一定是死罪；要看是否频繁出现、单次是否高耗时。
5.2.4 验证与回滚
-
验证
：将历史共识列表与 Top SQL 列表对齐。
-
回滚
：本步骤是只读，无回滚概念，只是审计与决策。
5.3 第 3 件：索引改造 + SQL 改造 + 锁治理
5.3.1 索引改造
5.3.1.1 复合索引的最左前缀
sql
ALTER TABLE orders ADD INDEX idx_user_status_created(user_id, status, created_at);
注意：复合索引列顺序决定能否用上，不要把它当成”(a,c)“能用”(a,c) 和 (a) 但不能用 (a, c, b)”的线性关系去理解。建议把”区分度高、等值查询用、范围放最后”作为通用规则。
5.3.1.2 覆盖索引
让 SELECT 用到的列都在索引里，避免回表：
sql
-- 假设有 (user_id, status, amount)，经常需要
SELECT user_id, status FROM orders WHERE user_id = ?;
-- 若索引已经覆盖，就不用回表看 amount 列
判断：EXPLAIN 的 Extra=Using index 是覆盖索引命中。
5.3.1.3 不可见索引（MySQL 8.0）
先建索引，标记为 INVISIBLE，让优化器不使用；验证业务不回归后再 ALTER VISIBLE：
sql
ALTER TABLE orders ADD INDEX idx_status_created(status, created_at), INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_status_created VISIBLE; -- 上线
ALTER TABLE orders ALTER INDEX idx_status_created INVISIBLE; -- 下线
5.3.1.4 降序索引（MySQL 8.0）
sql
ALTER TABLE t ADD INDEX idx_a_b(a ASC, b DESC);
8.0 才支持降序索引，5.7 不支持。优化器在 ORDER BY a ASC, b DESC 时可以直接命中。
5.3.1.5 函数索引（MySQL 8.0.13+）
sql
ALTER TABLE t ADD INDEX idx_lower_name((LOWER(name)));
注意：LOWER(name) 是表达式，索引键是 LOWER(name)，查询必须保持表达式一致才能命中：
sql
SELECT * FROM t WHERE LOWER(name) = 'abc'; -- 命中
SELECT * FROM t WHERE name = 'abc'; -- 不命中
5.3.1.6 删除不再使用的索引
sql
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
风险提醒：
schema_unused_indexes只看performance_schema，对长尾索引可能误判；生产环境删除索引前要”INVISIBLE 一段时间再删”。schema_redundant_indexes给的是”A 索引已经是 B 索引的前缀”这种；不要直接 DROP，先确认业务方不再按 A 索引的列做查询。
5.3.2 SQL 改造
5.3.2.1 避免 SELECT *
sql
SELECT a, b, c FROM orders WHERE user_id = ?;
如果表里有 TEXT/BLOB 字段，且仅为部分业务读，写具体列能省物理 IO。
5.3.2.2 OR 改 UNION ALL
sql
-- 改前
SELECT * FROM t WHERE a = 1 OR b = 2;
-- 改后
SELECT * FROM t WHERE a = 1 UNION ALL SELECT * FROM t WHERE b = 2;
5.3.2.3 深分页优化
sql
-- 改前
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 改后（让排序成本降到只 seek 一次）
SELECT * FROM orders WHERE id > <last_seen_id> ORDER BY id LIMIT 20;
或者延迟关联：
sql
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders WHERE ... ORDER BY id LIMIT 100000, 20) AS ids
ON o.id = ids.id;
5.3.2.4 IN 子查询转 JOIN
sql
-- 改前
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level = 'vip');
-- 改后
SELECT orders.* FROM orders INNER JOIN users ON orders.user_id = users.id
WHERE users.level = 'vip';
5.3.2.5 关联字段类型一致
sql
-- 关联字段一个是 varchar(10)，另一个是 varchar(20)，结果就是关联不上索引
-- 关联前要 DDL 统一
5.3.2.6 批量插入分批
sql
-- 一万行的 INSERT 拆成 100 行 * 100 批
INSERT INTO orders (...) VALUES (...), (...), ...;
5.3.2.7 Limit 0 实际查询审计
sql
SELECT * FROM t WHERE ... ORDER BY RAND() LIMIT 10;
ORDER BY RAND() 在 100 万行表上是 O(N log N) 排序，不要用。
5.3.3 锁治理
5.3.3.1 避免长事务
sql
-- 长事务是 undo 撑大、purge 缓慢、Buffer Pool 老数据不能淘汰的元凶
-- 经验值：单事务 < 5 万行 / < 5 秒（视业务而定）
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ASC;
5.3.3.2 死锁处理
sql
SHOW ENGINE INNODB STATUS\G
取 LATEST DETECTED DEADLOCK 段，按应用层做事务重试策略（应用层捕获 SQLSTATE 40001 / errno 1213 / 1205）。
5.3.3.3 索引失效的坑
- 字段上有函数：
WHERE LOWER(name) = 'abc'。 - 隐式转换：
WHERE id = '123'，其中 id 是 BIGINT，无法命中。 - 字符串加
=比较但左边是数值：触发隐式转换。 - 不等于
!=和NOT IN在某些版本上不走索引（多数版本还行）。
5.3.4 验证与回滚
-
验证
：EXPLAIN 对比改造前后。
-
回滚
：
-
索引改造：
ALTER TABLE t DROP INDEX idx_...。 -
SQL 改造：上线前代码 review / feature flag 控制；回滚由应用版本控制。
风险提醒：
ALTER TABLE ADD INDEX在 5.7 / 8.0 上是”在线”或”原地”操作，但仍会短期持有 MDL 写锁；大表建议用
pt-online-schema-change或gh-ost。跨表的索引改造要按业务低峰期执行。
5.4 第 4 件：Buffer Pool、Redo、IO 参数治理
5.4.1 Buffer Pool
ini
# /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 物理内存的 60%-70%（单机独占时）
innodb_buffer_pool_instances = 8 -- 把 buffer pool 拆为多个 instance，减少锁争用
innodb_buffer_pool_chunk_size = 128M -- 8.0 起步；动态调节内存
风险提醒：Buffer Pool 实例数 / 大小一旦修改，需要重启生效。从 8.0.3 起步，
innodb_buffer_pool_size是动态可调的，但只能增加；调整时 observer 影响小。 在线调节：SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024; -- 8GB注意只有按 chunk_size 整数倍调节才会生效。
5.4.1.1 Buffer Pool 状态观察
sql
SHOW ENGINE INNODB STATUS\G
BUFFER POOL AND MEMORY 段：当前 Buffer Pool 使用率、Pages made young / Pages made not young、Pages read / Pages written。如果 Pages made young 接近 Pages made not young，Buffer Pool 命中率高。
sql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-
Innodb_buffer_pool_read_requests：请求数。
-
Innodb_buffer_pool_reads：磁盘读取次数。
disk_reads / total_requests 越小越好，行业经验是命中率 > 99% 算健康。
5.4.2 Redo Log
ini
innodb_log_file_size = 4G -- 单个 redo 文件大小
innodb_log_files_in_group = 4 -- redo 文件个数
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 -- 1:每次提交 flush；2：每秒 flush；0：1 秒 flush 一次
风险提醒：
innodb_flush_log_at_trx_commit = 2提升性能，但崩溃后可能丢 1 秒数据。
innodb_log_file_size修改要重启（删除旧 redo 文件 + 新建）。
5.4.3 IO 容量与刷盘行为
ini
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 1
innodb_flush_method = O_DIRECT -- Linux 文件系统 O_DIRECT
innodb_change_buffering = all -- 8.0 默认 all
innodb_change_buffer_max_size = 25
innodb_io_capacity依硬件调整：SSD 大约 2000~4000；机械盘大约 200~600。低于实际能力时 InnoDB 会推后刷盘；过高时刷盘过激。
5.4.4 binlog 与磁盘
ini
log_bin = /var/lib/mysql/binlog/mysql-bin
expire_logs_days = 7
max_binlog_size = 1G
binlog_format = ROW -- 默认 ROW，性能差但安全
sync_binlog = 1 -- 写完 binlog 即刷盘
风险提醒：
sync_binlog = 1对吞吐有 10%~30% 影响，丢失概率低；sync_binlog = 0性能好但是崩了可能丢数据。生产强烈推荐 1，除非接受丢失部分事务。
5.4.5 5.7 与 8.0 重要参数差异
| 参数 | 5.7 | 8.0 |
| — | — | — |
| 缓冲池动态调整 | 部分 | 完整 |
| innodb_log_writer_threads | 不支持 | 8.0.11+ 支持多日志写线程 |
| innodb_deadlock_detect | 默认开启 | 默认开启，可关闭让应用处理 |
| innodb_online_alter_log_max_size | 上限 1GB | 8.0 提升可更大 |
| innodb_print_ddl_logs | 不支持 | 8.0.21+ 支持 |
| innodb_idle_flush_pct | 默认 100 | 8.0 默认 100 |
5.4.6 验证与回滚
-
验证
：
SHOW GLOBAL VARIABLES LIKE 'innodb_%'对比前后；SHOW ENGINE INNODB STATUS\G看 Buffer Pool 命中率、Log Sequence Number 增速。 -
回滚
：保留
my.cnf.bak，重启回到旧值。
5.5 第 5 件：把治理沉淀为监控告警与持续回归
5.5.1 慢查询监控告警
sql
-- 在 sys schema 里直接拿到 Top 50 慢查询 hash
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 50;
通过 mysql_exporter + Prometheus + Alertmanager：
yaml
# /etc/prometheus/rules/mysql.yaml
groups:
- name: mysql.slow
rules:
- alert: MysqlSlowQueriesRising
expr: increase(mysql_global_status_slow_queries_total[5m]) > 100
for: 2m
labels:
severity: warning
annotations:
summary: "Slow queries growing"
description: "Slow query count increased by 100 in last 5 min."
风险提醒：阈值要”基于业务基线”，不要硬编码成 100；夜间业务低谷、压测、业务上线都可能触发。
5.5.2 慢查询报告周报化
bash
#!/usr/bin/env bash
set -euo pipefail
LOG_DIR=/var/lib/mysql/slow
OUT_DIR=/var/lib/mysql/digest
mkdir -p "$OUT_DIR"
for f in $LOG_DIR/slow.log.$(date -d '-7 days' +%Y%m%d); do
pt-query-digest --limit=200 "$f" > "$OUT_DIR/digest-$(basename $f).txt"
done
把每周的报告归档到 WIKI 或对象存储。
5.5.3 SQL Review 与 Schema Review PR Template
text
# SQL / Schema Review Template
## Review Checklist
- [ ] EXPLAIN 前后对比已附上
- [ ] `Using filesort` / `Using temporary` 已规避或已说明
- [ ] `SELECT *` 不使用
- [ ] 关联字段类型一致
- [ ] 索引设计给覆盖索引或最左前缀
- [ ] 锁等待/长事务处理
- [ ] 灰度回滚路径明确
## Performance Baseline
- 改造前 P99: xxx ms
- 改造后 P99: yyy ms
## Rollback
- SQL: revert commit
- 索引: ALTER TABLE xxx DROP INDEX xxx
5.5.4 治理的回归测试
bash
#!/usr/bin/env bash
# sysbench-prep.sh：准备一个能压出来"原始健康基线"的库
sysbench --db-driver=mysql --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=root \
--mysql-password=$MYSQL_PASS --mysql-db=sbtest \
--table-size=2000000 \
--tables=10 oltp_read_write prepare
sysbench --db-driver=mysql --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=root \
--mysql-password=$MYSQL_PASS --mysql-db=sbtest \
--time=120 --threads=64 \
--report-interval=5 oltp_read_write run > /tmp/sysbench.txt
风险提醒：
sysbench写入数据量大，要提前确认磁盘余量；正式回归前要保证数据已被切到独立 schema，不能搞坏生产。
5.5.5 验证与回滚
-
验证
：监控曲线对比、SQL Review 覆盖率、A/B 验证。
-
回滚
：监控基线变更要配 version，回滚到上一个基线值。
六、常用命令
下面是文中频繁用到的命令（直接 copy 即用）。
6.1 慢查询相关
sql
-- 全局 / 会话参数
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'log_slow_extra';
-- 设置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
-- 看 PROCESSLIST
SELECT id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep';
-- 历史共识（8.0）
SELECT digest, schema_name, digest_text,
count_star AS execs,
sum_timer_wait/1e9 AS total_ms,
avg_timer_wait/1e9 AS avg_ms,
sum_lock_time/1e9 AS total_lock_ms,
sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 50;
-- sys 视图：Top SQL
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 50;
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
SELECT * FROM sys.statements_with_sorting;
SELECT * FROM sys.statements_with_temp_tables;
6.2 EXPLAIN / EXPLAIN ANALYZE
sql
EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...; -- 8.0
EXPLAIN FORMAT=TREE SELECT ...; -- 8.0.16+
EXPLAIN ANALYZE SELECT ...; -- 8.0.18+，需要 optimizer trace ENABLED
6.3 索引与表
sql
-- 创建索引
ALTER TABLE t ADD INDEX idx_a(a);
ALTER TABLE t ADD UNIQUE KEY uniq_b(b);
-- 8.0 特性
ALTER TABLE t ADD INDEX idx_a_b(a ASC, b DESC), INVISIBLE;
ALTER TABLE t ADD INDEX idx_lname((LOWER(name)));
-- 看索引是否生效
SHOW INDEX FROM t;
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
-- 看统计信息
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 't';
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 't';
ANALYZE TABLE t; -- 重算统计
风险提醒：
ANALYZE TABLE是只读元数据更新，不会锁全表，但会短时间持有 MDL 读锁。
6.4 锁与事务
sql
-- 当前活跃事务
SELECT trx_id, trx_state, trx_started, trx_query, trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;
-- 看锁等待
SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM performance_schema.data_locks; -- 8.0+
SELECT * FROM performance_schema.data_lock_waits; -- 8.0+
-- KILL 句柄（生产慎用，参考风险提醒）
SELECT id, time, info FROM information_schema.PROCESSLIST WHERE time > 60;
KILL <id>; -- MySQL 客户端命令，非 SQL
风险提醒：
KILL一个大事务会让它回滚并产生大量 undo log；杀前先评估回滚时间。生产推荐”先KILL QUERY <id>终止当前 SQL”，让事务自然提交或回滚。
6.5 性能指标拉取
sql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
SHOW ENGINE INNODB STATUS\G
6.6 复制相关
sql
-- 5.7
SHOW SLAVE STATUS\G
-- 8.0.22+
SHOW REPLICA STATUS\G
-- 主从切换（生产慎用）
STOP SLAVE; -- 5.7
STOP REPLICA; -- 8.0+
CHANGE MASTER TO MASTER_HOST='10.0.0.x'; -- 5.7
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.x'; -- 8.0+
START REPLICA;
风险提醒：主从切换有数据一致性风险，需要前置
FLUSH TABLES WITH READ LOCK、确认复制延迟为 0、确认Relay_Log_File和Exec_Master_Log_Pos。
6.7 pt 工具集常用命令
bash
# 慢查询分析
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt
# online DDL
pt-online-schema-change --alter "ADD INDEX idx_a(a)" D=mydb,t=t --execute
# DDL 前后 diff（5.7 / 8.0 通用）
pt-table-checksums h=master,u=root,p=$P,H=replica
# 主从同步延迟
pt-heartbeat --master h=master,u=root --replica h=replica,u=root --interval=1 --run-time=10
风险提醒：
pt-online-schema-change会触发 trigger，会对某些业务行为产生微妙影响；停用 trigger 前要做完整测试。
6.8 常用 EXPLAIN JSON 输出解读
json
{
"query_block": {
"select_id": 1,
"cost_info": { "query_cost": "5.12" },
"table": {
"table_name": "t",
"access_type": "range",
"possible_keys": ["idx_a"],
"key": "idx_a",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 95,
"filtered": "95.00",
"cost_info": { "read_cost": "0.50", "eval_cost": "9.50", "prefix_cost": "5.00" }
}
}
}
字段含义：
-
access_type: 连接类型。
-
rows_examined_per_scan: 估算扫描行数。
-
filtered: 估算过滤后剩余比例。
-
cost_info.eval_cost: 优化器对扫描后处理的成本估算。
-
prefix_cost: 累计成本（含本表）。
判断逻辑：
- 如果
cost_info.eval_cost / cost_info.prefix_cost很大，说明扫描不是瓶颈；瓶颈在扫描后处理。 - 如果
access_type=ALL但rows_examined_per_scan很小，是小表——可以接受。 - 如果
access_type=range但key是 NULL，可以建议补索引。
七、配置示例
7.1 一个”基线” my.cnf（5.7 / 8.0 通用）
ini
# /etc/my.cnf
[client]
default-character-set = utf8mb4
[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-storage-engine = InnoDB
skip-name-resolve
performance-schema = ON
# 慢查询
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 0.5
log_output = FILE
log_timestamps = SYSTEM
min_examined_row_limit = 1000
# 连接
max_connections = 2000
max_user_connections = 1000
wait_timeout = 600
interactive_timeout = 600
# 缓冲池
innodb_buffer_pool_size = 物理内存的 60%
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M
# Redo
innodb_log_file_size = 4G
innodb_log_files_in_group = 4
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
# IO
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 1
innodb_flush_method = O_DIRECT
innodb_change_buffering = all
# binlog
log_bin = /var/lib/mysql/binlog/mysql-bin
binlog_format = ROW
max_binlog_size = 1G
expire_logs_days = 7
sync_binlog = 1
# 复制（5.7）
server-id = 1
binlog-row-image = FULL
gtid-mode = ON
enforce-gtid-consistency = ON
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK -- 5.7 引入
# 8.0 是 source/replica 命名，下面给 8.0 兼容写法
master_info_repository = TABLE
relay_log_info_repository = TABLE
7.2 GTID 主从
sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.21',
MASTER_USER='repl',
MASTER_PASSWORD='***', -- 密码要走密钥系统
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
START SLAVE;
-- 8.0.22+
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.21',
SOURCE_USER='repl',
SOURCE_PASSWORD='***',
SOURCE_PORT=3306,
SOURCE_AUTO_POSITION=1;
START REPLICA;
7.3 在线 DDL（MySQL 8.0）
sql
ALTER TABLE t ADD INDEX idx_a(a), ALGORITHM=INPLACE, LOCK=NONE;
参数含义：
-
ALGORITHM=INPLACE：原地算法，避免拷贝表。
-
LOCK=NONE：在 ALTER 期间允许读写；对大表推荐。
风险与回滚：
- 真要”拷表”的 ALTER（如修改列类型）即使是 INPLACE，也会做表的临时拷贝，会撑爆磁盘，生产推荐用
gh-ost或pt-online-schema-change。 - 回滚：保留 ALTER 反向语句，下一个变更窗口执行。
7.4 Online DDL（pt-osc）
bash
pt-online-schema-change \
--alter "ADD INDEX idx_a(a)" \
--alter-foreign-keys-method auto \
--chunk-size 1000 \
--max-load Threads_running=50 \
--critical-load Threads_running=100 \
--set-vars wait_timeout=10000 \
D=db,t=t \
--execute
参数含义：
-
--chunk-size：一次操作多少行。
-
--max-load：运营阈值，达到则暂停。
-
--critical-load：超过立即停止。
-
--set-vars：按会话级别临时设置参数。
风险提醒：
-
pt-online-schema-change会创建 trigger，存在一段时间；某些 8.0 ROW 格式复制下 trigger 会让 GTID 出现一些副作用，需要
--no-drop-old-table等选项特殊处理。 -
--critical-load阈值要”基于业务基线”，否则一压测就被感知为”停止”。
7.5 连接池配置（应用侧示例）
properties
# HikariCP
spring.datasource.hikari.maximum-pool-size=64
spring.datasource.hikari.minimum-idle=8
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
# Druid
druid.initial-size=8
druid.min-idle=8
druid.max-active=64
druid.max-wait=30000
druid.pool-prepared-statements=true
druid.max-pool-prepared-statement-per-connection-size=20
连接数配置原则：
- DB 端
max_connections设置合理，避免连接风暴把 DB 打满。 - 应用连接池大小按”
DB max_connections / 应用实例数 / 2~4“为基线，再压测找最优值。 - 启用连接超时（
wait_timeout、socket_timeout），防止沉睡连接占用。
风险提醒：连接池设太大反而会引起问题——DB 被连接数打满，应用线程撞墙，体验变差。
八、日志或指标观察方法
8.1 慢查询日志的读取位置
bash
# 通用读
less /var/lib/mysql/slow.log
# 按时间过滤
awk '/^# Time:/ {keep = ($0 ~ "2024-06-15/")?1:0} keep {print}' /var/lib/mysql/slow.log
# 找 TOP 慢查询指纹
grep "Query_time:" /var/lib/mysql/slow.log | sort -rn | head
8.2 performance_schema 关键事件表
| 表 | 用途 |
| — | — |
| events_statements_current | 实时当前 SQL |
| events_statements_history | 最近执行（按 thread） |
| events_statements_history_long | 最近执行（实例级，FIFO） |
| events_statements_summary_by_digest | 按 digest 汇总 |
| events_waits_summary_global_by_event_name | IO 等待事件总览 |
| file_summary_by_event_name | 文件级 IO |
| table_io_waits_summary_by_table | 表级 IO |
| events_transactions_current | 实时当前事务 |
打开相关 instruments / consumers：
sql
-- 持续打开
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'
WHERE name LIKE 'statement/sql/%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES'
WHERE name LIKE 'events_statements_%';
风险提醒：
performance_schema自身消耗，按执行频率与事务粒度调整。建议默认打开，若发现重启后内存增长过快，调整max_digest_length、performance_schema_max_digest_length等。
8.3 sys schema 视图速查
| 视图 | 用途 |
| — | — |
| sys.host_summary | 按 host 汇总 |
| sys.io_by_latency | 慢 IO 文件排名 |
| sys.io_global_by_wait_by_latency | 全局 IO 类型排名 |
| sys.memory_by_thread_by_current_bytes | 各 thread 占用 |
| sys.statement_analysis | Top SQL |
| sys.statements_with_runtimes_in_95th_percentile | 95th 慢 SQL |
| sys.statements_with_sorting | 用到 filesort 的 SQL |
| sys.statements_with_temp_tables | 用到临时表的 SQL |
| sys.schema_unused_indexes | 未使用的索引 |
| sys.schema_redundant_indexes | 冗余索引 |
| sys.innodb_lock_waits | 锁等待 |
| sys.schema_tables_with_full_table_scans | 走全表扫的表 |
8.4 关键监控指标
promql
# QPS
mysql_global_status_questions_total
# 慢查询增长
increase(mysql_global_status_slow_queries_total[5m])
# Buffer Pool 命中率
1 - (mysql_global_status_innodb_buffer_pool_reads_total /
mysql_global_status_innodb_buffer_pool_read_requests_total)
# 长事务
mysql_info_schema_threads_seconds_total
# 锁等待
mysql_global_status_innodb_row_lock_waits_total
# 复制延迟
mysql_slave_status_seconds_behind_master
# 主从 5.7 / 8.0 兼容
mysql_slave_status_seconds_behind_source # 8.0
8.5 我的观察习惯
-
SHOW GLOBAL STATUS LIKE 'Slow_queries';5 秒一次取一次，看增速。
-
SHOW ENGINE INNODB STATUS\G每次重大变化前后取一次，做 diff。
-
慢日志每天归档
，按业务日清空、当月可查。
-
MySQL 主库必有主从延迟主面板
：
Seconds_Behind_Master（或Seconds_Behind_Source，8.0）。 -
所有 SSQL 改动都看
events_statements_summary_by_digest改造前后的
count_star、avg_timer_wait。
九、排查路径
下面是一份”慢查询出现时怎么排查”的标准路径。
9.1 故障信号分类
-
Threads_running大量增加 → 高并发导致。
-
Threads_connected接近
max_connections→ 连接风暴。 -
Innodb_log_waits非线性增长 → redo log 配置不足。
-
Innodb_buffer_pool_wait_free增长 → Buffer Pool 不足。
-
Created_tmp_disk_tables / Created_tmp_tables比例高 → 临时表落到磁盘。
-
Slow_queries增长 → 触发慢查询告警。
9.2 高效排查流
-
确认业务现象
：调用方 P99 上升 / 错误码。
-
拉集群状态
：
SHOW GLOBAL STATUS LIKE 'Threads%'、Innodb_buffer_pool_wait_free。 -
拉活跃会话
：
SELECT * FROM information_schema.PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC LIMIT 50; -
拉锁等待
：
SELECT * FROM sys.innodb_lock_waits;与SELECT * FROM sys.io_global_by_wait_by_latency LIMIT 10; -
拉共识 SQL
：
SELECT * FROM sys.statement_analysis LIMIT 50;看 Top SQL。 -
对 Top SQL 做 EXPLAIN
。
-
看慢日志 fingerprint
：
pt-query-digest /var/lib/mysql/slow.log | head -200。 -
看主从延迟 / 异常
：
SHOW SLAVE STATUS\G/SHOW REPLICA STATUS\G。 -
看磁盘 / IO
：
iostat -xz 1、dstat。 -
快速缓解
：
KILL QUERY <id>（不 KILL 连接）；调大 Buffer Pool；抓 Schema 改造。
9.3 内存与 CPU 维度
bash
top -bn1 -p $(pgrep -d, mysql)
ps -o pid,nlwp,pcpu,pmem,etime -p $(pgrep mysql)
bash
# 看 IO 行为
iostat -xz 1
vmstat 1
sar -n DEV 1
9.4 长事务专项排查
sql
SELECT trx_id, trx_state, trx_started, trx_query, trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(MINUTE, trx_started, NOW()) > 5
ORDER BY trx_started ASC;
按 trx_query 看具体 SQL 与 trx_rows_locked，如果 trx_rows_locked 巨大且 trx_state 是 RUNNING，基本可以确认业务方写了一段长事务。
处理建议：通知业务方 + 应用层捕获 KILL 信号前的回滚判断。不要直接 KILL 事务，避免”回滚风暴”。
9.5 真实排查路径示例
故障：凌晨 2 点订单服务 P99 上升。
-
SHOW PROCESSLIST看到 200+ 条
UPDATE orders SET ...状态Sleeping但有事务，疑似长事务。 -
SELECT trx_id, trx_started FROM information_schema.innodb_trx;看到 5 个事务 > 30 分钟。
-
业务侧查日志：
for update之后代码Thread.sleep(1000)再做 IO。 -
业务侧修改：移除 sleep，改为异步。
-
KILL <trx_mysql_thread_id>业务侧先回滚事务。
-
长事务删除后 P99 立即降下。
-
写一份事故复盘，沉淀到 PR Template。
十、风险提醒
下面列出 12 个最常见的”治理错或者治理过猛”风险。
10.1 KILL <id> 而不是 KILL QUERY <id>
-
KILL <id>终止连接，可能让事务无法回滚完成。
-
KILL QUERY <id>仅终止当前 SQL，让事务自然提交或回滚。生产推荐。
10.2 OPTIMIZE TABLE 反噬
-
OPTIMIZE TABLE t实际是 ALTER TABLE 的某种形式，会拷贝表；大表上很慢。
-
5.7 与 8.0 默认对 InnoDB 表
OPTIMIZE TABLE退化为ALTER TABLE ... FORCE，对应 ALGORITHM=COPY。 -
推荐做法：定期做
ALTER TABLE t ENGINE=InnoDB;来”重建表”，同样效果但控制更细致；或交给 pt-online-schema-change。
10.3 大 ALTER TABLE ADD INDEX
- 单次 ADD INDEX 是 INPLACE，5.7/8.0 默认不会锁整表写，但仍受限于 redo log、临时空间。
- 推荐 pt-osc 或 gh-ost，对应”撑爆磁盘””主从延迟”在白天发生概率高。
10.4 大 SET GLOBAL ... 在线参数调整
-
SET GLOBAL innodb_buffer_pool_size = 24*1024*1024*1024;看似 5 秒调整到位，但实际按 chunk 推进。
-
注意 IO 抖动：调整过程会重建 LRU 数据。
-
回滚：再次 SET GLOBAL 调到原值。
10.5 关闭二进制日志再切回
-
SET sql_log_bin = 0会让当前会话的 SQL 不写 binlog，仅管理员账户能用；临时做回滚 DDL 时不要让普通开发账户碰。
-
切回前必须显示
SET sql_log_bin = 1，否则业务代码可能会再次使用 0。
10.6 直接 DROP TABLE
-
没有备份/没有审计/没有回滚脚本。
-
DROP TABLE是 DDL，不可逆；先
ALTER TABLE t RENAME TO t_20240615。
10.7 修改主键 / 修改列类型
- 8.0 已经能做部分
ALTERINPLACE，但有些列类型改变（比如 VARCHAR(64) → VARCHAR(256)）仍可能 ALGORITHM=COPY。 - 生产前必须
SHOW CREATE TABLE t\G、EXPLAIN ALTER TABLE ...。
10.8 在线开启 general_log
- 5.7 默认 general_log=OFF；在线打开会写所有 SQL 到磁盘，IO 立刻打满。
- 调试时用临时会话打开：
sql
SET SESSION general_log = 'ON';
SET SESSION log_output = 'TABLE';
打开后关闭。
10.9 binlog_format = MIXED 隐性坑
- 5.7 默认 ROW，但早期 5.6 部署的实例可能是 MIXED。
- ROW 格式下 binlog size 大，主从重放压力大，需要更宽的 redo 和磁盘。
10.10 optimizer_search_depth
- 优化器在 join 重排时搜索深度的参数。值过大时优化器耗时长，planning 出现慢。
- 5.7 默认 62；8.0 默认 62。可以下调到 10~20 看是否缓解某些 join planning 慢。
10.11 启用 SQL 缓存（query_cache_type）
- 5.7 之前官方默认建议关闭。
- 5.7 起 query_cache_type 默认 0，8.0 已彻底移除。
- 5.6 / 早期 5.7 实例升级到 8.0 后还要清掉
query_cache_size。
10.12 大事务回滚
- 业务杀事务后立刻 abort，但回滚本身要 undo，把 short-lived 长事务拆成短事务是根治之道。
十一、验证方式
每件事都要做验证，验证的标准也要写明。
11.1 慢查询治理验证
-
指标
：慢查询数量、Buffer Pool 命中率、
Threads_runningP99、Com_select与Innodb_rows_read比例、Created_tmp_disk_tables / Created_tmp_tables。 -
持续时间
：至少 7 天，覆盖业务周一到周日。
-
样本
：每次对比时取 24h 窗口均值，避免抖动。
11.2 索引治理验证
-
EXPLAIN
：每条改造过的 SQL 都有 EXPLAIN 前后对比。
-
回归测试
：业务侧使用 sqlmock + 真实数据集覆盖率 100%。
-
压测
：sysbench / 业务脚本的 P99 都回到原基线 + 业务提升。
11.3 资源参数治理验证
-
SHOW GLOBAL STATUS
：指标走势连续观测 7 天。
-
iostat
：IO 利用率比之前平缓。
-
错误日志
：
InnoDB: page_cleaner等信息不再出现。
11.4 监控告警验证
-
告警回放
：在测试环境模拟慢查询爆发，看 Prometheus / Alertmanager 是否按预期告警。
-
覆盖率
：监控项与 DBA 自动化脚本交叉验证。
11.5 治理后业务侧验证
-
调用方接口
：P99 / P95 / ErrorRate 三指标。
-
DB CPU / QPS
：DB CPU 至少下降 30%（视业务而定）。
-
慢查询数量
：下降到基线的 50%。
十二、回滚方案
每件事的治理都要有回滚手段，下面按动作列。
12.1 慢日志相关回滚
sql
-- 关闭慢日志
SET GLOBAL slow_query_log = 'OFF';
-- 修改 /etc/my.cnf 恢复到原来的 long_query_time、log_output
systemctl restart mysqld
风险提醒：重启会让 MySQL 重启，会有短暂的不可用时间窗。
12.2 索引与 Schema 回滚
sql
-- 删除刚刚加的索引
ALTER TABLE t DROP INDEX idx_x;
-- 把列回退
ALTER TABLE t DROP COLUMN c;
业务代码同样要在分支上保留回滚代码。
12.3 SQL 改造回滚
- 由应用层做版本回滚（代码或 feature flag）。
- 数据未变，不会影响数据。
12.4 参数治理回滚
bash
cp /etc/my.cnf.bak /etc/my.cnf
systemctl restart mysqld
或者在线回滚：
sql
SET GLOBAL innodb_buffer_pool_size = <原值>;
SET GLOBAL innodb_io_capacity = <原值>;
风险提醒：部分参数在线修改是只读的（
SET GLOBAL改不了），必须重启。
12.5 监控告警回滚
- 把上一版的 yml 文件通过 git revert。
- 通过 prometheus reload 生效。
12.6 业务回归回滚
- 通过业务代码回滚版本。
- 保留 ALTER 前后的 DDL 字符串，避免 offline 后忘记重做。
十三、生产环境注意事项
13.1 不要随便改 sql_mode
sql_mode 决定 SQL 的严格性；改一次会让看似正常的 SQL 行为变化。建议先空跑的语句级别用 SET sql_mode = '<具体模式>' 验证。
13.2 升级与跨版本变更
-
5.7 → 8.0 升级要参考官方”升级检查指南”：
-
跑
mysql_upgrade。 -
注意 residual privileges（5.7 → 8.0 会有 schema 移除）。
-
注意保留密码字段的认证方式（5.7 → 8.0 推荐 caching_sha2_password）。
-
8.0 → 8.4 等小版本升级要先看 release notes。
13.3 严格区分 DDL 与 DML 操作窗口
- DDL：业务低峰（凌晨 1~5 点）或用 gh-ost 白天做。
- DML 变更：所有 ALTER 通过 PR，权限到团队级。
13.4 性能回归测试
- 任何对 1000 万行以上的表做 DDL 后必须跑性能回归。
- 性能测试脚本、流量 replay 与环境一致。
13.5 灾备与多版本并发
- 准备 secondary 实例用于 schema 差异验证。
- 生产做一次 DDL 必须经过 dev / staging 实例的对照测试。
13.6 备份
- 慢查询治理前的数据要保留 7~30 天的物理备份 + binlog：
bash
innobackupex --user=root --password=$PASS /data/backup/xb_$(date +%Y%m%d)
- 备份本身要 smoke tested：定期
mysqlbackup --prepare验证。
13.7 Binlog 与审计
- 长期保留 binlog 7~30 天，配合审计。
- binlog 切割点：保留
SHOW MASTER STATUS/SHOW BINARY LOG STATUS的当前点。 - 8.0.22+ 使用
SHOW BINARY LOG STATUS。
13.8 临时表空间管理
-
innodb_temp_tablespaces_dir默认在 datadir 下。
-
出现
/tmp临时表很多，可能是 GROUP BY/HAVING/ORDER BY RAND 之类的复杂 SQL。 -
8.0 增加了
TempTable引擎做优化。
13.9 连接数与连接池监控
-
Threads_connected接近
max_connections时，提前预警。 -
应用连接池 keepalive 与 DB
wait_timeout要协调，建议 DB 端设大（默认 28800s），连接池 idle timeout 设小。
13.10 GC 回收与磁盘
-
大表的
ALTER TABLE ... ENGINE=InnoDB在 5.7 默认会拷贝表到 tmpdir，tmpdir 要大。 -
tmpdir在
/tmp是不够的。 -
innodb_temp_tablespaces_dir也要分独立分区。
十四、5.7 与 8.0 的关键参数与命令差异表
下面把 5.7 与 8.0 在慢查询治理中真正用到的命令差异整理成表，便于升级路径对照。
14.1 复制 / binlog 相关
| 5.7 | 8.0 |
| — | — |
| SHOW MASTER STATUS | SHOW BINARY LOG STATUS （部分版本兼容） |
| SHOW SLAVE STATUS | SHOW REPLICA STATUS （8.0.22+） |
| CHANGE MASTER TO | CHANGE REPLICATION SOURCE TO （8.0.22+） |
| START SLAVE | START REPLICA |
| STOP SLAVE | STOP REPLICA |
| Relay_Master_Log_File | Relay_Source_Log_File |
| Seconds_Behind_Master | Seconds_Behind_Source |
| Master_Log_File | Source_Log_File |
| Master_UUID | Source_UUID |
| Replicate_Wild_Do_Table | Replicate_Wild_Do_Table （不变） |
风险提醒：升级路径上，8.0 仍然兼容 5.7 命令，但行为会带”warning”日志；新建集群推荐用 8.0 命名（
SOURCE/REPLICA）。
14.2 性能视图差异
| 5.7 | 8.0 |
| — | — |
| information_schema.innodb_locks | 移除；改用 performance_schema.data_locks |
| information_schema.innodb_lock_waits | 移除；改用 performance_schema.data_lock_waits |
| events_statements_summary_by_digest | 同名，更高采样精度 |
| sys.statement_analysis | 同名 |
| performance_schema.events_stages_history | 增加 stages 阶段信息 |
| EXPLAIN | EXPLAIN FORMAT=JSON 、EXPLAIN FORMAT=TREE、EXPLAIN ANALYZE |
| optimizer_trace | 增强 |
| OPTIMIZER_TRACE_MAX_LEN | 默认 size 调整 |
14.3 索引与 DDL 差异
| 5.7 | 8.0 |
| — | — |
| 索引默认 VISIBLE | 可用 INVISIBLE |
| 不支持降序索引 | 支持 DESC 索引 |
| 不支持函数索引 | 函数索引 |
| 不支持 hash join（默认） | 默认开启 hash join |
| 不支持 hash 索引适配 | 适配增强 |
| ALGORITHM=INPLACE 部分 ALTER 受限 | 范围更宽 |
14.4 安全与认证差异
| 5.7 | 8.0 |
| — | — |
| mysql_native_password 默认 | caching_sha2_password 默认 |
| ALTER USER ... IDENTIFIED WITH ... 兼容 | 推荐 caching_sha2_password |
| validate_password_* 插件可选 | 开箱即用 |
十五、慢查询治理的工业级案例
下面给出 4 个在生产里跑过的真实场景，每个都是”做完事后看明白为什么”的样本。
15.1 案例一：SELECT * 与隐式转换
现象：移动端订单列表 P99 上升到 800ms。
SQL：
sql
SELECT * FROM orders WHERE user_id = '1024';
问题点：
-
user_id是 BIGINT，字符串常量触发隐式转换。
-
SELECT *把
body这种 TEXT 都拉了出来。
修复：
sql
SELECT id, status, created_at, amount FROM orders WHERE user_id = 1024;
验证：
sql
EXPLAIN SELECT id, status, created_at, amount FROM orders WHERE user_id = 1024;
改造前 Extra 可能含 Using where，改造后会直接命中 PRIMARY，ref=const。
回滚：上线控制使用开关，业务放量没问题后立即 SELECT * 替换。
15.2 案例二：OR 转 UNION ALL
现象：复杂搜索接口 P99 1.5s。
SQL：
sql
SELECT * FROM orders WHERE user_id = 1024 OR created_at BETWEEN '2024-06-01' AND '2024-06-15';
修复：
sql
(SELECT * FROM orders WHERE user_id = 1024)
UNION ALL
(SELECT * FROM orders WHERE created_at BETWEEN '2024-06-01' AND '2024-06-15');
验证：EXPLAIN 显示两个独立索引范围扫描，最终合并。
回滚：应用代码按版本切换。
15.3 案例三：长事务回滚风暴
现象：上游应用写了一段 for update ... Thread.sleep(...) 然后 HTTP I/O。KILL QUERY 之后长事务让 DB Threads_running 上升。
根因：应用代码失误。
修复：
- 把 I/O 拆到事务外。
- 在代码上增加
ThreadLocal<TransactionBoundary>。 - 加测试用例，验证事务与 HTTP 调用不交叉。
验证：
sql
SELECT trx_id, TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS age_min
FROM information_schema.innodb_trx
ORDER BY age_min DESC;
业务侧观察 0 条 > 5 分钟。
回滚：应用代码回滚。
15.4 案例四：redo log 撑爆
现象：DB CPU 不高但写入吞吐抖，redo log 每秒 wait 高。
修复：
-
把
innodb_log_file_size从2G调到4G。 -
把
innodb_log_files_in_group从2调到4，做成4 × 4G = 16GB。 -
innodb_flush_log_at_trx_commit = 1保持不变。
验证：
sql
SHOW GLOBAL STATUS LIKE 'Innodb_log%';
Innodb_log_waits 应从非零回落为 0。
回滚：保留旧的 my.cnf，重启。
十六、慢查询治理复盘与周报模板
16.1 周报模板
text
# 慢查询治理周报 YYYY-MM-DD ~ YYYY-MM-DD
## 1. Top 5 SQL
| 排名 | SQL 指纹 | 总耗时(ms) | 次数 | 平均耗时(ms) | 优化状态 |
| --- | --- | --- | --- | --- | --- |
| 1 | SELECT orders ... WHERE ... | xxx | xxx | xx | 已加索引 |
| 2 | UPDATE ... | ... | ... | ... | 改造中 |
## 2. 关键指标趋势
- Slow_queries: x → y, 趋势 ↓
- Buffer Pool 命中率: x%, 趋势 →
- Threads_running P99: x, 趋势 ↓
- 复制延迟 P99: x 秒, 趋势 →
## 3. 已完成动作
- 新增索引: ...
- 改造 SQL: ...
- 调整参数: ...
## 4. 待办
- ...
## 5. 风险点
- ...
16.2 复盘文档结构
text
# 复盘：标题
## 时间
## 现象
## 影响范围
## 根因分析
## 直接原因
## 间接原因
## 处理过程
## 修复
## 验证
## 回滚
## 行动项
## 短期
## 中期
## 长期
16.3 常见慢查询根因类型
- 应用侧：索引缺失、SQL 写法、OR 写错、In 子查询、深分页、
SELECT *。 - Schema 侧：列类型不匹配、冗余/缺失索引、统计信息过期。
- 配置侧：Buffer Pool 不足、redo log 不足、IO 容量小、连接池过小/过大。
- 资源侧：CPU 争抢、IO 抖动、网络抖动、磁盘满、连接满。
- 复制侧：主从延迟、binlog 阻塞、Relay 错位。
- 监控侧：阈值错误、告警缺失、告警疲劳。
16.4 沟通模板
text
# 业务方需要做的事
应用版本: x.x
DB 影响: 慢查询 30 万/天 → 1.5 万/天
修改内容: 应用层 SQL
执行窗口: 凌晨 0-3 点
灰度比例: 5% → 25% → 50% → 100%
回滚方式: 应用层版本回滚
紧急联系人: ...
十七、长期演进
17.1 从慢查询治理到 SLO 治理
把”慢查询治理”抽象成”SLO 治理”：
- SLO：99% 调用 P99 < 200ms，DB CPU < 60%。
- 监控：调用方耗时直方图；DB CPU / QPS。
- 告警：5 分钟 P99 > 300ms。
- 容量预估：模型驱动。
17.2 从人工到平台化
把慢查询治理做成基础设施：
- SQL 自动审计（CI 阶段 EXPLAIN）；
- Schema 自动审核（PT-OSC 工作流化）；
- 慢查询报告自动生成（cron + pt-query-digest）；
- 告警与 SLO 自动关联（Prometheus + Alertmanager）。
17.3 SRE 视角的治理节奏
- 每日：当日慢查询摘要 + SLO 趋势。
- 每周：周报 + Top 10 SQL 跟踪表。
- 每月：根因类比（按”应用侧、Schema、配置、资源”分类）。
- 每季：参数基线审视 + 容量评估。
17.4 工具链最佳组合
- 慢查询采集：
slow_query_log+performance_schema+sysschema。 - 分析：
pt-query-digest+mysql_config_editor。 - 变更：
pt-online-schema-change/gh-ost。 - 监控：
mysqld_exporter+Prometheus+Alertmanager+Grafana。 - 日志：
filebeat+Elasticsearch/ClickHouse。 - 业务：
HikariCP/Druid+ ORM 防护参数。
17.5 治理节奏的真实定位
- 治理不是写完 SQL 改完索引就完了。
- 治理是长期工作：只要业务在演进，SQL 在写入，慢查询治理就一直在路上。
- 真正的闭环是：业务需求 → Schema 评审 → SQL Review → 上线 → 监控 → 警报 → 复盘 → 优化 → Schema 评审。
十七点五、慢查询与上层的协同治理
慢查询本身并不是孤立存在的。下面这段我把它当成”上层联动”的补充，分别从 SQL 防护、ORM 防护、缓存协同、变更治理四个角度展开。
17.5.1 SQL 防护（应用入口）
text
# 白名单式 SQL 防护
- 应用连接的 MySQL 账户没有 DDL/DROP 权限
- 应用连接的账户 GRANT OPTION 取消
- SQL Review 自动化校验：
- 不允许 SELECT *
- 不允许 ORDER BY RAND()
- 不允许大表全表查询
更精细一些的工具如 archery、Yearning、Bytebase、SQLFlow 等可以做规则化处理。
17.5.2 ORM 防护
java
// Hibernate / JPA
@QueryHints({ @QueryHint(name = "org.hibernate.timeout", value = "2000") })
List<SomeEntity> findByXxx();
// MyBatis
@CacheNamespace(size = 4096, ...)
业务侧长期要做的：
- ORM 生成的 SQL 要能
Mapper中显式指定字段。 - 控制 join 层级，避免 ORM 让 N 表 join。
- 大表查询必须带 LIMIT，做”分页”或者”cursor-based”。
17.5.3 缓存协同
java
// Spring Cache + Caffeine + Redis
@Cacheable(key = "#userId", value = "order:list")
public List<Order> list(int userId) {...}
把”读慢查询”变成”读缓存”。但要注意：
- 业务侧要评估 cache miss 落到 DB 的压力。
- 缓存击穿：唯一 key 缓存失效时大量请求打到 DB，要加
singleflight/Redisson lock/本地内存兜底。 - 缓存一致性：写时双删或延时双删。
17.5.4 变更治理
任何涉及”重新写 SQL、改变 SQL 行为、加索引、改字段、改主键”的代码都要走 PR / SQL Review：
text
# PR Template
- SQL 文本
- EXPLAIN 前后对比
- 灰度比例 + 回滚方式
- 影响面 + 执行窗口
不留”光改代码、不改 SQL”的事。
17.5.5 灰度放量的具体做法
下面是一段较详细的”慢查询治理灰度放量”流程，控制清单要写得清楚。
阶段一：筛选 Top SQL
bash
# 单文件最大 200MB，过大用 head / tail 切
pt-query-digest --limit=200 /var/lib/mysql/slow.log > /tmp/digest.txt
grep -E '^# Score' /tmp/digest.txt | head -20
阶段二：改造 SQL / 加索引（dev / staging）
bash
# 在 staging 库
mysql -uroot -p${PASS} < schema_change.sql
# 验证
pt-online-schema-change --print --execute ...
阶段三：测试库压测
bash
# sysbench 多线程
sysbench --db-driver=mysql \
--mysql-host=staging.host \
--mysql-port=3306 \
--mysql-user=readonly --mysql-password=$PASS \
--mysql-db=sbtest --table-size=2000000 --tables=10 \
--time=60 --threads=64 \
--report-interval=10 \
oltp_read_write run
阶段四：业务代码灰度
text
# 灰度比例
5% (业务内 1 个集群)
25% (4 个集群)
50% (8 个集群)
100%
每个阶段观察 30 分钟以上，确认 SLO 不退化。
阶段五：固化与文档
text
- PR 链接: ...
- 性能对比图: ...
- 事故复盘: ...
17.5.6 治理工作的工程习惯
-
不要把慢查询作为单一指标
：纳入 P99、错误率、调用方业务指标。
-
不要把”我想这样改”当成”它已经被验证”
：每次改造都要数据库 + 应用 + 监控 + 文档四件套。
-
不要在生产用
SELECT BENCHMARK(...)或SELECT SLEEP(...)看执行——可以使用
SELECT @@global.long_query_time；要让 SQL 在客户端模拟。 -
不要”先关后开”
：先开新方案验证，再关旧方案。
-
每次线上改造都留 PR + 文档
：第二年的自己不会记得第三周前为什么这样改。
-
永远不要把全部实例同时改
：至少 1 个不动。
-
任何时候都不要被一句”能跑就行”劝住
：记录是证据，是战斗的弹药。
十八、总结
慢查询治理在每个数据库上都会经历一次，或者更准确地说，会经历很多次。把它分成 5 件事，是为了让”治理”这个动作变得可重复、可度量、可自动化：
-
打开 + 收集 + 解析慢日志
是起点；一个不会记录慢查询的 MySQL 没法做治理。
-
定位 + EXPLAIN
是诊断；不要在不读 EXPLAIN 的情况下动索引。
-
索引 + SQL + 锁治理
是工程；要让开发、DBA 形成共同语言。
-
Buffer Pool + Redo + IO 治理
是资源调配；不要盲目参数堆叠。
-
监控 + 告警 + 回归
是闭环；治理没有回归就只是临时抱佛脚。
落到具体动作上，这 5 件事每一条都对应可执行的命令 + 可量化的指标 + 可回滚的路径。慢查询治理没有任何银弹，只有”持续观测 + 持续改造 + 持续回归”。希望这篇文章能在你下一次发现 DB CPU 飙到 90% 时，给你至少 5 件事做，而不是束手无策。
慢查询治理的最终形态不是”再也没有慢查询”，而是”慢查询总有，但总能被定位、能被解决、能被验证”。让”5 件事”成为你数据库日常的一部分，让业务侧、研发侧、运维侧用同一套语言交流慢查询，让每一次 P99 抖动都对应一个明确的”是哪件事没做好”。治理一旦形成节奏，慢查询就不再是黑天鹅，而是可被提前解决的白兔。
最后想强调一句：MySQL 慢查询并不是单点问题。它和业务形态、Schema 演进、应用层缓存、复制拓扑都有关。任何一次治理都要”顺带改善 Schema、修改应用层 SQL、增加监控项”，才会有可衡量的结果。愿你在每一次 DB CPU 飙红的时候都能稳住阵脚，把”治理”做成日常。
文末福利
今天给大家分享一份超级牛掰的Linux学习笔记，足足有1456页！是一位Linux运维大佬整理分享的，分享是获得大佬同意的，大家有需要的尽管收藏起来！
笔记介绍
这份笔记非常全面且详细，从Linux基础到shell脚本，再到防火墙、数据库、日志服务管理、Nginx、高可用集群、Redis、虚拟化、Docker等等，与其说Linux学习笔记，不如说是涵盖了运维各个核心知识。
并且图文并茂，代码清晰，每一章下面都有更具体详细的内容，十分适合Linux运维学习参考！
笔记展示
笔记下载
扫描下方二维码，回复暗号“1456页Linux笔记“，即可100%免费领取成功
免责声明：
本文所载程序、技术方法仅面向合法合规的安全研究与教学场景，旨在提升网络安全防护能力，具有明确的技术研究属性。
任何单位或个人未经授权，将本文内容用于攻击、破坏等非法用途的，由此引发的全部法律责任、民事赔偿及连带责任，均由行为人独立承担，本站不承担任何连带责任。
本站内容均为技术交流与知识分享目的发布，若存在版权侵权或其他异议，请通过邮件联系处理，具体联系方式可点击页面上方的联系我。
本文转载自：马哥Linux运维 点击关注 👉 点击关注 👉《MySQL 慢查询把系统拖垮后，我做了这 5 件事》
版权声明
本站仅做备份收录，仅供研究与教学参考之用。
读者将信息用于其他用途的，全部法律及连带责任由读者自行承担，本站不承担任何责任。
评论