線上MySQL的自增id用盡怎么辦？

作者：廢柴程序員 鏈接：https://www.jianshu.com/p/a6bc14005b52

MySQL的自增id都定義了初始值，然后不斷加步長。雖然自然數(shù)沒有上限，但定義了表示這個(gè)數(shù)的字節(jié)長度，計(jì)算機(jī)存儲(chǔ)就有上限。比如，無符號整型(unsigned int)是4個(gè)字節(jié)，上限就是2^32 - 1。那自增id用完，會(huì)怎么樣？

表定義自增值id

表定義的自增值達(dá)到上限后的邏輯是：再申請下一個(gè)id時(shí)，得到的值保持不變。

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)

mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//成功插入一行 4294967295
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

第一個(gè)insert成功后，該表的AUTO_INCREMENT還是4294967295，導(dǎo)致第二個(gè)insert又拿到相同自增id值，再試圖執(zhí)行插入語句，主鍵沖突。

2^32 - 1（4294967295）不是一個(gè)特別大的數(shù)，一個(gè)頻繁插入刪除數(shù)據(jù)的表是可能用完的。建表時(shí)就需要考慮你的表是否有可能達(dá)到該上限，若有，就應(yīng)創(chuàng)建成8字節(jié)的bigint unsigned。

InnoDB系統(tǒng)自增row_id

若你創(chuàng)建的InnoDB表未指定主鍵，則InnoDB會(huì)自動(dòng)創(chuàng)建一個(gè)不可見的，6個(gè)字節(jié)的row_id。InnoDB維護(hù)了一個(gè)全局的dict_sys->row_id值

所有無主鍵的InnoDB表，每插入一行數(shù)據(jù)，都將當(dāng)前的dict_sys->row_id作為要插入數(shù)據(jù)的row_id，然后把dict_sys->row_id加1。

代碼實(shí)現(xiàn)時(shí)row_id是個(gè)長度為8字節(jié)的無符號長整型(bigint unsigned)。但I(xiàn)nnoDB在設(shè)計(jì)時(shí)，給row_id留的只是6個(gè)字節(jié)的長度，這樣寫到數(shù)據(jù)表中時(shí)只放了最后6個(gè)字節(jié)，所以row_id能寫到數(shù)據(jù)表中的值，就有兩個(gè)特征：

1. row_id寫入表中的值范圍，是從0到2^48 - 1
2. 當(dāng)dict_sys.row_id=2^48時(shí)，如果再有插入數(shù)據(jù)的行為要來申請row_id，拿到以后再取最后6個(gè)字節(jié)的話就是0

即寫入表的row_id從0~2^48 - 1。達(dá)到上限后，下個(gè)值就是0，然后繼續(xù)循環(huán)。

2^48 - 1已經(jīng)很大，但若一個(gè)MySQL實(shí)例活得久，還是可能達(dá)到上限。

InnoDB里，申請到row_id=N后，就將這行數(shù)據(jù)寫入表中；若表中已經(jīng)存在row_id=N的行，新寫入的行就會(huì)覆蓋原有的行。

驗(yàn)證該結(jié)論：通過gdb修改系統(tǒng)的自增row_id。用gdb是為了便于復(fù)現(xiàn)問題，只能在測試環(huán)境使用。

• row_id用完的驗(yàn)證序列

• row_id用完的效果驗(yàn)證

可見，在我用gdb將dict_sys.row_id設(shè)置為2^48之后，再插入a=2會(huì)出現(xiàn)在表t的第一行，因?yàn)樵撝档膔ow_id=0。

之后再插入a=3，由于row_id=1，就覆蓋了之前a=1的行，因?yàn)閍=1這一行的row_id也是1。

所以應(yīng)該在InnoDB表中主動(dòng)創(chuàng)建自增主鍵：當(dāng)表自增id到達(dá)上限后，再插入數(shù)據(jù)時(shí)會(huì)報(bào)主鍵沖突錯(cuò)誤。

畢竟覆蓋數(shù)據(jù)，就意味著數(shù)據(jù)丟失，影響數(shù)據(jù)可靠性；報(bào)主鍵沖突，插入失敗，影響可用性。一般可靠性優(yōu)于可用性。

Xid

redo log和binlog有個(gè)共同字段Xid，用來對應(yīng)事務(wù)。Xid在MySQL內(nèi)部是如何生成的呢？

MySQL內(nèi)部維護(hù)了一個(gè)全局變量global_query_id

每次執(zhí)行語句時(shí)，將它賦值給query_id，然后給該變量+1：

若當(dāng)前語句是該事務(wù)執(zhí)行的第一條語句，則MySQL還會(huì)同時(shí)把query_id賦值給該事務(wù)的Xid：

而global_query_id是一個(gè)純內(nèi)存變量，重啟之后就清零了。所以同一DB實(shí)例，不同事務(wù)的Xid可能相同。

但MySQL重啟之后會(huì)重新生成新binlog文件，這就保證同一個(gè)binlog文件里的Xid唯一。

