聚集索引與非聚集索引

聚集索引與非聚集索引

????????數(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)該首先定義最唯一的列

3. 參考文章

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市万皿,隨后出現(xiàn)的幾起案子摧找,更是在濱河造成了極大的恐慌,老刑警劉巖牢硅,帶你破解...
    沈念sama閱讀 216,591評論 6 501
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件蹬耘,死亡現(xiàn)場離奇詭異,居然都是意外死亡减余,警方通過查閱死者的電腦和手機(jī)综苔,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,448評論 3 392
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來位岔,“玉大人如筛,你說我怎么就攤上這事∈闾В” “怎么了杨刨?”我有些...
    開封第一講書人閱讀 162,823評論 0 353
  • 文/不壞的土叔 我叫張陵,是天一觀的道長擦剑。 經(jīng)常有香客問我妖胀,道長,這世上最難降的妖魔是什么惠勒? 我笑而不...
    開封第一講書人閱讀 58,204評論 1 292
  • 正文 為了忘掉前任赚抡,我火速辦了婚禮,結(jié)果婚禮上纠屋,老公的妹妹穿的比我還像新娘涂臣。我一直安慰自己,他們只是感情好巾遭,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,228評論 6 388
  • 文/花漫 我一把揭開白布肉康。 她就那樣靜靜地躺著,像睡著了一般灼舍。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上涨薪,一...
    開封第一講書人閱讀 51,190評論 1 299
  • 那天骑素,我揣著相機(jī)與錄音,去河邊找鬼刚夺。 笑死献丑,一個(gè)胖子當(dāng)著我的面吹牛末捣,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播创橄,決...
    沈念sama閱讀 40,078評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼箩做,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了妥畏?” 一聲冷哼從身側(cè)響起邦邦,我...
    開封第一講書人閱讀 38,923評論 0 274
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎醉蚁,沒想到半個(gè)月后燃辖,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 45,334評論 1 310
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡网棍,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,550評論 2 333
  • 正文 我和宋清朗相戀三年黔龟,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片滥玷。...
    茶點(diǎn)故事閱讀 39,727評論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡氏身,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出惑畴,到底是詐尸還是另有隱情蛋欣,我是刑警寧澤,帶...
    沈念sama閱讀 35,428評論 5 343
  • 正文 年R本政府宣布桨菜,位于F島的核電站豁状,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏倒得。R本人自食惡果不足惜泻红,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,022評論 3 326
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望霞掺。 院中可真熱鬧谊路,春花似錦、人聲如沸菩彬。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,672評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽骗灶。三九已至惨恭,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間耙旦,已是汗流浹背脱羡。 一陣腳步聲響...
    開封第一講書人閱讀 32,826評論 1 269
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人锉罐。 一個(gè)月前我還...
    沈念sama閱讀 47,734評論 2 368
  • 正文 我出身青樓帆竹,卻偏偏與公主長得像,于是被迫代替她去往敵國和親脓规。 傳聞我的和親對象是個(gè)殘疾皇子栽连,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,619評論 2 354

推薦閱讀更多精彩內(nèi)容

  • 1.索引簡介 眾所周知,索引是關(guān)系型數(shù)據(jù)庫中給數(shù)據(jù)庫表中一列或多列的值排序后的存儲結(jié)構(gòu)侨舆,SQL的主流索引結(jié)構(gòu)有B+...
    shoulda閱讀 988評論 0 1
  • ORA-00001: 違反唯一約束條件 (.) 錯誤說明:當(dāng)在唯一索引所對應(yīng)的列上鍵入重復(fù)值時(shí)秒紧,會觸發(fā)此異常。 O...
    我想起個(gè)好名字閱讀 5,307評論 0 9
  • SQL SERVER提供了多種索引态罪。如果以存儲結(jié)構(gòu)結(jié)構(gòu)來區(qū)分噩茄,有聚集索引和非聚集索引;如果以數(shù)據(jù)的唯一性來區(qū)分复颈,則...
    不知名的蛋撻閱讀 6,031評論 0 5
  • 索引 數(shù)據(jù)庫中的查詢操作非常普遍耗啦,索引就是提升查找速度的一種手段 索引的類型 從數(shù)據(jù)結(jié)構(gòu)角度分 1.B+索引:傳統(tǒng)...
    一凡呀閱讀 2,914評論 0 8
  • 學(xué)習(xí)完整課程請移步 互聯(lián)網(wǎng) Java 全棧工程師 聚集索引一個(gè)表只能有一個(gè)凿菩,而非聚集索引一個(gè)表可以存在多個(gè) 聚集索...
    擼帝閱讀 26,369評論 0 1