高性能索引優(yōu)化策略（四）：聚集索引和非聚集索引數(shù)據(jù)結(jié)構(gòu)及優(yōu)劣對比

聚集索引并不是一個(gè)單獨(dú)的索引類型歹鱼，實(shí)際上是一種數(shù)據(jù)存儲的方式。聚集索引的實(shí)現(xiàn)細(xì)節(jié)各有不同，在InnoDB引擎中驮配，聚集索引實(shí)際上是將索引和數(shù)據(jù)行使用同樣的結(jié)構(gòu)存儲在一個(gè)二叉樹中熏纯。

如果數(shù)據(jù)表中有聚集索引同诫，則數(shù)據(jù)行實(shí)際上是存在所以的葉子節(jié)點(diǎn)≌晾剑“聚集”的形式實(shí)際是指相鄰鍵值的數(shù)據(jù)行是鄰近存儲的误窖。因?yàn)橐恍袛?shù)據(jù)不能存儲在兩個(gè)葉子節(jié)點(diǎn)上，因此在數(shù)據(jù)表中只能有一個(gè)聚集索引秩贰。由于是存儲引擎負(fù)責(zé)索引的實(shí)現(xiàn)霹俺，因此并不是全部的存儲引擎都支持聚集索引。在這里我們只討論InnoDB毒费，但支持聚集索引的存儲引擎實(shí)現(xiàn)聚集索引的原理都大同小異丙唧。

下圖展示了數(shù)據(jù)記錄在聚集索引的存儲布局。注意蝗罗，葉子節(jié)點(diǎn)包含了完整的數(shù)據(jù)行艇棕，而其他節(jié)點(diǎn)僅僅只有索引。在這個(gè)圖中串塑，索引列使用的是整數(shù)沼琉。

聚集索引數(shù)據(jù)存儲

有些數(shù)據(jù)庫服務(wù)器允許我們選擇對哪個(gè)索引進(jìn)行聚集，但MySQL的任意內(nèi)置的存儲引擎都不支持這么做桩匪。InnoDB使用主鍵對數(shù)據(jù)進(jìn)行聚集打瘪，這意味著上圖的索引列實(shí)際上是主鍵列。

如果數(shù)據(jù)表沒有定義主鍵傻昙，InnoDB會選擇使用唯一的非空列（Not Null）索引替代闺骚。如果沒有這樣的索引，InnoDB會定義一個(gè)隱藏的主鍵去完成數(shù)據(jù)聚集（因此妆档，數(shù)據(jù)表最好自己定義主鍵）僻爽。InnoDB的只能在一個(gè)數(shù)據(jù)頁中進(jìn)行數(shù)據(jù)聚集，因此即便是臨近的索引值的數(shù)據(jù)存儲頁也可能間隔很遠(yuǎn)贾惦。

一個(gè)聚集主鍵能夠提高性能胸梆，但同樣也可能導(dǎo)致嚴(yán)重的性能問題敦捧。因此，你應(yīng)當(dāng)謹(jǐn)慎考慮聚集的使用碰镜，尤其是當(dāng)你將一個(gè)數(shù)據(jù)表的存儲引擎從InnoDB改為其他引擎時(shí)兢卵。

聚集索引具有如下的優(yōu)勢：

可以將相關(guān)聯(lián)的數(shù)據(jù)行保持鄰近存儲。例如绪颖，郵箱應(yīng)用中秽荤，你可以使用user_id進(jìn)行聚集。這種情況下柠横，你可以從磁盤的很少的分頁中獲取用戶的全部消息窃款。而如果不使用聚集索引，每條消息都可能需要單獨(dú)占用磁盤I/O牍氛。
數(shù)據(jù)訪問很快：聚集索引將索引和數(shù)據(jù)同時(shí)存儲在二叉樹中雁乡，因此從聚集索引中獲取數(shù)據(jù)行比起非聚集索引的查詢來說快很多。
使用了覆蓋索引的查詢可以利用主鍵中包含在葉子節(jié)點(diǎn)的數(shù)據(jù)值糜俗。

