MySql索引那些事

一、什么是索引

數(shù)據(jù)庫索引,是數(shù)據(jù)庫管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)给涕,以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)迅腔。就像我們以前用的新華字典的目錄一樣,能幫助我們快速查詢到某一個(gè)字陶缺。

二钾挟、索引的分類


三、索引實(shí)例分析(以InnoDB為例)

3.1 InnoDB下索引的結(jié)構(gòu)

InnoDB下饱岸,表都是根據(jù)主鍵順序以索引的形式存放的掺出,這種數(shù)據(jù)存儲方式也被稱為聚簇索引,“聚簇”就是表示數(shù)據(jù)行和相鄰的鍵值緊湊的存儲在一起苫费,也就是數(shù)據(jù)行實(shí)際上是存儲在索引的葉子頁中汤锨。我們創(chuàng)建一張表來實(shí)際說明下InnoDB下的索引結(jié)構(gòu),建表語句如下:

create table person(id int primary key,

age int not nullindex (age)engine=InnoDB;

然后我們插入五條數(shù)據(jù)分別為(1,15),(2,17),(6,20),(10,18),(19,21)百框,索引的樹結(jié)構(gòu)如下:


上圖中展示了兩部分內(nèi)容闲礼,第一個(gè)圖為聚簇索引(主鍵索引)的內(nèi)容,可以看到铐维,數(shù)據(jù)按照Id的大小排序柬泽,對應(yīng)的索引會包含該索引的整行數(shù)據(jù)。

第二個(gè)圖展示了用age做索引的索引結(jié)構(gòu)圖嫁蛇,也就是非聚簇索引(非主鍵索引)锨并,可以看到索引以年齡排序,但是和主鍵索引不同的是睬棚,年齡索引對應(yīng)的卻是Id第煮,所以我們可以知道非主鍵索引記錄的內(nèi)容就是主鍵索引的值解幼。

這里可能有同學(xué)會有疑問,如果我建表的時(shí)候沒有指定主鍵的話包警,索引結(jié)構(gòu)又是如何的呢撵摆?其實(shí)在InnoDB中,如果沒有定義主鍵害晦,那么他會選擇一個(gè)唯一的非空索引代替特铝。如果沒有這樣的索引,那么他會隱式的定義一個(gè)主鍵來作為聚簇索引壹瘟。所以無論你是否設(shè)置主鍵苟呐,InnoDB還是會幫你滿足以上圖的形式來索引數(shù)據(jù)。接下來我們分析下索引查詢的流程俐筋。

3.2 索引查詢分析

假設(shè)我們執(zhí)行一條查詢語句?select * from person where ID = 6,因?yàn)橹苯邮褂玫氖侵麈IID查詢,所以就會用主鍵索引严衬,由于主鍵索引直接關(guān)聯(lián)了整行所有數(shù)據(jù)澄者,所以,引擎只要執(zhí)行一次就能查詢出結(jié)果请琳。

如果執(zhí)行的sql語句是非主鍵索引

select * from person where age = 18

上述語句會走age的普通索引粱挡,索引先根據(jù)age搜索等于18的索引記錄,找到ID=10的記錄俄精,然后再到主鍵索引搜索一次询筏,然后拿出需要查詢的數(shù)據(jù)。

從普通索引查出主鍵索引竖慧,然后查詢出數(shù)據(jù)的過程叫做回表嫌套。由于回表需要多執(zhí)行一次查詢,這也是為什么主鍵索引要比普通索引要快的原因圾旨,所以踱讨,我們要盡量使用主鍵查詢。

3.3 覆蓋索引

我們通常創(chuàng)建索引的依據(jù)都是根據(jù)查詢的where條件砍的,但是這只是我們通常的做法痹筛,我們根據(jù)上面的分析可以知道,如果要想查詢效率高廓鞠,第一帚稠,使用主鍵索引,第二床佳,避免回表滋早,也就是盡可能的在索引中就能獲取想要的數(shù)據(jù)。如果一個(gè)索引包含了需要查詢的字段夕土,那么我們就叫做“覆蓋索引”馆衔。

那么如何建立一個(gè)覆蓋索引呢瘟判?答案是通過聯(lián)合索引來實(shí)現(xiàn),通過聯(lián)合索引的字段來覆蓋要查詢的字段角溃,從而達(dá)到索引覆蓋的效果拷获。

我們把上面的建表語句改造下,來分析下如何實(shí)現(xiàn)覆蓋索引减细。

