键集分页

概念定义

OFFSET 10000 LIMIT 20 要先扫过并丢弃一万行——第五十页成本是第一页的五十倍。键集分页记住上一页最后一行，请求“排序在其之后”的行：数据库从索引定位后精确读二十行。

工作机制

游标是末行在排序列上的值元组：WHERE (created_at, id) > (last_created, last_id) ORDER BY created_at, id LIMIT 20。决胜列让排序成为全序，匹配的组合索引把谓词变成区间起点。游标常做签名或加密，防止客户端篡改。

使用场景

无限滚动、活动流、无法长持事务的导出等一切无界信息流；对外表现为不透明的 next 游标。向后翻页用镜像谓词与排序。

常见误区

游标不是随意的黑盒令牌，它编码排序元组，乱编就失去 seek 特性。非唯一排序列必须加决胜列，否则重复时间戳会让页面重行或漏行。排序列若无索引前缀对齐，每页都会落入排序节点。按可变列排序会让行在页间漂移，出现“看到两次或永远看不到”；并发插入下新行是否出现在后页，对信息流可接受、对账本则是契约问题。

代码示例

-- B-tree index for equality and range queries
CREATE INDEX idx_products_category_price
    ON products (category_id, unit_price);

-- Partial index: only index active products
CREATE INDEX idx_products_active
    ON products (product_name)
    WHERE discontinued_date IS NULL;

-- Expression index for case-insensitive searches
CREATE INDEX idx_customers_email_lower
    ON customers (LOWER(email));

-- Unique index for constraint enforcement
CREATE UNIQUE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date)
    WHERE status != 'cancelled';

-- Covering index (INCLUDE) for index-only scans
CREATE INDEX idx_order_items_covering
    ON order_items (order_id)
    INCLUDE (product_id, quantity, unit_price);

-- Concurrent index creation (no table locks)
CREATE INDEX CONCURRENTLY idx_products_name
    ON products (product_name);

-- BUFFERS 选项显示磁盘与缓存命中，真实成本的核心
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-10-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- 检查估算偏差：预估行数与实际行数差距大时统计信息过期
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 统计信息刷新后重测
ANALYZE orders;
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 验证新索引被采用：Index Scan / Index Only Scan
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

EXPLAIN (ANALYZE)
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'paid';