- 1.什么是聚簇索引?
不是一種單獨(dú)的索引方式,而是一種數(shù)據(jù)存儲方式,innoDB的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B-tree索引和數(shù)據(jù)行;
當(dāng)表有聚簇索引時(shí),它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁上,葉子頁包含了行的全部數(shù)據(jù),而節(jié)點(diǎn)頁只包含索引列;"聚簇"表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起,因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方,所以一個(gè)表只能有一個(gè)聚簇索引,類似于oracle的索引組織表;
下圖便展示了mysql的Innodb主鍵索引的原理圖:
在InnoDB中虎眨，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)作煌，這棵樹的葉子頁節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄香椎，這個(gè)索引的key是數(shù)據(jù)表的主鍵留瞳，因此InnoDB表數(shù)據(jù)文件本身就是主索引，所以必須有主鍵惹盼，也就是說innoDB通過主鍵聚集數(shù)據(jù)唤反，如果沒有顯示定義，innoDB會選擇一個(gè)唯一的非空索引代替.如果沒有這樣的索引,innoDB會隱式定義一個(gè)主鍵來作為聚簇索引.
- 2.聚簇索引的優(yōu)點(diǎn)和缺點(diǎn)
優(yōu)點(diǎn):
1 .可以把相關(guān)的數(shù)據(jù)保存在一起;比如根據(jù)用戶ID來聚集郵件數(shù)據(jù);
2.數(shù)據(jù)訪問更快,聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)B-tree中,因此聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找要快柴信。
3.使用覆蓋索引(索引列和查詢列一致)掃描的查詢可以直接使用聚簇索引頁節(jié)點(diǎn)中的主鍵值;
缺點(diǎn):
1.聚簇?cái)?shù)據(jù)最大限度地提高了I/O密集型應(yīng)用的性能,但如果數(shù)據(jù)全部都放在內(nèi)存中,則訪問的順序就沒那么重要了;
2.插入速度嚴(yán)格依賴于插入順尋,按照主鍵的順序插入是加載到innodb;如果不是按照主鍵順序加載數(shù)據(jù),那么加載完成后最好使用OPTIMIZE TABLE TABLENAME命令重新組織一下表套啤。
3.更新聚簇索引列的代價(jià)很高,因?yàn)闀?qiáng)制innodb將每個(gè)被更新的行移動到新的位置。
4.基于聚簇索引的表在插入新行,或者主鍵被更新導(dǎo)致需要移動行的時(shí)候,可能面臨“頁分裂”的問題.當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿的頁中時(shí),存儲引擎會將該頁分裂成兩個(gè)頁面來容納該行,這就是一次頁分裂操作,頁分裂會導(dǎo)致表占用更多的磁盤空間;
5.聚簇索引可能導(dǎo)致全表掃描變慢,尤其是行比較疏松,或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時(shí)候;
6.二級索引(非聚簇索引) 可能比想象的要更大,因?yàn)樵诙壦饕娜~子節(jié)點(diǎn)包含了引用行的主鍵列;二級索引訪問需要兩次索引查找,而不是一次;
不應(yīng)該適用二級索引,應(yīng)該使用hash索引;
-
3.MyISAM支持的索引
MyISAM是按照數(shù)據(jù)插入的順序存儲在磁盤上的随常，葉子節(jié)點(diǎn)存儲數(shù)據(jù)行的指針引用潜沦，隱藏了行的物理細(xì)節(jié) ，只顯示索引中的節(jié)點(diǎn)绪氛，索引中的每個(gè)葉子節(jié)點(diǎn)包含了具體行的指針引用唆鸡。其原理如圖所示:
MyISAM支持的索引.png
由此可得出:MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址枣察；
-
4.InnoDB和MyISAM的二級索引
innoDB二級索引的葉子節(jié)點(diǎn)中存儲的不是“行指針”争占，而是主鍵值燃逻，并以此作為指向行的指針，如圖所示:
InnoDB主鍵索引與二級索引示意圖.png
這樣做有什么好處嘞?
這樣就減少了出現(xiàn)當(dāng)行移動或者數(shù)據(jù)頁分裂時(shí)二級索引的維護(hù)工作臂痕，使用主鍵值當(dāng)作指針會讓二級索引占用更多的空間伯襟，但是，在移動行時(shí)無需更新二級索引中的這個(gè)主鍵值呀握童。
解釋下頁分裂:頁是InnoDB存儲引擎管理數(shù)據(jù)庫的最小磁盤單位姆怪，類型為B-Tree 節(jié)點(diǎn)的頁，存放的即是表中行的實(shí)際數(shù)據(jù)了,InnoDB中的頁大小為16KB澡绩，負(fù)載因子是15稽揭，且不可以更改,當(dāng)超過這個(gè)數(shù)據(jù)就會進(jìn)行頁分裂，且分裂時(shí)InnoDB一般會記錄下一個(gè)頁的數(shù)據(jù)信息英古。
頁分裂帶來的問題:頁會變得稀疏且被不規(guī)則的填充,導(dǎo)致最終數(shù)據(jù)碎片化淀衣。
解決:OPTIMIZE TABLE 來重建表并優(yōu)化頁的填充。 -
5.MyISAM的二級索引
MyISAM中召调，主索引和輔助索引(Secondary key)在結(jié)構(gòu)上沒有任何區(qū)別膨桥，只是主索引要求key是唯一的，而輔助索引的key是可以重復(fù)的唠叛，如圖所示:
MyISAM二級索引.png
這樣相對于使用B+的InnoDB可通過輔助索引快速找到所有的數(shù)據(jù)只嚣，而不需要再遍歷一邊主鍵索引，所以適用于OLAP艺沼。
以上便是對索引的相關(guān)補(bǔ)充册舞，后續(xù)將會對索引優(yōu)化繼續(xù)展開書寫...
