自增列id是怎樣的

這篇文章將為大家詳細講解有關(guān)自增列id是怎樣的，文章內容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

在使用建表時(shí)，我們通常會(huì )創(chuàng )建一個(gè)自增字段(AUTO_INCREMENT)，并以此字段作為主鍵。

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

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

• 如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì )選擇主鍵作為聚集索引、如果沒(méi)有顯式定義主鍵，則InnoDB會(huì )選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引、如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì )選擇內置6字節長(cháng)的ROWID作為隱含的聚集索引(ROWID隨著(zhù)行記錄的寫(xiě)入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

• 數據記錄本身被存于主索引（一顆B+Tree）的葉子節點(diǎn)上。這就要求同一個(gè)葉子節點(diǎn)內（大小為一個(gè)內存頁(yè)或磁盤(pán)頁(yè)）的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入時(shí)，MySQL會(huì )根據其主鍵將其插入適當的節點(diǎn)和位置，如果頁(yè)面達到裝載因子（InnoDB默認為15/16），則開(kāi)辟一個(gè)新的頁(yè)（節點(diǎn)）

• 如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì )順序添加到當前索引節點(diǎn)的后續位置，當一頁(yè)寫(xiě)滿(mǎn)，就會(huì )自動(dòng)開(kāi)辟一個(gè)新的頁(yè)

• 如果使用非自增主鍵（如果身份證號或學(xué)號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁(yè)得中間某個(gè)位置，此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數據，甚至目標頁(yè)面可能已經(jīng)被回寫(xiě)到磁盤(pán)上而從緩存中清掉，此時(shí)又要從磁盤(pán)上讀回來(lái)，這增加了很多開(kāi)銷(xiāo)，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。

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

2.自增列id一定是連續的嗎？

自增id是增長(cháng)的 不一定連續。

我們先來(lái)看下MySQL 對自增值的保存策略：

InnoDB 引擎的自增值，其實(shí)是保存在了內存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實(shí)現了“如果發(fā)生重啟，表的自增值可以恢復為 MySQL 重啟前的值”，具體情況是：

在 MySQL 5.7 及之前的版本，自增值保存在內存里，并沒(méi)有持久化。每次重啟后，第一次打開(kāi)表的時(shí)候，都會(huì )去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個(gè)表當前的自增值。?

舉例來(lái)說(shuō)，如果一個(gè)表當前數據行里最大的 id 是 10，AUTO_INCREMENT=11。這時(shí)候，我們刪除 id=10 的行，AUTO_INCREMENT 還是 11。但如果馬上重啟實(shí)例，重啟后這個(gè)表的 AUTO_INCREMENT 就會(huì )變成 10。?

也就是說(shuō)，MySQL 重啟可能會(huì )修改一個(gè)表的 AUTO_INCREMENT 的值。

在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時(shí)候依靠 redo log 恢復重啟之前的值。

造成自增id不連續的情況可能有：

• 1.唯一鍵沖突

• 2.事務(wù)回滾

• 3.insert … select語(yǔ)句批量申請自增id

3.自增id有上限嗎？

自增id是整型字段，我們常用int類(lèi)型來(lái)定義增長(cháng)id，而int類(lèi)型有上限 即增長(cháng)id也是有上限的。

下表列舉下 int 與 bigint 字段類(lèi)型的范圍：

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

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

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í)驗可以看出，當自增id達到最大時(shí)將無(wú)法擴展，第一個(gè) insert 語(yǔ)句插入數據成功后，這個(gè)表的AUTO_INCREMENT 沒(méi)有改變（還是 4294967295），就導致了第二個(gè) insert 語(yǔ)句又拿到相同的自增 id 值，再試圖執行插入語(yǔ)句，報主鍵沖突錯誤。

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

維護方面主要提供以下2點(diǎn)建議：

• 1.字段類(lèi)型選擇方面：推薦使用int無(wú)符號類(lèi)型，若可預測該表數據量將非常大 可改用bigint無(wú)符號類(lèi)型。

• 2.多關(guān)注大表的自增值，防止發(fā)生主鍵溢出情況。