MySQL的自增主键为何不连续

原创2021-02-02 08:30·程序猿集锦
  • 什么是连续自增主键？
  • 为什么要自增主键
  • 何时才使用到自增键的自增值
  • 主键的自增值记录在哪里
  • 自增值不连续的原因
    • 唯一索引约束插入数据失败
    • 事务回滚操作
    • 批量插入数据
  • InnoDB的自增值为什么不能回收利用？

什么是连续自增主键？

我们经常理解得像1,2,3,4,5,6...,n这样的主键才是连续自增的主键。

那么如果我的主键值是1,3,5,7...,2n+1这样的值算是连续自增主键吗？同理，如果我的主键值是2,4,6,8...,2n+2这样的值算是连续自增主键吗？

答案是肯定的。上面的情况都是连续自增的主键。

上面的奇数和偶数自增主键的值，也经常在我们的MySQL主主复制的集群中常见。我们在其中一个主机上面设置所有的主键为奇数增长，另外一台主机上面设置所有的主键为偶数增长，这样在主主复制的时候就可以避免主键冲突的情况发生。所以上面的所有情况都算是连续自增的主键。

所谓的连续自增主键是指表中的主键值按照一个指定的规律连续增长。

为什么要自增主键

由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑，当我们在查询的时候，效率也就更高。这就是我们为什么推荐为每一个InnoDB表都创建自增主键的原因所在。

但是下面的这样的情况，在某种程度上我们也可以认为它是自增主键，虽然它在表面上不是像前面我们提到的那种沿着某种规律增加的主键值，但是它们实现的效果和存在的意义和前面我们提到的连续自增主键是一样的效果。

比如：你的表中数据确实不是连续自增的，并且也不是按照某个规律自增。但是后续也没有任何数据插入到哪些空缺的位置上，这样的数据，我们也认为他们的数据页是连续的，也不会因为数据不连续而导致查询效率降低。它也不会产生数据分页和数据的空洞。

下面举例说明这样的情况。

例如：一个表的主键值是这样的：1,2,3,17,29,100,101,102,200,1000,...n

在[3,17]之间，[17,29]之间，[29,100]之间，这些空隙的地方如果不再插入任何数据，这个表的主键索引在物理存储上就不会发生页分裂的情况，也不会有数据空洞。所以我们也是可以认为是连续的。他们的效率和1,2,3,4,5,6...n这样的自增主键的查询效率是没有差别的。

何时才使用到自增键的自增值

当我们向一个包含自增主键的表中插入数据的时候，如果在插入的时候，把自增主键的值设置为0、null或者不配置这个字段的时候，这个表中插入的数据将使用自增值来自动维护这个字段值。

例如如下3种情况：

Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

针对上面的表，如下的插入语句将会使用自增主值来自动生成主键的值。

insert into t(id,a,b) values(0,1,1);
insert into t(id,a,b) values(null,2,2);
insert into t(a,b) values(3,3);

除了使用自增值来填补主键列，我们也可以在插入数据的时候自己指定主键的值，也就是说我们自己来维护我们要插入的数据行的主键，不依赖于自增主键来维护。但是此时我们在自己指定主键值的时候，有如下三种情况。

  1. 我们指定的值，比自增值小。此时自增值不会向后滚动还是原自增值。如果我们指定的小于自增值的主键值，在表中的主键列表中已经存在了，此时插入数据的时候，将会报错，主键冲突duplicate primary key for xxxx。如果我们指定的小于自增值的主键值，在表中的主键列表中不存在，此时插入数据成功。而表的自增值还是原来的自增值，不会向后滚动。
  2. 我们指定的值，等于自增值。此时自增值会向后滚动。我们指定的主键值刚好等于自增主键值，插入数据成功，然后自增值也会向后滚动。
  3. 我们指定的值，比自增值大。我们指定的主键值大于自增值，此时插入数据成功。同时自增值也会滚动到当前我们指定的主键值往后加auto_increment_increment之后的值。这里的auto_increment_increment是自增值每次向后滚动的步长。默认为1。可以自行配置。在我们配置双主复制的时候，会经常修改这个参数的值，让两个主的自增主键的步长错开，避免双主同步的时候主键冲突的问题发生。

主键的自增值记录在哪里

当我们为一个表配置了自增主键之后，我们在插入数据的时候，如果没有提供主键值，那么MySQL会自动的给我们分配一个主键值作为当前插入行的主键。这个自增值是记录在哪里呢？总得有个地方记录着每一个表下一行数据的主键值该是多少吧？那么它究竟存储在哪里呢？

