MySQL · 捉蟲動態· InnoDB自增列重復值問題 · 數據庫內核月報

**問題重現** 先從問題入手，重現下這個[bug](http://bugs.mysql.com/bug.php?id=199) ~~~ use test; drop table if exists 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 | +----+------+ ~~~ 這里我們關閉mysql，再啟動mysql,然后再插入一條數據 ~~~ insert into t1 values (null,2); select * FROM T1; +----+------+ | id | a | +----+------+ | 1 | 2 | +----+------+ | 2 | 2 | +----+------+ ~~~ 我們看到插入了（2,2），而如果我沒有重啟，插入同樣數據我們得到的應該是（4,2)。上面的測試反映了mysqld重啟后，InnoDB存儲引擎的表自增id可能出現重復利用的情況。自增id重復利用在某些場景下會出現問題。依然用上面的例子，假設t1有個歷史表t1_history用來存t1表的歷史數據，那么mysqld重啟前,ti_history中可能已經有了（2,2）這條數據，而重啟后我們又插入了（2，2），當新插入的(2,2)遷移到歷史表時，會違反主鍵約束。 **原因分析** InnoDB 自增列出現重復值的原因 ~~~ mysql> show create table t1\G; *************************** 1\. row *************************** Table: t1 Create 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=utf8 1 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重復的原因。 **MyISAM自增值** MyISAM也有這個問題嗎？MyISAM是沒有這個問題的。myisam會將這個值實時存儲在.MYI文件中（mi_state_info_write）。mysqld重起后會從.MYI中讀取AUTO_INCREMENT值（mi_state_info_read)。因此，MyISAM表重啟是不會出現自增id重復的問題。 **問題修復** MyISAM選擇將AUTO_INCREMENT實時存儲在.MYI文件頭部中。實際上.MYI頭部還會實時存其他信息，也就是說寫AUTO_INCREMENT只是個順帶的操作，其性能損耗可以忽略。InnoDB 表如果要解決這個問題，有兩種方法。1）將AUTO_INCREMENT最大值持久到frm文件中。2）將 AUTO_INCREMENT最大值持久到聚集索引根頁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值存儲在聚集索引根頁這個性能損耗是極小的。修復后的性能對比，我們新增了全局參數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.25ms set global innodb_autoinc_persistent=on; tps: 22003 rt:2.27ms ~~~ 可以看出性能損耗在%1以下。 **改進** 新增參數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=ON, innodb_autoinc_persistent_interval=1時性能損耗在%1以下。 innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=100時性能損耗可以忽略。 ~~~ **限制** 1 innodb_autoinc_persistent=on， innodb_autoinc_persistent_interval=N>1時，自增N次后持久化到聚集索引根頁,每次持久的值為當前AUTO_INCREMENT+(N-1)*innodb_autoextend_increment。重啟后讀取持久化的AUTO_INCREMENT值會偏大，造成一些浪費但不會重復。innodb_autoinc_persistent_interval=1 每次都持久化沒有這個問題。 2 如果innodb_autoinc_persistent=on，頻繁設置auto_increment_increment的可能會導致持久化到聚集索引根頁的值不準確。因為innodb_autoinc_persistent_interval計算沒有考慮auto_increment_increment變化的情況，參看dict_table_autoinc_update_if_greater。而設置auto_increment_increment的情況極少，可以忽略。注意：如果我們使用需要開啟innodb_autoinc_persistent，應該在參數文件中指定 ~~~ innodb_autoinc_persistent= on ~~~ 如果這樣指定set global innodb_autoinc_persistent=on;重啟后將不會從聚集索引根頁讀取AUTO_INCREMENT最大值。疑問：對于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表竟然也有這個要求，感覺是多余的。