第 31 章 MySQL面试：场景与调优

📖 本篇简介：本篇是面试场景题专场，考的是"线上真出事了你怎么办"——慢查询现场排查、大表 DDL、亿级数据设计、数据迁移、深分页、死锁。这些题没有标准答案，但有标准的排查思路和工具链。学完你应该能在白板上画出"从告警到定位到优化"的完整链路，并讲清 gh-ost、延迟关联这些实战手段。所有示例基于 MySQL 8.0 语法书写，本示例基于 MySQL 8.0 环境。

目录

• 本篇导读与答题思路
• 题目 1：线上数据库突然变慢，你的现场排查步骤
• 题目 2：一张 5000 万行的大表要加字段或加索引，怎么做不锁表
• 题目 3：亿级订单表怎么设计才能扛住高并发查询
• 题目 4：百万级数据迁移怎么做不停机
• 题目 5：深分页 LIMIT 1000000 20 特别慢，怎么优化
• 题目 6：线上出现死锁，怎么排查和处理
• 题目 7：一条慢 SQL 从 5 秒优化到 50 毫秒，你会怎么做
• 题目 8：数据库 CPU 突然飙高，磁盘 IO 却不高，怎么排查
• 速查表：场景调优速查
• 注意事项：场景题的答题套路与坑
• 练习题：动手自测与参考
• 复习清单

本篇导读与答题思路

场景题面试官不是要你背命令，而是看你的排障链路是否清晰、是否有敬畏心。答题时推荐用"四步走"框架：

1. 先止血：有没有可能先限流、降级、Kill 掉坏会话，把系统从卡死边缘拉回来。

2. 再定位：用什么工具（show processlist、慢日志、EXPLAIN、监控）找到根因。

3. 后优化：针对根因给方案（加索引、改 SQL、改架构）。

4. 防复发：监控、审计、变更流程怎么堵住下次。

记住一个原则：线上问题先备份、先快照，再动手。别在生产环境边猜边改。

场景题还有个加分动作：主动报"我会先做什么不做什么"。比如大表 DDL 时主动说"我不会在业务高峰直接 ALTER"，迁移时主动说"我会先在从库/影子库验证"。这种"自我约束"比技术细节更能让面试官相信你真上过线。

另外，答场景题一定要给量化目标。别说"优化一下查询"，要说"把 P95 从 2 秒压到 200 毫秒"；别说"迁移一下数据"，要说"预计 downtime 小于 30 秒、可回滚"。有数字的方案才像做过。

题目 1：线上数据库突然变慢，你的现场排查步骤

考点

• 现场止血意识、排查工具链（processlist、慢日志、监控指标）。
• 是 SQL 问题、锁问题、资源问题还是架构问题。
• 从现象到根因的假设验证顺序。

参考答案

我会按"先止血再定位"的顺序来：

第一步看现场：先 SHOW FULL PROCESSLIST 看当前在跑的会话，是不是有大量会话卡在同一个状态（比如 Waiting for table metadata lock、锁等待），把明显异常的会话 Kill 掉止血。

-- 查看当前所有连接在干什么，重点看 State 和 Time 列
SHOW FULL PROCESSLIST;

-- 发现某个长跑会话 id=12345 堵了别人，谨慎 Kill（先确认不是关键事务）
KILL 12345;

第二步看慢日志：开了慢查询日志的话，直接捞最近慢的 SQL：

-- 确认慢查询日志开关和阈值（超过 1 秒记下来）
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

第三步分方向定位：

• 是某条 SQL 突然变慢？拿 EXPLAIN 看是不是索引失效了（统计信息过期、数据分布变了）。
• 是锁等待？SHOW ENGINE INNODB STATUS 看 TRX 段和 LOCK 段。
• 是资源打满？看 CPU、IO、连接数监控，可能是连接数爆了或磁盘 IO 饱和。
• 是主从延迟？读写分离下从库延迟导致读旧数据，业务重试放大压力。

第四步给方案：针对根因加索引、改 SQL、扩容或限流，最后加监控告警防复发。

