MySql索引那些事

木木匠發表於2019-03-31
MySql索引

MySql索引那些事

概述

上一篇文章《一條sql語句在mysql中是如何執行的》我們聊到了sql語句內部的執行，包括InnoDB引擎是如何支援事務的，如何做到可以備份恢復的，那麼今天我們來聊一聊MySql索引的那些事，在這篇文章中，我會主要聊聊InnoDB下索引的資料結構，索引如何起作用的，如何更好的利用索引提高效率。

一、什麼是索引

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

二、索引的分類

分類角度 索引名稱
資料結構 B+樹，Hash索引，R-Tree等
儲存層面 聚簇索引，非聚簇索引
邏輯層面 主鍵索引，普通索引，複合索引，唯一索引，空間索引等

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

3.1 InnoDB下索引的結構

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

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

複製程式碼

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

MySql索引那些事

上圖中展示了兩部分內容，第一個圖為聚簇索引（主鍵索引）的內容，可以看到，資料按照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,
  PRIMARY KEY (`id`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

複製程式碼

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

MySql索引那些事

我們根據圖可以知道，聯合索引是和建立索引欄位順序有關的，上面這個例子就是先以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講》專欄

相關文章