關(guān)于mysql自增id，你需要知道的

更新時間：2020年08月11日 10:52:40 作者：MySQL技術(shù)

這篇文章主要介紹了關(guān)于mysql自增id的相關(guān)資料，幫助大家更好的理解和學習mysql，感興趣的朋友可以了解下

導讀：在使用MySQL建表時，我們通常會創(chuàng)建一個自增字段(AUTO_INCREMENT)，并以此字段作為主鍵。本篇文章將以問答的形式講述關(guān)于自增id的一切。

注：本文所講的都是基于Innodb存儲引擎。

1.MySQL為什么建議將自增列id設(shè)為主鍵？

如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會選擇主鍵作為聚集索引、如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、如果也沒有這樣的唯一索引，則InnoDB會選擇內(nèi)置6字節(jié)長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。
數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點上。這就要求同一個葉子節(jié)點內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據(jù)其主鍵將其插入適當?shù)墓?jié)點和位置，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節(jié)點）
如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節(jié)點的后續(xù)位置，當一頁寫滿，就會自動開辟一個新的頁
如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現(xiàn)有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，甚至目標頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁面。

綜上而言：當我們使用自增列作為主鍵時，存取效率是最高的。

2.自增列id一定是連續(xù)的嗎？

自增id是增長的不一定連續(xù)。

我們先來看下MySQL 對自增值的保存策略：

InnoDB 引擎的自增值，其實是保存在了內(nèi)存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復為 MySQL 重啟前的值”，具體情況是：
在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個表當前的自增值。
舉例來說，如果一個表當前數(shù)據(jù)行里最大的 id 是 10，AUTO_INCREMENT=11。這時候，我們刪除 id=10 的行，AUTO_INCREMENT 還是 11。但如果馬上重啟實例，重啟后這個表的 AUTO_INCREMENT 就會變成 10。
也就是說，MySQL 重啟可能會修改一個表的 AUTO_INCREMENT 的值。
在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復重啟之前的值。

造成自增id不連續(xù)的情況可能有：

1.唯一鍵沖突
2.事務(wù)回滾
3.insert ... select語句批量申請自增id

3.自增id有上限嗎？

自增id是整型字段，我們常用int類型來定義增長id，而int類型有上限即增長id也是有上限的。

下表列舉下 int 與 bigint 字段類型的范圍：

類型	大小	范圍（有符號）	范圍（無符號）
int	4字節(jié)	(-2147483648,2147483647)	(0,4294967295)
bigint	8字節(jié)	(-9223372036854775808,9223372036854775807)	(0,18446744073709551615)

從上表可以看出：當自增字段使用int有符號類型時，最大可達2147483647即21億多；使用int無符號類型時，最大可達4294967295即42億多。當然bigint能表示的范圍更大。

下面我們測試下當自增id達到最大時再次插入數(shù)據(jù)會怎么樣：

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;insert into t values(null);// 成功插入一行 4294967295show create table t;/* CREATE TABLE `t` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295;*/
insert into t values(null);//Duplicate entry '4294967295' for key 'PRIMARY'

從實驗可以看出，當自增id達到最大時將無法擴展，第一個 insert 語句插入數(shù)據(jù)成功后，這個表的AUTO_INCREMENT 沒有改變（還是 4294967295），就導致了第二個 insert 語句又拿到相同的自增 id 值，再試圖執(zhí)行插入語句，報主鍵沖突錯誤。

4.關(guān)于自增列我們該怎么維護？

維護方面主要提供以下2點建議：