慢日志拿到后，用工具聚合而不是肉眼翻文件：

-- 临时把阈值调成 0，抓一次全量慢 SQL（慎用，量大会很多）
SET GLOBAL long_query_time = 0;
-- 抓完记得调回去
SET GLOBAL long_query_time = 1;

# 用 pt-query-digest 聚合慢日志，按耗时排序看 TOP N（本机未装 mysql，仅作命令示范）
# pt-query-digest /data/mysql/slow.log | head -50

追问

• 追问：怎么快速判断是不是锁问题？ SHOW ENGINE INNODB STATUS 里找 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 段，看锁等待链。
• 追问：Kill 会话有什么风险？ Kill 掉未提交事务会触发回滚，回滚期间仍占资源，所以 Kill 前要评估事务大小。

题目 2：一张 5000 万行的大表要加字段或加索引，怎么做不锁表

考点

• 直接 ALTER TABLE 在大表上的问题（MDL 锁、复制延迟）。
• 在线 DDL：MySQL 原生 INSTANT/INPLACE 与第三方 gh-ost、pt-online-schema-change。

参考答案

先说为什么不能直接 ALTER TABLE：在 5000 万行的大表上，早期版本的 ALTER 会锁全表、复制整表，期间业务读写阻塞，主从还会延迟几十分钟。

现代 MySQL 8.0 的改善：

• INSTANT：8.0.12 后加列（末尾）几乎瞬间完成，只改元数据。
• INPLACE：加索引等操作不锁表（仍有短暂 MDL），后台在线完成。

-- MySQL 8.0：末尾加列，INSTANT 算法，秒级完成
ALTER TABLE t_order ADD COLUMN remark VARCHAR(255), ALGORITHM = INSTANT, LOCK = NONE;

-- 加索引：INPLACE 在线做，不阻塞读写
ALTER TABLE t_order ADD INDEX idx_user (user_id), ALGORITHM = INPLACE, LOCK = NONE;

但超大表（亿级）或老版本，业界标准做法是用第三方在线 DDL 工具：

• pt-online-schema-change（pt-osc）：建一张影子表，分批把老表数据拷过去，用触发器同步增量。
• gh-ost（GitHub 开源）：不走触发器，直接读 binlog 回放增量，对主库压力更小，是现在的主流。

核心思想都是：新建一张结构正确的影子表，分批拷贝历史数据，通过 binlog/触发器追平增量，最后rename切换，全程业务几乎无感知。

追问

• 追问：ALGORITHM=INPLACE 就真的不锁吗？ 不是绝对。开始和结束会拿短暂的 MDL 锁，如果当时有长事务，MDL 会排队，把后面的查询全堵住。
• 追问：gh-ost 相比 pt-osc 好在哪？ 不用触发器，主库压力小，可以在从库上跑、可暂停限速，更适合大表。

题目 3：亿级订单表怎么设计才能扛住高并发查询

考点

• 单表容量上限的认知、分库分表、冷热分离、索引设计、读写分离组合拳。

参考答案

一句话：没有银弹，是"分库分表 + 索引 + 归档 + 缓存 + 读写分离"的组合拳。

分层来答：

1. 先做对索引：高频查询条件全部建联合索引，覆盖索引避免回表，别让一条 SQL 扫几十万行。

2. 冷热分离：近 3 个月的热数据放在线库，历史订单归档到冷库（甚至压缩、列存），在线表只留活跃数据。

3. 分库分表：按 user_id 哈希水平拆分，比如 8 库 64 表，把单表从亿级降到百万级。

4. 读写分离：写走主库，查询走从库，配合缓存（Redis）扛高频读。

5. 避免大事务和深分页：订单列表用游标分页（见下一题），不让一次查询拖垮实例。

-- 冷热分离：把 2 年前的订单归档到归档表，在线表只留近 2 年
INSERT INTO t_order_archive
SELECT * FROM t_order WHERE create_time < '2024-01-01';   -- 分批，每批 1000 条
DELETE FROM t_order WHERE create_time < '2024-01-01' LIMIT 1000;  -- 小批删，避免大事务

