就這么個(gè)破分頁(yè)，我踩了三次大坑！

之前踩到一個(gè)比較無(wú)語(yǔ)的生產(chǎn) BUG，嚴(yán)格來(lái)說(shuō)其實(shí)也不能算是 BUG，只能說(shuō)開發(fā)同事對(duì)于業(yè)務(wù)同事的需求理解沒(méi)有到位。

這個(gè) BUG 其實(shí)和分頁(yè)沒(méi)有任何關(guān)系，但是當(dāng)我去排查問(wèn)題的時(shí)候，我看了一眼 SQL ，大概是這樣的：

select * from table order by priority limit 1;

priority，就是優(yōu)先級(jí)的意思。

按照優(yōu)先級(jí) order by 然后 limit 取優(yōu)先級(jí)最高（數(shù)字越小，優(yōu)先級(jí)越高）的第一條 ，結(jié)合業(yè)務(wù)背景和數(shù)據(jù)庫(kù)里面的數(shù)據(jù)，我立馬就意識(shí)到了問(wèn)題所在。

想起了我當(dāng)年在寫分頁(yè)邏輯的時(shí)候，雖然場(chǎng)景和這個(gè)完全不一樣，但是踩過(guò)到底層原理一模一樣的坑，這玩意印象深刻，所以立馬就識(shí)別出來(lái)了。

借著這個(gè)問(wèn)題，也盤點(diǎn)一下遇到過(guò)的三個(gè)關(guān)于分頁(yè)查詢有意思的坑。

職業(yè)生涯的第一個(gè)生產(chǎn) BUG

職業(yè)生涯的第一個(gè)生產(chǎn) BUG 就是一個(gè)小小的分頁(yè)查詢。

當(dāng)時(shí)還在做支付系統(tǒng)，接手的一個(gè)需求也很簡(jiǎn)單就是做一個(gè)定時(shí)任務(wù)，定時(shí)把數(shù)據(jù)庫(kù)里面狀態(tài)為初始化的訂單查詢出來(lái)，調(diào)用另一個(gè)服務(wù)提供的接口查詢訂單的狀態(tài)并更新。

由于流程上有數(shù)據(jù)強(qiáng)校驗(yàn)，不用考慮數(shù)據(jù)不存在的情況。所以該接口可能返回的狀態(tài)只有三種：成功，失敗，處理中。

很簡(jiǎn)單，很常規(guī)的一個(gè)需求對(duì)吧，我分分鐘就能寫出偽代碼：

//獲取訂單狀態(tài)為初始化的數(shù)據(jù)(0:初始化1:處理中2:成功3:失敗)
//select*fromorderwhereorder_status=0;
ArrayListinitOrderInfoList=queryInitOrderInfoList();
//循環(huán)處理這批數(shù)據(jù)
for(OrderInfoorderInfo:initOrderInfoList){
//捕獲異常以免一條數(shù)據(jù)錯(cuò)誤導(dǎo)致循環(huán)結(jié)束
try{
//發(fā)起rpc調(diào)用
StringorderStatus=queryOrderStatus(orderInfo.getOrderId);
//更新訂單狀態(tài)
updateOrderInfo(orderInfo.getOrderId,orderStatus);
}catch(Exceptione){
//打印異常
}
}

來(lái)，你說(shuō)上面這個(gè)程序有什么問(wèn)題？

其實(shí)在絕大部分情況下都沒(méi)啥大問(wèn)題，數(shù)據(jù)量不多的情況下程序跑起來(lái)沒(méi)有任何毛病。

但是，如果數(shù)據(jù)量多起來(lái)了，一次性把所有初始化狀態(tài)的訂單都拿出來(lái)，是不是有點(diǎn)不合理了，萬(wàn)一把內(nèi)存給你撐爆了怎么辦？

所以，在我已知數(shù)據(jù)量會(huì)很大的情況下，我采取了分批次獲取數(shù)據(jù)的模式，假設(shè)一次性取 100 條數(shù)據(jù)出來(lái)玩。

那么 SQL 就是這樣的：

select * from order where order_status=0 order by create_time limit 100;

所以上面的偽代碼會(huì)變成這樣：

while(true){
//獲取訂單狀態(tài)為初始化的數(shù)據(jù)(0:初始化1:處理中2:成功3:失敗)
//select*fromorderwhereorder_status=0orderbycreate_timelimit100;
ArrayListinitOrderInfoList=queryInitOrderInfoList();
//循環(huán)處理這批數(shù)據(jù)
for(OrderInfoorderInfo:initOrderInfoList){
//捕獲異常以免一條數(shù)據(jù)錯(cuò)誤導(dǎo)致循環(huán)結(jié)束
try{
//發(fā)起rpc調(diào)用
StringorderStatus=queryOrderStatus(orderInfo.getOrderId);
//更新訂單狀態(tài)
updateOrderInfo(orderInfo.getOrderId,orderStatus);
}catch(Exceptione){
//打印異常
}
}
}

