innodb自增列重復(fù)值問(wèn)題_MySQL

先從問(wèn)題入手，重現(xiàn)下這個(gè)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 |+----+------+

這里我們關(guān)閉mysql，再啟動(dòng)mysql,然后再插入一條數(shù)據(jù)

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

我們看到插入了（2,2），而如果我沒(méi)有重啟，插入同樣數(shù)據(jù)我們得到的應(yīng)該是（4,2)；

上面的測(cè)試反映了mysql重啟后，innodb存儲(chǔ)引擎的表自增id可能出現(xiàn)重復(fù)利用的情況。

自增id重復(fù)利用在某些場(chǎng)景下回出現(xiàn)問(wèn)題。依然用上面的例子，假設(shè)t1有個(gè)歷史表t1_history用來(lái)存t1表的歷史數(shù)據(jù)，那么mysqld重啟前,ti_history中可能已經(jīng)有了（2,2）這條數(shù)據(jù)，而重啟后我們又插入了（2，2），當(dāng)新插入的(2,2)遷移到歷史表時(shí)，會(huì)違反主鍵約束。

2 innodb 自增列出現(xiàn)重復(fù)值的原因

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)

建表時(shí)可以指定 AUTO_INCREMENT值，不指定時(shí)默認(rèn)為1.這個(gè)值表示當(dāng)前自增列的起始值大小，如果新插入的數(shù)據(jù)沒(méi)有指定自增列的值，那么自增列的值即為這個(gè)起始值。建表時(shí)這個(gè)值會(huì)存儲(chǔ)在.frm文件中。那么我們插入新的數(shù)據(jù)后，自增列的起始值會(huì)變大，這個(gè)變大的值會(huì)存回.frm文件嗎？

對(duì)于innodb表，這個(gè)值不會(huì)存回.frm中.而是存在內(nèi)存中（dict_table_struct.autoinc）。那么又問(wèn)，既然這個(gè)值沒(méi)有存回.frm中，為什么我們每次插入新的值后， show create table t1看到AUTO_INCREMENT值是跟隨變化的。其實(shí)show create table t1并沒(méi)有去讀frm取AUTO_INCREMENT,值，而是直接從dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

.frm中的AUTO_INCREMENT值，雖然不是實(shí)時(shí)更新的，但在我們?cè)趫?zhí)行一些DDL重建表示還是更新auto_increment值的。

知道了AUTO_INCREMENT是實(shí)時(shí)存儲(chǔ)內(nèi)存中的，同時(shí).frm中的AUTO_INCREMENT值時(shí)不實(shí)時(shí)的。那么，mysqld 重啟后，從哪里得到AUTO_INCREMENT呢? 內(nèi)存值肯定是丟失了，.frm中的AUTO_INCREMENT是不準(zhǔn)確的（很大可能比實(shí)際偏小）.實(shí)際上mysql采用執(zhí)行類似select max(id)+1 from t1;方法來(lái)得到AUTO_INCREMENT。而這種方法就會(huì)造成自增id重復(fù)的原因。

3 myisam也有這個(gè)問(wèn)題嗎

myisam是沒(méi)有這個(gè)問(wèn)題的。myisam表.frm文件也存AUTO_INCREMENT值，同innodb一樣，這個(gè)值也不是實(shí)時(shí)的。myisam會(huì)將這個(gè)值實(shí)時(shí)存儲(chǔ)在.MYI文件中（mi_state_info_write）。mysqld重起后會(huì)從.MYI中讀取AUTO_INCREMENT值（mi_state_info_read)。因此，myisam表重啟是不會(huì)出現(xiàn)自增id重復(fù)的問(wèn)題。

4 innodb 自增列出現(xiàn)重復(fù)問(wèn)題修復(fù)

