注意：下面討論的都是MySQL5.6版本中的innodb引擎。

比較規(guī)范的數(shù)據(jù)庫表設(shè)計(jì)（包括我們公司）都會(huì)有一條不成文的規(guī)定，那就是給每張表一個(gè)自增主鍵。那么自增主鍵除了有數(shù)據(jù)的唯一性外，還有什么所用呢？為什么要有自增主鍵？

之前我轉(zhuǎn)發(fā)的《58到家數(shù)據(jù)庫30條軍規(guī)解讀》中解釋道：

第三條先不必關(guān)注，我們來看看前兩條。 為什么能提高插入性能呢，避免page分頁又是怎么回事？

這里就不得不說一下聚集索引了。

聚集索引（Clustered Index）

一個(gè)聚集索引定義了表中數(shù)據(jù)的物理存儲(chǔ)順序。如何理解聚集索引呢，好比一個(gè)電話本，比如一個(gè)電話本是按照姓氏排序，并且電話號(hào)碼緊跟著后面。因?yàn)榫奂饕龥Q定了表中數(shù)據(jù)的物理存儲(chǔ)順序，那么一個(gè)表則有且只有一個(gè)聚集索引。一個(gè)聚集索引可以包含多個(gè)列。好比一個(gè)電話本是基于名字，姓氏同時(shí)排序。

Innodb的聚集索引

Innodb的存儲(chǔ)索引是基于B+tree，理所當(dāng)然，聚集索引也是基于B+tree。與非聚集索引的區(qū)別則是，聚集索引既存儲(chǔ)了索引，也存儲(chǔ)了行值。當(dāng)一個(gè)表有一個(gè)聚集索引，它的數(shù)據(jù)是存儲(chǔ)在索引的葉子頁（leaf pages）。因此innodb也能理解為基于索引的表。

那么innodb如何決定那個(gè)索引作為聚集索引呢？

innodb如何選擇一個(gè)聚集索引

對(duì)于innodb，主鍵毫無疑問是一個(gè)聚集索引。但是當(dāng)一個(gè)表沒有主鍵，或者沒有一個(gè)索引，innodb會(huì)如何處理呢。請(qǐng)看如下規(guī)則

如果一個(gè)主鍵被定義了，那么這個(gè)主鍵就是作為聚集索引

如果沒有主鍵被定義，那么該表的第一個(gè)唯一非空索引被作為聚集索引

如果沒有主鍵也沒有合適的唯一索引，那么innodb內(nèi)部會(huì)生成一個(gè)隱藏的主鍵作為聚集索引，這個(gè)隱藏的主鍵是一個(gè)6個(gè)字節(jié)的列，改列的值會(huì)隨著數(shù)據(jù)的插入自增。

還有一個(gè)需要注意的是：

次級(jí)索引的葉子節(jié)點(diǎn)并不存儲(chǔ)行數(shù)據(jù)的物理地址。而是存儲(chǔ)的該行的主鍵值。

所以：一次級(jí)索引包含了兩次查找。一次是查找次級(jí)索引自身。然后查找主鍵（聚集索引）

現(xiàn)在應(yīng)該明白了吧，建立自增主鍵的原因是：innodb中的每張表都會(huì)有一個(gè)聚集索引，而聚集索引又是以物理磁盤順序來存儲(chǔ)的，自增主鍵會(huì)把數(shù)據(jù)自動(dòng)向后插入，避免了插入過程中的聚集索引排序問題。聚集索引的排序，必然會(huì)帶來大范圍的數(shù)據(jù)的物理移動(dòng)，這里面帶來的磁盤IO性能損耗是非常大的。 而如果聚集索引上的值可以改動(dòng)的話，那么也會(huì)觸發(fā)物理磁盤上的移動(dòng)，于是就可能出現(xiàn)page分裂，表碎片橫生。

解讀中的第二點(diǎn)相信看了上面關(guān)于聚集索引的解釋后就很清楚了。

雖然遵循上面的原則也沒錯(cuò)，但某些特殊的情況也是可以自己指定一些非自增主鍵為聚集索引的。如： 當(dāng)數(shù)據(jù)量大，但長時(shí)間不會(huì)被更新的； 新生成的數(shù)據(jù)的索引本來就是按照自增的順序增加的等等。

舉個(gè)栗子，（只是栗子啊，現(xiàn)實(shí)中不太可能）： 有一家公司里的員工上百萬，有關(guān)員工的個(gè)人信息幾年都不會(huì)發(fā)生變化，那么以員工的user_id號(hào)來作為各個(gè)表的索引就很適合。因?yàn)橐粋€(gè)員工的信息都按照物理順序呢排列在一起了，避免了磁盤移動(dòng)查找數(shù)據(jù)的IO時(shí)間。比如說員工屬于幾個(gè)部門，一下子就查到了，不同分不同的地址去挨個(gè)進(jìn)行磁盤掃描。

栗子從簡，哈哈哈哈，就這樣子吧，我也不太會(huì)寫，簡單的記錄下來了~