Are You OK？主鍵、聚集索引、輔助索引

每張表都一定存在主鍵嗎？

關(guān)于這個(gè)問題，各位小伙伴們不妨先自己想一想，再往下尋找答案。

首先公布結(jié)論：對(duì)于 InnoDB 存儲(chǔ)引擎來說，每張表都一定有個(gè)主鍵（Primary Key）！

讓人非常遺憾的是，網(wǎng)絡(luò)上至今仍然有非常多的文章是這樣的描述：“一張表中必須有聚集索引，但不一定需要主鍵”。前半句是正確的，后半句是大錯(cuò)特錯(cuò)！

對(duì)于 InnoDB 存儲(chǔ)引擎來說，表采用的存儲(chǔ)方式稱為索引組織表（index organizedtable），也即表都是根據(jù)主鍵的順序來進(jìn)行組織存放的。如果主鍵都沒有，表怎么存？

那下面這段沒定義主鍵的建表語句是正確的嗎？

CREATE TABLE test(
 a INT NOT NULL,
 b INT NULL,
 c INT NOT NULL,
 d INT NOT NULL,
 UNIQUE KEY(b),
 UNIQUE KEY(d),
 UNIQUE KEY(c)
);

當(dāng)然是沒有任何問題的。

因?yàn)?不顯示定義主鍵 != 沒有主鍵。

如果在創(chuàng)建表時(shí)沒有顯式地定義主鍵，InnoDB 存儲(chǔ)引擎會(huì)按如下方式選擇或創(chuàng)建主鍵：

• 首先判斷表中是否有非空的唯一索引（Unique NOT NULL），如果有，則該列即為主鍵
• 如果不符合上述條件，InnoDB 存儲(chǔ)引擎自動(dòng)創(chuàng)建一個(gè) 6 字節(jié)大小的指針 _rowid 作為主鍵

那如果表中有多個(gè)非空唯一索引時(shí)怎么辦呢？ InnoDB 存儲(chǔ)引擎將選擇建表時(shí)第一個(gè)定義的非空唯一索引為主鍵。需要注意的是！主鍵的選擇根據(jù)的是非空唯一索引定義的順序，而不是建表時(shí)列的順序。

比如上面那段代碼，有 a、b、c、d 四個(gè)列，b、c、d 三列上都有唯一索引。不過 b 列不是非空的，所以不可能成為主鍵了。而 d 列首先被定義為非空的唯一索引，所以 InnoDB 存儲(chǔ)引擎將其視為主鍵。

B+ 樹索引總覽

InnoDB 存儲(chǔ)引擎支持以下幾種常見的索引：

• B+ 樹索引
• 全文索引
• 哈希索引

所謂哈希索引也就是得益于哈希算法的快速查找特性，不過哈希索引的致命缺點(diǎn)就是無法范圍查詢。并且 InnoDB 中哈希索引是自適應(yīng)的，也就是說 InnoDB 存儲(chǔ)引擎會(huì)根據(jù)表的使用情況自動(dòng)為表生成哈希索引，不能人為干預(yù)是否在一張表中生成哈希索引。

全文索引本文先暫且不做贅述。

再來看 B+ 樹索引，B+ 樹索引的本質(zhì)就是 B+ 樹在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)，它是目前關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)中查找最為常用的索引。

關(guān)于 B+ 樹的數(shù)據(jù)結(jié)構(gòu)我就不詳細(xì)說了，B 代表平衡（Balance），而不是二叉（Binary），B+ 樹是從最早的平衡二叉樹演化而來的，但是 B+ 樹不是一個(gè)二叉樹。

簡(jiǎn)單介紹下：B+ 樹是為磁盤或其他直接存取輔助設(shè)備設(shè)計(jì)的一種平衡查找樹。在 B+ 樹中，所有記錄節(jié)點(diǎn)都是按鍵值的大小順序存放在同一層的葉子節(jié)點(diǎn)上，各葉子節(jié)點(diǎn)之間通過雙向鏈表進(jìn)行連接。

