1. 背景

? ? 最近參與到一個(gè)用戶抽獎(jiǎng)玩法的項(xiàng)目，玩法其實(shí)挺簡(jiǎn)單邮绿，具體為：發(fā)起方活動(dòng)創(chuàng)建人A可以創(chuàng)建抽獎(jiǎng)活動(dòng)，通過(guò)設(shè)置一些必填元信息攀例，比如活動(dòng)開獎(jiǎng)時(shí)間船逮、中獎(jiǎng)人數(shù)、中獎(jiǎng)權(quán)益獎(jiǎng)品(折扣券肛度、滿減券等)傻唾，并發(fā)布上線。而參與方的用戶B通過(guò)進(jìn)入活動(dòng)頁(yè)留言的方式完成活動(dòng)的參與承耿。到了開獎(jiǎng)時(shí)間，會(huì)使用隨機(jī)算法伪煤，從所有留言中選出對(duì)應(yīng)活動(dòng)中獎(jiǎng)人數(shù)的用戶加袋，通知中獎(jiǎng)并發(fā)放獎(jiǎng)品。

? ? 自然抱既，對(duì)于參與方用戶职烧，我們需要透出各種狀態(tài)的活動(dòng)列表，如進(jìn)行中活動(dòng)、已結(jié)束活動(dòng)列表蚀之。而用戶最為關(guān)心的蝗敢，可能還是進(jìn)行中的活動(dòng)（對(duì)于我這種羊毛黨來(lái)說(shuō)如此）。以下示意圖截止大麥網(wǎng)的演唱會(huì)列表足删，按照開場(chǎng)時(shí)間由近至遠(yuǎn)排序寿谴。

? ? 回到我們的項(xiàng)目中，進(jìn)行中活動(dòng)列表如何排序呢失受？類似的讶泰，比較簡(jiǎn)單且符合人們體感的，應(yīng)該是基于活動(dòng)的結(jié)束時(shí)間拂到，由小至大（由近至遠(yuǎn)）痪署，越早結(jié)束的活動(dòng)，越早曝光兄旬，便于更多的用戶早點(diǎn)發(fā)現(xiàn)活動(dòng)狼犯，提高活動(dòng)的參與人數(shù)。

2. 存儲(chǔ)設(shè)計(jì)

? ? 在技術(shù)選型上领铐，對(duì)于活動(dòng)的存儲(chǔ)辜王，我們使用了mysql作為數(shù)據(jù)庫(kù)存儲(chǔ)，相應(yīng)的活動(dòng)表schema如下圖所示：

? ? 稍微注意兩點(diǎn)：
? ? 1.status標(biāo)識(shí)活動(dòng)的狀態(tài)罐孝，2-已上線即為進(jìn)行中的活動(dòng)呐馆，3-已下線即為已結(jié)束的活動(dòng)；

? ? 2.由于需要根據(jù)活動(dòng)的開獎(jiǎng)時(shí)間/結(jié)束時(shí)間進(jìn)行排序莲兢，因此建立了索引`idx_end_time`

3. 分頁(yè)查詢實(shí)現(xiàn)

????其實(shí)列表頁(yè)進(jìn)行分頁(yè)并沒什么難度汹来，產(chǎn)品業(yè)務(wù)上的需求也比較簡(jiǎn)單：基于活動(dòng)的結(jié)束時(shí)間由小至大排序，同時(shí)在分頁(yè)的翻頁(yè)過(guò)程中確保不會(huì)出現(xiàn)重復(fù)數(shù)據(jù)改艇。

????而談及分頁(yè)查詢收班，由于存儲(chǔ)使用了mysql，自然想到使用數(shù)據(jù)庫(kù)提供的limit功能谒兄，進(jìn)行分頁(yè)摔桦。但是在最終實(shí)現(xiàn)上，確實(shí)還是稍微遇到了一些坑承疲。這里描述下我們選擇實(shí)現(xiàn)方案的過(guò)程邻耕，以及如何確立最終方案。

3.1 limit offset

