MySQL的索引詳解

一. 索引基礎(chǔ)

1.1 簡介

在MySQL中，索引（index）也叫做“鍵（key）”，它是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。

索引對于良好的性能非常關(guān)鍵，尤其是當(dāng)表中的數(shù)據(jù)量越來越大時，索引對性能的影響就愈發(fā)重要。

索引優(yōu)化應(yīng)該是對查詢性能優(yōu)化最有效的手段，創(chuàng)建一個真正最優(yōu)的索引經(jīng)常需要重寫SQL查詢語句。

1.2 索引的工作原理

要理解MySQL中索引的工作原理，最簡單的方法就是去看一看一本書的索引部分：比如你想在一本書中尋找某個主題，一般會先看書的索引目錄，找到對應(yīng)的章節(jié)、對應(yīng)的頁碼后就可以快速找到你想看的內(nèi)容。

在MySQL中，存儲引擎用類似的方法使用索引，其先在索引中查找對應(yīng)的值，然后根據(jù)匹配的索引記錄找到對應(yīng)的數(shù)據(jù)行，最后將數(shù)據(jù)結(jié)果集返回給客戶端。

1.3 索引的類型

在MySQL中，通常我們所指的索引類型，有以下幾種：

• 常規(guī)索引

  常規(guī)索引，也叫普通索引（index或key），它可以常規(guī)地提高查詢效率。一張數(shù)據(jù)表中可以有多個常規(guī)索引。常規(guī)索引是使用最普遍的索引類型，如果沒有明確指明索引的類型，我們所說的索引都是指常規(guī)索引。

• 主鍵索引

  主鍵索引（Primary Key），也簡稱主鍵。它可以提高查詢效率，并提供唯一性約束。一張表中只能有一個主鍵。被標(biāo)志為自動增長的字段一定是主鍵，但主鍵不一定是自動增長。一般把主鍵定義在無意義的字段上（如：編號），主鍵的數(shù)據(jù)類型最好是數(shù)值。

• 唯一索引

  唯一索引（Unique Key），可以提高查詢效率，并提供唯一性約束。一張表中可以有多個唯一索引。

• 全文索引

  全文索引（Full Text），可以提高全文搜索的查詢效率，一般使用Sphinx替代。但Sphinx不支持中文檢索，Coreseek是支持中文的全文檢索引擎，也稱作具有中文分詞功能的Sphinx。實際項目中，我們用到的是Coreseek。

• 外鍵索引

  外鍵索引（Foreign Key），簡稱外鍵，它可以提高查詢效率，外鍵會自動和對應(yīng)的其他表的主鍵關(guān)聯(lián)。外鍵的主要作用是保證記錄的一致性和完整性。

  注意：只有InnoDB存儲引擎的表才支持外鍵。外鍵字段如果沒有指定索引名稱，會自動生成。如果要刪除父表（如分類表）中的記錄，必須先刪除子表（帶外鍵的表，如文章表）中的相應(yīng)記錄，否則會出錯。 創(chuàng)建表的時候，可以給字段設(shè)置外鍵，如 foreign key(cate_id) references cms_cate(id)，由于外鍵的效率并不是很好，因此并不推薦使用外鍵，但我們要使用外鍵的思想來保證數(shù)據(jù)的一致性和完整性。

1.4 索引的方法

在MySQL中，索引是在存儲引擎層實現(xiàn)的，而不是在服務(wù)器層。MySQL支持的索引方法，也可以說成是索引的類型（這是廣義層面上的），主要有以下幾種：

B-Tree 索引

如果沒有特別指明類型，那多半說的就是B-Tree 索引。不同的存儲引擎以不同的方式使用B-Tree索引，性能也各不相同。例如：MyISAM使用前綴壓縮技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原始的數(shù)據(jù)格式存儲索引。再如MyISAM通過數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)主鍵引用被索引的行。

