Heim > Datenbank > MySQL-Tutorial > innodb 自增列重复值问题_MySQL

innodb 自增列重复值问题_MySQL

WBOY
Freigeben: 2016-06-01 13:12:40
Original
1245 Leute haben es durchsucht

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

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

use test;drop table t1;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 * from t1;+----+------+| id | a |+----+------+| 1 | 2 || 2 | 2 || 3 | 2 |+----+------+delete from t1 where id=2;delete from t1 where id=3;select * from t1;+----+------+| id | a |+----+------+| 1 | 2 |+----+------+
Nach dem Login kopieren

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

insert into t1 values (null,2);select * FROM T1;+----+------+| id | a |+----+------+| 1 | 2 |+----+------+| 2 | 2 |+----+------+
Nach dem Login kopieren

  我们看到插入了(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 table t1/G;*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=innodb AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec)
Nach dem Login kopieren

    建表时可以指定 AUTO_INCREMENT值,不指定时默认为1.这个值表示当前自增列的起始值大小,如果新插入的数据没有指定自增列的值,那么自增列的值即为这个起始值。建表时这个值会存储在.frm文件中。那么我们插入新的数据后,自增列的起始值会变大,这个变大的值会存回.frm文件吗?

   对于innodb表,这个值不会存回.frm中.而是存在内存中(dict_table_struct.autoinc)。那么又问,既然这个值没有存回.frm中,为什么我们每次插入新的值后, show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1并没有去读frm取AUTO_INCREMENT,值,而是直接从dict_table_struct.autoinc取得的(ha_innobase::update_create_info)。

   .frm中的AUTO_INCREMENT值,虽然不是实时更新的,但在我们在执行一些DDL重建表示还是更新auto_increment值的。

 

   知道了AUTO_INCREMENT是实时存储内存中的,同时.frm中的AUTO_INCREMENT值时不实时的。那么,mysqld 重启后,从哪里得到AUTO_INCREMENT呢? 内存值肯定是丢失了,.frm中的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 runset global innodb_autoinc_persistent=off;tps: 22199 rt:2.25msset global innodb_autoinc_persistent=on;tps: 22003 rt:2.27ms
Nach dem Login kopieren

可以看出性能损耗在%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
Nach dem Login kopieren
 如果这样指定set global innodb_autoinc_persistent=on;重启后将不会从聚集索引根页读取auto_increment最大值. 
Nach dem Login kopieren

两个疑问:

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
Nach dem Login kopieren

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

 

附:

innodb_autoinc_lock_mode 这个参数主要解决自增列主备复制问题的,用于控制自增列值连续性的。与本文无关,详细可以参考这里

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage