mysql聚集索引的有哪一些缺點(diǎn)

　聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式（不是數(shù)據(jù)結(jié)構(gòu)，而是存儲(chǔ)結(jié)構(gòu)），具體細(xì)節(jié)依賴于其實(shí)現(xiàn)方式，但innodb的聚簇索引實(shí)際上是在同一個(gè)結(jié)構(gòu)中保存了btree索引和數(shù)據(jù)行。

　　當(dāng)表有索引時(shí)，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(yè)中，屬于聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起，因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚簇索引。因?yàn)槭谴鎯?chǔ)引擎負(fù)責(zé)實(shí)現(xiàn)索引，因此不是所有的存儲(chǔ)引擎都支持聚簇索引。下面主要介紹innodb，但下面討論的原理對(duì)于任何支持聚簇索引的引擎都適用：

　　葉子頁(yè)包含了行的全部數(shù)據(jù)，但是節(jié)點(diǎn)頁(yè)只包含了索引列（或者可以說非葉子節(jié)點(diǎn)的節(jié)點(diǎn)頁(yè)包含的是索引值的索引，因?yàn)檫@些節(jié)點(diǎn)頁(yè)包含的值是從索引列中提取出來的）。

　　innodb將通過主鍵聚集數(shù)據(jù)，如果沒有定義主鍵，Innodb會(huì)選擇第一個(gè)非空的唯一索引代替，如果沒有非空唯一索引，Innodb會(huì)隱式定義一個(gè)6字節(jié)的rowid主鍵來作為聚集索引。innodb只聚集在同一個(gè)頁(yè)面中的記錄，包含相鄰鍵值的頁(yè)面可能會(huì)相距甚遠(yuǎn)。

　　要注意：聚簇主鍵可能對(duì)性能有幫助，但也可能導(dǎo)致嚴(yán)重的性能問題，尤其是將表的存儲(chǔ)引擎從innodb轉(zhuǎn)換成其他引擎的時(shí)候。

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

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

　　B：數(shù)據(jù)訪問更快，聚集索引將索引和數(shù)據(jù)保存在同一個(gè)btree中，因此從聚集索引中獲取數(shù)據(jù)通常比在非聚集索引中查找要快

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

聚集索引的缺點(diǎn)：

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

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

　　C：更新聚集索引列的代價(jià)很高，因?yàn)闀?huì)強(qiáng)制innodb將每個(gè)被更新的行移動(dòng)到新的位置

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

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

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

　　G：二級(jí)索引訪問需要兩次索引查找，而不是一次

　　因?yàn)槎��?jí)索引葉子節(jié)點(diǎn)中保存的不是指向行的物理位置的指針，而是行的主鍵值。這意味著通過二級(jí)索引查找行，存儲(chǔ)引擎需要找到二級(jí)索引的葉子節(jié)點(diǎn)獲得對(duì)應(yīng)的主鍵值，然后根據(jù)這個(gè)主鍵值去聚集索引中查找對(duì)應(yīng)的行，這里做了重復(fù)的工作，兩次btree查找而不是一次，對(duì)于innodb，自適應(yīng)哈希索引能減少這樣的重復(fù)工作。

innodb和myisam物理存儲(chǔ)的數(shù)據(jù)分布對(duì)比：

　　myisam：

　　是按照數(shù)據(jù)插入的順序存儲(chǔ)在磁盤上的，myisam中的主鍵索引和二級(jí)索引在結(jié)構(gòu)上并沒有什么不同，主鍵索引就是一個(gè)名為primary的唯一非空索引。

　　innodb：

　　因?yàn)閕nnodb支持聚集索引，所以使用非常不同的方式存儲(chǔ)同樣的數(shù)據(jù)，innodb聚集索引包含了整個(gè)表的數(shù)據(jù)，而不是只有索引，因?yàn)樵贗nnodb中，聚集索引就是表，所以不像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ù)頁(yè)的分裂時(shí)二級(jí)索引的維護(hù)工作，使用主鍵值當(dāng)做指針會(huì)讓二級(jí)索引占用更多的空間，換來的好處是，innodb在移動(dòng)行時(shí)無須更新二級(jí)索引中的這個(gè)指針。

　　在innodb表中按主鍵順序插入行，如果正在使用Innodb表并且沒有什么數(shù)據(jù)需要聚集，那么可以定義一個(gè)代理鍵作為主鍵，這種主鍵的數(shù)據(jù)應(yīng)該和應(yīng)用無關(guān)，最簡(jiǎn)單的方法是使用auto_increment自增列，這樣可以保證數(shù)據(jù)行是按順序插入的，對(duì)于根據(jù)主鍵做關(guān)聯(lián)操作的性能也會(huì)更好。

　　不要使用UUID來作為聚集索引，否則性能會(huì)很糟糕，因?yàn)樗�使得聚集索引的插入變得完全隨機(jī)，使得數(shù)據(jù)沒有任何聚集特性。因?yàn)閁UID作為主鍵插入行不僅花費(fèi)的時(shí)間更長(zhǎng)，而且索引也更大，這一方面是因?yàn)橹麈I字段變長(zhǎng)了，另外一方面毫無疑問是由于頁(yè)分裂導(dǎo)致時(shí)間變長(zhǎng)和碎片導(dǎo)致的索引變大。因?yàn)橹麈I的值是順序的，所以Innodb把每一條記錄都存儲(chǔ)在上一條記錄的后面，當(dāng)達(dá)到頁(yè)的最大填充因子時(shí)（innodb默認(rèn)的最大填充因子是頁(yè)大小的十六分之十五，留出部分空間用于以后修改），下一條記錄就會(huì)寫入新的頁(yè)中，一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁(yè)就會(huì)近似被順序的記錄填滿，這也正是所期望的結(jié)果（然而，二級(jí)索引頁(yè)可能是不一樣的）。

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

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

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

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

把這些隨機(jī)值載入到聚集索引之后，也許需要做一次optimize table來重建表并優(yōu)化頁(yè)的填充。使用innodb時(shí)應(yīng)該盡可能地按照主鍵順序插入數(shù)據(jù)，并且盡可能地使用簡(jiǎn)單增加的聚簇鍵的值來插入新行。

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

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

以上就是mysql聚集索引的有哪些缺點(diǎn)的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！