MySQL核心月報 2015.01-MySQL · 捉蟲動态· InnoDB自增列重複值問題

<b>問題重制</b>

這裡我們關閉mysql，再啟動mysql,然後再插入一條資料

我們看到插入了（2,2），而如果我沒有重新開機，插入同樣資料我們得到的應該是（4,2)。 上面的測試反映了mysqld重新開機後，innodb存儲引擎的表自增id可能出現重複利用的情況。

自增id重複利用在某些場景下會出現問題。依然用上面的例子，假設t1有個曆史表t1_history用來存t1表的曆史資料，那麼mysqld重新開機前,ti_history中可能已經有了（2,2）這條資料，而重新開機後我們又插入了（2，2），當新插入的(2,2)遷移到曆史表時，會違反主鍵限制。

<b>原因分析</b>

innodb 自增列出現重複值的原因

建表時可以指定 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重複的原因。

<b>myisam自增值</b>

myisam也有這個問題嗎？myisam是沒有這個問題的。myisam會将這個值實時存儲在.myi檔案中（mi_state_info_write）。mysqld重起後會從.myi中讀取auto_increment值（mi_state_info_read)。是以，myisam表重新開機是不會出現自增id重複的問題。

<b>問題修複</b>

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則采用原有方式隻存儲在記憶體。

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

<b>改進</b>

新增參數innodb_autoinc_persistent_interval 用于控制持久化auto_increment值的頻率。例如：innodb_autoinc_persistent_interval=100，auto_incrememt_increment=1時，即每100次insert會控制持久化一次auto_increment值。每次持久的值為：目前值+innodb_autoinc_persistent_interval。

測試結論

<b>限制</b>

1 innodb_autoinc_persistent=on， innodb_autoinc_persistent_interval=n&gt;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，應該在參數檔案中指定

如果這樣指定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表竟然也有這個要求，感覺是多餘的。