來(lái)，你又來(lái)告訴我上面這一段邏輯有什么問(wèn)題？

作為程序員，我們看到 while(true) 這樣的寫法立馬就要警報(bào)拉滿，看看有沒(méi)有死循環(huán)的風(fēng)險(xiǎn)。

那你說(shuō)上面這段代碼在什么時(shí)候退不出來(lái)？

當(dāng)有任何一條數(shù)據(jù)的狀態(tài)沒(méi)有從初始化變成成功、失敗或者處理中的時(shí)候，就會(huì)導(dǎo)致一直循環(huán)。

而雖然發(fā)起 RPC 調(diào)用的地方，服務(wù)提供方能確保返回的狀態(tài)一定是成功、失敗、處理中這三者之中的一個(gè)，但是這個(gè)有一個(gè)前提是接口調(diào)用正常的情況下。

如果接口調(diào)用一旦異常，那么按照上面的寫法，在拋出異常后，狀態(tài)并未發(fā)生變化，還會(huì)是停留在“初始化”，從而導(dǎo)致死循環(huán)。

當(dāng)年，測(cè)試同學(xué)在測(cè)試階段直接就測(cè)出了這個(gè)問(wèn)題，然后我對(duì)其進(jìn)行了修改。

我改變了思路，把每次分批次查詢 100 條數(shù)據(jù)，修改為了分頁(yè)查詢，引入了 PageHelper 插件：

//是否是最后一頁(yè)
while(pageInfo.isLastPage){
pageNum=pageNum+1;
//獲取訂單狀態(tài)為初始化的數(shù)據(jù)(0:初始化1:處理中2:成功3:失敗)
//select*fromorderwhereorder_status=0orderbycreate_timelimitpageNum*100,100;
PageHelper.startPage(pageNum,100);
ArrayListinitOrderInfoList=queryInitOrderInfoList();
pageInfo=newPageInfo(initOrderInfoList);
//循環(huán)處理這批數(shù)據(jù)
for(OrderInfoorderInfo:initOrderInfoList){
//捕獲異常以免一條數(shù)據(jù)錯(cuò)誤導(dǎo)致循環(huán)結(jié)束
try{
//發(fā)起rpc調(diào)用
StringorderStatus=queryOrderStatus(orderInfo.getOrderId);
//更新訂單狀態(tài)
updateOrderInfo(orderInfo.getOrderId,orderStatus);
}catch(Exceptione){
//打印異常
}
}
}

跳出循環(huán)的條件為判斷當(dāng)前頁(yè)是否是最后一頁(yè)。

由于每循環(huán)一次，當(dāng)前頁(yè)就加一，那么理論上講一定會(huì)是翻到最后一頁(yè)的，沒(méi)有任何毛病，對(duì)不對(duì)？

我們可以分析一下上面的代碼邏輯。

假設(shè)，我們有 120 條 order_status=0 的數(shù)據(jù)。

那么第一頁(yè)，取出了 100 條數(shù)據(jù)：

SELECT * from order_info WHERE order_status=0 LIMIT 0,100;

這 100 條處理完成之后，第二頁(yè)還有數(shù)據(jù)嗎？

第二頁(yè)對(duì)應(yīng)的 sql 為：

SELECT * from order_info WHERE order_status=0 LIMIT 100,100;

但是這個(gè)時(shí)候，狀態(tài)為 0 的數(shù)據(jù)，只有 20 條了，而分頁(yè)要從第 100 條開始，是不是獲取不到數(shù)據(jù)，導(dǎo)致遺漏數(shù)據(jù)了？

確實(shí)一定會(huì)翻到最后一頁(yè)，解決了死循環(huán)的問(wèn)題，但又有大量的數(shù)據(jù)遺漏怎么辦呢？

當(dāng)時(shí)我苦思冥想，想到一個(gè)辦法：導(dǎo)致數(shù)據(jù)遺漏的原因是因?yàn)槲以诜?yè)的時(shí)候，數(shù)據(jù)狀態(tài)在變化，導(dǎo)致總體數(shù)據(jù)在變化。

那么如果我每次都從后往前取數(shù)據(jù)，每次都固定取最后一頁(yè)，能取到數(shù)據(jù)就代表還有數(shù)據(jù)要處理，循環(huán)結(jié)束條件修改為“當(dāng)前頁(yè)即是第一頁(yè)，也是最后一頁(yè)時(shí)”就結(jié)束，這樣不就不會(huì)遺漏數(shù)據(jù)了？