B-Tree 對索引列是順序存儲的，因此很適合查找范圍數(shù)據(jù)。它能夠加快訪問數(shù)據(jù)的速度，因為存儲引擎不再需要進(jìn)行全表掃描來獲取需要的數(shù)據(jù)。

如果一個索引中包括多個字段（列）的值，那它就是一個復(fù)合索引。復(fù)合索引對多個字段值進(jìn)行排序的依據(jù)是創(chuàng)建索引時列的順序。如下：

create table people (
 id int unsigned not null auto_increment primary key comment '主鍵id',
 last_name varchar(20) not null default '' comment '姓',
 first_name varchar(20) not null default '' comment '名',
 birthday date not null default '1970-01-01' comment '出生日期',
 gender tinyint unsigned not null default 3 comment '性別：1男，2女，3未知',
 key(last_name, first_name, birthday)
) engine=innodb default charset=utf8;

people表中也已經(jīng)插入了如下一些數(shù)據(jù)：

我們創(chuàng)建了一個復(fù)合索引 key(last_name, first_name, birthday)，對于表中的每一行數(shù)據(jù)，該索引中都包含了姓、名和出生日期這三列的值。索引也是根據(jù)這個順序來排序存儲的，如果某兩個人的姓和名都一樣，就會根據(jù)他們的出生日期來對索引排序存儲。

B-Tree 索引適用于全鍵值、鍵值范圍或鍵前綴查找，其中鍵前綴查找只適用于根據(jù)最左前綴查找。

復(fù)合索引對如下類型的查詢有效：

全值匹配

全值匹配指的是和索引中的所有列進(jìn)行匹配。例如：查找姓Allen、名Cuba、出生日期為1960-01-01的人。

SQL語句為：

select id,last_name,first_name,birthday from people where last_name='Allen' and first_name='Cuba' and birthday='1960-01-01';

。

匹配最左前綴

比如只使用索引的第一列，查找所有姓為Allen的人。SQL語句為：

select id,last_name,first_name,birthday from people where last_name='Allen';

匹配列前綴

比如只匹配索引的第一列的值的開頭部分，查找所有姓氏以A開頭的人。SQL語句為：

select id,last_name,first_name,birthday from people where last_name like ‘A%';

匹配范圍值

比如范圍匹配姓氏在Allen和Clinton之間的人。SQL語句為：

select id,last_name,first_name,birthday from people where last_name BETWEEN ‘Allen' And ‘Clinton';

這里也只使用了索引的第一列。

精確匹配第一列并范圍匹配后面的列

比如查找姓Allen，并且名字以字母C開頭的人。即全匹配復(fù)合索引的第一列，范圍匹配第二列。SQL語句為：

select id,last_name,first_name,birthday from people where last_name = ‘Allen' and first_name like'C%';

只訪問索引的查詢

B-Tree 通?？梢灾С帧爸辉L問索引的查詢”，即查詢只需要訪問索引，而無需訪問數(shù)據(jù)行。這和“覆蓋索引”的優(yōu)化相關(guān)，后面再講。

下面介紹一些復(fù)合索引會失效的情況：

（1）如果不是按照復(fù)合索引的最左列開始查找，則無法使用索引。例如：上面的例子中，索引無法用于查找查找名為Cuba的人，也無法查找某個特定出生日期的人，因為這兩列都不是復(fù)合索引 key(last_name, first_name, birthday) 的最左數(shù)據(jù)列。類似地，也無法查找姓氏以某個字母結(jié)尾的人，即like范圍查詢的模糊匹配符%，如果放在第一位會使索引失效。

（2）如果查找時跳過了索引中的列，則只有前面的索引列會用到，后面的索引列會失效。比如查找姓Allen且出生日期在某個特定日期的人。這里查找時，由于沒有指定查找名（first_name），故MySQL只能使用該復(fù)合索引的第一列（即last_name）。

