數(shù)據(jù)庫的聚簇索引是什么

這篇文章主要介紹“數(shù)據(jù)庫的聚簇索引是什么”，在日常操作中，相信很多人在數(shù)據(jù)庫的聚簇索引是什么問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”數(shù)據(jù)庫的聚簇索引是什么”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。具體的細(xì)節(jié)依賴于其實(shí)現(xiàn)方式，但I(xiàn)nnoDB的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行，即聚簇索引就是表。當(dāng)表有聚簇索引時(shí)，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁中，術(shù)語聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起。因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚簇索引。因?yàn)槭谴鎯?chǔ)引擎負(fù)責(zé)實(shí)現(xiàn)索引，因此不是所有的存儲(chǔ)引擎都支持聚簇索引。如下圖為聚簇索引的數(shù)據(jù)分布，葉子頁包含了行的全部數(shù)據(jù)，但是節(jié)點(diǎn)頁只包含了索引列。

                                                   圖-聚簇索引的數(shù)據(jù)分布

InnoDB通過主鍵聚集數(shù)據(jù)，如果沒有定義主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替，如果沒有這樣的索引，InnoDB會(huì)隱式定義一個(gè)主鍵來作為聚簇索引。聚簇主鍵可能對(duì)性能有幫助，但也可能導(dǎo)致嚴(yán)重的性能問題。但是目前MySQL內(nèi)建的存儲(chǔ)引擎暫時(shí)不支持選擇哪個(gè)索引作為聚簇索引。

聚簇的數(shù)據(jù)有一些重要的優(yōu)點(diǎn)：

• 可以將相關(guān)數(shù)據(jù)保存在一起，例如實(shí)現(xiàn)電子郵箱時(shí)，可以根據(jù)用戶id來聚集索引，這樣只需要從磁盤讀取少量的數(shù)據(jù)頁就能獲得某個(gè)用戶的全部郵件，如果沒有使用聚簇索引，則每封郵件都可能導(dǎo)致一次磁盤I/O；

• 數(shù)據(jù)訪問更快。聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)B-Tree中，因此從聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找更快；

• 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點(diǎn)中的主鍵值；

如果在設(shè)計(jì)表和查詢時(shí)能充分利用上面的優(yōu)點(diǎn)，那就能極大提升性能。同時(shí)聚簇索引也有一些缺點(diǎn)：

• 聚簇索引最大限度地提高了I/O密集型應(yīng)用的性能，但如果數(shù)據(jù)全部都放在內(nèi)存中，則訪問的順序就沒那么重要了，聚簇索引也就沒有什么優(yōu)勢(shì)了；

• 插入速度嚴(yán)重依賴于插入順序。按照主鍵的方式順序插入是速度最快的方式。但如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好用optimize table命令重新組織一下表；

• 更新聚簇索引的代價(jià)很高，因?yàn)闀?huì)強(qiáng)制InnoDB將每個(gè)被更新的行移到到新的位置；

• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候，可能面臨頁分裂的問題。當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿的頁中時(shí)，存儲(chǔ)引擎會(huì)將該頁分裂成兩個(gè)頁面來容納改行，這就是一次頁分裂的操作，頁分裂會(huì)導(dǎo)致表占用更多的磁盤空間；

• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導(dǎo)致存儲(chǔ)不連續(xù)的時(shí)候；

• 二級(jí)索引可能比想象的要更大，因?yàn)樵诙?jí)索引的葉子節(jié)點(diǎn)包含了引用行的主鍵列；

• 二級(jí)索引訪問需要兩次索引查找而不是一次，二級(jí)索引中保存的不是指向行的物理位置的指針，而是行的主鍵值，這意味著通過二級(jí)索引查找行，存儲(chǔ)引擎需要找到二級(jí)索引的葉子節(jié)點(diǎn)獲得對(duì)應(yīng)的主鍵值，然后根據(jù)這個(gè)值去聚簇索引中查找對(duì)應(yīng)的行，這里做了重復(fù)的工作：兩次B-Tree查找而不是一次。

InnoDB和MyISAM的數(shù)據(jù)分布對(duì)比

聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，以及對(duì)應(yīng)的主鍵索引和二級(jí)索引的數(shù)據(jù)分布也有區(qū)別。以下表為例進(jìn)行分析對(duì)比：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `last_name` char(30) NOT NULL,
  `first_name` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

MyISAM的數(shù)據(jù)分布非常簡(jiǎn)單，按照數(shù)據(jù)插入的順序存儲(chǔ)在磁盤上，如下圖所示，從行的旁邊顯示了行號(hào)，從0開始遞增，因?yàn)樾惺嵌ㄩL(zhǎng)的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行（MyISAM并不總是使用圖中的行號(hào)，而是根據(jù)定長(zhǎng)還是變長(zhǎng)的行使用不同的策略），這種分布方式很容易創(chuàng)建索引。下圖簡(jiǎn)要顯示MyISAM的主鍵分布，只顯示了索引中的節(jié)點(diǎn)，索引中的每個(gè)葉子節(jié)點(diǎn)包含行號(hào)，col2列的索引和其它索引沒有區(qū)別，事實(shí)上MyISAM主鍵索引和其它索引在結(jié)構(gòu)上沒有什么不同，主鍵索引就是一個(gè)名為PRIMARY的唯一非空索引。

                                             圖-MyISAM表layout_test的數(shù)據(jù)分布

                                                    圖-MyISAM表的主鍵分布

                                                  圖-MyISAM表col2列索引的分布

