39 | MySQL的id自增主键一定都是连续的？（写的不错～）

最新推荐文章于 2024-09-04 19:57:34 发布

笔仙笔仙

最新推荐文章于 2024-09-04 19:57:34 发布

阅读量627

点赞数

分类专栏： MySQL笔记文章标签： java mysql

原文链接：https://time.geekbang.org/column/article/80531

版权

MySQL笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

一、前言

先上一个表结构，后面例子就是依赖这个表数据


CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

二、正文

2.1.自增主键保存在哪里？

MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值。1）在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。2） MySQL 8.0 版本后，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

2.2.自增主键的自增原理是什么，有没有特殊情况？

主要是由两个参数控制：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。
双M多库下的自增主键值，最好分开设置不同，主偶备基

2.3.什么情况自增主键会出现不连续（举例）？

现有表数据：row=（1，1，1），insert into t values(null, 1, 1)。唯一键冲突后自增主键id值变为了3。
事务回滚也会出现自增id不连续

2.4.自增id不连续可不可以回退？为什么MySQL这么自增方式这么设计（举例说明这么设计的原因）？解决第二个问题的思路是什么？

不可以
从性能考虑。举例：假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。假设事务 A 申请到 id=2，事务 B 申请到 id=3。事务 B 正确提交了，但事务 A 出现了唯一键冲突把自增 id 回退为2，那么现在表里面已经有 id=3 的行，而当前的自增 id 值是 2。其他事务执行就会申请到 id=2，再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。
1）每次申请自增id值时都遍历主键索引拿最大。2）把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。

2.5.MySQL自增锁的优化是什么样的？

MySQL 5.0 版本的时候，自增锁的范围是语句级别。一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放
MySQL 5.1.22 新增参数 innodb_autoinc_lock_mode，总共有三个值：0，1(默认)，2。
y=2的x-1次方增长

2.5.1.参数说明

参数的值0 时，表示采用之前 MySQL 5.0 版本的策略
参数的值1 时：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放
这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁

2.5.2.insert … select 要使用语句级的锁，为什么这个参数的默认值不是 2？举例说明？如何设置insert...select的参数值？

当binlog_format=statement模式下，下面的场景会出现数据一致性问题。因为：事务在记录binlog的时候，是不能被打断的。基于这个前提，如果 binlog_format=statement,从库执行主库的binlog的时候，sessionb对应的语句，执行出来的数据，id是连续的，和主库不一致。
从并发插入数据性能的角度考虑设置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row