如果你在設(shè)計(jì)數(shù)據(jù)表和查詢時(shí)充分利用這些好處，將能顯著提升性能曲饱。然而悠抹，聚集索引也有缺點(diǎn)：

聚集能對受限于I/O負(fù)載的情況很大改善，但如果數(shù)據(jù)是在內(nèi)存而與磁盤無關(guān)扩淀，那聚集實(shí)際上幫不上什么忙楔敌。
插入速度嚴(yán)重依賴插入次序。按照主鍵順序插入數(shù)據(jù)是InnoDB表中最快的方式驻谆，如果加載數(shù)據(jù)不是按照主鍵次序卵凑，那在完成大量數(shù)據(jù)的加載后，最好是使用優(yōu)化表（OPTIMIZE TABLE）功能重新組織一下數(shù)據(jù)表胜臊。
更新聚集索引列的代價(jià)很高勺卢，因?yàn)檫@會強(qiáng)制InnoDB去移動更新的數(shù)據(jù)行到一個(gè)新的位置。
當(dāng)新的數(shù)據(jù)行插入時(shí)或行的主鍵更新引起數(shù)據(jù)行移動象对，數(shù)據(jù)表已經(jīng)構(gòu)建的聚集索引會分頁（page split）黑忱。分頁情況發(fā)生在新的數(shù)據(jù)行需要移入一個(gè)放滿數(shù)據(jù)的存儲頁時(shí)。存儲引擎必須將存儲頁分成兩部分去存儲新的行勒魔，這會導(dǎo)致數(shù)據(jù)表占用更多的磁盤空間甫煞。
聚集表在全表掃描時(shí)可能會更慢，尤其是數(shù)據(jù)行密集度不高或者是因?yàn)榉猪搶?dǎo)致存儲不連續(xù)冠绢。
非聚集索引（Secondary Index）會比你預(yù)期的存儲要大抚吠，這是因?yàn)槿~子節(jié)點(diǎn)包含了主鍵列引用的數(shù)據(jù)行。
非聚集索引訪問時(shí)需要兩次索引查找而不是一次弟胀。這個(gè)可能感覺有點(diǎn)費(fèi)解楷力，其原因在于非聚集索引存儲的是數(shù)據(jù)行指針喊式。記住，葉子節(jié)點(diǎn)并并不是存儲引用行的實(shí)際物理位置弥雹，而是該行的主鍵值垃帅。這意味著，使用非聚集索引查詢數(shù)據(jù)行時(shí)剪勿，存儲引擎首先通過非聚集索引找到其葉子節(jié)點(diǎn)贸诚，然后通過葉子節(jié)點(diǎn)的主鍵再找到數(shù)據(jù)行的值。這相當(dāng)于進(jìn)行了兩次二叉樹的查找厕吉，在InnoDB中酱固，自適應(yīng)的哈希索引可以減少這種概率。

InnoDB和MyISAM數(shù)據(jù)布局比較

聚集和非聚集數(shù)據(jù)布局头朱，以及主鍵和非聚集索引的差別可能讓人困惑和奇怪运悲。我們可以比較一下InnoDB和MyISAM對下面數(shù)據(jù)表的存儲布局來深入了解一下。

CREATE TABLE layout_test (
  col1 int NOT NULL,
  col2 int NOT NULL,
  PRIMARY KEY(col1),
  KEY(col2)
);

假設(shè)這個(gè)表產(chǎn)生了主鍵1到10000的數(shù)據(jù)项钮，采用的是隨機(jī)順序插入的班眯，然后在通過OPTIMIZE TABLE進(jìn)行了優(yōu)化。換言之烁巫，數(shù)據(jù)在磁盤上是有序排列的署隘，但行可能是隨機(jī)順序的。數(shù)據(jù)列col2使用1-100的隨機(jī)值填充亚隙，因此存在很多的重復(fù)值磁餐。

MyISAM的數(shù)據(jù)布局更簡單些，MyISAM按照插入的順序在磁盤存儲數(shù)據(jù)阿弃，如下圖所示诊霹。我們以0開始展示了數(shù)據(jù)行編號，由于每一行的大小是固定的渣淳，MyISAM可以從表最開始的地方根據(jù)所需要的字節(jié)數(shù)來找到任意行（MyISAM內(nèi)部并不總是使用行號脾还，而是根據(jù)行是否固定大小或可變大小使用不同的策略）。