InnoDB存储引擎表和MyISAM存储引擎表的自增值，是存在不同的地方的。

InnoDB是存在内存中而MyISAM是存储在它的数据文件.MYD结尾的文件中。

InnoDB表的自增键的键值，在MySQL重启后，可能会和重启之前的值不同。因为这个值原先是存储在内存中的，MySQL重启之后内存中的这个值就丢失了，在重启之后，它是怎么恢复的呢？重启MySQL之后，MySQL会取每一个innodb表中max(id)的值，然后基于这个值再加1后作为下一条数据的自增值，这样内存中就再次恢复了表的自增主键的值。

基于这种恢复的方式，如果我们在重启MySQL之前，把一个innodb表中的id最大的那行数据给删除了。然后重启MySQL数据库，那MySQL在恢复这个表的自增值的时候，如果基于这个表中max(id)+1来获取的自增值，那就不准确了，因为我们在重启之前，删除了表中最大的id，所以此时这个表的自增值将和我们重启之前的值不一样。比如：原先max(id)=100，再插入新数据的时候是101。此时我们删除了100这一行，此时max(id)=99，那么此时重启后就是99+1=100，那么新插入的数据行就是100了。这和重启之前插入数据的时候使用的是101，是不一样的。就是为什么innodb在MySQL重启之后，表的自增值有可能和重启之前不一样的原因。

但是这个实现方式在MySQL8.0之后就变了，MySQL8.0之后，将自增值记录在redo log日志文件中了。重启后，可以从redo log日志文件中获取重启之前的值。

而MyISAM表中的自增主键，不会出现这个现象。它的值是存储在MyISAM表的数据文件.MYD中。所以，重启后这个值不会不同，因为它的值进行了持久化到磁盘了。

自增值不连续的原因

自增值不连续的原因大概如如下3种情况：

  1. 因为违反唯一约束插入数据失败，但自增值向后滚动。
  2. 因为事务回滚导致数据没有插入，但自增值向后滚动。
  3. 因为批量插入未知行数的数据到表中，申请的自增值没有用完，导致后续再次插入数据后不能使用上次申请的自增值。

下面分别针对每一种情况进行试验举例说明。

唯一索引约束插入数据失败

当我们向一个自增主键的InnoDB表中插入数据的时候，如果因为违反表中定义的唯一索引的唯一约束，而导致插入数据失败。此时表的自增主键的键值是会向后加1滚动的。下次再次插入数据的时候，就不能再使用上次因插入数据失败而滚动生成的键值了，必须使用新滚动生成的键值。

注意：这里的失败不是指普通插入数据失败。例如：列值和列类型不匹配、列值大小或长度超过列的定义范围、列数目和表定义的数目不匹配，这些错误导致插入数据失败的时候，表的自增值不会被滚动加1。所以，我们不能一刀切地认为凡是插入数据失败了，那么这个表中的自增主键的自增值就会向后滚动加1。

下面我们来演示一下具体的示例，开始之前，先看下我使用的环境，因为我是基于之前的主主复制集群环境来演示，所以我这台MySQL数据库上配置的所有表的主键都是以1,3,5,7,9,11,13奇数的规则递增的。如下是当前使用的MySQL数据库实例参数配置情况：


下面实验中，用到的SQL语句大概如下：

Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t(a,b) values(1,1);
insert into t(a,b) values(2,2);
insert into t(a,b) values(3,3);
insert into t(a,b) values(4,4);
show create table t\G
select * from t;
create table s like t;
insert into s(a,b) select a,b from t;
inesrt into s(a,b) values(5,5);

演示步骤如下：

  1. 创建一个innodb表，表中有一个自增主键，和一个唯一约束索引。
  2. 查看表的创建语句。
  3. 向表中插入一行记录。
  4. 查看表结构中的自增主键的键值目前滚动到多少，此时应该是3。
  5. 再次插入相同的记录，这一次会报错，因为有唯一索引限制插入数据不能重复。
  6. 查看表结构中的自增主键的键值目前滚动到多少，此时应该是5。
  7. 再次插入相同的记录，这一次会报错，因为有唯一索引限制插入数据不能重复。
  8. 查看表结构中的自增主键的键值目前滚动到多少，此时应该是7。
  9. 插入一行符合唯一索引要求的数据行。
  10. 查看表结构中的自增主键的键值目前滚动到多少，此时应该是9。
  11. 查看表中所有的数据内容。此时第二行插入的数据的主键值，不是3也不是5，而是7。这是因为3和5被上面的两步错误的插入操作给占用了，而新插入的数据不能使用已经滚动过的值，需要使用新自增值，而我们的数据库设置的自增键值是以奇数规则增加的，所以5后面只能是7了，所以这里的值为7。