我再給你分析一下。

假設(shè)，我們有 120 條 order_status=0 的數(shù)據(jù)，從后往前取了 100 天出來(lái)進(jìn)行出來(lái)，有 90 條處理成功，10 條的狀態(tài)還是停留在“處理中”。

第二次再取的時(shí)候，會(huì)把剩下的 20 條和這次“處理中”的 10 條，共計(jì) 30 條再次取出來(lái)進(jìn)行處理。

確保沒(méi)有數(shù)據(jù)遺漏。

后來(lái)測(cè)試環(huán)節(jié)驗(yàn)收通過(guò)了，這個(gè)方案上線之后，也確實(shí)沒(méi)有遺漏過(guò)數(shù)據(jù)了。

直到后來(lái)又一天，提供 queryOrderStatus 接口的服務(wù)異常了，我發(fā)過(guò)去的請(qǐng)求超時(shí)了。

導(dǎo)致我取出來(lái)的數(shù)據(jù)，每一條都會(huì)拋出異常，都不會(huì)更新?tīng)顟B(tài)。從而導(dǎo)致我每次從后往前取數(shù)據(jù)，都取到的是同一批數(shù)據(jù)。

從程序上的表現(xiàn)上看，日志瘋狂的打印，但是其實(shí)一直在處理同一批，就是死循環(huán)了。

好在我當(dāng)時(shí)還在新手保護(hù)期，領(lǐng)導(dǎo)幫我扛下來(lái)了。

最后隨著業(yè)務(wù)的發(fā)展，這塊邏輯也完全發(fā)生了變化，邏輯由我們主動(dòng)去調(diào)用 RPC 接口查詢狀態(tài)變成了，下游狀態(tài)變化后進(jìn)行 MQ 主動(dòng)通知，所以我這一坨騷代碼也就隨之光榮下崗。

我現(xiàn)在想了一下，其實(shí)這個(gè)場(chǎng)景，用分頁(yè)的思想去取數(shù)據(jù)真的不好做。

還不如用最開始的分批次的思想，只不過(guò)在會(huì)變化的“狀態(tài)”之外，再加上另外一個(gè)不會(huì)改變的限定條件，比如常見(jiàn)的創(chuàng)建時(shí)間：

select * from order where order_status=0 and create_time>xxx order by create_time limit 100;

最好不要基于狀態(tài)去做分頁(yè)，如果一定要基于狀態(tài)去做分頁(yè)，那么要確保狀態(tài)在分頁(yè)邏輯里面會(huì)扭轉(zhuǎn)下去。

這就是我職業(yè)生涯的第一個(gè)生產(chǎn) BUG，一個(gè)低級(jí)的分頁(yè)邏輯錯(cuò)誤。

還是分頁(yè)，又踩到坑

這也是在工作的前兩年遇到的一個(gè)關(guān)于分頁(yè)的坑。

最開始在學(xué)校的時(shí)候，大家肯定都手?jǐn)]過(guò)分頁(yè)邏輯，自己去算總頁(yè)數(shù)，當(dāng)前頁(yè)，頁(yè)面大小啥的。

當(dāng)時(shí)功力尚淺，覺(jué)得這部分邏輯寫起來(lái)是真復(fù)雜，但是扣扣腦袋也還是可以寫出來(lái)。

后來(lái)參加工作了之后，在項(xiàng)目里面看到了 PageHelper 這個(gè)玩意，了解之后發(fā)了“斯國(guó)一”的驚嘆：有了這玩意，誰(shuí)還手寫分頁(yè)啊。

但是我在使用 PageHelper 的時(shí)候，也踩到過(guò)一個(gè)經(jīng)典的“坑”。

最開始的時(shí)候，代碼是這樣的：

PageHelper.startPage(pageNum,100);
Listlist=orderInfoMapper.select(param1);

后來(lái)為了避免不帶 where 條件的全表查詢，我把代碼修改成了這樣：

PageHelper.startPage(pageNum,100);
if(param!=null){
Listlist=orderInfoMapper.select(param);
}

然后，隨著程序的迭代，就出 BUG 了。因?yàn)橛械臉I(yè)務(wù)場(chǎng)景下，param 參數(shù)一路傳遞進(jìn)來(lái)之后就變成了 null。

但是這個(gè)時(shí)候 PageHelper 已經(jīng)在當(dāng)前線程的 ThreadLocal 里面設(shè)置了分頁(yè)參數(shù)了，但是沒(méi)有被消費(fèi)，這個(gè)參數(shù)就會(huì)一直保留在這個(gè)線程上，也就是放在線程的 ThreadLocal 里面。

