MySQL性能優(yōu)化之如何高效正確的使用索引

實踐是檢驗真理的唯一途徑，本篇只是站在索引使用的全局來定位的，你只需要通讀全篇并結(jié)合具體的例子，或回憶以往使用過的地方，對整體有個全面認(rèn)識，并理解索引是如何工作的，就可以了。在后續(xù)使用索引，或者優(yōu)化索引時，可以從這些方面出發(fā)，進(jìn)一步來加深對索引正確高效的使用。

一、索引失效

索引失效，是一個老生常談的話題了。只要提到數(shù)據(jù)庫優(yōu)化、使用索引，都能一口氣說出一大堆索引失效的場景，什么不能用、什么不該用這類的話，在此，我就不再一一羅列啰嗦了。

索引失效，是指表中有字段創(chuàng)建了索引，由于sql語句書寫不當(dāng)導(dǎo)致索引失效的情況。

在sql語句中，將索引列作為表達(dá)式的一部分、參與函數(shù)/數(shù)學(xué)等運算，將會導(dǎo)致索引失效。

例如，下面這個查詢無法使用age列的索引：

select id,name,age from t_user where age + 1 = 7;

很容易看出where中的表達(dá)式其實等價于age=8，但是MySQL無法自動解析這個表達(dá)式，這完全是用戶行為。

（在上一篇文章中，我們知道MySQL先在索引上按值進(jìn)行查找，然后返回索引值對應(yīng)的數(shù)據(jù)行，一旦對索引列進(jìn)行運算，則將無法正確的找到對應(yīng)的數(shù)據(jù)行，從而改為全表逐行掃描查詢對比）

二、前綴索引和索引選擇性

有時候?qū)?nèi)容很長的列作為索引列，這將會讓索引變得很大而且很慢。如果非要在該列添加索引，解決策略就是上一篇文章提到過的模擬哈希索引。

通?？梢运饕_始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率，但這樣也會降低索引的選擇性。

索引的選擇性是指，不重復(fù)的索引值(也稱為基數(shù))和表數(shù)據(jù)的記錄總數(shù)T的比值，范圍從1/T到1之間。索引的選擇性越高，則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行。

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

對于BLOB、TEXT或很大的VARCHAR類型的列，作為查詢條件時（原則上是要避免這樣的操作，但有時總是情非得已），該列必須使用前綴索引，這樣來提高查詢性能。因為MySQL是不允許索引這些列的完整長度的。

三、多列索引

多列索引，是指為每個列創(chuàng)立獨立的索引。

在SQL優(yōu)化時，有人會采取“把where條件里面的列都建上索引”，希望能夠?qū)Σ樵冃阅苡兴鶅?yōu)化。但實際上這樣的優(yōu)化是非常錯誤的，這樣一來最好的情況下也只能是“一星”索引，其性能比起真正最優(yōu)的索引可能差幾個數(shù)據(jù)級。有時如果無法設(shè)計一個“三星”索引，那么不如忽略掉where子句，集中精力優(yōu)化索引列的順序，或者創(chuàng)建一個全覆蓋索引。

三星索引：在Lahdenmaki和Leach編寫的Relational Database Index Design and the Optimizers一書中，提到如何評價一個索引是否適合某個查詢的“三星系統(tǒng)”：索引將相關(guān)的記錄放到一起則獲得“一星”；如果索引中的數(shù)據(jù)順序和查找中的排序順序一致則獲得“二星”；如果索引中的列包含了查詢中需要的全部列則獲得“三星”。

在多個列上建立獨立的單列索引，大部分情況下并不能提高M(jìn)ySQL的查詢性能。這也是將其錯誤的做法。

MySQL5.0及之后版本引入了索引合并策略，一定程度上可以使用表上的多個單列索引來定位指定的行。更早的MySQL只能使用其中某一個單列索引，然而這個情況下沒有哪一個獨立的單列索引是非常有效的。

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

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

2）當(dāng)需要對多個索引做聯(lián)合操作室（通常有多個OR條件），通常需要耗費大量的CPU和內(nèi)存資源在算法的緩存、排序和合并操作上。特別是當(dāng)其中有些索引的選擇性不高，需要合并掃描返回的大量數(shù)據(jù)的時候。

3）優(yōu)化器不會把這些計算到“查詢成本”中，優(yōu)化器只關(guān)心隨機(jī)頁面讀取。這會使得查詢的成本被“低估”，導(dǎo)致該執(zhí)行計劃還不如直接走全表掃描。這樣做不但會消耗更多的CPU和內(nèi)存資源，還可能會影響查詢的并發(fā)性，但如果是單獨運行這樣的查詢，則往往會忽略對并發(fā)性的影響。