? ? mysql數(shù)據(jù)庫(kù)提供了limit offset的用法燕鸽，為實(shí)現(xiàn)我們的需求兄世，會(huì)寫出如下SQL：

? ? select * from activity order by end_time asc limit offset, n;

????其語(yǔ)義是按照end_time由小至大排序，從offset偏移開始的位置啊研，取出n條數(shù)據(jù)御滩。上游請(qǐng)求入?yún)⒁矔?huì)比較簡(jiǎn)單鸥拧，如下示意。其中 n = pageSize削解，limit = (pageNum-1) * pageSize

? ? 不建議采用這種方式富弦，因?yàn)槭褂眠@種方式分頁(yè)有兩個(gè)比較明顯的問(wèn)題：

? ? 1.隨著頁(yè)數(shù)的增長(zhǎng)，查詢性能急劇下降氛驮。原因是limit offset, n腕柜， 數(shù)據(jù)庫(kù)底層實(shí)際會(huì)查詢出offset+n條數(shù)據(jù)，比如一頁(yè)10條記錄柳爽，查詢第一頁(yè)的話媳握，只會(huì)檢索最小的10條記錄。而第1001頁(yè)磷脯，則會(huì)查出10010條記錄蛾找，然后返回最后的10條；

? ? 2.無(wú)法保證分頁(yè)數(shù)據(jù)不重復(fù)赵誓。因?yàn)槲覀兣判蚴褂玫幕顒?dòng)的結(jié)束時(shí)間end_time打毛，而提供給用戶創(chuàng)建活動(dòng)時(shí)，只要活動(dòng)結(jié)束時(shí)間大于創(chuàng)建時(shí)刻30分鐘即可俩功，也就是說(shuō)活動(dòng)的結(jié)束時(shí)間是比較隨機(jī)的洪鸭。假設(shè)當(dāng)前查詢了第三頁(yè)的數(shù)據(jù)亏掀，緊接著查詢下一頁(yè)，如下圖。我們?cè)趦纱尾樵冎g嘶是，insert一條數(shù)據(jù)查近，可以看到id為185083的數(shù)據(jù)行竿奏，在第四頁(yè)實(shí)際查詢時(shí)荠耽，再次出現(xiàn)。出現(xiàn)問(wèn)題的原因在于豺谈，由于數(shù)據(jù)的動(dòng)態(tài)變化（這里我們插入了一條數(shù)據(jù)）郑象，偏移量offset變得不準(zhǔn)確（新增了一條記錄，offset為11才會(huì)得到理想的不重復(fù)結(jié)果）茬末。類似的厂榛，記錄如果因?yàn)楸粍h除導(dǎo)致數(shù)據(jù)庫(kù)結(jié)果集的變化，同樣會(huì)出現(xiàn)這個(gè)問(wèn)題丽惭。

3.2 limit

? ? 既然offset有性能以及不準(zhǔn)確的問(wèn)題击奶，那么我們直接采用limit呢，如下SQL：

????select * from activity where end_time > #endTime# order by end_time asc limit n;

? ? 語(yǔ)義是查詢活動(dòng)結(jié)束時(shí)間大于endTime的n條記錄數(shù)吐根，上游入?yún)⑷缦抡撸渲衛(wèi)astEndTime為上一頁(yè)返回值最后一條記錄的結(jié)束時(shí)間（即為上一次查詢結(jié)果中活動(dòng)結(jié)束時(shí)間最大的記錄），用來(lái)給#endTime#賦值拷橘，而 n = pageSize

? ? 看起來(lái)似乎很美好局义，where條件使用end_time的范圍查詢，命中`idx_end_time`索引冗疮，也不存在offset萄唇，沒有性能問(wèn)題的擔(dān)憂，同時(shí)還保證了去重术幔。

