MySQL 數(shù)據(jù)庫索引原理與分類

MySQL 數(shù)據(jù)庫專題放送~

前言


數(shù)據(jù)庫索引本質(zhì)上是一種數(shù)據(jù)結(jié)構(gòu)(存儲結(jié)構(gòu)+算法),目的是為了加快目標數(shù)據(jù)檢索的速度内斯。

目錄


1.索引的本質(zhì)與原理蒲每?
2.索引的分類?
3.福利彩蛋

1.索引的本質(zhì)與原理


我們先看一個問題:

假設(shè)現(xiàn)在有100000條從0到10000且從大到小排列的整型數(shù)據(jù)扫夜,1條數(shù)據(jù)的大小假設(shè)(真的只是假設(shè))是1KB,操作系統(tǒng)的每次I/O數(shù)據(jù)塊(頁)大小是8KB。
如果現(xiàn)在我要查找其中 50001 這個數(shù)據(jù)值驰徊,有如下幾個方式:
1.最蠢的方式笤闯,遍歷,每次遍歷到一個值棍厂,就用這個值跟目標值做對比颗味,如果不等于那么查找下一個。這樣的話那么每次I/O是8條數(shù)據(jù)牺弹,目標數(shù)據(jù)在50001/8 約6600多次I/O 才能找到目標數(shù)據(jù)浦马。
2.二分查找,最好一次性將100000條數(shù)據(jù)全部讀到內(nèi)存张漂,這樣查找也是很快的晶默。

但是即使二分查找很快,但這些數(shù)據(jù)也不能單單通過一次I/O全部進入內(nèi)存航攒,進行運算磺陡。

那么怎樣在I/O 塊大小 的限制下快速利用二分查找找到目標值呢?我們得引入新的數(shù)據(jù)結(jié)構(gòu),B+樹正好可以解決上述I/O塊大小的限制币他,解決限制不是說增大了限制范圍坞靶,而是我們在此限制上改變了數(shù)據(jù)的存儲結(jié)構(gòu),即在同等限制條件下圆丹,快速檢索到目標數(shù)據(jù)滩愁,如下是B+樹的原理講解:

注意,我們主要講解索引的原理辫封,沒有必要過于糾結(jié)B+樹的各種操作硝枉,及代碼實現(xiàn)

1.1 B+ 樹

B+樹圖示

根據(jù)上圖所示,及其論文定義:

1.圖上藍色的塊為關(guān)鍵字倦微,我們發(fā)現(xiàn)所有的關(guān)鍵字最終都會被包含在葉子節(jié)點當中妻味。
  圖上的黃色區(qū)塊表示的是子樹的指針域,比如根節(jié)點下的P2指向的就是28-65之間的索引欣福。
2.所有的葉子結(jié)點中包含了全部關(guān)鍵字的信息责球,及指向含有這些關(guān)鍵字記錄的指針,且葉子結(jié)點本身依關(guān)鍵字的大小自小而大
  的順序鏈接拓劝。 (而B 樹的葉子節(jié)點并沒有包括全部需要查找的信息)
3.所有的非終端結(jié)點可以看成是索引部分雏逾,結(jié)點中僅含有其子樹根結(jié)點中最大
 (或最小)關(guān)鍵字郑临。 (而B 樹的非終節(jié)點也包含需要查找的有效信息)

現(xiàn)在我們來看下查找數(shù)據(jù) 60 的 查找過程栖博,如下所示:

1.I/O第一次:讀入5、28厢洞、65 數(shù)據(jù)塊仇让,在此同級別節(jié)點塊上,60在28到65之間(其實是二分查找)躺翻,那走P2指針指向的子樹丧叽。
2.I/O第二次:讀入28、35公你、56 數(shù)據(jù)塊踊淳,在此同級別節(jié)點塊上,60大于56陕靠,所以走P3指針指向的子樹(上圖中就是葉子節(jié)點)迂尝。
3.I/O第三次:讀入葉子節(jié)點,在這個葉子節(jié)點中懦傍,使用二分查找算法找到目標值60。

由上述查找過程所示統(tǒng)共需要三次I/O就能查到目標值芦劣,性能大大提升粗俱。

2.索引的分類?


2.1 聚簇索引 & 非聚簇索引

InnoDB 主鍵使用的是聚簇索引虚吟,MyISAM 不管是主鍵索引寸认,還是二級索引使用的都是非聚簇索引签财。

下圖形象說明了聚簇索引表(InnoDB)和非聚簇索引(MyISAM)的區(qū)別:


聚簇索引與非聚簇索引

1.對于非聚簇索引表來說(右圖),表數(shù)據(jù)和索引是分成兩部分存儲的偏塞,主鍵索引和二級索引存儲上沒有任何區(qū)別唱蒸。使用的是B+樹作為索引的存儲結(jié)構(gòu),所有的節(jié)點都是索引灸叼,葉子節(jié)點存儲的是索引+索引對應(yīng)的記錄的地址神汹。


2.對于聚簇索引表來說(左圖),表數(shù)據(jù)是和主鍵一起存儲的古今,主鍵索引的葉結(jié)點存儲行數(shù)據(jù)(包含了主鍵值)屁魏,二級索引的葉結(jié)點存儲行的主鍵值。使用的是B+樹作為索引的存儲結(jié)構(gòu)捉腥,非葉子節(jié)點都是索引關(guān)鍵字氓拼,但非葉子節(jié)點中的關(guān)鍵字中不存儲對應(yīng)記錄的具體內(nèi)容或內(nèi)容地址。葉子節(jié)點上的數(shù)據(jù)是主鍵與具體記錄(數(shù)據(jù)內(nèi)容)抵碟。

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

