MySQL order by與group by查詢優化實現詳解

Posted on by

前言

order by滿足兩種情況，會使用 index 方式排序：

  • order by語句使用索引最左前列(最左匹配法則)
  • where子句和order by子句條件列組合滿足最左匹配法則(where條件使用索引的最左前綴為常量)

下面給出幾個實例來說明，如下所示我們創建表並為其創建組合索引(c1,c2,c3)。

CREATE TABLE `testc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(100) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL,
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `testc_c1_IDX` (`c1`,`c2`,`c3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

where與order by滿足最左匹配法則

# c1 c2滿足最左匹配法則
explain select * from testc where c1='a1' order by c2
# 與上面等價
explain select * from testc where c1='a1' order by c2,c3

key_len標明查找用到瞭索引 c1，Extra中是Using index condition 沒有同時出現using where ，表明 c2 索引用來讀取數據而非執行查找動作。

MySQL Innodb下的B+樹本身就是多路平衡樹，那麼索引換句話就是排好序的快速查找數據結構。如果order by用到瞭索引且排序和索引次序一樣，那麼無疑效果是最好的。

中間斷裂

如下所示，缺少瞭c2，order by不滿足最左匹配法則。

explain select * from testc where c1='a1' order by c3

可以看到Extra中Using index condition; Using filesort說明雖然where可以用到索引(單獨c1滿足最左匹配)，但是排序不滿足，故而出現瞭filesort。

大哥不在

如下c1不在，那麼很顯然無論查找還是排序都用不到索引。

explain select * from testc where c2='a2' order by c3

這裡Extra是Using where; Using filesort，說明通過where子句過濾結果，然後對結果進行文件排序。

范圍失效

如下所示，中間c2是個范圍搜索，那麼其後索引將失效也就是order by c3無法與where連接滿足最左匹配法則。

explain select * from testc where c1='a1' and c2 > 'a2' order by c3

如下圖所示，這裡type = range,ken_len表示用到瞭 c1,c2索引。Extra是Using index condition; Using filesort表示查詢用到瞭索引但是無法利用索引完成的排序操作。

這種情況如何優化呢？order by c2,c3！這樣就可以保證索引排序而不需要filesort。

explain select * from agriculture.testc where c1='a1' and c2  > 'a2' 
order by  c2,c3

order by 次序相反

如下所示，order by的次序沒有與索引次序保持一致。這裡Extra為Using index condition; Using filesort。

explain select * from testc where c1='a1' order by c3,c2

覆蓋索引

前面幾個都是select *，這裡查找索引列。

沒有where，order by滿足全值匹配，select查詢的數據是索引列。

explain select c1 from testc order by c1, c2,c3

這裡Extra中隻有Using index;

沒有where，order by 大哥丟失，select查詢的數據是索引列。

explain select c1 from testc order by c2,c3

這裡Extra中是Using index; Using filesort 。

這裡Extra信息為Using where; Using index; Using filesort。

explain select c1 from testc where c1='a1' order by c3,c2

filesort的兩種算法

filesort有兩種機制：雙路排序和單路排序。雙路排序簡單來講就是兩次掃描磁盤，最終得到數據。單路排序則是隻需要讀取一次，也就是一次磁盤IO。

雙路排序

MySQL4.1之前是使用雙路排序，讀取行指針和order by列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的數據輸出（可以理解為從磁盤讀取排序字段，在buffer進行排序，然後再從磁盤讀取其他字段）。

取一批數據要進行兩次磁盤IO，這是很耗時的。故而在MySQL4.1之後，出現瞭第二種改進的算法，也就是單路排序。

單路排序

從磁盤讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然後掃描排序後的列表進行輸出。它的效率更快一點，避免瞭第二次讀取數據，並且把隨機IO變成瞭順序IO。但是其會使用更多的空間，因為其緩存瞭數據在內存中。

單路的問題

可能取出的數據大小超過瞭sort_buffer的容量，導致每次隻能取sort_buffer容量大小的數據進行排序（創建tmp文件，多路合並），排完再取sort_buffer容量大小…從而多次IO(可能比雙路更多)。

可以嘗試增大sort_buffer_size參數的設置或者max_length_for_sort_data參數的設置。

總結

order by時select * 是一個大忌，應該是查詢需要的字段。

當query的字段大小總和小於max_length_for_sort_data而且排序字段不是text|blob類型時，會用改進後的算法–單路排序，否則使用雙路排序。

兩種算法的數據都有可能超出sort_buffer的容量，超出之後會創建tmp文件進行合並排序導致多次IO。尤其對於單路排序來說風險更大，所以需要適當調整sort_buffer的容量。

提高max_length_for_sort_data會增加使用單路排序算法的概率。但是如果設置的太高，數據總容量超過sort_buffer的概率就增大，明顯癥狀是磁盤IO高，CPU使用率低。

group by

前面提到的規則針對group by均適用，group by 實質是先排序後分組，遵照索引建的最佳左前綴。當無法使用索引時，增大max_length_for_sort_data和sort_buffer參數的值。

需要註意的是where優先級高於having，能寫在where限定的條件盡量不要通過having。

到此這篇關於MySQL order by與group by查詢優化實現詳解的文章就介紹到這瞭,更多相關MySQL order by與group by內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：