（3）如果查詢中有某個列的范圍查詢，則該列右邊的所有列都無法使用索引優(yōu)化查找。例如有查詢條件為 where last_name='Allen' and first_name like ‘C%' and birthday='1992-10-25'，這個查詢只能使用索引的前兩列，因為這里的 like 是一個范圍條件。假如，范圍查詢的列的值的數(shù)量有限，那么可以通過使用多個等于條件代替范圍條件進(jìn)行優(yōu)化，來使右邊的列也可以用到索引。

現(xiàn)在，我們知道了復(fù)合索引中列的順序是多么的重要，這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求，比如在一張表中，可能需要兩個復(fù)合索引 key(last_name, first_name, birthday) 和 key(first_name, last_name, birthday) 。

B-Tree索引是最常用的索引類型，后面，如果沒有特別說明，都是指的B-Tree索引。

1、哈希索引

哈希索引（hash index）基于哈希表實現(xiàn)，只有精確匹配索引所有列的查詢才有效。在MySQL中，只有Memory引擎顯示支持哈希索引。

2、空間數(shù)據(jù)索引（R-Tree）

MyISAM引擎支持空間索引，可以用作地理數(shù)據(jù)存儲。和B-Tree索引不同，該索引無須前綴查詢。

3、全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。全文索引和其他幾種索引的匹配方式完全不一樣，它更類似于搜索引擎做的事情，而不是簡單的where條件匹配?？梢栽谙嗤牧猩?，同時創(chuàng)建全文索引和B-Tree索引，全文索引適用于 Match Against 操作，而不是普通的where條件操作。

索引可以包含一個列（即字段）或多個列的值。如果索引包含多個列，一般會將其稱作復(fù)合索引，此時，列的順序就十分重要，因為MySQL只能高效的使用索引的最左前綴列。創(chuàng)建一個包含兩個列的索引，和創(chuàng)建兩個只包含一列的索引是大不相同的。

1.5 索引的優(yōu)點

索引可以讓MySQL快速地查找到我們所需要的數(shù)據(jù)，但這并不是索引的唯一作用。

最常見的B-Tree索引，按照順序存儲數(shù)據(jù)，所以，MySQL可以用來做Order By和Group By操作。因為數(shù)據(jù)是有序存儲的，B-Tree也就會把相關(guān)的列值都存儲在一起。最后，因為索引中也存儲了實際的列值，所以某些查詢只使用索引就能夠獲取到全部的數(shù)據(jù)，無需再回表查詢。據(jù)此特性，總結(jié)出索引有如下三個優(yōu)點：

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

此外，有人用“三星系統(tǒng)”（three-star system）來評價一個索引是否適合某個查詢語句。三星系統(tǒng)主要是指：如果索引能夠?qū)⑾嚓P(guān)的記錄放到一起就獲得一星；如果索引中的數(shù)據(jù)順序和查找中的排列順序一致就獲得二星；如果索引中的列包含了查詢需要的全部列就獲得三星。

索引并不總是最好的工具，也不是說索引越多越好?？偟膩碚f，只要當(dāng)索引幫助存儲引擎快速找到記錄帶來的好處大于其帶來的額外工作時，索引才是有用的。

對于非常小的表，大部分情況下簡單的全表掃描更高效，沒有必要再建立索引。對于中到大型的表，索引帶來的好處就非常明顯了。

二. 高性能的索引策略

正確地創(chuàng)建和使用索引是實現(xiàn)高性能查詢的基礎(chǔ)。前面，已經(jīng)介紹了各種類型的索引及其優(yōu)缺點，現(xiàn)在來看看如何真正地發(fā)揮這些索引的優(yōu)勢。下面的幾個小節(jié)將幫助大家理解如何高效地使用索引。

2.1 獨立的列

我們通常會看到一些查詢不當(dāng)?shù)厥褂盟饕?，或者使得MySQL無法使用已有的索引。如果SQL查詢語句中的列不是獨立的，則MySQL就不會使用到索引?！蔼毩⒌牧小笔侵杆饕胁荒苁潜磉_(dá)式的一部分，也不能是函數(shù)的參數(shù)。