1.當你需要取出一定范圍內(nèi)的數(shù)據(jù)時
桃漾,用聚簇索引也比用非聚簇索引好。
2.當通過聚簇索引查找目標數(shù)據(jù)時理論上比非聚簇索引要快拟逮,因為非聚簇索引定位到對應(yīng)主鍵時還要多一次目標記錄尋址,即多一次I/O撬统。

聚簇索引的缺點

1.插入速度嚴重依賴于插入順序,按照主鍵的順序插入是最快的方式唱歧,否則將會出現(xiàn)頁分裂宪摧,嚴重影響性能。因此颅崩,對于InnoDB表几于,我們一般都會定義一個自增的ID列為主鍵。
2.更新主鍵的代價很高沿后,因為將會導(dǎo)致被更新的行移動沿彭。因此,對于InnoDB表尖滚,我們一般定義主鍵為不可更新喉刘。
3.二級索引訪問需要兩次索引查找,第一次找到主鍵值漆弄,第二次根據(jù)主鍵值找到行數(shù)據(jù)睦裳。
二級索引的葉節(jié)點存儲的是主鍵值,而不是行指針(非聚簇索引存儲的是指針或者說是地址)撼唾,這是為了減少當出現(xiàn)行移動或數(shù)據(jù)頁分裂時二級索引的維護工作廉邑,但會讓二級索引占用更多的空間。
4.采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多,因為插入要保證主鍵不能重復(fù)蛛蒙,判斷主鍵不能重復(fù)糙箍,采用的方式在不同的索引下面會有很大的性能差距,聚簇索引遍歷所有的葉子節(jié)點牵祟,非聚簇索引也判斷所有的葉子節(jié)點深夯,但是聚簇索引的葉子節(jié)點除了帶有主鍵還有記錄值,記錄的大小往往比主鍵要大的多诺苹。這樣就會導(dǎo)致聚簇索引在判定新記錄攜帶的主鍵是否重復(fù)時進行昂貴的I/O代價咕晋。

唯一索引

主鍵就是唯一索引,但是唯一索引不一定是主鍵筝尾,唯一索引可以為空捡需,但是空值只能有一個,主鍵不能為空筹淫。
普通唯一索引:單個字段上建立唯一索引站辉,需要此字段所在的列上不能有重復(fù)的值,屬于二級索引损姜。
復(fù)合唯一索引:多個字段上聯(lián)合建立唯一索引饰剥,屬于二級索引。

覆蓋索引

查找的目標數(shù)據(jù)摧阅, 包含在索引中汰蓉,如建立idx_colum1_colum2.

select colum1 from table where colum1 = ? and colum2 > ?

通過查詢索引就能確定最終的數(shù)據(jù),不用再利用葉子節(jié)點中存儲的主鍵值去查詢對應(yīng)的數(shù)據(jù)棒卷。
覆蓋索引的性能是極高的顾孽。

索引原理篇講述完,下一篇講解索引的優(yōu)化比规,以及 explain 工具的使用若厚。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市蜒什,隨后出現(xiàn)的幾起案子测秸,更是在濱河造成了極大的恐慌,老刑警劉巖灾常,帶你破解...
    沈念sama閱讀 211,817評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件霎冯,死亡現(xiàn)場離奇詭異,居然都是意外死亡钞瀑,警方通過查閱死者的電腦和手機沈撞,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,329評論 3 385
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來雕什,“玉大人缠俺,你說我怎么就攤上這事拧廊。” “怎么了晋修?”我有些...
    開封第一講書人閱讀 157,354評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長凰盔。 經(jīng)常有香客問我墓卦,道長,這世上最難降的妖魔是什么户敬? 我笑而不...
    開封第一講書人閱讀 56,498評論 1 284
  • 正文 為了忘掉前任落剪,我火速辦了婚禮,結(jié)果婚禮上尿庐,老公的妹妹穿的比我還像新娘忠怖。我一直安慰自己,他們只是感情好抄瑟,可當我...
    茶點故事閱讀 65,600評論 6 386
  • 文/花漫 我一把揭開白布凡泣。 她就那樣靜靜地躺著,像睡著了一般皮假。 火紅的嫁衣襯著肌膚如雪鞋拟。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,829評論 1 290
  • 那天惹资,我揣著相機與錄音贺纲,去河邊找鬼。 笑死褪测,一個胖子當著我的面吹牛猴誊,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播侮措,決...
    沈念sama閱讀 38,979評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼懈叹,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了萝毛?” 一聲冷哼從身側(cè)響起项阴,我...
    開封第一講書人閱讀 37,722評論 0 266
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎笆包,沒想到半個月后环揽,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,189評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡庵佣,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,519評論 2 327
  • 正文 我和宋清朗相戀三年歉胶,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片巴粪。...
    茶點故事閱讀 38,654評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡通今,死狀恐怖粥谬,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情辫塌,我是刑警寧澤漏策,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布,位于F島的核電站臼氨,受9級特大地震影響掺喻,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜储矩,卻給世界環(huán)境...
    茶點故事閱讀 39,940評論 3 313
  • 文/蒙蒙 一感耙、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧持隧,春花似錦即硼、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,762評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至呀狼,卻和暖如春层皱,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背赠潦。 一陣腳步聲響...
    開封第一講書人閱讀 31,993評論 1 266
  • 我被黑心中介騙來泰國打工叫胖, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人她奥。 一個月前我還...
    沈念sama閱讀 46,382評論 2 360
  • 正文 我出身青樓瓮增,卻偏偏與公主長得像,于是被迫代替她去往敵國和親哩俭。 傳聞我的和親對象是個殘疾皇子绷跑,可洞房花燭夜當晚...
    茶點故事閱讀 43,543評論 2 349

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