企业官网建设流程全解析

03-01-B-MySQL索引锁事务与SQL优化面试与生产事故实战

️关键词：SQL 优化面试题 · 索引失效 · 死锁 · 长事务 · 深分页 · 慢 SQL 诊疗 · 连接池打满 · 数据倾斜

📌导读：A 篇讲"执行计划怎么读、索引怎么建、锁怎么排障"，本篇讲"面试官怎么考、生产上怎么炸、炸了怎么救"。高频题按知识依赖链一问一答连贯展开（每题： 30 秒电梯版 → 深挖版 → 追问链），事故按场景 → 根因 → 预防 → 解决 → 一句话教训五段式复盘。

一、高频面试题精讲（连贯问答链）

链条①：索引四连问

Q1：索引为什么能加速查询？底层原理？

** 30 秒电梯版**

B+ 树把"找数据"从 O(N) 变成 O(logN)：数据按索引列有序存储在叶子节点，叶子节点之间用双向链表串联——等值查询沿树往下走（几层就到），范围查询找到起点后沿链表顺序扫。

两个关键设计：①非叶子节点只存索引列+指针（不存数据），一页能装更多索引项 → 树更矮（3~4 层装千万行）；②叶子节点存完整数据行（聚簇索引）或主键值（二级索引）→ 顺序 IO 友好。

一句话总结：索引 = 有序 + 矮树 + 顺序链表——用空间（多一棵树）和写代价（每次写入维护树）换读速度。

** 深挖版**

** 追问链**：什么情况下索引会失效？→ Q2

Q2：索引失效的常见场景有哪些？

** 30 秒电梯版**

八场景（见 A 篇 2.2）：

1. 函数/运算包裹列：WHERE YEAR(create_time) = 2024；
2. 隐式类型转换：WHERE phone = 13800138000（phone 是 varchar）；
3. LIKE 左模糊：WHERE name LIKE '%张'；
4. OR 连非索引列：WHERE a = 1 OR b = 2（b 无索引）；
5. NOT / != / <>：WHERE status != 1；
6. 联合索引跳列：WHERE b = 2（索引是 (a,b)）；
7. 范围后断：WHERE a > 1 AND b = 2（索引 (a,b)，b 用不上）；
8. 统计信息不准：有索引却走全表——ANALYZE TABLE刷新。

一句话总结：索引存的是原始值的有序排列——任何让"原始值"变成"计算值"的操作（函数/转换/模糊）都会让索引失效。

** 深挖版**

** 追问链**：联合索引怎么设计？→ Q3

Q3：联合索引的设计原则？最左前缀是什么？

** 30 秒电梯版**

最左前缀：联合索引 (a,b,c) 的 B+ 树按 a 排序、a 相同按 b、b 相同按 c——只能按 a→(a,b)→(a,b,c) 的顺序命中，跳列（b=2）或乱序（c=3）都失效。

设计原则：

1. 等值列在前、范围列在后：WHERE a = 1 AND b > 2→ 索引 (a,b)，a 等值 b 范围；
2. 排序列紧跟等值列：WHERE a = 1 ORDER BY b→ 索引 (a,b) 消灭 filesort；
3. 高频查询列优先：一个联合索引服务多个查询（最左前缀的多种组合）；
4. 选择性高的列放前面（基数大）。

一句话总结：联合索引是"排队规则"——等值站前面、范围站后面、排序紧跟等值，跳一个就断。

** 深挖版**

** 追问链**：什么是覆盖索引？→ Q4

Q4：什么是覆盖索引？回表是什么？怎么优化深分页？

** 30 秒电梯版**

回表：二级索引叶子只存主键值，查其他列要拿主键回聚簇索引再查一次——两次 B+ 树查找。

覆盖索引：SELECT 的列全在索引里→ 不用回表 → Extra 显示Using index——消灭回表 = 减少一半 IO。

深分页优化（见 A 篇 2.4）：LIMIT 1000000, 10要取 100 万零 10 条再丢 100 万条——

• 延迟关联：子查询只在索引上翻页（SELECT id ... LIMIT 1000000,10），再 JOIN 回表取 10 行；
• 游标分页：WHERE id > 上页末值 LIMIT 10——与页码深度无关。

一句话总结：覆盖索引让"查"不回家（不回表），延迟关联让"翻"不白翻（只扫索引）——深分页的两个解法都围绕"减少无效 IO"。

** 深挖版**

链条②：锁与事务三连问

Q5：MySQL 死锁怎么排查和解决？

** 30 秒电梯版**

