03-01-B-MySQL索引锁事务与SQL优化面试与生产事故实战️关键词SQL 优化面试题 · 索引失效 · 死锁 · 长事务 · 深分页 · 慢 SQL 诊疗 · 连接池打满 · 数据倾斜导读A 篇讲执行计划怎么读、索引怎么建、锁怎么排障本篇讲面试官怎么考、生产上怎么炸、炸了怎么救。高频题按知识依赖链一问一答连贯展开每题 30 秒电梯版 → 深挖版 → 追问链事故按场景 → 根因 → 预防 → 解决 → 一句话教训五段式复盘。 目录03-01-B-MySQL索引锁事务与SQL优化面试与生产事故实战一、高频面试题精讲连贯问答链链条①索引四连问链条②锁与事务三连问链条③慢 SQL 诊疗三连问二、生产事故案例集五段式复盘事故一隐式类型转换索引失效全表扫 300 万行事故二长事务锁住热点行全站下单超时事故三深分页导出DB CPU 打满拖垮在线服务事故四间隙锁 唯一索引并发插入死锁风暴三、事故速查表四、面试答题万能框架一、高频面试题精讲连贯问答链链条①索引四连问Q1索引为什么能加速查询底层原理** 30 秒电梯版**B 树把找数据从 O(N) 变成 O(logN)数据按索引列有序存储在叶子节点叶子节点之间用双向链表串联——等值查询沿树往下走几层就到范围查询找到起点后沿链表顺序扫。两个关键设计①非叶子节点只存索引列指针不存数据一页能装更多索引项 → 树更矮3~4 层装千万行②叶子节点存完整数据行聚簇索引或主键值二级索引→ 顺序 IO 友好。一句话总结索引 有序 矮树 顺序链表——用空间多一棵树和写代价每次写入维护树换读速度。** 深挖版**要点说明聚簇 vs 二级索引聚簇索引叶子存整行InnoDB 主键二级索引叶子存主键值 →查非索引列要回表为什么不用 B 树/HashB 树非叶子也存数据 → 树更高Hash 不支持范围/排序——B 树是范围查询的最优解页PageInnoDB 最小 IO 单位 16KB——一次磁盘 IO 读一页树矮 IO 次数少** 追问链**什么情况下索引会失效→ Q2Q2索引失效的常见场景有哪些** 30 秒电梯版**八场景见 A 篇 2.2函数/运算包裹列WHERE YEAR(create_time) 2024隐式类型转换WHERE phone 13800138000phone 是 varcharLIKE 左模糊WHERE name LIKE %张OR 连非索引列WHERE a 1 OR b 2b 无索引NOT / ! / WHERE status ! 1联合索引跳列WHERE b 2索引是 (a,b)范围后断WHERE a 1 AND b 2索引 (a,b)b 用不上统计信息不准有索引却走全表——ANALYZE TABLE刷新。一句话总结索引存的是原始值的有序排列——任何让原始值变成计算值的操作函数/转换/模糊都会让索引失效。** 深挖版**要点说明隐式转换的本质varchar 列和 int 比较 → MySQL 把列 CAST 成数字 → 等价于函数包裹列 → 失效左模糊为什么不行B 树按前缀有序——张%能定位范围%张无法利用顺序优化器故意不走索引估算全表比索引快数据量小/选择性差——不是失效是优化器的选择FORCE INDEX可强制** 追问链**联合索引怎么设计→ Q3Q3联合索引的设计原则最左前缀是什么** 30 秒电梯版**最左前缀联合索引 (a,b,c) 的 B 树按 a 排序、a 相同按 b、b 相同按 c——只能按 a→(a,b)→(a,b,c) 的顺序命中跳列b2或乱序c3都失效。设计原则等值列在前、范围列在后WHERE a 1 AND b 2→ 索引 (a,b)a 等值 b 范围排序列紧跟等值列WHERE a 1 ORDER BY b→ 索引 (a,b) 消灭 filesort高频查询列优先一个联合索引服务多个查询最左前缀的多种组合选择性高的列放前面基数大。一句话总结联合索引是排队规则——等值站前面、范围站后面、排序紧跟等值跳一个就断。** 深挖版**要点说明key_len 判断用了几列bigint8 字节、varchar(50) utf8mb450×42——EXPLAIN 的 key_len 告诉你索引用到第几列范围后断的细节WHERE a 1 AND b 2 AND c 3索引 a,b,ca 和 b 能用c 用不上——范围列之后的列全部失效冗余索引(a) 和 (a,b) 同时存在 → (a) 冗余——pt-duplicate-key-checker 扫描清理** 追问链**什么是覆盖索引→ Q4Q4什么是覆盖索引回表是什么怎么优化深分页** 30 秒电梯版**回表二级索引叶子只存主键值查其他列要拿主键回聚簇索引再查一次——两次 B 树查找。覆盖索引SELECT 的列全在索引里→ 不用回表 → Extra 显示Using index——消灭回表 减少一半 IO。深分页优化见 A 篇 2.4LIMIT 1000000, 10要取 100 万零 10 条再丢 100 万条——延迟关联子查询只在索引上翻页SELECT id ... LIMIT 1000000,10再 JOIN 回表取 10 行游标分页WHERE id 上页末值 LIMIT 10——与页码深度无关。一句话总结覆盖索引让查不回家不回表延迟关联让翻不白翻只扫索引——深分页的两个解法都围绕减少无效 IO。** 深挖版**要点说明覆盖索引的代价索引变宽 → 一页装的索引项变少 → 树变高——不是所有列都该塞进索引前缀索引不能覆盖INDEX(name(10))只存前 10 字符 → 查 name 全值要回表——前缀索引省空间但牺牲覆盖能力延迟关联的局限子查询仍要扫 offset 行只是扫索引不扫数据——真正深的分页百万级转 ES链条②锁与事务三连问Q5MySQL 死锁怎么排查和解决** 30 秒电梯版**排查命令链SHOW ENGINE INNODB STATUS\G→ 搜LATEST DETECTED DEADLOCK——死锁日志告诉你两个事务各持有什么锁、等什么锁、谁被回滚performance_schema.data_lock_waits——当前锁等待关系information_schema.INNODB_TRX——当前持锁事务找长事务。解决①统一加锁顺序所有事务按相同顺序访问表/行② 缩小事务范围减少持锁时间③ 降隔离级别 RC消除间隙锁④ 加索引减少锁范围——没索引的 UPDATE 锁全表。一句话总结死锁 加锁顺序相反——排查看死锁日志根治统一顺序。** 深挖版**要点说明InnoDB 自动检测innodb_deadlock_detectON默认——检测到死锁回滚代价小的事务undo 少的关闭死锁检测高并发场景检测本身有开销——可关闭改用锁等待超时innodb_lock_wait_timeout 调小——权衡检测快但费 CPU不检测省 CPU 但等超时间隙锁与死锁两个事务各插一行到对方锁的间隙里 → 互相等 → 死锁——RR 隔离级别下 INSERT 也可能参与死锁** 追问链**间隙锁是什么→ Q6Q6什么是间隙锁为什么会产生怎么减少锁冲突** 30 秒电梯版**间隙锁Gap Lock锁住索引记录之间的空隙——RR 隔离级别下防幻读防止其他事务在范围内插入新行。InnoDB 默认加锁单位是临键锁Next-Key Lock 行锁 间隙锁。三个坑范围查询加锁范围大——WHERE id BETWEEN 90 AND 110 FOR UPDATE锁整个范围唯一索引等值查询命中时退化为行锁不加间隙锁没命中时加间隙锁RC 隔离级别没有间隙锁——降 RC 可大幅减少锁冲突。一句话总结间隙锁是 RR 防幻读的代价——锁空隙防插入但也锁住了并发要并发就降 RC要一致就忍间隙锁。** 深挖版**要点说明幻读的例子事务 A 两次SELECT COUNT(*) WHERE age 20中间事务 B 插入一行 age25 → 两次结果不同 幻读RR 怎么防间隙锁挡住 B 的插入 → A 两次读结果一致RC 为什么没有RC 允许不可重复读 → 不需要防幻读 → 不需要间隙锁 →锁粒度小、并发高** 追问链**长事务有什么危害→ Q7Q7长事务有什么危害怎么避免** 30 秒电梯版**三大危害见 A 篇 4.1undo log 不能清理MVCC 要保留旧版本给长事务读 → undo 表空间膨胀、purge 被拖住锁一直持有行锁/间隙锁不释放 → 其他事务排队 → 锁等待超时 → 雪崩主从延迟放大长事务的 binlog 在提交时才写入 → 从库收到一个超大事务 → 重放也要那么久。避免军规事务内不做 RPC、不调外部接口、不 sleep事务粒度 一个业务操作autocommit1显式事务要短批量操作拆小批。一句话总结长事务 锁不释放 undo 不清理 主从延迟放大器——事务越短越安全RPC 永远放事务外。** 深挖版**要点说明连接池里的残留事务事务 BEGIN 后异常没 ROLLBACK连接归还池 → 下一个请求拿到一个开着事务的连接——try-finally 是军规监控长事务SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60——超 60s 告警kill 长事务KILL thread_id——但要先确认业务影响回滚也要时间链条③慢 SQL 诊疗三连问Q8一条 SQL 慢了你的排查思路是什么** 30 秒电梯版**五步诊疗法见 A 篇 5.1发现慢查询日志slow_query_logON, long_query_time1或 APM/监控 RT 告警定位mysqldumpslow -s t -t 10 slow.log按总耗时排序取 Top10体检EXPLAIN看 type/key/rows/Extra——typeALL 红灯、keyNULL 没走索引、filesort 额外排序诊断根因归类——没走索引失效八场景走了但 rows 大选择性差filesort排序列没进索引锁等待PROCESSLIST 看 Waiting治疗加索引/改 SQL/覆盖索引/延迟关联/拆事务 → 验证EXPLAIN 对比 压测 RT。一句话总结发现→定位→体检→诊断→治疗——每一步都有命令不靠猜EXPLAIN 是 X 光片先诊断后开刀。** 深挖版**要点说明慢日志 vs 监控慢日志是事后尸检有完整 SQL监控是实时心电图RT 曲线——两者配合监控发现异常慢日志定位 SQLEXPLAIN 的 rows 是估算基于统计信息——统计不准时 rows 也不准ANALYZE TABLE刷新线上 EXPLAIN 安全吗安全——不真正执行但 EXPLAIN ANALYZE 会执行MySQL 8.0.18** 追问链**索引加了还是慢怎么办→ Q9Q9加了索引 SQL 还是慢可能是什么原因** 30 秒电梯版**五种可能走了索引但回表太多索引选择性差回表 IO 大 →覆盖索引把 SELECT 列加进索引filesort 没消灭ORDER BY 列没进索引 → 联合索引包含排序列深分页LIMIT offset 大 → 延迟关联/游标分页锁等待SQL 本身不慢在等锁 →SHOW PROCESSLIST看 Waiting for lock统计信息不准优化器选错索引 →ANALYZE TABLE。一句话总结索引不是万能药——回表、排序、分页、锁、统计五个维度逐个排查。** 深挖版**要点说明索引选择性差基数/总行数 10%——如 status 只有 5 个值建索引后 rows 仍然很大优化器选错索引多个索引时优化器估算错误 →FORCE INDEX临时解决 ANALYZE TABLE根治参数嗅探不同参数值走不同执行计划——MySQL 没有 SQL Server 的参数嗅探问题但统计信息倾斜会导致类似问题** 追问链**线上怎么紧急处理慢 SQL→ Q10Q10线上慢 SQL 导致服务不可用怎么紧急处理** 30 秒电梯版**紧急四步止血SHOW PROCESSLIST找到慢查询的 thread_id →KILL id杀掉——先恢复服务再查根因限流入口限流/降级非核心功能——减少 DB 压力定位慢日志 EXPLAIN 确认根因加索引/改 SQL修复加索引注意 DDL 锁表风险用 gh-ost/pt-osc 在线加或改代码——验证后上线。一句话总结先 KILL 止血、再限流降压、后定位根治——紧急处理的目标是恢复服务不是完美修复。** 深挖版**要点说明KILL 的代价被 KILL 的事务要回滚——大事务回滚也要时间KILL 不是瞬时的DDL 锁表风险ALTER TABLE ADD INDEX 在 5.6 前锁表——线上用 gh-ost/pt-osc 在线 DDL预案慢 SQL 告警 → 自动 KILL 脚本超 30s 自动杀 限流开关——自动化比人肉快二、生产事故案例集五段式复盘事故一隐式类型转换索引失效全表扫 300 万行** 事故场景**用户查询接口 RT 从 10ms 恶化到 8s。慢日志抓到SELECT * FROM t_user WHERE phone 13800138000——phone 列有索引但 EXPLAIN 显示typeALL, keyNULL, rows3200000。phone 是 varchar(20)传参是 long 类型。** 根因分析**隐式类型转换Q2 场景②varchar 列和 int 比较 → MySQL 把列CAST 成数字CAST(phone AS SIGNED) 13800138000→ 等价于函数包裹列 →索引失效→ 全表扫 320 万行。Java 端 MyBatis 传参用了 Long 类型与 varchar 列不匹配。️ 预防方案类型对齐军规Java 字段类型与 DB 列类型严格一致——varchar 列传 Stringbigint 列传 LongCR 检查点WHERE 条件中的列类型与参数类型比对静态扫描MyBatis XML 中#{phone}的 Java 类型与 DDL 比对工具EXPLAIN 纳入 CI核心 SQL 的执行计划作为回归测试typeALL 即失败。** 事故解决**止血KILL 慢查询 入口限流根治Java 端 phone 参数改 String 类型全仓扫描 Long vs varchar 不匹配12 处验证EXPLAIN typeref, rows1RT 恢复 8ms。** 一句话教训**varchar 列传 Long 给列套了个 CAST 函数 索引当场失效——类型不对齐索引白建。事故二长事务锁住热点行全站下单超时** 事故场景**大促期间订单服务突然全站下单超时RT 30s监控显示 DB 连接池打满。排查发现一个运营后台的批量修改订单状态操作开了事务UPDATE 了 5 万行后调了一个外部审核接口RT 45s——事务持锁 45 秒不提交5 万行的行锁全部不释放后续所有涉及这些行的下单请求排队等锁连接池 30 秒内打满。** 根因分析**长事务 事务内 RPCQ7事务内调外部接口 把网络 RT变成持锁时间——45s 的 RPC 让 5 万行锁了 45s。连接池打满是果等锁的请求占住连接不释放 → 新请求拿不到连接 → 全站不可用。一个后台操作拖死了整个交易链路。️ 预防方案军规事务内禁止 RPC/外部调用——审核结果异步回调事务只做 DB 操作批量操作拆小批5 万行拆成 50 批 × 1000 行每批一个短事务长事务监控INNODB_TRX 超 10s 告警 自动 KILL超 60s连接池保护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 实例。** 根因分析**深分页 × 全列查询Q4LIMIT 500000,10000 要取 51 万行再丢 50 万行——51 万次回表SELECT * 要取整行 filesortORDER BY id 没走索引优化→ CPU 和 IO 双打满。在线查询和导出共用实例无隔离——导出抢资源在线陪葬。️ 预防方案深分页禁止走在线库导出路由到只读从库/归档库/ES——物理隔离游标分页强制导出改WHERE id 上批末值 LIMIT 10000循环——每批只取 1 万行无深分页资源隔离在线库与导出/报表库分离SQL 审核LIMIT offset 10000 的 SQL 在审核平台直接拦截。** 事故解决**止血KILL 导出查询 导出功能下线根治导出改异步任务 游标分页 数据源切只读从库在线库与报表库分离验证压测导出 100 万行在线服务 RT 无感从库承担。** 一句话教训**深分页是取 N 丢 N-10的浪费SELECT * 是取整行的奢侈——导出的需求要满足但满足它的不该是在线库游标分页 从库隔离是标准答案。事故四间隙锁 唯一索引并发插入死锁风暴** 事故场景**秒杀服务高并发下单错误日志出现大量Deadlock found——每秒数十次死锁下单成功率从 99% 跌到 60%。死锁日志显示两个 INSERT 事务互相等待对方的间隙锁。隔离级别是 RRMySQL 默认。** 根因分析**RR 间隙锁 并发插入Q6事务 AINSERT ... ON DUPLICATE KEY UPDATE唯一索引 user_idsku_id——先查唯一索引判断是否存在没命中时加间隙锁事务 B 插入同一间隙 → 等 A 的间隙锁A 又等 B 的间隙锁B 也插入了相邻记录→互相等 → 死锁。高并发下这个模式每秒触发数十次。️ 预防方案降隔离级别 RC消除间隙锁——互联网高并发写入场景的标准选择需接受不可重复读减少唯一索引冲突秒杀用 Redis 预扣库存DB 层不做唯一键竞争批量合并写入单条 INSERT 改批量 INSERT减少事务数和锁竞争窗口死锁监控死锁次数 阈值告警 死锁日志自动采集分析。** 事故解决**止血隔离级别改 RCSET GLOBAL transaction_isolation READ-COMMITTED——死锁立即消失根治秒杀库存扣减改 Redis Lua 原子操作DB 只做异步落库RC 写入应用配置固化验证压测 1 万 QPS 下单零死锁成功率 99.9%。** 一句话教训**RR 的间隙锁防的是幻读挡的是并发——高并发写入场景RC 才是正解唯一索引 ON DUPLICATE 在 RR 下是死锁温床。三、事故速查表现象可能根因快速定位根治方案有索引却全表扫隐式类型转换/函数包裹/左模糊EXPLAIN keyNULL 对照失效八场景类型对齐改 SQL函数移到参数侧走了索引但 rows 巨大选择性差/统计不准key_len 判断用了几列ANALYZE TABLE联合索引提高选择性刷新统计Using filesortORDER BY 没走索引Extra 字段联合索引包含排序列等值排序深分页 RT 随页码恶化LIMIT offset 大慢日志看 LIMIT 值延迟关联/游标分页/转 ES大量 Deadlock found加锁顺序相反/间隙锁冲突SHOW ENGINE INNODB STATUS 死锁日志统一加锁顺序降 RC减少唯一键竞争Lock wait timeout长事务持锁INNODB_TRX 找长事务事务内禁 RPC拆小批自动 KILL连接池打满慢 SQL/锁等待占连接SHOW PROCESSLIST 看 WaitingKILL 慢查询限流连接池 leak 检测主从延迟突然飙升大事务/长事务 binlog 集中写入binlog 大小 事务时长拆小批事务缩短四、面试答题万能框架被问SQL 优化 / 索引 / 锁① 先给方法论五步诊疗法1. 发现2. 定位3. 体检4. 诊断5. 治疗EXPLAIN 是 X 光片先诊断后开刀② 按追问深挖1. 索引线B 树原理失效八场景最左前缀覆盖索引2. 锁线死锁排查链间隙锁长事务三危害3. 分页线深分页延迟关联游标分页③ 落到生产视角1. 类型对齐军规2. 事务内禁 RPC3. RC 降锁冲突4. 深分页转 ES5. 在线 DDL 用 gh-ost④ 用事故收尾1. 隐式转换全表扫2. 长事务锁全站3. 死锁风暴有画面感的案例胜过背书加分技巧谈索引失效主动列八场景不是三五个——完整度就是区分度谈死锁主动讲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》如果这篇文章对你有帮助欢迎点赞、收藏、关注