MySql索引那些事

一、什么是索引

數據庫索引，是數據庫管理系統中一個排序的數據結構，以協助快速查詢、更新數據庫表中數據。就像我們以前用的新華字典的目錄一樣，能幫助我們快速查詢到某一個字。

二、索引的分類

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

3.1 InnoDB下索引的結構

InnoDB下，表都是根據主鍵順序以索引的形式存放的，這種數據存儲方式也被稱為聚簇索引，“聚簇”就是表示數據行和相鄰的鍵值緊湊的存儲在一起，也就是數據行實際上是存儲在索引的葉子頁中。我們創建一張表來實際說明下InnoDB下的索引結構，建表語句如下：

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

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

上圖中展示了兩部分內容，第一個圖為聚簇索引（主鍵索引）的內容，可以看到，數據按照Id的大小排序，對應的索引會包含該索引的整行數據。

第二個圖展示了用age做索引的索引結構圖，也就是非聚簇索引（非主鍵索引），可以看到索引以年齡排序，但是和主鍵索引不同的是，年齡索引對應的卻是Id，所以我們可以知道非主鍵索引記錄的內容就是主鍵索引的值。

這里可能有同學會有疑問，如果我建表的時候沒有指定主鍵的話，索引結構又是如何的呢？其實在InnoDB中，如果沒有定義主鍵，那么他會選擇一個唯一的非空索引代替。如果沒有這樣的索引，那么他會隱式的定義一個主鍵來作為聚簇索引。所以無論你是否設置主鍵，InnoDB還是會幫你滿足以上圖的形式來索引數據。接下來我們分析下索引查詢的流程。

3.2 索引查詢分析

假設我們執行一條查詢語句 select * from person where ID = 6,因為直接使用的是主鍵ID查詢，所以就會用主鍵索引，由于主鍵索引直接關聯了整行所有數據，所以，引擎只要執行一次就能查詢出結果。

如果執行的sql語句是非主鍵索引

select * from person where age = 18

從普通索引查出主鍵索引，然后查詢出數據的過程叫做回表。由于回表需要多執行一次查詢，這也是為什么主鍵索引要比普通索引要快的原因，所以，我們要盡量使用主鍵查詢。上述語句會走age的普通索引，索引先根據age搜索等于18的索引記錄，找到ID=10的記錄，然后再到主鍵索引搜索一次，然后拿出需要查詢的數據。

3.3 覆蓋索引

我們通常創建索引的依據都是根據查詢的where條件，但是這只是我們通常的做法，我們根據上面的分析可以知道，如果要想查詢效率高，第一，使用主鍵索引，第二，避免回表，也就是盡可能的在索引中就能獲取想要的數據。如果一個索引包含了需要查詢的字段，那么我們就叫做“覆蓋索引”。

那么如何建立一個覆蓋索引呢？答案是通過聯合索引來實現，通過聯合索引的字段來覆蓋要查詢的字段，從而達到索引覆蓋的效果。

我們把上面的建表語句改造下，來分析下如何實現覆蓋索引。

 CREATE TABLE `person` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` varchar(1) DEFAULT NULL,

上面我創建了一個name和age的聯合索引，索引結構圖表示如下：

我們根據圖可以知道，聯合索引是和創建索引字段順序有關的，上面這個例子就是先以name排序，然后name相同再以age為標準排序。那么我們建表后該如何達到覆蓋索引的效果呢？相信有些同學已經知道了怎么寫sql可以達到覆蓋索引效果，sql如下：

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

因為我們需要查詢的字段name和age，都在索引中可以直接查詢到了，所以不需要查找到主鍵ID，然后再回表了。

看到這里，肯定有同學會說，既然這樣的話，我把所有需要查詢的字段組合都建上聯合索引不就行了嗎？答案是：不行。因為索引也是需要消耗空間的，而且維護索引也是需要成本的，這一點我會在后面的優缺點中提到。那么有沒有別的方式可以盡可能的實現不回表的效果呢？這里我們就要引入MySql的最左前綴原則了。

什么叫最左前綴原則呢？就是在索引的匹配中，可以以索引的最左N個字段,也可以是字符串索引的最左N個字符。比如在上圖中，要查詢以A開頭的名字，查詢語句就是

select name from person where name like 'A%'

這個時候就可以滿足最左前綴規則來使用索引查詢了，這里就會依賴索引查詢到第一個首字母是A的名字，然后向后遍歷，直到不滿足條件為止。

那么最左N個字段是什么意思呢？意思就是索引（name,age),可以直接利用 name來當做單獨索引使用，可以只使用聯合索引的部分字段，但是必須是順序一致，比如索引（a,b,c)，如果要想使用最左前綴規則，可以使用索引a,ab。

我們也可以利用該規則來少維護一個或多個索引，比如我們需要 a,ab,abc的查詢，那就只需要(a,b,c)聯合索引就滿足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一個新特性，叫做“索引條件推送（index condition pushdown)”，這也稱為索引下推。那么索引下推是這個什么東東呢？其實從“索引條件推送”這個名字就可以表明，這個特性是可以在索引中的字段進行條件判斷，然后過濾不滿足條件的記錄，減少回表的次數。

比如以上圖中的數據為準，sql如下：

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

那么如果沒有索引下推的情況下，首先會根據索引查詢出名字以A開頭的所有記錄，然后查詢出ID，然后回表去查詢對應的ID記錄，最后再判斷age=19，返回滿足條件的語句。因為滿足A開頭的記錄有2條，所以這種情況下，會回表2次。

在索引下推情況下，InnoDB會在索引內部直接判斷age=19是否滿足條件，過濾掉不滿足條件的記錄，所以只返回了一條，也就是只需要回表一次。從而提高了性能。

3.5 索引的優點與缺點

說了這么多關于索引的內容，我們來談談索引的優缺點。

優點：

減少服務器需要掃描的數據量索引可以幫助服務器避免排序和臨時表索引可以將隨機IO變為順序IO

缺點

索引會占用額外的存儲空間索引的維護需要一定的成本，插入數據后需要保證原來的索引有序，所以也會影響一定的數據庫性能。

五、總結

這篇博文我主要說了，索引的定義，索引的分類，索引按照不同的角度可以分為常見的哪幾種。然后我重點說了在InnoDB下索引的索引的數據結構。 主鍵索引和非主鍵索引的區別就是查詢主鍵索引可以直接返回數據，非主鍵索引需要先查詢出主鍵ID，然后再查詢出數據，這個過程就叫做回表。我們可以通過覆蓋索引減少回表的次數，從而達到提高性能的效果。在mysql5.6以后，InnoDB可以支持索引下推，在使用聯合索引的時候，如果可以在索引判斷條件，那么就在索引中過濾不滿足條件的行，從而減少回表次數。

六、參考

《高性能MySql》第3版

《MySql45講》專欄

【推薦課程：MySQL視頻教程】

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com