排查命令链：

1. SHOW ENGINE INNODB STATUS\G→ 搜LATEST DETECTED DEADLOCK——死锁日志告诉你两个事务各持有什么锁、等什么锁、谁被回滚；
2. performance_schema.data_lock_waits——当前锁等待关系；
3. information_schema.INNODB_TRX——当前持锁事务（找长事务）。

解决：①统一加锁顺序（所有事务按相同顺序访问表/行）；② 缩小事务范围（减少持锁时间）；③ 降隔离级别 RC（消除间隙锁）；④ 加索引（减少锁范围——没索引的 UPDATE 锁全表）。

一句话总结：死锁 = 加锁顺序相反——排查看死锁日志，根治统一顺序。

** 深挖版**

** 追问链**：间隙锁是什么？→ Q6

Q6：什么是间隙锁？为什么会产生？怎么减少锁冲突？

** 30 秒电梯版**

间隙锁（Gap Lock）：锁住索引记录之间的"空隙"——RR 隔离级别下防幻读（防止其他事务在范围内插入新行）。InnoDB 默认加锁单位是临键锁（Next-Key Lock）= 行锁 + 间隙锁。

三个坑

1. 范围查询加锁范围大——WHERE id BETWEEN 90 AND 110 FOR UPDATE锁整个范围；
2. 唯一索引等值查询命中时退化为行锁（不加间隙锁），没命中时加间隙锁；
3. RC 隔离级别没有间隙锁——降 RC 可大幅减少锁冲突。

一句话总结：间隙锁是 RR 防幻读的代价——锁"空隙"防插入，但也锁住了并发；要并发就降 RC，要一致就忍间隙锁。

** 深挖版**

** 追问链**：长事务有什么危害？→ Q7

Q7：长事务有什么危害？怎么避免？

** 30 秒电梯版**

三大危害（见 A 篇 4.1）：

1. undo log 不能清理：MVCC 要保留旧版本给长事务读 → undo 表空间膨胀、purge 被拖住；
2. 锁一直持有：行锁/间隙锁不释放 → 其他事务排队 → 锁等待超时 → 雪崩；
3. 主从延迟放大：长事务的 binlog 在提交时才写入 → 从库收到一个超大事务 → 重放也要那么久。

避免军规：事务内不做 RPC、不调外部接口、不 sleep；事务粒度 = 一个业务操作；autocommit=1，显式事务要短；批量操作拆小批。

一句话总结：长事务 = 锁不释放 + undo 不清理 + 主从延迟放大器——事务越短越安全，RPC 永远放事务外。

** 深挖版**

链条③：慢 SQL 诊疗三连问

Q8：一条 SQL 慢了，你的排查思路是什么？

** 30 秒电梯版**

五步诊疗法（见 A 篇 5.1）：

1. 发现：慢查询日志（slow_query_log=ON, long_query_time=1）或 APM/监控 RT 告警；
2. 定位：mysqldumpslow -s t -t 10 slow.log按总耗时排序取 Top10；
3. 体检：EXPLAIN看 type/key/rows/Extra——type=ALL 红灯、key=NULL 没走索引、filesort 额外排序；
4. 诊断：根因归类——没走索引（失效八场景）？走了但 rows 大（选择性差）？filesort（排序列没进索引）？锁等待（PROCESSLIST 看 Waiting）？
5. 治疗：加索引/改 SQL/覆盖索引/延迟关联/拆事务 → 验证（EXPLAIN 对比 + 压测 RT）。

一句话总结：发现→定位→体检→诊断→治疗——每一步都有命令，不靠猜；EXPLAIN 是 X 光片，先诊断后开刀。

** 深挖版**

** 追问链**：索引加了还是慢怎么办？→ Q9

Q9：加了索引 SQL 还是慢，可能是什么原因？

** 30 秒电梯版**

五种可能：

1. 走了索引但回表太多：索引选择性差，回表 IO 大 →覆盖索引（把 SELECT 列加进索引）；
2. filesort 没消灭：ORDER BY 列没进索引 → 联合索引包含排序列；
3. 深分页：LIMIT offset 大 → 延迟关联/游标分页；
4. 锁等待：SQL 本身不慢，在等锁 →SHOW PROCESSLIST看 Waiting for lock；
5. 统计信息不准：优化器选错索引 →ANALYZE TABLE。

一句话总结：索引不是万能药——回表、排序、分页、锁、统计，五个维度逐个排查。

** 深挖版**

** 追问链**：线上怎么紧急处理慢 SQL？→ Q10

