InnoDB的主键自增长是如何保证的

最新推荐文章于 2023-10-17 20:04:34 发布

尼牟

最新推荐文章于 2023-10-17 20:04:34 发布

阅读量374

点赞数

分类专栏： mysql 文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/WbX7342188/article/details/127451370

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

当设置了`自增主键`时，主键的生成可以完全依赖数据库，无需人为干预，在新增数据的时候，我们不设置主键字段的值，数据库就会为我们自动生成一个主键值。

1、自增长计数器：

在InnoDB的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter），当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化
执行如下的语句可以得到计数器的值：
```
select max(auto_inc_col) from t for update;
```

2、自增长与锁（AUTO-INC Locking）：

插入操作会依据这个自增长的计数器值加1赋值给自增长列。这个实现方式称作AUTO-INC Locking；
这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放；
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但是还存在一些性能上的问题：

1) 首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成） 2）其次，对于INSERT...SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞

3、innodb_autoinc_lock_mode参数：

从MySQL 5.1.22开始，InnoDB提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能
并且从该版本开始，InnoDB存储引擎提供了此参数来控制自增长的模式。该参数的默认值为1。
```
show VARIABLES like 'innodb_autoinc_lock_mode';
```
介绍自增长实现方式之前，需要对自增长的插入类型进行分类，如下表所示：
该参数共有3个有效值（0、1、2），具体说明如下：

statement-base replicion （SBR），row-base replication（RBR). 主从复制的方式

4、为什么要有自增主键？

1、避免页分裂

因为自增主键可以让主键索引保持递增顺序插入，因此避免了页分裂，索引会更加紧凑。而如果是用业务逻辑的字段做主键，则往往不容易保证有序递增插入，就会造成页分裂，导致写数据成本相对较高。

为何递增插入就可避免页分裂？
因为递增插入的记录之间没有空隙，记录自增主键索引的一个页面满了，就申请另外一个页面，接着从左边开始写数据。
所以不会存在以下情况：当数据页已经满了，此时来了一个新记录，需要插入到数据页中间有缝隙的两个数据之间，造成页分裂。

2、更省内存

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个二级索引都包含主键值，所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，数据页能存的索引就越多。 自增主键比身份证号占用空间小。二级索引占用空间也会相应小一些。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

那有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如有些业务的场景需求是这样的：只有一个索引且该索引是唯一索引。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

5、自增主键为什么只递增不连续？

原因一：插入失败

假设，表 t 里面已经有了 (1,1,1) 这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1);

这个语句的执行流程就是：

执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；
将插入行的值改成 (2,1,1);
将表的自增值改成 3；
继续执行插入数据操作，但是由于某些原因导致插入数据库失败。

可以看到，这个表的自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候失败，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。

原因二：事务回滚

例如：

insert into t values(null,2,2);// 插入的行是 (1,2,2)
begin;
insert into t values(null,2,2); // 插入的行是 (2,2,2)
rollback;  //回滚导致1和2都没有了
insert into t values(null,2,2); // 插入的行是 (3,2,2) ，所以id从3开始不连续了

为什么在出现唯一键冲突或者回滚的时候，MySQL 没有把表 t 的自增值回退呢？——为了性能

因为如果可以回退，则会造成主键冲突，如果要解决主键冲突就又会降低性能。为了保证性能，InnoDB 选择了语句执行失败时不回退自增 id。所以才只保证了自增 id 是递增的，但不保证是连续的。