MySQL 行锁、索引理解及常见面试题

在 MySQL 中，索引是提高数据库查询性能的重要手段。根据不同的分类标准，MySQL 的索引可以分为多种类型。
以下是常见的分类方式及其对应的索引类型：

一、按功能/逻辑分类（常见用途）

1、普通索引（Normal Index / INDEX）

最基本的索引类型。
允许重复值和 NULL 值。
仅用于加速查询。

2、唯一索引（Unique Index）

索引列的值必须唯一（允许一个 NULL 值，具体取决于存储引擎和版本）。
可以有多个唯一索引。
常用于约束数据唯一性，如身份证号、邮箱等。

3、主键索引（Primary Key Index）

特殊的唯一索引，不允许重复，也不允许 NULL。
一个表只能有一个主键。
InnoDB 存储引擎中，主键索引即“聚簇索引”（Clustered Index）。

4、外键索引（Foreign Key Index）

用于维护表之间的引用完整性。
外键字段通常需要建立索引（MySQL 自动为外键创建索引）。
主要用于关联表的数据一致性。

5、全文索引（Full-text Index）

用于对文本内容进行关键词搜索（如文章、描述字段）。
支持 MATCH() ... AGAINST() 查询语法。
仅支持 CHAR、VARCHAR、TEXT 类型。
MyISAM 和 InnoDB（MySQL 5.6+）都支持。

6、空间索引（Spatial Index）

用于地理空间数据类型（如 GEOMETRY、POINT 等）。
仅 MyISAM 和 InnoDB（部分支持）支持。
使用 SPATIAL INDEX 创建。

二、按物理存储结构分类

1、聚簇索引（Clustered Index）

数据行的物理存储顺序与索引顺序一致。
InnoDB 表必须有一个聚簇索引（通常是主键）。
聚簇索引的叶子节点存储的是完整的数据行。

2、 非聚簇索引 / 二级索引（Secondary Index / Non-clustered Index）

索引的叶子节点不包含完整的数据行，而是包含主键值（或指向数据行的指针）。
查询时需回表（通过主键查找完整数据）。
普通索引、唯一索引、全文索引等都是二级索引。

三、按索引涉及的列数分类

1、单列索引（Single-column Index）

基于表中的某一列创建的索引。

2、联合索引 / 复合索引（Composite Index / Multi-column Index）

基于多个列创建的索引。
遵循最左前缀原则（Leftmost Prefix Rule）。
例如：INDEX (name, age, city) 可用于查询 name、name + age 等，但不能用于单独 age。

四、其他特殊索引（MySQL 8.0+）

1、前缀索引（Prefix Index）

对字符串列的前 N 个字符建立索引，节省空间。
例如：INDEX (email(10))。

2、降序索引（Descending Index）

MySQL 8.0 开始支持在索引中指定升序/降序。
例如：INDEX (col1 ASC, col2 DESC)。

3、函数索引（Functional Key Parts）

MySQL 8.0.13+ 支持基于表达式创建索引。
例如：INDEX ((UPPER(name)))。

五、索引与锁的关系

1. 行锁什么情况会加上？

在 MySQL 的 InnoDB 引擎中，行锁是在事务执行过程中根据需要加上的，但并不是立即释放，
而是等到事务结束时才释放，这符合两阶段锁协议。具体来说，以下操作会触发行锁：

显式加锁：使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句时，会对查询的行加锁。

隐式加锁：在执行 UPDATE 或 DELETE 操作时，InnoDB 会自动对操作的行加独占锁（X 型锁）。

索引条件与锁的关系：
普通 SELECT：不会加锁，无论是否使用索引。
SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE：
如果查询条件使用了索引（包括主键或唯一索引），会加行锁。
如果查询条件没有使用索引，会加表锁。
因此，普通 SELECT 语句不会因为是否触发索引而加锁，只有在使用 FOR UPDATE 或 LOCK IN SHARE MODE 时，
索引的使用才会影响加锁的类型。

2. 索引与行锁的关系

InnoDB 的行锁是加在索引上的，这意味着：

主键索引：对主键或唯一索引加锁时，行锁会直接加在对应的索引记录上。
普通索引：对普通索引加锁时，行锁会加在索引项上，同时可能触发间隙锁或临键锁。
无索引：如果查询条件没有使用索引，InnoDB 会退化为表锁，锁住整个表。

六、MySQL 的事务隔离级别（Isolation Levels）

SQL 标准定义了四种隔离级别，MySQL 全部支持。

隔离级别	英文名称	能防止的问题	存在的问题	InnoDB 实现方式
读未提交	Read Uncommitted	什么都防不住	脏读、不可重复读、幻读	极少使用
读已提交	Read Committed	脏读	不可重复读、幻读	Oracle 默认；MySQL 可用
可重复读提交	Repeatable Read	脏读、不可重复读、幻读	理论上应存在幻读，但 InnoDB 使用临键锁（Next-Key Lock）解决了它	MySQL 默认
串行化	Serializable	全部问题	无	无

七、间隙锁（Gap Lock） vs 临键锁（Next-Key Lock）

1. 背景：InnoDB 的行锁机制
   InnoDB 使用 行级锁 来提高并发性能，但为了防止 幻读（Phantom Read），引入了更复杂的锁机制 —— 包括记录锁、间隙锁和临键锁。

🔹 记录锁（Record Lock）

锁住某一行记录本身。
例如：SELECT * FROM t WHERE id = 5 FOR UPDATE; → 锁住 id=5 这条记录。

🔹 间隙锁（Gap Lock）

锁住索引之间的“间隙”，不包括记录本身。

目的是防止其他事务在这个“空隙”中插入新数据，避免幻读。
只在 可重复读（REPEATABLE READ）及以上隔离级别 生效。