也就是說，B+ 樹的葉子節(jié)點(diǎn)存儲(chǔ)真正的記錄，而非葉子節(jié)點(diǎn)的存在是為了更快速的找到對(duì)應(yīng)記錄所在的葉子節(jié)點(diǎn)。如下圖是一個(gè)高度為 2 的 B+ 樹：

另外，需要注意的是，B+ 樹索引并不能找到一個(gè)給定鍵值的具體“行”！B+ 樹索引能找到的只是被查找數(shù)據(jù)行所在的“頁”。然后數(shù)據(jù)庫(kù)通過把頁讀入到內(nèi)存，再在內(nèi)存中進(jìn)行查找，最后得到要查找的數(shù)據(jù)。

肯定有些小伙伴會(huì)懵逼了，“頁” 又是什么東西？

這就得說到 InnoDB 存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)。

InnoDB 存儲(chǔ)引擎中，所有數(shù)據(jù)都被邏輯地存放在一個(gè)空間中，稱之為 表空間（tablespace），也就是說我們常說的表，可以看作是 InnoDB 存儲(chǔ)引擎邏輯結(jié)構(gòu)的最高層。表空間又由 段（segment）、區(qū)（extent）、頁（page） 組成（頁有時(shí)也稱為塊 block）。如下圖：

頁是 InnoDB 磁盤管理的最小單位，在 InnoDB 存儲(chǔ)引擎中，默認(rèn)每個(gè)頁的大小為 16KB。而頁里面存放的東西就是一行一行的記錄。

我們接下來要說的 聚集索引（clustered inex）和輔助索引（secondary index）其實(shí)都是一種 B+ 樹索引。也就是說不管是聚集索引還是輔助索引，其內(nèi)部都是 B+樹，即高度平衡的，葉子節(jié)點(diǎn)存放著所有的數(shù)據(jù)。（需要注意的是，索引是存儲(chǔ)引擎負(fù)責(zé)實(shí)現(xiàn)的，因此不是所有的存儲(chǔ)引擎都支持聚簇索引）

聚集索引與輔助索引不同之處就是，葉子節(jié)點(diǎn)存放的是否是一整行的信息。下文我們會(huì)詳細(xì)解釋。

主鍵和聚集索引的關(guān)系

先來看聚集索引，上面我們說過，InnoDB 存儲(chǔ)引擎表是索引組織表結(jié)構(gòu)，即表中數(shù)據(jù)都是按照主鍵順序進(jìn)行存放的。而聚集索引就是按照每張表的主鍵構(gòu)造一棵 B+ 樹，同時(shí)葉子節(jié)點(diǎn)中存放的即為表中一行一行的數(shù)據(jù)，所以聚集索引的葉子節(jié)點(diǎn)也被稱為數(shù)據(jù)節(jié)點(diǎn)。

也就是說，聚集索引能夠在 B+ 樹索引的葉子節(jié)點(diǎn)上直接找到數(shù)據(jù)。并且由于定義了數(shù)據(jù)的邏輯順序，查詢優(yōu)化器能夠快速發(fā)現(xiàn)到底是哪一段范圍的數(shù)據(jù)頁需要掃描。比如用戶需要查詢一張用戶表，查詢最后注冊(cè)的 10 位用戶，由于 B+ 樹索引的葉子節(jié)點(diǎn)是基于雙向鏈表的，所以用戶可以快速找到最后一個(gè)數(shù)據(jù)頁，并取出 10 條記錄。這也就是為什么大部分情況下查詢優(yōu)化器傾向于采用聚集索引了。

可以這么說：在聚集索引中，索引即數(shù)據(jù)，數(shù)據(jù)即索引。

另外，由于數(shù)據(jù)頁只能按照一棵 B+ 樹進(jìn)行查找排序，或者說無法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以每張表只能擁有一個(gè)聚集索引。

講了這么多，好像還沒講到主鍵和聚集索引有啥區(qū)別。一張表只能有一個(gè)主鍵，并且也只能有一個(gè)聚集索引，聚集索引還是按照主鍵來構(gòu)建的，那這種種跡象不都表明主鍵就是聚集索引？

事實(shí)上，主鍵和索引就不是一個(gè)層次的東西！