當(dāng)這個(gè)線程繼續(xù)往后跑，或者被復(fù)用的時(shí)候，遇到一條 SQL 語(yǔ)句時(shí)，就可能導(dǎo)致不該分頁(yè)的方法去消費(fèi)這個(gè)分頁(yè)參數(shù)，產(chǎn)生了莫名其妙的分頁(yè)。

所以，上面這個(gè)代碼，應(yīng)該寫成下面這個(gè)樣子：

if(param!=null){
PageHelper.startPage(pageNum,100);
Listlist=orderInfoMapper.select(param);
}

也是這次踩坑之后，我翻閱了 PageHelper 的源碼，了解了底層原理，并總結(jié)了一句話：需要保證在 PageHelper 方法調(diào)用后緊跟 MyBatis 查詢方法，否則會(huì)污染線程。

在正確使用 PageHelper 的情況下，其插件內(nèi)部，會(huì)在 finally 代碼段中自動(dòng)清除了在 ThreadLocal 中存儲(chǔ)的對(duì)象。

這樣就不會(huì)留坑。

這次翻頁(yè)源碼的過(guò)程影響也是比較深刻的，雖然那個(gè)時(shí)候經(jīng)驗(yàn)不多，但是得益于 MyBatis 的源碼和 PageHelper 的源碼寫的都非常的符合正常人的思維，閱讀起來(lái)門檻不高，再加上我有具體的疑問(wèn)，所以那是一次古早時(shí)期，尚在新手村時(shí)，為數(shù)不多的，閱讀源碼之后，感覺(jué)收獲滿滿的經(jīng)歷。

分頁(yè)丟數(shù)據(jù)

關(guān)于這個(gè) BUG 可以說(shuō)是印象深刻了。

當(dāng)年遇到這個(gè)坑的時(shí)候排查了很長(zhǎng)時(shí)間沒(méi)啥頭緒，最后還是組里的大佬指了條路。

業(yè)務(wù)需求很簡(jiǎn)單，就是在管理頁(yè)面上可以查詢訂單列表，查詢結(jié)果按照訂單的創(chuàng)建時(shí)間倒序排序。

對(duì)應(yīng)的分頁(yè) SQL 很簡(jiǎn)單，很常規(guī)，沒(méi)有任何問(wèn)題：

select * from table order by create_time desc limit 0,10;

但是當(dāng)年在頁(yè)面上的表現(xiàn)大概是這樣的：

訂單編號(hào)為 5 的這條數(shù)據(jù)，會(huì)同時(shí)出現(xiàn)在了第一頁(yè)和第二頁(yè)。

甚至有的數(shù)據(jù)在第二頁(yè)出現(xiàn)了之后，在第五頁(yè)又出現(xiàn)一次。

后來(lái)定位到產(chǎn)生這個(gè)問(wèn)題的原因是因?yàn)橛幸慌鷶?shù)量不小的訂單數(shù)據(jù)是通過(guò)線下執(zhí)行 SQL 的方式導(dǎo)入的。

而導(dǎo)入的這一批數(shù)據(jù)，寫 SQL 的同學(xué)為了方便，就把 create_time 都設(shè)置為了同一個(gè)值，比如都設(shè)置為了 2023-09-10 1256 這個(gè)時(shí)間。

由于 create_time 又是我作為 order by 的字段，當(dāng)這個(gè)字段的值大量都是同一個(gè)值的時(shí)候，就會(huì)導(dǎo)致上面的一條數(shù)據(jù)在不同的頁(yè)面上多次出現(xiàn)的情況。

針對(duì)這個(gè)現(xiàn)象，當(dāng)時(shí)組里的大佬分析明白之后，扔給我一個(gè)鏈接：

https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

這是 MySQL 官方文檔，這一章節(jié)叫做“對(duì) Limit 查詢的優(yōu)化”。

開篇的時(shí)候人家就是這樣說(shuō)的：

如果將 LIMIT row_count 和 ORDER BY 組合在一起，那么 MySQL 在找到排序結(jié)果的第一行 count 行時(shí)就停止排序，而不是對(duì)整個(gè)結(jié)果進(jìn)行排序。

然后給了這一段補(bǔ)充說(shuō)明：

如果多條記錄的 ORDER BY 列中有相同的值，服務(wù)器可以自由地按任何順序返回這些記錄，并可能根據(jù)整體執(zhí)行計(jì)劃的不同而采取不同的方式。

