Mysql聚簇索引和非聚簇索引-創(chuàng)新互聯(lián)

Mysql聚簇索引和非聚簇索引##

參考如下：

創(chuàng)新互聯(lián)建站長(zhǎng)期為上千余家客戶(hù)提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為船營(yíng)企業(yè)提供專(zhuān)業(yè)的網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)，船營(yíng)網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

• 淺談聚簇索引和非聚簇索引的區(qū)別

• mysql的聚簇索引與非聚簇索引的簡(jiǎn)短總結(jié)

在mysql數(shù)據(jù)庫(kù)中，myisam引擎和innodb引擎使用的索引類(lèi)型不同，myisam對(duì)應(yīng)的是非聚簇索引，而innodb對(duì)應(yīng)的是聚簇索引。聚簇索引也叫復(fù)合索引、聚集索引等等。
聚簇索引：“聚簇”的意思是數(shù)據(jù)行被按照一定順序一個(gè)個(gè)緊密地排列在一起存儲(chǔ)。一個(gè)表只能有一個(gè)聚簇索引，因?yàn)樵谝粋€(gè)表中數(shù)據(jù)的存放方式只有一種。
非聚簇索引：又叫二級(jí)索引。二級(jí)索引的葉子節(jié)點(diǎn)中保存的不是指向行的物理指針，而是行的主鍵值。當(dāng)通過(guò)二級(jí)索引查找行，存儲(chǔ)引擎需要在二級(jí)索引中找到相應(yīng)的葉子節(jié)點(diǎn)，獲得行的主鍵值，然后使用主鍵去聚簇索引中查找數(shù)據(jù)行，這需要兩次B-Tree查找。

非聚簇索引#####

以myisam為例，一個(gè)數(shù)據(jù)表table中，它是有table.frm、table.myd以及table.myi組成。table.myd記錄了數(shù)據(jù)，table.myi記錄了索引的數(shù)據(jù)。在用到索引時(shí)，先到table.myi（索引樹(shù)）中進(jìn)行查找，取到數(shù)據(jù)所在table.myd的行位置，拿到數(shù)據(jù)。所以myisam引擎的索引文件和數(shù)據(jù)文件是獨(dú)立分開(kāi)的，則稱(chēng)之為非聚簇索引。myisam類(lèi)型的索引，指向數(shù)據(jù)在行的位置。即每個(gè)索引相對(duì)獨(dú)立，查詢(xún)用到索引時(shí)，索引指向數(shù)據(jù)的位置。

聚簇索引#####

以innodb為例，在一個(gè)數(shù)據(jù)table中，它的數(shù)據(jù)文件和索引文件是同一個(gè)文件。即在查詢(xún)過(guò)程中，找到了索引，便找到了數(shù)據(jù)文件。在innodb中，即存儲(chǔ)主鍵索引值，又存儲(chǔ)行數(shù)據(jù)，稱(chēng)之為聚簇索引。     innodb索引，指向主鍵對(duì)數(shù)據(jù)的引用。非主鍵索引則指向?qū)χ麈I的引用。innodb中，沒(méi)有主見(jiàn)索引，則會(huì)使用unique索引，沒(méi)有unique索引，則會(huì)使用數(shù)據(jù)庫(kù)內(nèi)部的一個(gè)行的id來(lái)當(dāng)作主鍵索引。    在聚簇索引中，數(shù)據(jù)會(huì)被按照順序整理排列，當(dāng)使用where進(jìn)行順序、范圍、大小檢索時(shí)，會(huì)大大加速檢索效率。非聚簇索引在存儲(chǔ)時(shí)不會(huì)對(duì)數(shù)據(jù)進(jìn)行排序，相對(duì)產(chǎn)生的數(shù)據(jù)文件體積也比較大。

對(duì)于InnoDB聚簇表分布#####

InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹(shù)中，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹(shù)的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)，之后獲得行數(shù)據(jù)。
若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹(shù)中檢索Name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹(shù)種再執(zhí)行一次B+樹(shù)檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)。（重點(diǎn)在于通過(guò)其他鍵需要建立輔助索引）

聚簇索引具有唯一性，由于聚簇索引是將數(shù)據(jù)跟索引結(jié)構(gòu)放到一塊，因此一個(gè)表僅有一個(gè)聚簇索引。

表中行的物理順序和索引中行的物理順序是相同的，在創(chuàng)建任何非聚簇索引之前創(chuàng)建聚簇索引，這是因?yàn)榫鄞厮饕淖兞吮碇行械奈锢眄樞?，?shù)據(jù)行 按照一定的順序排列，并且自動(dòng)維護(hù)這個(gè)順序；

