MySQL如何對order by優化？

對于order by的優化，MySQL若可以利用索引的有序性進行排序，則優先使用索引進行排序，這種情況的執行效率是最快的；若無法有效利用索引的情況下，MySQL主要有3排序種算法對其進行優化每個算法都有一定的适用場景。

一、利用索引排序

B-tree索引可以很好的支援單點查詢、範圍查詢、有序性查詢。是以對于order by 的排序查詢，我們可以利用B-tree的有序性來有效的利用索引進行排序查詢。當然，如果可以利用索引進行排序對我們的SQL查詢本身也是有一定的要求限制的。

1.1 利用索引排序的特點

1）排序列必須有B-tree索引

2）如果為多表關聯查詢，排序列必須是對驅動表字段的排序

1.2、示例

##建表語句，sbtest3與sbtest4表字段與索引一緻，sbtest3的表資料量為30000，sbtest4的表資料量為60000
CREATE TABLE `sbtest4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_4` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=62768 DEFAULT CHARSET=utf8mb4

##單表排序查詢
##order by字段為B-tree索引字段，可以看到執行計劃有效利用了索引進行排序查詢
root@mysql57 13:25:  [db2]> explain select * from sbtest4 t4 order by t4.k desc limit 5;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | k_4  | 4       | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


##多表關聯排序查詢
##sbtest3為小表，在表關聯中作為驅動表與sbtest4進行關聯查詢，order by字段為驅動表b-tree索引字段，可有效利用索引進行查詢
root@mysql57 13:26:  [db2]> explain select * from sbtest4 t4 join sbtest3 t3 on t4.id=t3.id order by t3.k limit 5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | index  | PRIMARY       | k_3     | 4       | NULL      |    5 |   100.00 | NULL  |
|  1 | SIMPLE      | t4    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db2.t3.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

1.3、有效利用複合索引進行排序查詢

1、當複合索引的最左字首列為過濾條件的常量過濾時，order by字段配合常量過濾字段滿足最左字首時可以使用複合索引進行排序優化。

如，建立複合索引(a,b,c)可以使用複合索引掃描排序有：

from tbl_name where a=xx order by b,c;
from tbl_name where a=xx order by b;

2、過濾字段不是複合索引中的常量，但是order by列滿足最左字首是可以使用覆寫索引：

from tbl_name where a>xx order by a,b     #order by字段滿足最左字首

3、一些情況不能使用複合索引掃描排序的情況

from tbl_name where a=xx order by b desc,c asc;  #一列為升序一列為降序
from tbl_name where a=xx order by b,d;      #order by列引用了一個不在索引中的字段
from tbl_name where a=xx order by c;  #無法組合成索引的最左字首
from tbl_name where a=xx and b in (xx,xx) order by c;    #存在範圍查詢

4、值得注意的一個小問題

如果我們建立單列索引（A），實際上相當于在（A，ID）上建立了索引，其中ID為主鍵。這中情況下對于 where A=xx order by ID的查詢是非常有幫助的。但是如果我們建立了複合索引（A,B）,那麼就相當于在(A,B,ID)上建立了索引，那麼對于where A=xx order by ID這樣的查詢，就使用不到索引掃描排序，隻能用filesort排序(using filesort)了。

1.4、無法使用索引排序的幾種情況

1）排序基準太多，無法有效利用索引滿足所有的排序字段準則

2）對多個union子查詢進行排序

3）需要随機擷取結果記錄

二、僅對驅動表排序

2.1、僅對驅動表進行排序的特點

1）多表關聯查詢中，排序字段為驅動表字段，且該字段無法有效利用索引

2）被驅動表關聯字段為有效索引字段，有效利用INLJ算法進行表關聯

2.2、示例

##sbtest3作為驅動表，通過k字段的索引進行過濾找到滿足where條件的記錄
##由于order by字段c無法有效利用索引，是以必須将滿足過濾條件的記錄放至sort buffer進行排序處理
##在執行計劃中，我們可以看到“Using filesort”表示使用filesort進行了排序處理

