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

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

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

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

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

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

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

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)
);

當然是沒有任何問題的。

因為 不顯示定義主鍵 != 沒有主鍵。

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

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

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

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

B+ 樹索引總覽

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

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

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

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

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

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

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

也就是說，B+ 樹的葉子節(jié)點存儲真正的記錄，而非葉子節(jié)點的存在是為了更快速的找到對應記錄所在的葉子節(jié)點。如下圖是一個高度為 2 的 B+ 樹：

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

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

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

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

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

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

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

主鍵和聚集索引的關系

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

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

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

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

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

事實上，主鍵和索引就不是一個層次的東西！

主鍵是一種約束，這個約束用來強制表的實體完整性，一個表中只能有一個主鍵約束，并且主鍵約束中的列值必須是非空且唯一的。

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

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

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

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

聚集索引和輔助索引的關系

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

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

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

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

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

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

當通過輔助索引來尋找數(shù)據(jù)時，InnoDB 存儲引擎會先遍歷輔助索引并通過葉子節(jié)點獲得某個主鍵對應的聚集索引鍵，然后再通過聚集索引來找到一個完整的行記錄。

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

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