MySql中InnoDB表要用自增列做主鍵的原因是什么

InnoDB引擎表的特點(diǎn)

1、InnoDB引擎表是基于B+樹(shù)的索引組織表(IOT)

關(guān)于B+樹(shù)

B+ 樹(shù)的特點(diǎn):

• 所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中(稠密索引)，且鏈表中的關(guān)鍵字恰好是有序的;

• 不可能在非葉子結(jié)點(diǎn)命中;

• 非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引(稀疏索引)，葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)(關(guān)鍵字)數(shù)據(jù)的數(shù)據(jù)層;

2、主鍵(PRIMARY KEY)

如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì)選擇主鍵作為聚集索引、如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引、如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫(xiě)入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

3、主索引

數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點(diǎn)上。這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè)）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子（InnoDB默認(rèn)為15/16），則開(kāi)辟一個(gè)新的頁(yè)（節(jié)點(diǎn)）

4、自增主鍵

如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫(xiě)滿，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)

5、非自增主鍵

如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置，此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫(xiě)到磁盤上而從緩存中清掉，此時(shí)又要從磁盤上讀回來(lái)，這增加了很多開(kāi)銷，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。

總結(jié)

如果InnoDB表的數(shù)據(jù)寫(xiě)入順序能和B+樹(shù)索引的葉子節(jié)點(diǎn)順序一致的話，這時(shí)候存取效率是最高的，也就是下面這幾種情況的存取效率最高：
1、使用自增列(INT/BIGINT類型)做主鍵，這時(shí)候?qū)懭腠樞蚴亲栽龅?，和B+數(shù)葉子節(jié)點(diǎn)分裂順序一致；

2、該表不指定自增列做主鍵，同時(shí)也沒(méi)有可以被選為主鍵的唯一索引(上面的條件)，這時(shí)候InnoDB會(huì)選擇內(nèi)置的ROWID作為主鍵，寫(xiě)入順序和ROWID增長(zhǎng)順序一致；
除此以外，如果一個(gè)InnoDB表又沒(méi)有顯示主鍵，又有可以被選擇為主鍵的唯一索引，但該唯一索引可能不是遞增關(guān)系時(shí)(例如字符串、UUID、多字段聯(lián)合唯一索引的情況)，該表的存取效率就會(huì)比較差。