root@mysql57 13:55:  [db2]> explain select * from sbtest4 t4 join sbtest3 t3 on t4.id=t3.id where t3.k<1000 order by t3.c limit 5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                                 |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range  | PRIMARY,k_3   | k_3     | 4       | NULL      |    1 |   100.00 | Using index condition; Using filesort |
|  1 | SIMPLE      | t4    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db2.t3.id |    1 |   100.00 | NULL                                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)

2.3、對于使用驅動表排序的優化

若SQL無法有效利用索引進行優化，且僅僅是對驅動表進行排序處理，這已然是一種相對較好的情況，我們更多的隻需要關注SQL的where過濾條件是否可以有效利用索引減少驅動表需要掃描以及排序的記錄數。

三、使用臨時表進行排序

3.1、使用臨時表表進行排序的特點

多表關聯查詢中，排序字段為被驅動表字段，MySQL必須擷取到多表關聯的結果後才可以對這些記錄進行排序處理

3.2、示例

##sbtest3作為驅動表，通過k字段的索引進行過濾找到滿足where條件的記錄
##由于order by字段為被驅動表的c字段，是以必須将擷取到兩表join的結果集，然後進行排序
##在執行計劃中，我們可以看到“Using temporary; Using filesort”表示使用臨時表進行了排序處理

root@mysql57 14:00:  [db2]> explain select * from sbtest4 t4 join sbtest3 t3 on t4.id=t3.id where t3.k<1000 order by t4.k limit 5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range  | PRIMARY,k_3   | k_3     | 4       | NULL      |    1 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | t4    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db2.t3.id |    1 |   100.00 | NULL                                                   |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

3.3、對于使用臨時表排序的優化

對于使用臨時表進行排序的查詢其資源消耗是以上說到的三種排序方式下資源消耗最大的一種排序方式。在這種模式下，優先考慮排序字段是否可以等價替換為驅動表字段，将其轉換為隻對驅動表進行排序；若以上手段無效，我們隻能通過where過濾條件有效利用索引，通過索引過濾盡量減少SQL查詢掃描資料量；select隻查詢需要的字段，避免select *，盡量減少磁盤臨時表的使用。

四、三種排序方式對比

排序處理方式	執行計劃extra列資訊	排序性能比較
利用索引進行排序	無	高
隻對驅動表進行排序	Using filesort	中
對臨時表進行排序	Using temporary; Using filesort	低

4.1 利用索引進行排序

對于order by ... limit N的排序查詢，MySQL會優先權衡是否可以通過優先級隊列排序，在記憶體中完成排序。

4.2 filesort排序

MySQL優化器會優先選擇通過索引進行排序查詢，若SQL無有效索引可利用，一般會優先根據where條件進行索引過濾，将需要滿足過濾條件的記錄放在sort buffer中進行排序處理。若需要排序的記錄較少，sort_buffer_size的大小即可以滿足，此時的排序處理是相對比較快的；若需要排序的記錄較大或者有textblob這種大字段，sort_buffer_size大小無法滿足一次性對這些記錄進行排序，那麼MySQL會将需要排序的記錄切分為多塊兒，每塊兒通過sort buffer進行排序後，将結果集轉儲至磁盤臨時表，最終将這些排好序的記錄進行合并傳回，這種排序方式也較多“多路并歸排序”。

4.3 關于sort_buffer_size

對于多路并歸的排序方式，理論上隻要我們的sort_buffer_size足夠大，就可以避免使用到磁盤臨時表，但是若該參數是基于會話級别的，若設定不合理極有可能占用過多記憶體，導緻OOM。

4.4 排序相關參數以及具體含義

mysql>show global status like 'sort%';
+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| Sort_merge_passes       | 279044          |   //多路并歸方式處理的合并次數
| Sort_range              | 33816597        |   //通過索引範圍掃描檢索的結果進行排序的次數
| Sort_rows               | 7349842715      |   //目前為止已排序的全部記錄數
| Sort_scan               | 148047752       |   //通過全表掃描檢索的結果進行排序的次數
+-------------------------+-----------------+
傳回行數：[4]，耗時：8 ms.

五、三種排序掃描算法

5.1 兩次掃描算法

通過兩次掃描算法的基本處理流程：

1、排序時，隻将排序列和主鍵值放入sort buffer中進行排序處理，此步驟為第一次掃描，順序IO；

