InnoDB的主鍵選擇與插入優化

 在使用InnoDB存儲引擎時，如果沒有特别的需要，請永遠使用一個與業務無關的自增字段作為主鍵。

經常看到有文章或部落格讨論主鍵選擇問題，有人建議使用業務無關的自增主鍵，有人覺得沒有必要，完全可以使用如學号或身份證号這種唯一字段作為主鍵。 不論支援哪種論點，大多數論據都是業務層面的。如果從資料庫索引優化角度看，使用InnoDB引擎而不使用自增主鍵絕對是一個糟糕的主意。

上文讨論過InnoDB的索引實作，InnoDB使用聚集索引，資料記錄本身被存于主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節 點内（大小為一個記憶體頁或磁盤頁）的各條資料記錄按主鍵順序存放，是以每當有一條新的記錄插入時，MySQL會根據其主鍵将其插入适當的節點和位置，如果 頁面達到裝載因子（InnoDB預設為15/16），則開辟一個新的頁（節點）。

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

圖13

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

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

圖14

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

是以，隻要可以，請盡量在InnoDB上采用自增字段做主鍵。

本文轉自 位鵬飛 51CTO部落格，原文連結：http://blog.51cto.com/weipengfei/1256156，如需轉載請自行聯系原作者