加分点：能说出"订单 ID 里嵌入用户 ID 作为路由基因"（order_id 的高几位 = user_id 的哈希），这样按 order_id 查单也能定位分片，不用额外映射表。

追问

• 追问：分库分表后怎么做全局排序分页？ 很难。一般限制"只能按时间倒序在单分片内翻页"，跨分片深分页用业务上拉加载 + 冗余字段解决。
• 追问：亿级表还需要统计报表怎么办？ 不要在在线库跑报表，用 binlog 同步到数仓（ClickHouse）去做聚合查询。

题目 4：百万级数据迁移怎么做不停机

考点

• 停机迁移 vs 在线迁移、双写、数据校验、灰度切流。
• 老库新库双写期间的一致性。

参考答案

一句话方案：存量先搬、增量追平、双写校验、灰度切流、最后下线老库。

标准在线迁移步骤：

1. 存量迁移：用工具（如 pt-archiver、自研 job）分批把老库数据抽到新库，每批 1000 条，控制速率不压垮库。

2. 增量同步：通过 binlog（Canal/Debezium）把老库的增量变更实时同步到新库，追平迁移期间的新写入。

3. 双写/切读：业务层先双写（同时写老库和新库），然后把读流量灰度切到新库（先 1%、再 50%、再 100%）。

4. 数据校验：写比对任务抽样校验两边数据一致，行数、checksum 对得上。

5. 下线老库：观察一两周稳定后，停双写、下线老库。

伪代码（说明双写思路，MySQL 8.0 语法）：

-- 迁移窗口内，业务代码对同一笔订单同时写老库和新库
-- 老库 t_order（旧库）
INSERT INTO t_order (id, user_id, amount) VALUES (1001, 2002, 99.5);
-- 新库 t_order_new（新库），结构一致
INSERT INTO t_order_new (id, user_id, amount) VALUES (1001, 2002, 99.5);
-- 两边都写成功才算成功；读流量按灰度比例切到新库

关键原则：任何一步都能回滚，切流是灰度的，一旦新库有问题立刻切回老库。

追问

• 追问：怎么保证两边数据最终一致？ 双写期间用消息队列+对账任务兜底，发现不一致自动修复；别指望双写强一致，网络抖动总会有小差异。
• 追问：Canal 是怎么工作的？ 模拟从库，伪装成一个 MySQL 从节点拉取主库 binlog，解析后投到 MQ，业务消费 MQ 写新库。

题目 5：深分页 LIMIT 1000000 20 特别慢，怎么优化

考点

• OFFSET 大的代价：扫描并丢弃前 N 行。
• 延迟关联（覆盖索引子查询）与游标/keyset 分页。

参考答案

先说慢的原因：LIMIT 1000000, 20 意味着 MySQL 要先扫描 100 万行再丢弃，只留 20 行，扫描成本随 offset 线性增长。

我用 SQLite 实跑过一个 10 万行订单表的对照实验（原理与 MySQL 8.0 一致），结果非常直观：

OFFSET 0     （第1页）: 平均 0.40 ms
OFFSET 50000 （深分页）: 平均 3.96 ms
游标 id>50000（keyset）: 平均 0.33 ms

offset 越大越慢，而记住上一页最后一条 id 的游标分页始终飞快。

优化方案一：延迟关联——先用覆盖索引查出主键，再回表，减少回表次数：

-- 原写法：扫描 100 万行再回表
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;

-- 优化：先在覆盖索引上定位主键，再用主键回表
SELECT o.* FROM t_order o
INNER JOIN (
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20   -- 只走索引，不回表
) AS t ON o.id = t.id;

优化方案二（推荐）：游标分页（keyset），前端带上上一页最后一条 id：

-- 前端传 last_id=1000000，直接定位，不扫描跳过
SELECT * FROM t_order
WHERE id > 1000000     -- 记住上一页末尾 id
ORDER BY id
LIMIT 20;

