InnoDB中的row_id與自增鍵用完怎么辦

在數(shù)據(jù)庫表設(shè)計(jì)中會(huì)糾結(jié)于”自然鍵”和”代理鍵”的選擇，自然鍵在實(shí)現(xiàn)數(shù)據(jù)“軟刪除”時(shí)實(shí)現(xiàn)比較復(fù)雜，部分自然鍵因?yàn)殒I值過長或多列組合導(dǎo)致不適合作為表主鍵，而比較常見兩種代理鍵為自增列(auto incremnet)和全局唯一標(biāo)識(shí)列(GUID)。

使用自增列作為主鍵的優(yōu)缺點(diǎn)：

1、 主鍵鍵值長度短，INT列需要4個(gè)字節(jié)，BIGINT列需要8個(gè)字節(jié);

2、 自增主鍵順序遞增，在INSERT操作時(shí)”順序”寫入表;

3、 由于數(shù)據(jù)集中插入到表尾部，在高并發(fā)情況下容易造成”數(shù)據(jù)頁熱點(diǎn)”，影響插入效率;

4、 自增主鍵只能保證在表內(nèi)數(shù)據(jù)唯一，對(duì)于分庫分表場景，可能因錯(cuò)誤操作產(chǎn)生相同的“唯一值”。

自增鍵用完了會(huì)發(fā)生什么？

我們?cè)诮ū淼臅r(shí)候?yàn)槟硞€(gè)索引列（ 注意：必須是索引列 ）添加 AUTO_INCREMENT 屬性，就像這樣：

CREATE TABLE t (    c1 TINYINT AUTO_INCREMENT,    c2 TINYINT,    KEY idx_c1 (c1)) ENGINE=InnoDB;

表 t 中包含一個(gè)索引列 c1 ，該列被添加了 AUTO_INCREMENT 屬性。我們先向該表中插入一條記錄：

mysql> INSERT INTO t(c1, c2) VALUES(126, 1);Query OK, 1 row affected (0.02 sec)

之后我們不再在 INSERT 語句中顯式地插入 c1 列的值，它的默認(rèn)值就將是從當(dāng)前插入的最大的那個(gè)值之后自增，比如這樣：

mysql> INSERT INTO t(c2) VALUES(1);Query OK, 1 row affected (0.01 sec)

我們看一下此時(shí)表 t 中的數(shù)據(jù)：

mysql> SELECT * FROM t;+-----+------+| c1  | c2   |+-----+------+| 126 |    1 || 127 |    1 |+-----+------+2 rows in set (0.02 sec)

因?yàn)?c1 列是 TINYINT 類型的，使用1個(gè)字節(jié)存儲(chǔ)數(shù)據(jù)，它能存儲(chǔ)最大的值就是 127 ，如果當(dāng)該列的值到達(dá) 127 之后，我們繼續(xù)向表中插入數(shù)據(jù)，自增列 c1 的值將會(huì)變成什么呢？

mysql> INSERT INTO t(c2) VALUES(1);Query OK, 1 row affected (0.01 sec)

插入成功之后我們?cè)倏匆幌卤碇械臄?shù)據(jù)：

mysql> SELECT * FROM t;+-----+------+| c1  | c2   |+-----+------+| 126 |    1 || 127 |    1 || 127 |    1 |+-----+------+3 rows in set (0.01 sec)

很顯然，自增列 c1 的值將不再繼續(xù)增長，而是取的 TINYINT 所能存儲(chǔ)的最大值。

這里需要注意的是，在當(dāng)前舉的例子中，我們只是在自增列 c1 上邊建立了一個(gè)普通的二級(jí)索引 idx_c1 ，所以鍵值重復(fù)也沒啥問題。不過我們一般將 AUTO_INCREMENT 屬性應(yīng)用在表的主鍵上，此時(shí)如果自增列值達(dá)到了主鍵對(duì)應(yīng)數(shù)據(jù)類型所能存儲(chǔ)的最大值時(shí)，就會(huì)報(bào)錯(cuò)（因?yàn)橹麈I值重復(fù)），大家一定注意！

