MySQL自动递增遇见删除过就不是按照顺序进去递增

发布时间: 2023-11-21 12:27 阅读： 文章来源：1MUMB3657PS

很多低级开发工程师都想当然觉得自增主键是严格连续递增的，但事实真的如此吗？

创建一个测试表，执行

show create table

SHOW CREATE TABLE tbl_name：显示创建指定命名表的 CREATE TABLE 语句。要使用此语句，必须对该表具有一定的权限。此语句也适用于视图。

更改表的存储引擎时，不适用于新存储引擎的表选项会保留在表定义，以便在必要时将具有先前定义选项的表恢复到原始存储引擎。例如，将存储引擎从 InnoDB 更改为 MyISAM 时，将保留 InnoDB 特定的选项，例如 ROW_FORMAT=COMPACT。

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPACT ENGINE=InnoDB;mysql> ALTER TABLE t1 ENGINE=MyISAM;mysql> SHOW CREATE TABLE t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`c1` int(11) NOT NULL,PRIMARY KEY (`c1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

创建禁用严格模式的表时，若不支持指定的行格式，则使用存储引擎的默认行格式。表的实际行格式在 Row_format 列中报告，以响应SHOW TABLE STATUS。 SHOW CREATE TABLE 显示在 CREATE TABLE 语句中指定的行格式。

AUTO_INCREMENT=2，表示下一次插入数据时，若需要自动生成自增值，会生成id=2。

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

自增值的保存策略MyISAM

自增值保存在数据文件中。

InnoDB

自增值保存在内存，MySQL 8.0后，才有了“自增值持久化”能力，即才实现了“若重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：

5.7，自增值保存在内存，无持久化。每次重启后，第一次打开表时，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。

若一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时，我们删除id=10的行，AUTO_INCREMENT还是11。但若马上重启实例，重启后，该表的AUTO_INCREMENT就会变成10。

即MySQL重启可能会修改一个表的AUTO_INCREMENT值。

MySQL 8.0将自增值的变更记录在redo log，重启时依靠redo log恢复重启之前的值。

理解了MySQL对自增值的保存策略以后，我们再看看自增值修改机制。

自增值的修改策略

若字段id被定义为AUTO_INCREMENT，在插入一行数据时，自增值的行为如下：

若插入数据时id字段指定为0、null 或未指定值，则把该表当前AUTO_INCREMENT值填到自增字段若插入数据时id字段指定了具体值，则使用语句里指定值

根据要插入的值和当前自增值大小关系，假设要插入值X，而当前自增值Y，若：

X ｉｎｓｅｒｔ into t2(c,d) ｓｅｌｅｃｔ c,d from t;Query OK, 4 rows affected, 1 warning (0.01 sec)Records: 4Duplicates: 0Warnings: 1mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message|+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note| 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

由于两个session同时执行插入数据命令，所以binlog里对表t2的更新日志只有两种情况：要么先记session1，要么先记session2。

但无论哪种，这个binlog拿去从库执行或用来恢复临时实例，备库和临时实例里面，session2这个语句执行出来，生成的结果里，id都是连续的。 此时该库就发生了数据不一致。

因为原库session2的ｉｎｓｅｒｔ语句，生成的id不连续。这个不连续的id，用statement格式的binlog来串行执行，是执行不出来的。

要解决该问题，有如下方案：

让原库的批量插入数据语句，固定生成连续id值

所以，自增锁直到语句执行结束才释放，就是为了达此目的

在binlog里把插入数据的操作都如实记录进来，到备库执行时，不依赖自增主键去生成

其实就是innodb_autoinc_lock_mode=2，同时binlog_format=row。

所以生产上有ｉｎｓｅｒｔ … ｓｅｌｅｃｔ这种批量插入场景时，从并发插入的性能考虑，推荐设置：innodb_autoinc_lock_mode=2 && binlog_format=row，既能提升并发性，又不会出现数据一致性问题。

这里的“批量插入数据”，包含如下语句类型：

ｉｎｓｅｒｔ … ｓｅｌｅｃｔｒｅｐｌａｃｅ … ｓｅｌｅｃｔload data

在普通ｉｎｓｅｒｔ语句包含多个value值的场景，即使innodb_autoinc_lock_mode=1，也不会等语句执行完成才释放锁。因为这类语句在申请自增id时，可以精确计算出需要多少个id，然后一次性申请，申请完成后锁即可释放。

即批量插入数据的语句，之所以需要这么设置，是因为“不知道要预先申请多少个id”。

既然不知道要申请多少个自增id，那么最简单的就是需要一个时申请一个。但若一个ｓｅｌｅｃｔ … ｉｎｓｅｒｔ要插入10万行数据，就要申请10w次，速度慢还影响并发插入性能。

因此，对于批量插入数据语句，MySQL提供了批量申请自增id的策略：

语句执行过程中，第一次申请自增id，会分配1个1个用完以后，这个语句第二次申请自增id，会分配2个2个用完以后，还是这个语句，第三次申请自增id，会分配4个

依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。

看案例：

mysql> create table t2 like t;mysql> ｉｎｓｅｒｔ into t2(c,d) ｓｅｌｅｃｔ c,d from t;Query OK, 4 rows affected, 1 warning (0.00 sec)Records: 4Duplicates: 0Warnings: 1mysql> ｉｎｓｅｒｔ into t2 values(null, 5,5);Query OK, 1 row affected (0.00 sec)mysql> ｓｅｌｅｃｔ * from t2;+----+------+------+| id | c| d|+----+------+------+|1 |1 |1 ||2 |2 |2 ||3 |3 |3 ||4 |4 |4 ||8 |5 |5 |+----+------+------+5 rows in set (0.00 sec)

ｉｎｓｅｒｔ…ｓｅｌｅｃｔ实际上往t2中插入4行数据。但这四行数据是分三次申请的自增id，第一次申请到id=1，第二次id=2和id=3， 第三次id=4到id=7。由于该语句实际只用上了4个id，所以id=5到id=7就被浪费了。之后，再执行

ｉｎｓｅｒｔ into t2 values(null, 5,5)

实际上插入的数据是（8,5,5)。这是主键自增id不连续的三大原因。

作者：JavaEdge.

原文链接：https://blog.csdn.net/qq_33589510/article/details/117806343?spm=1001.2014.3001.5501

•••展开全文
上一篇：mysql内存表和redis 下一篇：mysql内存表支持事务吗
相关文章
热门文档