? ? 不過(guò)細(xì)想一下另萤，查詢有一個(gè)大問(wèn)題：會(huì)丟失數(shù)據(jù)，因?yàn)閑nd_time自身就是會(huì)有重復(fù)的（活動(dòng)創(chuàng)建者顯然傾向于選取某個(gè)好的開獎(jiǎng)時(shí)間诅挑，如周日晚上20點(diǎn)這樣的"黃金時(shí)間"）四敞。如下示例，上一頁(yè)條件為 end_time > '2020-05-18 18:51:33'拔妥，返回結(jié)果的end_time均為'2020-05-18 18:51:34'忿危，因此下一頁(yè)查詢時(shí)，lastEndTime為'2020-05-18 18:51:34'没龙，使用其作為where條件時(shí)铺厨，可以看到丟失了剩余的end_time為34秒的數(shù)據(jù)（id從185086至185089范圍），顯然這是不可接受的硬纤。

? ? 那么我們將查詢條件由大于解滓，改為大于等于是否可行？答案是不行筝家，包含等于的話洼裤，數(shù)據(jù)直接就重復(fù)出現(xiàn)了，并且當(dāng)記錄數(shù)大于等于分頁(yè)數(shù)時(shí)溪王，將永遠(yuǎn)查出同一頁(yè)數(shù)據(jù)腮鞍，這是災(zāi)難性的。

3.3 引入去重字段

? ? 使用end_time作為查詢條件會(huì)出現(xiàn)數(shù)據(jù)丟失或無(wú)法去重在扰，仔細(xì)分析下其原因在于end_time字段本身就是隨機(jī)缕减、可重復(fù)的。曾經(jīng)做過(guò)遍歷數(shù)據(jù)庫(kù)刷數(shù)據(jù)的需求芒珠，為實(shí)現(xiàn)無(wú)重復(fù)遍歷數(shù)據(jù)庫(kù)桥狡，我們一般會(huì)選取一個(gè)不會(huì)重復(fù)的字段（同時(shí)基于此字段建有索引），用該字段作為where條件逐步遍歷皱卓，比如現(xiàn)在我們用主鍵id來(lái)遍歷上面的activity表裹芝，SQL如下：

? ? select * from activity where id > #id# order by id asc limit n;

????上次查詢結(jié)果的id，作為下次查詢的條件值娜汁。

? ? 既然業(yè)務(wù)上需要用活動(dòng)結(jié)束時(shí)間排序嫂易，那么我們能否在使用end_time的同時(shí)，引入主鍵id來(lái)輔助排序掐禁，實(shí)現(xiàn)去重呢怜械？比如如下SQL：

? ? select * from activity where end_time >= #endTime# and id > #id# order by end_time asc, id asc limit n;

? ? 這時(shí)上游入?yún)⑷缦侣停松洗畏祷亟Y(jié)果的活動(dòng)結(jié)束時(shí)間與分頁(yè)數(shù)，還需要將上次活動(dòng)的id也帶給我們缕允，便于我們用來(lái)輔助sql語(yǔ)句各部分峡扩。（這里僅做示例，實(shí)際返回值或入?yún)⒅姓媳荆鲇诎踩紤]教届，建議id不要直接暴露出去，最好經(jīng)過(guò)加解密算法處理成某個(gè)加密串再透出）

????仔細(xì)分析下驾霜，相同結(jié)束時(shí)間的活動(dòng)案训，由于引入了id進(jìn)行判斷，避免了再次取出重復(fù)的數(shù)據(jù)粪糙。但是這條分頁(yè)查詢語(yǔ)句强霎，還是存在明顯問(wèn)題，如下所示猜旬。假設(shè)業(yè)務(wù)上先創(chuàng)建了三個(gè)結(jié)束時(shí)間為"2020-06-01 00:00:00"的活動(dòng)脆栋，接著創(chuàng)建五個(gè)結(jié)束時(shí)間為"2020-05-31 00:00:00"的活動(dòng)，最后又創(chuàng)建兩個(gè)結(jié)束時(shí)間為"2020-06-01 00:00:00"的活動(dòng)洒擦，10條記錄如下截圖第一個(gè)SQL的結(jié)果所示椿争。由于上一次查詢最后一條記錄的id等于205075，執(zhí)行最后一次查詢時(shí)熟嫩，用其作為where條件的取值秦踪，查詢結(jié)果卻只有兩條記錄，丟失了id為205068~205070的三條記錄掸茅。

