MySQL 核心原理：索引、事务、锁与性能排查

一、先建立 InnoDB 全景图

一次更新大致会同时影响：

SQL 层解析与优化
  ↓
InnoDB Buffer Pool 中的数据页
  ↓
undo log 保存旧版本
  ↓
redo log 保证崩溃恢复
  ↓
binlog 记录逻辑变更
  ↓
后台线程把脏页刷盘

理解 MySQL 不应只背索引。索引决定“怎么找到数据”，MVCC 和锁决定“并发时看见什么、能修改什么”，日志决定“失败后如何恢复”。

二、为什么 InnoDB 使用 B+Tree

B+Tree 适合磁盘和页式存储：

• 非叶子节点只保存索引信息，一个页可容纳更多键，树高更低；
• 叶子节点按键有序并相互连接，范围扫描高效；
• 单次查找通常只需很少的页访问；
• 等值、排序、范围查询都能复用同一结构。

聚簇索引与二级索引

InnoDB 的主键索引是聚簇索引，叶子节点保存完整行数据。二级索引叶子节点保存索引列和主键值。

二级索引查找
  ↓
得到主键
  ↓
回到聚簇索引查完整行（回表）

如果查询所需列都包含在索引中，就可以使用覆盖索引，避免回表。

主键应尽量短、稳定、递增。过长主键会被每个二级索引重复存储；频繁随机插入可能增加页分裂和缓存局部性问题。

三、联合索引与最左匹配

联合索引 (a, b, c) 按三列组合排序，通常可支持：

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?

能否使用后续列，要结合查询条件和优化器判断。遇到范围条件后，后续列通常不能继续用于缩小同一段扫描范围，但仍可能用于索引条件下推、覆盖或排序，不能简单背成“范围后索引全部失效”。

常见索引效果变差的原因

• 对索引列做函数或隐式类型转换；
• 联合索引缺少关键前缀；
• 低选择性条件返回数据过多；
• 前导模糊匹配，如 LIKE '%abc'；
• OR 两侧缺少合适索引；
• 统计信息不准，优化器估算错误；
• 查询本身需要返回大部分表数据，全表扫描反而更便宜。

“写了索引”不代表一定使用，“使用了索引”也不代表查询一定快。

四、MVCC 与 Read View

InnoDB 通过隐藏事务信息、undo log 版本链和 Read View 实现一致性非锁定读。

可以把 Read View 理解成一个可见性规则集合：当前事务读取某行时，根据该行版本的事务 ID 判断它是否可见；不可见就沿 undo 版本链寻找更早版本。

RC 与 RR 的核心差异

• READ COMMITTED 通常每次一致性读都创建新的 Read View，因此同一事务两次查询可能看到其他事务新提交的数据；
• REPEATABLE READ 通常在第一次一致性读时建立快照，后续一致性读复用它，因此普通快照读可以重复读取同一视图；
• 当前读，如 SELECT ... FOR UPDATE、UPDATE、DELETE，要读取可锁定的当前版本，不能用旧快照替代。

MVCC 主要优化读写并发，不意味着“所有读都不加锁”。

五、事务隔离与并发现象

常见并发现象：

• 脏读：读到未提交数据；
• 不可重复读：同一行两次读取结果不同；
• 幻读：同一条件两次查询，结果集合出现或消失记录；
• 丢失更新：并发更新互相覆盖。

MySQL InnoDB 默认通常是 RR。RR 下，快照读通过 MVCC 提供可重复读；锁定范围的当前读可使用 next-key lock 抑制幻行插入。

隔离级别越高不等于业务一定越安全。库存、额度、余额等场景仍需明确使用条件更新、锁或版本号。

六、InnoDB 的锁

Record Lock

锁定索引记录。InnoDB 的“行锁”本质上通常落在索引记录上。

Gap Lock

锁定索引记录之间的间隙，主要用于防止其他事务在范围中插入新记录。

Next-Key Lock

Record Lock 与其前方 Gap Lock 的组合，用于锁定一个范围。

