本文轉(zhuǎn)載自微信公眾號(hào)「程序新視界」，作者二師兄  。轉(zhuǎn)載本文請(qǐng)聯(lián)系程序新視界公眾號(hào)。

小弟新寫了一個(gè)功能，自測(cè)和測(cè)試環(huán)境測(cè)試都沒(méi)問(wèn)題，但在生產(chǎn)環(huán)境會(huì)出現(xiàn)偶發(fā)問(wèn)題。于是，加班到12點(diǎn)一直排查問(wèn)題，終于定位了的問(wèn)題原因：Mysql Limit查詢優(yōu)化導(dǎo)致?，F(xiàn)抽象出問(wèn)題模型及解決方案，分析給大家，避免大家踩坑。

問(wèn)題場(chǎng)景

新上線一個(gè)交易記錄導(dǎo)出功能，邏輯很簡(jiǎn)單：根據(jù)查詢條件，導(dǎo)出對(duì)應(yīng)的數(shù)據(jù)。由于數(shù)據(jù)量比較大，在查詢數(shù)據(jù)庫(kù)時(shí)采用了分頁(yè)查詢，每次查詢1000條數(shù)據(jù)。

自測(cè)正常，測(cè)試環(huán)境正常，上線之后運(yùn)營(yíng)反饋導(dǎo)出的數(shù)據(jù)有重復(fù)記錄。

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

原因分析

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

出現(xiàn)的場(chǎng)景是：以create_time排序，當(dāng)create_time存在相同值，通過(guò)limit分頁(yè)，導(dǎo)致分頁(yè)數(shù)據(jù)亂序。

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

也就是說(shuō)，數(shù)據(jù)會(huì)來(lái)回跳動(dòng)，一會(huì)兒出現(xiàn)在第一頁(yè)，一會(huì)兒出現(xiàn)在第二頁(yè)，這就導(dǎo)致導(dǎo)出的數(shù)據(jù)一部分重復(fù)，一部分缺失。

查看了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對(duì)列進(jìn)行排序時(shí)，如果對(duì)應(yīng)(ORDER BY的列)列存在多行相同數(shù)據(jù)，(Mysql)服務(wù)器會(huì)按照任意順序返回這些行，并且可能會(huì)根據(jù)整體執(zhí)行計(jì)劃以不同的方式返回。

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

解決方案

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

select * from tb_order order by create_time ,id desc; 

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

拓展知識(shí)

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

limit查詢優(yōu)化

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

了解了limit的一些特性，下面再回到本文的重點(diǎn)，limit row_count和order by結(jié)合使用特性。

limit與order by結(jié)合使用

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

下面示例中，根據(jù)category列進(jìn)行排序查詢，而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 | 
+----+----------+--------+ 

當(dāng)查詢語(yǔ)句包含limit時(shí)，可能會(huì)影響到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的結(jié)果位置發(fā)生了變化。

在實(shí)踐中，保持查詢結(jié)果的順序性往往非常重要，此時(shí)就需要引入其他列來(lái)保證結(jié)果的順序性了。當(dāng)上述實(shí)例引入id之后，查詢語(yǔ)句及結(jié)果如下：

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 | 
+----+----------+--------+ 

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

小結(jié)

本來(lái)通過(guò)實(shí)踐中偶發(fā)的一個(gè)坑，聊到了Mysql對(duì)limit查詢語(yǔ)句的優(yōu)化，同時(shí)提供了解決方案，即滿足了業(yè)務(wù)需求，又避免了業(yè)務(wù)邏輯的錯(cuò)誤。

很多朋友都在使用order by和limit語(yǔ)句進(jìn)行查詢，但如果不知道Mysql的這些優(yōu)化特性，很可能已經(jīng)入坑，只不過(guò)數(shù)據(jù)量沒(méi)有觸發(fā)呈現(xiàn)而已。

如果這篇文章幫到你了，關(guān)注一波，后續(xù)更多實(shí)戰(zhàn)干貨分享。

Mysql官方文檔：https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html