如果在執(zhí)行計劃EXPLAIN中看到索引合并，應(yīng)該好好檢查一下查詢和表的結(jié)構(gòu)，看是不是已經(jīng)是最優(yōu)的。也可以通過參數(shù)optimizer_switch來關(guān)閉索引合并功能，也可以使用IGNORE INDEX提示讓優(yōu)化器忽略掉某些索引。

對于多列索引，只要查詢的條件中用到了最左邊的列，索引一般就不會失效。

舉例說明如下：

表t_user創(chuàng)建了(id,name)的多列索引，具體如下：

mysql> show create table t_user;
+--------+---------------+
| Table | Create Table |
+--------+---------------+
| t_user | CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `idx` (`id`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------+
1 row in set

根據(jù)id進(jìn)行查詢，具體如下：

mysql> explain select * from t_user where id = 1;
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
| 1 | SIMPLE   | t_user | NULL    | ref | idx      | idx | 4    | const |  1 |   100 | NULL |
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
1 row in set

從執(zhí)行計劃中的type可以看出，索引是有效的。但如果根據(jù)name進(jìn)行查詢，則索引將會失效(全表掃描)，如下：

mysql> explain select * from t_user where name = 'xcbeyond';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | t_user | NULL    | ALL | NULL     | NULL | NULL  | NULL |  7 |  14.29 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

四、選擇合適的索引列順序

索引列順序?qū)嵲谑欠浅Ｖ匾?。正確的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好的滿足排序和分組的需要（只用于B-Tree索引，哈?；蛘咂渌饕鎯?shù)據(jù)并不是順序存儲）。

在一個多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進(jìn)行排列。所以索引可以按照升序或者降序進(jìn)行掃描，以滿足符合列順序的order by，group by和distinct等子句的查詢需求。

所以多列索引列的順序至關(guān)重要。對于如何選擇索引的列順序有一個經(jīng)驗法則：將選擇性最高的索引放在索引的最前列。在某些場景這個經(jīng)驗時非常有用，但是通常不如避免隨機(jī)IO和排序那么重要，考慮問題需要更全面。

當(dāng)不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。這時候索引的作用只是用于優(yōu)化where條件的查找。這種情況下，這樣設(shè)計的索引確實能夠最快的過濾出需要的行，對于在where的子句中只是用了索引部分前綴列的查詢來說選擇性也更高。然而性能不只是依賴于所有索引列的選擇性，也和查詢條件的具體值有關(guān)，也就是和值的分布有關(guān)（需要根據(jù)那些運行頻率最高的查詢來調(diào)整索引列的順序，讓這種情況下的索引列的選擇性最高）。

五、聚簇索引

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

非聚簇索引：將數(shù)據(jù)存儲與索引分開存儲，索引結(jié)構(gòu)的葉子節(jié)點指向了數(shù)據(jù)的對應(yīng)行。當(dāng)需要訪問數(shù)據(jù)時（通過索引訪問數(shù)據(jù)），在內(nèi)存中直接搜索索引，然后通過索引找到磁盤相應(yīng)數(shù)據(jù)，這也就是為什么索引不在key buffer命中時，速度慢的原因。

當(dāng)表有聚簇索引時，它的數(shù)據(jù)行實際上存放在索引的葉子頁中。“聚簇”表示數(shù)據(jù)行和相鄰的鍵值緊湊的存儲在一起。因為無法同時把數(shù)據(jù)行存放在兩個不同的地方，所以一個表只能有一個聚簇索引。

聚簇索引的設(shè)定：

默認(rèn)為主鍵。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoD會隱式定義一個主鍵來作為聚簇索引。InnoDB只聚集在同一個頁面中的記錄，包括相鄰鍵值的頁面可能會相距甚遠(yuǎn)。

(看到這里，如果你對B-Tree索引結(jié)構(gòu)熟悉的話，就知道為啥[key、data]作為一個二元組存放在一個節(jié)點了)

聚簇主鍵可能對性能有幫助，但也可能導(dǎo)致嚴(yán)重的性能問題。所以需要仔細(xì)的考慮聚簇索引，尤其是將表的存儲引擎從InnoDB改成其他引擎的時候（反過來也一樣）。

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

• 可以把相關(guān)數(shù)據(jù)保存在一起。例如實現(xiàn)電子郵箱時，可以根據(jù)用戶ID來聚集數(shù)據(jù)，這樣子只需要從磁盤中讀取少數(shù)的數(shù)據(jù)也技能獲取某個用戶的全部郵件。
• 數(shù)據(jù)訪問更快。聚簇索引把索引和數(shù)據(jù)都放在同一個B-Tree中，因此從聚簇索引中獲取數(shù)據(jù)比從非聚簇索引中要快。
• 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點中的主鍵值。
  

