聚集索引與非聚集索引
????????數(shù)據(jù)庫索引在物理存儲層面可以分為兩類:聚集索引蛇数、非聚集索引。
1. 聚集索引(聚簇索引)
1.1 特性
????????聚集索引可以類比字典中的A-Z的排序颈渊,字和字母的順序一致(物理順序與列值邏輯順序相同),數(shù)據(jù)行的物理順序與列值(一般是主鍵的那一列)的邏輯順序相同。
1.2 規(guī)則
????????因?yàn)榫奂饕壎藬?shù)據(jù)行的物理順序蝴光,所以一個(gè)表中只能擁有一個(gè)聚集索引她渴。
????????==注:推薦建表時(shí)設(shè)置聚集索引,如果后期才添加聚集索引蔑祟,數(shù)據(jù)量很大的時(shí)候趁耗,效率會很低, 因?yàn)樾枰苿訑?shù)據(jù)行來維持聚集索引的邏輯順序疆虚。==
MySQL中聚集索引的規(guī)則如下:
- 如果一個(gè)主鍵被定義了苛败,那么這個(gè)主鍵就是作為聚集索引
- 如果沒有主鍵被定義,那么該表的第一個(gè)唯一非空索引被作為聚集索引
- 如果沒有主鍵也沒有合適的唯一索引径簿,那么innodb內(nèi)部會生成一個(gè)隱藏的主鍵作為聚集索引罢屈,這個(gè)隱藏的主鍵是一個(gè)6個(gè)字節(jié)的列,改列的值會隨著數(shù)據(jù)的插入自增
2. 非聚集索引
2.1 特性
????????非聚集索引的邏輯順序與磁盤上行的物理存儲順序不同篇亭,一個(gè)表中可以擁有多個(gè)非聚集索引儡遮,可以類比字典中的偏旁排序,字與偏旁順序不一致暗赶。
????????除了聚集索引鄙币,其他的索引都是非聚集索引,非聚集索引包括:普通索引蹂随、唯一索引十嘿、全文索引等。
2.2 非聚集索引的二次查詢問題
????????非聚集索引葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)岳锁,只是有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊(上圖中LeafLevel模塊)绩衷,此如果使用非聚集索引查詢,而查詢列中包含了其他該索引沒有覆蓋的列激率,那么他還要進(jìn)行第二次的查詢咳燕,查詢節(jié)點(diǎn)上對應(yīng)的數(shù)據(jù)行的數(shù)據(jù)。
????????==因?yàn)榉蔷奂饕~子節(jié)點(diǎn)除了會存儲索引覆蓋列數(shù)據(jù)乒躺,也存放聚集索引所覆蓋的列數(shù)據(jù)招盲。==
id | userName | score |
---|---|---|
1 | 張三 | 60 |
2 | 李四 | 70 |
3 | 王五 | 80 |
... | ... | ... |
其中id為聚集索引(主鍵),userName為非聚集索引
-- 這兩種sql嘉冒,可以直接拿到結(jié)果值曹货,不需要二次查詢
select id, username from t1 where username = '小明';
select username from t1 where username = '小明';
-- 下面的sql需要二次查詢?nèi)カ@得 score 的數(shù)據(jù)
select username, score from t1 where username = '小明';
2.3 優(yōu)化二次查詢問題
非聚集索引會存放索引覆蓋的列及聚集索引覆蓋的列,若要查詢這些之外的數(shù)據(jù)讳推,就需要進(jìn)行二次掃描顶籽,降低了查詢效率。解決方案就是對于頻繁查詢的列創(chuàng)建聯(lián)合索引银觅。
參照上例:
給 username 和 score 兩列創(chuàng)建聯(lián)合索引index(username,score)礼饱,再執(zhí)行下面的sql就不需要進(jìn)行二次掃描:
select username, score from t1 where username = '小明';
==注:復(fù)合索引需要滿足最左側(cè)索引原則,如果查詢條件中沒有最左邊的一列或者多列,那么符合索引不起作用镊绪。==
條件:
- 最多可以把16個(gè)列合并成一個(gè)單獨(dú)的復(fù)合索引
- 構(gòu)成復(fù)合索引的列的總長度不能超過900字節(jié)匀伏,復(fù)合列的長度不能太長
- 所有的列必須來自同一個(gè)表中,不能跨表建立復(fù)合列
- 在復(fù)合索引中镰吆,列的排列順序是非常重要的帘撰,應(yīng)該首先定義最唯一的列