MySQL索引如何進行優化

這篇文章主要介紹了MySQL索引如何進行優化，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

　　創建 test 測試表

　　CREATE TABLE `test` (

　　`id` int(11) NOT NULL AUTO_INCREMENT,

　　`c1` varchar(10) DEFAULT NULL,

　　`c2` varchar(10) DEFAULT NULL,

　　`c3` varchar(10) DEFAULT NULL,

　　`c4` varchar(10) DEFAULT NULL,

　　`c5` varchar(10) DEFAULT NULL,

　　PRIMARY KEY (`id`),

　　KEY `idx_test_c1234` (`c1`,`c2`,`c3`,`c4`)

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('1','a1','a2','a3','a4','a5');

　　insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('2','b1','b2','b3','b4','b5');

　　insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('3','c1','c2','c3','c4','c5');

　　insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('4','d1','d2','d3','d4','d5');

　　insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('5','e1','e2','e3','e4','e5');

　　分析以下Case索引使用情況

　　Case 1：

　　執行以下SQL語句：

　　① EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4'

　　② EXPLAIN SELECT * FROM test WHERE c1='a1' AND c3='a3' AND c2='a2' AND c4='a4'

　　③ EXPLAIN SELECT * FROM test WHERE c1='a1' AND c4='a4' AND c3='a3' AND c2='a2'

　　④ EXPLAIN SELECT * FROM test WHERE c4='a4' AND c2='a2' AND c3='a3' AND c1='a1'

　　分析：創建聯合索引的順序為c1,c2,c3,c4，上述四組explain執行結果都一樣：type=ref，key_len=132，ref=const,const,const,const。

　　結論：在執行常量等值查詢時，改變索引列的順序并不會更改explain的執行結果，因為MySQL底層優化器會自動進行優化，但還是推薦按照索引順序列編寫SQL語句。

　　Case 2：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2'

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'

　　分析：當出現范圍的時候，type=range，key_len=99，比不用范圍key_len=66增加了，說明使用上了索引，但對比Case 1中的執行結果，說明c4上s索引失效。

　　結論：范圍右邊索引列失效，但是范圍當前位置(c3)的索引是有效的，從key_len=99可證明。

　　Case 2.1：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c4>'c4' AND c3='a3'

　　分析：與上面explain執行結果對比，key_len=132說明索引用到了4個，因此對此SQL語句MySQL底層優化器會進行優化(優化成WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4>'c4')：范圍右邊索引列失效(c4右邊已經沒有索引列了)，注意索引的順序(c1,c2,c3,c4)，所以c4右邊不會出現失效的索引列，因此4個索引全部用上。

　　結論：范圍右邊索引列失效，是有順序的：c1,c2,c3,c4，如果c3有范圍，則c4失效;如果c4有范圍，則沒有失效的索引列，從而會使用全部索引。

　　Case 2.2：(聲明：這個Case的解釋有待考察)

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1>'a1' AND c2='a2' AND c3='c3' AND c4='a4'

　　分析：如果在c1處使用范圍，則type=ALL，key=NULL，索引失效，全表掃描，這里違背了最佳左前綴原則，帶頭大哥已死，因為c1主要用于范圍，而不是查詢。

　　解決方式：使用覆蓋索引。

　　結論：在索引最佳左前綴原則中，如果最左前列(帶頭大哥)的索引失效，則后面的索引失效。

　　Case 3：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3

　　分析：利用最佳左前綴原則：中間兄弟不能斷，因此用到了c1和c2索引(查找)，從key_len=66，ref=const,const可以看出來，c3索引列也用在order by排序過程中(即也用到了c3索引)。

　　提問：如何證明order by c3也用到了索引?

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c5

　　分析：因為c5非索引字段，當用order by c5排序時，extra列出現了Using filesort，用到了文件排序，代表沒有使用索引排序，性能低。

　　Case 3.1：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' ORDER BY c3

　　分析：從key_len=66，ref=const,const可以看出來，查找只用到了c1和c2索引，c3索引用于排序。

　　Case 3.2：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' ORDER BY c4

　　分析：從key_len=66，ref=const,const可以看出來，查詢使用了c1和c2索引，由于使用了c4進行排序，跳過了c3，中間斷了，也無法使用c4的索引進行排序，出現了Using filesort。

　　Case 4：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c5='a5' ORDER BY c2,c3

　　分析：查找只用到索引c1，c2和c3索引用于排序，無Using filesort。

　　Case 4.1：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c5='a5' ORDER BY c3,c2

　　分析：和Case 4中explain的執行結果一樣，但是出現了Using filesort，因為索引的創建順序為c1,c2,c3,c4，但是排序的時候c2和c3顛倒位置了。

　　Case 4.2：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' ORDER BY c2,c3

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2,c3

　　分析：在查詢時增加了c5，但是explain的執行結果一樣，因為c5并未創建索引。

　　Case 4.3：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3,c2

　　分析：與Case 4.1相比，在Extra中并未出現Using filesort，因為c2是常量，在排序中被優化，所以索引未顛倒，不會出現Using filesort。

　　Case 5：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c4='a4' GROUP BY c2,c3

　　分析：如果gourp by沒有使用索引，會導致生成臨時表(Using temporary)，底層會先用order by排序，要想group by使用索引分組，前提條件是滿足order by使用索引排序。上面只用到c1上的索引進行查詢，因為c4中間斷了，根據索引最左前綴原則，索引key_len=33，ref=const，表示只用到一個索引。

　　Case 5.1：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1='a1' AND c4='a4' GROUP BY c3,c2

　　分析：對比Case 5，在group by時交換了c2和c3的位置，導致無法滿足order by(Using filesort)，即無法滿足group by(Using temporary)，極度惡劣。原因：c3和c2與索引創建順序相反。

　　Case 6：

　　執行SQL語句：EXPLAIN SELECT * FROM test WHERE c1>'a1' ORDER BY c1

　　分析：

　　① 在c1,c2,c3,c4上創建了索引，直接在c1上使用范圍，導致了索引失效，全表掃描：type=ALL，ref=NULL。因為此時c1主要用于排序，并不是查詢。

　　② 使用c1進行排序，出現了Using filesort。

　　③ 解決方法：使用覆蓋索引。

　　執行SQL語句：EXPLAIN SELECT c1 FROM test WHERE c1>'a1' ORDER BY c1

　　Case 7：

　　執行SQL語句：

　　EXPLAIN SELECT c1 FROM test ORDER BY c1 ASC, c2 DESC

　　分析：雖然排序的字段列與索引順序一樣，且order by默認升序，這里c2 DESC變成了降序，導致與索引的排序方式不同，從而產生Using filesort。

　　Case 8：

　　執行SQL語句：EXPLAIN SELECT c1 FROM test WHERE c1 IN('a1','b1') ORDER BY c2,c3

　　分析：對于排序來說，多個相等條件也是范圍查詢。

　　總結：

　　① MySQL支持兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序。index效率高，filesort效率低。

　　② order by滿足兩種情況會使用Using index：

　　a. order by語句使用索引最左前列。

　　b. 使用where子句與order by子句條件列組合滿足索引最左前列。

　　③ 盡量在索引列上完成排序，遵循索引建立(索引創建的順序)時的最佳左前綴原則。

　　④ 如果order by的條件不在索引列上，就會產生Using filesort。

　　⑤ group by與order by很類似，其實質是先排序后分組，遵照索引創建順序的最佳左前綴原則。注意where高于having，能寫在where中的限定條件就不要去having限定了。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL索引如何進行優化”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!