例如：下面這條SQL查詢語句，就無法使用主鍵索引id：

select id,last_name,first_name,birthday from people where id+1=3;

很容易看出，上面的where表達(dá)式其實可以簡寫為 where id=2，但是MySQL無法自動解析這個表達(dá)式。我們應(yīng)該養(yǎng)成簡化where條件的習(xí)慣，始終將索引列單獨放在比較運算符的一側(cè)。故要想使用到主鍵索引，正確地寫法為：

select id,last_name,first_name,birthday from people where id=2;

下面是另一個常見的錯誤寫法：

select ... from ... where to_days(current_date()) - to_days(date_col) <= 10;

2.2 前綴索引和索引的選擇性

有時候，我們需要索引很長的字符列，這會讓索引變得大且慢。通常的解決方法是，只索引列的前面幾個字符，這樣可以大大節(jié)約索引空間，從而提高索引的效率。但是，也會降低索引的選擇性。索引的選擇性是指，不重復(fù)的索引值的數(shù)目（也稱為基數(shù)）與數(shù)據(jù)表中的記錄總數(shù)的比值，取值范圍是0到1。

唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

一般情況下，某個列前綴的選擇性也是足夠高的，足以滿足查詢性能。對于Blob、Text或很長的Varchar類型的列，必須使用前綴索引，即只對列的前面幾個字符進(jìn)行索引，因為MySQL不允許索引這些列的完整長度。

添加前綴索引的方法如下：

alter table user add key(address(8)); // 只索引address字段的前8個字符

前綴索引是一種能使索引更小、更快的有效辦法，但缺點是：MySQL無法使用前綴索引做 Order By 和 Group By 操作，也無法使用前綴索引做覆蓋掃描。

有時，后綴索引（suffix index）也有用途，例如查找某個域名的所有電子郵件地址。但MySQL原生并不支持后綴索引，我們可以把字符串反轉(zhuǎn)后存儲，并基于此建立前綴索引，然后通過觸發(fā)器來維護(hù)這種索引。

2.3 多列索引

多列索引是指一個索引中包含多個列，必須要注意多個列的順序。多列索引也叫復(fù)合索引，如前面的 key(last_name, first_name, birthday) 就是一個復(fù)合索引。

一個常見的錯誤就是，為每個列創(chuàng)建單獨的索引，或者，按照錯誤的順序創(chuàng)建了多列索引。

先來看第一個問題，為每個列創(chuàng)建獨立的索引，從 show create table 中，很容易看到這種情況：

create table t (
 c1 int,
 c2 int,
 c3 int,
 key(c1),
 key(c2),
 key(c3)
);

這種錯誤的索引策略，一般是由于人們聽到一些專家諸如“把where條件里面的列都加上索引”這樣模糊的建議導(dǎo)致的。

在多個列上創(chuàng)建獨立的單列索引大部分情況下并不能提高M(jìn)ySQL的查詢性能。在MySQL 5.0及以后的版本中，引入了一種叫索引合并（index merge）的策略，它在一定程度上可以使用表上的多個單列索引來定位指定的行。但效率還是比復(fù)合索引差很多。

例如：表 film_actor 在字段 film_id 和 actor_id 上各有一個單列索引，SQL查詢語句如下：

select film_id,actor_id from film_actor where actor_id=1 or film_id=1;

在MySQL5.0以后的版本中，查詢能夠同時使用這兩個單列索引進(jìn)行掃描，并將結(jié)果進(jìn)行合并。這種算法有三個變種：or條件的聯(lián)合（union）、and條件的相交（intersection）、組合前兩種情況的聯(lián)合及相交。

上面的查詢就是使用了兩個索引掃描的聯(lián)合，通過explain中的Extra列（Extra的值中會出現(xiàn)union字符），可以看出這一點：

explain select film_id,actor_id from film_actor where actor_id=1 or film_id=1\G

索引合并策略有時候是一種優(yōu)化的結(jié)果，但實際上更多時候它說明了表上的索引建得很糟：

