> 導讀：在使用MySQL建表時，我們通常會建立一個自增欄位(AUTO_INCREMENT)，並以此欄位作為主鍵。本篇文章將以問答的形式講述關於自增id的一切。

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

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

* 如果我們定義了主鍵(PRIMARY KEY)，那麼InnoDB會選擇主鍵作為聚集索引、如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、如果也沒有這樣的唯一索引，則InnoDB會選擇內建6位元組長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

* 資料記錄本身被存於主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點內（大小為一個記憶體頁或磁碟頁）的各條資料記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開闢一個新的頁（節點）

* 如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁

* 如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動資料，甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉，此時又要從磁碟上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不透過OPTIMIZE TABLE來重建表並最佳化填充頁面。

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

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

自增id是增長的 不一定連續。

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

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

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

* 1.唯一鍵衝突
* 2.事務回滾
* 3.insert ... select語句批次申請自增id

#### 3.自增id有上限嗎？

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

下表列舉下 `int` 與 `bigint` 欄位型別的範圍：

|  型別  | 大小  |               範圍（有符號）               |      範圍（無符號）      |
| :----: | :---: | :----------------------------------------: | :----------------------: |
|  int   | 4位元組 |          (-2147483648,2147483647)          |      (0,4294967295)      |
| bigint | 8位元組 | (-9223372036854775808,9223372036854775807) | (0,18446744073709551615) |

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

下面我們測試下當自增id達到最大時再次插入資料會怎麼樣：

```mysql
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'
```

從實驗可以看出，當自增id達到最大時將無法擴充套件，第一個 insert 語句插入資料成功後，這個表的AUTO_INCREMENT 沒有改變（還是 4294967295），就導致了第二個 insert 語句又拿到相同的自增 id 值，再試圖執行插入語句，報主鍵衝突錯誤。

#### 4.關於自增列 我們該怎麼維護？

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

* 1.欄位型別選擇方面：推薦使用int無符號型別，若可預測該表資料量將非常大 可改用bigint無符號型別。
* 2.多關注大表的自增值，防止發生主鍵溢位情況。

關於自增id，你可能還不知道

相關文章