MySQL数据空洞

有时候删除了一张表一般的数据，但是查看表占用空间发现并没有发生变化。介是为嘛？

InnoDB的底层是B+树，B+树的叶子节点存储数据，因为磁盘预读原理，一般叶子节点的大小会和page的大小保持一致。

当我们删除了数据后，并没有把申请的空间还给 *** 作系统，而是将当前空间标记为“可复用”，这样当新数据插入时，就不同重新再申请空间，可以直接将数据插入到“可复用”的空间中；

这样“可复用”的空间，就称为数据空洞。

当向一个page已满的叶子节点插入数据时，为了保证数据均衡，会把所有的数据再进行一次分配，使得两个节点的数据量不会相差太多，但也就会造成有很多的“可复用”空间。

这是今天要到的一个问题，由朋友 杨长江 提交给我，版本5.7.17。如下：

已知的一个BUG是skip-slave-error参数设置问题，而且这个BUG再8.0.25依旧存在，参考如下：

而拿到朋友的参数文件后，发现并没有设置skip-slave-error参数，那么是其他什么BUG导致的呢？实际上这个BUG是5.7.23以下的版本，并且设置了replicate_wild_do_table等过滤规则会后对CREATE DATABASE/ALTER DATABASE/DROP DATABASE会过滤掉 *** 作，并且从库的GTID也会被抛弃掉，这样就产生了大量的空洞。

稍微浏览修改，如BUG描述增加对database *** 作的判定。

如果使用较老的版本应该注意这个奇特的问题。

* G r e a t S Q L 社 区 原 创 内 容 未 经 授 权 不 得 随 意 使 用 ， 转 载 请 联 系 小 编 并 注 明 来 源 。

本文在测试 insert、insert ignore、replace into三种数据插入方式的时候，发现插入数据的时候在表内存在带有“唯一特性”的值重复的情况下三种语句的处理方式。最终发现了MySQL主键自增值“空洞”了

测试场景为MySQL 8.0：

1、建表，包含主键及唯一约束

2、写入初始测试数据

insert方式插入数据在处理过程中发生主键传统等错误时候，语句会被终止，并告知错误的原因。而使用insert ignore的方式进行数据插入，则会忽略插入错误的行继续插入没有问题的行记录，最终以warning进行提示。

在测试过程中惊奇地发现测试表中的主键自增列发生了改变，经过之前的 *** 作已经变成了7：

最后，replace into的方式导致如果插入数据是原值的情况，然后主键冲突，就对该主键的内容进行替换，如果唯一键冲突，唯一值所在行就会删除，重新插入新的行，如果都不冲突则正常插入数据。

上文测试了三种插入数据的方式，可是测试过程中发现插入失败的时候，自增列的自增值居然变大了。

为了更好地理解，首先让我们具体认识一下 AUTO_INCREMENT属性在不同的存储引擎当中，其自增值的保存策略有所不同：

可是理解了这个并不能马上理解现在的这个问题，我们知道当数据进行数据插入的时候，如果插入的数据中自增列不指定其值的时候，该列就会以当前自增值作为其值，如果指定其值就会插入指定的值，当然也有满足唯一的原则，同时插入指定值大于自增值时，自增值也会随之改变。而自增值使用的算法是以 auto_increment_offset参数决定开始，以auto_increment_increment决定步长来实现的,默认情况都是1：

那么，为什么会出现插入数据未成功，自增值却变大了的情况呢？原因很简单，用插入数据的流程来进行分析：

因为自增值的保存是在插入数据真正执行前完成的，因此就会出现这种问题了。

这个时候有人就会想了，可以把 AUTO_INCREMENT值改回去吗？简单测试一下：

显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值“卡”在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里id=4和id=6之间差了个5的值，将自增值改回5，当插入数据时，自增值就会插入5的值并且把自增值加1，问题就出现了，此时自增值再进行插入就违背了唯一的原则了

在生产环境中还存在很多类似的问题，如：

在插入过程中，开启了一个事务，在插入的时候发生了事务的回滚，当回滚后再次插入数据，发现自增值又出现了“空洞”，那么问题又来了，为什么在插入数据的时候发生了回滚，数据回滚了，自增值却没有回滚呢？为了更直观，继续测试，假设有两个事务。

测试前数据：

进行测试：

测试后数据：

发现还是“空洞”了，而且此时答案也十分清楚了，在不同事务在进行写入 *** 作的时候申请自增值，为了避免两个事务申请到相同的自增值，所以需要对其加锁，按照一定顺序进行申请自增值。根据前面的例子来看：

此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值“卡”在中间的情况了，以后有机会再继续聊聊自增锁的问题。

En j o y G r e a t S Q L : )

《深 入 浅 出 M G R 》 视 频 课 程

戳 此 小 程 序 即 可 直 达 B 站

https://www.bilibi l i . c om/medialist/play/136385008 2? business=space_collection&business_id=343928&desc=0

文 章 推 荐 ：

G r e a t S Q L 是 由 万 里 数 据 库 维 护 的 M y S Q L 分 支 ， 专 注 于 提 升 M G R 可 靠 性 及 性 能 ， 支 持 I n n o D B 并 行 查 询 特 性 ， 是 适 用 于 金 融 级 应 用 的 M y S Q L 分 支 版 本 。

G i t e e :

h t t p s : / / g i t e e . c o m / G r e a t S Q L / G r e a t S Q L

G i t H u b :

h t t p s : / / g i t h u b . c o m / G r e a t S Q L / G r e a t S Q L

h t t p s : / / s p a c e . b i l i b i l i . c o m / 1 3 6 3 8 5 0 0 8 2 / v i d e o

微 信 &Q Q 群 ：

可 扫 码 添 加 G r e a t S Q L 社 区 助 手 微 信 好 友 ， 发 送 验 证 信 息 “ 加 群 ” 加 入 G r e a t S Q L / M G R 交 流 微 信 群 ， 亦 可 直 接 扫 码 加 入 G r e a t S Q L / M G R 交 流 Q Q 群 。

---