MySQL 為什么InnoDB表要建議用自增列做主鍵

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

我們先了解下InnoDB引擎表的一些關(guān)鍵特征：

• InnoDB引擎表是基于B+樹的索引組織表(IOT)；
• 每個(gè)表都需要有一個(gè)聚集索引(clustered index)；
• 所有的行記錄都存儲(chǔ)在B+樹的葉子節(jié)點(diǎn)(leaf pages of the tree)；
• 基于聚集索引的增、刪、改、查的效率相對(duì)是最高的；
• 如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì)選擇其作為聚集索引；
• 如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引；
• 如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

綜上總結(jié)，如果InnoDB表的數(shù)據(jù)寫入順序能和B+樹索引的葉子節(jié)點(diǎn)順序一致的話，這時(shí)候存取效率是最高的，也就是下面這幾種情況的存取效率最高：

• 使用自增列(INT/BIGINT類型)做主鍵，這時(shí)候?qū)懭腠樞蚴亲栽龅?，和B+數(shù)葉子節(jié)點(diǎn)分裂順序一致；
• 該表不指定自增列做主鍵，同時(shí)也沒(méi)有可以被選為主鍵的唯一索引(上面的條件)，這時(shí)候InnoDB會(huì)選擇內(nèi)置的ROWID作為主鍵，寫入順序和ROWID增長(zhǎng)順序一致；
• 除此以外，如果一個(gè)InnoDB表又沒(méi)有顯示主鍵，又有可以被選擇為主鍵的唯一索引，但該唯一索引可能不是遞增關(guān)系時(shí)(例如字符串、UUID、多字段聯(lián)合唯一索引的情況)，該表的存取效率就會(huì)比較差。

實(shí)際情況是如何呢？經(jīng)過(guò)簡(jiǎn)單TPCC基準(zhǔn)測(cè)試，修改為使用自增列作為主鍵與原始表結(jié)構(gòu)分別進(jìn)行TPCC測(cè)試，前者的TpmC結(jié)果比后者高9%倍，足見使用自增列做InnoDB表主鍵的明顯好處，其他更多不同場(chǎng)景下使用自增列的性能提升可以自行對(duì)比測(cè)試下。

附圖：

1、B+樹典型結(jié)構(gòu)

2、InnoDB主鍵邏輯結(jié)構(gòu)

延伸閱讀：

1、TPCC-MySQL使用手冊(cè)

2、B+Tree index structures in InnoDB

3、B+Tree Indexes and InnoDB – Percona

4、MySQL官方手冊(cè): Clustered and Secondary Indexes