示例图片如下：


事务回滚操作

当我们向一个自增主键的InnoDB表中插入数据的时候，如果我们使用了显示的开启事务然后因为某种原因我们最后回滚了我们的事务，此时对于表的自增值也会发生滚动，而接下里新插入的数据，也将不能使用滚动过的自增值，而是需要重新申请一个新的自增值。

接下来我们来验证一下我们的结论。此次试验是基于前面的“插入数据失败”的实验进行。

实验步骤如下：

  1. 当前表的数据内容和自增值是多少。因为是基于前面的实验来的，此次的自增值为9。
  2. 显示的开启事务。
  3. 插入一行符合要求的数据。
  4. 回滚事务。
  5. 查看表的自增值是多少，此时应该是11。
  6. 插入一行符合要求的数据。
  7. 查看表的自增值是多少，此时应该是13。这是新数据行将要用到的自增值。
  8. 查看表中所有的数据内容。发现新增加的数据行的主键值不是9，而是11。是因为9已经被事务中插入的一行数据给占用了，因为我们回滚了那一行插入的数据，所以表中没有这一行数据，但是自增值已经滚动并且不能被再次使用了。当我们新插入数据的时候，只能申请下一个自增值来作为主键。而我们的数据库设置的自增键值是以奇数规则增加的，所以9后面只能是11了。

实现结果如下：


批量插入数据

如果在批量插入数据的时候，为了提高插入的效率。在申请自增值的时候，有一个二倍关系的策略。具体如下。这里的批量插入数据只是你不知道将要插入多少数据到目标表中，需要一边读取，一边插入。而对于values后面跟着具体的数据行的时候，不会出现下面这样的情况，values后面跟着具体的行数的批量插入会在开始插入的时候，一次性的申请对应行数的自增值，不会分批次申请自增值。

  1. 插入第一行的时候，申请1个自增值。
  2. 如果还要插入数据，则第二次申请2个自增值。
  3. 如果还要插入数据，则第三次申请4个自增值。
  4. 如果还要插入数据，则第四次申请8个自增值。以此类推。

上面的这种规则，就可能会出现下面的这种情况，如果第4次申请的8个自增值，用不了怎么办？那自增值就被浪费了。如果下一个事务再次插入数据的时候，则会基于上一个事务申请后的自增值基础上再申请。此时就出现自增值不连续的情况出现。

我们基于上面的“事务回滚操作”的实验继续。我们创建一个和t表一样的数据表s，保证t表中有4条记录。然后我们想从t表中查询数据，向s表中插入数据。插入数据的时候，不查询t表的主键id，使用s表的自增主键自己维护主键值。

步骤如下：

  1. 插入一条数据在t表中，保证里面只有4条记录。
  2. 创建一个表s，结构和t表一样。
  3. 从t表中查询数据，插入到s表中。
  4. 查看s表的数据内容和自增值是多少。
  5. 手动向s表中插入一条记录，看下这条记录的主键值是多少。此时发现新插入记录的主键值和上面的4条记录不是连续的。原因就是上面描述的那样，在批量插入未知记录数到表中的时候，每次申请的自增值的个数是上一次的申请个数的2倍。而如果申请2倍之后，刚插入了几条发现没有数据可以插入了，此时申请的自增值就浪费掉了。

示例结果如下：

InnoDB的自增值为什么不能回收利用？

innodb的自增值为什么不能回退重复利用呢？为了提升插入数据的效率和并行度。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

  1. 假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
  2. 事务 B 正确提交了，但事务 A 出现了唯一键冲突。
  3. 如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
  4. 接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。

而为了解决这个主键冲突，有两种方法：

  1. 每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在。
  2. 把自增 id 地锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

可见，这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首，就是我们假设的这个“允许自增 id 回退”的前提导致的。

因此，InnoDB 放弃了这个设计，语句执行失败也不回退自增 id。也正是因为这样，所以才只保证了自增 id 是递增的，但不保证是连续的。

举报
评论 0
    热门：