Mysql排序的特性詳情

更新時間：2022年05月25日 14:18:57 作者：程序新視界

這篇文章主要介紹Mysql排序的特性,新寫了一個功能,自測和測試環(huán)境測試都沒問題,但在生產(chǎn)環(huán)境會出現(xiàn)偶發(fā)問題。于是,加班到12點一直排查問題,終于定位了的問題原因:Mysql Limit查詢優(yōu)化導致?，F(xiàn)抽象出問題模型及解決方案,分析給大家，避免大家踩坑,需要的朋友可以參考一下

1、問題場景

新上線一個交易記錄導出功能，邏輯很簡單：根據(jù)查詢條件，導出對應的數(shù)據(jù)。由于數(shù)據(jù)量比較大，在查詢數(shù)據(jù)庫時采用了分頁查詢，每次查詢1000條數(shù)據(jù)。

自測正常，測試環(huán)境正常，上線之后運營反饋導出的數(shù)據(jù)有重復記錄。

原本是以為業(yè)務邏輯問題，重新Review了一遍代碼，依舊未找到問題原因。最后只好把SQL語句拿出來單獨執(zhí)行，導出數(shù)據(jù)，對比發(fā)現(xiàn)竟然是SQL語句查詢結果亂序?qū)е碌摹?/p>

2、原因分析

查詢語句以create_time進行倒序排序，通過limit進行分頁，在正常情況下不會出現(xiàn)問題。但當業(yè)務并發(fā)量比較大，導致create_time存在大量相同值時，再基于limit進行分頁，就會出現(xiàn)亂序問題。

出現(xiàn)的場景是：以create_time排序，當create_time存在相同值，通過limit分頁，導致分頁數(shù)據(jù)亂序。

比如，查詢1000條數(shù)據(jù)，其中有一批create_time記錄值都為”2021-10-28 12:12:12“，當創(chuàng)建時間相同的這些數(shù)據(jù)，一部分出現(xiàn)在第一頁，一部分出現(xiàn)在第二頁，在查詢第二頁的數(shù)據(jù)時，可能會出現(xiàn)第一頁已經(jīng)查過的數(shù)據(jù)。

也就是說，數(shù)據(jù)會來回跳動，一會兒出現(xiàn)在第一頁，一會兒出現(xiàn)在第二頁，這就導致導出的數(shù)據(jù)一部分重復，一部分缺失。

查看了Mysql 5.7和8.0的官方文檔，描述如下：

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

上述內(nèi)容概述：在使用ORDER BY對列進行排序時，如果對應（ORDER BY的列）列存在多行相同數(shù)據(jù)，（Mysql）服務器會按照任意順序返回這些行，并且可能會根據(jù)整體執(zhí)行計劃以不同的方式返回。

簡單來說就是：ORDER BY查詢的數(shù)據(jù)，如果ORDER BY列存在多行相同數(shù)據(jù)，Mysql會隨機返回。這就會導致雖然使用了排序，但也會發(fā)生亂序的狀況。

3、解決方案

針對上述問題，基本的解決思路是：避免ORDER BY列的值出現(xiàn)重復。因此，可以加入其他維度，比如ID等其他排序列。

select * from tb_order order by create_time ,id desc;

這樣，在create_time相同時，會根據(jù)id進行排序，而id肯定是不同的，就再不會出現(xiàn)上述問題了。

4、拓展知識

其實，上述內(nèi)容在Mysql的官網(wǎng)已經(jīng)有明確說明，而且還舉了例子。下面對官網(wǎng)的內(nèi)容和例子做一個簡單的匯總總結。

4.1 limit查詢優(yōu)化

如果我們只是查詢一個結果集的一部分，那么不要查詢所有數(shù)據(jù)，然后再丟棄不需要的數(shù)據(jù)，而是要通過limit條件來進行限制。

在沒使用having條件時，Mysql可能會對limit條件優(yōu)化：

如果只查詢幾條數(shù)據(jù)，建議使用limit，這樣Mysql可能會用到索引，而通常情況下Mysql是全表掃描；
如果將limit row_count和order by結合使用，Mysql會在找到第一個row_count結果集后立刻停止排序，而不是對整個結果集進行排序。如果此時基于索引進行操作，速度會更快。如果必須進行文件排序，在找到row_count結果集之前，會對部分或所有符合條件的結果進行排序。但當找到row_count結果之后，便不會對剩余部分進行排序了。這種特性的一個表現(xiàn)就是我們前面提到的帶有l(wèi)imit和不帶limit進行查詢時，返回的結果順序可能不同。
如果將limit row_count和distinct結合使用，Mysql會在找到row_count結果集唯一行后立馬停止。
在某些情況下，可以通過按照順序讀取索引（或?qū)λ饕M行排序），然后計算摘要直到索引變化來實現(xiàn)group by。在這種情況下，limit row_count不會計算任何不必要的group by值。
一旦MySQL向客戶端發(fā)送了所需數(shù)量的行，就會中止查詢，除非使用了SQL_CALC_FOUND_ROWS。在這種情況下，可以使用 SELECT FOUND_ROWS() 檢索行數(shù)。
LIMIT 0會快速返回一個空集合，通?？捎糜跈z查SQL的有效性。還可以用于在應用程序中獲得結果集的類型。在Mysql客戶端中，可以使用--column-type-info來顯示結果列類型。
如果使用臨時表來解析查詢，Mysql會使用 limit row_count來計算需要多少空間。
如果order by未使用索引，且存在limit條件，則優(yōu)化器可能會避免使用合并文件，而采用內(nèi)存filesort操作對內(nèi)存中的行進行排序。

了解了limit的一些特性，下面再回到本文的重點，limit row_count和order by結合使用特性。

4.2 limit與order by結合使用

在上面第二條中已經(jīng)提到，limit row_count和order by結合呈現(xiàn)的特性之一就是結果返回的順序是不確定的。而影響執(zhí)行計劃的一個因素就是limit，因此帶有limit與不帶有limit執(zhí)行同樣的查詢語句，返回結果的順序可能不同。

下面示例中，根據(jù)category列進行排序查詢，而id和rating是不確定的：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

當查詢語句包含limit時，可能會影響到category值相同的數(shù)據(jù)：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

其中id為3和4的結果位置發(fā)生了變化。

在實踐中，保持查詢結果的順序性往往非常重要，此時就需要引入其他列來保證結果的順序性了。

當上述實例引入id之后，查詢語句及結果如下：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+
?
mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

可以看出，當添加了id列的排序，即使category相同，也不會出現(xiàn)亂序問題。這正與我們最初的解決方案一致。