• 當(dāng)出現(xiàn)對多個索引做相交操作時（通常有多個and條件），通常意味著需要一個包含所有相關(guān)列的復(fù)合索引，而不是多個獨立的單列索引。

• 當(dāng)出現(xiàn)對多個索引做聯(lián)合操作時（通常有多個or條件），通常需要消耗大量的CPU和內(nèi)存資源在算法的緩存、排序和合并操作上。此時，可以將查詢改寫成兩個查詢Union的方式：

select film_id,actor_id from film_actor where actor_id=1
union all
select film_id,actor_id from film_actor where film_id=1 and actor_id<>1;

如果在explain的結(jié)果中，發(fā)現(xiàn)了索引的聯(lián)合，應(yīng)該好好檢查一下SQL查詢語句和表的結(jié)構(gòu)，看是不是已經(jīng)是最優(yōu)的了，能否將其拆分為多個查詢Union的方式等等。

2.4 選擇合適的索引列順序

最容易引起困惑的就是復(fù)合索引中列的順序。在復(fù)合索引中，正確地列順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要。

索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列，第三列…。所以，索引可以按照升序或者降序進(jìn)行掃描，以滿足精確符合列順序的order by、group by和distinct等子句的查詢需求。

當(dāng)不需要考慮排序和分組時，將選擇性最高的列放到復(fù)合索引的最左側(cè)（最前列）通常是很好的。這時，索引的作用只是用于優(yōu)化where條件的查找。但是，可能我們也需要根據(jù)那些運行頻率最高的查詢來調(diào)整索引列的順序，讓這種情況下索引的選擇性最高。

以下面的查詢?yōu)槔?/p>

select * from payment where staff_id=2 and customer_id=500;

是應(yīng)該創(chuàng)建一個 key(staff_id, customer_id) 的索引還是 key(customer_id, staff_id) 的索引？可以跑一些查詢來確定表中值的分布情況，并確定哪個列的選擇性更高。比如：可以用下面的查詢來預(yù)測一下：

select sum(staff_id=2), sum(customer_id=500) from payment\G

假如，結(jié)果顯示：sum(staff_id=2)的值為7000，而sum(customer_id=500)的值為60。由此可知，在上面的查詢中，customer_id的選擇性更高，應(yīng)該將其放在索引的最前面，也就是使用key(customer_id, staff_id) 。

但是，這樣做有一個地方需要注意，查詢的結(jié)果非常依賴于選定的具體值。如果按照上述方法優(yōu)化，可能對其他不同條件值的查詢不公平，也可能導(dǎo)致服務(wù)器的整體性能變得更糟。

如果是從pt-query-digest這樣的工具的報告中提取“最差查詢”，再按上述辦法選定的索引順序往往是非常高效的。假如，沒有類似地具體查詢來運行，那么最好還是根據(jù)經(jīng)驗法則來做，因為經(jīng)驗法則考慮的是全局基數(shù)和選擇性，而不是某個具體條件值的查詢。通過經(jīng)驗法則，判斷選擇性的方法如下：

select count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
from payment\G

假如，結(jié)果顯示：staff_id_selectivity的值為0.001，而customer_id_selectivity的值為0.086。我們知道，值越大，選擇性越高。故customer_id的選擇性更高。因此，還是將其作為索引列的第一列：

alter table payment add key(customer_id, staff_id);

盡管，關(guān)于選擇性和全局基數(shù)的經(jīng)驗法則值得去研究和分析，但一定別忘了order by、group by 等因素的影響，這些因素可能對查詢的性能造成非常大的影響。

2.5 聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。具體的細(xì)節(jié)依賴于其實現(xiàn)方式，但I(xiàn)nnoDB 的聚簇索引實際上在同一結(jié)構(gòu)中保存了 B-Tree 索引和數(shù)據(jù)行。

