概述

今天我們來(lái)聊一聊MySql索引的那些事，在這篇文章中，我會(huì)主要聊聊InnoDB下索引的數(shù)據(jù)結(jié)構(gòu)渊抽，索引如何起作用的，如何更好的利用索引提高效率议忽。

正文

一懒闷、什么是索引

數(shù)據(jù)庫(kù)索引，是數(shù)據(jù)庫(kù)管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)栈幸，以協(xié)助快速查詢愤估、更新數(shù)據(jù)庫(kù)表中數(shù)據(jù)。就像我們以前用的新華字典的目錄一樣速址，能幫助我們快速查詢到某一個(gè)字玩焰。

二、索引的分類

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

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

InnoDB下，表都是根據(jù)主鍵順序以索引的形式存放的嗅钻，這種數(shù)據(jù)存儲(chǔ)方式也被稱為聚簇索引皂冰，“聚簇”就是表示數(shù)據(jù)行和相鄰的鍵值緊湊的存儲(chǔ)在一起，也就是數(shù)據(jù)行實(shí)際上是存儲(chǔ)在索引的葉子頁(yè)中养篓。我們創(chuàng)建一張表來(lái)實(shí)際說(shuō)明下InnoDB下的索引結(jié)構(gòu)秃流，建表語(yǔ)句如下：

create table person(
id int primary key, 
age int not null
index (age)engine=InnoDB;

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

image

上圖中展示了兩部分內(nèi)容柳弄，第一個(gè)圖為聚簇索引（主鍵索引）的內(nèi)容舶胀，可以看到概说，數(shù)據(jù)按照Id的大小排序，對(duì)應(yīng)的索引會(huì)包含該索引的整行數(shù)據(jù)嚣伐。

第二個(gè)圖展示了用age做索引的索引結(jié)構(gòu)圖糖赔，也就是非聚簇索引（非主鍵索引），可以看到索引以年齡排序轩端，但是和主鍵索引不同的是放典，年齡索引對(duì)應(yīng)的卻是Id，所以我們可以知道非主鍵索引記錄的內(nèi)容就是主鍵索引的值基茵。

這里可能有同學(xué)會(huì)有疑問(wèn)奋构，如果我建表的時(shí)候沒(méi)有指定主鍵的話，索引結(jié)構(gòu)又是如何的呢拱层？其實(shí)在InnoDB中弥臼，如果沒(méi)有定義主鍵，那么他會(huì)選擇一個(gè)唯一的非空索引代替根灯。如果沒(méi)有這樣的索引径缅，那么他會(huì)隱式的定義一個(gè)主鍵來(lái)作為聚簇索引。所以無(wú)論你是否設(shè)置主鍵烙肺，InnoDB還是會(huì)幫你滿足以上圖的形式來(lái)索引數(shù)據(jù)纳猪。接下來(lái)我們分析下索引查詢的流程。

3.2 索引查詢分析

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

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

select * from person where age = 18;

上述語(yǔ)句會(huì)走age的普通索引宿饱，索引先根據(jù)age搜索等于18的索引記錄熏瞄，找到ID=10的記錄，然后再到主鍵索引搜索一次谬以，然后拿出需要查詢的數(shù)據(jù)强饮。

從普通索引查出主鍵索引，然后查詢出數(shù)據(jù)的過(guò)程叫做回表为黎。由于回表需要多執(zhí)行一次查詢邮丰，這也是為什么主鍵索引要比普通索引要快的原因，所以铭乾，我們要盡量使用主鍵查詢剪廉。

3.3 覆蓋索引

我們通常創(chuàng)建索引的依據(jù)都是根據(jù)查詢的where條件，但是這只是我們通常的做法炕檩，我們根據(jù)上面的分析可以知道斗蒋，如果要想查詢效率高，第一，使用主鍵索引泉沾，第二捞蚂，避免回表，也就是盡可能的在索引中就能獲取想要的數(shù)據(jù)跷究。如果一個(gè)索引包含了需要查詢的字段姓迅，那么我們就叫做“覆蓋索引”。

那么如何建立一個(gè)覆蓋索引呢俊马？答案是通過(guò)聯(lián)合索引來(lái)實(shí)現(xiàn)队贱，通過(guò)聯(lián)合索引的字段來(lái)覆蓋要查詢的字段，從而達(dá)到索引覆蓋的效果潭袱。

我們把上面的建表語(yǔ)句改造下柱嫌，來(lái)分析下如何實(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)圖表示如下：

image

我們根據(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é)會(huì)說(shuō)疾牲，既然這樣的話植捎，我把所有需要查詢的字段組合都建上聯(lián)合索引不就行了嗎？答案是：不行阳柔。因?yàn)樗饕彩切枰目臻g的焰枢，而且維護(hù)索引也是需要成本的，這一點(diǎn)我會(huì)在后面的優(yōu)缺點(diǎn)中提到舌剂。那么有沒(méi)有別的方式可以盡可能的實(shí)現(xiàn)不回表的效果呢济锄？這里我們就要引入MySql的最左前綴原則了。