C 端"上拉加载下一页"场景优先用游标分页；后台必须跳页的场景才用延迟关联。

追问

• 追问：游标分页能跳页吗？ 不能，只能连续往下翻。要"第 100 页"这种跳页需求，后台管理系统接受 offset，但要限制最大页数。
• 追问：ORDER BY 不是主键怎么办？ 游标分页的排序列必须有索引且唯一（或联合 id 一起），否则翻页会漏数据或重复。

题目 6：线上出现死锁，怎么排查和处理

考点

• 死锁的四个必要条件、InnoDB 死锁日志、现场处理与预防。

参考答案

先说结论：死锁是两个事务互相持有对方要的锁，InnoDB 会主动选一个代价小的事务回滚打破死锁。排查用 SHOW ENGINE INNODB STATUS。

-- 查看最近一次死锁的详细日志（在 LATEST DETECTED DEADLOCK 段）
SHOW ENGINE INNODB STATUS;

日志里重点看：两个事务分别持了什么锁、等什么锁、执行了哪条 SQL。拿到 SQL 后分析加锁顺序。

常见死锁来源：

1. 多个事务以不同顺序更新同一批行：A 先锁 1 再锁 2，B 先锁 2 再锁 1，就死锁了。

2. 间隙锁互相等待插入：RR 下两个事务都持有相邻间隙的间隙锁，互相等对方释放再插入。

3. 唯一索引插入冲突：一个事务插入未提交，另一个事务插入同值会等锁，回滚时连锁反应。

处理与预防：

• 保持固定的加锁顺序（所有事务都按 id 升序更新）。
• 大事务拆小，缩短持锁时间。
• 业务捕获 1213 死锁错误并重试。

-- 业务代码伪逻辑：捕获死锁错误码 1213 后自动重试（最多 3 次）
-- MySQL 8.0 死锁会报 ERROR 1213 (40001): Deadlock found
-- 应用层 catch 后 sleep 一小段再重发同一事务即可

一个经典的顺序交叉死锁现场（两个会话）：

-- 会话 A：先锁 id=1，再想锁 id=2
BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;  -- 锁住 id=1
UPDATE account SET balance = balance - 10 WHERE id = 2;  -- 等 id=2 的锁

-- 会话 B：先锁 id=2，再想锁 id=1
BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 2;  -- 锁住 id=2
UPDATE account SET balance = balance - 10 WHERE id = 1;  -- 等 id=1 的锁 -> 死锁！
-- InnoDB 检测到循环等待，回滚其中一个事务，报 1213

解决办法：让所有事务都按 id 升序加锁，A 和 B 都先锁 id=1 再锁 id=2，就不会交叉。

追问

• 追问：InnoDB 怎么选回滚谁？ 选回滚代价小（修改行数少、undo 量小）的那个事务牺牲掉。
• 追问：死锁和锁等待区别？ 锁等待是单向等（等别人释放，最终会拿到），死锁是循环等（互相等，必须牺牲一个）。

题目 7：一条慢 SQL 从 5 秒优化到 50 毫秒，你会怎么做

考点

• 系统化优化流程：EXPLAIN → 索引 → SQL 改写 → 架构。
• 优化前后可量化验证。

参考答案

我会按这个流水线走，每一步都用 EXPLAIN 验证：

第 1 步：EXPLAIN 看问题在哪

-- 目标：把下面这条 5 秒的订单查询压到 50ms
EXPLAIN SELECT * FROM t_order
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY create_time DESC LIMIT 20;

重点看 type 是不是 ALL、key 是不是 NULL、rows 是不是很大、Extra 有没有 Using filesort。

第 2 步：补联合索引。发现没索引就建：

-- 等值列在前，排序列在后，顺序 (user_id, status, create_time)
ALTER TABLE t_order ADD INDEX idx_uid_status_time (user_id, status, create_time);

这样 WHERE 走索引、ORDER BY 也借索引顺序消除了 filesort，一步到位。

第 3 步：消灭 SELECT \*。只查需要的列，尽量让索引覆盖：

