4. 索引与排序：ORDER BY与索引、索引排序优化、LIMIT与索引

在PostgreSQL中，ORDER BY与LIMIT是查询中最常见的排序与分页操作。如果缺乏索引支持，数据库必须对结果集进行显式排序（Sort节点），当数据量较大时，排序操作会消耗大量CPU和内存，甚至触发磁盘临时文件。本章深入剖析索引如何消除排序、优化LIMIT性能，以及多字段排序时的索引设计策略。

4.1 ORDER BY与索引：索引排序原理

PostgreSQL的B-Tree索引天生按索引键值有序存储。当查询的ORDER BY子句与索引的排序方向一致时，优化器可以直接扫描索引并返回有序结果，无需额外的Sort步骤。这种“索引排序”是性能提升的核心来源。

排序场景	索引支持条件	执行计划特征	性能影响
单字段升序	索引字段 = ORDER BY字段，ASC	Index Scan Backward / Forward	无Sort节点，极快
单字段降序	索引字段 = ORDER BY字段，DESC	Index Scan Backward	无Sort节点，极快
多字段混合排序	索引字段顺序与ORDER BY完全匹配，方向一致	Index Only Scan	无Sort节点，极快
排序方向与索引相反	索引支持反向扫描（默认B-Tree支持）	Index Scan Backward	无Sort节点，但扫描方向反转
ORDER BY字段不在索引中	无	Seq Scan + Sort	显式排序，性能差

4.2 索引排序优化：多字段与混合方向

实际业务中，ORDER BY经常涉及多个字段，且升降序混合。PostgreSQL的B-Tree索引支持多字段排序，但索引定义必须与查询的排序需求精确匹配。

4.2.1 多字段排序的索引设计原则

• 字段顺序匹配：索引字段的顺序必须与ORDER BY子句中的字段顺序完全一致。
• 排序方向匹配：索引创建时可以通过ASC/DESC指定每个字段的排序方向，必须与查询一致。
• 前导字段优先：如果ORDER BY只使用索引的前N个字段，索引仍可部分生效。

4.2.2 索引排序与过滤条件的组合

当WHERE条件与ORDER BY同时存在时，索引设计需要兼顾过滤与排序。最佳策略是：将等值过滤字段放在索引前列，排序字段放在后面。

-- 查询：查询2024年订单，按创建时间降序排列
SELECT * FROM orders
WHERE status = 'paid' AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

-- 最优索引：status等值过滤在前，created_at排序在后
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 执行计划将使用 Index Scan，无需Sort
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status = 'paid' AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

4.3 LIMIT与索引：提前终止扫描

LIMIT与索引排序结合时，PostgreSQL可以提前终止索引扫描，这是“Top-N”查询性能优化的核心。当索引已经有序时，数据库只需扫描前N条记录即可返回结果，无需扫描整个索引或表。

4.3.1 LIMIT + 索引排序的执行机制

• 索引有序时：优化器使用Limit节点包裹Index Scan，扫描到第N条记录后立即停止。
• 索引无序时：数据库必须先扫描所有满足条件的记录，然后进行Sort，最后取前N条。当数据量巨大时，Sort成为性能瓶颈。

查询模式	索引支持	扫描行数	排序开销	典型场景
ORDER BY + LIMIT	索引完全覆盖排序	N行（LIMIT值）	无	排行榜、最新文章、分页第一页
ORDER BY + LIMIT	索引部分覆盖	大量行 + N行	有（Sort）	多字段排序不匹配
WHERE + ORDER BY + LIMIT	复合索引覆盖过滤+排序	N行	无	带条件的分页查询
无ORDER BY + LIMIT	任意索引	N行	无	随机取N条

4.4 实战案例：分页查询优化

分页查询是ORDER BY + LIMIT的典型应用。传统OFFSET分页在深翻页时性能急剧下降，而“游标分页”利用索引排序可以保持稳定性能。

4.4.1 传统OFFSET分页的问题

-- 第10000页，每页20条
SELECT * FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 199980;

-- 执行计划：即使有索引，也需要扫描199980 + 20行，然后丢弃前199980行
-- 随着页码增大，扫描行数线性增长

4.4.2 游标分页（Keyset Pagination）

-- 第一页：获取前20条
SELECT * FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 第二页：使用上一页最后一条记录的created_at和id作为游标
SELECT * FROM orders
WHERE status = 'paid'
  AND (created_at, id) < ('2024-12-01 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 索引支持：CREATE INDEX idx_orders_status_cid ON orders (status, created_at DESC, id DESC);
-- 每次查询只扫描20行，性能恒定

4.5 索引排序优化检查清单

检查项	优化动作	验证方法
ORDER BY字段是否在索引中	创建包含排序字段的索引	EXPLAIN中无Sort节点
排序方向是否匹配	索引使用DESC/ASC与查询一致	Index Scan方向与ORDER BY一致
多字段顺序是否匹配	索引字段顺序与ORDER BY一致	执行计划显示Index Only Scan
WHERE条件是否利用索引前导字段	等值条件放在索引前列	索引过滤行数接近LIMIT值
LIMIT是否触发提前终止	确认索引有序且无Sort	扫描行数 = LIMIT值
深分页是否使用游标	改用Keyset Pagination	扫描行数恒定，不随页码增长