PostgreSQL / 系统课时

为核心查询设计索引

为核心查询设计索引

本课针对客户订单分页查询设计复合索引,并使用 EXPLAIN ANALYZE 验证扫描方式。完成后,你将能根据过滤、关联和排序条件选择索引列顺序,而不是盲目为每个字段建索引。

学习目标

  • 从查询条件中提取索引候选列
  • 正确安排复合索引的列顺序
  • 使用执行计划验证索引是否真正降低扫描成本

从访问路径提取索引

核心分页查询按 customer_id 等值过滤,再按 created_at DESC, id DESC 排序并限制返回数量。因此索引应先放等值列,再放排序列。

订单明细通过 order_id 关联订单,也需要单列索引。唯一约束已经为订单号创建索引,不必重复创建同列索引。

创建核心索引

下面的 SQL 可直接执行,前提是前课的表已经存在。

sql
CREATE INDEX idx_orders_customer_created_id
ON orders (customer_id, created_at DESC, id DESC);

CREATE INDEX idx_order_item_order_id
ON order_item (order_id);

预期结果

text
CREATE INDEX
CREATE INDEX

索引名称可以按团队规范调整,但同一模式下必须保持唯一。

验证分页查询

使用与真实接口接近的条件执行计划。将 <ID> 替换为存在且拥有较多订单的客户主键,否则优化器可能因数据量太小而选择顺序扫描。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    order_no,
    status,
    total_amount,
    created_at
FROM orders
WHERE customer_id = <ID>
ORDER BY created_at DESC, id DESC
LIMIT 20;

预期结果

text
执行计划应优先出现 Index Scan 或 Index Only Scan,
索引名称为 idx_orders_customer_created_id,
并在取得少量记录后完成 LIMIT。
实际成本、耗时、行数和缓冲区统计会随数据分布变化。

不要把具体成本数字写成固定验收值。索引验证应关注扫描类型、实际读取行数、排序节点和缓冲区访问是否符合预期。

判断索引列顺序

把 created_at 放在 customer_id 前面,会削弱按客户等值过滤的能力。当前顺序可以先定位客户范围,再按索引顺序直接读取最新订单。

复合索引不是所有列的任意组合。应根据最常见查询中的等值、范围和排序顺序设计,并检查是否能同时减少过滤和排序成本。

避免过度索引

每个索引都会占用磁盘,并增加插入、更新和删除成本。低选择性的状态列通常不适合单独建索引,但若只查询少量未处理订单,可考虑部分索引。

例如只针对待处理订单的场景,可在确认查询频繁后再创建 WHERE status = 'PENDING' 的部分索引,而不是提前为所有状态建立多个索引。

建立验证闭环

索引上线前后应比较同一 SQL、相近参数和相近数据量下的执行计划。还要检查慢查询日志或统计视图,确认优化的是高频或高成本查询。

当查询模式变化时,应重新评估索引。索引设计是持续验证过程,不是建表阶段的一次性工作。

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

目录与阅读设置

阅读设置

17

记录一个想法

为核心查询设计索引

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

登录哪都通