MYSQL Innodb中的聚簇索引和非聚簇索引

聚簇索引

數(shù)據(jù)庫(kù)表的索引從數(shù)據(jù)存儲(chǔ)方式上可以分為聚簇索引和非聚簇索引(又叫二級(jí)索引)兩種攒岛。
存儲(chǔ)引擎Innodb中的聚簇索引在同一個(gè)B+-Tree中保存了索引和數(shù)據(jù)行够坐,在聚簇索引中,實(shí)際的數(shù)據(jù)保存在葉子頁(yè)中架谎,中間的節(jié)點(diǎn)頁(yè)保存指向下一層節(jié)點(diǎn)頁(yè)(也可能是葉子葉)的指針。“聚簇”的意思是數(shù)據(jù)行被按照一定順序一個(gè)個(gè)緊密地排列在一起存儲(chǔ)遮晚。一個(gè)表只能有一個(gè)聚簇索引,因?yàn)樵谝粋€(gè)表中數(shù)據(jù)的存放方式只有一種迫悠。

一般來(lái)說鹏漆,將通過主鍵作為聚簇索引的索引列,也就是通過主鍵聚集數(shù)據(jù)创泄。下圖展示了Innodb中聚簇索引的結(jié)構(gòu)(圖片來(lái)自《高性能MySQL(第三版)》):

image.png

聚簇索引的結(jié)構(gòu)

這里要特別注意‘頁(yè)’的概念艺玲,一個(gè)頁(yè)可以理解為一塊具有一定大小的連續(xù)的存儲(chǔ)區(qū)域。相同頁(yè)內(nèi)的數(shù)據(jù)行在物理上是相鄰的鞠抑,因此邏輯上鍵值相鄰的頁(yè)在物理上可能相隔很遠(yuǎn)饭聚。

在中間的某個(gè)節(jié)點(diǎn)頁(yè)中,主鍵<11的葉子頁(yè)和11<主鍵<21的葉子頁(yè)分別被兩個(gè)指針?biāo)赶蚋樽荆抑麈I<11的葉子頁(yè)也有一個(gè)指針指向了11<主鍵<21的葉子頁(yè)秒梳,其余頁(yè)之間的關(guān)系也是一樣。

聚簇索引的優(yōu)點(diǎn)

  1. 聚簇索引將索引和數(shù)據(jù)行保存在同一個(gè)B+-Tree中箕速,查詢通過聚簇索引可以直接獲取數(shù)據(jù)酪碘,相比非聚簇索引需要第二次查詢(非覆蓋索引的情況下)效率要高。
  2. 聚簇索引對(duì)于范圍查詢的效率很高盐茎,因?yàn)槠鋽?shù)據(jù)是按照大小排列的兴垦,

聚簇索引的缺點(diǎn)

  1. 聚簇索引的更新代價(jià)比較高,如果更新了行的聚簇索引列字柠,就需要將數(shù)據(jù)移動(dòng)到相應(yīng)的位置探越。這可能因?yàn)橐迦氲捻?yè)已滿而導(dǎo)致“頁(yè)分裂”。
  2. 插入速度嚴(yán)重依賴于插入順序窑业,按照主鍵進(jìn)行插入的速度是加載數(shù)據(jù)到Innodb中的最快方式钦幔。如果不是按照主鍵插入,最好在加載完成后使用OPTIMIZE TABLE命令重新組織一下表常柄。
  3. 聚簇索引在插入新行和更新主鍵時(shí)鲤氢,可能導(dǎo)致“頁(yè)分裂”問題。
  4. 聚簇索引可能導(dǎo)致全表掃描速度變慢西潘,因?yàn)榭赡苄枰虞d物理上相隔較遠(yuǎn)的頁(yè)到內(nèi)存中(需要耗時(shí)的磁盤尋道操作)铜异。

非聚簇索引

