sql語句按照指定的字段進(jìn)行排序是查詢數(shù)據(jù)時是一個很常見的操作。當(dāng)涉及到大量數(shù)據(jù)時，對于 ORDER BY 操作，可以考慮為相應(yīng)的列添加索引，如果不使用索引，mysql會使用filesort來進(jìn)行排序。

filesort

filesort雖然有file，但是不一定是文件排序，要分情況。下面來看下排序的具體邏輯。

filesort會將查詢行數(shù)據(jù)放入sort_buffer中，然后按排序字段進(jìn)行排序。sort_buffer的大小有變量sort_buffer_size來控制，默認(rèn)大小256kb。

mysql> SELECT @@sort_buffer_size;
+--------------------+
| @@sort_buffer_size |
+--------------------+
|             262144 |

如果要排序的數(shù)據(jù)內(nèi)容小于sort_buffer_size，排序在內(nèi)存中即可完成；否則filesort會使用臨時文件進(jìn)行排序。數(shù)據(jù)越多生成的臨時文件越多，每份文件單獨(dú)排序后再歸并合并成一個有序的結(jié)果。

臨時文件存放再 tmpdir 變量指定的目錄下，排序完成后會自動刪除。

mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp     |

如果使用了filesort，在explain的Extra會顯示：Using filesort。是否使用了臨時文件還需要根據(jù)具體的執(zhí)行過程來判斷。下面通過information_schema.OPTIMIZER_TRACE表來查看是否使用臨時文件。

1、準(zhǔn)備一個t_user表，首先開啟optimizer_trace

mysql> SET optimizer_trace='enabled=on';

2、然后執(zhí)行查看explain這里再Extra會有Using filesort。

mysql> explain select * from t_user order by username;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2700 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

3、執(zhí)行查詢

select * from t_user order by username;

注意這里是執(zhí)行查詢，不是explain，否則下面一步看不到信息

4、查看OPTIMIZER_TRACE

SELECT * FROM information_schema.OPTIMIZER_TRACE;

OPTIMIZER_TRACE一共有4列:

QUERY：表示當(dāng)前查詢語句

TRACE: 包含查詢優(yōu)化器的trace信息，json格式

MISSING_BYTES_BEYOND_MAXMEM:丟失字節(jié)數(shù)。

在trace內(nèi)容中有“filesort_summary”部分是關(guān)于filesort處理信息,如上面查詢對應(yīng)的filesort_summary

mysql8：

"filesort_summary": {
  "memory_available": 262144,
  "key_size": 481,
  "row_size": 6591,
  "max_rows_per_buffer": 39,
  "num_rows_estimate": 2700,
  "num_rows_found": 2800,
  "num_initial_chunks_spilled_to_disk": 8,
  "peak_memory_used": 264192,
  "sort_algorithm": "std::sort",
  "sort_mode": "<fixed_sort_key, packed_additional_fields>"
}

mysql5.7：

"filesort_summary": {
  "rows": 2800,
  "examined_rows": 2800,
  "number_of_tmp_files": 6,
  "sort_buffer_size": 261784,
  "sort_mode": "<sort_key, rowid>"
}

這里看到5.7和8差別還是有點(diǎn)大，但是幾個主要的字段還是差不多的。這里以5.7的trace信息來看。

sort_buffer_size是sort_buffer的大小。

rows：數(shù)據(jù)行數(shù)

number_of_tmp_files：臨時文件數(shù)，如果該值為0，則表示未使用臨時文件，sort_buffer夠用。

sort_mode：排序方式。這個指定了參與排序的數(shù)據(jù)內(nèi)容不同。

sort_key, rowid：sort_buffer中加載的數(shù)據(jù)只有排序字段（sort_key）和rowid，rowid用來排序后再回表查詢獲取行數(shù)據(jù)。

sort_key, additional_fields：sort_buffer中加載數(shù)據(jù)包含所有的要查詢的字段。

