MySQL數據庫ORDER BY優化總結(為排序使用索引)
在使用order by時，經常出現Using filesort，所以對于此類sql語句我們需要去盡力優化，使其盡量使用Using index。
那么，我們對于這類型的語句我們怎么去做優化呢?因為這一塊還是比較容易混淆的，所以我弄了個實驗，相信大家跟我一起做下實驗就都能理解了~
1. 環境準備
- drop table if exists test;
- create table test(
- id int primary key auto_increment,
- c1 varchar(10),
- c2 varchar(10),
- c3 varchar(10),
- c4 varchar(10),
- c5 varchar(10)
- ) ENGINE=INNODB default CHARSET=utf8;
- insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
- insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
- insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
- insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
- insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
2. 創建btree索引
- create index idx_c1234 on test(c1,c2,c3,c4);
- show index from test;
3. 范圍掃導致全表掃描
- explain select * from test where c1>'a1' order by c1;
分析：
- 在c1,c2,c3,c4上創建了索引，直接在c1上使用范圍，導致了索引失效，全表掃描：type=ALL，ref=Null。因為此時c1主要用于排序，并不是查詢。
- 使用c1進行排序，出現了Using filesort。
- 解決方法：使用覆蓋索引。
4、覆蓋索引--》優化
- explain select c1 from testwhere c1>'a1' order by c1;
分析：
- 使用了覆蓋索引，不走全掃，走索引范圍掃描
- 排序時按照索引的順序，所以不會出現Using filesort。
這里不懂沒關系，后面我會分享索引的八大法則，保證看得懂...
5. 沒有按最左列索引排序
- explain select c1 from testwhere c1>'a1' order by c2;
分析：
這里出現了Using filesort，是因為排序用的c2，與索引的創建順序(c1,c2,c3,c4)不一致。
6. 排序索引列與索引創建的順序相反
- explain select c1 from testwhere c1>'a1' order by c2,c1;
分析：
這里出現了Using filesort。因為排序索引列(c2,c1)與索引創建的順序(c1,c2)相反，從而產生了重排，也就出現了Using filesort。
7. order by索引列排序不一致
- explain select c1 from testwhere c1>'a1' order by c1 asc,c2 desc;
分析：
雖然排序的字段列與索引順序一樣，且order by默認升序，這里c2 desc變成了降序，導致與索引的排序方式不同，從而產生Using filesort。如果是order by c1 asc,c2 asc或者order by c1 desc,c2 desc就會是using index了。
實驗總結
1. MySQL支持兩種方式的排序filesort和index
Using index是指MySQL掃描索引本身完成排序。index效率高，filesort效率低。
2. 為排序使用索引
假設KEY test(a,b,c)
(1) order by 能使用索引最左前綴
- -order by a
- -order by a,b
- -order by a,b,c
- -order by a asc,b asc,c asc
- -order by a desc,b desc,c desc
(2) 如果where使用索引最左前綴定位為常量，則order by可以使用索引
- -where a= const order by b,c
- -where a= const and b= const order by c
- -where a= const and b> consst order by b,c
(3) 不能使用索引進行排序
- -order by a asc,b desc, c desc /*排序不一致*/
- -where g=const order by b,c /*丟失a索引*/
- -where a=const order by c /*丟失b索引*/
- -where a=const order by a,d /*d不是索引一部分*/
- -where a in (....) order by b,c /*對于排序來說，多個相等條件也是范圍查詢*/
3. filesort有兩種排序算法：雙路排序和單路排序
雙路排序：在MySQL4.1之前使用雙路排序，就是兩次磁盤掃描，得到最終數據。讀取行指針和order by列，對他們進行排序，然后掃描已經排好序的列表，按照列表中的值重新從列表中讀取對應的數據輸出。即從磁盤讀取排序字段，在buffer進行排序，再從磁盤取其他字段。如果使用雙路排序，取一批數據要對磁盤進行兩次掃描，眾所周知，I/O操作是很耗時的，因此在MySQL4.1以后，出現了改進的算法：單路排序。
單路排序：從磁盤中查詢所需的列，按照order by列在buffer中對它們進行排序，然后掃描排序后的列表進行輸出。它的效率更高一些，避免了第二次讀取數據，并且把隨機I/O變成了順序I/O，但是會使用更多的空間，因為它把每一行都保存在內存中了。但當讀取數據超過sort_buffer的容量時，就會導致多次讀取數據，并創建臨時表，最后多路合并，產生多次I/O，反而增加其I/O運算。
解決方式：
- 增加sort_buffer_size參數的設置。
- 增大max_length_for_sort_data參數的設置。
4. 提升order by速度
- 在使用order by時，不要用select *，只查詢所需的字段。因為當查詢字段過多時，會導致sort_buffer不夠，從而使用多路排序或進行多次I/O操作。
- 增加sort_buffer_size。
- 增加max_length_for_sort_data。
5. 優化group by
group by與order by很類似，其實質是先排序后分組，遵照索引創建順序的最佳左前綴法則。當無法使用索引列的時候，也要對sort_buffer_size和max_length_for_sort_data參數進行調整。注意where高于having，能寫在where中的限定條件就不要去having限定了。