锁的实际范围取决于：

• 隔离级别；
• 是否命中唯一索引；
• 查询条件和实际执行计划；
• 扫描到的索引记录；
• 是否属于锁定读或写操作。

因此不要只看 SQL 的 WHERE 条件猜锁范围，必须结合索引和执行计划。

七、死锁如何产生与治理

典型死锁是不同事务以不同顺序持有并等待资源：

事务 A：锁订单 1 → 等订单 2
事务 B：锁订单 2 → 等订单 1

治理原则：

1. 多表、多行更新保持固定顺序；
2. 缩短事务，不在事务中做远程调用；
3. 建立合适索引，减少无关记录扫描与加锁；
4. 单次批量不要过大；
5. 应用必须捕获死锁异常并做有限重试；
6. 通过 SHOW ENGINE INNODB STATUS、错误日志和性能视图分析死锁链。

死锁不是简单“提高隔离级别”就能消除，写操作之间仍可能形成循环等待。

八、redo、undo 与 binlog

undo log

保存修改前的逻辑信息，用于事务回滚和 MVCC 版本读取。长事务会阻碍旧版本清理，导致 undo 膨胀。

redo log

InnoDB 的物理/页级恢复日志。更新先修改内存页并记录 redo，之后再异步刷脏页，借助 WAL 降低随机写并支持崩溃恢复。

binlog

MySQL Server 层的逻辑变更日志，主要用于复制、审计和时间点恢复。

两阶段提交

一次事务同时涉及 redo log 和 binlog。MySQL 通过 prepare/commit 协调两类日志，避免崩溃后出现“存储引擎认为提交、binlog 却没有”或相反的状态。

九、高并发资金更新如何保证安全

条件更新

UPDATE account
SET available = available - :amount
WHERE account_id = :id
  AND available >= :amount;

根据受影响行数判断是否成功，可以把校验和扣减放入一个原子 SQL，避免“先查余额再更新”的竞态。

幂等唯一键

对业务请求号、凭证号建立唯一索引：

业务幂等号唯一约束
  ↓
同一请求重复提交
  ↓
数据库拒绝第二次插入

唯一键是资金系统非常重要的最后一道防线，但仍需明确冲突后的业务返回语义。

热点账户

大量请求更新同一余额行时，瓶颈往往是行锁串行、锁等待和连接占用。可根据实时性要求采用：

• 同账户请求串行化；
• 异步聚合、批量入账；
• 凭证先落库，余额异步更新；
• 拆分业务账户或分段计数；
• 限制事务持锁时间。

不能为了吞吐简单把一个强一致余额随机拆成多个值，必须同时设计汇总和一致性规则。

十、慢 SQL 排查顺序

1. 获取真实 SQL、参数、耗时分布和调用频率；
2. 看 EXPLAIN / EXPLAIN ANALYZE；
3. 关注访问类型、实际扫描行数、过滤率、选中索引和 Extra；
4. 检查索引选择性、数据分布与统计信息；
5. 检查锁等待、事务时长和连接池；
6. 判断瓶颈是 CPU、磁盘 I/O、网络返回量还是锁；
7. 修改后用相同数据规模和参数复测。

不要看到慢查询就先加索引。索引会增加写放大、存储占用和维护成本。

十一、高频面试问题

1. 聚簇索引和二级索引有什么区别？
2. 联合索引为什么遵循最左匹配？
3. 覆盖索引和索引条件下推分别解决什么问题？
4. RC 与 RR 的 Read View 有何差异？
5. 快照读与当前读有什么区别？
6. Record、Gap、Next-Key Lock 如何配合？
7. 为什么有索引仍可能慢？
8. redo log、undo log、binlog 分别解决什么问题？
9. 两阶段提交为什么必要？
10. 账户余额如何防止超扣、重复入账和热点锁竞争？

参考资料

• InnoDB 简介与聚簇索引
• InnoDB 锁与事务模型
• InnoDB 事务隔离级别
• InnoDB 死锁