當(dāng)表中有聚簇索引時，它的數(shù)據(jù)行實際上存放在索引的葉子頁（leaf page）中，也就是說，葉子頁包含了行的全部數(shù)據(jù)，而節(jié)點頁只包含了索引列的數(shù)據(jù)。

因為是存儲引擎負(fù)責(zé)實現(xiàn)索引，因此并不是所有的存儲引擎都支持聚簇索引。本節(jié)我們主要關(guān)注InnoDB，這里討論的內(nèi)容對于任何支持聚簇索引的存儲引擎都是適用的。

InnoDB 通過主鍵聚集數(shù)據(jù)，如果沒有定義主鍵，InnoDB 會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB 會隱式定義一個主鍵來作為聚簇索引。

聚簇索引的優(yōu)點：

• 可以把相關(guān)的數(shù)據(jù)保存在一起。
• 數(shù)據(jù)訪問更快。聚簇索引將索引和數(shù)據(jù)保存在同一個B-Tree中，因此，從聚簇索引中獲取數(shù)據(jù)通常比非聚簇索引要快。
• 使用覆蓋索引掃描的查詢可以直接使用節(jié)點頁中的主鍵值。

如果在設(shè)計表和查詢時，能充分利用上面的優(yōu)點，就可以極大地提升性能。

聚簇索引的缺點：

• 聚簇索引最大限度地提高了I/O密集型應(yīng)用的性能，但如果數(shù)據(jù)全部放在內(nèi)存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優(yōu)勢了。
• 插入速度嚴(yán)重依賴于插入順序。按照主鍵的順序插入是插入數(shù)據(jù)到InnoDB表中速度最快的方式。但如果不是按照主鍵順序插入數(shù)據(jù)，那么，在操作完畢后，最好使用 OPTIMIZE TABLE 命令重新組織一下表。
• 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置。
• 基于聚簇索引的表在插入新行，或者主鍵被更新，導(dǎo)致需要移動行的時候，可能面臨“頁分裂（page split）”的問題。頁分裂會導(dǎo)致表占用更多的磁盤空間。

在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那樣需要獨立的行存儲。聚簇索引的每一個葉子節(jié)點都包含了主鍵值、事務(wù)ID、用于事務(wù)和MVCC（多版本控制）的回滾指針以及所有的剩余列。

InnoDB的二級索引（非聚簇索引）和聚簇索引差別很大，二級索引的葉子節(jié)點中存儲的不是“行指針”，而是主鍵值。故通過二級索引查找數(shù)據(jù)時，會進(jìn)行兩次索引查找。存儲引擎需要先查找二級索引的葉子節(jié)點來獲得對應(yīng)的主鍵值，然后根據(jù)這個主鍵值到聚簇索引中查找對應(yīng)的數(shù)據(jù)行。

為了保證數(shù)據(jù)行按順序插入，最簡單的方法是將主鍵定義為 auto_increment 自動增長。使用InnoDB時，應(yīng)該盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地使用單調(diào)增加的主鍵值來插入新行。

對于高并發(fā)工作負(fù)載，在InnoDB中按主鍵順序插入可能會造成明顯的主鍵值爭用的問題。這個問題非常嚴(yán)重，可自行百度解決。

2.6 覆蓋索引

通常大家都會根據(jù)查詢的where條件來創(chuàng)建合適的索引，但這只是索引優(yōu)化的一個方面。設(shè)計優(yōu)秀的索引，應(yīng)該考慮整個查詢，而不單單是where條件部分。

索引確實是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來直接獲取列的數(shù)據(jù)，這樣就不必再去讀取數(shù)據(jù)行。如果索引的葉子節(jié)點中已經(jīng)包含了要查詢的全部數(shù)據(jù)，那么，還有什么必要再回表查詢呢？

如果一個索引包含（或者覆蓋）了所有需要查詢的字段（列）的值，我們稱之為“覆蓋索引”。

覆蓋索引是非常有用的，能夠極大地提高性能?？紤]一下，如果查詢只需要掃描索引，而無須回表獲取數(shù)據(jù)行，會帶來多少好處：

