前言

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

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

下面給出幾個實例來說明，如下所示我們創(chuàng)建表并為其創(chuàng)建組合索引(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標(biāo)明查找用到了索引 c1，Extra中是Using index condition 沒有同時出現(xiàn)using where ，表明 c2 索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。

MySQL Innodb下的B+樹本身就是多路平衡樹，那么索引換句話就是排好序的快速查找數(shù)據(jù)結(jié)構(gòu)。如果order by用到了索引且排序和索引次序一樣，那么無疑效果是最好的。

中間斷裂

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

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

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

大哥不在

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

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

這里Extra是Using where; Using filesort，說明通過where子句過濾結(jié)果，然后對結(jié)果進(jìn)行文件排序。

范圍失效

如下所示，中間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表示查詢用到了索引但是無法利用索引完成的排序操作。

這種情況如何優(yōu)化呢？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查詢的數(shù)據(jù)是索引列。

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

這里Extra中只有Using index;

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

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有兩種機(jī)制：雙路排序和單路排序。雙路排序簡單來講就是兩次掃描磁盤，最終得到數(shù)據(jù)。單路排序則是只需要讀取一次，也就是一次磁盤IO。

雙路排序

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

取一批數(shù)據(jù)要進(jìn)行兩次磁盤IO，這是很耗時的。故而在MySQL4.1之后，出現(xiàn)了第二種改進(jìn)的算法，也就是單路排序。

單路排序

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

單路的問題

可能取出的數(shù)據(jù)大小超過了sort_buffer的容量，導(dǎo)致每次只能取sort_buffer容量大小的數(shù)據(jù)進(jìn)行排序（創(chuàng)建tmp文件，多路合并），排完再取sort_buffer容量大小…從而多次IO(可能比雙路更多)。

可以嘗試增大sort_buffer_size參數(shù)的設(shè)置或者max_length_for_sort_data參數(shù)的設(shè)置。

總結(jié)

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

當(dāng)query的字段大小總和小于max_length_for_sort_data而且排序字段不是text|blob類型時，會用改進(jìn)后的算法–單路排序，否則使用雙路排序。

兩種算法的數(shù)據(jù)都有可能超出sort_buffer的容量，超出之后會創(chuàng)建tmp文件進(jìn)行合并排序?qū)е露啻蜪O。尤其對于單路排序來說風(fēng)險更大，所以需要適當(dāng)調(diào)整sort_buffer的容量。

提高max_length_for_sort_data會增加使用單路排序算法的概率。但是如果設(shè)置的太高，數(shù)據(jù)總?cè)萘砍^sort_buffer的概率就增大，明顯癥狀是磁盤IO高，CPU使用率低。

group by

前面提到的規(guī)則針對group by均適用，group by 實質(zhì)是先排序后分組，遵照索引建的最佳左前綴。當(dāng)無法使用索引時，增大max_length_for_sort_data和sort_buffer參數(shù)的值。

需要注意的是where優(yōu)先級高于having，能寫在where限定的條件盡量不要通過having。

到此這篇關(guān)于MySQL order by與group by查詢優(yōu)化實現(xiàn)詳解的文章就介紹到這了,更多相關(guān)MySQL order by與group by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論