2、若sort buffer可以一次性存儲所有需要排序字段，則直接在sort buffer中進行排序；

3、若sort_buffer_size無法滿足一次性存儲全部的排序字段，則會将每次讀取到sort buffer中的排序記錄固化到磁盤，多路歸并排序算法，保證臨時檔案中記錄是有序的。

4、根據排好序的記錄通過主鍵回表查詢，讀取需要的表資料。由于此時是根須排序字段進行排序的，通過主鍵回表查詢會産生大量的随機IO，是以MySQL會将這些記錄放至緩沖區按照主鍵進行排序，緩沖區大小由read_rnd_buffer_size控制，最終通過排好序的主鍵進行回表掃描查詢，此為第二次掃描。

5、一般需要排序記錄較大超過max_length_for_sort_data設定值或者查詢select中包含BLOB或者TEXT類型字段的情況下會使用該算法進行排序。

5.2 一次掃描算法

一次掃描算法的基本處理流程：

1、排序時，将查詢的所有列（排序列以及非排序列）全部放入sort buffer進行排序，此為一次掃描；

2、若sort_buffer_size不夠大，需要将每次排好序的記錄固化到磁盤；

3、按照排好序的記錄直接輸出結果；

4、該算法下，隻需要掃描一次表資料，避免了回表查詢。隻有當需要排序的記錄小于max_length_for_sort_data定義參數大小時，MySQL才會優先使用一次掃描算法。

5.3 優先隊列排序

優先隊列排序也成為堆排序，主要是針對order by ... limit M,N的優化。雖然該排序算法需要掃描所有的記錄，但是對于sort_buffer_size來講該排序算法下僅僅需要M+N個元組的空間即可進行排序，避免了sort buffer不夠而導緻需要臨時檔案進行歸并排序的問題。對于升序，采用大頂堆，最終堆中的元素組成了最小的N個元素，對于降序，采用小頂堆，最終堆中的元素組成了最大的N的元素。

MySQL如何對order by優化？

一、利用索引排序

1.1 利用索引排序的特點

1.2、示例

1.3、有效利用複合索引進行排序查詢

1.4、無法使用索引排序的幾種情況

二、僅對驅動表排序

2.1、僅對驅動表進行排序的特點

2.2、示例

2.3、對于使用驅動表排序的優化

三、使用臨時表進行排序

3.1、使用臨時表表進行排序的特點

3.2、示例

3.3、對于使用臨時表排序的優化

四、三種排序方式對比

4.1 利用索引進行排序

4.2 filesort排序

4.3 關于sort_buffer_size

4.4 排序相關參數以及具體含義

五、三種排序掃描算法

5.1 兩次掃描算法

5.2 一次掃描算法

5.3 優先隊列排序

繼續閱讀

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

CSMA/CD1． CSMA/CD的概述2． CSMA 的工作原理3． CSMA/CD控制規程及特點4． CSMA/CD協定5． CSMA/CD的優點6．結束語

極大似然法(ML)與最大期望法(EM)

C++ 第十五周報告1--《冒泡法排序》

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

筆試面試題目：滑動視窗(二)

MySQL的4種隔離級别？出現問題

資料結構與算法（27）——排序（二）

neo4j之cypher使用文檔

Dijkstra--簡易版（最短路徑）

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離

hdu7108哈希

MySQL如何對order by優化？

一、 利用索引排序

1.1 利用索引排序的特點

1.2、示例

1.3、有效利用複合索引進行排序查詢

1.4、無法使用索引排序的幾種情況

二、僅對驅動表排序

2.1、僅對驅動表進行排序的特點

2.2、示例

2.3、對于使用驅動表排序的優化

三、使用臨時表進行排序

3.1、使用臨時表表進行排序的特點

3.2、示例

3.3、對于使用臨時表排序的優化

四、三種排序方式對比

4.1 利用索引進行排序

4.2 filesort排序

4.3 關于sort_buffer_size

4.4 排序相關參數以及具體含義

五、三種排序掃描算法

5.1 兩次掃描算法

5.2 一次掃描算法

5.3 優先隊列排序

繼續閱讀

一、利用索引排序