MySQL按点赞数与ID双列排序实现高效无重复分页问题咨询

需求背景 #

为了提升查询速度，我选择基于主键id做游标分页，筛选条件为posts.id > $start，避免传统offset分页越往后性能越差的问题。

常规帖子拉取逻辑 #

按id倒序拉取，分页逻辑实现如下：

// 接收游标起点
$start = $_GET['start'];

if ($start <= 0) {
    // 首次请求
    $offSet = "AND posts.id > $start";
} else if ($start > 0) {
    // 后续分页请求
    $offSet = "AND posts.id < $start";
}

// 拉取帖子列表
$posts = $db->query("SELECT posts.id, posts.likes,
                        posts.dislikes, posts.comments
    FROM posts, users
    WHERE posts.user_id=users.id
        $offSet
    ORDER BY posts.id DESC LIMIT 0, 15");

原高赞帖子拉取逻辑 #

// 拉取高赞帖子
$posts = $db->query("SELECT posts.id, posts.likes,
                        posts.dislikes, posts.comments
    FROM posts, users
    WHERE posts.user_id=users.id
        $offSet
      AND posts.likes != 0
    ORDER BY posts.likes DESC LIMIT 0, 15");

存在的问题 #

按ORDER BY likes DESC排序时，id不再是连续倒序的，原有基于id的分页逻辑会失效，拉取下一页时会重复返回第一页的内容。需要兼顾按点赞倒序的排序规则，同时保留游标分页的高性能，避免后续查询MySQL扫描大量前置行。

优化方案（@RickJames 提供） #

采用双字段游标分页方案，同时用点赞数和id作为分页游标，保证排序稳定性和查询性能：

// 分页游标初始化，首次请求均为0
$start = $leftoff_likes = 0;

// 拉取高赞帖子
$posts = $db->query("
    SELECT posts.id, posts.body, posts.user_id, posts.tag, posts.date, posts.comments, posts.likes, posts.dislikes, users.name, users.image 
    FROM posts, users 
    WHERE   posts.likes <= $leftoff_likes
      AND ( posts.likes <  $leftoff_likes OR posts.id < $start ) 
      AND posts.user_id=users.id 
    ORDER BY posts.likes DESC, posts.id DESC 
    LIMIT 0, 15");

echo json_encode($posts);

方案逻辑说明 #

1. 首次请求时$leftoff_likes和$start都设为0，正常拉取点赞最高的15条内容
2. 每次返回结果时，记录最后一条内容的likes值和id，下一次请求时分别传入$leftoff_likes和$start
3. 筛选逻辑：先限定所有内容的点赞数小于等于上一页末尾的点赞数，再排除已经返回过的内容——要么点赞数比末尾点赞数低，要么点赞数相同但id比末尾id小
4. 排序规则统一为likes DESC, id DESC，保证同点赞数的内容按id倒序排列，顺序稳定不会乱序
5. 可以给posts表创建(likes, id)联合索引，查询时可以直接通过索引定位分页起点，完全避免全表扫描，分页性能稳定不随页码增加下降

内容的提问来源于stack exchange，提问作者Max Njoroge