MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43206 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

39 | 自增主键为什么不是连续的?

林晓斌 2019-02-11
第 4 篇文章中,我们提到过自增主键,由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑。
之前我见过有的业务设计依赖于自增主键的连续性,也就是说,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不能保证连续递增。
今天这篇文章,我们就来说说这个问题,看看什么情况下自增主键会出现 “空洞”?
为了便于说明,我们创建一个表 t,其中 id 是自增主键字段、c 是唯一索引。
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;

自增值保存在哪儿?

在这个空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据,再执行 show create table 命令,就可以看到如下图所示的结果:
图 1 自动生成的 AUTO_INCREMENT 值
可以看到,表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。
其实,这个输出结果容易引起这样的误解:自增值是保存在表结构定义里的。实际上,表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值。
不同的引擎对于自增值的保存策略不同。
MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是:
在 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 恢复重启之前的值。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(29)

  • 长杰
    在最后一个例子中,执行 insert into t2(c,d) select c,d from t; 这个语句的时候,如果隔离级别是可重复读(repeatable read),binlog_format=statement。这个语句会对表 t 的所有记录和间隙加锁。
    你觉得为什么需要这么做呢?
    假如原库不对t表所有记录和间隙加锁,如果有其他事物新增数据并先与这个批量操作提交,由于事物的隔离级别是可重复读,t2是看不到新增的数据的。但是记录的binlog是statement格式,备库或基于binlog恢复的临时库,t2会看到新增的数据,出现数据不一致的情况。

    作者回复: 👍 这是一个典型的场景

    2019-02-11
    19
  • 帽子掉了
    老师您好,我有一个时序问题,想请教一下。
    从这篇文章的介绍来看,获取自增id和写binlog是有先后顺序的。
    那么在binlog为statement的情况下。
    语句A先获取id=1,然后B获取id=2,接着B提交,写binlog,再A写binlog。
    这个时候如果binlog重放,是不是会发生B的id为1,而A的id为2的不一致的情况?

    作者回复: 好问题,不会
    因为binlog在记录这种带自增值的语句之前,会在前面多一句,用于指定“接下来这个语句要需要的 自增ID值是多少”,而这个值,是在主库上这一行插入成功后对应的自增值,所以是一致的

    2019-02-13
    3
    9
  • Nomius
    不知道老师还关不关注.
    (1)问一下为什么一张表上面只能有一个自增的字段? (这个大概能从文章中分析出来,因为autoincrement是定义在表结构中,如果有多个的话实现自增的时候逻辑太复杂了)
    (2)为什么自增的字段上面必须要有索引?

    作者回复: 1. 是的
    2. 我觉得最初的一个原因是,由于以前(8.0版本前)自增主键值是不持久化的,只放在内存里面。每次重启后,重新打开表时,需要计算“自增字段里面的最大值”,然后加1,作为当前的autoincrement的值。
    如果没有索引,算这个值就要做全表扫描,性能可能很差,影响访问表的速度。
    好问题。不过这个只是我个人猜测,也可能还有别的原因。😆

    2019-06-05
    8
  • aliang
    老师,我们这边有的开发不喜欢用mysql自带的主键自增功能,而是在程序中控制主键(时间+业务+机器+序列,bigint类型,实际长度有17位,其中序列保存在内存中,每次递增,主键值不连续)。理由是
    (1)通过这样的主键可以直接定位数据,减少索引(2)如果自增,必须先存数据得到主键才可继续下面的程序,如果自己计算主键,可以在入库前进行异步处理
    (3)a表要insert得到主键,然后处理b表,然后根据条件还要update a表。如果程序自己控制,就不用先insert a表,数据可以在内存中,直到最后一次提交。(对于a表,本来是insert+update,最后只是一条insert,少一次数据库操作)
    我想请问的是:
    (1)针对理由1,是否可以用组合索引替代?
    (2)针对理由2,是否mysql自身的主键自增分配逻辑就已经能实现了?
    (3)针对理由3,主键更长意味着更大的索引(主键索引和普通索引),你觉得怎样做会更好呢

    作者回复: “(时间+业务+机器+序列,bigint类型,实际长度有17位,其中序列保存在内存中,每次递增,主键值不连续)。” ----bigint就是8位,这个你需要确定一下。如果是8位的还好,如果是17位的字符串,就比较耗费空间;

    (1)如果“序列”是递增的,还是不能直接用来体现业务逻辑吧? 创建有业务意义的字段索引估计还是省不了的 ?
    (2)mysql确实做不到“插入之前就先算好接下来的id是多少”,一般都是insert执行完成后,再执行select last_insert_id
     (3) 先insert a再update b再update a,确实看上去比较奇怪,不过感觉这个逻辑应该是可以优化的,不应该作为“主键选择”的一个依据。你可否脱敏一下,把模拟的表结构和业务逻辑说下,看看是不是可以优化的。

    总之,按照你说的“时间+业务+机器+序列”这种模式,有点像用uuid,主要的问题还是,如果这个表的索引多,占用的空间比较大

    2019-02-12
    1
    6
  • godtrue
    最喜欢这样的文章,以为比较简单和熟悉,也能打开一扇窗,让人看到一个不同的世界,并且无比丰富多彩。
    在什么场景下自增主键可能不连续?
    1:唯一键冲突
    2:事务回滚
    3:自增主键的批量申请
    深层次原因是,不判断自增主键是否已存在和减少加锁的时间范围和粒度->为了更高的性能->自增主键不能回退->自增主键不连续
    自增主键是怎么做的唯一性的?
    自增值加1,自增锁控制并发
    自增主键的生成性能如何?
    这个需要测试一下,数据库的自增主键也用做生成唯一数字,作为其他单号,比如:并发量小的订单号,性能可能一般。
    自增主键有最大值嘛?如果有,到了咋弄?
    最大值应该有,因为数字总有个范围,到了当做字符串的一部分,然后再自增拼接上另一部分,貌似也可以。
    自增主键的作用?保存机制?修改机制?
    作用:让主键索引尽量地保持递增顺序插入,避免页分裂,使索引更紧凑。
    保存机制:不同的存储引擎不一样。
    MyISAM 引擎的自增值保存在数据文件中。
    InnoDB 引擎的自增值,先是保存在了内存里,到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,放在了redolog里。
    修改机制:
    在 MySQL 里面,如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下:
    1:如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
    2:如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。

    根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是 X,当前的自增值是 Y。
    1:如果 X<Y,那么这个表的自增值不变;
    2:如果 X≥Y,就需要把当前自增值修改为新的自增值。

    作者回复: 👍

    2019-08-07
    5
  • 进阶的码农
    课后题
    在最后一个例子中,执行 insert into t2(c,d) select c,d from t; 这个语句的时候,如果隔离级别是可重复读(repeatable read),binlog_format=statement会加记录锁和间隙锁。啥我的binlog_format=row也加锁了
    2019-03-12
    3
  • 进阶的码农
    上期问题解答,有点疑问
    set sql_log_bin=off;
    alter table tbl_name engine=innodb;

    为什么备库需要执行set sql_log_bin=off这一句
    把表的引擎改成innodb不就能解决重启后内存表被删除的问题吗?
    2019-03-12
    2
  • Ryoma
    在8.0.3版本后,innodb_autoinc_lock_mode默认值已是2,在binlog_format默认值为row的前提下,想来也是为了增加并发。

    https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode

    作者回复: 👍 大势所趋😆

    2019-02-14
    2
  • 涛哥
    老师,能如果两个事务同时并发插入,主键没有指明的话,加锁的情况能说明下吗

    作者回复: 是说自增主键没指定?

    两个语句分别去申请自增主键,申请到的值是不一样的,所以并不冲突

    2019-04-21
    1
  • hetiu
    老师,请问下innodb_autoinc_lock_mode配置是库级别的还是实例级别的?

    作者回复: 全局的

    2019-03-05
    1
  • AstonPutting
    老师,innodb_autoinc_lock_mode = 2,binlog_format = statement 不也会出现数据不一致的问题吗?不是很理解 binlog_format = statement 的情况下,1 与 2 的区别。

    作者回复: innodb_autoinc_lock_mode = 2的时候就要binlog_format = row才好

    2019-02-21
    1
  • 悟空
    赶上了进度,把春节期间的补回来了

    作者回复: 👍

    2019-02-12
    1
  • we
    insert into t values(null,1,1);
    begin;
    insert into t values(null,2,2);
    rolllack;
    insert into t values(null,2,2);
    // 插入的行是 (3,2,2)

    老师 里面是 rollback 吧

    作者回复: 是的,我手残了。。

    多谢指出,发起勘误了哈

    2019-02-12
    1
  • Jacin
    自增id,能保证时间顺序上先insert的id更小吗?
    我遇到Oracle数据库序列的问题,序列有个order选项来保证顺序。应该是出于并发性能考虑。不知道MySQL是否有类似机制,保证自增id时间上严格顺序?
    2019-12-12
  • 宝仔
    老师这种情况
    insert into t (id,c,d) values (1,1,1),(2,2,2),(3,3,3),(null,4,4);
    或者
    insert into t (id,c,d) values (1,1,1),(null,2,2),(3,3,3),(null,4,4);
    能解释下吗?
    第一种情况自增id变成8了
    第二种情况自增id变成6了
    2019-12-10
  • alioo
    mysql8.0自增id记录到redolog里,redolog是滚动的会不会丢失啊?
    2019-07-26
  • alioo
    binglog是statement的话,记录锁是为了防止t 表在上述sql执行期间产生更新,间隙锁是为了防止t表在上述sql执行期间产生插入操作
    2019-07-26
  • 醉红尘
    😂我错了
    老师我想问的是 在row模式下,使用 replace into 还是会导致主从的自增ID不一致问题,这个有解决方法吗?
    2019-07-18
  • 醉红尘
    老师,请问一下,有什么办法解决存在唯一索引的表的主从自增主键不一致问题吗?
    示例如下:
    1、新建一张带有唯一索引的表并写入测试数据
    CREATE TABLE TTT(ID INT AUTO_INCREMENT PRIMARY KEY,USER_NAME VARCHAR(50),USER_AGE VARCHAR(50),UNIQUE key IDX_NAME(USER_NAME))

    INSERT INTO TTT(USER_NAME,USER_AGE) VALUES('A1','1'),('A2','2'),('A3','3'),('A4','4'),('A5','5'),('A6','6');

    2、唯一键冲突场景
    INSERT INTO TTT(USER_NAME,USER_AGE) VALUES('A1','1'),('A2','2'),('A3','3'),('A4','4'),('A5','5'),('A6','6');
    2019-07-18
  • 李刚
    1、如果插入的时候,是主键冲突了,AUTO_INCREMENT值不会改变。
    2、如果插入的field,类型不匹配,比如定义的是int,结果插入英文字符串,AUTO_INCREMENT值也不会改变。
    帮忙解释下,这2种现象。
    2019-06-19
收起评论
29
返回
顶部