「Mysql索引原理（六）」聚簇索引

? ?本節(jié)課主要關(guān)注InnoDB，但是這里討論的原理對(duì)于任何支持聚簇索引的存儲(chǔ)引擎都是適用的。

創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括曲沃網(wǎng)站建設(shè)、曲沃網(wǎng)站制作、曲沃網(wǎng)頁制作以及曲沃網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，曲沃網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到曲沃省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

? ?葉子節(jié)點(diǎn)包含了全部數(shù)據(jù)，其他節(jié)點(diǎn)只包含索引列。InnoDB將通過主鍵聚集數(shù)據(jù)，也就是說上圖中的“被索引的列”就是主鍵列。如果沒有定義主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替。如果沒有這樣的索引InnoDB會(huì)隱式定義一個(gè)主鍵來作為聚簇索引。

? ?如果主鍵比較大的話，那輔助索引將會(huì)變的更大，因?yàn)?輔助索引的葉子存儲(chǔ)的是主鍵值；過長(zhǎng)的主鍵值，會(huì)導(dǎo)致非葉子節(jié)點(diǎn)占用占用更多的物理空間

所以建議使用int的auto_increment作為主鍵

? ?主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲(chǔ)在上一條記錄的后面。當(dāng)達(dá)到頁的最大值時(shí)，下一條記錄就會(huì)寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會(huì)近似于被順序的記錄填滿。

? ?聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會(huì)干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁，當(dāng)然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡(jiǎn)單了，它只需要一 頁一頁地寫，索引結(jié)構(gòu)相對(duì)緊湊，磁盤碎片少，效率也高。

? ?因?yàn)镸yISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進(jìn)行I/O讀取，于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。（強(qiáng)烈的對(duì)比）

? ?不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢(shì)些，因?yàn)樗饕伎臻g小，這些操作是需要在內(nèi)存中完成的。

? ?MyISM使用的是非聚簇索引， 非聚簇索引的兩棵B+樹看上去沒什么不同 ，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲(chǔ)的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點(diǎn)存儲(chǔ)了主鍵，輔助鍵索引B+樹存儲(chǔ)了輔助鍵。表數(shù)據(jù)存儲(chǔ)在獨(dú)立的地方，這兩顆B+樹的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)，對(duì)于表數(shù)據(jù)來說，這兩個(gè)鍵沒有任何差別。由于 索引樹是獨(dú)立的，通過輔助鍵檢索無需訪問主鍵的索引樹 。

? ?所以說，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設(shè)置。 一般要根據(jù)這個(gè)表最常用的SQL查詢方式來進(jìn)行選擇，某個(gè)字段作為聚簇索引，或組合聚簇索引 ，這個(gè)要看實(shí)際情況。

? ?聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，主鍵索引和二級(jí)索引的數(shù)據(jù)分布也有區(qū)別，通常會(huì)讓人感到困擾和以外，下面通過一個(gè)列子來講解InnoDB和MyISAM是如何存儲(chǔ)數(shù)據(jù)的：

? ?該表的主鍵取值1~10000，按照隨機(jī)順序插入并使用optimize table命令做了優(yōu)化。換句話說，數(shù)據(jù)在磁盤上的存儲(chǔ)方式已是最優(yōu)，但行的順序是隨機(jī)的。列col2的值是從1~100之間隨機(jī)賦值，所以有很多重復(fù)的值。

? ?MyISAM的數(shù)據(jù)分布很簡(jiǎn)單，所以先介紹它。MyISAM按照數(shù)據(jù)插入的順序存儲(chǔ)在磁盤上，如下圖所示：

在行的旁邊顯示行號(hào)，從0開始遞增。因?yàn)樾惺嵌ㄩL(zhǎng)的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行。

col2上的索引

? ?事實(shí)上，MyISAM中主鍵索引和其他索引在結(jié)構(gòu)上沒有什么不同。主鍵索引就是一個(gè)名為PRIMARY的唯一非空索引。

? ?InnoDB支持聚簇索引，所以使用不同的方式存儲(chǔ)同樣的數(shù)據(jù)。