CREATE TABLE `person` (

? `id` int(11) NOT NULL,

? `age` int(11) DEFAULT NULL,

? `name` varchar(20) DEFAULT NULL,

? `sex` varchar(1) DEFAULT NULL,

? PRIMARY KEY (`id`),

? KEY `name_age` (`name`,`age`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

上面我創(chuàng)建了一個(gè)name和age的聯(lián)合索引匆瓜,索引結(jié)構(gòu)圖表示如下:


我們根據(jù)圖可以知道,聯(lián)合索引是和創(chuàng)建索引字段順序有關(guān)的未蝌,上面這個(gè)例子就是先以name排序驮吱,然后name相同再以age為標(biāo)準(zhǔn)排序。那么我們建表后該如何達(dá)到覆蓋索引的效果呢萧吠?相信有些同學(xué)已經(jīng)知道了怎么寫sql可以達(dá)到覆蓋索引效果左冬,sql如下:

select name,age from person where name = "Barry"

因?yàn)槲覀冃枰樵兊淖侄蝞ame和age,都在索引中可以直接查詢到了纸型,所以不需要查找到主鍵ID拇砰,然后再回表了。

看到這里狰腌,肯定有同學(xué)會說除破,既然這樣的話,我把所有需要查詢的字段組合都建上聯(lián)合索引不就行了嗎琼腔?答案是:不行瑰枫。因?yàn)樗饕彩切枰目臻g的,而且維護(hù)索引也是需要成本的丹莲,這一點(diǎn)我會在后面的優(yōu)缺點(diǎn)中提到光坝。那么有沒有別的方式可以盡可能的實(shí)現(xiàn)不回表的效果呢?這里我們就要引入MySql的最左前綴原則了甥材。

什么叫最左前綴原則呢教馆?就是在索引的匹配中,可以以索引的最左N個(gè)字段,也可以是字符串索引的最左N個(gè)字符擂达。比如在上圖中土铺,要查詢以A開頭的名字,查詢語句就是

select name from person where name like 'A%'

這個(gè)時(shí)候就可以滿足最左前綴規(guī)則來使用索引查詢了板鬓,這里就會依賴索引查詢到第一個(gè)首字母是A的名字悲敷,然后向后遍歷,直到不滿足條件為止俭令。

那么最左N個(gè)字段是什么意思呢后德?意思就是索引(name,age),可以直接利用 name來當(dāng)做單獨(dú)索引使用,可以只使用聯(lián)合索引的部分字段抄腔,但是必須是順序一致瓢湃,比如索引(a,b,c)理张,如果要想使用最左前綴規(guī)則,可以使用索引a,ab绵患。

我們也可以利用該規(guī)則來少維護(hù)一個(gè)或多個(gè)索引雾叭,比如我們需要 a,ab,abc的查詢,那就只需要(a,b,c)聯(lián)合索引就滿足要求了落蝙。

3.4 索引下推

在MySql 5.6版本中引入了一個(gè)新特性织狐,叫做“索引條件推送(index condition pushdown)”,這也稱為索引下推筏勒。那么索引下推是這個(gè)什么東東呢移迫?其實(shí)從“索引條件推送”這個(gè)名字就可以表明,這個(gè)特性是可以在索引中的字段進(jìn)行條件判斷管行,然后過濾不滿足條件的記錄厨埋,減少回表的次數(shù)。

比如以上圖中的數(shù)據(jù)為準(zhǔn)捐顷,sql如下:

select * from person where name like 'A%' and age =19;

那么如果沒有索引下推的情況下揽咕,首先會根據(jù)索引查詢出名字以A開頭的所有記錄,然后查詢出ID套菜,然后回表去查詢對應(yīng)的ID記錄,最后再判斷age=19设易,返回滿足條件的語句逗柴。因?yàn)闈M足A開頭的記錄有2條,所以這種情況下顿肺,會回表2次戏溺。

在索引下推情況下,InnoDB會在索引內(nèi)部直接判斷age=19是否滿足條件屠尊,過濾掉不滿足條件的記錄旷祸,所以只返回了一條,也就是只需要回表一次讼昆。從而提高了性能托享。

3.5 索引的優(yōu)點(diǎn)與缺點(diǎn)

說了這么多關(guān)于索引的內(nèi)容,我們來談?wù)勊饕膬?yōu)缺點(diǎn)浸赫。

優(yōu)點(diǎn):

減少服務(wù)器需要掃描的數(shù)據(jù)量

索引可以幫助服務(wù)器避免排序和臨時(shí)表

索引可以將隨機(jī)IO變?yōu)轫樞騃O

缺點(diǎn)

索引會占用額外的存儲空間

索引的維護(hù)需要一定的成本闰围,插入數(shù)據(jù)后需要保證原來的索引有序,所以也會影響一定的數(shù)據(jù)庫性能既峡。

五羡榴、總結(jié)

這篇博文我主要說了,索引的定義运敢,索引的分類校仑,索引按照不同的角度可以分為常見的哪幾種忠售。然后我重點(diǎn)說了在InnoDB下索引的索引的數(shù)據(jù)結(jié)構(gòu)。 主鍵索引和非主鍵索引的區(qū)別就是查詢主鍵索引可以直接返回?cái)?shù)據(jù)迄沫,非主鍵索引需要先查詢出主鍵ID稻扬,然后再查詢出數(shù)據(jù),這個(gè)過程就叫做回表邢滑。我們可以通過覆蓋索引減少回表的次數(shù)腐螟,從而達(dá)到提高性能的效果。在mysql5.6以后困后,InnoDB可以支持索引下推乐纸,在使用聯(lián)合索引的時(shí)候,如果可以在索引判斷條件摇予,那么就在索引中過濾不滿足條件的行汽绢,從而減少回表次數(shù)。

