PostgreSQL / 系统课时
为核心查询设计索引
为核心查询设计索引
本课针对客户订单分页查询设计复合索引,并使用 EXPLAIN ANALYZE 验证扫描方式。完成后,你将能根据过滤、关联和排序条件选择索引列顺序,而不是盲目为每个字段建索引。
学习目标
- 从查询条件中提取索引候选列
- 正确安排复合索引的列顺序
- 使用执行计划验证索引是否真正降低扫描成本
从访问路径提取索引
核心分页查询按 customer_id 等值过滤,再按 created_at DESC, id DESC 排序并限制返回数量。因此索引应先放等值列,再放排序列。
订单明细通过 order_id 关联订单,也需要单列索引。唯一约束已经为订单号创建索引,不必重复创建同列索引。
创建核心索引
下面的 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);预期结果
CREATE INDEX
CREATE INDEX索引名称可以按团队规范调整,但同一模式下必须保持唯一。
验证分页查询
使用与真实接口接近的条件执行计划。将 <ID> 替换为存在且拥有较多订单的客户主键,否则优化器可能因数据量太小而选择顺序扫描。
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;预期结果
执行计划应优先出现 Index Scan 或 Index Only Scan,
索引名称为 idx_orders_customer_created_id,
并在取得少量记录后完成 LIMIT。
实际成本、耗时、行数和缓冲区统计会随数据分布变化。不要把具体成本数字写成固定验收值。索引验证应关注扫描类型、实际读取行数、排序节点和缓冲区访问是否符合预期。
判断索引列顺序
把 created_at 放在 customer_id 前面,会削弱按客户等值过滤的能力。当前顺序可以先定位客户范围,再按索引顺序直接读取最新订单。
复合索引不是所有列的任意组合。应根据最常见查询中的等值、范围和排序顺序设计,并检查是否能同时减少过滤和排序成本。
避免过度索引
每个索引都会占用磁盘,并增加插入、更新和删除成本。低选择性的状态列通常不适合单独建索引,但若只查询少量未处理订单,可考虑部分索引。
例如只针对待处理订单的场景,可在确认查询频繁后再创建 WHERE status = 'PENDING' 的部分索引,而不是提前为所有状态建立多个索引。
建立验证闭环
索引上线前后应比较同一 SQL、相近参数和相近数据量下的执行计划。还要检查慢查询日志或统计视图,确认优化的是高频或高成本查询。
当查询模式变化时,应重新评估索引。索引设计是持续验证过程,不是建表阶段的一次性工作。