Q10：线上慢 SQL 导致服务不可用，怎么紧急处理？

** 30 秒电梯版**

紧急四步：

1. 止血：SHOW PROCESSLIST找到慢查询的 thread_id →KILL <id>杀掉——先恢复服务，再查根因；
2. 限流：入口限流/降级非核心功能——减少 DB 压力；
3. 定位：慢日志 + EXPLAIN 确认根因（加索引/改 SQL）；
4. 修复：加索引（注意 DDL 锁表风险，用 gh-ost/pt-osc 在线加）或改代码——验证后上线。

一句话总结：先 KILL 止血、再限流降压、后定位根治——紧急处理的目标是恢复服务，不是完美修复。

** 深挖版**

二、生产事故案例集（五段式复盘）

事故一：隐式类型转换，索引失效全表扫 300 万行

** 事故场景**

用户查询接口 RT 从 10ms 恶化到 8s。慢日志抓到：SELECT * FROM t_user WHERE phone = 13800138000——phone 列有索引，但 EXPLAIN 显示type=ALL, key=NULL, rows=3200000。phone 是 varchar(20)，传参是 long 类型。

** 根因分析**

隐式类型转换（Q2 场景②）：varchar 列和 int 比较 → MySQL 把列CAST 成数字（CAST(phone AS SIGNED) = 13800138000）→ 等价于函数包裹列 →索引失效→ 全表扫 320 万行。Java 端 MyBatis 传参用了 Long 类型，与 varchar 列不匹配。

️ 预防方案

1. 类型对齐军规：Java 字段类型与 DB 列类型严格一致——varchar 列传 String，bigint 列传 Long；
2. CR 检查点：WHERE 条件中的列类型与参数类型比对；
3. 静态扫描：MyBatis XML 中#{phone}的 Java 类型与 DDL 比对工具；
4. EXPLAIN 纳入 CI：核心 SQL 的执行计划作为回归测试（type=ALL 即失败）。

** 事故解决**

• 止血：KILL 慢查询 + 入口限流；
• 根治：Java 端 phone 参数改 String 类型；全仓扫描 Long vs varchar 不匹配（12 处）；
• 验证：EXPLAIN type=ref, rows=1，RT 恢复 8ms。

** 一句话教训**

varchar 列传 Long = 给列套了个 CAST 函数 = 索引当场失效——类型不对齐，索引白建。

事故二：长事务锁住热点行，全站下单超时

** 事故场景**

大促期间，订单服务突然全站下单超时（RT > 30s），监控显示 DB 连接池打满。排查发现：一个运营后台的"批量修改订单状态"操作开了事务，UPDATE 了 5 万行后调了一个外部审核接口（RT 45s）——事务持锁 45 秒不提交，5 万行的行锁全部不释放，后续所有涉及这些行的下单请求排队等锁，连接池 30 秒内打满。

** 根因分析**

长事务 + 事务内 RPC（Q7）：事务内调外部接口 = 把"网络 RT"变成"持锁时间"——45s 的 RPC 让 5 万行锁了 45s。连接池打满是果：等锁的请求占住连接不释放 → 新请求拿不到连接 → 全站不可用。一个后台操作拖死了整个交易链路。

️ 预防方案

1. 军规：事务内禁止 RPC/外部调用——审核结果异步回调，事务只做 DB 操作；
2. 批量操作拆小批：5 万行拆成 50 批 × 1000 行，每批一个短事务；
3. 长事务监控：INNODB_TRX 超 10s 告警 + 自动 KILL（超 60s）；
4. 连接池保护：HikariCP 的 leakDetectionThreshold（连接借出超 30s 告警）+ maximumPoolSize 与 DB max_connections 对齐。

** 事故解决**

• 止血：KILL 长事务（回滚 5 万行 undo 花了 8s）+ 连接池重启；
• 根治：批量操作改异步任务（MQ + 小批事务）；审核接口改回调模式；长事务自动 KILL 上线；
• 验证：压测批量 5 万行，单事务持锁 <200ms，下单 RT 无感。

** 一句话教训**

事务内调 RPC = 把网络延迟变成持锁时间——45 秒的审核接口，锁住了 5 万行和整个交易链路；RPC 永远放事务外。

事故三：深分页导出，DB CPU 打满拖垮在线服务

** 事故场景**

运营后台"订单导出"功能上线：SELECT * FROM t_order WHERE create_time > '2024-01-01' ORDER BY id LIMIT 500000, 10000。运营一点导出，DB CPU 瞬间 100%，在线订单查询 RT 从 5ms 恶化到 2s——导出和在线查询共用一个 DB 实例。

