全面解析 Mysql-InnoDB 索引存储，锁，如何上锁

本文详细介绍了MySQL-InnoDB存储引擎的索引类型，包括聚簇索引和二级索引的工作原理，强调了B+Tree在索引存储中的作用。此外，文章深入探讨了锁机制，如意向锁、记录锁、间隙锁和下一键锁，解释了它们在防止幻读和优化并发性能方面的作用。文章还讨论了不同SQL语句设置的锁类型以及锁表操作，并提供了一个典型的死锁例子。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引

在InnoDB中，根据索引存储的方式，可以分为聚簇索引，以及二级索引。
聚簇索引在一张表中只会存在一个，且必然会存在一个，二级索引则没有限制。

表上有定义主键时，聚簇索引作用在主键上
表上不存在主键，但存在not null的唯一键时，聚簇索引作用在找到的第一个not null的唯一键上
如果没有主键，同时也没有合适的唯一键时，InnoDB会自动生成一个隐藏的聚簇索引 GEN_CLUST_INDEX，可以把它理解为一个6字节的自增主键。

在InnoDB中，索引存储的数据格式是B+Tree。如果是对树结构不熟悉的朋友可以点击这里补习一下这方面的知识，最终的数据存储如下图：

在这里插入图片描述

B+Tree的特点是只在最下层的叶子节点存储数据，因此对于聚簇索引来说，叶子节点上的data存储的就是我们数据库中看到的对应行的数据，而对于二级索引，叶子节点上的data存储的则是聚簇索引的键值。

当我们使用二级索引查询的时候，实际上需要先在该二级索引树中找到对应的索引位置，获取该索引data中存储的聚簇索引键值，然后再回到聚簇索引树中查到对应的数据返回，这里等于要查两次B+Tree。

但假若你查询的字段恰好是你的sql应用到的索引字段的话，那么就无需再查一次聚簇索引，这时候可以直接从二级索引返回数据，这就是sql优化中的索引覆盖（需要注意的是，只有快照读以及 select lock in share mode可以使用索引覆盖，for update必然会回表检查聚簇索引）。

如果你的查询中没有应用到索引的话，那当然就只能对聚簇索引的所有叶子节点逐个进行匹配数据行返回咯。

在Mysql的学习中，我认为对于B+Tree的认识是十分重要的一点，你需要在你的脑海中形成B+Tree的存储结构图谱，这对于整个Mysql的学习都有很大的帮助。

二级索引叶子节点的排序，可以理解成order by 二级索引自身，主键索引;

锁

意向锁

InnoDB支持多种粒度锁，允许行锁和表锁共同使用。意向锁是一种表锁，他存在的目的是为了更好地支持非意向表锁和行锁的兼容。意向锁是一种与行级锁兼容的表锁，这意味着任何的行级锁都不会阻止意向锁的锁定。有两种类型的意向锁：

意向共享锁（IS）：每次申请行级共享锁时，会先申请意向共享锁。
意向排他锁（IX）：每次申请行级排他锁时，会先申请意向排他锁。

表级锁的兼容性汇总如下：
在这里插入图片描述
意向锁是不会阻止除全表锁定以外的操作的，而且意向锁都是与自身兼容，可重入的锁。
看到这里可能会疑惑，那么意向锁这个设计的真正意图是什么呢？
设想如下场景：

A事务申请了一个排他行锁（申请排他行锁的同时必然也会申请一个排他意向锁），锁定了一行索引。
B事务这时候需要申请一个排他表锁。

在不考虑意向锁的情况下，B事务只能够遍历所有的数据索引行，才能确认这张表上是否有数据行被加上了行锁，这样的效率显然是十分低的。

现实的情况是，由于有意向锁这个设计，获取表锁的同时只需要检查该表是否被加上了意向锁就可以得知是否有索引行被锁定，而无需遍历所有的行。

记录锁（Record Locks）

记录锁就是我们常说的行锁，他锁定的不是真正意义上的行，而是索引树上的叶子节点。
假如有这样的一条sql：

select * from user where name = 'xxx' for update;

name 是聚簇索引，锁定对应的聚簇索引。
name是二级索引，锁定该二级索引。若触发索引覆盖，则直接返回数据。否则通过叶子节点中存储的聚簇索引key，回到聚簇索引树中，查找数据并且锁定聚簇索引。若name是一个外键索引，那么同时锁定关联表的索引。
name是一个非索引列，锁住所有的聚簇索引。

间隙锁（Gap Locks）

记录锁是InnoDB中一种专为解决幻读而设计的锁。它是一个与所有锁兼容的可重入锁，唯一互斥的是插入操作（注意不是与写锁互斥）。

在我的这篇文章中写过，Innodb的 Repeatable read 事务隔离级别中是不会出现幻读的。在这里，我提及的是由于mvcc版本控制的关系，所以幻读不会出现。但实际上InnoDB中有两种select操作，一种是我们平时写的 select * from table，这种称之为快照读，读取的是mvcc版本控制的快照行。

另一种是select * from table for update，或者select * from table lock in share mode。这两种语句读的是最新的数据，称之为当前读。

