創建 test 測試表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(10) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, `c3` varchar(10) ...
創建 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限定了。
通俗理解口訣：
全值匹配我最愛，最左首碼我的菜；
帶頭大哥不能死，中間兄弟斷狗帶；
索引列上少計算，範圍之後全完蛋；
覆蓋索引不寫星，Like百分右邊站；
不等空值還有or，索引失效要少用。
補充：in和exists優化
原則：小表驅動大表，即小的數據集驅動大的數據集。
in：當B表的數據集小於A表的數據集時，in優於exists
select * from A where id in (select id from B)
等價於：
for select id from B
for select * from A where A.id=B.id
exists：當A表的數據集小於B表的數據集時，exists優於in
select * from A where exists (select 1 from B where B.id=A.id)
等價於：
for select * from A
for select * from B where B.id=A.id
（A表與B表的id欄位應建立索引）