換句話說(shuō)，相對(duì)于未排序列，這些記錄的排序順序是 nondeterministic 的：

然后官方給了一個(gè)示例。

首先，不帶 limit 的時(shí)候查詢結(jié)果是這樣的：

基于這個(gè)結(jié)果，如果我要取前五條數(shù)據(jù)，對(duì)應(yīng)的 id 應(yīng)該是 1,5,3,4,6。

但是當(dāng)我們帶著 limit 的時(shí)候查詢結(jié)果可能是這樣的：

對(duì)應(yīng)的 id 實(shí)際是 1,5,4,3,6。

這就是前面說(shuō)的：如果多條記錄的 ORDER BY 列中有相同的值，服務(wù)器可以自由地按任何順序返回這些記錄，并可能根據(jù)整體執(zhí)行計(jì)劃的不同而采取不同的方式。

從程序上的表現(xiàn)上來(lái)看，結(jié)果就是 nondeterministic。

所以看到這里，我們大概可以知道我前面遇到的分頁(yè)問(wèn)題的原因是因?yàn)槟且慌謩?dòng)插入的數(shù)據(jù)對(duì)應(yīng)的 create_time 字段都是一樣的，而 MySQL 這邊又對(duì) Limit 參數(shù)做了優(yōu)化，運(yùn)行結(jié)果出現(xiàn)了不確定性，從而頁(yè)面上出現(xiàn)了重復(fù)的數(shù)據(jù)。

而回到文章最開始的這個(gè) SQL，也就是我一眼看出問(wèn)題的這個(gè) SQL：

select * from table order by priority limit 1;

因?yàn)樵谖覀兊慕缑嫔?，只是約定了數(shù)字越小優(yōu)先級(jí)越高，數(shù)字必須大于 0。

所以當(dāng)大家在輸入優(yōu)先級(jí)的時(shí)候，大部分情況下都默認(rèn)自己編輯的數(shù)據(jù)對(duì)應(yīng)的優(yōu)先級(jí)最高，也就是設(shè)置為 1，從而導(dǎo)致數(shù)據(jù)庫(kù)里面有大量的優(yōu)先級(jí)為 1 的數(shù)據(jù)。

而程序每次處理，又只會(huì)按照優(yōu)先級(jí)排序只會(huì)，取一條數(shù)據(jù)出來(lái)進(jìn)行處理。

經(jīng)過(guò)前面的分析我們可以知道，這樣取出來(lái)的數(shù)據(jù)，不一定每次都一樣。

所以由于有這段代碼的存在，導(dǎo)致業(yè)務(wù)上的表現(xiàn)就很奇怪，明明是一模一樣的請(qǐng)求參數(shù)，但是最終返回的結(jié)果可能不相同。

好，現(xiàn)在，我問(wèn)你，你說(shuō)在前面，我給出的這樣的分頁(yè)查詢的 SQL 語(yǔ)句有沒(méi)有毛??？

select * from table order by create_time desc limit 0,10;

沒(méi)有任何毛病嘛，執(zhí)行結(jié)果也沒(méi)有任何毛??？

有沒(méi)有給你按照 create_time 排序？

摸著良心說(shuō)，是有的。

有沒(méi)有給你取出排序后的 10 條數(shù)據(jù)？

也是有的。

所以，針對(duì)這種現(xiàn)象，官方的態(tài)度是：我沒(méi)錯(cuò)！在我的概念里面，沒(méi)有“分頁(yè)”這樣的玩意，你通過(guò)組合我提供的功能，搞出了“分頁(yè)”這種業(yè)務(wù)場(chǎng)景，現(xiàn)在業(yè)務(wù)場(chǎng)景出問(wèn)題了，你反過(guò)來(lái)說(shuō)我底層有問(wèn)題？

這不是欺負(fù)老實(shí)人嗎？我沒(méi)錯(cuò)！

所以，官方把這兩種案例都拿出來(lái)，并且強(qiáng)調(diào)：

在每種情況下，查詢結(jié)果都是按 ORDER BY 的列進(jìn)行排序的，這樣的結(jié)果是符合 SQL 標(biāo)準(zhǔn)的。

雖然我沒(méi)錯(cuò)，但是我還是可以給你指?jìng)€(gè)路。

如果你非常在意執(zhí)行結(jié)果的順序，那么在 ORDER BY 子句中包含一個(gè)額外的列，以確保順序具有確定性。

例如，如果 id 值是唯一的，你可以通過(guò)這樣的排序使給定類別值的行按 id 順序出現(xiàn)。

你這樣去寫，排序的時(shí)候加個(gè) id 字段，就穩(wěn)了：

好了，本文的技術(shù)部分就到這里啦。