MySQL按里程排序存在重复值时，如何实现无Offset正确分页？

表结构 #

CREATE TABLE `posts` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(160) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `categoryId` int(11) UNSIGNED NOT NULL,
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdAt` int(11) NOT NULL,
  `updatedAt` int(11) NOT NULL,
  `carId` int(11) UNSIGNED DEFAULT NULL,
  `userId` int(11) UNSIGNED NOT NULL,
  `coverUrl` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `costAmount` int(10) UNSIGNED DEFAULT NULL,
  `costCurrency` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mileage` int(11) UNSIGNED DEFAULT NULL,
  `searchContent` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

测试数据 #

INSERT INTO posts (`id`, title, categoryId, content, createdAt, updatedAt, carId, userId, mileage, coverUrl, costAmount, costCurrency, `searchContent`) VALUES
(129, 'Вытащил двигатель с авто, подтёки масла', 21, '', 1664353399, 1664353399, 68, 17, 228500, 'img1', 13000, 'uah', 'text'),
(152, 'Вытащил двигатель с авто, подтёки масла', 14, '', 1639699200, 1664359803, 68, 17, 228500, 'img1', 13000, 'uah', 'text'),
(165, 'Вытащил двигатель с авто, подтёки масла', 14, 'text'),
(111, 'Скол на новом лобовом', 21, 'test', 1664304339, 1664304339, 68, 17, 227700, 'img1', 0, 'usd', 'text'),
(130, 'Скол на новом лобовом', 21, 'test', 1664353423, 1664353423, 68, 17, 227700, 'img1', 0, 'usd', 'text'),
(141, 'Скол на новом лобовом', 21, 'test', 1664354165, 1664354165, 68, 17, 227700, 'img1', 0, 'usd', ''),
(166, 'Скол на новом лобовом', 14, 'test', 1639180800, 1664372713, 68, 17, 227700, 'img1', 0, 'usd', ''),
(112, 'Замена лобового', 21, 'test', 1664304427, 1664304427, 68, 17, 227500, 'img1', 10500, 'uah', ''),
(118, 'Замена левой фары после дтп', 19, 'test', 1664304860, 1664304860, 68, 17, 227500, 'img1', 3, 'usd', '');

当前需求与问题 #

需要对posts表按mileage降序做懒加载分页，且不能使用OFFSET。但存在多条记录mileage相同但id不同的情况，比如当最后一条加载的数据是id=130、mileage=227700时，需要获取同里程下id更大的后续记录（如id=141、166）。

当前查询语句（:qp0为传入的最后一条数据id）：

SELECT
    posts.id,
    posts.title,
    posts.categoryId,
    posts.content,
    posts.userId,
    posts.createdAt,
    posts.mileage,
    posts.costAmount,
    posts.costCurrency,
    posts.coverUrl,
    user.username,
    user.country,
    user.avatar,
    user.city,
    categories.id AS categoryId,
    categories.name,
    post_counter.comments,
    post_counter.likes
FROM
    posts
INNER JOIN user ON USER.id = posts.userId
LEFT JOIN categories ON categories.id = posts.categoryId
LEFT JOIN post_counter ON post_counter.postId = posts.id
WHERE
    posts.carId = 68 and posts.id < :qp0
ORDER BY
    posts.mileage
DESC
LIMIT 6

尝试过posts.carId = 68 and posts.id < 130 and posts.mileage < 227700或posts.carId = 68 and posts.id < 130这类条件，都无法获取同一里程下id更大的记录，只能得到id更小的数据。需要修改查询逻辑实现正确的无Offset分页。

核心思路是基于排序规则的复合条件过滤：因为我们的分页排序逻辑是mileage降序，同里程下id降序（保证大id排在前面），所以下一页的过滤条件需要覆盖两种情况：要么里程小于上一页最后一条的里程，要么里程相同但id小于上一页最后一条的id。

修改后的查询语句 #

需要从前端传入两个参数：上一页最后一条记录的mileage（:last_mileage）和id（:last_id），具体SQL如下：

SELECT
    posts.id,
    posts.title,
    posts.categoryId,
    posts.content,
    posts.userId,
    posts.createdAt,
    posts.mileage,
    posts.costAmount,
    posts.costCurrency,
    posts.coverUrl,
    user.username,
    user.country,
    user.avatar,
    user.city,
    categories.id AS categoryId,
    categories.name,
    post_counter.comments,
    post_counter.likes
FROM
    posts
INNER JOIN user ON USER.id = posts.userId
LEFT JOIN categories ON categories.id = posts.categoryId
LEFT JOIN post_counter ON post_counter.postId = posts.id
WHERE
    posts.carId = 68
    AND (
        posts.mileage < :last_mileage
        OR (posts.mileage = :last_mileage AND posts.id < :last_id)
    )
ORDER BY
    posts.mileage DESC,
    posts.id DESC
LIMIT 6

逻辑说明 #

1. 明确排序规则：将ORDER BY改为posts.mileage DESC, posts.id DESC，确保同里程下id更大的记录排在前面，分页顺序不会混乱。
2. 复合过滤条件：
   • posts.mileage < :last_mileage：匹配所有里程比上一页最后一条更小的记录。
   • (posts.mileage = :last_mileage AND posts.id < :last_id)：匹配和上一页最后一条里程相同，但id更小的记录（因为排序是id降序，这些就是该里程下上一页记录之后的后续条目）。
3. 参数传递：每次分页请求需要带上上一页最后一条记录的mileage和id，而非仅传id，这样才能精准定位下一页的起始位置。

索引优化 #

为提升查询效率，建议创建复合索引，直接覆盖WHERE和ORDER BY的条件：

CREATE INDEX idx_posts_carid_mileage_id ON posts(carId, mileage DESC, id DESC);

内容的提问来源于stack exchange，提问作者Denis Maksiura