在MySQL中，索引（index）也叫做“鍵（key）”，它是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。
讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：國(guó)際域名空間、雅安服務(wù)器托管、營(yíng)銷軟件、網(wǎng)站建設(shè)、陳倉(cāng)網(wǎng)站維護(hù)、網(wǎng)站推廣。
索引對(duì)于良好的性能非常關(guān)鍵，尤其是當(dāng)表中的數(shù)據(jù)量越來(lái)越大時(shí)，索引對(duì)性能的影響就愈發(fā)重要。
索引優(yōu)化應(yīng)該是對(duì)查詢性能優(yōu)化最有效的手段，創(chuàng)建一個(gè)真正最優(yōu)的索引經(jīng)常需要重寫(xiě)SQL查詢語(yǔ)句。
要理解MySQL中索引的工作原理，最簡(jiǎn)單的方法就是去看一看一本書(shū)的索引部分：比如你想在一本書(shū)中尋找某個(gè)主題，一般會(huì)先看書(shū)的索引目錄，找到對(duì)應(yīng)的章節(jié)、對(duì)應(yīng)的頁(yè)碼后就可以快速找到你想看的內(nèi)容。
在MySQL中，存儲(chǔ)引擎用類似的方法使用索引，其先在索引中查找對(duì)應(yīng)的值，然后根據(jù)匹配的索引記錄找到對(duì)應(yīng)的數(shù)據(jù)行，最后將數(shù)據(jù)結(jié)果集返回給客戶端。
在MySQL中，通常我們所指的索引類型，有以下幾種：
常規(guī)索引
常規(guī)索引，也叫普通索引（index或key），它可以常規(guī)地提高查詢效率。一張數(shù)據(jù)表中可以有多個(gè)常規(guī)索引。常規(guī)索引是使用最普遍的索引類型，如果沒(méi)有明確指明索引的類型，我們所說(shuō)的索引都是指常規(guī)索引。
主鍵索引
主鍵索引（Primary Key），也簡(jiǎn)稱主鍵。它可以提高查詢效率，并提供唯一性約束。一張表中只能有一個(gè)主鍵。被標(biāo)志為自動(dòng)增長(zhǎng)的字段一定是主鍵，但主鍵不一定是自動(dòng)增長(zhǎng)。一般把主鍵定義在無(wú)意義的字段上（如：編號(hào)），主鍵的數(shù)據(jù)類型最好是數(shù)值。
唯一索引
唯一索引（Unique Key），可以提高查詢效率，并提供唯一性約束。一張表中可以有多個(gè)唯一索引。
全文索引
全文索引（Full Text），可以提高全文搜索的查詢效率，一般使用Sphinx替代。但Sphinx不支持中文檢索，Coreseek是支持中文的全文檢索引擎，也稱作具有中文分詞功能的Sphinx。實(shí)際項(xiàng)目中，我們用到的是Coreseek。
外鍵索引
外鍵索引（Foreign Key），簡(jiǎn)稱外鍵，它可以提高查詢效率，外鍵會(huì)自動(dòng)和對(duì)應(yīng)的其他表的主鍵關(guān)聯(lián)。外鍵的主要作用是保證記錄的一致性和完整性。
注意：只有InnoDB存儲(chǔ)引擎的表才支持外鍵。外鍵字段如果沒(méi)有指定索引名稱，會(huì)自動(dòng)生成。如果要?jiǎng)h除父表（如分類表）中的記錄，必須先刪除子表（帶外鍵的表，如文章表）中的相應(yīng)記錄，否則會(huì)出錯(cuò)。 創(chuàng)建表的時(shí)候，可以給字段設(shè)置外鍵，如 foreign key(cate_id) references cms_cate(id)，由于外鍵的效率并不是很好，因此并不推薦使用外鍵，但我們要使用外鍵的思想來(lái)保證數(shù)據(jù)的一致性和完整性。
在MySQL中，索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的，而不是在服務(wù)器層。MySQL支持的索引方法，也可以說(shuō)成是索引的類型（這是廣義層面上的），主要有以下幾種：
B-Tree 索引
如果沒(méi)有特別指明類型，那多半說(shuō)的就是B-Tree 索引。不同的存儲(chǔ)引擎以不同的方式使用B-Tree索引，性能也各不相同。例如：MyISAM使用前綴壓縮技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原始的數(shù)據(jù)格式存儲(chǔ)索引。再如MyISAM通過(guò)數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)主鍵引用被索引的行。
B-Tree 對(duì)索引列是順序存儲(chǔ)的，因此很適合查找范圍數(shù)據(jù)。它能夠加快訪問(wèn)數(shù)據(jù)的速度，因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描來(lái)獲取需要的數(shù)據(jù)。
如果一個(gè)索引中包括多個(gè)字段（列）的值，那它就是一個(gè)復(fù)合索引。復(fù)合索引對(duì)多個(gè)字段值進(jìn)行排序的依據(jù)是創(chuàng)建索引時(shí)列的順序。如下：
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ù)：
| id | last_name | first_name | birthday | gender |
|---|---|---|---|---|
| 1 | Clinton | Bill | 1970-01-01 | 3 |
| 2 | Allen | Cuba | 1960-01-01 | 3 |
| 3 | Bush | George | 1970-01-01 | 3 |
| 4 | Smith | Kim | 1970-01-01 | 3 |
| 5 | Allen | Cally | 1989-06-08 | 3 |
| … | … | … | … | … |
我們創(chuàng)建了一個(gè)復(fù)合索引 key(last_name, first_name, birthday)，對(duì)于表中的每一行數(shù)據(jù)，該索引中都包含了姓、名和出生日期這三列的值。索引也是根據(jù)這個(gè)順序來(lái)排序存儲(chǔ)的，如果某兩個(gè)人的姓和名都一樣，就會(huì)根據(jù)他們的出生日期來(lái)對(duì)索引排序存儲(chǔ)。
B-Tree 索引適用于全鍵值、鍵值范圍或鍵前綴查找，其中鍵前綴查找只適用于根據(jù)最左前綴查找。
復(fù)合索引對(duì)如下類型的查詢有效：
全值匹配
全值匹配指的是和索引中的所有列進(jìn)行匹配。例如：查找姓Allen、名Cuba、出生日期為1960-01-01的人。
SQL語(yǔ)句為：
select id,last_name,first_name,birthday from people where last_name='Allen' and first_name='Cuba' and birthday='1960-01-01';
。
匹配最左前綴
比如只使用索引的第一列，查找所有姓為Allen的人。SQL語(yǔ)句為：
select id,last_name,first_name,birthday from people where last_name='Allen';
匹配列前綴
比如只匹配索引的第一列的值的開(kāi)頭部分，查找所有姓氏以A開(kāi)頭的人。SQL語(yǔ)句為：
select id,last_name,first_name,birthday from people where last_name like ‘A%';
匹配范圍值
比如范圍匹配姓氏在Allen和Clinton之間的人。SQL語(yǔ)句為：
select id,last_name,first_name,birthday from people where last_name BETWEEN ‘Allen' And ‘Clinton';
這里也只使用了索引的第一列。
精確匹配第一列并范圍匹配后面的列
比如查找姓Allen，并且名字以字母C開(kāi)頭的人。即全匹配復(fù)合索引的第一列，范圍匹配第二列。SQL語(yǔ)句為：
select id,last_name,first_name,birthday from people where last_name = ‘Allen' and first_name like'C%';
只訪問(wèn)索引的查詢
B-Tree 通?？梢灾С帧爸辉L問(wèn)索引的查詢”，即查詢只需要訪問(wèn)索引，而無(wú)需訪問(wèn)數(shù)據(jù)行。這和“覆蓋索引”的優(yōu)化相關(guān)，后面再講。
下面介紹一些復(fù)合索引會(huì)失效的情況：
（1）如果不是按照復(fù)合索引的最左列開(kāi)始查找，則無(wú)法使用索引。例如：上面的例子中，索引無(wú)法用于查找查找名為Cuba的人，也無(wú)法查找某個(gè)特定出生日期的人，因?yàn)檫@兩列都不是復(fù)合索引 key(last_name, first_name, birthday) 的最左數(shù)據(jù)列。類似地，也無(wú)法查找姓氏以某個(gè)字母結(jié)尾的人，即like范圍查詢的模糊匹配符%，如果放在第一位會(huì)使索引失效。
（2）如果查找時(shí)跳過(guò)了索引中的列，則只有前面的索引列會(huì)用到，后面的索引列會(huì)失效。比如查找姓Allen且出生日期在某個(gè)特定日期的人。這里查找時(shí)，由于沒(méi)有指定查找名（first_name），故MySQL只能使用該復(fù)合索引的第一列（即last_name）。
（3）如果查詢中有某個(gè)列的范圍查詢，則該列右邊的所有列都無(wú)法使用索引優(yōu)化查找。例如有查詢條件為 where last_name='Allen' and first_name like ‘C%' and birthday='1992-10-25'，這個(gè)查詢只能使用索引的前兩列，因?yàn)檫@里的 like 是一個(gè)范圍條件。假如，范圍查詢的列的值的數(shù)量有限，那么可以通過(guò)使用多個(gè)等于條件代替范圍條件進(jìn)行優(yōu)化，來(lái)使右邊的列也可以用到索引。
現(xiàn)在，我們知道了復(fù)合索引中列的順序是多么的重要，這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時(shí)候，可能需要使用相同的列但順序不同的索引來(lái)滿足不同類型的查詢需求，比如在一張表中，可能需要兩個(gè)復(fù)合索引 key(last_name, first_name, birthday) 和 key(first_name, last_name, birthday) 。
B-Tree索引是最常用的索引類型，后面，如果沒(méi)有特別說(shuō)明，都是指的B-Tree索引。
1、哈希索引
哈希索引（hash index）基于哈希表實(shí)現(xiàn)，只有精確匹配索引所有列的查詢才有效。在MySQL中，只有Memory引擎顯示支持哈希索引。
2、空間數(shù)據(jù)索引（R-Tree）
MyISAM引擎支持空間索引，可以用作地理數(shù)據(jù)存儲(chǔ)。和B-Tree索引不同，該索引無(wú)須前綴查詢。
3、全文索引
全文索引是一種特殊類型的索引，它查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。全文索引和其他幾種索引的匹配方式完全不一樣，它更類似于搜索引擎做的事情，而不是簡(jiǎn)單的where條件匹配?？梢栽谙嗤牧猩?，同時(shí)創(chuàng)建全文索引和B-Tree索引，全文索引適用于 Match Against 操作，而不是普通的where條件操作。
索引可以包含一個(gè)列（即字段）或多個(gè)列的值。如果索引包含多個(gè)列，一般會(huì)將其稱作復(fù)合索引，此時(shí)，列的順序就十分重要，因?yàn)镸ySQL只能高效的使用索引的最左前綴列。創(chuàng)建一個(gè)包含兩個(gè)列的索引，和創(chuàng)建兩個(gè)只包含一列的索引是大不相同的。
索引可以讓MySQL快速地查找到我們所需要的數(shù)據(jù)，但這并不是索引的唯一作用。
最常見(jiàn)的B-Tree索引，按照順序存儲(chǔ)數(shù)據(jù)，所以，MySQL可以用來(lái)做Order By和Group By操作。因?yàn)閿?shù)據(jù)是有序存儲(chǔ)的，B-Tree也就會(huì)把相關(guān)的列值都存儲(chǔ)在一起。最后，因?yàn)樗饕幸泊鎯?chǔ)了實(shí)際的列值，所以某些查詢只使用索引就能夠獲取到全部的數(shù)據(jù)，無(wú)需再回表查詢。據(jù)此特性，總結(jié)出索引有如下三個(gè)優(yōu)點(diǎn)：
此外，有人用“三星系統(tǒng)”（three-star system）來(lái)評(píng)價(jià)一個(gè)索引是否適合某個(gè)查詢語(yǔ)句。三星系統(tǒng)主要是指：如果索引能夠?qū)⑾嚓P(guān)的記錄放到一起就獲得一星；如果索引中的數(shù)據(jù)順序和查找中的排列順序一致就獲得二星；如果索引中的列包含了查詢需要的全部列就獲得三星。
索引并不總是最好的工具，也不是說(shuō)索引越多越好?？偟膩?lái)說(shuō)，只要當(dāng)索引幫助存儲(chǔ)引擎快速找到記錄帶來(lái)的好處大于其帶來(lái)的額外工作時(shí)，索引才是有用的。
對(duì)于非常小的表，大部分情況下簡(jiǎn)單的全表掃描更高效，沒(méi)有必要再建立索引。對(duì)于中到大型的表，索引帶來(lái)的好處就非常明顯了。
正確地創(chuàng)建和使用索引是實(shí)現(xiàn)高性能查詢的基礎(chǔ)。前面，已經(jīng)介紹了各種類型的索引及其優(yōu)缺點(diǎn)，現(xiàn)在來(lái)看看如何真正地發(fā)揮這些索引的優(yōu)勢(shì)。下面的幾個(gè)小節(jié)將幫助大家理解如何高效地使用索引。
我們通常會(huì)看到一些查詢不當(dāng)?shù)厥褂盟饕?，或者使得MySQL無(wú)法使用已有的索引。如果SQL查詢語(yǔ)句中的列不是獨(dú)立的，則MySQL就不會(huì)使用到索引。“獨(dú)立的列”是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。
例如：下面這條SQL查詢語(yǔ)句，就無(wú)法使用主鍵索引id：
select id,last_name,first_name,birthday from people where id+1=3;
很容易看出，上面的where表達(dá)式其實(shí)可以簡(jiǎn)寫(xiě)為 where id=2，但是MySQL無(wú)法自動(dòng)解析這個(gè)表達(dá)式。我們應(yīng)該養(yǎng)成簡(jiǎn)化where條件的習(xí)慣，始終將索引列單獨(dú)放在比較運(yùn)算符的一側(cè)。故要想使用到主鍵索引，正確地寫(xiě)法為：
select id,last_name,first_name,birthday from people where id=2;
下面是另一個(gè)常見(jiàn)的錯(cuò)誤寫(xiě)法：
select ... from ... where to_days(current_date()) - to_days(date_col) <= 10;
有時(shí)候，我們需要索引很長(zhǎng)的字符列，這會(huì)讓索引變得大且慢。通常的解決方法是，只索引列的前面幾個(gè)字符，這樣可以大大節(jié)約索引空間，從而提高索引的效率。但是，也會(huì)降低索引的選擇性。索引的選擇性是指，不重復(fù)的索引值的數(shù)目（也稱為基數(shù)）與數(shù)據(jù)表中的記錄總數(shù)的比值，取值范圍是0到1。
唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
一般情況下，某個(gè)列前綴的選擇性也是足夠高的，足以滿足查詢性能。對(duì)于Blob、Text或很長(zhǎng)的Varchar類型的列，必須使用前綴索引，即只對(duì)列的前面幾個(gè)字符進(jìn)行索引，因?yàn)镸ySQL不允許索引這些列的完整長(zhǎng)度。
添加前綴索引的方法如下：
alter table user add key(address(8)); // 只索引address字段的前8個(gè)字符
前綴索引是一種能使索引更小、更快的有效辦法，但缺點(diǎn)是：MySQL無(wú)法使用前綴索引做 Order By 和 Group By 操作，也無(wú)法使用前綴索引做覆蓋掃描。
有時(shí)，后綴索引（suffix index）也有用途，例如查找某個(gè)域名的所有電子郵件地址。但MySQL原生并不支持后綴索引，我們可以把字符串反轉(zhuǎn)后存儲(chǔ)，并基于此建立前綴索引，然后通過(guò)觸發(fā)器來(lái)維護(hù)這種索引。
多列索引是指一個(gè)索引中包含多個(gè)列，必須要注意多個(gè)列的順序。多列索引也叫復(fù)合索引，如前面的 key(last_name, first_name, birthday) 就是一個(gè)復(fù)合索引。
一個(gè)常見(jiàn)的錯(cuò)誤就是，為每個(gè)列創(chuàng)建單獨(dú)的索引，或者，按照錯(cuò)誤的順序創(chuàng)建了多列索引。
先來(lái)看第一個(gè)問(wèn)題，為每個(gè)列創(chuàng)建獨(dú)立的索引，從 show create table 中，很容易看到這種情況：
create table t ( c1 int, c2 int, c3 int, key(c1), key(c2), key(c3) );
這種錯(cuò)誤的索引策略，一般是由于人們聽(tīng)到一些專家諸如“把where條件里面的列都加上索引”這樣模糊的建議導(dǎo)致的。
在多個(gè)列上創(chuàng)建獨(dú)立的單列索引大部分情況下并不能提高M(jìn)ySQL的查詢性能。在MySQL 5.0及以后的版本中，引入了一種叫索引合并（index merge）的策略，它在一定程度上可以使用表上的多個(gè)單列索引來(lái)定位指定的行。但效率還是比復(fù)合索引差很多。
例如：表 film_actor 在字段 film_id 和 actor_id 上各有一個(gè)單列索引，SQL查詢語(yǔ)句如下：
select film_id,actor_id from film_actor where actor_id=1 or film_id=1;
在MySQL5.0以后的版本中，查詢能夠同時(shí)使用這兩個(gè)單列索引進(jìn)行掃描，并將結(jié)果進(jìn)行合并。這種算法有三個(gè)變種：or條件的聯(lián)合（union）、and條件的相交（intersection）、組合前兩種情況的聯(lián)合及相交。
上面的查詢就是使用了兩個(gè)索引掃描的聯(lián)合，通過(guò)explain中的Extra列（Extra的值中會(huì)出現(xiàn)union字符），可以看出這一點(diǎn)：
explain select film_id,actor_id from film_actor where actor_id=1 or film_id=1\G
索引合并策略有時(shí)候是一種優(yōu)化的結(jié)果，但實(shí)際上更多時(shí)候它說(shuō)明了表上的索引建得很糟：
當(dāng)出現(xiàn)對(duì)多個(gè)索引做聯(lián)合操作時(shí)（通常有多個(gè)or條件），通常需要消耗大量的CPU和內(nèi)存資源在算法的緩存、排序和合并操作上。此時(shí)，可以將查詢改寫(xiě)成兩個(gè)查詢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查詢語(yǔ)句和表的結(jié)構(gòu)，看是不是已經(jīng)是最優(yōu)的了，能否將其拆分為多個(gè)查詢Union的方式等等。
最容易引起困惑的就是復(fù)合索引中列的順序。在復(fù)合索引中，正確地列順序依賴于使用該索引的查詢，并且同時(shí)需要考慮如何更好地滿足排序和分組的需要。
索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列，第三列…。所以，索引可以按照升序或者降序進(jìn)行掃描，以滿足精確符合列順序的order by、group by和distinct等子句的查詢需求。
當(dāng)不需要考慮排序和分組時(shí)，將選擇性最高的列放到復(fù)合索引的最左側(cè)（最前列）通常是很好的。這時(shí)，索引的作用只是用于優(yōu)化where條件的查找。但是，可能我們也需要根據(jù)那些運(yùn)行頻率最高的查詢來(lái)調(diào)整索引列的順序，讓這種情況下索引的選擇性最高。
以下面的查詢?yōu)槔?/p>
select * from payment where staff_id=2 and customer_id=500;
是應(yīng)該創(chuàng)建一個(gè) key(staff_id, customer_id) 的索引還是 key(customer_id, staff_id) 的索引？可以跑一些查詢來(lái)確定表中值的分布情況，并確定哪個(gè)列的選擇性更高。比如：可以用下面的查詢來(lái)預(yù)測(cè)一下：
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) 。
但是，這樣做有一個(gè)地方需要注意，查詢的結(jié)果非常依賴于選定的具體值。如果按照上述方法優(yōu)化，可能對(duì)其他不同條件值的查詢不公平，也可能導(dǎo)致服務(wù)器的整體性能變得更糟。
如果是從pt-query-digest這樣的工具的報(bào)告中提取“最差查詢”，再按上述辦法選定的索引順序往往是非常高效的。假如，沒(méi)有類似地具體查詢來(lái)運(yùn)行，那么最好還是根據(jù)經(jīng)驗(yàn)法則來(lái)做，因?yàn)榻?jīng)驗(yàn)法則考慮的是全局基數(shù)和選擇性，而不是某個(gè)具體條件值的查詢。通過(guò)經(jīng)驗(yàn)法則，判斷選擇性的方法如下：
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)驗(yàn)法則值得去研究和分析，但一定別忘了order by、group by 等因素的影響，這些因素可能對(duì)查詢的性能造成非常大的影響。
聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。具體的細(xì)節(jié)依賴于其實(shí)現(xiàn)方式，但I(xiàn)nnoDB 的聚簇索引實(shí)際上在同一結(jié)構(gòu)中保存了 B-Tree 索引和數(shù)據(jù)行。
當(dāng)表中有聚簇索引時(shí)，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(yè)（leaf page）中，也就是說(shuō)，葉子頁(yè)包含了行的全部數(shù)據(jù)，而節(jié)點(diǎn)頁(yè)只包含了索引列的數(shù)據(jù)。
因?yàn)槭谴鎯?chǔ)引擎負(fù)責(zé)實(shí)現(xiàn)索引，因此并不是所有的存儲(chǔ)引擎都支持聚簇索引。本節(jié)我們主要關(guān)注InnoDB，這里討論的內(nèi)容對(duì)于任何支持聚簇索引的存儲(chǔ)引擎都是適用的。
InnoDB 通過(guò)主鍵聚集數(shù)據(jù)，如果沒(méi)有定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一的非空索引代替。如果沒(méi)有這樣的索引，InnoDB 會(huì)隱式定義一個(gè)主鍵來(lái)作為聚簇索引。
聚簇索引的優(yōu)點(diǎn)：
如果在設(shè)計(jì)表和查詢時(shí)，能充分利用上面的優(yōu)點(diǎn)，就可以極大地提升性能。
聚簇索引的缺點(diǎn)：
在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那樣需要獨(dú)立的行存儲(chǔ)。聚簇索引的每一個(gè)葉子節(jié)點(diǎn)都包含了主鍵值、事務(wù)ID、用于事務(wù)和MVCC（多版本控制）的回滾指針以及所有的剩余列。
InnoDB的二級(jí)索引（非聚簇索引）和聚簇索引差別很大，二級(jí)索引的葉子節(jié)點(diǎn)中存儲(chǔ)的不是“行指針”，而是主鍵值。故通過(guò)二級(jí)索引查找數(shù)據(jù)時(shí)，會(huì)進(jìn)行兩次索引查找。存儲(chǔ)引擎需要先查找二級(jí)索引的葉子節(jié)點(diǎn)來(lái)獲得對(duì)應(yīng)的主鍵值，然后根據(jù)這個(gè)主鍵值到聚簇索引中查找對(duì)應(yīng)的數(shù)據(jù)行。
為了保證數(shù)據(jù)行按順序插入，最簡(jiǎn)單的方法是將主鍵定義為 auto_increment 自動(dòng)增長(zhǎng)。使用InnoDB時(shí)，應(yīng)該盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地使用單調(diào)增加的主鍵值來(lái)插入新行。
對(duì)于高并發(fā)工作負(fù)載，在InnoDB中按主鍵順序插入可能會(huì)造成明顯的主鍵值爭(zhēng)用的問(wèn)題。這個(gè)問(wèn)題非常嚴(yán)重，可自行百度解決。
通常大家都會(huì)根據(jù)查詢的where條件來(lái)創(chuàng)建合適的索引，但這只是索引優(yōu)化的一個(gè)方面。設(shè)計(jì)優(yōu)秀的索引，應(yīng)該考慮整個(gè)查詢，而不單單是where條件部分。
索引確實(shí)是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來(lái)直接獲取列的數(shù)據(jù)，這樣就不必再去讀取數(shù)據(jù)行。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含了要查詢的全部數(shù)據(jù)，那么，還有什么必要再回表查詢呢？
如果一個(gè)索引包含（或者覆蓋）了所有需要查詢的字段（列）的值，我們稱之為“覆蓋索引”。
覆蓋索引是非常有用的，能夠極大地提高性能?？紤]一下，如果查詢只需要掃描索引，而無(wú)須回表獲取數(shù)據(jù)行，會(huì)帶來(lái)多少好處：
在所有這些場(chǎng)景中，在索引中就完成所有查詢的成本一般比再回表查詢小得多。
B-Tree索引可以成為覆蓋索引，但哈希索引、空間索引和全文索引等均不支持覆蓋索引。
當(dāng)發(fā)起一個(gè)被索引覆蓋的查詢（也叫做索引覆蓋查詢）時(shí)，在 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表是我們?cè)谏厦娴男」?jié)中創(chuàng)建的，它包含一個(gè)主鍵（id）索引和一個(gè)多列的復(fù)合索引key(last_name, first_name, birthday)，這兩個(gè)索引覆蓋了四個(gè)字段的值。如果一個(gè)SQL查詢語(yǔ)句，要查詢的字段都在這四個(gè)字段之中，那么，這個(gè)查詢就可以被稱為索引覆蓋查詢。如果一個(gè)索引包含了某個(gè)SQL查詢語(yǔ)句中所有要查詢的字段的值，這個(gè)索引對(duì)于該查詢語(yǔ)句來(lái)說(shuō)，就是一個(gè)覆蓋索引。例如，key(last_name, first_name, birthday) 對(duì)于 select last_name,first_name from people 就是覆蓋索引。
MySQL有兩種方式可以生成有序的結(jié)果集：通過(guò)排序操作（order by）和 按索引順序掃描的自動(dòng)排序（即通過(guò)索引來(lái)排序）。其實(shí)，這兩種排序操作是不沖突的，也就是說(shuō) order by 可以使用索引來(lái)排序。
確切地說(shuō)，MySQL的對(duì)結(jié)果集的排序方式有下面兩種：
1、索引排序
索引排序是指使用索引中的字段值對(duì)結(jié)果集進(jìn)行排序。如果explain出來(lái)的type參數(shù)的值為index，就說(shuō)明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出來(lái)的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）是指將查詢出來(lái)的結(jié)果集通過(guò)額外的操作進(jìn)行排序，然后返回給客戶端。這種排序方式，沒(méi)有使用到索引排序，效率較低。雖然文件排序，MySQL將其稱為filesort，但并不一定使用磁盤(pán)文件。
如果explain出來(lái)的Extra參數(shù)的值包含“Using filesort”字符串，就說(shuō)明是文件排序。此時(shí)，你就必須對(duì)索引或SQL查詢語(yǔ)句進(jìn)行優(yōu)化了。如：
explain select id,last_name,first_name from people where id > 3 order by last_name;
MySQL可以使用同一個(gè)索引既滿足查找，又滿足查詢。如果可能，設(shè)計(jì)索引時(shí)，應(yīng)該盡可能地同時(shí)滿足這兩種操作。
只有當(dāng)索引的列包含where條件中的字段和order by中的字段，且索引中列的順序和where + order by 中包含的所有字段的順序一致（注意：order by在where的后面）時(shí)，才有可能使用到索引排序。
現(xiàn)在，我們來(lái)優(yōu)化上面的那條SQL語(yǔ)句，使其利用索引排序。
首先，添加一個(gè)多列索引。
alter table people add key(id,last_name);
會(huì)發(fā)現(xiàn)，僅添加 key(id,last_name)，還是沒(méi)辦法使用索引排序，這是因?yàn)?，where + order by 語(yǔ)句也要滿足索引的最左前綴要求，而where id > 3是一個(gè)范圍條件，會(huì)導(dǎo)致后面的order by last_name無(wú)法使用索引key(id,last_name)。
其次，將SQL語(yǔ)句中的 order by last_name 改為 order by id,last_name。
注意：如果SQL查詢語(yǔ)句是一個(gè)關(guān)聯(lián)多張表的關(guān)聯(lián)查詢，則只有當(dāng)order by排序的字段全部來(lái)自于第一張表時(shí)，才能使用索引排序。
下面列出幾種不能使用索引排序的情況：
1、如果order by根據(jù)多個(gè)字段排序，但多個(gè)字段的排序方向不一致，即有的字段是asc（升序，默認(rèn)是升序），有的字段是desc（降序）。如：
explain select * from people where last_name='Allen' order by first_name asc, birthday desc;
2、如果order by包含了一個(gè)不在索引列的字段。如：
explain select * from people where last_name='Allen' order by first_name, gender;
3、如果索引列的第一列是一個(gè)范圍查找條件。如：
explain select * from people where last_name like 'A%' order by first_name;
4、對(duì)于這種情況，可以將SQL語(yǔ)句優(yōu)化為：
explain select * from people where last_name like 'A%' order by last_name,first_name;
MySQL允許在相同的列上創(chuàng)建多個(gè)索引（只不過(guò)索引的名稱不同），由于MySQL需要單獨(dú)維護(hù)重復(fù)的索引，并且優(yōu)化器在優(yōu)化查詢時(shí)也需要逐個(gè)地進(jìn)行分析考慮，故重復(fù)的索引會(huì)影響性能。
重復(fù)索引是指在相同的列上按照相同的列順序創(chuàng)建的類型相同的索引。應(yīng)該避免創(chuàng)建重復(fù)索引，發(fā)現(xiàn)以后也應(yīng)立即刪除。
冗余索引和重復(fù)索引不同。如果創(chuàng)建了索引 key(A, B)，再來(lái)創(chuàng)建索引 key(A)，就是冗余索引。因?yàn)樗饕?A)只是前一個(gè)索引的前綴索引。索引(A, B)也可以當(dāng)做索引(A)來(lái)使用。但是，如果再創(chuàng)建索引(B,A)，就不是冗余索引了。
冗余索引通常發(fā)生在為表添加新索引的時(shí)候。例如，有人可能會(huì)增加一個(gè)新的索引(A, B)，而不是擴(kuò)展已有的索引(A)。還有一種情況是，將一個(gè)二級(jí)索引(A)擴(kuò)展為(A, ID)，其中ID是主鍵，對(duì)于InnoDB來(lái)說(shuō)，二級(jí)索引中已經(jīng)默認(rèn)包含了主鍵列，所以這也是冗余的。
大多數(shù)情況下，都不需要冗余索引。應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引。但有時(shí)，出于性能方面的考慮，也需要冗余索引，因?yàn)閿U(kuò)展已有的索引會(huì)導(dǎo)致其變大，從而會(huì)影響其他使用該索引的查詢語(yǔ)句的性能。
在擴(kuò)展索引的時(shí)候，需要特別小心。因?yàn)槎?jí)索引的葉子節(jié)點(diǎn)包含了主鍵值，所以在列(A)上的索引就相當(dāng)于在(A, ID)上的索引。如果有人用了像 where A=5 order by ID 這樣的查詢，索引(A)就非常有用。但是，如果你將索引(A)修改為索引(A, B)，則實(shí)際上就變成了索引(A, B, ID)，那么，上面查詢的order by語(yǔ)句就無(wú)法使用索引排序，而只能使用文件排序了。
推薦使用Percona工具箱中的pt-upgrade工具來(lái)仔細(xì)檢查計(jì)劃中的索引變更。
因此，只有當(dāng)你對(duì)一個(gè)索引相關(guān)的所有查詢都很清楚時(shí)，才去擴(kuò)展原有的索引。否則，創(chuàng)建一個(gè)新的索引（讓原有索引成為新索引的冗余索引）才是最保險(xiǎn)的方法。
MySQL服務(wù)器中可能會(huì)有一些永遠(yuǎn)都不會(huì)用到的索引，這樣的索引完全是累贅，建議考慮刪除。但要注意的是，唯一索引的唯一性約束功能，可能某個(gè)唯一索引一直沒(méi)有被查詢使用，卻能用于避免產(chǎn)生重復(fù)的數(shù)據(jù)。
重慶分公司
重慶分公司
