程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> innodb 自增列重復值相關問題

innodb 自增列重復值相關問題

編輯:MySQL綜合教程

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 |
+----+------+

這裡我們關閉mysql,再啟動mysql,然後再插入一條數據 

insert into t1 values (null,2);
select * FROM T1;
+----+------+
| 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 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.這個值表示當前自增列的起始值大小,如果新插入的數據沒有指定自增列的值,那麼自增列的值即為這個起始值。建表時這個值會存儲在.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 run
set global innodb_autoinc_persistent=off;
tps: 22199 rt:2.25ms
set 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表竟然也有這個要求,感覺是多余的。

 

附:

innodb_autoinc_lock_mode 這個參數主要解決自增列主備復制問題的,用於控制自增列值連續性的。與本文無關,詳細可以參考這裡

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved