一、问题背景：“越来越慢”的斜率比单次故障更危险
单次慢查询排查完就结束了；而“越来越慢”说明系统还在被持续消耗。一线最常遇到的三种开头：
- 数据增长型：新业务上线时明明做过优化，一年后同样的查询从 200ms 涨到 2s+，没人动过表结构。
- 使用漂移型：开发上了新的报表需求，巡检时才发现某条 SQL 每次 30s，打了 40 次/小时。
- 资源型：buffer pool 命中率被新 SQL 挤压，整个库都在变慢，但报警时大家各自以为是别人家的锅。
这三种类型的共同点是：表现一致、原因分层。混在一起排查就会“调了半天参数，其实根因是少一个索引”。这篇文章会给出一个可复现的排查路径，外加一个真实案例的完整闭环。
二、目标
- 把“越来越慢”拆解成可度量的三类问题（执行慢 / 等待慢 / 资源慢），用证据而非感觉定位。
- 完成一条具体 SQL 的改造闭环：定位 → 根因 → 修复 → 验证 → 回滚。
- 沉淀两个东西：慢日志与 digest 告警，防止下次同类问题再走一遍同样长的排查链。
- 把“临时修好”和“回归测试通过”写进交付。
三、环境与版本假设
3.1 基础假设
| 项目 |
说明 |
| 版本 |
MySQL 8.4.13 LTS（InnoDB，utf8mb4 + utf8mb4_0900_ai_ci 默认字符集） |
| 部署 |
自建：1 主（写）+ 1 从（读，业务部分查询+offset 借此消化） |
| 实例规格 |
16C / 64G / NVMe 盘，buffer pool 当前 48G |
| 业务库 |
bizdb，核心表 orders（约 6200 万行） |
| 变更方式 |
凭权限账号在有变更窗口由双人在场执行 |
orders 表结构（简化）：
CREATE TABLE `orders` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint unsigned NOT NULL COMMENT '下单用户ID',
`status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '订单状态',
`amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '金额',
`merchant_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '商家ID',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_merchant_created` (`merchant_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
3.2 与 8.0 / 9.7 / RDS 的已知差异（不确认不乱写）
| 差异点 |
8.0（EOL） |
8.4 LTS（本篇基准） |
9.7 LTS |
| redo 日志参数 |
innodb_log_file_size 可用（推荐 innodb_redo_log_capacity，8.0.30+） |
旧参数已移除，只能用 innodb_redo_log_capacity |
同 8.4 |
| 默认认证插件 |
mysql_native_password 可用 |
默认禁用 mysql_native_password |
延续 |
SHOW SLAVE STATUS |
可用（已 deprecated） |
删除，一律 SHOW REPLICA STATUS |
同 8.4 |
RDS 常见差异：
- 参数走控制台，"SET PERSIST" 与 SUPER 权限动作被管制；
- 慢日志通过平台导出/内置表格，不一定可写 slow_query_log_file；
- DDL 大表提供 gh-ost 或 PTOSC 内置封装，直接 ALTER 有平台保护限制；
- 云平台只暴露基础指标，我们需要自建 exporter 或使用平台自带监控。
四、前置检查：你能动什么、动了谁看得见
4.1 先确认自己的权限
-- 目的：确认你具备查询/改参数/开慢日志的能力
SHOW GRANTS FOR CURRENT_USER();
输出示例：
GRANT SELECT, PROCESS, SUPER ON *.* TO `dba_ops`@`10.%`
| 想做的事 |
需要的权限 |
说明 |
| 看会话 |
PROCESS |
SHOW PROCESSLIST 能看到别人的会话，否则只看见自己 |
| 改全局参数 |
SYSTEM_VARIABLES_ADMIN 或 SUPER |
SET GLOBAL/SET PERSIST |
| 开慢日志 |
SLOW_QUERY_LOG_ADMIN（8.0+） |
或 SUPER 覆盖 |
KILL <id> |
CONNECTION_ADMIN 或 SUPER |
高风险，见第八节 |
| DDL |
ALTER on 库 |
还有 INDEX/CREATE 等按需 |
4.2 记录当前参数快照（排查基准，出问题好回滚）
-- 目的：把本次会话要涉及的参数快照下来（回滚与复盘的基准，务必留存）
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
'innodb_buffer_pool_size',
'innodb_redo_log_capacity',
'long_query_time',
'slow_query_log',
'log_timestamps',
'tmp_table_size',
'max_heap_table_size',
'lock_wait_timeout'
);
快照输出：
innodb_buffer_pool_size 51539607552
innodb_redo_log_capacity 104857600
long_query_time 10.000000
slow_query_log OFF
log_timestamps UTC
tmp_table_size 16777216
max_heap_table_size 16777216
lock_wait_timeout 31536000
判断依据：slow_query_log=OFF、long_query_time=10s——当前空闲峰 10 秒才写一条慢日志，等于没开慢日志；log_timestamps=UTC 是 8.x 默认，日志时间与系统时区差 8 小时，排查时容易看错事件顺序，本次顺手改掉。
4.3 服务器资源基线（先确认是 Database 的锅还是机器的锅）
# 目的：DB 主机 CPU / IO / 内存快照
uptime; free -g; df -h /data; iostat -dx 1 5
基线输出：
load average: 6.42, 6.10, 5.86 （16 核，负载偏高）
free -g: total used free buff/cache
Mem: 63G 59G 2G 1.2G （buffer pool 48G 常驻，具体看 mysqld RSS）
r_await 0.3ms, w_await 1.8ms, %util 平均 45%
结论先行：IO 不堵（NVMe）、内存接近满但 buffer pool 主导属正常；load 高主要由用户态 CPU 抬升。问题在 CPU 型慢查询，先不怀疑磁盘。
五、初判：把“越来越慢”拆成三种慢
“慢”只有三种根因，任何一条都对应明确的证据：
| 类型 |
释义 |
证据来源 |
| 执行慢 |
SQL 本身扫描/JOIN/排序放大了 |
EXPLAIN、rows_examined |
| 等待慢 |
查询本身不差，但在排队（锁、汇报线程） |
PROCESSLIST 的 state、MDL/innodb waits |
| 资源慢 |
算得不慢但内存/IO/CPU 竞争让每步都变慢 |
命中率、临时表落盘、OS wait |
5.1 PROCESSLIST 抓现场（目的：看“现在谁在跑哪一类的活”）
SHOW FULL PROCESSLIST;
输出示例：
+-----+----------+-----------+--------+---------+------+--------------+--------------------------+
| Id | User | db | Command| Time | State | Info |
+-----+----------+-----------+--------+---------+------+--------------+--------------------------+
| 12 | app_rw | bizdb | Query | 142 | Sending data | SELECT COUNT(*) FROM orde...
| 13 | app_rw | bizdb | Query | 138 | Sending data | SELECT COUNT(*) FROM orde...
| 15 | app_ro | bizdb | Query | 87 | Sending data | SELECT ... WHERE user_id=...
| 220 | rpt_user | bizdb | Query | 1802 | Sending data | SELECT user_id , SUM(amoun...
| 310 | app_rw | NULL | Sleep | 3 | | NULL
+-----+----------+-----------+--------+---------+------+--------------+--------------------------+
判断依据：
- 多条
COUNT(*) 类查询挂在 Sending data 几百秒——执行慢（扫描量大），不是等待锁。
- 220 号报表会话 1802 秒——长查询，需要单独关注（若此时并发一条 DDL，它会把 MDL 排队的坑引爆，见第十节）。
- state 大量
Waiting for table metadata lock / Waiting for table lock 才是“等待慢”的信号，本例没有，先按顺序走。
5.2 三类慢的排查对照表
| 类型 |
典型证据 |
典型根因 |
修复方向 |
| 执行慢 |
无锁等待 + rows 大 + CPU 高 |
缺索引/统计信息/SQL 写法 |
索引、SQL、统计 |
| 等待慢 |
processlist 卡在锁状态 |
MDL/行锁/外键、外键级联 |
kill 或降密度，改 SQL |
| 资源慢 |
buffer 命中率掉盘 IO/CPU 比率 |
tmp 表落盘/swap/磁盘坏 |
资源配置与容量 |
六、步骤一：慢日志与 digest 指标落地
6.1 慢日志配置（不走配置也能临时开；落配置为了重启仍生效）
方式 A：运行期直接生效（SLOW_QUERY_LOG_ADMIN 权限，RDS 走控制台）：
-- 目的：先把慢日志开了，问题不等人；参数动态生效，无需重启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.3;
SET GLOBAL log_timestamps = SYSTEM;
方式 B：配置文件持久化（路径 RHEL 系 /etc/my.cnf，Ubuntu /etc/mysql/mysql.conf.d/mysqld.cnf）：
[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
long_query_time = 0.3
log_timestamps = SYSTEM
生效方式与验证：SET GLOBAL 立即生效；my.cnf 只在重启时读到，改完用 SHOW VARIABLES 确认：
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
验证输出：
slow_query_log ON
slow_query_log_file /data/mysql/slow.log
long_query_time 0.300000
权限收尾：慢日志文件可能带敏感 SQL（含参数值）。权限归 mysql:mysql 0640，日志目录禁止其余人读；日志文件轮转要配套：
# logrotate 示例（/etc/logrotate.d/mysql-slow）
/data/mysql/slow.log {
size 512M
rotate 14
compress
missingok
notifempty
postrotate
# 用 FLUSH SLOW LOGS 换新文件，比 kill -HUP 干净
mysql --login-path=admin -e 'FLUSH SLOW LOGS;'
endscript
}
凭证实践：--login-path=admin 来自先跑一次 mysql_config_editor set --login-path=admin --host=127.0.0.1 --user=admin --password（密码加密存储于 ~/.mylogin.cnf），避免在任何脚本或命令行明文出现密码。
慢日志的性能代价：慢日志本身有文件写入开销，量级不大，但两个坑有明显放大效应：
- 不要顺手开
log_queries_not_using_indexes：它会把所有不走索引的查询（包括定时探活、内部统计）全灌进慢日志，高峰期日志体积和 IO 放大若干倍；要用也必须配 log_throttle_queries_not_using_indexes 限流。
- 文件应与数据盘分离存放，或至少做好轮转（上面 logrotate 已给出）；磁盘满时 MySQL 行为不友好，监控别漏掉该目录的容量。
退出机制：排查结束要关掉或放宽阈值——SET GLOBAL long_query_time 回到基线值，避免长期以低阈值刷日志。
6.2 看懂一行慢日志
# Time: 2026-10-20T19:22:14.100123+08:00
# User@Host: rpt_user[rpt_user] @ app-3 [10.2.3.3] Id: 220
# Query_time: 2.561 Lock_time: 0.000 Rows_sent: 1 Rows_examined: 62817821
SELECT COUNT(*) FROM orders WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';
读法：
Query_time 2.561s vs Lock_time 0.000——锁不背锅，纯执行慢。Rows_examined: 62,817,821 vs Rows_sent: 1——读了 6280 万行只为返回 1 行，典型的扫描方式错误（缺索引或计划退化）。这两个数就是第八节 DDL 前后的核心对比项。
6.3 digest 聚合（比逐条看日志快得多，同一条模板 SQL 合并）
-- 目的：按模板聚合，找出最“贵”的 Top 慢 SQL（注意 TIMER 单位是皮秒）
SELECT
SCHEMA_NAME, DIGEST_TEXT,
COUNT_STAR,
ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
ROUND(AVG_TIMER_WAIT/1e9, 1) AS avg_ms,
ROUND(MAX_TIMER_WAIT/1e9, 0) AS max_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'bizdb' AND DIGEST_TEXT LIKE '%orders%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;
聚合输出：
SCHEMA_NAME DIGEST_TEXT COUNT_STAR total_s avg_ms max_ms
bizdb SELECT COUNT(*) FROM `orders` WHERE ... 1254 489.12 389.9 4990
bizdb SELECT SUM( `amount` ) FROM `orders` GROUP BY ...30 618.55 20618.3 24011
...
判断依据：COUNT(*) 类高频且总时间大；报表类是长尾单体最贵。两个都值得整改，但先修高频（对业务影响面积大）。
pt-query-digest（Percona Toolkit）同样干这事，直接解析慢日志文件并按总耗时聚合；具体到 8.4 的兼容细节以你所用版本的官方说明为准。示例如下：
pt-query-digest /data/mysql/slow.log | head -n 12
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ==================== ============== ===== ======= ===== =============
# 1 0xF23A6D9A0F1B2C3D 489.1232 45.7% 1254 0.3901 0.13 SELECT orders
# 2 0x9C1D2E3F4A5B6C7D 618.5531 57.9% 30 20.6183 0.31 SELECT orders orders
# MISC 0xMANY 242.1140 22.6% 88876 0.0027 0.0 <9 ITEMS>
本篇只用内置视图（performance_schema/sys）完成全部闭环，不引入额外工具依赖。
七、复现与证据链：EXPLAIN 看执行计划
7.1 抓到慢 SQL 的原始形态
从 processlist 拿到的业务 SQL（参数会变）：
-- 业务“单用户状态统计”接口，查 30 天订单状态汇总
SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001
AND status = 2
AND created_at >= '2026-09-01';
7.2 EXPLAIN（先看计划，不看数据）
EXPLAIN
SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001
AND status = 2
AND created_at >= '2026-09-01';
EXPLAIN 输出：
+----+-------------+--------+-------+----------------------+-----------+---------+------+----------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+-------+----------------------+-----------+---------+------+----------+-------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | 62817821 | 0.00 | Using where |
+----+-------------+--------+-------+----------------------+-----------+---------+------+----------+-------------+
判断依据逐项：
type=ALL、key=NULL——全表扫，索引一个都没用上，主因落在执行计划错误的扫描方式上。直接根因找到了；6200 万行的体量只是背景，问题在于扫描方式把这个体量放大到了极限。Extra=Using where（没有 Using index condition/Using index）——二级索引没有参与，必须整表回源读。- 顺带核对可疑点：SQL 里参数是否类型匹配？本例
user_id 是 bigint，即便业务传入字符串常量，MySQL 比较时会把常量转成数字，索引仍可用；真正坑的是 varchar 列上存数字、或 join 两侧字符集/排序规则不一致的场景（隐式转换会让索引失效）。本例列类型匹配，先不归因到隐式转换。
7.3 EXPLAIN ANALYZE（8.0.18+：实际跑一遍的真实耗时与行数）
EXPLAIN ANALYZE
SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';
输出：
-> Aggregate: count(1), sum(orders.amount) (cost=6.5e6 rows=1) (actual time=2668..2668 rows=1 loops=1)
-> Filter: ((orders.status = 2) and (orders.created_at >= TIMESTAMP'2026-09-01'))
(actual time=0.25..2661 rows=42 loops=1)
-> Table scan on orders (cost=5.9e6 rows=6.28e7) (actual time=0.06..2198 rows=6.28e7 loops=1)
判断依据：全表扫 6280 万行耗时约 2.2s，过滤后只剩 42 行——6280 万 : 42 ≈ 149 万倍的读写放大。扫描 6280 万行只为产出 42 行结果，前后落差的 2.6s 全花在了不该读的行上。
7.4 优化器为什么弃用索引：optimizer trace（“计划退化”的最后一块证据）
EXPLAIN 说明计划错了，trace 说明为什么错——这对复盘归因和说服开发同样重要：
-- 目的：开启 optimizer trace，回看优化器在候选访问路径之间怎么估成本
SET optimizer_trace = "enabled=on";
SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
trace 关键片段：
{
"considered_execution_plans": [
{
"table": "`orders`",
"best_access_path": {
"considered_access_paths": [
{ "access_type": "ref", "index": "idx_user_id", "rows": 217400, "cost": 9.2e5, "chosen": false },
{ "access_type": "scan", "rows": 62817821, "cost": 7.6e5, "chosen": true }
]
}
}
]
}
判断依据：idx_user_id 的访问路径 chosen:false，原因写在数字里——估算要访问 21.7 万行、成本 9.2e5，高于全表扫的 7.6e5。这条 trace 就是“计划因数据增长而退化”的直接证据，比“我猜缺索引”有说服力一个量级。用完即关（trace 只影响当前会话）：SET optimizer_trace = "enabled=off";
EXPLAIN FORMAT=TREE
SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';
输出：
-> Aggregate: count(1), sum(orders.amount) (cost=7.6e6 rows=1)
-> Filter: (orders.status = 2) and (orders.created_at >= TIMESTAMP'2026-09-01') (cost=6.1e6)
-> Table scan on orders (cost=6.1e6 rows=62817821)
判断依据：树形输出和 EXPLAIN ANALYZE 同构，只是没有 actual 行；适合先快速看结构，再决定要不要跑 ANALYZE 版本（后者会真执行，超长查询要谨慎）。
八、修复闭环：加复合索引，一次到位
8.1 根因表述（带证据）
- orders 表随业务增长到 6200 万行；查询条件是
user_id + status + created_at 三段。
- 现有索引
idx_user_id(user_id) 是单列索引；优化器基于持久化统计估算索引基数，认为该 user 键的二级索引区间已过大（随时间增长到翻过“成本临界点”的程度），走 idx_user_id 回表的成本反而估得比全表扫高，于是放弃索引选了 type=ALL。
- 越来越慢的机理：这个 user 的数据量随时间持续增长 → 单列索引候选区间行数持续增长 → 越过成本临界点后某天优化器“弃索引选全表” → 慢一个数量级，且之后随全表继续增长继续变慢。慢的斜率来自数据，但可修的点始终在索引结构上。
8.2 索引方案（索引列顺序的选择依据）
-- 目标索引：(user_id, status, created_at)
-- 等值条件放前面（user_id, status），范围列放最后（created_at）
ALTER TABLE orders
ADD INDEX idx_user_status_created (user_id, status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;
索引列顺序的依据：等值条件（user_id =、status =）在前，可以把 key 直接锁到指定前缀；created_at >= 是范围条件，放最后还能继续利用索引有序性做前缀扫描。顺序错放（created_at 在中间）会让后面的等值条件变成区间内过滤，读放大依旧。
8.3 Online DDL 的三道防线（高风险操作：ALTER 在主库执行）
影响范围 & 风险：ALGORITHM=INPLACE 允许读写并行，但执行瞬间需要短暂的 MDL-X（元数据独占锁）。若此时有长事务/长查询持有该表 MDL-S，你的 ALTER 会排在后面，后续所有连接该表的查询全部排队——一个“上线新索引”的动作变成一次瞬时大故障。
防线 1 —— 提前看长事务，别带病上线：
-- 目的：找出执行时长超过 10 秒的活动事务（trx），他们持有 MDL
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec, trx_state,
trx_rows_locked
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10
ORDER BY trx_started;
示例输出（长事务是典型挡路者，先处理再上线）：
trx_id trx_started sec trx_state trx_rows_locked
4211 2026-10-20 19:05:02 1841 RUNNING 8
-- KILL 属高风险操作：先用 Id + User + Host 核对会话归属（这是谁家的连接、执行的是什么），
-- 通知业务方确认后再终止，并记录工单
KILL 220;
防线 2 —— 给 ALTER 自设超时，避免自己成为凶手：
-- 目的：会话级设置，只影响本会话；如果拿不到 MDL，30 秒自动放弃，
-- 避免把自己变成全表阻塞的源头
SET SESSION lock_wait_timeout = 30;
防线 3 —— 在测试库预演（灰度的意义）：
# 用生产最近备份恢复的测试库（或预发从库），先执行同一 ALTER，再跑 EXPLAIN / EXPLAIN ANALYZE
# 验证执行计划与真实耗时。不要在生产从库上手工 ALTER：会造成主从结构不一致。
8.4 预演库上的计划验证（真实跑过 DDL 的环境，非生产）
-- 测试库（生产备份恢复的同结构表）已执行同一 ALTER，这里验证计划
EXPLAIN
SELECT COUNT(*) FROM orders
WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';
预演 EXPLAIN 输出：
+----+-------------+--------+-------+------------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | orders | range | idx_user_status_created| 9 | NULL | 42 | Using index condition |
+----+-------------+--------+-------+------------------------+------------------+---------+------+------+-----------------------+
判断依据：type=range，rows=42，与目标一致。key_len=9 解释如下：bigint unsigned 8 字节 + tinyint unsigned 1 字节 = 9，正好锁定前两段等值前缀；两列均为 NOT NULL，不需要为 NULL 值留出额外的 1 字节。
8.5 主库正式执行（变更窗口中，点对点作业记录）
-- 变更窗口内执行，前面两道防线已验证
SET SESSION lock_wait_timeout = 30;
ALTER TABLE orders
ADD INDEX idx_user_status_created (user_id, status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;
执行期间旁路监控：
SHOW PROCESSLIST; -- 看 State 是否出现 Waiting for table metadata lock 长时间堆积
完成确认：
SHOW INDEX FROM orders WHERE Key_name = 'idx_user_status_created';
ANALYZE TABLE orders; -- 8.0+ 起统计持久化；执行后优化器立即拿到新索引的基数
Table Non_unique Key_name Seq_in_index Column_name Cardinality
orders 1 idx_user_status_created 1 user_id 13984512
orders 1 idx_user_status_created 2 status 4
orders 1 idx_user_status_created 3 created_at 61789442
8.6 验证：同一条 SQL 走新计划 + 真实执行
EXPLAIN ANALYZE
SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';
产出：
-> Aggregate: count(1), sum(orders.amount) (cost=0.099 rows=1) (actual time=0.19..0.19 rows=1 loops=1)
-> Index range scan on orders using idx_user_status_created
(actual time=0.11..0.13 rows=42 loops=1)
前后对比：
| 阶段 |
rows 读取 |
实际耗时 |
| 前 |
62,817,821 |
≈2.66 s |
| 后 |
42 |
≈0.19 ms |
业务侧验证：微服务监控看接口 RT 曲线回落到毫秒级，从库同步正常、复制延迟回零：
SHOW REPLICA STATUS\G -- 8.4 用 REPLICA；Seconds_Behind_Source 回 0，中继日志正常应用
8.7 上线之后：24 小时观察窗与固化
观察窗动作（DDL 完成后 24h 内）：
-- 1. 慢日志日志：新 SQL 模板不应再出现（digest 里该模板的 avg_ms 下降一个数量级）
SELECT ROUND(AVG_TIMER_WAIT/1e9,1) AS avg_ms, COUNT_STAR
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'bizdb' AND DIGEST_TEXT LIKE '%COUNT(*)%';
-- 2. 资源回落：CPU、threads_running、iops 回到基线
SHOW GLOBAL STATUS LIKE 'Threads_running';
-- 3. 复制侧：从库回放是否追平（-index 建立在从库要回放一遍，延迟会短暂抬升）
SHOW REPLICA STATUS\G
固化动作：
- 把前后两次 EXPLAIN/EXPLAIN ANALYZE、慢日志行、trace 输出打包进工单；这是未来“为什么这个索引在这里”的新人考题答案。
- runbook 增补：orders 表默认带
idx_user_status_created，删它就是事故；未来新复合索引按同样的三段式（等值→等值→范围）评审。
- 回归口径：把“扫描行数/返回行数”这个比值当成慢 SQL 的健康分，长期留档归档。
九、参数层：这次的“配置示例与生效方式”
9.1 buffer pool 命中率——看资源类型慢的证据
-- 目的：buffer pool 命中率应长期 >99%，明显下滑代表内存或 SQL 读放大异常
SELECT 1- (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads')
) / (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')
) AS miss_ratio;
miss_ratio
0.0007 （0.07%，健康；>0.01 才值得排查）
buffer pool 在线调整（8.0 起支持在线 resize，8.4 同）：
SET GLOBAL innodb_buffer_pool_size = 34359738368; -- 32G：预留给其他进程/临时缩容的例子
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_resize_status';
生效方式：后台按 chunk 分批扩缩，进度用上面那个状态值看；调整结束时显示 completed resizing buffer pool，失败会有 error 说明（伴随 InnoDB_buffer_pool_resize_status 记录阶段）。
调参依据：buffer pool 是 InnoDB 的内存领域，越大读命中率越高；但大到挤压 OS 甚至触发 swap 就得不偿失。常规经验：专用 DB 服务器，物理内存的 50%~70%。64G 的机器对应 32~45G，本例原 48G（75%）略偏高，可结合命中率长期数据决定是否下调。
9.2 redo 容量——8.4 生效方式（区别 8.0 的旧参数）
-- 8.4：统一入口是 innodb_redo_log_capacity；旧 innodb_log_file_size 已删
SET GLOBAL innodb_redo_log_capacity = 1073741824; -- 1G
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
设置依据：高写入峰值场景（批量导入、大量 binlog）适当调大，避免 checkpoint 过频造成延迟抖动；估算思路是“覆盖一段写入窗口的 redo 量”，一般从 1G~2G 起步，按每秒写入量与磁盘容量预算决定上限。改完观察磁盘占用与恢复时间（崩溃恢复要重放 redo）。
9.3 内部临时表——8.0+ 用 TempTable 引擎，参数与 5.7 时代不一样
-- 先看临时表是否在落盘：Created_tmp_disk_tables 增长快说明内存额度不够
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW VARIABLES LIKE 'temptable%';
Created_tmp_tables 1204657
Created_tmp_disk_tables 18320 （落盘占比 1.5%，尚可；超过 10% 再调）
temptable_max_ram 1073741824 （默认 1G 内存额度）
temptable_max_mmap 0 （默认不使用 mmap 溢出）
-- 8.0+ 默认 internal_tmp_mem_storage_engine=TempTable；内存额度不够时溢出到 InnoDB 磁盘临时表
SET PERSIST temptable_max_ram = 4294967296; -- 4G（示例；须同时受可用内存约束）
生效方式：SET PERSIST 立即生效且重启后仍保留（写进 mysqld-auto.cnf），需 SYSTEM_VARIABLES_ADMIN 权限。
名词区分：tmp_table_size/max_heap_table_size 管的是显式 CREATE TABLE ... ENGINE=MEMORY 与 MEMORY 引擎型内部临时表；8.0+ 默认 TempTable 引擎看的是 temptable_max_ram/temptable_max_mmap。改“临时表落盘多”的问题先确认当前引擎再改对应参数，别两套参数混着调。
RDS 注意：SET PERSIST 不允许时，走控制台参数组；SET GLOBAL 只覆盖当前运行，重启会被平台参数组覆盖，归口以平台为准。
9.4 lock_wait_timeout——DDL 拿不到锁时的行为
-- 目的：DDL 拿不到 MDL 时的排队上限；默认 31536000 秒（一年），意味着会一直排
-- 在业务高峰里就意味着全网阻塞。全局收敛到 30 分钟，DDL 前仍在会话级再压短（见第八节）
SET GLOBAL lock_wait_timeout = 1800;
9.5 binlog 磁盘占用——另一种“越来越慢”的真实场景
磁盘被 binlog 慢慢吃满，写放大后触发刷盘等待、甚至写入停滞，表现也是“越来越慢”。作为标准盘面：
SHOW BINARY LOGS; -- 看当前 binlog 文件集与占用
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000121 | 1073741824| No |
| binlog.000122 | 1073741824| No |
| ...（合计 92 个文件 ≈ 92G） |
+---------------+------------+-----------+
-- 到期清理周期（默认 2592000 秒 = 30 天；按备份/回放需求收敛）
SELECT @@binlog_expire_logs_seconds;
SET PERSIST binlog_expire_logs_seconds = 1209600; -- 14 天（示例）
高风险操作提示（PURGE BINARY LOGS）：手工 PURGE BINARY LOGS BEFORE '<时间>'; 会立即删除到期文件。必须先确认所有从库都已拉取到该位点之后（从库还在拉取的 binlog 被删，复制直接断），且复制流依赖、备份依赖梳理清楚；正常情况交给 binlog_expire_logs_seconds 自动过期更稳。
判断依据：SHOW BINARY LOGS 的总占用应纳入容量基线；主从延迟波动、binlog 总大小环比上涨，都要有趋势图而不是靠告警爆。
十、留一个案例：真正“等待慢”的小闭环（MDL 事故）
10.1 现象
凌晨 2 点，监控告警同时报了接口 RT 抬升与应用连接池耗尽；processlist 里几十条会话全卡在 Waiting for table metadata lock。
10.2 初判：看谁在阻塞谁
-- 目的：直接输出“等锁对”（阻塞者/被阻塞者），sys schema 内置于 8.0+
SELECT * FROM sys.schema_table_lock_waits\G
Waiting PID 311
Waiting User app_rw
Waiting Lock Type MDL_SHARED_READ
Blocking PID 220
Blocking User rpt_user
Blocking Lock Type MDL_SHARED_READ -- 长查询持共享锁
Blocking Query: SELECT user_id, SUM(amount) ...
判断依据：Blocking PID 220 就是第三节里那条 1800 秒报表查询，说明阻塞源头不是 DDL 本身，而是长查询 + DDL + 连接排队的传播链。
10.3 根因
报表会话 220 还挂着一条跑了 1800 秒的 SELECT，它持有 orders 的 MDL-S；随后值班跑的 ALTER TABLE orders ... ADD INDEX ... 需要升级 MDL，接管队列里排在后面；因为 lock_wait_timeout 默认一年，ALTER 无限排队，它后面的每个新连接（读）也排队——连接池耗尽是这条传播链的尾端。
10.4 修复与验证
-- 三步闭环：终止阻塞源 -> DDL 完成 -> 业务恢复
KILL 220; -- 高风险：先核对连接归属（rpt_user@报表机4）
SHOW FULL PROCESSLIST; -- ALTER 开始；几分钟内（INPLACE building index）
SELECT COUNT(*) FROM information_schema.processlist
WHERE State like 'Waiting for table metadata lock'; -- 预期归零
10.5 回滚与复盘
- 回滚预案：ALTER 处于 INPLACE 阶段时可以
KILL 该 ALTER 连接，MySQL 会自动清理并回滚临时索引结构，数据无损；超大表的清理本身也需要时间，KILL 前先评估。
- 复盘动作：全局把
lock_wait_timeout 收敛到 600（宁可 DDL 拿不到锁失败重试，也不要业务连接排队）；报表平台的夜间长查询纳入白名单并在变更前同步给 DBA。
10.6 再一个小闭环：行锁等待（写入突然扎堆的另一半真相）
现象：某天下午出现“单笔提交变慢”，processlist 里多条会话在 updating 但业务反馈接口超时；没有 MDL 排队。
检查：
-- 目的：直接看行锁等待链（阻塞事务、被阻塞事务、SQL、等了多久）
SELECT * FROM sys.innodb_lock_waits\G
wait_started : 2026-10-20 15:22:14
wait_age : 00:01:48
waiting_pid : 115
blocking_pid : 98
blocking_trx_started : 2026-10-20 15:20:03
blocking_query : UPDATE orders SET status=... WHERE id IN (...) -- 大事务
sql_kill_blocking_query : KILL 98
根因：会话 98 一个大 UPDATE 同时锁几百个 id，后续需要这些 id 的事务全部排队盯它 108 秒。
修复：先 KILL 98 解除（高风险：核对后执行）；开发侧把大 UPDATE 拆批（每批 100~500 行）并提交后释放；同时把 innodb_lock_wait_timeout 保持默认 50s，配告警不让它默默扛。
验证：sys.innodb_lock_waits 返回为空、SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits' 增速回到基线。
复盘：长事务的监控阈值（trx 运行时长 > 60s 告警）、大事务拆批规范进代码 review 清单。
十一、监测与告警（把优化变成可维护的体系）
11.1 必要监控项（以 mysqld_exporter 指标为准，指标名以你部署版本为准）
# 慢查询速率：正常治理后应长期趋近 0
rate(mysql_global_status_slow_queries[5m]) > 0.1
# 突发并发（threads_running，上限参考 max_connections 与自家基线）
mysql_global_status_threads_running{instance="db-primary"} > 64
# 连接数饱和
mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.85
# 行锁等待次数（计数型指标，用 rate）
rate(mysql_global_status_innodb_row_lock_waits[5m]) > 0.5
# buffer pool 未命中率抬升
(1 - (mysql_global_status_innodb_buffer_pool_reads /
mysql_global_status_innodb_buffer_pool_read_requests)) > 0.001
阈值必须以自家基线校准：先观察 1-2 周，看常态值到多少才算异常；不同业务模型没有统一阈值。指标名以你部署的 mysqld_exporter 版本导出为准（不同版本命名略有出入）。
11.2 慢日志与 digest 告警（防止回到“人肉看日志”）
- 慢日志关键词告警：slow.log 尾部追一条 grep，命中 "Rows_examined: 数字位数≥7"（即百万行以上扫描）即上报警。
- digest 告警：performance_schema 里连续出现 avg_ms > 500 的模板 SQL，把模板文本发 IM 给 owner。
- 每月消化清单：Top 20 慢 SQL（按总耗时）交给开发团队迭代，按“扫描行数/返回行数”的比率长期跟踪。
11.3 日常巡检的固定四查（脚本化，每天早班第一次跑）
-- 1) 复制健康：延迟与通道报错
SHOW REPLICA STATUS\G -- 关注 Seconds_Behind_Source、Last_IO_Errno、Last_SQL_Errno
-- 2) 慢查询增速（从 status 计数器取 diff，不靠翻日志）
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 3) 长事务：超过 60s 的活动事务（阻塞类故障的早期信号）
SELECT trx_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec, trx_rows_locked
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;
-- 4) 连接水位
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
判断依据：四项里任何一项越过自家基线（不是三家文档里的通用值），当天处理而不是等告警来盯。请把这套 SQL 写进 cron/定时任务，输出发到值班群，巡检从“记得看”变成“不看就有人催”。
十二、风险与回滚手册
| 回滚目标 |
动作 |
生效方式 |
备注 |
| 新加的索引 |
ALTER TABLE orders DROP INDEX idx_user_status_created, ALGORITHM=INPLACE, LOCK=NONE; |
online，可立即执行 |
优化器回到原计划，业务回到改前行为 |
| 参数（buffer pool 等） |
SET GLOBAL innodb_buffer_pool_size = <原值>; |
动态，后台逐步生效 |
按 4.2 记录的参数快照回填 |
SET PERSIST 参数 |
RESET PERSIST tmp_table_size;（8.0+） |
移除持久化条目 |
只影响重启后的取值；当前进程内存值需另用 SET GLOBAL 同步 |
| 慢日志 |
SET GLOBAL slow_query_log = OFF; SET GLOBAL long_query_time 回原值 |
动态 |
— |
| KILL 大查询 |
无回滚动作；业务方登记 |
— |
检查业务链路，该条重试 |
| 应用 SQL 改造 |
发布系统回滚上一版本 / 配置中心开关切回 |
立即 |
SQL 改造类修复要有独立开关，别把回滚绑在 DDL 上 |
十三、生产注意事项
- 变更窗口与灰度：DDL 一律申请窗口；先从库/测试库验证计划；主库执行时提前核对长事务（8.3 防线 1）与
lock_wait_timeout 设短（8.3 防线 2）。
- 双人复核：DDL 语句逐字核对（一字之差会把
ADD INDEX 变成 DROP INDEX），变更结果写入值班记录。
- 备份先行：变更前主库执行
mysqldump --single-transaction --set-gtid-purged=OFF bizdb orders > /backup/orders_$(date +%F).sql（超大表改用 XtraBackup 做物理备份，恢复速度比逻辑导出快得多），给最坏情况留一条恢复路径。
- 超大表：
ALGORITHM=INPLACE 覆盖不到的操作（或对超大表不放心时）用影子表方案：gh-ost（读 binlog 异步切换）或 pt-online-schema-change（触发器双写）；两者都会带来主从延迟与一倍额外磁盘容量，使用前必须读工具文档并在测试环境预演。
- 权限最小化：查询账号有
PROCESS 但不给 DDL；只读账号日常用；DDL 由专门的变更账号在窗口内执行。
- SQL 上线前置：涉及 orders 这类核心表的新 SQL，提交时附 EXPLAIN 截图与预计扫描行数；超过 10 万行扫描的模板上线前单独评审，“越来越慢”的口袋从这里收紧。
差异点回顾（8.0 → 8.4 升级提醒）
innodb_log_file_size/innodb_log_files_in_group 在 8.4 已删除，只能用 innodb_redo_log_capacity。mysql_native_password 默认禁用；8.4 移除了 default_authentication_plugin 选项（由 authentication_policy 取代）。旧客户端/驱动若不支持 caching_sha2_password，优先升级驱动；确需兼容再按官方指引显式启用 native 插件（不推荐）。SHOW SLAVE STATUS 等旧 SLAVE 语法在 8.4 移除；SHOW REPLICA STATUS 取代。SET PERSIST 在 8.0+ 都可用；云 RDS 平台上常被禁用，以各家控制台的参数组入口为准。
总结
“越来越慢”的本质是“慢的斜率”，把这条斜率的原因找到，你就能把一个过去了的故障变成一个非常可复现的工程动作：
- 三类慢分开查：执行慢用 EXPLAIN 证据、等待慢用锁视图、资源慢用命中率与临时表落盘计数。三个证据链没到齐，不要急着调参数。
- 修复最小、验证可测：最小变更（一条 DDL 或一个参数），每次变更前后用同一 SQL 采样对比（本例 6280 万行扫描 → 42 行），并在预演库先跑一遍。
- Online DDL 三道防线：查长事务 → 限制
lock_wait_timeout → 从库灰度。
- 回滚要画出来：
DROP INDEX 也是 online；参数回滚用配置快照；SET PERSIST 提供了“改了就可退”的安全带。
- 沉淀监控：慢日志速率、digest 告警、
Rows_examined 关键词——让下次同样的斜率在“变慢”阶段就被发现，而不是业务先察觉。
这套流程走完，一次优化不只是修复一条 SQL，而是把一类问题从“上线之后才发现”挪到了“上线之前就拦住”。
发表于 半小时前
|
查看: 4|
回复: 0