-- 只查列表页要的列，并把它们补进联合索引形成覆盖索引
SELECT id, amount, create_time FROM t_order
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY create_time DESC LIMIT 20;

第 4 步：检查写法坑。有没有隐式类型转换（字符串列用数字查）、有没有函数包裹索引列（WHERE DATE(create_time)=... 会让索引失效）。

第 5 步：还不够就上架构。加缓存、读写分离、冷热归档，把查询压力从数据库挪走。

追问

• 追问：加了索引还是慢？ 看是不是索引选错了，用 FORCE INDEX 验证；或者数据分布让优化器认为全表扫更快，需要 ANALYZE TABLE 更新统计信息。
• 追问：怎么衡量优化效果？ 优化前后各跑 10 次取平均，对比慢日志里的 Query_time，不要只看一次。

题目 8：数据库 CPU 突然飙高，磁盘 IO 却不高，怎么排查

考点

• CPU 高而 IO 不高，说明瓶颈在"计算"而非"读盘"。
• 常见根因：大量并发查询、临时表排序、全表扫描在内存里跑、连接数爆。
• 定位工具链。

参考答案

一句话结论：CPU 高、IO 不高，说明请求大部分在内存里"空转"，重点查是不是有大量并发小查询在做排序、聚合、正则，或者连接数爆了。

排查顺序：

1. 先看连接数和活跃会话：是不是突发流量把连接打满，每个会话都在跑 SQL。

-- 看当前连接数和最大连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- 看当前都在跑什么，找 State 集中的会话
SHOW FULL PROCESSLIST;

2. 看是不是大量排序/临时表：CPU 高常常是 Using filesort、Using temporary 在内存里频繁排序聚合。慢日志里找这类 SQL。

3. 看是不是全表扫描在内存里跑：数据量不大但被反复全表扫，Buffer Pool 命中后 IO 很低，但 CPU 一直算。用 SHOW STATUS LIKE 'Handler_read_rnd_next' 判断扫描量。

4. 看是不是锁等待的空转或重试风暴：上游超时重试放大了请求量。

常见根因表：

| 现象 | 可能根因 | 处理 |

|---|---|---|

| 连接数打满 | 突发流量/慢查询占着连接 | 限流、Kill 慢会话、加连接池 |

| CPU 高 IO 低 | 大量 filesort/临时表/全表扫 | 优化 SQL、加索引消除排序 |

| CPU 周期性高 | 定时任务/报表批跑 | 错峰、限流、迁到从库 |

追问

• 追问：怎么区分是 MySQL 自己 CPU 高还是机器上别的进程？ 先 top 看 mysqld 进程占比，再进 MySQL 看 Threads_running，别一上来就改库。
• 追问：连接数满了但没慢查询？ 可能是连接泄漏（应用拿了连接不还），查连接池配置和长连接超时 wait_timeout。

速查表：场景调优速查

| 场景 | 首选方案 | 关键命令/工具 |

|---|---|---|

| DB 突然变慢 | 先 processlist 止血，再慢日志+EXPLAIN 定位 | SHOW FULL PROCESSLIST |

| 大表加列 | 8.0 用 INSTANT，超大表用 gh-ost | ALGORITHM=INPLACE |

| 亿级表 | 分库分表+冷热分离+缓存+读写分离 | 按 user_id 哈希 |

| 不停机迁移 | 存量搬+binlog 追增量+双写灰度切流 | Canal/Canal |

| 深分页 | 游标 keyset 分页，退而求其次延迟关联 | WHERE id > last_id |

| 死锁 | 看 INNODB STATUS，统一加锁顺序，业务重试 | 错误码 1213 |

| 慢 SQL | EXPLAIN→联合索引→消灭 SELECT *→覆盖索引 | EXPLAIN ANALYZE（8.0） |

注意事项：场景题的答题套路与坑

1. 一上来就说加索引：场景题要先讲排查链路，索引只是定位后的手段之一。

2. 忘了"先止血"：线上问题第一步是 Kill 坏会话、限流降级，不是慢慢查。