row_id用完了會(huì)發(fā)生什么？
在我們使用 InnoDB 存儲(chǔ)引擎來建表時(shí)，如果我們自己沒有顯式地創(chuàng)建主鍵時(shí)，存儲(chǔ)引擎會(huì)默認(rèn)找一個(gè)具有 NOT NULL 屬性的唯一二級(jí)索引列來充當(dāng)主鍵，如果我們?cè)诮ū碚Z句中也沒有寫具有 NOT NULL 屬性的唯一二級(jí)索引列，那很抱歉，存儲(chǔ)引擎默認(rèn)會(huì)為我們添加一個(gè)稱之為 row_id 的主鍵列。

這個(gè) row_id 列默認(rèn)是6個(gè)字節(jié)大小，值得注意的是，設(shè)計(jì) InnoDB 的大叔并不是為每一個(gè)用戶未顯式創(chuàng)建主鍵的表的 row_id 列都單獨(dú)維護(hù)一個(gè)計(jì)數(shù)器，而是所有的表都共享一個(gè)全局的計(jì)數(shù)器。比方說我們沒有對(duì)表 t1 和 t2 顯式創(chuàng)建主鍵，存儲(chǔ)引擎為它們都創(chuàng)建了一個(gè) row_id 列，如果我們向表 t1 中插入了一條記錄，那么就從全局計(jì)數(shù)器里分配一個(gè)值做該表 row_id 列的值，然后將全局計(jì)數(shù)器加1；接著我們?cè)傧虮?t2 中插入一條記錄，那么就再從全局計(jì)數(shù)器里分配一個(gè)值做該表 row_id 列的值，然后將全局計(jì)數(shù)器加1。

有很多同學(xué)有疑惑，如果這個(gè)全局計(jì)數(shù)器的值超過了6個(gè)字節(jié)所能表示的最大值時(shí)，會(huì)發(fā)生什么，全局計(jì)數(shù)器從0重新開始技術(shù)，一切從頭再來么？

哈哈:smile:，并不會(huì)這樣。雖然 row_id 由6個(gè)字節(jié)組成，但是設(shè)計(jì) InnoDB 的大叔卻是使用8個(gè)字節(jié)存儲(chǔ)全局計(jì)數(shù)器的值，他們將這8個(gè)字節(jié)分兩次寫入 row_id ，第一次寫入右邊四個(gè)字節(jié)到 row_id 的右邊4個(gè)字節(jié)，接著將左邊四個(gè)字節(jié)再寫入 row_id 的左邊的2個(gè)字節(jié)，就像這樣：

在將全局計(jì)數(shù)器左邊四個(gè)字節(jié)再寫入 row_id 的左邊的2個(gè)字節(jié) 時(shí)采用如下函數(shù)：

UNIV_INLINEvoidmach_write_to_2(/*============*/byte*b,/*!< in: pointer to two bytes where to store 也就是row_id值前2個(gè)字節(jié)所在的內(nèi)存地址*/ulintn)/*!< in: ulint integer to be stored 也就是全局計(jì)數(shù)器的左4個(gè)字節(jié)值*/{ut_ad(b);ut_ad((n | 0xFFFFUL) <= 0xFFFFUL);b[0] = (byte)(n >> 8);b[1] = (byte)(n);}

可以看到代碼中有這樣一行：

ut_ad((n | 0xFFFFUL) <= 0xFFFFUL);

這是一個(gè)斷言函數(shù)，這行代碼的意思就是全局計(jì)數(shù)器的左邊4個(gè)字節(jié)值 n 必須不大于2個(gè)字節(jié)所能存儲(chǔ)的最大值，否則的話斷言就失敗了，然后MySQL就掛掉了，就掛掉了，就掛掉了～

也就是說如果 row_id 用完了之后MySQL就會(huì)掛掉，那種程序直接退出的掛掉～ 不過6個(gè)字節(jié)已經(jīng)足夠大了，大家可以算算如果想讓MySQL掛掉需要插入多少條記錄呢？