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),重点观察实际行数、扫描行数、排序节点和缓冲区读取。小数据量下顺序扫描可能更便宜,因此应在接近生产规模的数据上判断。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

优化过滤与排序查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通