? ?第一眼看上去，感覺和前面的沒什么區(qū)別，但是該圖顯示了整個(gè)表，而不是只有索引。因?yàn)樵贗nnoDB中，聚簇索引就是表，所以不像MyISAM那樣需要獨(dú)立的行存儲(chǔ)，這也是為什么MyISAM索引和數(shù)據(jù)結(jié)構(gòu)是分開的。

? ?聚簇索引的每一個(gè)葉子節(jié)點(diǎn)都包含了主鍵值。事務(wù)ID、用于事務(wù)和MVCC的回滾指針以及所有的剩余列。如果主鍵是一個(gè)列前綴索引，InnoDB也會(huì)包含完整的主鍵列和剩下的其他列。

? ?還有一點(diǎn)和MyISAM不同的是，InnoDB的二級(jí)索引和聚簇索引很不相同。InnoDB的二級(jí)索引的葉子節(jié)點(diǎn)中存儲(chǔ)的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。這樣的策略減少了當(dāng)出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁分裂時(shí)二級(jí)索引的維護(hù)工作。使用主鍵值當(dāng)作指針會(huì)讓二級(jí)索引占用更多的空間，換來的好處是，InnoDB在移動(dòng)時(shí)無需更新二級(jí)索引中的這個(gè)“指針”。

? ?我們?cè)趤砜匆幌?col2索引 。

? ?每一個(gè)葉子節(jié)點(diǎn)包含了索引列（這里是col2），緊接著是主鍵值（col1），上圖我們省略了非葉子節(jié)點(diǎn)這樣的細(xì)節(jié)。InnoDB非葉子節(jié)點(diǎn)包含了索引列和一個(gè)指向下一級(jí)節(jié)點(diǎn)的指針。

? ?最后，以一張圖表示InnoDB和MyISAM保存數(shù)據(jù)和索引的區(qū)別。

? ?前面講過，最好使用AUTO_INCREMENT自增列來聚集數(shù)據(jù)，避免隨機(jī)的、不連續(xù)的、值分布范圍大的列做聚簇索引，特別是對(duì)于I/O密集型的應(yīng)用。例如，從性能角度考慮，使用UUID來作為聚簇索引則會(huì)很糟糕：他使得聚簇索引的插入變得完全隨機(jī)，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。

? ?為了演示這一點(diǎn)，我們做兩個(gè)基準(zhǔn)測(cè)試：

1、使用證書ID插入userinfo表，和uuid作為主鍵的userinfo_uuid表

? ?userinfo_uuid表跟userinfo表除了主鍵給為UUID，其他字段都一樣

? ?測(cè)試這兩個(gè)表的設(shè)計(jì)，首先在一個(gè)有足夠內(nèi)存容納索引的服務(wù)器上向這兩個(gè)表各插入100萬條記錄。然后向兩個(gè)表繼續(xù)插入300萬數(shù)據(jù)，使索引的大小超過服務(wù)器的內(nèi)存容量。測(cè)試結(jié)果如下：

? ?向UUID主鍵插入行不僅花費(fèi)的時(shí)間更長(zhǎng)，而且索引占用的空間也更大。這一方面是由于主鍵字段更長(zhǎng)，另一方面毫無疑問是由于頁分裂和碎片導(dǎo)致的。

? ?為了明白為什么會(huì)這樣，來看看往第一個(gè)表中插入數(shù)據(jù)時(shí)，索引發(fā)生了什么變化。

自整型主鍵插入

? ?因?yàn)橹麈I是順序的，所以InnoDB把每一條記錄都存在上一條記錄的后面。當(dāng)達(dá)到頁的最大容量后，下一條記錄就會(huì)寫入到新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會(huì)近似于被順序的記錄填滿，這也正是所期望的結(jié)果。

UUID插入

? ?因?yàn)樾滦械闹麈I值不一定比之前插入的大，所以InnoDB無法簡(jiǎn)單的總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置，通常是已有數(shù)據(jù)的中間位置，并且分配空間。這會(huì)正價(jià)很多的額外工作，并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化。

缺點(diǎn)：

把這些隨機(jī)值載入到聚簇索引后，也許需要做一次OPTIMIZE TABLE來重建表并優(yōu)化頁的填充。