雖然MySQL重啟不會(huì)導(dǎo)致同一個(gè)binlog里面出現(xiàn)兩個(gè)相同Xid，但若global_query_id達(dá)到上限，就會(huì)繼續(xù)從0開始計(jì)數(shù)。理論上還是會(huì)出現(xiàn)同一個(gè)binlog里面出現(xiàn)相同Xid。

因?yàn)間lobal_query_id8字節(jié)，上限2^64 - 1。要出現(xiàn)這種情況，需滿足：

1. 執(zhí)行一個(gè)事務(wù)，假設(shè)Xid是A
2. 接下來執(zhí)行2^64次查詢語句，讓global_query_id回到A 2^64太大了，這種可能只存在于理論中。
3. 再啟動(dòng)一個(gè)事務(wù)，這個(gè)事務(wù)的Xid也是A

Innodb trx_id

• Xid由server層維護(hù)

InnoDB內(nèi)部使用Xid，為了關(guān)聯(lián)InnoDB事務(wù)和server

但I(xiàn)nnoDB自己的trx_id，是另外維護(hù)的事務(wù)id（transaction id）。

InnoDB內(nèi)部維護(hù)了一個(gè)max_trx_id全局變量，每次需要申請一個(gè)新的trx_id時(shí)，就獲得max_trx_id的當(dāng)前值，然后并將max_trx_id加1。

InnoDB數(shù)據(jù)可見性的核心思想

每一行數(shù)據(jù)都記錄了更新它的trx_id，當(dāng)一個(gè)事務(wù)讀到一行數(shù)據(jù)時(shí)，判斷該數(shù)據(jù)是否可見，就是通過事務(wù)的一致性視圖與這行數(shù)據(jù)的trx_id做對比。

對于正在執(zhí)行的事務(wù)，你可以從information_schema.innodb_trx表中看到事務(wù)的trx_id。

看如下案例：事務(wù)的trx_id

S2 的執(zhí)行記錄：

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+-----------------+---------------------+
| trx_id          | trx_mysql_thread_id |
+-----------------+---------------------+
| 421972504382792 |                  70 |
+-----------------+---------------------+
1 row in set (0.00 sec)

mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+---------+---------------------+
| trx_id  | trx_mysql_thread_id |
+---------+---------------------+
| 1355623 |                  70 |
+---------+---------------------+
1 row in set (0.01 sec)

S2從innodb_trx表里查出的這兩個(gè)字段，第二個(gè)字段trx_mysql_thread_id就是線程id。顯示線程id，是為說明這兩次查詢看到的事務(wù)對應(yīng)的線程id都是5，即S1所在線程。

t2時(shí)顯示的trx_id是一個(gè)很大的數(shù)；t4時(shí)刻顯示的trx_id是1289，看上去是一個(gè)比較正常的數(shù)字。這是為啥？

t1時(shí)，S1還未涉及更新，是一個(gè)只讀事務(wù)。對于只讀事務(wù)，InnoDB并不會(huì)分配trx_id：

• t1時(shí)，trx_id的值就是0。而這個(gè)很大的數(shù)，只是顯示用
• 直到S1在t3時(shí)執(zhí)行insert，InnoDB才真正分配trx_id。所以t4時(shí)，S2查到該trx_id的值就是1289。

除了明顯的修改類語句，若在select 語句后面加上for update，也不是只讀事務(wù)。

• update 和 delete語句除了事務(wù)本身，還涉及到標(biāo)記刪除舊數(shù)據(jù)，即要把數(shù)據(jù)放到purge隊(duì)列里等待后續(xù)物理刪除，這個(gè)操作也會(huì)把max_trx_id+1， 因此在一個(gè)事務(wù)中至少加2
• InnoDB的后臺(tái)操作，比如表的索引信息統(tǒng)計(jì)這類操作，也是會(huì)啟動(dòng)內(nèi)部事務(wù)的，因此你可能看到，trx_id值并不是按照加1遞增的。

t2時(shí)查到的很大數(shù)字是怎么來的？

每次查詢時(shí)，由系統(tǒng)臨時(shí)計(jì)算：

當(dāng)前事務(wù)的trx變量的指針地址轉(zhuǎn)成整數(shù)，再加上248

這樣可以保證：

• 因?yàn)橥恢蛔x事務(wù)在執(zhí)行期間，它的指針地址不會(huì)變，所以無論在 innodb_trx還是在innodb_locks表里，同一個(gè)只讀事務(wù)查出來的trx_id就會(huì)是一樣的
• 若有并行只讀事務(wù)，每個(gè)事務(wù)的trx變量的指針地址肯定不同。這樣，不同并發(fā)只讀事務(wù)，查出來的trx_id就是不同的。

為什么要加248？

