聚集索引：也稱 Clustered Index。是指關系表記錄的物理順序與索引的邏輯順序相同。由于一張表只能按照一種物理順序存放，一張表最多也只能存在一個聚集索引。與非聚集索引相比，聚集索引有著更快的檢索速度。
公司主營業(yè)務：做網(wǎng)站、成都網(wǎng)站制作、移動網(wǎng)站開發(fā)等業(yè)務。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)公司是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領域給我們帶來的挑戰(zhàn)，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)公司推出應縣免費做網(wǎng)站回饋大家。
MySQL 里只有 INNODB 表支持聚集索引，INNODB 表數(shù)據(jù)本身就是聚集索引，也就是常說 IOT，索引組織表。非葉子節(jié)點按照主鍵順序存放，葉子節(jié)點存放主鍵以及對應的行記錄。所以對 INNODB 表進行全表順序掃描會非?？?。
非聚集索引：也叫 Secondary Index。指的是非葉子節(jié)點按照索引的鍵值順序存放，葉子節(jié)點存放索引鍵值以及對應的主鍵鍵值。MySQL 里除了 INNODB 表主鍵外，其他的都是二級索引。MYISAM，memory 等引擎的表索引都是非聚集索引。簡單點說，就是索引與行數(shù)據(jù)分開存儲。一張表可以有多個二級索引。
正常MySQL底層存儲索引默認使用的是B+樹,但是如果設立了分區(qū)表的情況下,他的底層是如何進行存儲的呢?
查看MySQL對應的自己電腦上面的安裝目錄下,是有一個ibd文件,用innblock和bcview兩個小工具，(github上可以搜到),便可以實現(xiàn)對ibd文件的查看,通過對比著沒有設立分區(qū)的表的ibd文件,就可以看出來
分區(qū)表會將索引分成分區(qū)個個數(shù)的索引樹來存儲索引，也就是分開存儲。
? ?本節(jié)課主要關注InnoDB，但是這里討論的原理對于任何支持聚簇索引的存儲引擎都是適用的。
? ?葉子節(jié)點包含了全部數(shù)據(jù)，其他節(jié)點只包含索引列。InnoDB將通過主鍵聚集數(shù)據(jù)，也就是說上圖中的“被索引的列”就是主鍵列。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引InnoDB會隱式定義一個主鍵來作為聚簇索引。
? ?如果主鍵比較大的話，那輔助索引將會變的更大，因為 輔助索引的葉子存儲的是主鍵值；過長的主鍵值，會導致非葉子節(jié)點占用占用更多的物理空間
所以建議使用int的auto_increment作為主鍵
? ?主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲在上一條記錄的后面。當達到頁的最大值時，下一條記錄就會寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿。
? ?聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一 頁一頁地寫，索引結構相對緊湊，磁盤碎片少，效率也高。
? ?因為MyISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進行I/O讀取，于是開始不停的尋道不停的旋轉。聚簇索引則只需一次I/O。（強烈的對比）
? ?不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢些，因為索引所占空間小，這些操作是需要在內(nèi)存中完成的。
? ?MyISM使用的是非聚簇索引， 非聚簇索引的兩棵B+樹看上去沒什么不同 ，節(jié)點的結構完全一致只是存儲的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數(shù)據(jù)存儲在獨立的地方，這兩顆B+樹的葉子節(jié)點都使用一個地址指向真正的表數(shù)據(jù)，對于表數(shù)據(jù)來說，這兩個鍵沒有任何差別。由于 索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹 。
? ?所以說，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設置。 一般要根據(jù)這個表最常用的SQL查詢方式來進行選擇，某個字段作為聚簇索引，或組合聚簇索引 ，這個要看實際情況。
? ?聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，主鍵索引和二級索引的數(shù)據(jù)分布也有區(qū)別，通常會讓人感到困擾和以外，下面通過一個列子來講解InnoDB和MyISAM是如何存儲數(shù)據(jù)的：
? ?該表的主鍵取值1~10000，按照隨機順序插入并使用optimize table命令做了優(yōu)化。換句話說，數(shù)據(jù)在磁盤上的存儲方式已是最優(yōu)，但行的順序是隨機的。列col2的值是從1~100之間隨機賦值，所以有很多重復的值。
? ?MyISAM的數(shù)據(jù)分布很簡單，所以先介紹它。MyISAM按照數(shù)據(jù)插入的順序存儲在磁盤上，如下圖所示：
在行的旁邊顯示行號，從0開始遞增。因為行是定長的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行。
col2上的索引
? ?事實上，MyISAM中主鍵索引和其他索引在結構上沒有什么不同。主鍵索引就是一個名為PRIMARY的唯一非空索引。
? ?InnoDB支持聚簇索引，所以使用不同的方式存儲同樣的數(shù)據(jù)。
? ?第一眼看上去，感覺和前面的沒什么區(qū)別，但是該圖顯示了整個表，而不是只有索引。因為在InnoDB中，聚簇索引就是表，所以不像MyISAM那樣需要獨立的行存儲，這也是為什么MyISAM索引和數(shù)據(jù)結構是分開的。
? ?聚簇索引的每一個葉子節(jié)點都包含了主鍵值。事務ID、用于事務和MVCC的回滾指針以及所有的剩余列。如果主鍵是一個列前綴索引，InnoDB也會包含完整的主鍵列和剩下的其他列。
? ?還有一點和MyISAM不同的是，InnoDB的二級索引和聚簇索引很不相同。InnoDB的二級索引的葉子節(jié)點中存儲的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。這樣的策略減少了當出現(xiàn)行移動或者數(shù)據(jù)頁分裂時二級索引的維護工作。使用主鍵值當作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB在移動時無需更新二級索引中的這個“指針”。
? ?我們在來看一下 col2索引 。
? ?每一個葉子節(jié)點包含了索引列（這里是col2），緊接著是主鍵值（col1），上圖我們省略了非葉子節(jié)點這樣的細節(jié)。InnoDB非葉子節(jié)點包含了索引列和一個指向下一級節(jié)點的指針。
? ?最后，以一張圖表示InnoDB和MyISAM保存數(shù)據(jù)和索引的區(qū)別。
? ?前面講過，最好使用AUTO_INCREMENT自增列來聚集數(shù)據(jù)，避免隨機的、不連續(xù)的、值分布范圍大的列做聚簇索引，特別是對于I/O密集型的應用。例如，從性能角度考慮，使用UUID來作為聚簇索引則會很糟糕：他使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。
? ?為了演示這一點，我們做兩個基準測試：
1、使用證書ID插入userinfo表，和uuid作為主鍵的userinfo_uuid表
? ?userinfo_uuid表跟userinfo表除了主鍵給為UUID，其他字段都一樣
? ?測試這兩個表的設計，首先在一個有足夠內(nèi)存容納索引的服務器上向這兩個表各插入100萬條記錄。然后向兩個表繼續(xù)插入300萬數(shù)據(jù)，使索引的大小超過服務器的內(nèi)存容量。測試結果如下：
? ?向UUID主鍵插入行不僅花費的時間更長，而且索引占用的空間也更大。這一方面是由于主鍵字段更長，另一方面毫無疑問是由于頁分裂和碎片導致的。
? ?為了明白為什么會這樣，來看看往第一個表中插入數(shù)據(jù)時，索引發(fā)生了什么變化。
自整型主鍵插入
? ?因為主鍵是順序的，所以InnoDB把每一條記錄都存在上一條記錄的后面。當達到頁的最大容量后，下一條記錄就會寫入到新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿，這也正是所期望的結果。
UUID插入
? ?因為新行的主鍵值不一定比之前插入的大，所以InnoDB無法簡單的總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置，通常是已有數(shù)據(jù)的中間位置，并且分配空間。這會正價很多的額外工作，并導致數(shù)據(jù)分布不夠優(yōu)化。
缺點：
把這些隨機值載入到聚簇索引后，也許需要做一次OPTIMIZE TABLE來重建表并優(yōu)化頁的填充。
結論 ：使用InnoDB時應盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地單調(diào)增加聚簇鍵的值來插入新行。
重慶分公司
重慶分公司