myisam選擇將AUTO_INCREMENT實(shí)時(shí)存儲(chǔ)在.MYI文件頭部中。實(shí)際上.MYI頭部還會(huì)實(shí)時(shí)存其他信息，也就是說(shuō)寫(xiě)AUTO_INCREMENT只是個(gè)順帶的操作。其性能損耗可以忽略。InnoDB 表如果要解決這個(gè)問(wèn)題，有兩種方法。1）將auto_increment最大值持久到frm文件中。2）將 auto_increment最大值持久到聚集索引根頁(yè)trx_id所在的位置。第一種方法直接寫(xiě)文件性能消耗較大，這是一額外的操作，而不是以個(gè)順帶的操作。如是我們采用第二種方案。為什么選擇存儲(chǔ)在聚集索引根頁(yè)頁(yè)頭trx_id。頁(yè)頭trx_id中存存儲(chǔ)trx_id,只對(duì)二級(jí)索引頁(yè)和insert buf 頁(yè)頭有效（MVCC).而聚集索引根頁(yè)頁(yè)頭trx_id這個(gè)值是沒(méi)有使用的，始終保持初始值0.正好這個(gè)位置8個(gè)字節(jié)可存放自增值的值。我們每次更新AUTO_INCREMENT值時(shí)，同時(shí)將這個(gè)值修改到聚集索引根頁(yè)頁(yè)頭trx_id的位置。 這個(gè)寫(xiě)操作跟真正的數(shù)據(jù)寫(xiě)操作一樣，遵守write-ahead log原則，只不過(guò)這里只需要redo log ,而不需要undo log。因?yàn)槲覀儾恍枰貪LAUTO_INCREMENT的變化（即回滾后自增列值會(huì)保留，即使insert 回滾了，auto_increment值不會(huì)回滾）

因此，AUTO_INCREMENT值存儲(chǔ)在聚集索引根頁(yè)trx_id所在的位置，實(shí)際上是對(duì)內(nèi)存根頁(yè)的修改和多了一條redo log（量很小）,而這個(gè)redo log 的寫(xiě)入也是異步的，可以說(shuō)是原有事務(wù)log的一個(gè)順帶操作。因此AUTO_INCREMENT值存儲(chǔ)在聚集索引根頁(yè)這個(gè)性能損耗是極小的。

5 修復(fù)后的性能對(duì)比

我們新增了全局參數(shù)innodb_autoinc_persistent 取值on/off； on 表示將AUTO_INCREMENT值實(shí)時(shí)存儲(chǔ)在聚集索引根頁(yè)。off則采用原有方式只存儲(chǔ)在內(nèi)存。

./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

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

6 改進(jìn)

新增參數(shù)innodb_autoinc_persistent_interval 用于控制持久化auto_increment值的頻率。例如：innodb_autoinc_persistent_interval=100，auto_incrememt_increment=1時(shí)，即每100次insert會(huì)控制持久化一次auto_increment值。每次持久的值為：當(dāng)前值+innodb_autoinc_persistent_interval.

測(cè)試結(jié)果如下

注意：如果我們使用需要開(kāi)啟innodb_autoinc_persistent，應(yīng)該在參數(shù)文件中指定，

innodb_autoinc_persistent= on

 如果這樣指定set global innodb_autoinc_persistent=on;重啟后將不會(huì)從聚集索引根頁(yè)讀取auto_increment最大值. 

兩個(gè)疑問(wèn)：

1 對(duì)于innodb和 myisam 存儲(chǔ)引擎，.frm中的AUTO_INCREMENT是多余的。其他存儲(chǔ)引擎沒(méi)有研究，不知道有沒(méi)有用處。

2 innodb表，重啟通過(guò)select max(id)+1 from t1得到AUTO_INCREMENT值，如果id上有索引那么這個(gè)語(yǔ)句使用索引查找就很快。那么，這個(gè)可以解釋mysql 為什么要求自增列必須包含在索引中的原因。 如果沒(méi)有指定索引，則報(bào)如下錯(cuò)誤，

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

而myisam表竟然也有這個(gè)要求，感覺(jué)是多余的。

附：

innodb_autoinc_lock_mode 這個(gè)參數(shù)主要解決自增列主備復(fù)制問(wèn)題的，用于控制自增列值連續(xù)性的。與本文無(wú)關(guān)，詳細(xì)可以參考這里

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com