MySQL 索引与事务
10 题 · 平均 2.7/5 · low 2.75 · medium 2.6 · high 3 · 第 1 轮
**核心考点定位**：考察对数据库事务四大特性的定义理解、实现机制认知，以及区分 ACID 与 CAP 中可用性(Availability)的概念边界。
**知识点拆解**：
1. **ACID 四特性与实现机制**：原子性由 undo log 保障（可回滚）；一致性是最终目标（数据合法状态转换）；隔离性由锁 + MVCC 实现（并发事务互不干扰）；持久性由 redo log 保障（宕机可恢复）。一致性是目标，另外三个是手段。
2. **ACID 与 CAP 的区别**：ACID 的 I 是 Isolation（隔离性），CAP 的 A 是 Availability（可用性）。两者字母重叠但含义完全不同，面试中混淆即失分。
3. **MySQL InnoDB 的实现路径**：undo log（原子性）→ 业务约束 + 其他三特性（一致性）→ 锁 + MVCC + 隔离级别（隔离性）→ redo log 两阶段提交（持久性）。四者协同保证事务正确性。
**考察方向**：
- **深度**：undo log 和 redo log 如何分别支撑原子性和持久性
- **广度**：CAP 理论、BASE 理论、隔离级别
- **应用**：分布式事务中 ACID 如何退化（Seata AT / TCC / 本地消息表）
**延伸递进面试题**：
1. **undo log 和 redo log 有什么区别？各自解决什么问题？**
- 解答要点：undo log 记录修改前的旧值，用于回滚（原子性）和 MVCC（快照读）；redo log 记录修改后的新值，用于崩溃恢复（持久性）。undo 在表空间，redo 在 ib_logfile。
2. **两阶段提交(2PC)在 InnoDB 中怎么用的？**
- 解答要点：事务提交时 redo log 先写 prepare 状态，再写 binlog，最后 redo log 写 commit。崩溃恢复时根据 binlog 是否完整决定 redo log 的 prepare 记录是 commit 还是 rollback。
3. **分布式事务如何保证 ACID？**
- 解答要点：分布式场景下 ACID 无法强保证，退化为 BASE（基本可用、软状态、最终一致）。常见方案：2PC/XA（强一致但阻塞）、TCC（最终一致但侵入大）、本地消息表（最终一致但需对账）。
面试官您好，事务的 ACID 特性指的是原子性、一致性、隔离性和持久性，核心目标是保证数据的一致性。
原子性是指事务中的操作要么全部成功，要么全部回滚，MySQL 是通过 undo log 来实现的——每次修改前把旧值记录下来，如果事务失败就沿 undo log 回滚。
一致性是最终目标，事务执行前后数据都要处于合法状态，比如转账场景里 A 账户扣了钱，B 账户必须加到对应的钱。这个一致性其实是由其他三个特性来保障的。
隔离性是指多个并发事务之间互不干扰，MySQL 通过锁机制和 MVCC 来实现，不同的隔离级别对应不同的可见性规则。MySQL 默认使用可重复读级别。
持久性是指事务一旦提交，即使系统崩溃数据也不会丢失，这是通过 redo log 来保证的，崩溃恢复时根据 redo log 重做未完成的操作。
所以总结来说，一致性是目标，原子性、隔离性、持久性是手段。另外需要注意，ACID 的 I 是隔离性，和 CAP 里的可用性(A)不是一个概念，这个很容易混淆。
https://chat.deepseek.com/share/bgzvkk02xzsh7bjd82
**核心考点定位**：考察对联合索引在 B+ 树中的物理排列方式的理解，以及能否将底层数据结构映射到 SQL 查询行为。
**知识点拆解**：
1. **B+ 树中的联合索引排列规则**：联合索引 (a,b,c) 在 B+ 树中先按 a 排序，a 相同按 b 排序，b 相同按 c 排序，与字典序排序一致。WHERE 条件必须从最左侧开始，跳过中间字段则后续字段失去有序性，无法利用索引范围扫描。
2. **范围查询截断效应**：WHERE a=1 AND b>2 AND c=3 中，b 用范围条件后，不同 b 值下的 c 排序各自独立，c 无法利用索引，只能回表后过滤。
3. **MySQL 8.0 索引跳跃扫描**：当最左字段区分度极低时，MySQL 8.0 可逐个遍历最左字段值，在每个子树内利用后续字段。不是万能解，需满足低基数、无索引下推等条件。
**考察方向**：
- **深度**：B+ 树键值排列规则决定索引利用方式
- **广度**：索引跳跃扫描、索引下推(ICP)、covering index
- **应用**：联合索引字段顺序设计原则
**延伸递进面试题**：
1. **联合索引 (a,b,c)，WHERE b=2 AND c=3 能走索引吗？**
- 解答要点：传统规则下不能（跳过 a）。MySQL 8.0 特定条件下可用索引跳跃扫描。
2. **联合索引 (a,b,c)，WHERE a=1 AND c=3 能走索引吗？**
- 解答要点：a=1 走索引，c 无法利用索引排序（b 被跳过），c 回表后过滤。
3. **联合索引字段顺序怎么设计？**
- 解答要点：高频单独查询的字段放左边；区分度高的放左边；左侧字段若为范围查询则后续字段失效。
面试官您好，最左前缀匹配的本质是 B+ 树中联合索引的排序规则。
联合索引比如 (a,b,c)，在 B+ 树里先用 a 排序，a 相同再用 b 排序，b 也相同再用 c 排序，和字典排序一个道理。所以查询条件必须从最左边开始匹配。WHERE a=1 能走索引，WHERE a=1 AND b=2 也能走，但跳过 a 直接查 WHERE b=2 就不行了，因为所有 b 值分散在不同 a 值下面，无法利用有序性。
还有一个重要场景，WHERE a=1 AND b>2 AND c=3。b 用了范围查询，不同 b 值下面的 c 排序是独立的，c 无法继续利用索引做范围扫描，只能回表后过滤。
MySQL 8.0 有个例外叫索引跳跃扫描，最左字段区分度很低时，MySQL 可以逐个遍历最左字段的值，在每个子树内利用后续字段，但这不是通用方案。
总结来说，最左前缀匹配不是 MySQL 人为规定的规则，而是 B+ 树的排序结构决定的物理事实。
https://chat.deepseek.com/share/f59k51fog1moopg9u0
**核心考点定位**：理解 InnoDB 二级索引与聚簇索引的关系，掌握减少回表的工程手法。
**知识点拆解**：
1. **什么是回表**：InnoDB 采用聚簇索引，二级索引叶子节点只存「索引列 + 主键」。查询用二级索引时，先从二级索引找到主键 ID，再拿主键回聚簇索引 B+ 树查一次完整行，这个"回头找主表"的动作叫回表。
2. **回表为什么慢**：每次回表都是一次独立的 B+ 树查找，如果行数据不在 Buffer Pool 里就是随机磁盘 IO。100 万行匹配只 LIMIT 10，MySQL 仍可能回表 100 万次（在排序/过滤前）。
3. **减少回表三板斧**：
- **覆盖索引**：SELECT 的列全放进索引，叶子节点直接返回，`EXPLAIN` 的 `Extra` 显示 `Using index`。
- **延迟关联**：先用索引表子查询拿到主键 ID 并 LIMIT，再 JOIN 主表取完整行，把回表次数压到 LIMIT 数量。
- **索引下推 ICP**：MySQL 5.6+ 特性，在索引扫描阶段就用非索引列条件过滤，减少需要回表的行数，`EXPLAIN` 显示 `Using index condition`。
4. **反模式**：把大字段（TEXT/BLOB、长 VARCHAR）放进索引；SELECT * 让覆盖索引失效；给低基数字段单独建索引导致扫描后大量回表。
**考察方向**：
- **深度**：是否理解 B+ 树结构决定了回表必须二次查找
- **广度**：能否关联到覆盖索引、延迟关联、ICP、聚簇索引选择
- **应用**：能否通过 `EXPLAIN` 识别回表并给出具体的 SQL 改写方案
**延伸递进面试题**：
1. **`Extra` 列里的 `Using index` 和 `Using index condition` 有什么区别？**
- 解答要点：`Using index` 是覆盖索引，完全在二级索引内解决；`Using index condition` 是索引下推，还在索引阶段做过滤但需要回表取其他列。
2. **一条 `SELECT * FROM t WHERE a=? ORDER BY b LIMIT 10` 有索引 `(a,b)`，还会有回表吗？如何彻底消除？**
- 解答要点：有。`SELECT *` 导致覆盖失败，回表 10 次。若要彻底消除，要么建覆盖索引包含所有 SELECT 列，要么用延迟关联。
3. **为什么 InnoDB 推荐用自增主键而不是 UUID 做聚簇索引？**
- 解答要点：自增主键保证插入顺序 = 物理顺序，避免页分裂和随机 IO；UUID 打散主键，二级索引叶子存的主键无序，回表和插入都会退化。
面试官您好，回表是 InnoDB 的一个特有现象。InnoDB 把数据存在聚簇索引里，而二级索引的叶子节点只存"索引列 + 主键 ID"，不存完整行。所以当我们用二级索引查数据时，先在二级索引的 B+ 树上定位到主键 ID，再拿这个 ID 回聚簇索引 B+ 树查一次完整行，这次回头查主表的过程就叫回表。回表贵，是因为每一次都是一次独立的 B+ 树查找，如果行数据不在 Buffer Pool 里，就是随机磁盘 IO；而且回表次数取决于索引扫描到的行数，不是最终 LIMIT 数量，一条 `LIMIT 10` 的查询可能回表几百万次。
减少回表主要有三个手段。第一是覆盖索引，把 SELECT 需要的列都放进一个索引里，这样在二级索引叶子节点就能拿到全部数据，EXPLAIN 里会显示 `Using index`，完全不需要回表。第二是延迟关联，写法是先在一个子查询里只查主键 ID，配合 WHERE 和 ORDER BY 和 LIMIT，然后再拿这批主键 JOIN 回主表拿完整行，这样回表次数就被压到 LIMIT 那个数。第三是索引下推 ICP，这是 MySQL 5.6 加的特性，对于范围查询，把非索引列的过滤条件推到索引扫描阶段做，减少需要回表的行数，EXPLAIN 里能看到 `Using index condition`。
工程上还要注意几个坑，一是不要在索引里放大字段和长字符串，会让索引膨胀；二是尽量避免 SELECT *，它会让覆盖索引失效；三是主键最好用自增整数，UUID 会让二级索引里存的主键无序，回表时随机 IO 更多。
https://chat.deepseek.com/share/5e7y27yvn1p1s5d5s1
**核心考点定位**：理解联合索引的排序规则（B+ 树复合键排序）与 MySQL 8.0 的 Skip Scan 优化边界。
**知识点拆解**：
1. **联合索引的排序规则**：`(A,B,C)` 复合键的 B+ 树按字典序排序——先按 A 排序，A 相同时再按 B 排序，B 相同时再按 C 排序。所以查询能否命中索引取决于"是否提供了前缀"。
2. **AB 查询**：`WHERE a=? AND b=?` 完整命中 A、B 两段定位。若没有 C 条件，C 不能作为定位，只能作为过滤（回表后判断）。
3. **AC 查询**：`WHERE a=? AND c=?` 只有 A 段能定位，C 段中间隔了 B，无法利用排序，C 走过滤，`Extra=Using where`。
4. **BC 查询**：`WHERE b=? AND c=?` 默认走不到索引——因为 A 未知，B 的排序在不同 A 之间不连续。**MySQL 8.0+ 例外**：若 A 基数很低（如 gender=0/1），优化器可能做 **Skip Scan**，把查询拆成 `A=0 AND B=? AND C=?` 和 `A=1 AND B=? AND C=?` 两段在索引内部完成，`Extra=Using index for skip scan`。
5. **索引下推 ICP（Index Condition Pushdown）**：这是 MySQL 5.6 引入的优化，把非索引列的 WHERE 条件前推到索引扫描阶段做过滤，减少回表次数。`Extra=Using index condition`。**它跟 Skip Scan 是两回事**——ICP 减少回表次数，Skip Scan 让原本走不到的索引能走到。
6. **EXPLAIN 识别技巧**：看 `key` 是否是目标索引、`rows` 是否显著下降、`Extra` 是否出现 `Using index`（覆盖）、`Using index condition`（ICP）、`Using index for skip scan`（Skip Scan）。
**考察方向**：
- **深度**：能否解释为什么联合索引必须按前缀命中（B+ 树排序本质）
- **广度**：能否区分 ICP 与 Skip Scan 这两个容易混淆的优化
- **应用**：能否通过 `EXPLAIN` 判断实际执行计划并给出改进方案
**延伸递进面试题**：
1. **`WHERE a IN (1,2,3) AND b=?` 会走联合索引吗？**
- 解答要点：会。A 是有限枚举，等价于把 A 展开成几个等值条件，B 在每个 A 值内仍然有序，优化器可以正常利用。
2. **`WHERE a>? AND b=?` 会怎样？为什么范围查询后面的列用不上？**
- 解答要点：只有 A 走索引（范围），B 用不上。原因是 A 值有多个匹配范围，每个范围内的 B 才有序，跨范围 B 就不连续了。
3. **`FORCE INDEX` 能强行让 BC 走 (A,B,C) 索引吗？会有什么问题？**
- 解答要点：能强行走，但可能变成全索引扫描，比全表扫描还慢（索引比表小不了多少，还多一次回表）。所以 FORCE INDEX 要用 `EXPLAIN` 验证。
面试官您好，这个问题关键要看联合索引的排序规则。`(A,B,C)` 复合索引在 B+ 树上按字典序排——先按 A 排，A 相同再按 B 排，B 相同再按 C 排。所以查询要能命中索引，就必须能提供前缀。
**AB 查询** `WHERE a=? AND b=?`：A 定位到一片区间，这片区间内 B 是有序的，所以 B 也能定位，两段都能用；如果 WHERE 没有 C，C 只能作为过滤条件在回表后判断。
**AC 查询** `WHERE a=? AND c=?`：A 定位到区间后，中间隔了一个 B 未知，C 在这个区间里不再有序，所以 C 用不上索引定位，只能走过滤。EXPLAIN 会看到 `Extra=Using where`，key_len 只覆盖 A 段。
**BC 查询** `WHERE b=? AND c=?`：默认走不到 (A,B,C) 索引。因为 A 未知，B 的有序性被 A 的分隔打散。但这里有个例外，MySQL 8.0 有个 **Skip Scan（跳跃扫描）** 优化：如果 A 的基数很低，比如是性别、状态这种只有几个值的字段，优化器会把这个查询在索引内部拆成 `A=0 AND B=? AND C=?`、`A=1 AND B=? AND C=?` 等多段分别扫描再合并。EXPLAIN 的 Extra 会显示 `Using index for skip scan`。
这里容易混淆的一个概念是 **ICP（索引下推）**。ICP 是 MySQL 5.6 加的优化，把 WHERE 里非索引列的条件前推到索引扫描阶段做过滤，减少回表次数，Extra 显示 `Using index condition`。ICP 不会让走不到的索引变走得到，它只是减少回表。Skip Scan 才是让原本用不到前缀列的查询也能用上索引。
所以工程实践上，如果有明确的 BC 查询模式，还是单独建 `(B,C)` 索引更稳妥，不要指望 Skip Scan 兜底——它的触发条件比较苛刻，A 基数低是硬要求。另外也不要用 `FORCE INDEX` 硬推，因为可能退化成全索引扫描，比全表扫描还慢。
https://chat.deepseek.com/share/6snsbux2u1oe10v9gu
**核心考点定位**：掌握一套从"发现未走索引"到"定位失效原因"再到"给出修复方案"的完整排查链。
**知识点拆解**：
1. **确认索引确实存在**：`SHOW INDEX FROM t` 看索引列、`Cardinality` 基数是否正常、`Non_unique` 是否唯一。索引不存在或列写错是最基础的可能。
2. **确认 SQL 真的没走索引**：
- `EXPLAIN` 看 `key` 列是否为 `NULL`、`rows` 是否等于全表、`Extra` 是否有 `Using filesort` / `Using temporary`
- `EXPLAIN FORMAT=JSON` 看 `missing_covering_index` 建议、`cost_info.query_cost`
- `sys.schema_index_statistics`：`rows_selected / rows_examined` 长期低于 1% 说明索引扫描了大量无用行
- `performance_schema.table_io_waits_summary_by_index_usage`：索引长期 `count_star=0` 说明这个索引根本没人用
- MySQL 8.0：`EXPLAIN ANALYZE` 看真实执行代价
3. **常见失效场景清单**：
- **隐式类型转换**：字符串列 vs 数字条件（`WHERE varchar_col = 123`），MySQL 会强制把 varchar 转 number，索引失效
- **字段上做函数/运算**：`WHERE YEAR(create_time)=2024`、`WHERE UPPER(name)='A'`、`WHERE age+1=10`
- **字符集/排序规则不一致**：JOIN 时两表字段 charset 或 collation 不同
- **左模糊**：`WHERE name LIKE '%ab'`，前缀不确定无法用 B+ 树有序性
- **OR 拼接非索引列**：`WHERE a=1 OR non_index=2` 退化为全表
- **`NOT IN`、`!=`、`< >`、`IS NOT NULL`**：范围或反向判断，优化器倾向全表
- **`ORDER BY` / `GROUP BY` 非索引列**：引发 `filesort`，若排序后需要大量回表，优化器可能放弃索引
- **多值匹配**：`WHERE FIND_IN_SET('x', col)`、`col LIKE '%a%'` 全失效
- **`IN` 元素过多**：几百个值优化器倾向全表（受 `eq_range_index_dive_limit` 影响）
4. **修复方案**：
- 改写 SQL：函数包装 → 反向范围条件（`YEAR(t)=2024` → `t >= '2024-01-01' AND t < '2025-01-01'`）
- 加索引：新建覆盖索引 / 联合索引
- 强制手段：`ANALYZE TABLE` 刷新统计信息、`FORCE INDEX` 硬推、临时 `optimizer_switch` 关闭 index_merge
- 索引设计：把高频查询列放到联合索引前面，把选择性高的列放前面
**考察方向**：
- **深度**：能否说清"为什么某个 SQL 没走索引"的优化器决策逻辑（cost 模型）
- **广度**：能否覆盖 sys schema、performance_schema、INFORMATION_SCHEMA 三层视图
- **应用**：能否在真实线上慢查询中快速定位并给出修复方案
**延伸递进面试题**：
1. **为什么 `EXPLAIN` 显示的 `rows` 可能比实际扫描行数大或小很多？**
- 解答要点：`rows` 是基于统计信息的估算，`ANALYZE TABLE` 后会更准；8.0 用 `EXPLAIN ANALYZE` 看真实值。
2. **`IN (1,2,...,500)` 里的 500 个值为什么不走索引？**
- 解答要点：受 `eq_range_index_dive_limit`（默认 200）限制，超过阈值优化器用 `statistics` 估算而不是精确 `index dive`，估算偏大导致选全表。可以 `SET eq_range_index_dive_limit=1000`。
3. **`sys.statement_analysis` 里怎么定位长期不走的索引？**
- 解答要点：JOIN `schema_index_statistics` 看 `count_star=0` 且 `rows_examined>0` 的索引，是候选可删除索引；再结合 `EXPLAIN` 验证是否真的没人用。
面试官您好，索引失效排查我一般分三步走。
第一步是**确认现象**。跑一下 `EXPLAIN`，重点看三个字段：`key` 是不是 NULL，如果是就是没走索引；`rows` 是不是接近全表；`Extra` 里有没有 `Using filesort` 或 `Using temporary`。如果不确定，可以再跑 `EXPLAIN FORMAT=JSON`，里面会有 `missing_covering_index` 建议和 `cost_info` 的估算代价，MySQL 8.0 还有 `EXPLAIN ANALYZE` 能看真实执行耗时。
第二步是**定位原因**，按几个高频场景去排查。第一类是**隐式转换**，比如 varchar 列跟数字比较，`WHERE code=123` 而不是 `WHERE code='123'`，MySQL 会把 varchar 强制转 number，索引就废了。第二类是**字段上加了函数或运算**，比如 `WHERE YEAR(create_time)=2024`、`WHERE UPPER(name)='A'`、`WHERE age+1=10`，这些都会让索引失效，标准改法是反向写成范围条件 `create_time >= '2024-01-01' AND create_time < '2025-01-01'`。第三类是**查询模式问题**，`LIKE` 左模糊、`OR` 拼接非索引列、`NOT IN`、`!=`、`< >`、`IS NOT NULL`，这些都会让优化器倾向全表。第四类是**排序或分组问题**，`ORDER BY` 一个非索引列会触发 filesort，如果文件排序代价高，优化器会放弃索引走全表。第五类是**统计信息过期**，MySQL 是基于采样估算的，数据倾斜严重时会选错。
第三步是**修复**。SQL 能改就改，把函数包装改成范围条件。改不了的加索引，联合索引要按高频查询列的顺序放。如果怀疑是统计信息不准，先跑 `ANALYZE TABLE` 刷新一下。真要保底可以 `FORCE INDEX` 硬推，但要配合 `EXPLAIN ANALYZE` 验证真实代价，别引入更差的全索引扫描。
日常我也会盯着 `sys.schema_index_statistics` 和 `performance_schema.table_io_waits_summary_by_index_usage`，发现 `count_star=0` 或者 `rows_selected/rows_examined` 长期低于 1% 的索引，就是失效或者无效索引，该改该删。
https://chat.deepseek.com/share/jqfr120k19i2vcl6e3
**核心考点定位**：理解乐观锁与悲观锁的本质差异（冲突时机的假设不同），掌握 MySQL 中的具体实现方式与选型判断。
**知识点拆解**：
1. **本质差异**：
- **悲观锁**：假设"我一定会冲突"，操作前先加锁，其他事务阻塞等待
- **乐观锁**：假设"大概率不会冲突"，不加锁，更新时校验版本，冲突才失败重试
- 关键区别：**悲观锁串行化访问，乐观锁并发执行 + 事后校验**
2. **MySQL 中的乐观锁实现**：MySQL 本身没有乐观锁，需要在应用层做。标准做法是加 `version` 列：
```sql
UPDATE t SET col=?, version=version+1 WHERE id=? AND version=?;
```
影响行数 = 1 表示成功；影响行数 = 0 表示版本冲突，业务层选择重试或失败。这也是 **CAS（Compare-And-Swap）** 的数据库版本。
3. **MySQL 中的悲观锁实现**：
- `SELECT ... FOR UPDATE`：加排他锁（X 锁），其他事务读/写都要等
- `SELECT ... FOR SHARE`（8.0+）/ `LOCK IN SHARE MODE`（5.7）：加共享锁（S 锁），其他事务读可以，写要等
- `UPDATE` / `DELETE`：隐式加 X 锁（不需要显式声明）
- `INSERT`：隐式加 S 锁（gap + insert intention lock）
4. **加锁粒度**：
- **行锁**：锁定单行，最常用
- **临键锁（Next-Key Lock）**：锁定一行 + 后面的间隙，RR 隔离级别下默认，防止幻读
- **间隙锁（Gap Lock）**：只锁间隙，不锁记录，RR 独有
- **表锁 / MDL**：DDL 会加 MDL，阻塞 DML
5. **MySQL 8.0 增强**：
- `FOR UPDATE NOWAIT`：拿不到锁立即返回错误，不阻塞
- `FOR UPDATE SKIP LOCKED`：跳过已加锁的行，用于"队列消费"模式
- `LOCKED_COLUMNS=NONE`：锁表而不是锁行
6. **死锁与监控**：
- `SHOW ENGINE INNODB STATUS` 看 `LATEST DETECTED DEADLOCK` 段
- `information_schema.innodb_trx` 查看当前事务
- `information_schema.innodb_lock_waits`（8.0 之前）/ `performance_schema.data_lock_waits`（8.0）看锁等待链
- `innodb_lock_wait_timeout` 控制等待超时（默认 50 秒）
- `innodb_deadlock_detect=ON`（默认）通过 wait-for graph 检测死锁，回滚 undo log 少的
7. **选型原则**：
- **乐观锁适用**：读多写少、冲突率低（点赞计数、库存扣减中低并发场景）、能容忍重试
- **悲观锁适用**：读少写多、冲突率高、必须强一致（余额、订单支付、账户转账）
- 悲观锁的代价：并发度低、有死锁风险、有超时问题
**考察方向**：
- **深度**：能否说清乐观锁/悲观锁的假设差异，以及 MySQL 里为什么乐观锁要放在应用层
- **广度**：能否关联到隔离级别、死锁检测、锁等待监控
- **应用**：能否针对具体业务场景给出选型建议
**延伸递进面试题**：
1. **`SELECT FOR UPDATE` 在 RC 和 RR 隔离级别下锁的粒度有什么区别？**
- 解答要点：RC 下只加当前读的行锁，无间隙锁；RR 下加 next-key lock，包含间隙锁，防止幻读。
2. **`FOR UPDATE SKIP LOCKED` 可以用来做什么？**
- 解答要点：任务队列场景。多个 worker 抢任务时，跳过已被其他 worker 加锁的行，避免互相阻塞。适合 Redis 之外的 DB 队列实现。
3. **乐观锁的 `version` 列，遇到重试风暴怎么办？**
- 解答要点：加指数退避 + 最大重试次数；核心业务可切换到悲观锁或分布式锁；也可用 CAS 型 `UPDATE ... WHERE old_val=?` 代替 version。
面试官您好，乐观锁和悲观锁本质上是两种对"冲突概率"的假设。悲观锁假设"我一定会冲突"，操作前先加锁，别人只能等；乐观锁假设"大概率不会冲突"，先不加锁直接干活，提交前再校验，冲突才失败重试。
**悲观锁在 MySQL 里是原生支持的**。核心语法是 `SELECT ... FOR UPDATE`，它会给读到的行加排他锁，其他事务读、写都得等。8.0 之后还有 `SELECT ... FOR SHARE`，加的是共享锁，其他事务读可以并行，只有写要等。`UPDATE` 和 `DELETE` 本身就会隐式加 X 锁，不需要显式声明。加锁粒度主要有行锁、临键锁（next-key lock）、间隙锁，RR 隔离级别下默认用临键锁防止幻读。
8.0 还有两个增强语法我觉得很实用：`FOR UPDATE NOWAIT`，拿不到锁立即报错不阻塞；`FOR UPDATE SKIP LOCKED`，跳过已经被加锁的行，特别适合任务队列场景——多个 worker 抢任务，跳过的行让下一个 worker 处理，互不干扰。
**乐观锁 MySQL 没有原生实现，要在应用层做**。标准做法是表里加一个 `version` 列，更新的时候带上版本条件：`UPDATE t SET col=?, version=version+1 WHERE id=? AND version=?`。这条 SQL 的影响行数如果是 1，说明成功；如果是 0，说明别人在我读和写之间改过了，版本对不上，业务层决定是重试还是失败。这本质就是 CAS，Compare-And-Swap 的数据库版本。
**选型上**，悲观锁适合高冲突、强一致的场景，比如余额扣减、订单支付；乐观锁适合低冲突、能容忍重试的场景，比如点赞计数、普通库存扣减。悲观锁的代价是并发度低、有死锁风险、有超时问题，MySQL 默认 `innodb_lock_wait_timeout=50s`，超过就报错。死锁的话 `innodb_deadlock_detect=ON`（默认开启），InnoDB 会通过 wait-for graph 检测到死锁，回滚 undo log 少的那个事务。要排查线上死锁看 `SHOW ENGINE INNODB STATUS` 的 LATEST DETECTED DEADLOCK 段，或者查 `performance_schema.data_lock_waits` 看锁等待链。
https://chat.deepseek.com/share/1f4jfjd7lcbtnwc0hu
**核心考点定位**：理解 MySQL 优化器基于成本模型的决策机制，掌握选错索引的诊断与修复手段。
**知识点拆解**：
1. **优化器成本模型**：MySQL 使用基于成本的优化器（CBO），核心公式 `cost = cpu_cost × rows + io_cost × pages_read`。它会为每种可能计划算 cost，选最小的。cost 是基于**统计信息估算**出来的，不是真实扫描值，这就是选错索引的根源。
2. **统计信息从哪来**：InnoDB 持久化统计（`innodb_stats_persistent=ON`），用 `innodb_stats_persistent_sample_pages`（默认 20 页）随机采样估算基数。**采样只有 20 页**，数据倾斜、热点集中在少数页时估算必失准。
3. **选错索引的典型场景**：
- **统计信息过期**：数据大量增删后没 `ANALYZE`，优化器用旧基数算
- **数据倾斜**：某个值占比 90%，采样页没扫到，优化器以为"很分散"
- **`IN` 元素过多**：超过 `eq_range_index_dive_limit`（默认 200），从精确 `index dive` 切换成基于统计估算
- **多索引可选**：`(a)`、`(a,b)`、`(a,c)` 都存在，优化器挑了错误的
- **`ORDER BY` 排序列**：优化器偏好 `prefer_ordering_index=on`，即使成本更高也倾向避免 filesort
- **`index_merge` 陷阱**：优化器认为 index merge 更快但实际更慢
4. **诊断手段**：
- `EXPLAIN` 看 `key`、`rows`、`Extra`、`key_len`
- `EXPLAIN FORMAT=JSON`：看 `cost_info.query_cost`、`attached_condition`、`missing_covering_index`
- `EXPLAIN ANALYZE`（8.0）：看**真实执行耗时**与估算的差距
- `SHOW INDEX FROM t`：看 `Cardinality` 是否合理
- `sys.statement_analysis`：找慢查询关联的 index 使用
5. **修复手段（按优先级）**：
1. **改写 SQL**：把无法用索引的写法改掉（去掉函数包装、拆 `OR` 为 `UNION`）
2. **补索引/调索引**：加更贴合查询的索引
3. **刷新统计**：`ANALYZE TABLE` 或 8.0 建**直方图** `ANALYZE TABLE t UPDATE HISTOGRAM ON col`
4. **优化器开关**：调 `optimizer_switch`、`eq_range_index_dive_limit`
5. **FORCE INDEX / IGNORE INDEX**：硬指定，最后兜底
6. **8.0 Optimizer Hints**：`/*+ FORCE_INDEX(t idx) */`，写在 SQL 里同样有硬编码风险
6. **工程治理**：
- 定期扫描 `performance_schema.table_io_waits_summary_by_index_usage`，`count_star=0` 的索引是候选删除
- 建索引、删索引要有 CR 流程，避免"索引删了但 SQL 还在 FORCE INDEX"
- 生产环境变更前跑 `EXPLAIN ANALYZE` 看真实代价
**考察方向**：
- **深度**：能否讲清成本模型的估算逻辑，采样为什么会导致失真
- **广度**：能否覆盖统计信息、优化器开关、直方图、Hints 全链条
- **应用**：能否在真实线上事故中给出一套排查与兜底方案
**延伸递进面试题**：
1. **`eq_range_index_dive_limit` 是什么？为什么超过它反而选错？**
- 解答要点：控制 `IN` 或范围条件里 index dive 的最大次数。超过后优化器退回用 `stats.records` 均分估算，倾斜数据下偏差巨大。
2. **`prefer_ordering_index` 是干什么的？为什么会导致"更贵的索引被选中"？**
- 解答要点：默认 `on`，优化器偏好能避免 filesort 的索引。即使该索引要扫描更多行，只要省了排序就选它。
3. **MySQL 8.0 直方图是什么？什么时候该建？**
- 解答要点：`ANALYZE TABLE t UPDATE HISTOGRAM ON col` 会把列的值分布采样存下来，帮助优化器更准地估算基数。适合长尾分布（如 status 字段 90% 都是 1）场景。
面试官您好，MySQL 选错索引本质上是基于成本的优化器（CBO）算错了成本。它做决策的核心是 `cost = rows × cpu_cost + pages × io_cost`，`rows` 和 `pages` 都来自统计信息，而统计信息是 InnoDB 通过 `innodb_stats_persistent_sample_pages` 采样估算出来的，默认只采样 20 页。所以只要数据有倾斜、采样页没覆盖到热点分布，优化器就会算错行数，选到错误的索引。
典型场景有几个：一是**统计信息过期**，数据大量增删后没跑 `ANALYZE`，优化器用旧数据算；二是**数据倾斜**，某个值占比 90% 但采样没扫到；三是 `IN` 元素超过 `eq_range_index_dive_limit`（默认 200），优化器从精确 index dive 退回均分估算；四是**排序偏好**，`prefer_ordering_index=on` 让优化器倾向选能避免 filesort 的索引，即使要扫更多行。
排查上我会先跑 `EXPLAIN` 看 `key`、`rows`、`Extra`，再用 `EXPLAIN FORMAT=JSON` 看 `cost_info` 和 `attached_condition`，MySQL 8.0 最好用 `EXPLAIN ANALYZE` 直接看真实耗时和估算的差异，差距越大说明统计信息越不准。
修复我按优先级来：先**改 SQL**，把走不到索引的写法改掉（比如函数包装改范围条件，`OR` 拆 `UNION`）；再考虑**补索引**或者调整联合索引顺序；然后跑 `ANALYZE TABLE` 刷新统计，如果是长尾分布的场景，MySQL 8.0 还可以建**直方图** `ANALYZE TABLE t UPDATE HISTOGRAM ON col`；实在不行再动 `optimizer_switch` 或者硬指定。
`FORCE INDEX` 我一般放最后手段。它写死在 SQL 里有两个风险：一是数据变化后强走可能变成全索引扫描反而更慢；二是我之前踩过坑——代码里 `FORCE INDEX` 硬编码，后来团队把这个索引当冗余删了，业务直接生产报错。所以我的原则是 `FORCE INDEX` 上线前必须 `EXPLAIN ANALYZE` 验证真实代价，而且要有索引治理流程，通过 `performance_schema.table_io_waits_summary_by_index_usage` 定期扫 `count_star=0` 的索引，删之前先 grep 代码库确认没人引用。
https://chat.deepseek.com/share/9fe4n94b1weytntp9b
**核心考点定位**：理解 MySQL 5.7 binlog 组提交的三阶段模型与 fsync 合并优化，掌握其在双写（binlog + redo）场景下的收益。
**知识点拆解**：
1. **背景：为什么需要组提交**
- 事务提交时必须保证 binlog 和 redo log 都刷盘（**双 1 配置**），否则可能丢失或数据不一致
- 单个事务提交要 fsync 两次：一次 binlog，一次 redo log
- fsync 是磁盘 IO 的瓶颈，一次 fsync 可能几毫秒
- 高并发下每秒数千事务，fsync 次数成为吞吐瓶颈
2. **什么是组提交**
- **binlog 组提交**（MySQL 5.7 引入，5.6 前已有简化版）：多个事务的 binlog 写完后合并 fsync
- **redo log 组提交**（InnoDB 一直有）：多个事务的 redo log 写完后合并 fsync
- 本质：把 N 个事务的 2N 次 fsync 合并成 2 次
3. **binlog 组提交的三阶段模型**
- **Flush 阶段**：每个事务把 binlog 从 page cache 刷到 OS cache（顺序写，快），此时事务独立进行，无排队
- **Sync 阶段**：一个事务（Group Leader）等所有事务 flush 完后，代表组执行 fsync，把 binlog 刷到磁盘；组内其他事务等待
- **Commit 阶段**：fsync 完成后，所有事务一起更新 binlog 状态、通知主线程 commit，进入 redo log 提交
4. **Group Leader 机制**（5.7 新特性）
- 只有一个事务是 Group Leader，其他是 Group Follower
- Group Leader 负责在 Sync 阶段做 fsync，Group Follower 等待
- 组越大，Leader 的 fsync 覆盖的事务越多，每个事务平摊成本越低
- 5.7 引入**组提交并发优化**：允许多个 Group Leader 交替工作，减少组间阻塞
5. **redo log 组提交**
- InnoDB 的 WAL 机制，事务修改先写 redo log，不立刻刷脏页
- 多个事务的 redo 可以合并 fsync
- `innodb_flush_log_at_trx_commit`：1=每次 commit fsync，0=每秒 fsync（可能丢 1 秒数据），2=写 OS cache 不 fsync（MySQL 崩溃不丢，系统崩溃可能丢）
6. **5.7 的关键优化：联合提交**
- binlog 组提交的 Commit 阶段触发 InnoDB redo log 组提交的 fsync
- 相当于 binlog 和 redo 的组提交"绑在一起"完成
- 总 fsync 次数从"2N"降到"2"（每组），性能提升显著
7. **性能影响**
- `sync_binlog=1`（默认，最安全）+ 组提交：每组 fsync 一次 binlog
- `sync_binlog=1000`：每 1000 个事务 fsync 一次 binlog，崩溃最多丢 1000 个事务
- `sync_binlog=0`：由 OS 决定 fsync 时机，最快但可能丢大量数据
- 生产环境通常：双 1 配置保安全 + 依赖组提交提性能
**考察方向**：
- **深度**：能否讲清 Flush / Sync / Commit 三阶段模型，Group Leader 机制
- **广度**：能否关联到 2PC、WAL、redo log 组提交
- **应用**：能否根据业务容忍度选择 `sync_binlog` 与 `innodb_flush_log_at_trx_commit`
**延伸递进面试题**：
1. **binlog 组提交的 Commit 阶段和 InnoDB 的 2PC 有什么关系？**
- 解答要点：binlog 组提交的 Commit 阶段触发 InnoDB 的 redo log prepare→commit 两阶段，两者组提交在此联动。
2. **`sync_binlog=1` 和 `innodb_flush_log_at_trx_commit=1` 都能保证零丢失吗？**
- 解答要点：只能保证 MySQL 层不丢（binlog 有、redo 有），但操作系统崩溃时 OS cache 未落盘仍会丢。要真正持久需要 `O_DIRECT` + `fdatasync`。
3. **MySQL 8.0 对组提交有哪些优化？**
- 解答要点：8.0 引入了 binlog 多线程写入（binlog 组提交优化）、GTID 压缩、`binlog_transaction_compression` 等。
面试官您好，MySQL 5.7 的组提交，主要是解决高并发下事务提交时 binlog 和 redo log 频繁 fsync 的性能瓶颈。要理解组提交，先要理解一个事务提交要经过什么。
在双 1 配置下（`sync_binlog=1` + `innodb_flush_log_at_trx_commit=1`），单个事务提交最少需要 2 次 fsync：一次刷 binlog，一次刷 redo log。fsync 是磁盘 IO 的瓶颈，一次可能几毫秒，高并发下每秒几千个事务就要几千次 fsync，直接把磁盘打爆。组提交就是把多个事务"打包"起来，一次 fsync 覆盖多个事务。
**binlog 组提交分三个阶段**：
**Flush 阶段**：每个事务把 binlog 从 MySQL 自己的 page cache 刷到 OS cache。这一步是顺序写，很快，每个事务独立进行，不需要排队。
**Sync 阶段**：关键的一步。事务进入这个阶段后要排队，由一个事务当 Group Leader 代表整组事务做 fsync，把 binlog 从 OS cache 刷到磁盘。其他事务作为 Group Follower 等待。这里组越大，fsync 成本被平摊得越薄。
**Commit 阶段**：Leader fsync 完成后，通知所有 Follower 一起更新 binlog 状态。同时这一步会触发 InnoDB 的 redo log 提交——**binlog 的 Commit 阶段和 InnoDB 的 redo 组提交在这里联动**，相当于 2PC 增强版：binlog 和 redo 一起提交，一次 fsync 完成两件事。
MySQL 5.7 的 Group Leader 机制引入了**多个 Group Leader 交替工作**，允许组之间的并行度更高，减少了组与组之间的阻塞。
**为什么要这么麻烦**：因为 fsync 慢，但事务提交不能丢 binlog 或 redo，只能靠组提交把 fsync 次数从"每事务 2 次"降到"每组 2 次"，性能可以从几百 TPS 提升到几千甚至上万 TPS。
**参数选择**：`sync_binlog=1` 是最安全的配置，崩溃不丢；`sync_binlog=1000` 是每 1000 个事务 fsync 一次，能提升性能但可能丢最多 1000 个事务；`sync_binlog=0` 交给 OS 决定，最快但可能丢大量数据。生产上通常是双 1 配置保安全，靠组提交拿性能，牺牲一点 CPU 换取数据可靠。
https://chat.deepseek.com/share/dsrk0ch0l2279ncpir
**核心考点定位**：以业务查询模式为锚点，系统掌握索引设计的全维度决策框架，覆盖数据、访问、性能、治理四条主线。
**知识点拆解**：
1. **业务维度：先看查询，再定索引**
- **查询频率**：一个索引每月只被用几次，收益远不如维护成本
- **查询模式**：等值 / 范围 / 排序 / 分组 / JOIN，每个模式对索引要求不同
- **数据量**：小表（< 1 万行）优化器倾向全表；大表必须靠索引
- **读写比**：读多写少 → 多建索引；写多读少 → 少建但建好
2. **基数（Cardinality）与选择率**
- 基数 = `distinct_count / total_rows`
- 基数接近 1（如 ID、订单号）→ 单列索引最有效
- 基数很低（如 gender、status 只有 2-3 值）→ 单列索引几乎无用，考虑放联合索引前缀或 Skip Scan
- 中基数（如 city、user_id）→ 单列索引常见
- `SHOW INDEX FROM t` 里的 `Cardinality` 是采样估算，用 `SELECT COUNT(DISTINCT col), COUNT(*) FROM t` 拿真实值
3. **联合索引的列顺序原则（关键）**
- **等值条件列放前**：`WHERE a=? AND b>?` → 索引顺序 `(a,b)` 才对
- **范围条件列放后**：范围列后面的列无法用索引定位（跨范围无序）
- **排序列放最后**：`ORDER BY` 的列如果放在联合索引最后，且前面都是等值，可以省 filesort
- **高基数列尽量靠前**：能更快收敛匹配行
- **避免低基数列开头**：如 `(status, user_id)`，status 基数 3 会导致扫描大量重复行
4. **覆盖索引优先**
- `SELECT` 的列全部命中某个索引 → 走覆盖索引，`Extra=Using index`
- 覆盖索引能省掉回表，性能提升数倍
- 权衡：索引宽度 vs 覆盖收益。避免为了覆盖把所有列都塞进一个索引，那样索引本身会膨胀
5. **回表数量控制**
- 回表次数 = 索引扫描到的行数，不是最终 LIMIT 数量
- `LIMIT 10` 的查询可能回表几百万次
- 优化：延迟关联、覆盖索引、ICP
6. **索引键长度限制**
- InnoDB 默认：`innodb_large_prefix=ON` 时 3072 字节（`COMPRESSED`/`DYNAMIC` 行格式）
- 旧版本 767 字节（`COMPACT`/`REDUNDANT` 行格式）
- TEXT/BLOB 不能整列建索引，必须用**前缀索引** `INDEX(name(20))`
- 前缀长度选择：`SELECT COUNT(DISTINCT LEFT(name, N)) / COUNT(DISTINCT name) FROM t`，达到 0.99+ 停止
7. **索引类型选择**
- B+ 树索引（默认）：等值、范围、排序都能用
- 全文索引：中文模糊搜索场景，需要 `ngram` 分词器
- 空间索引：GIS 场景
- HASH 索引：仅 Memory 引擎支持；InnoDB 的 adaptive hash index 是自动的，无法手动控制
- MySQL 8.0 新增：`FUNCTIONAL INDEX`（表达式索引），`DESCENDING INDEX`（降序索引）
8. **冗余索引治理**
- **前缀冗余**：`(a,b,c)` 已存在时 `(a,b)`、`(a)` 是冗余
- **完全冗余**：`(a)` 和 `(a)` 重复
- **反向冗余**：`(a,b)` 和 `(b,a)` 都能用，但用途不同，通常保留一个
- 治理手段：
- `sys.schema_unused_indexes` 找 `count_star=0` 的索引
- `performance_schema.table_io_waits_summary_by_index_usage` 精确统计
- MySQL 8.0 `ALTER TABLE t ALTER INDEX idx INVISIBLE` 灰度下线，观察 1-2 周再 DELETE
9. **特殊场景**
- **JOIN 场景**：外键关联字段必须建索引，否则 join 时右表全表扫描
- **分库分表**：索引设计要考虑 shard key 是否在索引里，跨分片查询尤其要谨慎
- **唯一约束**：业务上唯一的字段必须建唯一索引，同时能防止重复插入，还能加速等值查询
- **主键选择**：优先自增 bigint，避免 UUID（无序、体积大、页分裂）
- **NULL 值**：索引列允许 NULL 会增加 NULL bitmap 开销，优先 NOT NULL + 默认值
10. **工程化治理**
- 索引变更走 CR：谁能加、谁能删要有流程
- `FORCE INDEX` 硬编码是雷区：索引被删业务立即崩
- 定期审计：每月扫一次 `schema_unused_indexes` 和慢查询
- 生产变更前必跑 `EXPLAIN ANALYZE`（8.0）看真实代价
**考察方向**：
- **深度**：能否讲清联合索引列顺序、覆盖索引、基数选择背后的原理
- **广度**：能否覆盖索引类型、长度限制、8.0 新特性、分库分表
- **应用**：能否针对真实业务给出索引设计 + 治理的完整方案
**延伸递进面试题**：
1. **`(status, user_id, create_time)` 是常见设计，如果查询是 `WHERE status=1 ORDER BY create_time DESC`，这个索引能用吗？**
- 解答要点：能。status 等值命中前缀，`user_id` 未知但因为是联合索引的中间列——不对，中间列未知则 create_time 无法走排序。所以要改成 `(status, create_time)` 或直接建 `(status, create_time DESC)`（8.0 支持）。
2. **TEXT 字段做前缀索引怎么选长度？**
- 解答要点：跑 `SELECT COUNT(DISTINCT LEFT(col, N)), COUNT(DISTINCT col) FROM t` 让比值达到 0.99 或以上，同时兼顾空间。经验值：邮箱 20、手机号 11、用户名 10-15。
3. **MySQL 8.0 的 `INVISIBLE INDEX` 相比直接删除有什么好处？**
- 解答要点：可以先设置为不可见观察 1-2 周，确认没有 SQL 走这个索引后，再执行真正的 `DROP INDEX`。避免"删了才发现在用"的事故。DDL 也是在线的。
面试官您好，索引设计我按四条主线来讲：业务、数据、性能、治理。
**第一条，业务维度**。索引不是越多越好，一定要业务驱动——有查询模式才建索引。我会先摸清几个问题：谁在查、查多频繁、单条查询返回多少行、要不要排序、表是读多还是写多。小表几千行不用建，优化器自己全表扫；大表必须靠索引。写多读少的表要克制，每个索引都是 INSERT/UPDATE/DELETE 的额外维护成本。
**第二条，数据维度，重点是基数和联合索引列顺序**。基数就是 `distinct_count / total_rows`，越接近 1 越适合建单列索引，比如 ID、订单号；基数很低（如 status 只有 2-3 值）单独建索引基本没用，但可以放联合索引前面做前缀。联合索引的列顺序有讲究：等值条件列放前面、范围条件列放后面、`ORDER BY` 列放最后——这样能省 filesort。反过来如果排序列前面还有个未知列，排序就废了。
**第三条，性能维度，重点是覆盖索引和回表控制**。如果能把 SELECT 列全部塞进一个索引里，就走覆盖索引，EXPLAIN 显示 `Using index`，性能能提升好几倍。但索引宽度也有代价，不能什么都往里塞。回表次数取决于索引扫描行数而不是 LIMIT，所以要用延迟关联或 ICP 减少。索引键长度也有硬限制，InnoDB 默认 3072 字节，TEXT 字段必须做前缀索引，用 `SELECT COUNT(DISTINCT LEFT(col, N)) / COUNT(DISTINCT col)` 找最优长度。
**第四条，索引治理**。这块特别重要，很多线上事故出在这里。三个点：一是冗余治理，`(a,b,c)` 存在时 `(a,b)`、`(a)` 就是冗余，应该删掉；二是无索引治理，用 `sys.schema_unused_indexes` 或者 `performance_schema.table_io_waits_summary_by_index_usage.count_star=0` 找长期没人用的索引；三是灰度下线，MySQL 8.0 有 `ALTER TABLE t ALTER INDEX idx INVISIBLE`，可以先设为不可见观察一两周，确认没有 SQL 依赖后再真删。
特殊场景还有几个：JOIN 关联字段必须建索引；分库分表要考虑 shard key 是否在索引里；唯一约束优先用唯一索引既能防重又能加速；主键选自增 bigint 避免 UUID 打散 B+ 树导致页分裂和随机 IO；索引列最好 NOT NULL，避免 NULL bitmap 的额外开销。
最后工程化流程：任何索引变更走 CR，生产变更前跑 `EXPLAIN ANALYZE` 看真实代价，每月审计一次 `schema_unused_indexes`。特别警惕 `FORCE INDEX` 硬编码——团队有人删了这个索引而代码里还强推，业务直接生产报错，我之前踩过这个坑。
---
**核心考点定位**：既要有操作系统层死锁的理论理解，更要掌握 InnoDB 死锁的检测、监控与业务侧规避手段。
**知识点拆解**：
1. **死锁理论（四必要条件）**：
- **互斥**：一次只能被一个事务持有
- **占有且等待**：持有 A 的同时等 B
- **不可剥夺**：事务持有的锁不能被动回收，必须主动释放
- **循环等待**：A 等 B、B 等 C、C 等 A 形成环
- 破坏任一条件即可防止死锁
2. **InnoDB 的死锁机制**：
- InnoDB 是**自动检测**型：默认 `innodb_deadlock_detect=ON`，事务在等锁时会持续构建 wait-for graph，一旦形成环就立刻识别出死锁
- **牺牲者选择**：回滚 undo log 少（改动小）的事务，让它抛出 `ERROR 1213`，另一事务继续执行
- 死锁检测成本：O(N²) 检查当前所有等锁事务，事务数很多时开销大
- 超时兜底：`innodb_lock_wait_timeout`（默认 50 秒），超过直接抛 `ERROR 1205: Lock wait timeout exceeded`，事务回滚
3. **InnoDB 常见死锁场景**：
- **间隙锁交叉**：两个事务在 RR 隔离级别下分别用 `INSERT` 或 `SELECT FOR UPDATE` 请求不同的间隙锁，最终循环等待
- **不同顺序更新多行**：事务 A 先锁 id=1 再锁 id=2，事务 B 先锁 id=2 再锁 id=1
- **`INSERT ... ON DUPLICATE KEY UPDATE`**：并发插入相同主键，一个 INSERT 一个 UPDATE 触发死锁
- **索引缺失导致的锁升级**：本该走索引却全表扫描，锁大量行放大冲突面
- **外键约束**：父子表同时更新时的加锁顺序
- **`UPDATE` 无 WHERE 条件**：锁全表
4. **死锁监控与排查**：
- `SHOW ENGINE INNODB STATUS`：看 `LATEST DETECTED DEADLOCK` 段，包含两个事务的 SQL、持有的锁、等待的锁
- `information_schema.innodb_trx`：当前运行中的事务
- `information_schema.innodb_locks`（5.7）/ `performance_schema.data_locks`（8.0）：当前持有的锁
- `performance_schema.data_lock_waits`（8.0）：锁等待链，看谁等谁
- 慢查询日志里 `Error 1213` 频次监控
5. **业务侧防死锁方案（优先级从高到低）**：
- **固定加锁顺序**：所有事务按主键升序加锁，如统一先锁 id 小的行
- **缩小事务粒度**：事务内越少锁越好，避免长事务
- **减少事务内 SQL 数量**：一条 SQL 完成就一条，避免多次 SELECT FOR UPDATE + UPDATE
- **加锁前先用 SELECT 判断**：能用 `SELECT ... FOR UPDATE NOWAIT`（8.0）就别阻塞
- **走乐观锁**：用 `version` 列 CAS 更新，避免悲观锁冲突
- **合理建索引**：无索引的 UPDATE/DELETE 会锁大量行
- **控制并发**：热点行加应用层分布式锁（Redis、Zookeeper）串行化
- **调大 timeout**：`innodb_lock_wait_timeout` 从 50s 提到 300s，让慢事务有机会完成
6. **参数调整场景**：
- 默认 `innodb_deadlock_detect=ON`：适合事务短、数量少
- 大量长事务：检测开销大，可 `innodb_deadlock_detect=OFF` + 依赖 timeout
- 高并发写入：可能需 `innodb_lock_wait_timeout` 减小到 5-10 秒快速失败
**考察方向**：
- **深度**：能否讲清 InnoDB 的自动死锁检测机制与牺牲者选择逻辑
- **广度**：能否覆盖监控工具链、参数调优、业务侧规避
- **应用**：能否在真实线上死锁事故中给出一套定位与修复方案
**延伸递进面试题**：
1. **为什么两个相同 SQL 并发执行会死锁，同一个 SQL 单独跑不会？**
- 解答要点：SQL 相同不代表访问顺序相同。RR 隔离下两条 UPDATE 可能锁不同的间隙/行，如果扫描顺序受 B+ 树遍历影响，可能形成交叉等待。
2. **`innodb_deadlock_detect=OFF` 有什么后果？**
- 解答要点：不再自动检测，只能依赖 `innodb_lock_wait_timeout` 让等锁方超时回滚。优点：省 CPU（大事务场景明显）；缺点：恢复慢、等待时间长。
3. **怎么在压测环境主动触发死锁？**
- 解答要点：两个会话，会话 1 `BEGIN; UPDATE t SET c=c+1 WHERE id=1;`（不 commit），会话 2 `BEGIN; UPDATE t SET c=c+1 WHERE id=2;`，然后会话 1 `UPDATE t SET c=c+1 WHERE id=2;`，会话 2 `UPDATE t SET c=c+1 WHERE id=1;`，会话 1 立刻收到 1213 报错。
面试官您好，死锁我先从理论上讲清楚，再落到 InnoDB 的具体表现。
理论上死锁有四个必要条件：互斥、占有且等待、不可剥夺、循环等待。破坏任意一个就能防止死锁。经典例子是两个事务，A 拿了资源 1 等 2，B 拿了资源 2 等 1，就形成循环等待，谁都动不了。
但在 MySQL InnoDB 里，死锁的表现和检测方式跟理论不太一样，我主要讲几个点。
**InnoDB 的默认行为是自动检测**。`innodb_deadlock_detect` 默认开启，事务在等锁的时候会持续构建 wait-for graph，一旦发现环，就立刻选出牺牲者——通常是 undo log 少、改动少的那个事务，回滚它并抛 `ERROR 1213: Deadlock found`，另一事务继续执行。所以开发者看到的错误是 1213，业务代码要做重试。检测本身有 O(N²) 开销，事务特别多的场景可以考虑关闭检测，改用 `innodb_lock_wait_timeout`（默认 50 秒）来兜底——超时会抛 1205 错误。
**常见死锁场景**有几个。第一是间隙锁交叉，RR 隔离级别下两个事务用不同的 WHERE 条件触发不同间隙锁，最终循环等待。第二是多行更新顺序不同，A 先锁 id=1 再锁 id=2，B 反过来。第三是 `INSERT ... ON DUPLICATE KEY UPDATE`，并发插相同主键时一个走 INSERT 一个走 UPDATE 触发死锁。第四是 UPDATE 没写 WHERE 条件锁全表。第五是索引缺失导致锁大量行放大冲突面。
**排查线上死锁**我会按这个链路走：先跑 `SHOW ENGINE INNODB STATUS` 看 `LATEST DETECTED DEADLOCK` 段，里面会显示两个事务的 SQL、各自持有的锁、等待的锁；再看 `performance_schema.data_lock_waits` 找锁等待链；最后看 `innodb_trx` 找超长事务。慢查询日志里频繁出现 1213 错误就是死锁高发信号。
**预防方案**我按优先级列一下。第一是**固定加锁顺序**，所有事务按主键升序加锁，这是最有效的办法，业务侧只要约定统一顺序就能消掉大部分死锁。第二是**缩小事务粒度**，一条 SQL 能完成就别拆多条，事务内减少锁数量。第三是**避免长事务**，长事务持有锁越久，越容易和其他事务撞。第四是**加索引**，无索引的 UPDATE 会锁大量行。第五是**用乐观锁绕过**，`version` 列 CAS 更新，冲突就重试，不用悲观锁串行化。第六是**8.0 的 NOWAIT / SKIP LOCKED**，`SELECT FOR UPDATE NOWAIT` 拿不到锁立即返回，避免阻塞等待成环。
参数层面，默认配置基本够用，只是长事务多、检测开销大的场景可以 `innodb_deadlock_detect=OFF`，靠 `innodb_lock_wait_timeout` 兜底。反过来热点行死锁频发的场景，把 `innodb_lock_wait_timeout` 调小让失败快一点，业务侧快速重试。
https://chat.deepseek.com/share/wy9ppt0dw6b7uhgyct
---