什么叫最左前綴原則呢霍转？就是在索引的匹配中荐绝，可以以索引的最左N個(gè)字段,也可以是字符串索引的最左N個(gè)字符。比如在上圖中谴忧，要查詢以A開頭的名字很泊，查詢語(yǔ)句就是

select name from person where name like 'A%';

這個(gè)時(shí)候就可以滿足最左前綴規(guī)則來(lái)使用索引查詢了角虫，這里就會(huì)依賴索引查詢到第一個(gè)首字母是A的名字，然后向后遍歷委造，直到不滿足條件為止戳鹅。

那么最左N個(gè)字段是什么意思呢？意思就是索引（name,age),可以直接利用 name來(lái)當(dāng)做單獨(dú)索引使用昏兆，可以只使用聯(lián)合索引的部分字段枫虏，但是必須是順序一致，比如索引（a,b,c)爬虱，如果要想使用最左前綴規(guī)則隶债，可以使用索引a,ab。

我們也可以利用該規(guī)則來(lái)少維護(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)行條件判斷，然后過(guò)濾不滿足條件的記錄定罢，減少回表的次數(shù)笤虫。

比如以上圖中的數(shù)據(jù)為準(zhǔn)，sql如下：

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

那么如果沒(méi)有索引下推的情況下祖凫，首先會(huì)根據(jù)索引查詢出名字以A開頭的所有記錄琼蚯，然后查詢出ID，然后回表去查詢對(duì)應(yīng)的ID記錄蝙场，最后再判斷age=19凌停，返回滿足條件的語(yǔ)句。因?yàn)闈M足A開頭的記錄有2條售滤，所以這種情況下，會(huì)回表2次台诗。

在索引下推情況下完箩，InnoDB會(huì)在索引內(nèi)部直接判斷age=19是否滿足條件，過(guò)濾掉不滿足條件的記錄拉队，所以只返回了一條弊知，也就是只需要回表一次。從而提高了性能粱快。

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

說(shuō)了這么多關(guān)于索引的內(nèi)容秩彤，我們來(lái)談?wù)勊饕膬?yōu)缺點(diǎn)叔扼。

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

• 減少服務(wù)器需要掃描的數(shù)據(jù)量
• 索引可以幫助服務(wù)器避免排序和臨時(shí)表
• 索引可以將隨機(jī)IO變?yōu)轫樞騃O

缺點(diǎn)

• 索引會(huì)占用額外的存儲(chǔ)空間
• 索引的維護(hù)需要一定的成本，插入數(shù)據(jù)后需要保證原來(lái)的索引有序漫雷，所以也會(huì)影響一定的數(shù)據(jù)庫(kù)性能瓜富。

五、總結(jié)

這篇博文主要介紹了索引的定義降盹，索引的分類与柑，索引按照不同的角度可以分為常見的哪幾種。然后我重點(diǎn)說(shuō)了在InnoDB下索引的索引的數(shù)據(jù)結(jié)構(gòu)蓄坏。 主鍵索引和非主鍵索引的區(qū)別就是查詢主鍵索引可以直接返回?cái)?shù)據(jù)价捧，非主鍵索引需要先查詢出主鍵ID，然后再查詢出數(shù)據(jù)涡戳，這個(gè)過(guò)程就叫做回表结蟋。我們可以通過(guò)覆蓋索引減少回表的次數(shù)，從而達(dá)到提高性能的效果渔彰。在mysql5.6以后嵌屎，InnoDB可以支持索引下推，在使用聯(lián)合索引的時(shí)候胳岂，如果可以在索引判斷條件编整，那么就在索引中過(guò)濾不滿足條件的行，從而減少回表次數(shù)乳丰。

參考資料

1. MySql索引那些事