聚簇索引的缺點：

• 最大限度的提高了I/O密集型應(yīng)用的性能，但如果數(shù)據(jù)全部都放在內(nèi)存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優(yōu)勢了。
• 插入速度嚴(yán)重依賴于插入順序。按照主鍵的順序插入是加載數(shù)據(jù)到InnoDB表中速度最快的方式。但如果不是按照逐漸順序加載數(shù)據(jù)，那么在加載完成后最好使用OPTIMIZE TABLE重新組織一下表。
• 更新聚簇索引列的代價很高。因為要強(qiáng)制InnoDB將每個被更新的行移動到新的位置。
• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致移動行的時候，可能面臨“頁分裂”的問題。當(dāng)行的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁來容納該行，這就是一次頁分裂操作，這也意味著這樣導(dǎo)致表占用更多的磁盤空間。
• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏時，或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時候。
• 二級索引(非聚簇索引)可能比想想的要更大。因為二級索引的葉子結(jié)點包含了引用行的主鍵列。
• 二級索引訪問需要兩次索引查找，而不是一次。
  

六、覆蓋索引

通常大家都會根據(jù)查詢的where條件來創(chuàng)建合適的索引，不過這也只是索引優(yōu)化的一個方面。設(shè)計優(yōu)秀的索引應(yīng)該考慮到整個查詢，而不單單是where條件部分。索引確實是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來直接獲取列的數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行。如果一個索引包含所有需要查詢的字段值，我們就稱其為“覆蓋索引”，即：一個索引覆蓋where條件的所有列。

覆蓋索引的好處如下：

• 索引條目通常遠(yuǎn)小于數(shù)據(jù)行的大小，所以如果只需要讀取索引，那么MySQL就會極大的減少數(shù)據(jù)訪問量。這對緩存的負(fù)載非常重要，因為這種情況下響應(yīng)時間大部分花費在數(shù)據(jù)拷貝下。覆蓋索引對于I/O密集型的應(yīng)用也有幫助，因為索引比數(shù)據(jù)更小，更容易全部放進(jìn)去內(nèi)存。
• 因為索引是按照列值順序存儲的，對于I/O密集型的范圍查詢會比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的I/O要少的多。對于某些存儲引擎，例如MyISAM和Percona XtraDB，甚至可以通過POTIMIZE命令使得索引完全順序排列，這樣就可以讓簡單的范圍查詢能使用完全排序的索引訪問。
• 一些存儲引擎，如MyISAM在內(nèi)存中只緩存索引。數(shù)據(jù)則依賴于操作系統(tǒng)來緩存，因此要訪問數(shù)據(jù)需要一次系統(tǒng)調(diào)用。這可能會導(dǎo)致嚴(yán)重的性能問題，尤其是那些系統(tǒng)調(diào)用占了數(shù)據(jù)訪問中的最大開銷的場景。
• 由于InnoDB的聚簇索引，覆蓋索引對于InnoDB表特別有用。InnoDB的二級索引在葉子節(jié)點保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢
  

不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用B-Tree所以來做覆蓋索引，另外不同的存儲引擎實現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

七、使用索引掃描來排序

MySQL有兩種方式可以生成有序的結(jié)果集：通過排序操作，或者按索引順序掃描。如果EXPLAIN出來的type列的值為index，則說明MySQL使用了索引掃描來做排序。

掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對應(yīng)的行。這基本上都是隨機(jī)I/O，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序的全表掃描慢，尤其是在I/O密集型的工作負(fù)載時。

MySQL可以使用同一個索引既滿足排序，又用于查找行。因此，如果可能，設(shè)計索引時應(yīng)該盡可能的同時滿足這兩種情況，即：索引列作為排序列。

• 只有當(dāng)索引的列順序和order by子句的順序完全一致，并且所有列的排序方向都一樣時，MySQL才能夠使用索引來對結(jié)果做排序。
• 如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)order by子句引用的字段全部為第一個表時，才能使用索引做排序。order by子句和查找性查詢的限制是一樣的：需要滿足索引的最左前綴的要求；否則，MySQL都需要執(zhí)行的順序操作，而無法使用索引排序。
  

八、冗余、重復(fù)索引

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

比如：　　　

create table test{
 id int not null primary key,
 a int not null,
 b int not null,
 unique(id)
 index(id)
}engine=InnoDB;

