學習筆記: 【MYSQL】order by 和group by索引優化

1.order by 子句，盡量使用index方式排序，避免使用 filesort方式排序（即explain select...,查看Extra的欄位，using index 優於 using filesort）
2.盡可能在索引列上完成排序操作，遵照索引的最佳左前綴
3.如果不在索引列上，filesort會使用到sort_buffer的容量去進行排序，如果查詢的字段的總數據超出了sort_buffer的容量，就會導致每次只能取sort_buffer容量大小的數據，進行排序（創建tmp文件，再合併），排完後再次取sort_buffer容量大小，再排.....大量的I/O操作。解決：增大max_length_for_sort_data參數的設置＋增大sort_buffer_size參數的設置

提高order by 的速度

order by 時大忌是使用 select *
當query的字段大小總和小於 max_length_for_sort_data 而且排序字段不是TEXT | BLOB類型時，會用上列第三個算法排序
當嘗試提高sort_buffer_size，此參數會提高效率，但實際還是要根據系統的能力去提高，因為此值針對每個進程的
當嘗試提高max_length_for_sort_data，如果設太高，數據總容量會超出sort_buffer_size的概率就會增大，反而是提高硬碟I/O活動和降低處理器使用率

MYSQL兩種排序方式：結果直接或結果字段使用到索引即產生有效排序

MYSQL能為排序與查詢使用相同的索引

key a_b_c(a,b,c)

order by 可以使用到索引最左前綴

-order by a

-order by a,b

-order by a,b,c

-order by a desc, b desc , c desc

如果where使用索引的最左前綴定義為常量(const)，則order by 能使用索引

-where a = const order by b,c

-where a = const and b = const order by c

-where a = const order by b,c

-where a = const and b > const order by b,c

不能使用索引進行排序

-order by a asc, b desc , c desc (因排序不一致)

-where f = 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 (a = const order by a,d (d非索引)

-where a in (...) order by b,c (對於排序來說，多個相等條件也是範圍查詢)

group by 同order by

學習筆記