1. 背景

    最近参与到一个用户抽奖玩法的项目，玩法其实挺简单，具体为：发起方活动创建人A可以创建抽奖活动，通过设置一些必填元信息，比如活动开奖时间、中奖人数、中奖权益奖品(折扣券、满减券等)，并发布上线。而参与方的用户B通过进入活动页留言的方式完成活动的参与。到了开奖时间，会使用随机算法，从所有留言中选出对应活动中奖人数的用户，通知中奖并发放奖品。

    自然，对于参与方用户，我们需要透出各种状态的活动列表，如进行中活动、已结束活动列表。而用户最为关心的，可能还是进行中的活动（对于我这种羊毛党来说如此）。以下示意图截止大麦网的演唱会列表，按照开场时间由近至远排序。

    回到我们的项目中，进行中活动列表如何排序呢？类似的，比较简单且符合人们体感的，应该是基于活动的结束时间，由小至大（由近至远），越早结束的活动，越早曝光，便于更多的用户早点发现活动，提高活动的参与人数。

2. 存储设计

    在技术选型上，对于活动的存储，我们使用了mysql作为数据库存储，相应的活动表schema如下图所示：

    稍微注意两点：
    1.status标识活动的状态，2-已上线即为进行中的活动，3-已下线即为已结束的活动；

    2.由于需要根据活动的开奖时间/结束时间进行排序，因此建立了索引`idx_end_time`

3. 分页查询实现

    其实列表页进行分页并没什么难度，产品业务上的需求也比较简单：基于活动的结束时间由小至大排序，同时在分页的翻页过程中确保不会出现重复数据。

    而谈及分页查询，由于存储使用了mysql，自然想到使用数据库提供的limit功能，进行分页。但是在最终实现上，确实还是稍微遇到了一些坑。这里描述下我们选择实现方案的过程，以及如何确立最终方案。

3.1 limit offset

    mysql数据库提供了limit offset的用法，为实现我们的需求，会写出如下SQL：

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

    其语义是按照end_time由小至大排序，从offset偏移开始的位置，取出n条数据。上游请求入参也会比较简单，如下示意。其中 n = pageSize，limit = (pageNum-1) * pageSize

    不建议采用这种方式，因为使用这种方式分页有两个比较明显的问题：

    1.随着页数的增长，查询性能急剧下降。原因是limit offset, n， 数据库底层实际会查询出offset+n条数据，比如一页10条记录，查询第一页的话，只会检索最小的10条记录。而第1001页，则会查出10010条记录，然后返回最后的10条；

    2.无法保证分页数据不重复。因为我们排序使用的活动的结束时间end_time，而提供给用户创建活动时，只要活动结束时间大于创建时刻30分钟即可，也就是说活动的结束时间是比较随机的。假设当前查询了第三页的数据，紧接着查询下一页，如下图。我们在两次查询之间，insert一条数据，可以看到id为185083的数据行，在第四页实际查询时，再次出现。出现问题的原因在于，由于数据的动态变化（这里我们插入了一条数据），偏移量offset变得不准确（新增了一条记录，offset为11才会得到理想的不重复结果）。类似的，记录如果因为被删除导致数据库结果集的变化，同样会出现这个问题。

3.2 limit

    既然offset有性能以及不准确的问题，那么我们直接采用limit呢，如下SQL：

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

    语义是查询活动结束时间大于endTime的n条记录数，上游入参如下，其中lastEndTime为上一页返回值最后一条记录的结束时间（即为上一次查询结果中活动结束时间最大的记录），用来给#endTime#赋值，而 n = pageSize

    看起来似乎很美好，where条件使用end_time的范围查询，命中`idx_end_time`索引，也不存在offset，没有性能问题的担忧，同时还保证了去重。

    不过细想一下，查询有一个大问题：会丢失数据，因为end_time自身就是会有重复的（活动创建者显然倾向于选取某个好的开奖时间，如周日晚上20点这样的"黄金时间"）。如下示例，上一页条件为 end_time > '2020-05-18 18:51:33'，返回结果的end_time均为'2020-05-18 18:51:34'，因此下一页查询时，lastEndTime为'2020-05-18 18:51:34'，使用其作为where条件时，可以看到丢失了剩余的end_time为34秒的数据（id从185086至185089范围），显然这是不可接受的。

    那么我们将查询条件由大于，改为大于等于是否可行？答案是不行，包含等于的话，数据直接就重复出现了，并且当记录数大于等于分页数时，将永远查出同一页数据，这是灾难性的。