InnoDB的數(shù)據(jù)分布如下圖所示，因?yàn)镮nnoDB支持聚簇索引，所以使用不同的方式存儲(chǔ)相同的數(shù)據(jù)，會(huì)注意到該圖顯示了整個(gè)表，而不是只有索引，事實(shí)上，在InnoDB中聚簇索引就是表，所以不像MyISAM那樣需要單獨(dú)的行存儲(chǔ)。聚簇索引的每個(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è)“指針”。

                                                    圖-InnoDB表的主鍵分布

下圖展示了InnoDB的二級(jí)索引結(jié)構(gòu)，其中省略了一些細(xì)節(jié)，InnoDB的非葉子節(jié)點(diǎn)包含了索引列和一個(gè)指向下級(jí)節(jié)點(diǎn)的指針（下級(jí)節(jié)點(diǎn)可以是非葉子節(jié)點(diǎn)，也可以是葉子節(jié)點(diǎn)），每個(gè)葉子節(jié)點(diǎn)都包含了索引列（這里是col2），緊接著是主鍵列（col1）。

                                                       圖-InnoDB的二級(jí)索引分布

下圖是InnoDB和MyISAM如何存儲(chǔ)表的抽象圖，可以很容易看出二者保存數(shù)據(jù)和索引的區(qū)別：

                                                            圖-聚簇和非聚簇索引對(duì)比圖

可以看到InnoDB主索引是聚集索引，輔助索引是非聚集，存儲(chǔ)的是主鍵值。 MyISAM索引是非聚集索引，輔助索引和主索引都是存的數(shù)據(jù)行地址。

向聚簇索引插入順序的索引值

如果正在使用的InnoDB表沒有什么數(shù)據(jù)需要聚集，那么可以定義一個(gè)代理鍵作為主鍵，這種主鍵的數(shù)據(jù)應(yīng)該和應(yīng)用無關(guān)，最簡(jiǎn)單的辦法就是使用自增列，這樣可以保證數(shù)據(jù)行是按順序?qū)懭耄瑢?duì)于根據(jù)主鍵做關(guān)聯(lián)操作的性能也會(huì)更好。最好避免隨機(jī)的（不連續(xù)且值的分布范圍非常大）聚簇索引，特別是對(duì)于I/O密集型的應(yīng)用，例如從性能的角度來看，使用UUID來作為聚簇索引則會(huì)很槽糕：它使得聚簇索引的插入變得完全隨機(jī)，這是最壞的情況，使得數(shù)據(jù)沒有任何的聚集特性?？梢宰鰧?duì)比試驗(yàn)，自增id和UUID作為主鍵的性能對(duì)比，可以發(fā)現(xiàn)UUID主鍵插入行不僅花費(fèi)的時(shí)間更長(zhǎng)，而且索引占用的空間也更大，這樣一方面是由于主鍵字段更長(zhǎng)，另一方面是由于頁分裂和碎片導(dǎo)致的。

                                                        圖-向聚簇索引插入順序的索引值

如上圖所示，因?yàn)橹麈I的值是順序的，所以InnoDB把每一條記錄都存儲(chǔ)在上一條記錄的后面。當(dāng)達(dá)到頁的最大填充因子時(shí)，下一條記錄就會(huì)寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會(huì)近似于被順序的記錄填滿，這也會(huì)是所期望的結(jié)果（然而二級(jí)索引頁可能是不一樣的）。

向聚簇索引中插入無序的值

對(duì)比一下使用UUID聚簇索引的表插入順序，因?yàn)樾滦械闹麈I值不一定比之前插入的大，所以InnoDB無法簡(jiǎn)單地總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置-通常是已有數(shù)據(jù)的中間位置-并且分配空間，這會(huì)增加很多額外的工作，并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化。以下是總結(jié)的一些缺點(diǎn)：

• 寫入的目標(biāo)頁可能已經(jīng)刷入磁盤上并從緩存中移除，或者是還沒有被加載到緩存中，InnoDB在插入之前不得不先找到并從磁盤讀取目標(biāo)頁到內(nèi)存中，這將導(dǎo)致大量的隨機(jī)I/O；

• 因?yàn)閷懭胧莵y序的，InnoDB不得不頻繁地做頁分裂操作，以便為新的行分配空間。頁分裂會(huì)導(dǎo)致移動(dòng)大量數(shù)據(jù)，一次插入最少需要修改三個(gè)頁而不是一個(gè)頁；

• 由于頻繁的頁分裂，頁會(huì)變得稀疏并不被規(guī)則地填充，所以最終數(shù)據(jù)會(huì)有碎片。

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

                                                        圖-向聚簇索引中插入無序的值

從這個(gè)案例可以看出，使用InnoDB時(shí)應(yīng)該盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地使用單調(diào)遞增的聚簇索引來載入新行。

順序的主鍵什么時(shí)候會(huì)造成更壞的結(jié)果？

對(duì)于高并發(fā)工作負(fù)載，在InnoDB中按主鍵順序插入可能會(huì)造成明顯的爭(zhēng)用。主鍵的上界會(huì)成為熱點(diǎn)，因?yàn)樗械牟迦攵及l(fā)送在這里，所以高并發(fā)插入可能導(dǎo)致間隙鎖競(jìng)爭(zhēng)。另一個(gè)熱點(diǎn)是AUTO_INCREMENT鎖機(jī)制；如果遇到這個(gè)問題，則可以考慮重新設(shè)計(jì)表或者應(yīng)用，或者更改innodb_autoinc_lock_mode配置。

到此，關(guān)于“數(shù)據(jù)庫的聚簇索引是什么”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！