MySQL 索引与锁

目录

1. 索引
   1.1 B+ 树索引
   1.2 全文索引
   1.3 哈希索引
   1.4 索引选择的 Cardinality 值
2. 锁
   2.1 锁类型
   2.2 一致性非锁定读
   2.3 一致性锁定读
   2.4 自增计数
3. MySQL 锁的分类
   3.1 Lock 锁的类型
   3.2 阻塞
   3.3 死锁

1. 索引

InnoDB 存储引擎支持多种索引结构，以下是常见的类型及其特点：

1.1 B+ 树索引

B+ 树索引是 InnoDB 中最常用的索引类型，分为以下两种：

• 聚集索引（Clustered Index）
  以主键构建 B+ 树，叶子节点存储完整的行记录，因此叶子节点也被称为数据页。由于数据和索引存储在一起，聚集索引的查询效率较高。
• 辅助索引（Secondary Index）
  以索引键（非主键）构建 B+ 树，叶子节点存储的是主键值而非完整行记录。查询时，需通过主键值回表（即再次访问聚集索引）获取完整数据。
  B+ 树索引的查找过程并非直接定位到具体行，而是找到包含目标键值的数据页，将其加载到内存后，通过页目录（Page Directory）定位到具体行。

1.2 全文索引

全文索引用于快速检索文本数据中的任意内容，常用于搜索场景。它基于倒排索引（Inverted Index）实现，核心步骤包括：

• 对文本内容进行分词（Tokenization）。
• 构建索引辅助表，记录分词与数据位置的映射关系。
  全文索引在 InnoDB 中从 MySQL 5.6 开始支持，适用于需要高效文本搜索的应用。

1.3 哈希索引

哈希索引通过哈希算法将键值映射到存储位置，具有极高的查找效率（时间复杂度 O(1)）。但在 InnoDB 中：

• 用户无法手动创建哈希索引。
• InnoDB 的自适应哈希索引（Adaptive Hash Index, AHI）会根据查询频率自动优化，将频繁访问的 B+ 树索引转换为哈希索引，类似于 JVM 的 JIT 编译对热点代码的优化。

1.4 索引选择的 Cardinality 值

选择索引列时，应参考 Cardinality 值（基数），即索引列中唯一值的数量：

• Cardinality 值越高，重复值越少，索引的区分度和效率越高。
• 对低基数的列（如性别字段）创建索引效果有限，应优先选择高基数的列（如用户 ID）。

2. 锁

锁是数据库区别于文件系统的重要特性，用于管理共享资源的并发访问。InnoDB 存储引擎主要在行级别实现锁机制。

2.1 锁类型

InnoDB 提供以下标准行级锁：

• 共享锁（S Lock）：允许事务读取某行数据，多个事务可同时持有 S 锁。
• 排他锁（X Lock）：允许事务更新或删除某行数据，独占访问，禁止其他事务加锁。

此外，InnoDB 支持多粒度锁定，允许在表级和行级同时加锁。为此，引入了意向锁（Intention Lock）：

• 意向共享锁（IS Lock）：事务意图在表中某些行加 S 锁。
• 意向排他锁（IX Lock）：事务意图在表中某些行加 X 锁。
  意向锁的作用是提高锁兼容性检测效率。

2.2 一致性非锁定读

一致性非锁定读基于多版本并发控制（MVCC），无需等待 X 锁释放：

• RC 隔离级别（Read Committed）：读取最新的快照数据，可能出现不可重复读。
• RR 隔离级别（Repeatable Read）：读取事务开始时的快照数据，避免不可重复读。

2.3 一致性锁定读

一致性锁定读通过显式加锁实现：

• SELECT ... FOR UPDATE：加 X 锁，锁定行并禁止其他事务读写。
• SELECT ... LOCK IN SHARE MODE：加 S 锁，允许其他事务读取但禁止修改。

2.4 自增计数

InnoDB 的自增计数通过表级别的自增计数器实现。当插入操作完成时，计数器自增并持久化。不同隔离级别下，自增行为可能略有差异，但通常在事务提交后生效。

3. MySQL 锁的分类

MySQL 中的锁分为两类：

• Latch：轻量级锁，由存储引擎内核管理，用于保护内部共享资源（如缓冲池），用户无法控制。
• Lock：用户可控的锁，用于保护数据行、表等资源，分为行锁和表锁。

3.1 Lock 锁的类型

InnoDB 的 Lock 锁包括：

• Record Lock：锁定单行记录。
• Gap Lock：锁定索引记录之间的间隙，防止插入新记录。
• Next-Key Lock：Record Lock 和 Gap Lock 的组合，锁定记录本身及其前方的间隙。

Next-Key Lock 的特点

• 默认在 RR 隔离级别下使用，防止幻读（Phantom Read）。
• 若查询使用聚集索引，可能降级为 Record Lock。
• 对于 INSERT 操作，InnoDB 检查插入位置的下一记录是否被锁定，若被锁定则阻塞插入。

Next-Key Lock 与幻读

疑问解答：RR 模式下读取快照数据，为何仍需 Next-Key Lock 防幻读？
答：MVCC 仅保证读取一致性，但不阻止其他事务插入新数据。Next-Key Lock 通过锁定间隙，确保当前事务扫描范围内无新记录插入，从而彻底解决幻读问题。

Next-Key Lock 与 Previous-Key Locking

特性	Next-Key Lock	Previous-Key Locking
锁定范围	记录本身及其前方间隙	记录本身及其后方间隙
主要目的	防止幻读	解决写偏斜（Write Skew）
应用场景	InnoDB RR 隔离级别	理论机制，少见于实际数据库
方向	向前锁定	向后锁定

Previous-Key Locking 示例：在会议室预定场景中，两个事务检查空位并同时预定，可能导致超员。Previous-Key Locking 通过锁定前向间隙避免此类写偏斜，但在 InnoDB 中未直接实现。

3.2 阻塞

阻塞由锁的兼容性引起。例如，S 锁与 S 锁兼容，但 S 锁与 X 锁冲突，事务需等待锁释放。InnoDB 默认不回滚阻塞事务，除非超时或死锁。

3.3 死锁

死锁指多个事务相互等待锁资源。InnoDB 通过等待图（Wait-for Graph）检测死锁：

• 若图中存在环路，则发生死锁。
• 解决方法：回滚 Undo 量最小的事务。
  死锁概率公式：n² * r² / (4 * R²)，其中 n 为并发事务数，r 为操作次数，R 为数据行数。大事务（r 过大）易引发死锁。

总结

本文详细介绍了 MySQL InnoDB 的索引和锁机制。索引方面，B+ 树索引提供高效查询支持，全文索引和哈希索引满足特定需求，Cardinality 值指导索引选择。锁方面，InnoDB 通过行级锁、多粒度锁和 MVCC 实现并发控制，Next-Key Lock 有效防止幻读，死锁则通过等待图检测和回滚解决。这些机制共同保障了 MySQL 在高并发场景下的性能与数据一致性。