sort_key, packed_additional_fields：和上面的additional_fields差不多，只是有些可變長度會進(jìn)行壓縮。

那么sort_mode這兩種類型一種有所有查詢字段，一種只有排序字段，查詢優(yōu)化器是根據(jù)什么選擇的呢。這里有一個參數(shù)max_length_for_sort_data，如果查詢的數(shù)據(jù)行記錄超過該值，則會采用sort_key, rowid模式，否則會采用sort_key, additional_fields模式。不過這個值在MySQL 8.0.20被標(biāo)為過時了，建議通過調(diào)整sort_buffer_size大小來控制join_buffer大小，盡量避免使用磁盤臨時文件。這兩種排序模式比較：additional_fields模式不需要回表，如果查詢的列比較多，可能會導(dǎo)致sort_buffer所能容納的行數(shù)據(jù)變少；rowid模式每行數(shù)據(jù)很小，sort_buffer可以加載更多的行，但是最后返回?cái)?shù)據(jù)要回表。不同數(shù)據(jù)量場景，要合理設(shè)置sort_buffer_size和max_length_for_sort_data搭配。

這里看到排序的過程，一行行拿出來進(jìn)行比較，數(shù)據(jù)量大還會使用到臨時文件，還是比較耗時的。那么有沒有更快的方法呢？那就是使用索引。

使用索引

為什么使用索引會快？因?yàn)闃?gòu)建后的索引就是天然有序的，不需要再經(jīng)過一行行逐一對sort_key進(jìn)行比較。跳過額外的filesort。

排序使用索引的情況一般在Extra中只有Using index。使用索引一般場景：

1、查詢條件中有排序索引

2、索引覆蓋，查詢的列都在對應(yīng)的索引中

3、多列索引排序，滿足最左匹配

4、排序方向一致

這只是一般的規(guī)則，下面結(jié)合幾個具體的例子來看看。

t_user表上有復(fù)合索引 (username,gender,department) 還有索引(phone)

例1：

SELECT uid,phone FROM t_user ORDER BY phone;

查詢列都在索引上（uid是主鍵），Using index

例2：

SELECT * FROM t_user ORDER BY phone;

索引不包含查詢訪問的所有列，則僅當(dāng)索引訪問比其他訪問方法更高效時才使用該索引.這里查詢優(yōu)化器選擇了Using filesort。 不太理解？

例3：

SELECT uid,phone FROM t_user WHERE uid<20 ORDER BY phone;

雖然查詢內(nèi)容是索引覆蓋，但是where條件不在索引上（不是同一個索引），Using filesort。

這個應(yīng)該可以理解一個索引篩選出來的數(shù)據(jù)內(nèi)容對另一個索引是無序的。

例4：

SELECT username,gender FROM t_user  ORDER BY username,gender;
SELECT username,gender FROM t_user WHERE username='a' ORDER BY gender;

查詢內(nèi)容索引覆蓋，where滿足索引最左匹配部分，Using index。

例5：

SELECT username,gender FROM t_user WHERE username LIKE 'a%'  ORDER BY username desc,gender ;

多列排序，第一列排序username滿足使用索引條件，gender和username排序方向相反，無法使用索引，會進(jìn)行依次filesort。

例6：

SELECT username,gender FROM t_user WHERE username LIKE 'a%'  ORDER BY username,department ;

username排序使用filesort，department排序不滿足最左匹配，中間復(fù)合索引斷開（缺少gender）使用filesort。

這里只看了幾個常見例子，可能有些場景比這復(fù)雜的多。想一想能使用到索引排序一個最基本的條件：當(dāng)前要查詢的數(shù)據(jù)范圍（where篩選后）在排序列（對應(yīng)的索引）是有序的。然后才考慮覆蓋索引等其它需要滿足的條件。

到此這篇關(guān)于mysql order by 排序原理的文章就介紹到這了,更多相關(guān)mysql order by 排序內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論