主鍵是一種約束，這個(gè)約束用來強(qiáng)制表的實(shí)體完整性，一個(gè)表中只能有一個(gè)主鍵約束，并且主鍵約束中的列值必須是非空且唯一的。

而聚集索引它作為一種索引，其目的不是為了約束啥，而是為了對(duì)數(shù)據(jù)行進(jìn)行排序以提高查詢的效率，換句話說它決定的是數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)。

? 形象點(diǎn)說，一個(gè)沒加聚集索引的表，它的數(shù)據(jù)是一行一行 無序 地存放在磁盤存儲(chǔ)器上的。而如果給表添加了聚集索引，那么表在磁盤上的存儲(chǔ)結(jié)構(gòu)就由一行一行排列的結(jié)構(gòu)轉(zhuǎn)變成了 樹狀結(jié)構(gòu)，也就是 B+ 樹結(jié)構(gòu)，換句話說，就是整個(gè)表就變成了一個(gè)索引，也就是上面提到的 “索引即數(shù)據(jù)，數(shù)據(jù)即索引”。

而至于 “主鍵就是索引” 這種觀點(diǎn)的由來，是因?yàn)椋篒nnoDB 存儲(chǔ)引擎中，每張表都一定存在主鍵（顯示或隱式），而聚集索引依賴于主鍵的建立，所以如果沒有強(qiáng)制指定使用非聚集索引，InnoDB 在創(chuàng)建主鍵的同時(shí)會(huì)建立一個(gè)唯一的聚集索引（也有些文章稱之為 主鍵索引）。

所以，不要說 “主鍵就是聚集索引”，應(yīng)該這樣說：“聚集索引一般都是加在主鍵上的”。

聚集索引和輔助索引的關(guān)系

輔助索引（Secondary Index）也稱為 非聚集索引、二級(jí)索引。其和聚集索引的最大區(qū)別就在于，輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)。

簡(jiǎn)單來說，一行記錄我們可以用 “主鍵 + 其他數(shù)據(jù)” 這樣的組合來標(biāo)識(shí)，聚集索引中的葉子節(jié)點(diǎn)存儲(chǔ)的就是這一整個(gè)組合，而非聚集索引中的葉子節(jié)點(diǎn)只存儲(chǔ)了這個(gè)組合中的主鍵，那其他數(shù)據(jù)我怎么獲得呢？

非聚集索引的葉子節(jié)點(diǎn)說還包含了一個(gè) 書簽（bookmark），該書簽用來告訴 InnoDB 存儲(chǔ)引擎哪里可以找到與索引相對(duì)應(yīng)的行數(shù)據(jù)。

那各位不妨想一想，行數(shù)據(jù)存儲(chǔ)在哪里呢？

沒錯(cuò)，上文說過，聚集索引中的葉子節(jié)點(diǎn)中存放的就是表中一行一行的數(shù)據(jù)，所以 InnoDB 存儲(chǔ)引擎的輔助索引中的書簽其實(shí)就是相應(yīng)行數(shù)據(jù)的聚集索引鍵。

也就是說，輔助索引的葉子節(jié)點(diǎn)包含的是：每行數(shù)據(jù)的主鍵 + 該行數(shù)據(jù)對(duì)應(yīng)的聚集索引鍵。

當(dāng)通過輔助索引來尋找數(shù)據(jù)時(shí)，InnoDB 存儲(chǔ)引擎會(huì)先遍歷輔助索引并通過葉子節(jié)點(diǎn)獲得某個(gè)主鍵對(duì)應(yīng)的聚集索引鍵，然后再通過聚集索引來找到一個(gè)完整的行記錄。

舉個(gè)例子，如果在一棵高度為 3 的輔助索引樹中查找數(shù)據(jù)，那需要對(duì)這棵輔助索引樹遍歷 3 次找到指定聚集索引鍵，如果聚集索引樹的高度同樣為 3，那么還需要對(duì)聚集索引樹進(jìn)行 3 次查找，最終找到一個(gè)完整的行數(shù)據(jù)所在的頁，因此一共需要 6 次邏輯 IO 訪問以得到最終的一個(gè)數(shù)據(jù)頁。

另外，很顯然的是，輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因此每張表上可以有多個(gè)輔助索引。