非聚簇索引,又叫二級(jí)索引秸架。Innodb中二級(jí)索引的葉子節(jié)點(diǎn)中保存的不是指向行的物理指針(MyISAM又不一樣揍庄,它的二級(jí)索引葉子節(jié)點(diǎn)存儲(chǔ)的是物理指針),而是行的主鍵值东抹。當(dāng)通過二級(jí)索引查找行蚂子,存儲(chǔ)引擎需要在二級(jí)索引中找到相應(yīng)的葉子節(jié)點(diǎn)沃测,獲得行的主鍵值,然后使用主鍵去聚簇索引中查找數(shù)據(jù)行食茎,這需要兩次B+-Tree查找蒂破。
好處很明顯就是在數(shù)據(jù)爺裂開數(shù)據(jù)存儲(chǔ)位置發(fā)生改變時(shí)不用維護(hù)數(shù)據(jù)物理指針的變化了。缺點(diǎn)也是有的多了一次B+Tree的查詢(根據(jù)主鍵在聚簇索引查詢出主鍵所在的葉子頁(yè))别渔。

總結(jié)

下面是Innodb聚簇索引和非聚簇索引的示意圖(圖片來(lái)自《高性能MySQL(第三版)》:

image.png

Innodb聚簇索引和非聚簇索引

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末附迷,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子哎媚,更是在濱河造成了極大的恐慌喇伯,老刑警劉巖,帶你破解...
    沈念sama閱讀 217,542評(píng)論 6 504
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件拨与,死亡現(xiàn)場(chǎng)離奇詭異稻据,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)买喧,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,822評(píng)論 3 394
  • 文/潘曉璐 我一進(jìn)店門捻悯,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人淤毛,你說我怎么就攤上這事今缚。” “怎么了低淡?”我有些...
    開封第一講書人閱讀 163,912評(píng)論 0 354
  • 文/不壞的土叔 我叫張陵姓言,是天一觀的道長(zhǎng)。 經(jīng)常有香客問我查牌,道長(zhǎng)事期,這世上最難降的妖魔是什么滥壕? 我笑而不...
    開封第一講書人閱讀 58,449評(píng)論 1 293
  • 正文 為了忘掉前任纸颜,我火速辦了婚禮,結(jié)果婚禮上绎橘,老公的妹妹穿的比我還像新娘胁孙。我一直安慰自己,他們只是感情好称鳞,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,500評(píng)論 6 392
  • 文/花漫 我一把揭開白布涮较。 她就那樣靜靜地躺著,像睡著了一般冈止。 火紅的嫁衣襯著肌膚如雪狂票。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 51,370評(píng)論 1 302
  • 那天熙暴,我揣著相機(jī)與錄音闺属,去河邊找鬼慌盯。 笑死,一個(gè)胖子當(dāng)著我的面吹牛掂器,可吹牛的內(nèi)容都是我干的亚皂。 我是一名探鬼主播,決...
    沈念sama閱讀 40,193評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼国瓮,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼灭必!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起乃摹,我...
    開封第一講書人閱讀 39,074評(píng)論 0 276
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤禁漓,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后峡懈,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體璃饱,經(jīng)...
    沈念sama閱讀 45,505評(píng)論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,722評(píng)論 3 335
  • 正文 我和宋清朗相戀三年肪康,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了荚恶。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 39,841評(píng)論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡磷支,死狀恐怖谒撼,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情雾狈,我是刑警寧澤廓潜,帶...
    沈念sama閱讀 35,569評(píng)論 5 345
  • 正文 年R本政府宣布,位于F島的核電站善榛,受9級(jí)特大地震影響辩蛋,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜移盆,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,168評(píng)論 3 328
  • 文/蒙蒙 一悼院、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧咒循,春花似錦据途、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,783評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至裆蒸,卻和暖如春熔萧,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,918評(píng)論 1 269
  • 我被黑心中介騙來(lái)泰國(guó)打工佛致, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留遂赠,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 47,962評(píng)論 2 370
  • 正文 我出身青樓晌杰,卻偏偏與公主長(zhǎng)得像跷睦,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子肋演,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,781評(píng)論 2 354