前言

今天同事做數據清理的時候發現我這邊有一張表沒有主鍵，這個表有兩個字段，ID和Name，ID作者唯一索引，在我印象里，一個數據表如果沒有主鍵索引，它會內部創建主鍵索引，創建的標準就是唯一性，我覺得應該是使用ID創建內部的主鍵索引。 所以，會有兩個問題

第一個問題，如果確實使用ID創建了內部的主鍵索引，直接通過ID查詢確實只會有一次 B+ 樹的搜索，這樣的話唯一索引其實就是沒有意義的浪費。

第二個問題，如果不是通過ID創建內部的主鍵索引，而且通過其他方式內建的主鍵索引，那么 ID查詢就需要就需要先通過二級索引 ID 搜索 B+ 樹 查詢到主鍵索引，再通過主鍵索引搜索B+樹查詢到數據記錄，得需要兩次的 索引樹的查詢。

所以這種做法怎么著都是不合理的。 下午補習了丁奇老師的 MYSQL45講 的關于索引的的兩節 深入淺出索引，做個筆記

innodb 索引模型

我們都知道，InnoDB 使用了 B+ 樹索引模型，數據都是存儲在 B+ 樹的葉子節點上的。每一個索引都會對應一個 B+ 樹。 簡單的建表語句，ID是主鍵索引，k是普通索引，對應兩顆B+ 樹

( id int primary key, k int not null, name varchar

索引k是1對應的ID是100，以此類推，2對應200，3對應300... 可以看到 主鍵索引的葉子節點存的是整行數據，也稱聚簇索引 非主鍵索引的葉子節點內容是主鍵的值，也稱二級索引

根據上面的索引結構說明，我們來討論一個問題：基于主鍵索引和普通索引的查詢有什么區別？

• 如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹；
• 如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次。這個過程稱為回表。
• 也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該盡量使用主鍵查詢。

索引維護

B+ 樹為了維護索引有序性，在插入新值的時候需要做必要的維護。以上面這個圖為例，如果插入新的行 ID 值為 700，則只需要在 R5 的記錄后面插入一個新記錄。如果新插入的 ID 值為 400，就相對麻煩了，需要邏輯上挪動后面的數據，空出位置。 而更糟的情況是，如果 R5 所在的數據頁已經滿了，根據 B+ 樹的算法，這時候需要申請一個新的數據頁，然后挪動部分數據過去。這個過程稱為頁分裂。在這種情況下，性能自然會受影響。 除了性能外，頁分裂操作還影響數據頁的利用率。原本放在一個頁的數據，現在分到兩個頁中，整體空間利用率降低大約 50%。當然有分裂就有合并。當相鄰兩個頁由于刪除了數據，利用率很低之后，會將數據頁做合并。合并的過程，可以認為是分裂過程的逆過程。

基于上面的索引維護過程說明，我們來討論一個案例：

你可能在一些建表規范里面見到過類似的描述，要求建表語句里一定要有自增主鍵。當然事無絕對，我們來分析一下哪些場景下應該使用自增主鍵，而哪些場景下不應該。

自增主鍵是指自增列上定義的主鍵，在建表語句中一般是這么定義的： NOT NULL PRIMARY KEY AUTO_INCREMENT。 插入新記錄的時候可以不指定 ID 的值，系統會獲取當前 ID 最大值加 1 作為下一條記錄的 ID 值。 也就是說，自增主鍵的插入數據模式，正符合了我們前面提到的遞增插入的場景。每次插入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。 而有業務邏輯的字段做主鍵，則往往不容易保證有序插入，這樣寫數據成本相對較高。

除了考慮性能外，我們還可以從存儲空間的角度來看。假設你的表中確實有一個唯一字段，比如字符串類型的身份證號，那應該用身份證號做主鍵，還是用自增字段做主鍵呢？ 由于每個非主鍵索引的葉子節點上都是主鍵的值。如果用身份證號做主鍵，那么每個二級索引的葉子節點占用約 20 個字節，而如果用整型做主鍵，則只要 4 個字節，如果是長整型（bigint）則是 8 個字節。

顯然，主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小。

所以，從性能和存儲空間方面考量，自增主鍵往往是更合理的選擇。有沒有什么場景適合用業務字段直接做主鍵的呢？還是有的。 比如，有些業務的場景需求是這樣的： 只有一個索引；該索引必須是唯一索引。 你一定看出來了，這就是典型的 KV 場景。由于沒有其他索引，所以也就不用考慮其他索引的葉子節點大小的問題。 這時候我們就要優先考慮上一段提到的“盡量使用主鍵查詢”原則，直接將這個索引設置為主鍵，可以避免每次查詢需要搜索兩棵樹。

總結

以上是生活随笔為你收集整理的mysql建表语句主键自增_MYSQL索引-上的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。