MySql索引那些事

今天我們來聊一聊MySql索引的那些事，在這篇文章中，我會主要聊聊InnoDB下索引的資料結構，索引如何運作的，如何更好的利用索引提高效率。

一、什麼是索引

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

二、索引的分類

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

三、索引實例分析(以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开头的名字，查询语句就是

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select name from person where name like &#39;A%&#39;</span><br/>

这个时候就可以满足最左前缀规则来使用索引查询了，这里就会依赖索引查询到第一个首字母是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如下：

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select * from person where name like &#39;A%&#39; and age =19;</span><br/>

那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。

在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。

3.5 索引的优点与缺点

说了这么多关于索引的内容，我们来谈谈索引的优缺点。

优点：

减少服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机IO变为顺序IO

缺点

索引会占用额外的存储空间索引的维护需要一定的成本，插入数据后需要保证原来的索引有序，所以也会影响一定的数据库性能。

五、总结

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

六、參考

《高效能MySql》第3版

《MySql45講》欄位

【推薦課程：MySQL影片教學】  

#

以上是MySql索引那些事的詳細內容。更多資訊請關注PHP中文網其他相關文章！