聚簇索引默認(rèn)是主鍵，如果表中沒(méi)有定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一且非空的索引代替。如果沒(méi)有這樣的索引，InnoDB 會(huì)隱式定義一個(gè)主鍵（類(lèi)似oracle中的RowId）來(lái)作為聚簇索引。如果已經(jīng)設(shè)置了主鍵為聚簇索引又希望再單獨(dú)設(shè)置聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復(fù)設(shè)置主鍵即可。

對(duì)于MyISAM非聚簇分布#####

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

使用聚簇索引的優(yōu)勢(shì)：#####

每次使用輔助索引檢索都要經(jīng)過(guò)兩次B+樹(shù)查找，看上去聚簇索引的效率明顯要低于非聚簇索引，這不是多此一舉嗎？聚簇索引的優(yōu)勢(shì)在哪？

1.由于行數(shù)據(jù)和聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)在一起，同一頁(yè)中會(huì)有多條行數(shù)據(jù)，訪(fǎng)問(wèn)同一數(shù)據(jù)頁(yè)不同行記錄時(shí)，已經(jīng)把頁(yè)加載到了Buffer中（緩存器），再次訪(fǎng)問(wèn)時(shí)，會(huì)在內(nèi)存中完成訪(fǎng)問(wèn)，不必訪(fǎng)問(wèn)磁盤(pán)。這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了，如果按照主鍵Id來(lái)組織數(shù)據(jù)，獲得數(shù)據(jù)更快。

2.輔助索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵值，而不是數(shù)據(jù)的存放地址。好處是當(dāng)行數(shù)據(jù)放生變化時(shí)，索引樹(shù)的節(jié)點(diǎn)也需要分裂變化；或者是我們需要查找的數(shù)據(jù)，在上一次IO讀寫(xiě)的緩存中沒(méi)有，需要發(fā)生一次新的IO操作時(shí)，可以避免對(duì)輔助索引的維護(hù)工作，只需要維護(hù)聚簇索引樹(shù)就好了。另一個(gè)好處是，因?yàn)檩o助索引存放的是主鍵值，減少了輔助索引占用的存儲(chǔ)空間大小。

注：我們知道一次io讀寫(xiě)，可以獲取到16K大小的資源，我們稱(chēng)之為讀取到的數(shù)據(jù)區(qū)域?yàn)镻age。而我們的B樹(shù)，B+樹(shù)的索引結(jié)構(gòu)，葉子節(jié)點(diǎn)上存放好多個(gè)關(guān)鍵字（索引值）和對(duì)應(yīng)的數(shù)據(jù)，都會(huì)在一次IO操作中被讀取到緩存中，所以在訪(fǎng)問(wèn)同一個(gè)頁(yè)中的不同記錄時(shí)，會(huì)在內(nèi)存里操作，而不用再次進(jìn)行IO操作了。除非發(fā)生了頁(yè)的分裂，即要查詢(xún)的行數(shù)據(jù)不在上次IO操作的換村里，才會(huì)觸發(fā)新的IO操作。

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

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

聚簇索引需要注意的地方#####

當(dāng)使用主鍵為聚簇索引時(shí)，主鍵最好不要使用uuid，因?yàn)閡uid的值太過(guò)離散，不適合排序且可能出線(xiàn)新增加記錄的uuid，會(huì)插入在索引樹(shù)中間的位置，導(dǎo)致索引樹(shù)調(diào)整復(fù)雜度變大，消耗更多的時(shí)間和資源。

建議使用int類(lèi)型的自增，方便排序并且默認(rèn)會(huì)在索引樹(shù)的末尾增加主鍵值，對(duì)索引樹(shù)的結(jié)構(gòu)影響最小。而且，主鍵值占用的存儲(chǔ)空間越大，輔助索引中保存的主鍵值也會(huì)跟著變大，占用存儲(chǔ)空間，也會(huì)影響到IO操作讀取到的數(shù)據(jù)量。

為什么主鍵通常建議使用自增id#####

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

聚簇索引的優(yōu)點(diǎn)#####

聚簇索引將索引和數(shù)據(jù)行保存在同一個(gè)B-Tree中，查詢(xún)通過(guò)聚簇索引可以直接獲取數(shù)據(jù)，相比非聚簇索引需要第二次查詢(xún)（非覆蓋索引的情況下）效率要高。
聚簇索引對(duì)于范圍查詢(xún)的效率很高，因?yàn)槠鋽?shù)據(jù)是按照大小排列的，

本文標(biāo)題：Mysql聚簇索引和非聚簇索引-創(chuàng)新互聯(lián)
文章網(wǎng)址：http://bm7419.com/article2/cecoic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營(yíng)銷(xiāo)、網(wǎng)站制作、品牌網(wǎng)站建設(shè)、定制網(wǎng)站、網(wǎng)頁(yè)設(shè)計(jì)公司、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)