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;

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

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

復合索引對如下類型的查詢有效：

全值匹配

全值匹配指的是和索引中的所有列進行匹配。例如：查找姓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開頭的人。即全匹配復合索引的第一列，范圍匹配第二列。SQL語句為：

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

只訪問索引的查詢

B-Tree 通常可以支持“只訪問索引的查詢”，即查詢只需要訪問索引，而無需訪問數據行。這和“覆蓋索引”的優化相關，后面再講。

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

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

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

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

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

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

1、哈希索引

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

2、空間數據索引（R-Tree）

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

3、全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關鍵詞，而不是直接比較索引中的值。全文索引和其他幾種索引的匹配方式完全不一樣，它更類似于搜索引擎做的事情，而不是簡單的where條件匹配。可以在相同的列上，同時創建全文索引和B-Tree索引，全文索引適用于 Match Against 操作，而不是普通的where條件操作。

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

1.5 索引的優點

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

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

• 索引大大減少了MySQL服務器需要掃描的數據量。
• 索引可以幫助服務器避免排序和臨時表。
• 索引可以將隨機I/O變為順序I/O。

此外，有人用“三星系統”（three-star system）來評價一個索引是否適合某個查詢語句。三星系統主要是指：如果索引能夠將相關的記錄放到一起就獲得一星；如果索引中的數據順序和查找中的排列順序一致就獲得二星；如果索引中的列包含了查詢需要的全部列就獲得三星。

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

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

二. 高性能的索引策略

正確地創建和使用索引是實現高性能查詢的基礎。前面，已經介紹了各種類型的索引及其優缺點，現在來看看如何真正地發揮這些索引的優勢。下面的幾個小節將幫助大家理解如何高效地使用索引。

2.1 獨立的列

我們通常會看到一些查詢不當地使用索引，或者使得MySQL無法使用已有的索引。如果SQL查詢語句中的列不是獨立的，則MySQL就不會使用到索引。“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數。

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

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

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

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 前綴索引和索引的選擇性

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

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

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

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

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

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

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

2.3 多列索引

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

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

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

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

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

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

例如：表 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以后的版本中，查詢能夠同時使用這兩個單列索引進行掃描，并將結果進行合并。這種算法有三個變種：or條件的聯合（union）、and條件的相交（intersection）、組合前兩種情況的聯合及相交。

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

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

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

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

• 當出現對多個索引做聯合操作時（通常有多個or條件），通常需要消耗大量的CPU和內存資源在算法的緩存、排序和合并操作上。此時，可以將查詢改寫成兩個查詢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的結果中，發現了索引的聯合，應該好好檢查一下SQL查詢語句和表的結構，看是不是已經是最優的了，能否將其拆分為多個查詢Union的方式等等。

2.4 選擇合適的索引列順序

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

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

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

以下面的查詢為例：

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

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

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

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

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

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

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

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

alter table payment add key(customer_id, staff_id);

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

2.5 聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式。具體的細節依賴于其實現方式，但InnoDB 的聚簇索引實際上在同一結構中保存了 B-Tree 索引和數據行。

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

因為是存儲引擎負責實現索引，因此并不是所有的存儲引擎都支持聚簇索引。本節我們主要關注InnoDB，這里討論的內容對于任何支持聚簇索引的存儲引擎都是適用的。

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

聚簇索引的優點：

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

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

聚簇索引的缺點：

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

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

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

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

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

2.6 覆蓋索引

通常大家都會根據查詢的where條件來創建合適的索引，但這只是索引優化的一個方面。設計優秀的索引，應該考慮整個查詢，而不單單是where條件部分。

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

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

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

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

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

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

當發起一個被索引覆蓋的查詢（也叫做索引覆蓋查詢）時，在 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表是我們在上面的小節中創建的，它包含一個主鍵（id）索引和一個多列的復合索引key(last_name, first_name, birthday)，這兩個索引覆蓋了四個字段的值。如果一個SQL查詢語句，要查詢的字段都在這四個字段之中，那么，這個查詢就可以被稱為索引覆蓋查詢。如果一個索引包含了某個SQL查詢語句中所有要查詢的字段的值，這個索引對于該查詢語句來說，就是一個覆蓋索引。例如，key(last_name, first_name, birthday) 對于 select last_name,first_name from people 就是覆蓋索引。

2.7 使用索引掃描來做排序

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

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

1、索引排序

索引排序是指使用索引中的字段值對結果集進行排序。如果explain出來的type參數的值為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）是指將查詢出來的結果集通過額外的操作進行排序，然后返回給客戶端。這種排序方式，沒有使用到索引排序，效率較低。雖然文件排序，MySQL將其稱為filesort，但并不一定使用磁盤文件。

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

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

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

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

現在，我們來優化上面的那條SQL語句，使其利用索引排序。

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

alter table people add key(id,last_name);

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

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

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

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

1、如果order by根據多個字段排序，但多個字段的排序方向不一致，即有的字段是asc（升序，默認是升序），有的字段是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語句優化為：

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

2.8 冗余和重復索引

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

重復索引是指在相同的列上按照相同的列順序創建的類型相同的索引。應該避免創建重復索引，發現以后也應立即刪除。

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

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

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

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

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

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

2.9 未使用的索引

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