中文
devexamples

Keyset Pagination Seek Method

Paginate with the seek method on a compound cursor, comparing plans against OFFSET to show why deep pages stay fast.

SqlData Fetching

Code

snippet
-- 键集分页：锚点条件让索引直接定位下一页
SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ('2026-10-21 14:30:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 对照的 OFFSET 版本：深页要扫过全部前序行
SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- 首页请求（无锚点）
SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 配套复合索引：排序方向与查询一致
CREATE INDEX idx_articles_page
  ON articles (created_at DESC, id DESC);

-- 验证两种方案的代价差异
EXPLAIN ANALYZE
SELECT id FROM articles
WHERE (created_at, id) < ('2026-10-21 14:30:00', 98765)
ORDER BY created_at DESC, id DESC LIMIT 20;

Related Examples