數(shù)據(jù)次序

這種結(jié)構(gòu)使其很容易構(gòu)建索引入愧，下圖繪制了一個(gè)數(shù)據(jù)系列荠呐，這個(gè)圖中，物理的細(xì)節(jié)（例如存儲頁）被抽象隱藏砂客，在索引中只有節(jié)點(diǎn)泥张。每個(gè)索引的葉子節(jié)點(diǎn)可以簡單地包含對應(yīng)的行號，在下圖是其主鍵鞠值。在這里隱藏了一些細(xì)節(jié)媚创，例如在前一個(gè)節(jié)點(diǎn)后有少個(gè)內(nèi)部的二叉樹節(jié)點(diǎn)，但這對于理解非聚集存儲引擎基礎(chǔ)的數(shù)據(jù)布局來說并不重要彤恶。

MyISAM聚集索引結(jié)構(gòu)

那對于col2列的索引怎么樣钞钙。實(shí)際上鳄橘，它和其他索引一樣。

MyISAM非聚集索引結(jié)構(gòu)

事實(shí)上芒炼，在MyISAM中瘫怜，主鍵和其他索引并沒有結(jié)構(gòu)上的區(qū)別。主鍵只是一個(gè)簡單的唯一本刽、不為空的索引鲸湃，僅僅是名字命名為主鍵而已。

InnoDB由于聚集索引的組織方式子寓，存儲同樣數(shù)據(jù)的結(jié)構(gòu)十分不同暗挑，如下圖所示。

InnDB聚集索引結(jié)構(gòu)

初看這張圖斜友，感覺似乎和MyISAM的并無太大不同炸裆，但是仔細(xì)再看一遍，實(shí)際上這個(gè)圖展示了整張數(shù)據(jù)表鲜屏，而不只是索引烹看。由于聚集索引在InnoDB中已經(jīng)是整張表了，因此這里沒有像在MyISAM中的獨(dú)立的行存儲洛史。

InnoDB的聚集索引每個(gè)葉子節(jié)點(diǎn)都包含主鍵值听系、事務(wù)ID、回滾指針以便進(jìn)行事務(wù)和MVCC（Multi Version Cocurrent Control, 并發(fā)多版本控制）虹菲，以及剩下的其他列（示例數(shù)據(jù)表就是col2）。如果主鍵作用在一個(gè)列的前綴上掉瞳，InnoDB會在其他列中包含主鍵列的完整值毕源。

同樣，與MyISAM相比陕习，非聚集索引和聚集索引有很大不同霎褐。相比于存儲行指針，InnoDB非聚集索引的葉子節(jié)點(diǎn)存儲的是主鍵值该镣，由主鍵再指向數(shù)據(jù)冻璃。這個(gè)策略在行移動或數(shù)據(jù)分頁需要維護(hù)非聚集索引時(shí)，減少了很多工作损合。使用行主鍵值作為指針意味著索引更大省艳，但也意味著InnoDB可以移動行而不需要去更新非聚集索引的指針。下圖展示了非聚集索引的數(shù)據(jù)布局嫁审，可以看到非聚集索引實(shí)際上存儲了主鍵的值跋炕。

InnoDB非聚集索引結(jié)構(gòu)

這些圖展示了二叉樹的葉子節(jié)點(diǎn)，但是我們隱藏了非葉子節(jié)點(diǎn)的細(xì)節(jié)律适。InnoDB中非葉子節(jié)點(diǎn)的二叉樹的每一個(gè)節(jié)點(diǎn)都包含索引列辐烂，并另外附加了下一個(gè)層級的節(jié)點(diǎn)的指針（有可能是非葉子節(jié)點(diǎn)也可能是葉子節(jié)點(diǎn)）遏插。這個(gè)對聚集索引和非聚集索引都一樣。下圖展示了InnoDB和MyISAM的索引的抽象結(jié)構(gòu)對比纠修，從中可以看出二者的不同之處胳嘲。

InnoDB和MyISAM存儲結(jié)構(gòu)對比