導(dǎo)讀：在使用MySQL建表時(shí)，我們通常會(huì)創(chuàng)建一個(gè)自增字段(AUTO_INCREMENT)，并以此字段作為主鍵。本篇文章將以問(wèn)答的形式講述關(guān)于自增id的一切。
注： 本文所講的都是基于Innodb存儲(chǔ)引擎。
1.MySQL為什么建議將自增列id設(shè)為主鍵？
- 如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì)選擇主鍵作為聚集索引、如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引、如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。
- 數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點(diǎn)上。這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè)）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子（InnoDB默認(rèn)為15/16），則開辟一個(gè)新的頁(yè)（節(jié)點(diǎn)）
- 如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)
- 如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置，此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時(shí)又要從磁盤上讀回來(lái)，這增加了很多開銷，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。
綜上而言：當(dāng)我們使用自增列作為主鍵時(shí)，存取效率是最高的。
2.自增列id一定是連續(xù)的嗎？
自增id是增長(zhǎng)的 不一定連續(xù)。
我們先來(lái)看下MySQL 對(duì)自增值的保存策略：
InnoDB 引擎的自增值，其實(shí)是保存在了內(nèi)存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實(shí)現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為 MySQL 重啟前的值”，具體情況是：
在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里，并沒(méi)有持久化。每次重啟后，第一次打開表的時(shí)候，都會(huì)去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個(gè)表當(dāng)前的自增值。
舉例來(lái)說(shuō)，如果一個(gè)表當(dāng)前數(shù)據(jù)行里最大的 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 恢復(fù)重啟之前的值。
造成自增id不連續(xù)的情況可能有：
- 1.唯一鍵沖突
- 2.事務(wù)回滾
- 3.insert ... select語(yǔ)句批量申請(qǐng)自增id
3.自增id有上限嗎？
自增id是整型字段，我們常用int類型來(lái)定義增長(zhǎng)id，而int類型有上限 即增長(zhǎng)id也是有上限的。
下表列舉下 int 與 bigint 字段類型的范圍：
| 類型 |
大小 |
范圍（有符號(hào)） |
范圍（無(wú)符號(hào)） |
| int |
4字節(jié) |
(-2147483648,2147483647) |
(0,4294967295) |
| bigint |
8字節(jié) |
(-9223372036854775808,9223372036854775807) |
(0,18446744073709551615) |
從上表可以看出：當(dāng)自增字段使用int有符號(hào)類型時(shí)，最大可達(dá)2147483647即21億多；使用int無(wú)符號(hào)類型時(shí)，最大可達(dá)4294967295即42億多。當(dāng)然bigint能表示的范圍更大。
下面我們測(cè)試下當(dāng)自增id達(dá)到最大時(shí)再次插入數(shù)據(jù)會(huì)怎么樣：
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'
從實(shí)驗(yàn)可以看出，當(dāng)自增id達(dá)到最大時(shí)將無(wú)法擴(kuò)展，第一個(gè) insert 語(yǔ)句插入數(shù)據(jù)成功后，這個(gè)表的AUTO_INCREMENT 沒(méi)有改變（還是 4294967295），就導(dǎo)致了第二個(gè) insert 語(yǔ)句又拿到相同的自增 id 值，再試圖執(zhí)行插入語(yǔ)句，報(bào)主鍵沖突錯(cuò)誤。
4.關(guān)于自增列 我們?cè)撛趺淳S護(hù)？
維護(hù)方面主要提供以下2點(diǎn)建議：
- 1.字段類型選擇方面：推薦使用int無(wú)符號(hào)類型，若可預(yù)測(cè)該表數(shù)據(jù)量將非常大 可改用bigint無(wú)符號(hào)類型。
- 2.多關(guān)注大表的自增值，防止發(fā)生主鍵溢出情況。
以上就是關(guān)于mysql自增id，你需要知道的的詳細(xì)內(nèi)容，更多關(guān)于mysql自增id的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL的自增ID(主鍵) 用完了的解決方法
- MySQL表自增id溢出的故障復(fù)盤解決
- 關(guān)于MySQL自增ID的一些小問(wèn)題總結(jié)
- 關(guān)于Mysql自增id的這些你可能還不知道
- mysql自增id超大問(wèn)題的排查與解決
- MySQL分表自增ID問(wèn)題的解決方法
- 線上MySQL的自增id用盡怎么辦
