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

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

參考如下：

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

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

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

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

非聚簇索引#####

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

聚簇索引#####

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

對于InnoDB聚簇表分布#####

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

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

表中行的物理順序和索引中行的物理順序是相同的，在創(chuàng)建任何非聚簇索引之前創(chuàng)建聚簇索引，這是因為聚簇索引改變了表中行的物理順序，數(shù)據(jù)行 按照一定的順序排列，并且自動維護(hù)這個順序；

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

對于MyISAM非聚簇分布#####

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

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

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

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

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

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

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

4.不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢些，因為索引所占空間小，這些操作是需要在內(nèi)存中完成的。

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

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

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

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

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

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

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

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

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

廣告

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