为什么说间隙锁是为了防止幻读做出的设计呢？假设某个非唯一索引的叶子节点如下图
在这里插入图片描述
我们知道B+Tree是有顺序的，如果没有间隙锁的话

select * from table where key = 12 for update;

这条语句只会锁定值为12的二级索引。
此时我们再插入一条key = 12的数据，新加入的这条数据的索引是没有被锁住的，于是我在同一个事务中再次执行上面的sql，就会发现本来只能查到一条数据，现在变成了两条，出现了幻影行。

在引入间隙锁这个概念后，我们上面的SQL同时会锁住间隙，防止幻影行的产生。

间隙

间隙就是2个索引之间的空隙。
理清楚间隙的概念是很有必有的。
我们可以认为，间隙锁的间隙就是最靠近被锁定的记录锁的头尾2条记录的范围就是间隙。
如根据上图，记录锁如果是12，那么间隙是他前面的10跟后面的13之间都不可插入记录。
如果记录锁是3，那么间隙就是小于3的无限值到5.
如果记录锁是99，那么间隙就是90到大于99的无限值。
在真正的实现方式上，为了锁住10-13之间的间隙，InnoDB需要生成2个间隙锁，分别是10-12,12-13。

下一键锁（Next-Key Locks）

这个锁是记录锁和间隙锁的组合。
在 Repeatable read 以上的隔离级别中，InnoDB 默认的加锁方式就是这种锁。
他对一个索引加上下一键锁，就等于为这个索引同时加上了记录锁和间隙锁。

插入意向锁

插入意向锁是在插入数据时使用的一种可重入间隙锁，当插入时遇到下一键锁就会申请加上插入意向锁。
两个插入操作如果插入的是同一个间隙，但是不同行的话，不会堵塞。
它的作用是在插入时阻塞线程等待被唤醒，同时提高插入效率。

InnoDB中不同SQL设置的锁

select * from table 默认是快照读，不加锁。
select * from table for update 或者 select * from table for share mode
- 使用到聚簇索引或者唯一索引，使用记录锁锁住对应的索引。
- 使用到二级索引，用下一键锁锁住对应的二级索引，若无索引覆盖，则同时用记录锁锁住对应的聚簇索引。若是外键索引，同时使用共享锁锁定外键关联表的对应索引。
- 没有使用到索引，使用下一键锁锁住所有的聚簇索引。
update 语句中 where 部分与上面的 select 语句的所使用的锁是一致的。
update 语句中的 set 部分
- 如果 set 包含聚簇索引，则被修改行的所有索引都会加上记录锁。
- 如果set包含索引，则分别会为新，旧索引分别加上记录锁。
insert语句，在插入前会请求插入意向锁，并在插入后的数据行的所有索引加上记录锁。如果检测到唯一索引冲突，那么会报错并且为该唯一索引加上一个读的记录锁。如果获取聚簇索引或者唯一键上的锁有冲突时，将首先请求该索引上的共享锁。
delete 语句，如果搜索条件是唯一索引则对该行的索引使用记录锁，否则使用下一键锁。

一个典型的死锁例子：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

事务1：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

事务2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

事务3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

事务：

COMMIT;

由于事务一获取到了该行的排他锁，于是事务2,3插入的时候检测到有冲突，并同时向该行上请求共享锁。
在事务1提交后，事务2,3同时获得共享锁，又同时请求该行的排他锁。由于对方已经持有了共享锁，于是谁也无法获得排他锁，造成死锁。

锁表

首先需要清楚，alter table等操作产生的是表锁，而在Innodb中当你执行的SQL没有使用到索引列时，产生的是锁表。

同时明确一个概念，锁表锁的是什么，如果锁表使用的不是表锁，那么是使用什么锁来完成的呢？
答案是当锁表时，是用的是下一键锁来锁定全部的聚簇索引。

以下面这张表来作实例，其中id是主键，age是普通索引,name上无索引
在这里插入图片描述

select * from table where name = 1 for update

假设执行了上面的语句，这会造成锁表。

此时任何的插入更新语句都会阻塞，原因是作为聚簇索引的id列被使用记录锁和间隙锁完全锁住了。
普通的select语句仍然可以执行，由于执行的是快照读，不牵扯到锁的处理。
select age,id from table where age = 1 lock in share mode; 这样的施加共享锁的语句可以执行，这是由于锁表是锁住聚簇索引，但是age是一个普通索引，而且查询列id和age都存储了索引树的叶子节点中，不需要回表查询聚簇索引。
select name from table where age = 1 lock in share mode;这样的语句会被阻塞住，由于name列不存储在age索引的节点中，所以需要回到聚簇索引中搜索，但是整个聚簇索引都已经被锁住了。
select age,id from table where age = 1 for update; 任何需要施加排他锁的语句都必然要回到聚簇索引中检索，无法使用索引覆盖，因此会阻塞。

锁表的开销实际上比我们想的要更大一些。
就上图来说，当锁表时，当锁表时，会生成3个记录锁，以及4个间隙锁。
当数据量持续增加时，这样的空间开销对于数据库来说也是一个不小的负担，我们始终应该关注我们的sql，防止锁表情况的发生。