如何解決通過shell腳本模擬MySQL自增列的不一致問題

130次閱讀

共計 2113 個字符，預計需要花費 6 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下如何解決通過 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 的參數值情況

MySQL 5.7 中的參數值情況：

首先我們可以模擬一下這個測試的基線，把兩個變量都修改為 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

for i in {1..500}
do
mysql test_new EOF
SELECT * FROM t$i;
EOF
done

sleep 10;

for i in {1..3}
do
mysql test_new EOF
SHOW CREATE TABLE t$i;
EOF
done

測試完成之后，來查看自增列的值情況.

在 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 中，發現這類問題竟然還復現不了了，至于是代碼層級做了修復還是和其它參數有關，就需要深入一下了。

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=100 DEFAULT CHARSET=latin1

看完了這篇文章，相信你對“如何解決通過 shell 腳本模擬 MySQL 自增列的不一致問題”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完