MySQL自增主键有什么作用？来自大厂的使用经验

超越不平凡

已于 2024-02-29 09:46:40 修改

阅读量1k

点赞数 25

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

于 2023-12-21 11:00:44 首次发布

本文链接：https://blog.csdn.net/qq_39209927/article/details/135124792

版权

MySql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文将介绍两个问题：

MySQL数据库为什么需要自增主键？
自增主键为什么是递增不连续的？

下面分别看下这两个问题

一、MySQL数据库为什么需要自增主键

MySQL常用的InnoDB存储引擎是使用B+Tree来维护索引的，通过B+Tree来维护索引的有序性，在插入新值时需要做必要的维护，以下图为例

如果新增ID列为700，则只需在R5后插入即可。如果新增加的是400，就相对麻烦了，需要逻辑上挪动后面的数据。有更糟糕的情况，如果R5所在的数据页已经满了，根据B+Tree的算法，这时需要申请一个新的数据页，然后挪动部分数据。这个过程叫做页分裂，在这种情况下，性能会受到影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页，整体空间利用率降低大约50%。

当然有分裂就有合并，当相邻两个页由于删除数据，利用率很低之后，会将数据页做合并。合并过程，可以认为是分裂的逆向过程，所以一般情况下都是逻辑删除。

自增主键每次都是追加记录，不涉及挪动数据，也不会触发页分裂。这是自增主键的好处之一。

除了考虑性能，还需要从存储空间的角度来看。假如用其他比较长的字符串做主键，普通索引的叶子节点是主键索引，那势必会占用较大的空间，而如果用自增主键的话，占用的空间相对小很多。这也是自增主键的好处之一。

好了到这里你应该清楚了为什么使用自增主键了吧，关于索引的分类欢迎留言讨论。下面来看下一个问题，为什么自增主键能保证自增，但是不保证连续？

二、自增主键为自增但是非连续

以下表为例

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(11) DEFAULT NULL,
  `id_card` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)，
  UNIQUE KEY （`id_card`）
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这个表里执行insert语句插入数据后，在看表结构会有哪些变化

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(11) DEFAULT NULL,
  `id_card` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)，
  UNIQUE KEY （`id_card`）
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

可以看到，表定义里面出现了一个 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2。

这个输出结果容易引起这样的误解：自增值是保存在表结构定义里的。实际上，表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同。

MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：
- 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。
- 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

自增值的修改机制

如果主键id被定义为AUTO_INCREMENT，在插入一行数据时，会有如下行为：

如果插入数据 id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

新的自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

自增值的修改时机

insert语句的执行流程如下

执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,'张三', 1);
InnoDB 发现用户没有指定自增 id 的值，获取表 user 当前的自增值 2；
将传入的行的值改成 (2,'李四', 1);
将表的自增值改成 3；
继续执行插入数据操作，由于已经存在 id_card=1 的记录，所以报 Duplicate key error，语句返回。

可以看到，这个表的自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键 id_card冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。

这种情况就出现了自增值不连续的情况。唯一键会引起自增值的不连续。

同样的，事务回滚也会引起自增值的不连续，这很好理解，事务回滚后自增值不会跟着回滚，所以事务回滚也是导致自增值不连续的原因，但是是递增的。

为什么在出现唯一键冲突或者回滚的时候，MySQL没有回滚自增主键呢？其实，MySQL这么设计是为了提升性能。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

假设事务 A 申请到了 id=2，事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
事务 B 正确提交了，但事务 A 出现了唯一键冲突。
如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”

而为了解决这个主键冲突，有两种方法：

每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在。
把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

可见，这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首，就是我们假设的这个“允许自增 id 回退”的前提导致的。

因此，InnoDB 放弃了这个设计，语句执行失败也不回退自增 id。也正是因为这样，所以才只保证了自增 id 是递增的，但不保证是连续的。

往期精彩内容推荐：

MySQL数据更新流程原来这么复杂-CSDN博客

MySQL中order by原来是这么工作的-CSDN博客

不止内存优势：Redis高性能背后的神秘面纱，超越想象加速秘诀-CSDN博客