mysql - 为什么InnoDB表最好要有自增列做主键 ?

Question

为什么InnoDB表最好要有自增列做主键

Answer 1

先看看B+樹的特徵

InnoDB引擎表是基於B+樹的索引組織表(IOT)

關於B+樹

（圖片來自網路）

B+ 樹的特徵：

a、所有關鍵字都出現在葉子結點的鍊錶中(稠密索引)，且鍊錶中的關鍵字恰好是有序的;

b、不可能在非葉子結點命中;

c、非葉子結點相當於是葉子結點的索引(稀疏索引)，葉子結點相當於是儲存(關鍵字)資料的資料層。

1、如果我們定義了主鍵(PRIMARY KEY)

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

2、資料記錄本身被存於主索引（一顆B+Tree）的葉子節點上

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

3.如果表格使用自增主鍵

那麼每次插入新的記錄，記錄就會順序添加到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁

4、如果使用非自增主鍵（如果身分證號或學號等）

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

總結：如果InnoDB表的資料寫入順序能和B+樹索引的葉子節點順序一致的話，這時候訪問效率是最高的，也就是下面這幾種情況的​​訪問效率最高：

a、使用自增列(INT/BIGINT類型)做主鍵，這時候寫入順序是自增的，和B+數葉子節點分裂順序一致；

b、該表不指定自增列做主鍵，同時也沒有可以被選為主鍵的唯一索引(上面的條件)，這時候InnoDB會選擇內建的ROWID作為主鍵，寫入順序和ROWID增長順序一致；

c、如果一個InnoDB表又沒有顯示主鍵，又有可以被選擇為主鍵的唯一索引，但該唯一索引可能不是遞增關係時(例如字串、UUID、多字段聯合唯一索引的情況)，該表的存取效率就會比較差。

一下是來自《高效能MySQL》中的原話

Answer 2

原文網址：http://imysql.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml

我們先來了解下InnoDB引擎表的一些關鍵特徵：

InnoDB引擎表是基於B+樹的索引組織表(IOT)；
每個表都需要有一個聚集索引(clustered index)；
所有的行記錄都儲存在B+樹的葉子節點(leaf pages of the tree)；
基於聚集索引的增、刪、改、查的效率相對是最高的；
如果我們定義了主鍵(PRIMARY KEY)，那麼InnoDB會選擇其作為聚集索引；
如果沒有明確定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引；
如果也沒有這樣的唯一索引，則InnoDB會選擇內建6位元組長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。
綜上總結，如果InnoDB表的資料寫入順序能和B+樹索引的葉子節點順序一致的話，這時候存取效率是最高的，也就是下面這幾種情況的​​存取效率最高：

使用自增列(INT/BIGINT類型)做主鍵，這時候寫入順序是自增的，和B+數葉子節點分裂順序一致；
該表不指定自增列做主鍵，同時也沒有可以被選為主鍵的唯一索引(上面的條件)，這時候InnoDB會選擇內建的ROWID作為主鍵，寫入順序和ROWID增長順序一致；
除此以外，如果一個InnoDB表又沒有顯示主鍵，又有可以被選擇為主鍵的唯一索引，但該唯一索引可能不是遞增關係時(例如字串、UUID、多字段聯合唯一索引的情況)，該表的訪問效率就會比較差。
實際情況是如何呢？經過簡單TPCC基準測試，修改為使用自增列作為主鍵與原始表結構分別進行TPCC測試，前者的TpmC結果比後者高9%倍，足見使用自增列做InnoDB表主鍵的明顯好處，其他更多不同場景下使用自增列的效能提升可以自行對比測試下。

Answer 3

innodb是聚集索引，你不加主鍵，他也會加上一個隱性的主鍵。 innodb的資料表本身的資料就是一個按主鍵分佈的索引檔。
可以看一下這篇文章 http://segmentfault.com/a/1190000003046591