????數(shù)據(jù)丟失的原因也很直觀椅邓，因?yàn)閕d與end_time之間是邏輯與的關(guān)系，因此過(guò)濾了id小于等于205075的所有記錄昧狮。

? ? 進(jìn)一步分析景馁，其實(shí)我們引入id的原因，是因?yàn)閑nd_time字段會(huì)有重復(fù)逗鸣；而產(chǎn)生重復(fù)的前提合住，是需要end_time相等時(shí)（其實(shí)我們?cè)诓灰氲扔跅l件時(shí)，并不會(huì)出現(xiàn)重復(fù)撒璧，而是會(huì)丟失數(shù)據(jù)）透葛。換句話說(shuō)，如果end_time并不相等卿樱，是不需要用id做進(jìn)一步判斷的僚害。因此我們對(duì)上面的SQL稍加修改，如下：

? ? select * from activity where end_time > #endTime# or (end_time = #endTime# and id > #id#) order by end_time asc, id asc limit n;

3.4 最后的坑

? ? 我們?cè)倩氐阶铋_始繁调，其實(shí)活動(dòng)列表頁(yè)萨蚕，并不只有進(jìn)行中靶草，還有已結(jié)束活動(dòng)列表。而業(yè)務(wù)的需求门岔，對(duì)于已結(jié)束活動(dòng)列表爱致，是需要從大到小排序的（其實(shí)時(shí)間上也是由近至遠(yuǎn)烤送，不過(guò)因?yàn)橐呀Y(jié)束是過(guò)去時(shí)態(tài)寒随，因此排序就會(huì)反過(guò)來(lái)變?yōu)閺拇蟮叫×耍虼讼鄳?yīng)的SQL需要修改where與排序條件帮坚，如下：

????select * from activity where end_time < #endTime# or (end_time = #endTime# and id > #id#) order by end_time desc, id asc limit n;

? ? 當(dāng)我測(cè)試活動(dòng)結(jié)束列表頁(yè)時(shí)妻往，接口的響應(yīng)時(shí)間明顯比查詢進(jìn)行中列表慢了不少。接口是沒有太多別的邏輯的试和，因此初步排查下來(lái)讯泣，將原因鎖定在SQL上，那就使用explain查看執(zhí)行計(jì)劃吧阅悍。先看正序排序好渠，執(zhí)行計(jì)劃如下〗谑樱可以看到查詢是命中了`idx_end_time`索引的拳锚，查詢性能上比較有保障。

? ? 再看逆序查詢寻行，執(zhí)行計(jì)劃如下霍掺。額，居然是全表掃描+文件排序拌蜘，也說(shuō)明了為啥查詢已結(jié)束活動(dòng)列表時(shí)杆烁，接口響應(yīng)會(huì)明顯變慢了。

? ? 好吧简卧，SQL并不復(fù)雜兔魂，而且類似的查詢SQL，為啥執(zhí)行器在執(zhí)行時(shí)举娩，前一條命中了索引析校，后一條選擇了全表掃描？好在MYSQL的官方文檔做出了解釋（https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html）晓铆。

? ? 有了官方指導(dǎo)勺良，SQL修改如下：

????select * from activity where end_time < #endTime# or (end_time = #endTime# and id < #id#) order by end_time desc, id desc limit n;

? ? 執(zhí)行計(jì)劃來(lái)看，再次命中了`idx_end_time`索引骄噪；再次測(cè)試尚困，接口響應(yīng)耗時(shí)也得到了改善，終于可以露出欣慰的笑容了链蕊。

4. 結(jié)語(yǔ)

? ? 其實(shí)在剛接需求時(shí)事甜，是沒有想到一個(gè)簡(jiǎn)單的列表頁(yè)分頁(yè)查詢會(huì)有如此的坑谬泌，因此還是稍作總結(jié)記錄。當(dāng)然上文最終采取的方案逻谦，僅僅說(shuō)明是適合掌实、滿足我們業(yè)務(wù)需求的方案，不一定具有普適性邦马。描述如有錯(cuò)誤贱鼻，也煩請(qǐng)大家指正。