MySQL中高效正確使用索引的方法

這篇文章將為大家詳細講解有關MySQL中高效正確使用索引的方法，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

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

一、索引失效

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

索引失效，是指表中有字段創建了索引，由于sql語句書寫不當導致索引失效的情況。

在sql語句中，將索引列作為表達式的一部分、參與函數/數學等運算，將會導致索引失效。

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

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

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

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

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

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

通常可以索引開始的部分字符，這樣可以大大節約索引空間，從而提高索引效率，但這樣也會降低索引的選擇性。

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

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

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

三、多列索引

多列索引，是指為每個列創立獨立的索引。

在SQL優化時，有人會采取“把where條件里面的列都建上索引”，希望能夠對查詢性能有所優化。但實際上這樣的優化是非常錯誤的，這樣一來最好的情況下也只能是“一星”索引，其性能比起真正最優的索引可能差幾個數據級。有時如果無法設計一個“三星”索引，那么不如忽略掉where子句，集中精力優化索引列的順序，或者創建一個全覆蓋索引。

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

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

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

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

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

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

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

如果在執行計劃EXPLAIN中看到索引合并，應該好好檢查一下查詢和表的結構，看是不是已經是最優的。也可以通過參數optimizer_switch來關閉索引合并功能，也可以使用IGNORE INDEX提示讓優化器忽略掉某些索引。

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

舉例說明如下：

表t_user創建了(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

根據id進行查詢，具體如下：

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

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

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

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

索引列順序實在是非常重要的。正確的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好的滿足排序和分組的需要（只用于B-Tree索引，哈希或者其他索引存儲數據并不是順序存儲）。

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

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

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

五、聚簇索引

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

非聚簇索引：將數據存儲與索引分開存儲，索引結構的葉子節點指向了數據的對應行。當需要訪問數據時（通過索引訪問數據），在內存中直接搜索索引，然后通過索引找到磁盤相應數據，這也就是為什么索引不在key buffer命中時，速度慢的原因。

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

聚簇索引的設定：

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

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

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

聚簇索引的優點：

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

聚簇索引的缺點：

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

六、覆蓋索引

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

覆蓋索引的好處如下：

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

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

七、使用索引掃描來排序

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

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

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

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

八、冗余、重復索引

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

比如：　　　

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

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

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

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

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

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

九、未使用的索引

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

可以使用Performance_schema中的table_io_waits_summary_by_index_usage表進行查找：

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的行鎖效率很高，內存使用也很少，但是鎖定行的時候仍然會帶來額外的開銷，其次，鎖定超過需要的行會增加鎖爭用并減少并發性。

十一、總結

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

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

1. 在區分度高的字段上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有數據頁，此時和不使用索引區別不大。
2. 聯合索引，注意最左匹配原則：必須按照從左到右的順序匹配，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的順序可以任意調整。
3. 查詢記錄的時候，少使用*，盡量去利用索引覆蓋，可以減少回表操作，提升效率。
4. 有些查詢可以采用聯合索引，進而使用到索引下推，也可以減少回表操作，提升效率。
5. 禁止對索引字段使用函數、運算符操作，這樣將會使索引失效。
6. 字符串字段和數字比較的時候會使索引無效。
7. 模糊查詢 '%值%' 會使索引無效，變為全表掃描，但是 '值%' 這種可以有效利用索引。
8. 排序中盡量使用到索引字段，這樣可以減少排序，提升查詢效率。

關于MySQL中高效正確使用索引的方法就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。