一個經(jīng)驗不足的人可能是想創(chuàng)建一個主鍵，先加上唯一限制(unique(id))，然后再加上索引(index(id))以供查詢使用。然而唯一限制和主鍵限制都是通過索引使用，因此，上面的寫法實際上在相同的列上創(chuàng)建了三個重復(fù)的索引。通常并沒有理由要這樣做，除非是在同一列上創(chuàng)建不同類型的索引來滿足不同的查詢需求。

冗余索引和重復(fù)索引有一些不同，比如：如果創(chuàng)建了索引（A,B），再創(chuàng)建（A）那就是冗余索引，因為A就是前一個索引的前綴索引。索引（A,B）完全就可以當(dāng)做A來使用。但是如果創(chuàng)建了索引（B,A）那就不是冗余索引了，索引B也不是。因為B不是索引(A,B）的最左前綴索引。另外，其他不同類型的索引，例如哈希，全文索引也不會是B-Tree的冗余索引。

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

大多數(shù)情況下不需要冗余索引，應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新的索引。但也有時候處于性能方面的考慮需要冗余索引，因為擴(kuò)展已有的索引會導(dǎo)致其變得太大 ，從而影響其他使用該索引的查詢的性能。例如，在一個整數(shù)列索引上添加一個很長的varchar列，那性能可能會急劇下降。特別是有索引把這個索引當(dāng)中覆蓋索引時，或者這是MyISAM表并且有很多范圍查詢的時候。

解決冗余索引和重復(fù)索引的方法非常簡單，刪除這些索引就可以。但是首先要做的事找出這樣的索引?？梢酝ㄟ^寫一些復(fù)雜的訪問information_schema表的查詢來找，不過還有兩個更簡單的方法就是使用Shlomi Noach的common_schema中的一些視圖來定位（common_schema是一系列可以安裝在服務(wù)器上的常用的存儲和視圖）。另外一個方法就是使用Percona Toolkit中的pt_duplicate-key-checker，該工具通過分析表結(jié)構(gòu)來找出冗余和重復(fù)索引。

九、未使用的索引

除了冗余索引和重復(fù)索引，可能還會有一些服務(wù)器永遠(yuǎn)不用的索引。這樣的索引完全是累贅，建議直接刪除。

可以使用Performance_schema中的table_io_waits_summary_by_index_usage表進(jìn)行查找：

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name, index_name;

十、索引和鎖

索引可以讓查詢鎖定更少的行。如果你的查詢從不訪問那些不需要的行，那么就會鎖定更少的行，從兩個方面來看這對性能都有什么好處。

首先，雖然InnoDB的行鎖效率很高，內(nèi)存使用也很少，但是鎖定行的時候仍然會帶來額外的開銷，其次，鎖定超過需要的行會增加鎖爭用并減少并發(fā)性。

十一、總結(jié)

通過上面大篇文字的講解，都是用來說明如何高效的使用索引，避免錯誤使用。索引是一個看似簡單，但實際用起來卻是非常復(fù)雜的東西，要想真正用好它，需要不斷的實踐。實踐是檢驗真理的唯一途徑，本篇只是站在索引使用的全局來定位的，你只需要通讀全篇并結(jié)合具體的例子，或回憶以往使用過的地方，對整體有個全面認(rèn)識，并理解索引是如何工作的，就可以了。在后續(xù)使用索引，或者優(yōu)化索引時，可以從這些方面出發(fā)，進(jìn)一步來加深對索引正確高效的使用。

在平時使用索引中，有以下幾點總結(jié)及建議：

1. 在區(qū)分度高的字段上面建立索引可以有效的使用索引，區(qū)分度太低，無法有效的利用索引，可能需要掃描所有數(shù)據(jù)頁，此時和不使用索引區(qū)別不大。
2. 聯(lián)合索引，注意最左匹配原則：必須按照從左到右的順序匹配，MySQL會一直向右匹配直到遇到范圍查詢(>、、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。
3. 查詢記錄的時候，少使用*，盡量去利用索引覆蓋，可以減少回表操作，提升效率。
4. 有些查詢可以采用聯(lián)合索引，進(jìn)而使用到索引下推，也可以減少回表操作，提升效率。
5. 禁止對索引字段使用函數(shù)、運算符操作，這樣將會使索引失效。
6. 字符串字段和數(shù)字比較的時候會使索引無效。
7. 模糊查詢 '%值%' 會使索引無效，變?yōu)槿頀呙?，但?code> '值%' 這種可以有效利用索引。
8. 排序中盡量使用到索引字段，這樣可以減少排序，提升查詢效率。

以上就是MySQL性能優(yōu)化之如何高效正確的使用索引的詳細(xì)內(nèi)容，更多關(guān)于MySQL 索引的資料請關(guān)注腳本之家其它相關(guān)文章！