不知道MySQL排序的特性，加班到12點，認了認了！
本文轉載自微信公眾號「程序新視界」，作者二師兄 。轉載本文請聯系程序新視界公眾號。
小弟新寫了一個功能，自測和測試環境測試都沒問題，但在生產環境會出現偶發問題。于是，加班到12點一直排查問題，終于定位了的問題原因：Mysql Limit查詢優化導致。現抽象出問題模型及解決方案，分析給大家，避免大家踩坑。
問題場景
新上線一個交易記錄導出功能，邏輯很簡單：根據查詢條件，導出對應的數據。由于數據量比較大，在查詢數據庫時采用了分頁查詢，每次查詢1000條數據。
自測正常，測試環境正常，上線之后運營反饋導出的數據有重復記錄。
原本是以為業務邏輯問題，重新Review了一遍代碼，依舊未找到問題原因。最后只好把SQL語句拿出來單獨執行，導出數據，對比發現竟然是SQL語句查詢結果亂序導致的。
原因分析
查詢語句以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會隨機返回。這就會導致雖然使用了排序，但也會發生亂序的狀況。
解決方案
針對上述問題，基本的解決思路是：避免ORDER BY列的值出現重復。因此，可以加入其他維度，比如ID等其他排序列。
- select * from tb_order order by create_time ,id desc;
這樣，在create_time相同時，會根據id進行排序，而id肯定是不同的，就再不會出現上述問題了。
拓展知識
其實，上述內容在Mysql的官網已經有明確說明，而且還舉了例子。下面對官網的內容和例子做一個簡單的匯總總結。
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結合使用特性。
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相同，也不會出現亂序問題。這正與我們最初的解決方案一致。
小結
本來通過實踐中偶發的一個坑，聊到了Mysql對limit查詢語句的優化，同時提供了解決方案，即滿足了業務需求，又避免了業務邏輯的錯誤。
很多朋友都在使用order by和limit語句進行查詢，但如果不知道Mysql的這些優化特性，很可能已經入坑，只不過數據量沒有觸發呈現而已。
如果這篇文章幫到你了，關注一波，后續更多實戰干貨分享。
Mysql官方文檔：https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html
