mysql中用INSERT IGNORE避免InnoDB上的自动增量漏洞(自增列上数据不连续问题)

数据库  /  管理员 发布于 4个月前   269

你是否在MySQL 5.1.22版或更新的版本上使用InnoDB表？

如果是这样，你可能在你的自动增量列中有间隙。

一个简单的INSERT IGNORE查询会为每个被忽略的插入创建间隙，但这是没有记录的行为。

这个文档错误已经提交了。

首先，我们将从一个简单的问题开始。为什么我们在自动增加的列上有间隙？

其次，我将向你展示一个模仿INSERT IGNORE行为而不丢失自动增量值的技巧。

让我们开始吧!

为什么我们会有空隙？

InnoDB检查表上的一个自动增加计数器，如果需要一个新的值，就增加该计数器并将新的值分配给该列。

在MySQL 5.1.22之前，InnoDB使用一种叫做 "传统 "的方法来访问该计数器的值。

这个方法使用了一个叫做AUTO-INC的特殊表锁，一直到查询或事务结束。

由于这个原因，两个查询不能同时拥有AUTO-INC锁，所以我们失去了并发性和性能。

对于像INSERT INTO table1 ... SELECT ... FROM table2这样的长时间运行的查询，问题就更严重了。

在5.1.22及以后的版本中，自动增加值的锁算法是可配置的，你可以使用innodb_autoinc_lock_mode选择不同的算法。

默认值是1，这是一种新的算法，叫做 "连续"。

由于这个新值，一个简单的插入查询，如单行或多行的INSERT/REPLACE，在AUTO-INC上使用一个轻量级的mutex而不是表锁。

我们已经恢复了并发性和性能，但代价很小。

像INSERT ... ON DUPLICATE KEY UPDATE这样的查询在auto_increment列上产生空隙。

为了避免这种小小的不便，我们可以回到传统的方法，

将innodb_autoinc_lock_mode改为0，但是会损失性能和并发性。

我怎样才能解决INSERT IGNORE的这个问题？

正如我之前告诉你的，INSERT IGNORE会产生间隙，这一点并没有记录在案，所以也许你多年来一直没有意识到这个问题。

你可以使用一个特殊的突变表来模仿INSERT IGNORE的行为，正如Baron的博客中所解释的那样，来摆脱间隙问题。

"互斥 "表是一个聪明的技巧，它允许连接表，同时在查询中保持它们彼此独立。

这一属性允许进行有趣的查询，否则是不可能的。

这就是我们的互斥表。我们只需要插入一个整数值：

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被忽略，没有行被插入。

与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发现的：

https://www.percona.com/about-us/our-team/michael-rikmas/

当时我们正在为一个客户做一个咨询案例：

https://www.percona.com/mysql-consulting/overview/

所以，如果这能让你不用做ALTER TABLE来改变自动增量列的大小，那就送他一杯啤酒吧 

转：

https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/