原文链接：Avoiding auto-increment holes on InnoDB with INSERT IGNORE

你如果在使用MySQL 5.1.22或以上版本，很可能遇到了InnnoDB上自增列（auto-increment columns）的空洞（数据不连续）问题。一个简单的INSERT IGNORE插入语句，当遇到重复数据跳过插入操作时，一个空隙就会在自增列上出现，数据不再连续；官方文档并未描述这种现象，一个文档Bug已经提交给官方了。

首先，让我们从一个简单的问题开始。为什么在自增列上会出现数据空洞？然后我会给你介绍一个小窍门，通过模拟INSERT IGNORE的行为来避免数据在自增列上的空洞问题。让我们开始吧！

为什么会有数据空洞？
当一个自增列需要一个新的值时，InnoDB会检查该列上的一个计数器，将计数器加1后将计数器的值赋给该列。在MySQL 5.1.22之前InnoDB使用一个被称为“传统的”（Traditional）方法来获取计数器的值。这个方法在查询或者事务进行期间，会对表加一个特殊的表锁AUTO-INC，因此，两个查询语句不能同时获取到AUTO-INC锁，查询不能并发执行，导致了性能的下降，当执行类似INSERT INTO table1 … SELECT … FROM table2的查询时，这个问题更加明显。
在MySQL 5.1.22及之后的版本里，我们可以通过修改innodb_autoinc_lock_mode这个配置，来选择另一种获取自增列计数器的加锁算法。这个配置的默认值是1，表示一种新的名为“连续的”（consecutive）加锁算法。有了这种新的算法，像INSERT/REPLACE这种单行或多行的简单插入操作，不再使用AUTO-INC表锁，而是使用一个轻量的互斥锁。各个语句之间得以并发执行，性能得以提升。不过这里有一个小小的代价，类似INSERT … ON DUPLICATE KEY UPDATE这样的查询会在自增列上造成数据空洞问题。
为了避免这个小问题，可以将innodb_autoinc_lock_mode这个配置项改回0，但是会让MySQL损失并发性能。

如果解决INSERT IGNORE的数据空洞问题？

就像我在上面提到过的，INSERT IGNORE的数据空洞问题并未在官方文档中被描述，所以很可能你多年来都未察觉到这个问题。在Barcon的博客里，介绍了如何通过一个特殊的互斥表来模拟INSERT IGNORE的行为来避免这个问题。

互斥表是一个聪明的技巧，它允许表连接的同时保持它们的独立性，这个特性能够实现一些以前不可能的有趣的查询。

这是我们的互斥表，我们只需要在里面插入一个整数：

create table mutex(
i int not null primary key
);
insert into mutex(i) values (1);

我们在InnoDB上带自增列的表长这样：

CREATE TABLE foo (
id int(11) NOT NULL AUTO_INCREMENT,
name int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uniqname (name)
) ENGINE=InnoDB;

通过左外连接（LEFT OUTER JOIN:）插入一个值：

insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 1 row affected (0.00 sec)

多次插入同一个值，你会看到，插入操作被忽略了，没有新的行被插入。这跟INSERT IGNORE的行为是一样的：

insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)

现在让我们检查一下自增列的计数器：

show create table foo\G
*************************** 1. row ***************************
Table: foo
Create Table: CREATE TABLE foo (
id int(11) NOT NULL AUTO_INCREMENT,
name int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uniqname (name)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

没有数据空洞。

这个技巧是我们在为一个客户做案例咨询时，由Michael Rikmas发现的。如果这个技巧帮助你避免通过ALTER TABLE去调整AUTO_INCREMENT的值，不妨请他喝一杯。

本作品采用《CC 协议》，转载必须注明作者和本文链接