InnoDB 引擎爲什麼推薦使用自增 ID 作爲主鍵？

回答：MySQL InnoDB 引擎底層數據結構是 B+ 樹，所謂的索引其實就是一棵 B+ 樹，一個表有多少個索引就會有多少顆 B+ 樹，MySQL 中的數據都是按順序保存在 B+ 樹葉子節點上的。

MySQL 在底層又是以數據頁爲單位來存儲數據的，一個數據頁大小默認爲 16k，當然你也可以自定義大小，也就是說如果一個數據頁存滿了，MySQL 就會去申請一個新的數據頁來存儲數據。

當往一個快滿或已滿的數據頁中插入數據時，新插入的數據會將數據頁寫滿，MySQL 就需要申請新的數據頁，並且把上個數據頁中的部分數據挪到新的數據頁上。這就造成了頁分裂，這個大量移動數據的過程是會嚴重影響插入效率的。

自增 id 可以保證每次插入時 B + 索引是從右邊擴展的，可以避免 B + 樹頻繁合併和分裂（對比使用 UUID 而言）。如果使用字符串主鍵和隨機主鍵，會使得數據隨機插入，效率比較差。

一、InnoDB 中的 B + 樹

先理解 InnoDB 中的 B + 樹，如圖所示。

InnoDB 使用聚集索引，數據記錄本身被存於主索引（一顆 B + 樹）的葉子節點上。這就要求同一個葉子節點內（大小爲一個內存頁或磁盤頁）的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL 會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB 默認爲 15/16），則開闢一個新的頁（節點）。

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

這樣就會形成一個緊湊的索引結構，近似順序填滿。由於每次插入時也不需要移動已有數據，因此效率很高，也不會增加很多開銷在維護索引上。

如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁的中間某個位置：

此時 MySQL 不得不爲了將新記錄插到合適位置而移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁地移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過 OPTIMIZE TABLE 來重建表並優化填充頁面。

因此，只要可以，請儘量在 InnoDB 上採用自增字段做主鍵。

二、儘量使用更小的主鍵

在滿足業務需求的情況下，儘量使用佔空間更小的主鍵。

三、什麼時候不需用自增主鍵？

（1）數據量小

數據量很小，小到全表掃描效率比掃描索引樹要高時，不適合建立索引，就更沒有自增主鍵的必要了。

數據量千級，索引樹大小不大，對性能和空間影響都不會很大。

（2）KV 場景

在全表只有一個唯一索引（Key-Value 場景），且讀多寫少的前提下，應儘量避免查詢時回表（也就是搜索兩顆索引樹），這種情況可以考慮用業務字段做主鍵。

四、主鍵自增帶來的劣勢是什麼？

在高併發的場景下，自增主鍵也有一些弊端。

在 InnoDB 中按主鍵順序插入可能會造成明顯的爭用。主鍵上界會成爲” 熱點”，因爲所有的插入都發生在這裏，所以併發插入可能導致間隙鎖競爭。另一個熱點可能是 AUTO_INCREMENT 鎖機制：如果遇到這個問題，則可能需要考慮重新設計表或者應用，或者更改 innodb_autoinc_lock_mode 配置。

從 MySQL 5.1.22 版本開始，InnoDB 存儲引擎中提供了一種輕量級互斥量的自增長實現機制，這種機制大大提高了自增長值插入的性能。並且從該版本開始，InnoDB 存儲引擎提供了一個參數 innodb_autoinc_lock_mode（自增鎖模式）來控制自增長的模式，該參數的默認值爲 1 或者 2。

show variables like 'innodb_autoinc_lock_mode';

參數 innodb_autoinc_lock_mode 總共有三個有效值可供設定，即 0、1、2。從 MySQL 8.0 開始默認是 2。

三種模式簡要說明:

0：traditonal （每次都會產生表鎖）

1：consecutive （會產生一個輕量鎖，simple insert 會獲得批量的鎖，保證連續插入）

2：interleaved （不會鎖表，來一個處理一個，併發最高）

來源：

https://www.toutiao.com/a7028920796470952485/?log_from=f2ca5b7baf4e4_1637026606133

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/kl7lJmM5bXpdgnMir-GrxA

猜你喜歡