• 索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，所以如果只需要讀取索引，那MySQL就會極大地減少數(shù)據(jù)訪問量。覆蓋索引對I/O密集型的應(yīng)用也有幫助，因為索引比數(shù)據(jù)更小，更容易全部放入內(nèi)存中。
• 因為索引是按照列值順序存儲的（至少在單個頁內(nèi)是這樣），所以對于I/O密集型的范圍查詢比隨機從磁盤讀取每一行的數(shù)據(jù)I/O要少得多。
• 由于InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引（非聚簇索引）在葉子節(jié)點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

在所有這些場景中，在索引中就完成所有查詢的成本一般比再回表查詢小得多。

B-Tree索引可以成為覆蓋索引，但哈希索引、空間索引和全文索引等均不支持覆蓋索引。

當(dāng)發(fā)起一個被索引覆蓋的查詢（也叫做索引覆蓋查詢）時，在 explain 的 Extra 列，可以看到 “Using index” 的信息。如：

explain select id from people;
explain select last_name from people;
explain select id,first_name from people;
explain select last_name,first_name,birthday from people;
explain select last_name,first_name,birthday from people where last_name='Allen';

people表是我們在上面的小節(jié)中創(chuàng)建的，它包含一個主鍵（id）索引和一個多列的復(fù)合索引key(last_name, first_name, birthday)，這兩個索引覆蓋了四個字段的值。如果一個SQL查詢語句，要查詢的字段都在這四個字段之中，那么，這個查詢就可以被稱為索引覆蓋查詢。如果一個索引包含了某個SQL查詢語句中所有要查詢的字段的值，這個索引對于該查詢語句來說，就是一個覆蓋索引。例如，key(last_name, first_name, birthday) 對于 select last_name,first_name from people 就是覆蓋索引。

2.7 使用索引掃描來做排序

MySQL有兩種方式可以生成有序的結(jié)果集：通過排序操作（order by）和 按索引順序掃描的自動排序（即通過索引來排序）。其實，這兩種排序操作是不沖突的，也就是說 order by 可以使用索引來排序。

確切地說，MySQL的對結(jié)果集的排序方式有下面兩種：

1、索引排序

索引排序是指使用索引中的字段值對結(jié)果集進(jìn)行排序。如果explain出來的type參數(shù)的值為index，就說明MySQL一定使用了索引排序。如：

explain select id from people;
explain select id,last_name from people order by id desc;
explain select last_name from people;
explain select last_name from people order by last_name;
explain select last_name from people order by last_name desc;

注意：就算explain出來的type的值不是index，也有可能是索引排序。如：

explain select id from people where id >3;
explain select id,last_name from people where id >3 order by id desc;

2、文件排序

文件排序（filesort）是指將查詢出來的結(jié)果集通過額外的操作進(jìn)行排序，然后返回給客戶端。這種排序方式，沒有使用到索引排序，效率較低。雖然文件排序，MySQL將其稱為filesort，但并不一定使用磁盤文件。

如果explain出來的Extra參數(shù)的值包含“Using filesort”字符串，就說明是文件排序。此時，你就必須對索引或SQL查詢語句進(jìn)行優(yōu)化了。如：

explain select id,last_name,first_name from people where id > 3 order by last_name;

MySQL可以使用同一個索引既滿足查找，又滿足查詢。如果可能，設(shè)計索引時，應(yīng)該盡可能地同時滿足這兩種操作。

只有當(dāng)索引的列包含where條件中的字段和order by中的字段，且索引中列的順序和where + order by 中包含的所有字段的順序一致（注意：order by在where的后面）時，才有可能使用到索引排序。

現(xiàn)在，我們來優(yōu)化上面的那條SQL語句，使其利用索引排序。

首先，添加一個多列索引。

alter table people add key(id,last_name);

