通過shell腳本模拟MySQL自增列的不一緻問題

   MySQL的自增列問題其實很有意思，在重新開機資料庫之後，會按照max(id)+1的方式來計算，這樣一個看起來有些别扭的實作方式在早期版本就飽受诟病，在MySQL 5.7都沒有解決掉，終于在8.0松口了，計劃在這個版本中修複。

   而重新開機會帶來自增列一類的潛在問題，而如果不重新開機其實也有可能會有自增列的不一緻問題。和兩個參數table_definition_cache和table_open_cache還是密切相關的。

主要的原因是什麼呢，引用阿裡資料庫核心團隊的解釋（https://www.kancloud.cn/taobaomysql/monthly/67171）：一方面InnoDB表自增值是存儲在表對象中的，表對象又是放在緩存中的，如果表太多而不能全部放在緩存中的話，老的表就會被置換出來，這種被置換出來的表下次再使用的時候，就要重新打開一遍，對自增列來說，這個過程就和執行個體重新開機類似，需要

select max(id) + 1 算一下自增值。

  表對象緩存大小由 table_definition_cache 系統變量控制，最小值為400，表緩存相關的另一個系統變量是table_open_cache，這個控制的是所有線程打開表的緩存大小，這個緩存放在server層。

   我在檢視了5.6.14的環境之後，發現這個值已經提升到了500，而在MySQL 5.7中，提升到了1400，可見這方面了下了大功夫。

  MySQL 5.6.14的參數值情況

# mysqladmin var|grep table_open_cache

| table_open_cache                           | 256

| table_open_cache_instances                 | 1  

# mysqladmin var|grep table_definition_cache

| table_definition_cache                     | 500

MySQL 5.7中的參數值情況：

mysql> show variables like 'table_definition_cache';

| Variable_name          | Value |

| table_definition_cache | 1400  |

mysql> show variables like 'table_open_cache';

| Variable_name    | Value |

| table_open_cache | 2000  | 阿裡的同學給出了testcase的僞代碼，我就來實作以下，給出shell版本的測試腳本。

首先我們可以模拟一下這個測試的基線，把兩個變量都修改為400.

SET GLOBAL table_definition_cache = 400;

SET GLOBAL table_open_cache = 400;

然後使用如下的shell腳本，仔細來看，腳本邏輯很簡單了。

生成500個表，然後插入一條資料，修改自增列值，然後查詢表裡的資料，使得資料能夠刷出，稍作等待，檢視show create table的結果。

for i in {1..500}

do

mysql   test_new <<EOF

CREATE TABLE t$i(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY(id)) ENGINE=InnoDB;

INSERT INTO t$i(name) VALUES("InnoDB");

ALTER TABLE t$i AUTO_INCREMENT = 100;

EOF

done

mysql    test_new <<EOF

SELECT * FROM t$i;

sleep 10;

for i in {1..3}

SHOW CREATE TABLE t$i;

測試完成之後，來檢視自增列的值情況.

在5.6.14中效果很明顯。

Table   Create Table

t1      CREATE TABLE `t1` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(30) DEFAULT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

而在5.7中，發現這類問題竟然還複現不了了，至于是代碼層級做了修複還是和其它參數有關，就需要深入一下了。

t1      CREATE TABLE `t1` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(30) DEFAULT NULL,\n  PRIMARY KEY

(`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1