mysql自增长列重复_innodb-自增列重复值问题

1 innodb 自增列出现重复值的问题

先从问题入手;重现下这个bug

usetest;drop tablet1;create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;insert into t1 values (1,2);insert into t1 values (null,2);insert into t1 values (null,2);select * fromt1;;----;------;

| id | a |

;----;------;

| 1 | 2 |

| 2 | 2 |

| 3 | 2 |

;----;------;

delete from t1 where id=2;delete from t1 where id=3;select * fromt1;;----;------;

| id | a |

;----;------;

| 1 | 2 |

;----;------;

这里我们关闭mysql;再启动mysql,然后再插入一条数据

insert into t1 values (null,2);select * FROMT1;;----;------;

| id | a |

;----;------;

| 1 | 2 |

;----;------;

| 2 | 2 |

;----;------;

我们看到插入了(2,2);而如果我没有重启;插入同样数据我们得到的应该是(4,2);

上面的测试反映了mysql重启后;innodb存储引擎的表自增id可能出现重复利用的情况。

自增id重复利用在某些场景下回出现问题。依然用上面的例子;假设t1有个历史表t1_history用来存t1表的历史数据;那么mysqld重启前,ti_history中可能已经有了(2,2)这条数据;而重启后我们又插入了(2;2);当新插入的(2,2)迁移到历史表时;会违反主键约束。

2 innodb 自增列出现重复值的原因

mysql> show create tablet1G;*************************** 1. row ***************************

Table: t1Create Table: CREATE TABLE;t1; (

;id;int(11) NOT NULLAUTO_INCREMENT,

;a;int(11) DEFAULT NULL,PRIMARY KEY(;id;)

) ENGINE=innodb AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec)

建表时可以指定 AUTO_INCREMENT值;不指定时默认为1.这个值表示当前自增列的起始值大小;如果新插入的数据没有指定自增列的值;那么自增列的值即为这个起始值。

对于innodb表;这个值是存在内存中(dict_table_struct.autoinc)。那么又问;为什么我们每次插入新的值后; show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的(ha_innobase::update_create_info)。

知道了AUTO_INCREMENT是实时存储内存中的。那么;mysqld 重启后;从哪里得到AUTO_INCREMENT呢? 内存值肯定是丢失了;.实际上mysql采用执行类似select max(id);1 from t1;方法来得到AUTO_INCREMENT。而这种方法就会造成自增id重复的原因。

3 myisam也有这个问题吗

myisam是没有这个问题的。myisam表.frm文件也存AUTO_INCREMENT值;同innodb一样;这个值也不是实时的。myisam会将这个值实时存储在.MYI文件中(mi_state_info_write)。mysqld重起后会从.MYI中读取AUTO_INCREMENT值(mi_state_info_read)。因此;myisam表重启是不会出现自增id重复的问题。

4 innodb 自增列出现重复问题修复

myisam选择将AUTO_INCREMENT实时存储在.MYI文件头部中。实际上.MYI头部还会实时存其他信息;也就是说写AUTO_INCREMENT只是个顺带的操作。其性能损耗可以忽略。InnoDB 表如果要解决这个问题;有两种方法。1)将auto_increment最大值持久到frm文件中。2)将 auto_increment最大值持久到聚集索引根页trx_id所在的位置。第一种方法直接写文件性能消耗较大;这是一额外的操作;而不是以个顺带的操作。如是我们采用第二种方案。为什么选择存储在聚集索引根页页头trx_id。页头trx_id中存存储trx_id,只对二级索引页和insert buf 页头有效(MVCC).而聚集索引根页页头trx_id这个值是没有使用的;始终保持初始值0.正好这个位置8个字节可存放自增值的值。我们每次更新AUTO_INCREMENT值时;同时将这个值修改到聚集索引根页页头trx_id的位置。 这个写操作跟真正的数据写操作一样;遵守write-ahead log原则;只不过这里只需要redo log ,而不需要undo log。因为我们不需要回滚AUTO_INCREMENT的变化(即回滚后自增列值会保留;即使insert 回滚了;auto_increment值不会回滚)

因此;AUTO_INCREMENT值存储在聚集索引根页trx_id所在的位置;实际上是对内存根页的修改和多了一条redo log(量很小),而这个redo log 的写入也是异步的;可以说是原有事务log的一个顺带操作。因此AUTO_INCREMENT值存储在聚集索引根页这个性能损耗是极小的。

5 修复后的性能对比

我们新增了全局参数innodb_autoinc_persistent  取值on/off; on 表示将AUTO_INCREMENT值实时存储在聚集索引根页。off则采用原有方式只存储在内存。

./bin/sysbench --test=sysbench/tests/db/insert.lua --mysql-port=4001 --mysql-user=root --mysql-table-engine=innodb --mysql-db=sbtest --oltp-table-size=0 --oltp-tables-count=1 --num-threads=100 --mysql-socket=/u01/zy/sysbench/build5/run/mysql.sock --max-time=7200 --max-requests run

set global innodb_autoinc_persistent=off;

tps:22199 rt:2.25msset global innodb_autoinc_persistent=on;

tps:22003 rt:2.27ms

可以看出性能损耗在%1以下。

6 改进

新增参数innodb_autoinc_persistent_interval 用于控制持久化auto_increment值的频率。例如;innodb_autoinc_persistent_interval=100;auto_incrememt_increment=1时;即每100次insert会控制持久化一次auto_increment值。每次持久的值为;当前值;innodb_autoinc_persistent_interval.

测试结果如下

innodb_autoinc_persistent=OFF

innodb_autoinc_persistent=ON

innodb_autoinc_persistent_interval=1

innodb_autoinc_persistent=ON

innodb_autoinc_persistent_interval=10

innodb_autoinc_persistent=ON

innodb_autoinc_persistent_interval=100

TPS

22199

22003

22069

22209

RT(ms)

2.25

2.27

2.26

2.25

注意;如果我们使用需要开启innodb_autoinc_persistent;应该在参数文件中指定;

innodb_autoinc_persistent= on

如果这样指定set global innodb_autoinc_persistent=on;重启后将不会从聚集索引根页读取auto_increment最大值.

两个疑问;

1 对于innodb和 myisam 存储引擎;.frm中的AUTO_INCREMENT是多余的。其他存储引擎没有研究;不知道有没有用处。

2 innodb表;重启通过select max(id);1 from t1得到AUTO_INCREMENT值;如果id上有索引那么这个语句使用索引查找就很快。那么;这个可以解释mysql 为什么要求自增列必须包含在索引中的原因。 如果没有指定索引;则报如下错误;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

而myisam表竟然也有这个要求;感觉是多余的。