** 根因分析**

深分页 × 全列查询（Q4）：LIMIT 500000,10000 要取 51 万行再丢 50 万行——51 万次回表（SELECT * 要取整行）+ filesort（ORDER BY id 没走索引优化）→ CPU 和 IO 双打满。在线查询和导出共用实例无隔离——导出抢资源，在线陪葬。

️ 预防方案

1. 深分页禁止走在线库：导出路由到只读从库/归档库/ES——物理隔离；
2. 游标分页强制：导出改WHERE id > 上批末值 LIMIT 10000循环——每批只取 1 万行，无深分页；
3. 资源隔离：在线库与导出/报表库分离；
4. SQL 审核：LIMIT offset > 10000 的 SQL 在审核平台直接拦截。

** 事故解决**

• 止血：KILL 导出查询 + 导出功能下线；
• 根治：导出改异步任务 + 游标分页 + 数据源切只读从库；在线库与报表库分离；
• 验证：压测导出 100 万行，在线服务 RT 无感（从库承担）。

** 一句话教训**

深分页是"取 N 丢 N-10"的浪费，SELECT * 是"取整行"的奢侈——导出的需求要满足，但满足它的不该是在线库；游标分页 + 从库隔离是标准答案。

事故四：间隙锁 + 唯一索引，并发插入死锁风暴

** 事故场景**

秒杀服务高并发下单，错误日志出现大量Deadlock found——每秒数十次死锁，下单成功率从 99% 跌到 60%。死锁日志显示：两个 INSERT 事务互相等待对方的间隙锁。隔离级别是 RR（MySQL 默认）。

** 根因分析**

RR 间隙锁 + 并发插入（Q6）：事务 AINSERT ... ON DUPLICATE KEY UPDATE（唯一索引 user_id+sku_id）——先查唯一索引判断是否存在，没命中时加间隙锁；事务 B 插入同一间隙 → 等 A 的间隙锁；A 又等 B 的间隙锁（B 也插入了相邻记录）→互相等 → 死锁。高并发下这个模式每秒触发数十次。

️ 预防方案

1. 降隔离级别 RC：消除间隙锁——互联网高并发写入场景的标准选择（需接受不可重复读）；
2. 减少唯一索引冲突：秒杀用 Redis 预扣库存，DB 层不做唯一键竞争；
3. 批量合并写入：单条 INSERT 改批量 INSERT（减少事务数和锁竞争窗口）；
4. 死锁监控：死锁次数 > 阈值告警 + 死锁日志自动采集分析。

** 事故解决**

• 止血：隔离级别改 RC（SET GLOBAL transaction_isolation = 'READ-COMMITTED'）——死锁立即消失；
• 根治：秒杀库存扣减改 Redis Lua 原子操作，DB 只做异步落库；RC 写入应用配置固化；
• 验证：压测 1 万 QPS 下单，零死锁，成功率 99.9%。

** 一句话教训**

RR 的间隙锁防的是幻读，挡的是并发——高并发写入场景，RC 才是正解；唯一索引 + ON DUPLICATE 在 RR 下是死锁温床。

三、事故速查表

四、面试答题万能框架

加分技巧：

• 谈索引失效主动列八场景（不是三五个）——完整度就是区分度；
• 谈死锁主动讲"SHOW ENGINE INNODB STATUS → data_lock_waits → INNODB_TRX 三步链"——命令级熟练度；
• 谈间隙锁主动说"RR 特有、RC 没有、唯一索引命中退化为行锁"——机制级理解；
• 谈长事务主动带"undo 膨胀 + 锁不释放 + 主从延迟放大"三危害——不是只说"慢"；
• 被问"遇到过什么 SQL 问题"，用事故二（长事务锁全站）——"一个后台操作拖死交易链路"的反差最有冲击力。

📌结语：SQL 优化面试题的尽头是"扫描量意识"——每个优化手段（索引/覆盖/延迟关联/拆事务）的本质都是减少扫描量；生产事故的尽头是"纪律意识"——类型对齐、事务内禁 RPC、深分页转 ES、RC 降锁冲突，每条纪律都是一次事故的墓碑。

📌配套阅读：

上一篇：《02-06-X-锁机制全景详解.md》

A 篇：《03-01-A-MySQL索引锁事务与SQL优化实战详解.md》

下一篇：《03-02-A-MySQL主从复制与高可用详解.md》

如果这篇文章对你有帮助，欢迎点赞、收藏、关注！