如果是 app 端的查詢，不建議設(shè)計多字段排序，因為在多字段排序的環(huán)境下，服務(wù)端在進行多條件的過濾查詢時，可能會把有效的數(shù)據(jù)給過濾掉，如果無法避開，盡量將多個排序字段合并到一個排序字段上，保證數(shù)據(jù)的查詢符合預(yù)期。
?一、問題背景
最近部分銷售人員反饋在 APP 上查詢自己名下客戶訂單數(shù)據(jù)時，當往下拉取數(shù)據(jù)的時候，列表上經(jīng)常出現(xiàn)重復(fù)的訂單數(shù)據(jù)，經(jīng)過排查，后端代碼是通過如下方式來實現(xiàn)數(shù)據(jù)的分頁查詢的。
limit offset, size order by create_time desc
經(jīng)過細致的分析，這種排序方式，在 app 端分頁查詢的時候，確實存在問題。
詳細的分析過程如下！
二、原因分析
首先我們初始化一張表，用于模擬訂單表查詢。
CREATE TABLE `tb_order` (
`order_id` bigint(11) unsigned NOT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
然后初始化 5 條數(shù)據(jù)進去，方便數(shù)據(jù)分析
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (1, '2023-03-03 12:00:01');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (2, '2023-03-03 12:00:02');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (3, '2023-03-03 12:00:03');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (4, '2023-03-03 12:00:04');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (5, '2023-03-03 12:00:05');
假設(shè)我們每次只查詢 2 條數(shù)據(jù)，并且按照時間倒序來查詢，結(jié)果如下：
-- 發(fā)起第一頁查詢
select * from tb_order order by create_time desc limit 0,2;
-- 第一頁查詢結(jié)果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|
-- 發(fā)起第二頁查詢
select * from tb_order order by create_time desc limit 2,2;
-- 第二頁查詢結(jié)果
|order_id | create_time |
|3 | 2023-03-03 12:00:03|
|2 | 2023-03-03 12:00:02|
當訂單數(shù)據(jù)沒有發(fā)生變動的時候，這種查詢方式是不會造成出現(xiàn)重復(fù)的數(shù)據(jù)問題。
但是當訂單數(shù)據(jù)發(fā)生了變動，比如在查詢的時候，突然新增了訂單數(shù)據(jù)，此時的查詢結(jié)果就完全不一樣了。
還是以上面為例，假設(shè)在第一次查詢的時候，突然新增了一條數(shù)據(jù)，看看結(jié)果如何。
-- 發(fā)起第一頁查詢
select * from tb_order order by create_time desc limit 0,2;
-- 第一頁查詢結(jié)果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|
-- 新增一條訂單數(shù)據(jù)
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (6, '2023-03-03 12:00:06');
-- 發(fā)起第二頁查詢
select * from tb_order order by create_time desc limit 2,2;
-- 第二頁查詢結(jié)果
|order_id | create_time |
|4 | 2023-03-03 12:00:04|
|3 | 2023-03-03 12:00:03|
可以很明顯的發(fā)現(xiàn)，訂單ID=4的數(shù)據(jù)，出現(xiàn)在頁面上兩次，正常情況下只有一次！
上面說到的是新增一條數(shù)據(jù)，假設(shè)刪除某條數(shù)據(jù)，看看結(jié)果如何。
-- 發(fā)起第一頁查詢
select * from tb_order order by create_time desc limit 0,2;
-- 第一頁查詢結(jié)果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|
-- 刪除一條訂單數(shù)據(jù)
delete from tb_order where order_id = 4;
-- 發(fā)起第二頁查詢
select * from tb_order order by create_time desc limit 2,2;
-- 第二頁查詢結(jié)果
|order_id | create_time |
|2 | 2023-03-03 12:00:02|
|1 | 2023-03-03 12:00:01|
可以很明顯的發(fā)現(xiàn)，刪除訂單ID=4的數(shù)據(jù)之后，頁面查詢結(jié)果直接到訂單ID=2了，直接跳過訂單ID=3了，也就是說訂單ID=3的數(shù)據(jù)展示，丟失了！
總結(jié)下來，結(jié)論如下！
- 當新增某條數(shù)據(jù)之后，通過常規(guī)的分頁查詢，列表會出現(xiàn)數(shù)據(jù)重復(fù)的現(xiàn)象；
- 當刪除某條數(shù)據(jù)之后，通過常規(guī)的分頁查詢，列表會出現(xiàn)數(shù)據(jù)丟失的現(xiàn)象；
那怎么解決以上的問題呢？辦法如下！
三、解決方案
針對上面所說的分頁查詢方式，我們需要做一些調(diào)整，調(diào)整辦法如下：
- 第一步：當查詢出當頁的數(shù)據(jù)之后，記錄下本次拉取的最后一條數(shù)據(jù)的排序字段值；當發(fā)起下一頁數(shù)據(jù)查詢的時候，帶上這個參數(shù)，服務(wù)端通過這個參數(shù)做過濾條件
- 第二步：排序字段值不能出現(xiàn)重復(fù)
以上面的新增為例，詳細的實踐過程如下：
-- 發(fā)起第一頁查詢
select * from tb_order order by create_time desc limit 0,2;
-- 第一頁查詢結(jié)果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|
-- 新增一條訂單數(shù)據(jù)
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (6, '2023-03-03 12:00:06');
-- 發(fā)起第二頁查詢，帶上第一頁查詢的最后一條數(shù)據(jù)的排序字段值
select * from tb_order where create_time < '2023-03-03 12:00:04' order by create_time desc limit 0,2;
-- 第二頁查詢結(jié)果
|order_id | create_time |
|3 | 2023-03-03 12:00:03|
|2 | 2023-03-03 12:00:02|
此時的查詢結(jié)果正常，符合預(yù)期效果！
同樣的，以上面的刪除為例，詳細的實踐過程如下：
-- 發(fā)起第一頁查詢
select * from tb_order order by create_time desc limit 0,2;
-- 第一頁查詢結(jié)果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|
-- 刪除一條訂單數(shù)據(jù)
delete from tb_order where order_id = 4;
-- 發(fā)起第二頁查詢
select * from tb_order where create_time < '2023-03-03 12:00:04' order by create_time desc limit 0,2;
-- 第二頁查詢結(jié)果
|order_id | create_time |
|3 | 2023-03-03 12:00:03|
|2 | 2023-03-03 12:00:02|
查詢結(jié)果與預(yù)期一致，正常！
四、深入思考
- 選擇的排序字段值出現(xiàn)了重復(fù)，怎么辦？
在上面我們提到了，排序字段值不能出現(xiàn)重復(fù)的要求，但是現(xiàn)實的情況是，如果以訂單的創(chuàng)建時間來排序，當同一秒多次下單的時候大概率會出現(xiàn)重復(fù)，這個時候只能在訂單表里面新增一個排序字段，設(shè)置全局唯一索引，內(nèi)容是以時間為基礎(chǔ)來生成，比如雪花算法，或者自己寫一個基于時間全局自增的算法，確保全局唯一，最重要的是值的長度必須固定，訂單主鍵 ID 的生成規(guī)則推薦采用此方式，利用主鍵 ID 來排序效率查詢會非常高！
如果是 app 端的查詢，不建議設(shè)計多字段排序，因為在多字段排序的環(huán)境下，服務(wù)端在進行多條件的過濾查詢時，可能會把有效的數(shù)據(jù)給過濾掉，如果無法避開，盡量將多個排序字段合并到一個排序字段上，保證數(shù)據(jù)的查詢符合預(yù)期。
五、小結(jié)
本文主要圍繞 app 端分頁查詢出現(xiàn)數(shù)據(jù)重復(fù)或丟失的問題，進行一次復(fù)盤總結(jié)，如果有描述不對的地方，歡迎網(wǎng)友留言指出！
六、參考
1、知乎 - HQGDD - 分頁出現(xiàn)數(shù)據(jù)重復(fù)或丟失的問題，一文搞定！