锁范围：(上一个索引值, 当前索引值]，即：左开右闭区间。
InnoDB 的锁是“向前看，不向后防”——
它通过 当前记录的临键锁 (prev, curr] 来防止“在当前值前面插入”，
但不会主动去锁“当前值后面”的间隙，除非那个间隙属于另一个被锁定的范围。
例如：现有索引值：3, 5, 6, 7, 9
SELECT * FROM test WHERE id BETWEEN 5 AND 7 FOR UPDATE;
具体推导过程如下：
左侧边界：从 id=5 开始，向左扩展到前一个值 id=3，锁定 (3, 5]。
中间部分：覆盖 id=6，锁定 (5, 6]。
右侧边界（扩展部分）：覆盖 id=7，锁定 (6, 7]；同时，为了阻断幻读，必须继续向右锁定 id=7 到下一个索引值 id=9 之间的间隙，即 (7, 9]。

🔹 临键锁（Next-Key Lock）

是 记录锁 + 间隙锁 的组合。
锁住一个左开右闭区间：(gap_start, gap_end]
SELECT * FROM t WHERE id > 5 AND id < 7 FOR UPDATE;
最终形成多个 Next-Key Locks，比如锁定区间 (3,5] 和 (5,7]
作用：既防止修改已有记录，也防止插入新记录，彻底解决幻读。    

锁类型	锁定对象	是否包含记录本身	是否防止插入
记录锁	某个索引记录	是	否
间隙锁	两个索引之间的“间隙”	否	是
临键锁	记录本身 + 前面的间隙	是	是

InnoDB 默认使用 Next-Key Lock 来实现行级并发控制，在 RR 隔离级别下避免幻读。

八、 常见面试题

Q1: 什么是行锁、间隙锁和临键锁？

行锁（Record Lock）：锁定单行记录，通常用于主键或唯一索引。
间隙锁（Gap Lock）：锁定一个索引区间，防止其他事务在区间内插入数据，常用于普通索引或范围查询。
临键锁（Next-Key Lock）：行锁和间隙锁的组合，锁定一个左开右闭的区间，用于解决幻读问题。

Q2: 什么时候会加间隙锁？

在基于普通索引或唯一索引的范围查询中，InnoDB 会自动加间隙锁。
例如，SELECT * FROM test WHERE id BETWEEN 5 AND 7 FOR UPDATE; 会锁定 (3, 9] 区间。

Q3: 如何避免表锁？

确保查询条件使用索引，否则 InnoDB 会退化为表锁。可以通过 EXPLAIN 分析查询是否使用了索引。

Q4: 什么是两阶段锁协议？

两阶段锁协议是指事务在执行过程中逐步加锁，但在事务提交或回滚时才统一释放锁。
这种机制确保了事务的隔离性和一致性。

Q5: 如何查看当前事务加的锁？

在 MySQL 8.0 及以上版本，可以通过 SELECT * FROM performance_schema.data_locks\G; 
查看 InnoDB 为事务加的锁。

Q6: 不可重复读和幻读区别及解决方案？

不可重复读：同一数据行的值被修改或删除。
解决方法：使用行级锁（如SELECT ... FOR UPDATE）或设置隔离级别为REPEATABLE READ（MySQL默认）

幻读：数据行的数量变化（如新增或删除符合条件的行）
解决方法：REPEATABLE READ隔离级别下，使用间隙锁（Gap Lock）或设置隔离级别为SERIALIZABLE

Q7: 是全部解决还是部分解决幻读？哪些不能解决？

结论：InnoDB 在 RR 级别下“基本”解决了幻读，但并非 100% 绝对杜绝。
快照读（普通 SELECT）：通过 MVCC（多版本并发控制）机制，
事务内多次读取看到的是事务开启时的数据快照，完全不会发生幻读。
当前读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE / UPDATE / DELETE）：
通过临键锁（间隙锁）机制，锁住查询范围，阻止其他事务插入，在绝大多数情况下解决了幻读。
不能解决的极端场景（会发生幻读）：
在“先快照读，后当前读”的混合场景下，可能会出现类似幻读的现象。
场景举例：
事务 A 执行普通 SELECT * FROM test WHERE id > 100;（快照读，返回 0 行）。
事务 B 插入一条 id = 101 的记录并提交。
事务 A 再次执行 SELECT * FROM test WHERE id > 100 FOR UPDATE;（当前读）。
为什么发生：因为当前读（FOR UPDATE）会忽略 MVCC 快照，直接读取数据库的最新版本数据，
此时事务 A 会“凭空”看到事务 B 刚插入的这条记录，导致两次读取结果不一致。
规避方法：在开启事务后，第一时间使用 SELECT ... FOR UPDATE 进行占位加锁，
后续再执行相关操作。

Q8: 假设id已有值，3，5，6，7，9。隔离级别为repeatable时临时锁范围对比。

SQL 语句	语义	最终锁定范围
5 < id <= 7	id > 5 AND id <= 7（左开右闭）	(5, 9]
5 <= id <= 7	id >= 5 AND id <= 7（闭区间）	(3, 9]
5 < id < 7	id > 5 AND id < 7（开区间）	(5, 7)

`5 <= id <= 7`效果等同于between 5 and 7
只要 SQL 的查询条件里包含了边界值（比如 id=7），临键锁就会向右“多锁一点”（扩展到下一个索引值）；
如果查询条件不包含边界值（比如 id=5），锁的范围就会从该边界之后开始。

总结

MySQL 的行锁机制是 InnoDB 引擎实现事务隔离性的重要手段。
理解行锁、间隙锁和临键锁的区别，以及索引在加锁中的作用，
是优化数据库性能和解决并发问题的关键。