會發(fā)現(xiàn)，僅添加 key(id,last_name)，還是沒辦法使用索引排序，這是因為，where + order by 語句也要滿足索引的最左前綴要求，而where id > 3是一個范圍條件，會導(dǎo)致后面的order by last_name無法使用索引key(id,last_name)。

其次，將SQL語句中的 order by last_name 改為 order by id,last_name。

注意：如果SQL查詢語句是一個關(guān)聯(lián)多張表的關(guān)聯(lián)查詢，則只有當(dāng)order by排序的字段全部來自于第一張表時，才能使用索引排序。

下面列出幾種不能使用索引排序的情況：

1、如果order by根據(jù)多個字段排序，但多個字段的排序方向不一致，即有的字段是asc（升序，默認(rèn)是升序），有的字段是desc（降序）。如：

explain select * from people where last_name='Allen' order by first_name asc, birthday desc;

2、如果order by包含了一個不在索引列的字段。如：

explain select * from people where last_name='Allen' order by first_name, gender;

3、如果索引列的第一列是一個范圍查找條件。如：

explain select * from people where last_name like 'A%' order by first_name;

4、對于這種情況，可以將SQL語句優(yōu)化為：

explain select * from people where last_name like 'A%' order by last_name,first_name;

2.8 冗余和重復(fù)索引

MySQL允許在相同的列上創(chuàng)建多個索引（只不過索引的名稱不同），由于MySQL需要單獨維護(hù)重復(fù)的索引，并且優(yōu)化器在優(yōu)化查詢時也需要逐個地進(jìn)行分析考慮，故重復(fù)的索引會影響性能。

重復(fù)索引是指在相同的列上按照相同的列順序創(chuàng)建的類型相同的索引。應(yīng)該避免創(chuàng)建重復(fù)索引，發(fā)現(xiàn)以后也應(yīng)立即刪除。

冗余索引和重復(fù)索引不同。如果創(chuàng)建了索引 key(A, B)，再來創(chuàng)建索引 key(A)，就是冗余索引。因為索引(A)只是前一個索引的前綴索引。索引(A, B)也可以當(dāng)做索引(A)來使用。但是，如果再創(chuàng)建索引(B,A)，就不是冗余索引了。

冗余索引通常發(fā)生在為表添加新索引的時候。例如，有人可能會增加一個新的索引(A, B)，而不是擴(kuò)展已有的索引(A)。還有一種情況是，將一個二級索引(A)擴(kuò)展為(A, ID)，其中ID是主鍵，對于InnoDB來說，二級索引中已經(jīng)默認(rèn)包含了主鍵列，所以這也是冗余的。

大多數(shù)情況下，都不需要冗余索引。應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引。但有時，出于性能方面的考慮，也需要冗余索引，因為擴(kuò)展已有的索引會導(dǎo)致其變大，從而會影響其他使用該索引的查詢語句的性能。

在擴(kuò)展索引的時候，需要特別小心。因為二級索引的葉子節(jié)點包含了主鍵值，所以在列(A)上的索引就相當(dāng)于在(A, ID)上的索引。如果有人用了像 where A=5 order by ID 這樣的查詢，索引(A)就非常有用。但是，如果你將索引(A)修改為索引(A, B)，則實際上就變成了索引(A, B, ID)，那么，上面查詢的order by語句就無法使用索引排序，而只能使用文件排序了。

推薦使用Percona工具箱中的pt-upgrade工具來仔細(xì)檢查計劃中的索引變更。

因此，只有當(dāng)你對一個索引相關(guān)的所有查詢都很清楚時，才去擴(kuò)展原有的索引。否則，創(chuàng)建一個新的索引（讓原有索引成為新索引的冗余索引）才是最保險的方法。

2.9 未使用的索引

MySQL服務(wù)器中可能會有一些永遠(yuǎn)都不會用到的索引，這樣的索引完全是累贅，建議考慮刪除。但要注意的是，唯一索引的唯一性約束功能，可能某個唯一索引一直沒有被查詢使用，卻能用于避免產(chǎn)生重復(fù)的數(shù)據(jù)。