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

filesort

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

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

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

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

臨時(shí)文件存放再 tmpdir 變量指定的目錄下，排序完成后會(huì)自動(dòng)刪除。

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

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

1、準(zhǔn)備一個(gè)t_user表，首先開(kāi)啟optimizer_trace

mysql> SET optimizer_trace='enabled=on';

2、然后執(zhí)行查看explain這里再Extra會(huì)有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í)行查詢(xún)

select * from t_user order by username;

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

4、查看OPTIMIZER_TRACE

SELECT * FROM information_schema.OPTIMIZER_TRACE;

OPTIMIZER_TRACE一共有4列:

QUERY：表示當(dāng)前查詢(xún)語(yǔ)句

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

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

在trace內(nèi)容中有“filesort_summary”部分是關(guān)于filesort處理信息,如上面查詢(xún)對(duì)應(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)大，但是幾個(gè)主要的字段還是差不多的。這里以5.7的trace信息來(lái)看。

sort_buffer_size是sort_buffer的大小。

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

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

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

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

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

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

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

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

使用索引

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

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

1、查詢(xún)條件中有排序索引

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

3、多列索引排序，滿(mǎn)足最左匹配

4、排序方向一致

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

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

例1：

SELECT uid,phone FROM t_user ORDER BY phone;

查詢(xún)列都在索引上（uid是主鍵），Using index

例2：

SELECT * FROM t_user ORDER BY phone;

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

例3：

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

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

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

例4：

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

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

例5：

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

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

例6：

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

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

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

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

最新評(píng)論