共計(jì) 2291 個字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。
這篇文章將為大家詳細(xì)講解有關(guān)自增列 id 是怎樣的，文章內(nèi)容質(zhì)量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。
在使用 MySQL 建表時，我們通常會創(chuàng)建一個自增字段 (AUTO_INCREMENT)，并以此字段作為主鍵。
注：本文所講的都是基于 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é)點(diǎn)上。這就要求同一個葉子節(jié)點(diǎn)內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時，MySQL 會根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁面達(dá)到裝載因子（InnoDB 默認(rèn)為 15/16），則開辟一個新的頁（節(jié)點(diǎn)）
如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁寫滿，就會自動開辟一個新的頁
如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個位置，此時 MySQL 不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，甚至目標(biāo)頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過 OPTIMIZE TABLE 來重建表并優(yōu)化填充頁面。
綜上而言：當(dāng)我們使用自增列作為主鍵時，存取效率是最高的。
2. 自增列 id 一定是連續(xù)的嗎？
自增 id 是增長的 不一定連續(xù)。
我們先來看下 MySQL 對自增值的保存策略：
InnoDB 引擎的自增值，其實(shí)是保存在了內(nèi)存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實(shí)現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為 MySQL 重啟前的值”，具體情況是：
在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個表當(dāng)前的自增值。?
舉例來說，如果一個表當(dāng)前數(shù)據(jù)行里最大的 id 是 10，AUTO_INCREMENT=11。這時候，我們刪除 id=10 的行，AUTO_INCREMENT 還是 11。但如果馬上重啟實(shí)例，重啟后這個表的 AUTO_INCREMENT 就會變成 10。?
也就是說，MySQL 重啟可能會修改一個表的 AUTO_INCREMENT 的值。
在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復(fù)重啟之前的值。
造成自增 id 不連續(xù)的情況可能有：
1. 唯一鍵沖突
2. 事務(wù)回滾
3.insert … select 語句批量申請自增 id
3. 自增 id 有上限嗎？
自增 id 是整型字段，我們常用 int 類型來定義增長 id，而 int 類型有上限 即增長 id 也是有上限的。
下表列舉下
int 與
bigint 字段類型的范圍：
類型大小范圍（有符號）范圍（無符號）int4 字節(jié) (-2147483648,2147483647)(0,4294967295)bigint8 字節(jié) (-9223372036854775808,9223372036854775807)(0,18446744073709551615)
從上表可以看出：當(dāng)自增字段使用 int 有符號類型時，最大可達(dá) 2147483647 即 21 億多；使用 int 無符號類型時，最大可達(dá) 4294967295 即 42 億多。當(dāng)然 bigint 能表示的范圍更大。
下面我們測試下當(dāng)自增 id 達(dá)到最大時再次插入數(shù)據(jù)會怎么樣：
create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
// 成功插入一行 4294967295
show 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從實(shí)驗(yàn)可以看出，當(dāng)自增 id 達(dá)到最大時將無法擴(kuò)展，第一個 insert 語句插入數(shù)據(jù)成功后，這個表的 AUTO_INCREMENT 沒有改變（還是 4294967295），就導(dǎo)致了第二個 insert 語句又拿到相同的自增 id 值，再試圖執(zhí)行插入語句，報(bào)主鍵沖突錯誤。
4. 關(guān)于自增列 我們該怎么維護(hù)？
維護(hù)方面主要提供以下 2 點(diǎn)建議：
1. 字段類型選擇方面：推薦使用 int 無符號類型，若可預(yù)測該表數(shù)據(jù)量將非常大 可改用 bigint 無符號類型。
2. 多關(guān)注大表的自增值，防止發(fā)生主鍵溢出情況。
關(guān)于自增列 id 是怎樣的就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
