技术小册

主题

MySQL 生产踩坑 ​

一句话：这些故障都不是「MySQL 有 bug」，而是默认行为在特定负载下被放大；知道触发条件和排查命令，比记住结论更有用。

适用版本：MySQL 8.0（InnoDB）。参数默认值以 8.0 为准，8.4 的差异单独标注。

为什么需要 ​

生产事故的共同点是：现象看起来像 A，根因其实在 B。比如「磁盘涨」不一定是数据涨，「ALTER 卡住」不一定是 DDL 慢，「查询变慢」不一定是没索引。下面五个案例都按「现象 → 证据 → 根因 → 处置」组织。

核心机制 ​

案例 1：长事务拖垮整个库 ​

现象：CPU 不高，但磁盘使用率从 50% 快速涨到 95%，Undo 表空间持续变大。

证据：

sql
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING'
ORDER BY trx_started;

SHOW ENGINE INNODB STATUS\G   -- 看 History list length

根因：一个事务开了批量更新后长时间不提交。它持有的 ReadView 让 Undo 旧版本无法被 Purge 回收，版本链越来越长，所有查询的可见性判断都变慢，Undo 表空间持续膨胀。

处置：确认 trx_query 后 kill 对应会话，观察 History list length 是否下降。注意 Undo 表空间文件不会自动缩小，空间先变为可复用；需要缩容要按官方文档重建表空间或实例。

预防：应用侧设事务超时；监控 seconds_open > 60。注意 innodb_lock_wait_timeout=50 只管行锁等待，max_execution_time 只管只读 SELECT，都不等于事务超时；事务超时要在连接池/框架层实现。

案例 2：DELETE 全表忘加 WHERE ​

现象：执行 DELETE FROM orders; 后客户端卡住，Ctrl+C 也迟迟不返回。

证据：SHOW PROCESSLIST 看语句状态；performance_schema.data_locks 看锁范围；SHOW ENGINE INNODB STATUS\G 看事务和 Undo 增长。

根因：这不是「Ctrl+C 不回滚」的问题。InnoDB 的单条 DELETE 本身是一个语句级事务：KILL QUERY 会回滚这条语句已经做的修改，但回滚同样要扫 Undo，可能和删除一样久；如果客户端断开时事务未提交，连接关闭会回滚整个事务。真正危险的是：语句执行期间持有大量行锁、生成大量 Undo，期间业务写入被阻塞；如果语句最终执行完，删除就提交了。

处置：先 KILL 语句，再确认事务是否已结束（innodb_trx）；锁释放前不要继续压测。

预防：生产 DELETE/UPDATE 必须带 WHERE 和 LIMIT；开 sql_safe_updates=1；大表清理用 pt-archiver 分批，每批提交。

案例 3：ALTER 卡住，后续查询全排队 ​

现象：ALTER TABLE 长时间不返回，kill 掉 ALTER 后仍然卡，新的 SELECT 也开始排队。

证据：

sql
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 'orders';

SHOW PROCESSLIST;

根因：MDL（metadata lock）排他锁在等长事务释放共享锁。长事务不提交，MDL 共享锁不释放；ALTER 排队，后续 DML 又排在 ALTER 后面，形成队列，表现为数据库假死。

处置：找到持有 MDL 共享锁的长事务，kill 掉；确认 metadata_locks 中排他锁消失后再重试 DDL。

预防：DDL 前检查长事务；设置 lock_wait_timeout（8.0 默认 31536000 秒，等于不超时，必须显式调小）；大表 DDL 用 pt-osc / gh-ost。

案例 4：自增主键耗尽 ​

现象：INSERT 报 Duplicate entry '2147483647' for key 'PRIMARY'。

证据：

sql
SHOW CREATE TABLE orders;              -- 看主键类型
SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE TABLE_SCHEMA = 'order_db' AND TABLE_NAME = 'orders';

根因：INT 有符号上限约 21.47 亿；自增到达上限后不会复用已删除的 ID，下一条 INSERT 就会冲突。

处置：把主键改为 BIGINT UNSIGNED。BIGINT 有符号上限约 9.22×10^18（922 京），无符号约 1.84×10^19；改类型要评估锁表时间，用 Online DDL 或 gh-ost。

预防：核心表主键直接用 BIGINT；监控自增使用率，超过 70% 告警。

案例 5：统计信息过期，优化器选错索引 ​

现象：同一条查询从毫秒级变成秒级，EXPLAIN 显示用了不该用的索引或全表扫描。

证据：

sql
EXPLAIN SELECT ...;
SHOW INDEX FROM orders;   -- 看 Cardinality
ANALYZE TABLE orders;

根因：优化器按统计信息估算成本。大批量增删后统计信息过期，估算偏差导致选错计划。

处置：先 ANALYZE TABLE 再复测；仍不对时，核对索引设计与 SQL 写法，最后才考虑 FORCE INDEX 作为临时手段。

预防：大批量变更后主动 ANALYZE TABLE；核心 SQL 的 EXPLAIN 纳入上线检查；监控慢查询。

最小上手 ​

把上面的排查命令存成 runbook，故障时按顺序执行：

sql
-- 1. 谁在跑
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC
LIMIT 20;

-- 2. 有没有长事务
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING'
ORDER BY trx_started;

-- 3. 谁在等锁
SELECT * FROM sys.innodb_lock_waits\G

-- 4. 有没有 MDL 排队
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_NAME IS NOT NULL;

生产动作 ​

  • 长事务：告警阈值 60 秒；应用侧事务边界要短，RPC、消息、用户输入不要放事务里。
  • 误删/误改：sql_safe_updates=1；DELETE/UPDATE 带 LIMIT；大表用 pt-archiver；备份 + Binlog 能恢复到时间点。
  • MDL：DDL 前查长事务；lock_wait_timeout 调小；大表用 gh-ost/pt-osc。
  • 自增：主键用 BIGINT；使用率 70% 告警；不要依赖「删除后会复用」。
  • 统计信息：大批量变更后 ANALYZE TABLE；核心 SQL 上线前看 EXPLAIN。
  • 通用：所有 kill 操作前确认会话和事务；kill 不等于回滚完成，要观察锁和 Undo 是否释放。

边界 ​

  • 这些案例覆盖的是「配置/使用不当」导致的问题，不覆盖硬件故障、内核 bug、网络分区。
  • KILL 是急救，不是修复；根因在事务边界、SQL 写法或表设计。
  • 监控指标要结合业务基线，同一个 seconds_open 对不同业务的意义不同。

讲清楚 ​

生产上的 MySQL 故障，很多时候是默认行为在极端负载下被放大。长事务让 Undo 不能回收，磁盘和查询一起变慢；全表 DELETE 的问题不是「Ctrl+C 不回滚」，而是长时间持锁和生成大量 Undo；ALTER 卡住通常是长事务持有 MDL 共享锁，不是 DDL 本身慢；自增耗尽要在建表时就用 BIGINT；查询突然变慢先看统计信息和执行计划，不要盲目加索引。排查顺序固定为：谁在跑、有没有长事务、谁在等锁、有没有 MDL 排队。

把一门技术讲透到既懂也会用

技术小册 · 持续更新