非连续UUID主键表如何查询指定id后按inserted_at排序的n条数据

解法 #

我们采用**键集分页（Keyset Pagination）**方案实现该需求，该方案性能远高于OFFSET分页，同时能避免时间重复导致的分页错乱问题：

1. 单查询写法（CTE实现）

假设你需要按inserted_at倒序排序（越新的记录越靠前），取目标id之后的10条（n=10）记录，写法如下：

WITH target_row AS (
    -- 先取出目标id对应的时间和id值
    SELECT inserted_at, id 
    FROM users 
    WHERE id = '替换为你的目标uuid值'
)
SELECT u.*
FROM users u, target_row t
WHERE 
    -- 时间早于目标行
    u.inserted_at < t.inserted_at 
    -- 时间相同的情况下，id小于目标行（保证排序唯一）
    OR (u.inserted_at = t.inserted_at AND u.id < t.id)
-- 排序规则必须和WHERE中的比较逻辑对应
ORDER BY u.inserted_at DESC, u.id DESC
LIMIT 10; -- 替换为你需要的n值

如果需要按inserted_at正序排序（越晚新增的记录越靠后），只需要反转比较符和排序方向即可：

WITH target_row AS (
    SELECT inserted_at, id 
    FROM users 
    WHERE id = '替换为你的目标uuid值'
)
SELECT u.*
FROM users u, target_row t
WHERE 
    u.inserted_at > t.inserted_at 
    OR (u.inserted_at = t.inserted_at AND u.id > t.id)
ORDER BY u.inserted_at ASC, u.id ASC
LIMIT 10;

2. 拆分为两次查询写法

如果你不想使用CTE，也可以拆分为两次单独查询，效果完全一致：
第一步先查询目标行的时间和id：

SELECT inserted_at, id FROM users WHERE id = '替换为你的目标uuid值';

假设查询得到的时间为'2024-05-01 14:30:00+08'，id为'a1b2c3d4-5678-90ef-ghij-klmnopqrstuv'，第二步查询后续n条记录：

SELECT * FROM users
WHERE 
    inserted_at < '2024-05-01 14:30:00+08' 
    OR (inserted_at = '2024-05-01 14:30:00+08' AND id < 'a1b2c3d4-5678-90ef-ghij-klmnopqrstuv')
ORDER BY inserted_at DESC, id DESC
LIMIT 10;

优化建议

给inserted_at和id创建匹配排序方向的联合索引，可大幅提升查询性能，适配大数据量场景：

-- 适配倒序排序的索引
CREATE INDEX idx_users_inserted_at_id_desc ON users (inserted_at DESC, id DESC);
-- 如果用正序排序，可以创建对应索引
CREATE INDEX idx_users_inserted_at_id_asc ON users (inserted_at ASC, id ASC);

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