概述

在 MySQL 中，自增主键是一种特殊的数据类型，它提供了一种在插入新记录时自动生成唯一标识符的方法。自增主键通常用于充当数据库表的唯一标识符，确保每条记录都有一个不同的 ID。 自增主键的实现并非像想象中那么简单。本文将深入探究自增主键的属性、修改机制以及影响其连续性的因素。

自增值的属性特征

1. 自增主键值存储位置 在 MySQL 5.7 及之前版本中，自增值存储在内存中。每次重新启动后，第一次打开表时，MySQL 会查找自增值的最大值 (max(id))，然后将 max(id)+1 作为该表的当前自增值。 在 MySQL 8.0 及更高版本中，自增值的更改会记录在 redo log 中。在重新启动时，MySQL 会依靠 redo log 恢复重新启动之前的值。 2. 自增主键值的修改机制 在表中定义主键 id 为自增值后，在插入新行时，自增值的修改机制如下： 1. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么 MySQL 会将该表的当前自增值填写到自增字段中。 2. 如果插入数据时 id 字段指定了具体的值，MySQL 会直接使用语句中指定的值。 根据要插入的值和当前自增值的大小关系，自增值的更改结果也不同。假设要插入的值为 X，当前自增值为 Y： 1. 如果 X < Y，则该表的自增值保持不变。 2. 如果 X ≥ Y，则 MySQL 需要将当前自增值修改为新的自增值。

新增语句对自增主键的影响

让我们观察一下以下 SQL 语句是如何影响自增主键的： sql INSERT INTO t VALUES (NULL, 1, 1); 流程图如下： 流程步骤： 1. AUTO_INCREMENT = 1（表示下次插入数据时，如果需要自动生成自增值，将生成 id = 1。） 2. INSERT INTO t VALUES (NULL, 1, 1)（执行器调用 InnoDB 引擎接口写入一行，传入的值为 (0, 1, 1)） 3. getAUTO_INCREMENT = 1（InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 1） 4. AUTO_INCREMENT = 2 INSERT INTO t VALUES (1, 1, 1)（将传入行的值更改为 (1, 1, 1)，并将自增值更改为 2） 5. INSERT (1，1，1) 执行插入操作，至此流程结束。 从流程中可以看出，MySQL 先将自增值 +1，然后再执行新增语句。需要注意的是，此过程不是原子操作。如果 SQL 语句执行失败，则自增值可能无法连续。

自增主键值不连续的情况

1. 唯一主键冲突 当执行以下 SQL 语句时： sql INSERT INTO t VALUES (NULL, 1, 1); 第一次插入可能会成功，因为自增值的修改机制如下： 如果插入数据时 id 字段指定为 0、null 或未指定值，那么 MySQL 会将该表的当前自增值填写到自增字段中。 但是，当再次执行以下 SQL 语句时，可能会出现错误： sql INSERT INTO t VALUES (2, 1, 1); 因为表 t 中的 c 字段是唯一索引，因此会出现 Duplicatekeyerror 错误，导致新增失败。 错误流程步骤： 1. AUTO_INCREMENT = 2（表示下次插入数据时，如果需要自动生成自增值，将生成 id = 2。） 2. INSERT INTO t VALUES (NULL, 1, 1)（执行器调用 InnoDB 引擎接口写入一行，传入的值为 (0, 1, 1)） 3. getAUTO_INCREMENT = 2（InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2） 4. AUTO_INCREMENT = 3 INSERT INTO t VALUES (2, 1, 1)（将传入行的值更改为 (2, 1, 1)，并将自增值更改为 3） 5. INSERT (2，1，1) 执行插入操作，由于已经存在 c = 1 的记录，所以报错 Duplicatekeyerror，语句返回。 可以看到，即使插入操作失败，自增值仍然会被修改，从而导致自增主键不连续。

结论

在 MySQL 中使用自增主键时，了解其属性、修改机制以及影响其连续性的因素非常重要。通过理解这些机制，可以避免自增主键不连续，并确保数据库表的完整性和准确性。 为了确保自增主键的连续性，建议采用以下最佳实践： 仅在需要时使用自增主键。 避免在自增主键字段上使用显式值。 定期运行 OPTIMIZE TABLE 语句来回收未使用的自增值。 了解 redo log 和崩溃恢复如何影响自增主键。

---

mysql的设置主键自增

列的自增属性，一般用来设置整数列根据一定步长逐步增长的值，类似于其他数据库的序列。 不过这里的“序列”是基于特定一张表的。 关于自增属性的相关特性如下：1. 控制自增属性性能的变量：innodb_autoinc_lock_modeinnodb_autoinc_lock_mode=0代表传统模式，也就是说，在对有自增属性的字段插入记录时，会持续持有一个表级别的自增锁，直到语句执行结束为止。 比如说下面两条语句，SQL 1 在执行期间，一直持有对表 f1 的表级自增锁，接下来 SQL 2 执行时锁超时。 innodb_autoinc_lock_mode=1代表连续模式，和传统模式差不多，不同的点在于对于简单的插入语句，比如 SQL 2，只在分配新的 ID 过程中持有一个轻量级的互斥锁（线程级别，而不是事务级别），而不是直到语句结束才释放的表锁。 代表交错模式。 这个模式下放弃了自增表锁，产生的值会不连续。 不过这是性能最高的模式，多条插入语句可以并发执行。 MySQL 8.0 默认就是交错模式。 那针对复制安全来说，以上三种模式，0 和 1 对语句级别安全，也就是产生的二进制日志复制到任何其他机器都数据都一致；2 对于语句级别不安全；三种模式对二进制日志格式为行的都安全。 2. 控制自增属性的步长以及偏移量一般用在主主复制架构或者多源复制架构里，主动规避主键冲突。 auto_increment_increment 控制步长auto_increment_offset 控制偏移量3. 对于要立刻获取插入值的需求就是说要获取一张表任何时候的最大值，应该时刻执行以下 SQL 3 ，而不是 SQL 2。 SQL 2 里的函数 last_insert_id() 只获取上一条语句最开始的 ID，只适合简单的 INSERT。 4. 自增列溢出现象自增属性的列如果到了此列数据类型的最大值，会发生值溢出。 比如变更表 f1 的自增属性列为 tinyint。

mysql自增序列过大性能影响

自增列可能会成为一个性能瓶颈。 当innodb_autoinc_lock_mode是0时，一个自增列会在一个插入语句执行期间被一直锁住，这样它就成为一个严重的性。