結(jié)論 ：使用InnoDB時(shí)應(yīng)盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地單調(diào)增加聚簇鍵的值來插入新行。

聚簇索引

??mysql的索引策略中有一條是聚簇索引，而聚簇索引并不是唯一索引，普通索引之類的索引類型，而是一種數(shù)據(jù)的存儲(chǔ)方式。大多數(shù)索引存在的形式為B-tree，葉子節(jié)點(diǎn)的索引則和其對(duì)應(yīng)的數(shù)據(jù)行數(shù)據(jù)緊湊的存儲(chǔ)在一起，這就是術(shù)語聚簇的含義。實(shí)現(xiàn)數(shù)據(jù)存儲(chǔ)形式的是存儲(chǔ)引擎，但并不是所有存儲(chǔ)引擎都支持聚簇索引，而著名的InnoDB則是支持的引擎之一，下面都以InnoDB為例。

??而存儲(chǔ)引擎不能管理兩份同樣的數(shù)據(jù)，所以聚簇索引在同一張數(shù)據(jù)表中只能存在一個(gè)，其他的索引只能是非聚簇索引，也就是二級(jí)索引。數(shù)據(jù)表的如果有指定primary key，那么InnoDB就會(huì)把primary key作為聚簇索引來存儲(chǔ)，如果沒有，則會(huì)取第一個(gè)not null，unique的索引作為聚簇索引，unique的索引也不存在的話，InnoDB就會(huì)自行的，隱式的指定一個(gè)row ID列作為聚簇索引存儲(chǔ)，但這個(gè)row ID不會(huì)被用戶管理。

聚簇索引一些重要的優(yōu)點(diǎn)：

??1. 在有聚簇索引的數(shù)據(jù)表中，使用聚簇索引進(jìn)行查詢的時(shí)候，因?yàn)樗饕蛿?shù)據(jù)聚集在同一個(gè)B-tree中，能夠直接從索引獲取到數(shù)據(jù)行，比非聚簇索引的性能要好。

??2. 反之在沒有聚簇索引的數(shù)據(jù)表中，因?yàn)椴荒芡ㄟ^unique的值去聚集數(shù)據(jù)，所以需要通過非聚簇索引查詢數(shù)據(jù)的物理地址或者全表掃描來獲取數(shù)據(jù)，這樣每一行數(shù)據(jù)可能都會(huì)導(dǎo)致一次磁盤I/O。

在提升性能的同時(shí)，聚簇索引也存在著缺點(diǎn)：

??1. 更新聚簇索引的代價(jià)會(huì)很大，因?yàn)樾枰獙?shù)據(jù)行和主鍵進(jìn)行重排，移動(dòng)到新的位置，并且二級(jí)索引可能也需要更新。

??2. 聚簇索引的插入速度嚴(yán)重依賴插入順序，嚴(yán)格的升序主鍵是性能最好的方式，但如果主鍵是亂序的插入，例如用uuid作為主鍵，當(dāng)主鍵值需要插入到某一頁已經(jīng)寫滿的page中，存儲(chǔ)引擎就需要將page分裂成兩個(gè)頁面來容納數(shù)據(jù)，這一個(gè) 頁分裂(page split) 操作，page split會(huì)使得數(shù)據(jù)表占用更多的磁盤空間。

??3. 通過二級(jí)索引獲取需要兩次索引查找，因?yàn)槎?jí)索引保存的是聚簇索引的主鍵，而不是指向數(shù)據(jù)的邏輯指針，所以獲取主鍵后需要再進(jìn)行一次搜索才能獲取數(shù)據(jù)。

??聚簇索引和二級(jí)索引的數(shù)據(jù)分布方式不同，在MyISAM和InnoDB的數(shù)據(jù)文件組織方式中也有體現(xiàn)。

??MyISAM的數(shù)據(jù)由3個(gè)文件組成：1. .frm(表結(jié)構(gòu)描述文件) ，2. .MYD(數(shù)據(jù)行文件) ，3. .MYI(索引文件) 。

??InnoDB則有2個(gè)文件：1. .frm(表結(jié)構(gòu)描述文件) ，2. .MYD(數(shù)據(jù)行文件和索引信息) 。

