InnoDB Primary Key 與 UUID 以及 Auto-Increment Integer
由於有時候會當 DBA，大概整理一下小心得，徵求專家熱心補充更正。 XD
其實，當我們在討論 MySQL，大多不是在討論 MyISAM，而是 InnoDB。 如果有人直言 MySQL 很爛，卻沒辦法說清楚 MyISAM 跟 InnoDB 的差別，那麼，請別相信他。XD
在 InnoDB 裡，primary key 的設計對 storage efficiency 有極大的影響。 有些系統會將 UUID 設為 primary key，其實這對資料庫效能會有極大的衝擊。
InnoDB 處理 storage 的方式，是靠 clustered index，而 InnoDB 會優先將 primary key 設為 clustered index，若沒有，則選擇 unique index，若還是沒有 unique index，InnoDB 會挑出適當的 row value 作為 hidden clustered index。
在 clustered index 的 leaf node 中，不像 MyISAM 儲存的是 row number，而是，clustered index 本身 “就是” 這個 table，也因此 clustered index 的 leaf node 其實不僅儲存了 primary key columns, 還儲存了 TID, RP 以及其他欄位。
了解了 clustered index 的運作方式，再來討論 UUID，UUID 基本上是 36 個英數字元組成，透過 unhex 後，可使用 BINARY(16) 儲存。UUID 的產生大概又分為幾種，random / ordered / timestamp based uuid ，不管是哪種方式，亂數分佈的成分一定在，這會導致在 row insert 的時候，沒有辦法以 sequential 的方式寫入。
由於 UUID 在 insert 的時候，不一定會比先前的鍵值更大，也因此 InnoDB 必須要計算出適當的位置來安插新的資料。 而目標頁 (Page) 很有可能已經寫入到磁碟中，且從快取中移除，或尚未在快取內，而 InnoDB 必須在每次寫入時去做頁查找，如此行為會消耗大量的 random I/O.
不連續的插入行為，也會導致 InnoDB 必須不斷的分頁 (split pages) 來創造更多的空間來安插新的資料，也因此創建出來的 pages 會是相當稀疏且有許多碎片。
再來是 secondary index，secondary index 其實就是我們平常建在 table 上的 BTREE / HASH Index。
在 InnoDB 的 index 中，index 的 leaf node 不儲存 row pointer，取而代之，他儲存的是 primary key 的值，找到值之後，再用這個 primary key value 去 clustered index 找出對應的 row data。
也就是說，secondary index 的 left node 中不僅儲存了 index 本身的鍵值，還會把 primary key columns 也儲存進去。
這樣講就很明瞭了，primary key 儲存的值越大，同時會影響到所有 secondary index 的大小。 把 BINARY(16) UUID 設定為 primary key，所有的 secondary index 也都要將 BINARY(16) UUID 儲存進 index 才行。
這樣聽起來好像很無感，如果 primary key 要多存 16Bytes , secondary index 也要多存 16Bytes，這樣隨便一個 10M 個 rows 加起來就多 320MB 了，你如果用大整數 BIGINT 儲存，不僅速度快，還能省下不少空間。
如果你曾搜尋過一些 benchmarking 做 bigint auto_inc 跟 uuid insertion speed 的比較，雖能看出 I/O 效能，卻沒有確切的解釋。
而透過了解 clustered index 應該就能明暸為何 bigint auto_inc 做 insertion 速度是最快最穩。 因為，即使是透過時間序列產生的 UUID 在 insertion 的效能表現上也未如 unsigned int + auto increment 佳。
所以，即使在表格內已有 UUID，一般還是會建議在 MySQL 中另外建立一個 column ，使用 unsigned int + auto_increment 做 primary key，一方面可以穩定 insertion speed，另一方面讓索引的查詢更快、分布平均、也讓索引更小。