3.3 引入去重字段

    使用end_time作为查询条件会出现数据丢失或无法去重，仔细分析下其原因在于end_time字段本身就是随机、可重复的。曾经做过遍历数据库刷数据的需求，为实现无重复遍历数据库，我们一般会选取一个不会重复的字段（同时基于此字段建有索引），用该字段作为where条件逐步遍历，比如现在我们用主键id来遍历上面的activity表，SQL如下：

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

    上次查询结果的id，作为下次查询的条件值。

    既然业务上需要用活动结束时间排序，那么我们能否在使用end_time的同时，引入主键id来辅助排序，实现去重呢？比如如下SQL：

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

    这时上游入参如下，除了上次返回结果的活动结束时间与分页数，还需要将上次活动的id也带给我们，便于我们用来辅助sql语句各部分。（这里仅做示例，实际返回值或入参中，出于安全考虑，建议id不要直接暴露出去，最好经过加解密算法处理成某个加密串再透出）

    仔细分析下，相同结束时间的活动，由于引入了id进行判断，避免了再次取出重复的数据。但是这条分页查询语句，还是存在明显问题，如下所示。假设业务上先创建了三个结束时间为"2020-06-01 00:00:00"的活动，接着创建五个结束时间为"2020-05-31 00:00:00"的活动，最后又创建两个结束时间为"2020-06-01 00:00:00"的活动，10条记录如下截图第一个SQL的结果所示。由于上一次查询最后一条记录的id等于205075，执行最后一次查询时，用其作为where条件的取值，查询结果却只有两条记录，丢失了id为205068~205070的三条记录。

    数据丢失的原因也很直观，因为id与end_time之间是逻辑与的关系，因此过滤了id小于等于205075的所有记录。

    进一步分析，其实我们引入id的原因，是因为end_time字段会有重复；而产生重复的前提，是需要end_time相等时（其实我们在不引入等于条件时，并不会出现重复，而是会丢失数据）。换句话说，如果end_time并不相等，是不需要用id做进一步判断的。因此我们对上面的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 最后的坑

    我们再回到最开始，其实活动列表页，并不只有进行中，还有已结束活动列表。而业务的需求，对于已结束活动列表，是需要从大到小排序的（其实时间上也是由近至远，不过因为已结束是过去时态，因此排序就会反过来变为从大到小了），因此相应的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;

    当我测试活动结束列表页时，接口的响应时间明显比查询进行中列表慢了不少。接口是没有太多别的逻辑的，因此初步排查下来，将原因锁定在SQL上，那就使用explain查看执行计划吧。先看正序排序，执行计划如下。可以看到查询是命中了`idx_end_time`索引的，查询性能上比较有保障。

    再看逆序查询，执行计划如下。额，居然是全表扫描+文件排序，也说明了为啥查询已结束活动列表时，接口响应会明显变慢了。

    好吧，SQL并不复杂，而且类似的查询SQL，为啥执行器在执行时，前一条命中了索引，后一条选择了全表扫描？好在MYSQL的官方文档做出了解释（https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html）。

    有了官方指导，SQL修改如下：

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

    执行计划来看，再次命中了`idx_end_time`索引；再次测试，接口响应耗时也得到了改善，终于可以露出欣慰的笑容了。

4. 结语

    其实在刚接需求时，是没有想到一个简单的列表页分页查询会有如此的坑，因此还是稍作总结记录。当然上文最终采取的方案，仅仅说明是适合、满足我们业务需求的方案，不一定具有普适性。描述如有错误，也烦请大家指正。