??MyISAM引擎沒有使用索引和數(shù)據(jù)的聚集的分布方式，所以主鍵和其他索引的是沒有區(qū)別，就都存儲(chǔ)在索引文件中。

InnoDB的鎖機(jī)制是使用索引來實(shí)現(xiàn)，表現(xiàn)的等級(jí)為行級(jí)鎖，而MyISAM則是表級(jí)鎖，這也跟數(shù)據(jù)分布方式有關(guān)。InnoDB的主鍵索引與數(shù)據(jù)緊湊的聚集在一起，并且包含了事務(wù)ID，用于事務(wù)MVCC的回滾指針，而MyISAM則是數(shù)據(jù)與索引分離，無法實(shí)現(xiàn)如此細(xì)粒度的鎖。

??1. InnoDB暫時(shí)不能由用戶選定索引作為聚簇索引，InnoDB有自己的聚簇索引選取規(guī)則，所以在創(chuàng)建表的時(shí)候最好設(shè)置一個(gè)與業(yè)務(wù)無關(guān)的主鍵id作為聚簇索引，這樣修改二級(jí)索引和數(shù)據(jù)的時(shí)候，無需移動(dòng)數(shù)據(jù)位置，提升性能。

??2. 聚簇索引的主鍵id不要使用uuid，uuid會(huì)使得數(shù)據(jù)的插入添加額外的頁分裂操作，降低性能，最好使用單調(diào)自增的id。

MySql 索引（聚集索引，輔助索引，聯(lián)合索引，覆蓋索引..）

引入一個(gè)面試問題:

看完以下以后再回顧，會(huì)發(fā)現(xiàn)迎刃而解

Mysql 可以為每一張表設(shè)置 存儲(chǔ)引擎 這里我們只說 InnoDB 存儲(chǔ)引擎.

由于實(shí)際情況，數(shù)據(jù)頁只能按照一棵 B+樹 進(jìn)行排序， 因此每張表只能擁有一個(gè) 聚集索引（即 主鍵）。

栗子：

每個(gè)葉子節(jié)點(diǎn)的索引行中包含了一個(gè)書簽（bookmark）. 該書簽是用來告訴 InnoDB存儲(chǔ)引擎哪里可以找到該索引對(duì)應(yīng)的數(shù)據(jù)行或者說 行數(shù)據(jù)！ 由于InnoDB存儲(chǔ)引擎表， 是按照主鍵來構(gòu)建的， 所以 ，該書簽內(nèi)其實(shí)包含或者說指向了 數(shù)據(jù)行所對(duì)應(yīng)的聚集索引鍵

也就是說 輔助索引的 葉結(jié)點(diǎn)保存了 指向?qū)?yīng)數(shù)據(jù)的 聚集索引， 可以通過該聚集索引 找到對(duì)應(yīng)的數(shù)據(jù)行

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因?yàn)槊繌埍砩峡梢杂卸鄠€(gè)輔助索引。

當(dāng)通過輔助索引來尋找數(shù)據(jù)時(shí)，InnoDB 存儲(chǔ)引擎會(huì)遍歷輔助索引并通過葉級(jí)別的指針獲得指向主鍵索引(聚集索引)的主鍵，然后再通過聚集索引找到一個(gè)完整的數(shù)據(jù)行。

例如：

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

： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創(chuàng)建索引的方式一樣，不同的是 可以同時(shí)添加多列來作為索引項(xiàng);

從本質(zhì)上來說，聯(lián)合索引也是一課B+樹

個(gè)人理解： 所謂最左原則， 是因?yàn)?存儲(chǔ)引擎構(gòu)建組合索引時(shí) 是根據(jù)最左邊的那一列索引項(xiàng)進(jìn)行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項(xiàng)，這樣 才可以找到對(duì)應(yīng)的索引，繼而 去尋找對(duì)應(yīng)的數(shù)據(jù)

： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

比如 這里沒有根據(jù)最左原則使用組合索引，但是 優(yōu)化器依然進(jìn)行選擇

共勉，歡迎指導(dǎo)謝謝~