SQL如何利用子查询实现分页性能优化_索引覆盖与嵌套过滤-SQL-PHP中文网

子查询分页比offset更快，因其通过游标（如上一页最后id）直接定位，避免扫描丢弃前n行；需有序索引、仅适用于逐页加载，且过滤条件须内外一致。

sql如何利用子查询实现分页性能优化_索引覆盖与嵌套过滤

子查询分页为什么比 OFFSET 更快

因为 OFFSET 要跳过前 N 行，数据库仍需扫描、排序、丢弃那些行；而用子查询（比如先查出第 N 页起始的 id）能直接定位，避免全量扫描。

典型场景是主键或时间字段有序、且有索引——比如按 created_at 倒序分页查最新订单。这时候用子查询“记住上一页最后一条的 id”，下一页就从它之后取，效率翻倍。

必须确保排序字段有索引，否则子查询本身会变慢
不能用于“跳页”（如直接跳到第 100 页），只适合“逐页加载”或“无限滚动”
如果排序字段有重复值（比如多个订单同秒创建），要加 id 作为第二排序条件，否则可能漏数据或重复

怎么写“游标式”子查询分页（MySQL / PostgreSQL）

核心思路：不用 OFFSET，改用 WHERE id > ? ORDER BY id LIMIT N，其中 ? 是上一页最后一条的 id。

示例（查下一页 20 条，已知上一页最后的 id 是 1005）：

SELECT * FROM orders 
WHERE id > 1005 
ORDER BY id ASC 
LIMIT 20;

注意点：

排序方向必须和游标一致（ASC 就用 >，DESC 就用 ）
WHERE 条件字段必须是排序字段或其前缀（否则索引无法下推）
PostgreSQL 支持 WHERE (created_at, id) > (?, ?) 复合游标，MySQL 8.0+ 也支持，但老版本得拼 OR (created_at = ? AND id > ?)

索引覆盖对子查询分页的影响

如果子查询只查 id，但最终 SELECT * 需要回表取其他字段，性能优势会被抵消。所以得让索引“覆盖”所有需要的列。

比如分页只展示 id、title、created_at，那就建联合索引：

CREATE INDEX idx_orders_cursor ON orders (created_at DESC, id DESC, title);

这样 SELECT id, title, created_at ... 就能走索引覆盖，不回表。

不要在索引里加 TEXT 或大字段，会拖慢索引写入和内存占用
如果业务要求查 *，又没法加所有字段进索引，不如接受少量回表，但务必确保主键或排序字段索引存在
用 EXPLAIN 看 Extra 字段是否含 Using index，这是索引覆盖的关键标志

嵌套子查询过滤时容易漏掉的边界问题

当把分页和复杂过滤（比如用户权限、状态筛选）一起写进子查询，最容易出错的是 WHERE 条件没同步进内层查询，导致游标值不在过滤后的结果集里。

错误写法（外层过滤，内层没跟）：

SELECT * FROM orders 
WHERE id > (SELECT id FROM orders WHERE status = 'paid' ORDER BY id DESC LIMIT 1 OFFSET 19) 
  AND status = 'paid' 
ORDER BY id DESC 
LIMIT 20;

正确做法是把过滤条件复制进子查询：

SELECT * FROM orders 
WHERE id > (SELECT id FROM orders WHERE status = 'paid' ORDER BY id DESC LIMIT 1 OFFSET 19) 
  AND status = 'paid' 
ORDER BY id DESC 
LIMIT 20;