Mysql排序的特性詳情
1、問題場景
新上線一個交易記錄導出功能，邏輯很簡單：根據查詢條件，導出對應的數據。由於數據量比較大，在查詢數據庫時采用瞭分頁查詢，每次查詢1000條數據。
自測正常，測試環境正常，上線之後運營反饋導出的數據有重復記錄。
原本是以為業務邏輯問題，重新Review瞭一遍代碼，依舊未找到問題原因。最後隻好把SQL語句拿出來單獨執行，導出數據，對比發現竟然是SQL語句查詢結果亂序導致的。
2、原因分析
查詢語句以create_time進行倒序排序，通過limit進行分頁，在正常情況下不會出現問題。但當業務並發量比較大，導致create_time存在大量相同值時，再基於limit進行分頁，就會出現亂序問題。
出現的場景是：以create_time排序，當create_time存在相同值，通過limit分頁，導致分頁數據亂序。
比如，查詢1000條數據，其中有一批create_time記錄值都為”2021-10-28 12:12:12“，當創建時間相同的這些數據，一部分出現在第一頁，一部分出現在第二頁，在查詢第二頁的數據時，可能會出現第一頁已經查過的數據。
也就是說，數據會來回跳動，一會兒出現在第一頁，一會兒出現在第二頁，這就導致導出的數據一部分重復，一部分缺失。
查看瞭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.
上述內容概述：在使用ORDER BY對列進行排序時，如果對應（ORDER BY的列）列存在多行相同數據，（Mysql）服務器會按照任意順序返回這些行，並且可能會根據整體執行計劃以不同的方式返回。
簡單來說就是：ORDER BY查詢的數據，如果ORDER BY列存在多行相同數據，Mysql會隨機返回。這就會導致雖然使用瞭排序，但也會發生亂序的狀況。
3、解決方案
針對上述問題，基本的解決思路是：避免ORDER BY列的值出現重復。因此，可以加入其他維度，比如ID等其他排序列。
select * from tb_order order by create_time ,id desc;
這樣，在create_time相同時，會根據id進行排序，而id肯定是不同的，就再不會出現上述問題瞭。
4、拓展知識
其實，上述內容在Mysql的官網已經有明確說明，而且還舉瞭例子。下面對官網的內容和例子做一個簡單的匯總總結。
4.1 limit查詢優化
如果我們隻是查詢一個結果集的一部分，那麼不要查詢所有數據，然後再丟棄不需要的數據，而是要通過limit條件來進行限制。
在沒使用having條件時，Mysql可能會對limit條件優化：
- 如果隻查詢幾條數據，建議使用
limit，這樣Mysql可能會用到索引，而通常情況下Mysql是全表掃描； - 如果將
limit row_count和order by結合使用，Mysql會在找到第一個row_count結果集後立刻停止排序，而不是對整個結果集進行排序。如果此時基於索引進行操作，速度會更快。如果必須進行文件排序，在找到row_count結果集之前，會對部分或所有符合條件的結果進行排序。但當找到row_count結果之後，便不會對剩餘部分進行排序瞭。這種特性的一個表現就是我們前面提到的帶有limit和不帶limit進行查詢時，返回的結果順序可能不同。 - 如果將
limit row_count和distinct結合使用，Mysql會在找到row_count結果集唯一行後立馬停止。 - 在某些情況下，可以通過按照順序讀取索引（或對索引進行排序），然後計算摘要直到索引變化來實現group by。在這種情況下，
limit row_count不會計算任何不必要的group by值。 - 一旦MySQL向客戶端發送瞭所需數量的行，就會中止查詢，除非使用瞭
SQL_CALC_FOUND_ROWS。在這種情況下，可以使用SELECT FOUND_ROWS()檢索行數。 - LIMIT 0會快速返回一個空集合，通常可用於檢查SQL的有效性。還可以用於在應用程序中獲得結果集的類型。在Mysql客戶端中，可以使用
--column-type-info來顯示結果列類型。 - 如果使用臨時表來解析查詢，
Mysql會使用limit row_count來計算需要多少空間。 - 如果
order by未使用索引，且存在limit條件，則優化器可能會避免使用合並文件，而采用內存filesort操作對內存中的行進行排序。
瞭解瞭limit的一些特性，下面再回到本文的重點，limit row_count和order by結合使用特性。
4.2 limit與order by結合使用
在上面第二條中已經提到，limit row_count和order by結合呈現的特性之一就是結果返回的順序是不確定的。而影響執行計劃的一個因素就是limit，因此帶有limit與不帶有limit執行同樣的查詢語句，返回結果的順序可能不同。
下面示例中，根據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值相同的數據：
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的結果位置發生瞭變化。
在實踐中，保持查詢結果的順序性往往非常重要，此時就需要引入其他列來保證結果的順序性瞭。
當上述實例引入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相同，也不會出現亂序問題。這正與我們最初的解決方案一致。
5、小結
本來通過實踐中偶發的一個坑，聊到瞭Mysql對limit查詢語句的優化，同時提供瞭解決方案，即滿足瞭業務需求，又避免瞭業務邏輯的錯誤。
很多朋友都在使用order by和limit語句進行查詢，但如果不知道Mysql的這些優化特性，很可能已經入坑，隻不過數據量沒有觸發呈現而已。
到此這篇關於Mysql排序的特性詳情的文章就介紹到這瞭,更多相關Mysql排序特性內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！