mysql索引原理(1)

聚簇索引并不是一種單獨的索引類型,而是一種數(shù)據(jù)存儲方式,是對磁盤上實際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序的算法防楷。

特點是存儲數(shù)據(jù)的順序和索引順序一致。一般情況下主鍵會默認創(chuàng)建聚簇索引吆玖,且一張表只允許存在一個聚簇索引。

聚簇索引的葉子節(jié)點就是數(shù)據(jù)節(jié)點痒谴,而非聚簇索引的葉子節(jié)點仍然是索引節(jié)點衰伯,只不過有指向對應數(shù)據(jù)塊的指針。

MySQL中积蔚,不同的存儲引擎對索引的實現(xiàn)方式不同意鲸,大致說下MyISAM和InnoDB兩種存儲引擎。

一尽爆、MyISAM

MyISAM的B+Tree的葉子節(jié)點上的data怎顾,并不是數(shù)據(jù)本身,而是數(shù)據(jù)存放的地址漱贱。主索引和輔助索引沒啥區(qū)別槐雾,只是主索引中的key一定得是唯一的。這里的索引都是非聚簇索引幅狮。

MYISAM的主鍵索引和二級索引沒有任何區(qū)別募强,主鍵索引僅僅只是一個叫做PRIMARY的唯一、非空的索引崇摄,且MYISAM引擎中可以不設主鍵擎值。

MyISAM還采用壓縮機制存儲索引,比如逐抑,第一個索引為“her”鸠儿,第二個索引為“here”,那么第二個索引會被存儲為“3,e”厕氨,這樣的缺點是同一個節(jié)點中的索引只能采用順序查找进每。

MyISAM的B+tree索引結構

二、InnoDB

2.1什么是聚簇索引

InnoDB 的數(shù)據(jù)文件本身就是索引文件命斧,在同一個結構中保存了B+Tree索引和數(shù)據(jù)行田晚。B+Tree的葉子節(jié)點上的data就是數(shù)據(jù)本身,key為主鍵冯丙,這是聚簇索引肉瓦。

聚簇索引的葉子節(jié)點包含了行的全部數(shù)據(jù),索引節(jié)點則只包含索引列胃惜。

“聚簇”表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起泞莉。由于無法同時把數(shù)據(jù)行存放在兩個不同的地方,所以一個表只能有一個聚簇索引船殉。
InnoDB的索引B+Tree索引結構

非聚簇索引鲫趁,葉子節(jié)點上的data是主鍵 (所以聚簇索引的key,不能過長)利虫。為什么存放的主鍵挨厚,而不是記錄所在地址呢堡僻,理由相當簡單,因為記錄所在地址并不能保證一定不會變疫剃,但主鍵可以保證钉疫。

2.1聚簇索引的讀取

聚 簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的,即:只要索引是相鄰的巢价,那么對應的數(shù)據(jù)一定也是相鄰地存放在磁盤上的牲阁。如果主鍵不是自增id,就會不斷地調(diào)整數(shù)據(jù)的物理地址壤躲、分頁城菊,當然也有其他一些措施來減少這些操作,但卻無法徹底避免碉克。如果是自增的凌唬,那就簡單了,它只需要一 頁一頁地寫漏麦,索引結構相對緊湊客税,磁盤碎片少,效率也高撕贞。

聚簇索引不但在檢索上可以大大滴提高效率霎挟,在數(shù)據(jù)讀取上也一樣。比如:需要查詢f~t的所有單詞麻掸。

一個使用MyISAM的主索引,一個使用InnoDB的聚簇索引赐纱。兩種索引的B+Tree檢索時間一樣脊奋,但讀取時卻有了差異。

因為MyISAM的主索引并非聚簇索引疙描,那么他的數(shù)據(jù)的物理地址必然是凌亂的诚隙,拿到這些物理地址,按照合適的算法進行I/O讀取起胰,于是開始不停的尋道不停的旋轉久又。聚簇索引則只需一次I/O。

不過效五,如果涉及到大數(shù)據(jù)量的排序地消、全表掃描、count之類的操作的話畏妖,還是MyISAM占優(yōu)勢些脉执,因為索引所占空間小,這些操作是需要在內(nèi)存中完成的戒劫。

鑒于聚簇索引的范圍查詢效率半夷,很多人認為使用主鍵作為聚簇索引太多浪費婆廊,畢竟幾乎不會使用主鍵進行范圍查詢。但若再考慮到聚簇索引的存儲巫橄,就不好定論了淘邻。

2.2非聚簇索引

InnoDB的二級索引與主鍵索引有很大的不同。InnoDB的二級索引的葉子節(jié)點存放的是KEY字段加主鍵值湘换,而不是行指針(row pointers)宾舅,這減小了移動數(shù)據(jù)或者數(shù)據(jù)頁面分裂時維護二級索引的開銷,因為InnoDB不需要更新索引的行指針枚尼。

這意味著通過二級索引查找行贴浙,存儲引擎需要找到二級索引的葉子節(jié)點獲得對應的主鍵值,然后根據(jù)這個主鍵值去聚簇索引查找到對應的行署恍。

2.3聚簇索引優(yōu)缺點

聚簇索引的優(yōu)點:

1崎溃、數(shù)據(jù)訪問更快,聚集索引將索引和數(shù)據(jù)都保存在同一個B+Tree中盯质,因此從聚集索引中獲取數(shù)據(jù)通常比非聚集索引中查找更快袁串;

2、使用覆蓋索引的查詢可以直接使用節(jié)點頁(非葉子頁)中的主鍵值呼巷;

