MySQL重大Bug自增主键不是连续递增?

本文深入探讨了MySQL自增主键的保存策略、修改机制和自增值的生成算法,揭示了自增主键不连续递增的原因,包括唯一键冲突、事务回滚和批量插入数据时的锁机制。了解这些,有助于优化数据库设计和提高并发性能。
摘要由CSDN通过智能技术生成

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

创建一个测试表,执行

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: t1
Create 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,在插入一行数据时,自增值的行为如下ÿ

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值