保證只讀事務(wù)顯示的trx_id值比較大，正常情況下就會(huì)區(qū)別于讀寫事務(wù)的id。但trx_id跟row_id的邏輯類似，定義為8個(gè)字節(jié)。理論上還是可能出現(xiàn)一個(gè)讀寫事務(wù)與一個(gè)只讀事務(wù)顯示的trx_id相同。不過概率很低，也沒有什么實(shí)質(zhì)危害，不管。

為何只讀事務(wù)不分配trx_id？

• 減小事務(wù)視圖里面活躍事務(wù)數(shù)組的大小。因?yàn)楫?dāng)前正在運(yùn)行的只讀事務(wù)，不影響數(shù)據(jù)的可見性判斷。所以，在創(chuàng)建事務(wù)的一致性視圖時(shí)，InnoDB就只需要拷貝讀寫事務(wù)的trx_id
• 減少trx_id的申請次數(shù)。InnoDB執(zhí)行一個(gè)普通的select語句，也要對應(yīng)一個(gè)只讀事務(wù)。所以只讀事務(wù)優(yōu)化后，普通查詢語句無需申請trx_id，大大減少并發(fā)事務(wù)申請trx_id的鎖沖突

由于只讀事務(wù)不分配trx_id，顯然trx_id的增速變慢。

但 max_trx_id 會(huì)持久化存儲(chǔ)，重啟也不會(huì)重置為0。理論上，只要一個(gè)MySQL實(shí)例跑得夠久，就可能出現(xiàn)max_trx_id達(dá)到2^48 - 1，然后從0開始循環(huán)。

達(dá)到該狀態(tài)后，MySQL就會(huì)持續(xù)出現(xiàn)一個(gè)臟讀bug：

首先把當(dāng)前的max_trx_id先修改成2^48 - 1。這里是可重復(fù)讀。

• 復(fù)現(xiàn)臟讀

• 因?yàn)橄到y(tǒng)的max_trx_id被設(shè)置成2^48 - 1，所以在session A啟動(dòng)的事務(wù)TA的低水位就是2^48 - 1。

t2時(shí):

• session B執(zhí)行第一條update語句的事務(wù)id=2^48 - 1
• 第二條事務(wù)id就是0了，這條update執(zhí)行后生成的數(shù)據(jù)版本上的trx_id=0

t3時(shí)：

session A執(zhí)行select的可見性判斷：c=3這個(gè)數(shù)據(jù)版本的trx_id（0），小于事務(wù)TA的低水位（2^48 - 1），所以認(rèn)為該數(shù)據(jù)可見。

但這是臟讀。

由于低水位值會(huì)持續(xù)增加，而事務(wù)id從0開始計(jì)數(shù)，導(dǎo)致系統(tǒng)在該時(shí)刻后，所有查詢都會(huì)出現(xiàn)臟讀。

并且MySQL重啟時(shí)max_trx_id也不會(huì)清0，即重啟MySQL，這個(gè)bug仍然存在。那這bug也是只存在于理論上嗎？

假設(shè)一個(gè)MySQL實(shí)例的TPS是50w，持續(xù)這樣，17.8年后就會(huì)出現(xiàn)該情況。但從MySQL真正開始流行到現(xiàn)在，恐怕都還沒有實(shí)例跑到過這個(gè)上限。不過，只要MySQL實(shí)例服務(wù)時(shí)間夠長，就必然會(huì)出現(xiàn)該bug。

這也可以加深對低水位和數(shù)據(jù)可見性的理解

thread_id

系統(tǒng)保存了一個(gè)全局變量thread_id_counter

每新建一個(gè)連接，就將thread_id_counter賦值給這個(gè)新連接的線程變量new_id。

thread_id_counter定義為4個(gè)字節(jié)，因此達(dá)到2^32 - 1，就會(huì)重置為0，繼續(xù)增加。

但不會(huì)在show processlist看到兩個(gè)相同的thread_id。因?yàn)镸ySQL使用了一個(gè)唯一數(shù)組

給新線程分配thread_id時(shí)的邏輯：

總結(jié)

每種自增id有各自的應(yīng)用場景，在達(dá)到上限后的表現(xiàn)也不同：

表的自增id達(dá)到上限后，再申請時(shí)它的值就不會(huì)改變，進(jìn)而導(dǎo)致繼續(xù)插入數(shù)據(jù)時(shí)報(bào)主鍵沖突錯(cuò)誤

row_id達(dá)到上限后，則會(huì)歸0再重新遞增，如果出現(xiàn)相同的row_id，后寫的數(shù)據(jù)會(huì)覆蓋之前的數(shù)據(jù)

Xid只需要不在同一個(gè)binlog文件中出現(xiàn)重復(fù)值即可。雖然理論上會(huì)出現(xiàn)重復(fù)值，但是概率極小，可以忽略不計(jì)

InnoDB的max_trx_id 遞增值每次MySQL重啟都會(huì)被保存起來，所以我們文章中提到的臟讀的例子就是一個(gè)必現(xiàn)的bug，好在留給我們的時(shí)間還很充裕