覺得不錯請點(diǎn)贊支持侧戴,歡迎留言或進(jìn)我的個(gè)人群855801563領(lǐng)取【架構(gòu)資料專題目合集90期】宁昭、【BATJTMD大廠JAVA面試真題1000+】,本群專用于學(xué)習(xí)交流技術(shù)酗宋、分享面試機(jī)會积仗,拒絕廣告,我也會在群內(nèi)不定期答題蜕猫、探討寂曹。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市回右,隨后出現(xiàn)的幾起案子隆圆,更是在濱河造成了極大的恐慌,老刑警劉巖翔烁,帶你破解...
    沈念sama閱讀 211,743評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件渺氧,死亡現(xiàn)場離奇詭異,居然都是意外死亡蹬屹,警方通過查閱死者的電腦和手機(jī)侣背,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評論 3 385
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來慨默,“玉大人秃踩,你說我怎么就攤上這事∫捣ぃ” “怎么了憔杨?”我有些...
    開封第一講書人閱讀 157,285評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長蒜胖。 經(jīng)常有香客問我消别,道長抛蚤,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,485評論 1 283
  • 正文 為了忘掉前任寻狂,我火速辦了婚禮岁经,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘蛇券。我一直安慰自己缀壤,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,581評論 6 386
  • 文/花漫 我一把揭開白布纠亚。 她就那樣靜靜地躺著塘慕,像睡著了一般。 火紅的嫁衣襯著肌膚如雪蒂胞。 梳的紋絲不亂的頭發(fā)上图呢,一...
    開封第一講書人閱讀 49,821評論 1 290
  • 那天,我揣著相機(jī)與錄音骗随,去河邊找鬼蛤织。 笑死,一個(gè)胖子當(dāng)著我的面吹牛鸿染,可吹牛的內(nèi)容都是我干的指蚜。 我是一名探鬼主播,決...
    沈念sama閱讀 38,960評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼涨椒,長吁一口氣:“原來是場噩夢啊……” “哼摊鸡!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起丢烘,我...
    開封第一講書人閱讀 37,719評論 0 266
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎些椒,沒想到半個(gè)月后播瞳,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,186評論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡免糕,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,516評論 2 327
  • 正文 我和宋清朗相戀三年赢乓,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片石窑。...
    茶點(diǎn)故事閱讀 38,650評論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡牌芋,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出松逊,到底是詐尸還是另有隱情躺屁,我是刑警寧澤,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布经宏,位于F島的核電站犀暑,受9級特大地震影響驯击,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜耐亏,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,936評論 3 313
  • 文/蒙蒙 一徊都、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧广辰,春花似錦暇矫、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至干发,卻和暖如春朱巨,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背枉长。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評論 1 266
  • 我被黑心中介騙來泰國打工冀续, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人必峰。 一個(gè)月前我還...
    沈念sama閱讀 46,370評論 2 360
  • 正文 我出身青樓洪唐,卻偏偏與公主長得像,于是被迫代替她去往敵國和親吼蚁。 傳聞我的和親對象是個(gè)殘疾皇子凭需,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,527評論 2 349

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

  • 一、什么是索引 數(shù)據(jù)庫索引肝匆,是數(shù)據(jù)庫管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)粒蜈,以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)旗国。就像我們以前...
    java伯爵閱讀 422評論 0 0
  • 概述 今天我們來聊一聊MySql索引的那些事枯怖,在這篇文章中,我會主要聊聊InnoDB下索引的數(shù)據(jù)結(jié)構(gòu)能曾,索引如何起作...
    GeekerLou閱讀 198評論 0 0
  • MYSQL 基礎(chǔ)知識 1 MySQL數(shù)據(jù)庫概要 2 簡單MySQL環(huán)境 3 數(shù)據(jù)的存儲和獲取 4 MySQL基本操...
    Kingtester閱讀 7,787評論 5 116
  • 一度硝、MySQL優(yōu)化 MySQL優(yōu)化從哪些方面入手: (1)存儲層(數(shù)據(jù)) 構(gòu)建良好的數(shù)據(jù)結(jié)構(gòu)∈倜幔可以大大的提升我們S...
    寵辱不驚丶?xì)q月靜好閱讀 2,422評論 1 8
  • 最近總是有朋友說我胖蕊程,我真心不想解釋太多,我只是壯驼唱,實(shí)在搞不懂你們對于胖的定義藻茂,是不是比較大只點(diǎn),肌肉線條不夠清...
    768d9d9e00b9閱讀 202評論 0 0