PostgreSQL / 系统课时
优化过滤与排序查询
优化过滤与排序查询
本课解决“查询条件明确却仍然慢”的问题。你将把过滤列、排序列和返回范围组合起来,写出可直接利用复合索引的 SQL,并用稳定排序避免分页结果重复或遗漏。
学习目标
- 根据等值过滤、范围过滤和排序顺序设计复合索引
- 使用
ORDER BY与LIMIT减少无效扫描和排序 - 识别函数包裹、返回列过多等常见优化障碍
先确定查询形状
优化前先固定业务查询,而不是脱离 SQL 单独“猜索引”。下面场景要求查询某客户最近支付成功的 2 笔订单,并在创建时间相同的情况下按主键倒序保持结果稳定。
sql
CREATE TEMP TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL,
status text NOT NULL,
created_at timestamp NOT NULL,
amount numeric(10, 2) NOT NULL
);
INSERT INTO orders (id, customer_id, status, created_at, amount) VALUES
(1, 1001, 'PAID', '2026-07-30 09:00:00', 68.00),
(2, 1001, 'PENDING', '2026-07-30 09:30:00', 88.00),
(3, 1001, 'PAID', '2026-07-30 10:00:00', 128.00),
(4, 1002, 'PAID', '2026-07-30 10:10:00', 50.00),
(5, 1001, 'PAID', '2026-07-30 10:00:00', 168.00);
CREATE INDEX idx_orders_customer_status_created_id
ON orders (customer_id, status, created_at DESC, id DESC);
SELECT id, created_at, amount
FROM orders
WHERE customer_id = 1001
AND status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 2;预期结果
text
id | created_at | amount
----+---------------------+--------
5 | 2026-07-30 10:00:00 | 168.00
3 | 2026-07-30 10:00:00 | 128.00让索引顺序匹配查询
复合索引通常把等值过滤列放在前面,再放范围或排序列。本例先按 customer_id、status 缩小范围,随后直接按 created_at DESC, id DESC 读取,减少额外排序。
索引列顺序必须结合真实查询频率决定。若系统主要只按 status 查询,以上索引未必合适;若条件经常缺少最左侧的 customer_id,也可能无法充分利用该索引。
控制排序与返回范围
排序字段应明确且稳定。只按 created_at 排序时,同一时间的多行顺序不确定;追加唯一的 id 可形成稳定顺序。配合 LIMIT 后,数据库可在取得足够行时提前停止。
不要无条件使用 SELECT *。只返回页面或接口真正需要的列,可减少堆表访问、网络传输和对象构造开销,也更有机会触发仅索引扫描。
避免破坏索引条件
不要在索引列上随意套函数或做不必要的类型转换。例如按日期过滤时,应使用时间范围,而不是把 created_at 转成日期后比较。参数类型也应与列类型一致。
验证优化效果时使用 EXPLAIN (ANALYZE, BUFFERS),重点观察实际行数、扫描行数、排序节点和缓冲区读取。小数据量下顺序扫描可能更便宜,因此应在接近生产规模的数据上判断。
按自己的节奏,确认真正理解。