3. 大表 DDL 说直接 ALTER：必须提 gh-ost/pt-osc，否则暴露没做过。

4. 迁移方案不提双写和回滚：在线迁移的精髓是"随时可切回老库"，不说这点不专业。

5. 深分页只说 LIMIT 改小：真正的优化是游标分页，不是限制用户翻几页。

6. 死锁只会说"加索引"：根因常常是加锁顺序不一致，要从业务事务顺序上改。

7. CPU 高就想着加机器：先看是不是 SQL 在内存里空转（filesort/临时表），加机器前先把慢 SQL 干掉，否则扩了也白扩。

8. 迁移/DDL 不提回滚预案：任何线上变更都要先说"怎么撤回"，没有回滚方案的方案不是完整方案。

练习题：动手自测与参考

练习 1：业务反馈订单列表越翻越慢，你怀疑是深分页，怎么在数据库层验证？

<details>

<summary>参考答案</summary>

分别执行 LIMIT 0,20、LIMIT 100000,20、LIMIT 500000,20，对比慢日志里的耗时；再用 EXPLAIN 看 rows 和 Extra。如果随 offset 线性变慢，就是深分页问题，改 keyset 分页。

</details>

练习 2：凌晨要给 8000 万行的 t_order 加一个普通索引，业务 9 点上班不能影响，你怎么安排？

<details>

<summary>参考答案</summary>

用 gh-ost 在从库上先跑（或主库低峰限速跑），分批拷贝影子表、追 binlog 增量，最后低峰期 rename 切换；切换前后都有短暂 MDL，选凌晨业务最低谷做，并备好回滚方案。

</details>

练习 3：应用日志频繁出现 Deadlock found when trying to get lock，除了重试还要做什么？

<details>

<summary>参考答案</summary>

抓 SHOW ENGINE INNODB STATUS 的死锁段，找出两个事务加锁顺序，统一业务代码里更新多行的顺序；把大事务拆小缩短持锁时间；重试只是兜底。

</details>

练习 4：业务上线后慢日志突然多了一大批 Using filesort 的 SQL，你优先从哪类 SQL 入手优化？

<details>

<summary>参考答案</summary>

优先从"带 ORDER BY 且结果集大"的 SQL 入手：给排序列和前面的等值过滤列建联合索引，让排序直接借索引顺序完成，消除 filesort；结果集小的 filesort 内存里就排完了，不急着优化。

</details>

练习 5：要把一张 100 万行的老表迁到新结构，DBA 让你写迁移方案，你会分哪几步？

<details>

<summary>参考答案</summary>

① 低峰分批抽存量（每批 1000）；② binlog 追增量；③ 双写+灰度切读；④ 抽样对账校验；⑤ 观察一周后停双写下线老表；全程保留回滚到老库的开关。

</details>

复习清单

| 题目 | 核心结论 | 易错点 |

|---|---|---|

| 1 现场排查 | 先 processlist 止血，再慢日志+EXPLAIN+监控分方向定位 | 一上来就改，不先止血 |

| 2 大表 DDL | 8.0 INSTANT/INPLACE，超大表用 gh-ost/pt-osc | 说直接 ALTER 大表 |

| 3 亿级表 | 分库分表+冷热分离+缓存+读写分离组合拳 | 只说分表不说归档缓存 |

| 4 不停机迁移 | 存量搬+binlog 追增量+双写灰度，随时可回滚 | 不提双写和回滚预案 |

| 5 深分页 | keyset 游标最优，延迟关联次之 | 以为改小 limit 就解决 |

| 6 死锁 | 看死锁日志，统一加锁顺序，业务重试 1213 | 只会说"加索引" |

| 7 慢 SQL | EXPLAIN→联合索引→覆盖索引→架构，量化对比 | 不验证就下结论 |

| 8 CPU 高 IO 低 | 先看连接数与 filesort/临时表，多为空转或重试风暴 | 一上来就加机器 |

下一篇预告：第 32 章 大厂MySQL面试：原理深挖