3囱修、可以將相關的數(shù)據(jù)聚集在一起。例如實現(xiàn)電子郵箱時王悍,可以根據(jù)用戶ID來聚集數(shù)據(jù)破镰,這樣只需要從磁盤讀取少數(shù)的數(shù)據(jù)頁就能獲取某個用戶的全部郵件。

聚簇索引的缺點:

1压储、 更新聚集索引列的代價很高鲜漩,因為會導致InnoDB將被更新的行移動到新的位置;

2集惋、 二級索引會比你想象要大孕似,因為二級索引葉子節(jié)點存放了二級索引鍵值和相應行的主鍵值;

3刮刑、 二級索引訪問數(shù)據(jù)需要二次查找喉祭,而不是一次;

4雷绢、 聚集索引插入數(shù)據(jù)時速度比較慢(時間花費在“物理存儲的排序”上)泛烙,插入數(shù)據(jù)要先找到位置然后才插入。

參考

1习寸、MySQL聚簇索引
2胶惰、MYSQL性能調(diào)優(yōu): 對聚簇索引和非聚簇索引的認識
3、mysql索引原理之聚簇索引
4霞溪、Mysql聚簇索引和非聚簇索引原理(數(shù)據(jù)庫)
5孵滞、MySQL索引實現(xiàn)原理分析
6中捆、MySql數(shù)據(jù)庫索引原理
7、MySQL索引背后的數(shù)據(jù)結構及算法原理
8坊饶、MySql索引原理與使用大全

?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末泄伪,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子匿级,更是在濱河造成了極大的恐慌蟋滴,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,743評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件痘绎,死亡現(xiàn)場離奇詭異津函,居然都是意外死亡,警方通過查閱死者的電腦和手機孤页,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評論 3 385
  • 文/潘曉璐 我一進店門尔苦,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人行施,你說我怎么就攤上這事允坚。” “怎么了蛾号?”我有些...
    開封第一講書人閱讀 157,285評論 0 348
  • 文/不壞的土叔 我叫張陵稠项,是天一觀的道長。 經(jīng)常有香客問我鲜结,道長展运,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,485評論 1 283
  • 正文 為了忘掉前任精刷,我火速辦了婚禮乐疆,結果婚禮上,老公的妹妹穿的比我還像新娘贬养。我一直安慰自己,他們只是感情好琴庵,可當我...
    茶點故事閱讀 65,581評論 6 386
  • 文/花漫 我一把揭開白布误算。 她就那樣靜靜地躺著,像睡著了一般迷殿。 火紅的嫁衣襯著肌膚如雪儿礼。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,821評論 1 290
  • 那天庆寺,我揣著相機與錄音蚊夫,去河邊找鬼。 笑死懦尝,一個胖子當著我的面吹牛知纷,可吹牛的內(nèi)容都是我干的壤圃。 我是一名探鬼主播,決...
    沈念sama閱讀 38,960評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼琅轧,長吁一口氣:“原來是場噩夢啊……” “哼伍绳!你這毒婦竟也來了?” 一聲冷哼從身側響起乍桂,我...
    開封第一講書人閱讀 37,719評論 0 266
  • 序言:老撾萬榮一對情侶失蹤冲杀,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后睹酌,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體权谁,經(jīng)...
    沈念sama閱讀 44,186評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,516評論 2 327
  • 正文 我和宋清朗相戀三年憋沿,在試婚紗的時候發(fā)現(xiàn)自己被綠了旺芽。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,650評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡卤妒,死狀恐怖甥绿,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情则披,我是刑警寧澤共缕,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布,位于F島的核電站士复,受9級特大地震影響图谷,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜阱洪,卻給世界環(huán)境...
    茶點故事閱讀 39,936評論 3 313
  • 文/蒙蒙 一便贵、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧冗荸,春花似錦承璃、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至程癌,卻和暖如春舷嗡,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背嵌莉。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評論 1 266
  • 我被黑心中介騙來泰國打工进萄, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人。 一個月前我還...
    沈念sama閱讀 46,370評論 2 360
  • 正文 我出身青樓中鼠,卻偏偏與公主長得像可婶,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子兜蠕,可洞房花燭夜當晚...
    茶點故事閱讀 43,527評論 2 349

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

  • 聚簇索引并不是一種單獨的索引類型扰肌,而是一種數(shù)據(jù)存儲方式。比如熊杨,InnoDB的聚簇索引使用B+Tree的數(shù)據(jù)結構存儲...
    sherlock_6981閱讀 1,859評論 0 2
  • 聚簇索引并不是一種單獨的索引類型曙旭,而是一種數(shù)據(jù)存儲方式。比如晶府,InnoDB的聚簇索引使用B+Tree的數(shù)據(jù)結構存儲...
    大頭8086閱讀 17,468評論 7 40
  • 《高性能MySQL》&《MySQL技術內(nèi)幕 InnoDB存儲引擎》筆記 第一章 MySQL架構與歷史 MySQL的...
    xiaogmail閱讀 12,745評論 0 39
  • 索引 數(shù)據(jù)庫中的查詢操作非常普遍川陆,索引就是提升查找速度的一種手段 索引的類型 從數(shù)據(jù)結構角度分 1.B+索引:傳統(tǒng)...
    一凡呀閱讀 2,886評論 0 8
  • Vi淺淺 我的小肉剂习,第一次正式畫水彩,用了水粉紙感覺效果不是很好较沪,紙面不夠光滑顏色暈染沒有那么強鳞绕,肉肉的質(zhì)感總是讓...
    Vi淺淺閱讀 115評論 2 6