数据库

查询优化

选择合适的索引来提升查询性能。

在使用PostgreSQL或任何关系型数据库时，索引是提升查询性能的关键。将索引与常见查询模式对齐，可以使数据检索速度提升一个数量级。

本指南旨在：

  • 帮助识别查询中可能通过索引优化的部分
  • 介绍有助于识别有用索引的工具

这不是一份全面的资源，而是您优化之旅的有用起点。

如果您是查询优化的新手，可能会对我们的index_advisor工具感兴趣，它能自动检测可提升特定查询性能的索引。

示例查询

考虑以下从两个表中检索客户姓名和购买日期的示例查询：

1
2
3
4
5
6
7
8
9
select  a.name,  b.date_of_purchasefrom  customers as a  join orders as b on a.id = b.customer_idwhere a.sign_up_date > '2023-01-01' and b.status = 'shipped'order by b.date_of_purchaselimit 10;

在这个查询中，有几个部分可能通过索引来优化性能：

where子句：

where子句根据特定条件筛选行，对涉及的列建立索引可以改善这一过程：

  • a.sign_up_date：如果经常按sign_up_date筛选，为此列建立索引可以加速查询
  • b.status：如果该列具有多样化的值，为状态建立索引可能有益
1
2
3
create index idx_customers_sign_up_date on customers (sign_up_date);create index idx_orders_status on orders (status);

join 列优化

为用于表连接的列创建索引可以帮助Postgres避免在连接表时进行全表扫描。

  • 为 a.id 和 b.customer_id 创建索引可能会提升这个查询中连接操作的性能
  • 注意：如果 a.id 是 customers 表的主键，那么它已经有索引了
1
create index idx_orders_customer_id on orders (customer_id);

order by 子句优化

排序操作也可以通过索引来优化：

  • 在 b.date_of_purchase 上创建索引可以改善排序过程，特别是当使用 limit 子句返回部分行时效果更明显
1
create index idx_orders_date_of_purchase on orders (date_of_purchase);

关键概念

以下是一些需要牢记的概念和工具，它们能帮助您选择最适合的索引类型，并衡量索引的实际效果：

分析查询计划

使用 explain 命令来理解查询的执行过程。查找性能瓶颈，例如顺序扫描(Sequential Scans)或高成本数值。如果创建的索引没有降低查询计划的成本，就应该移除它。

例如：

1
explain select * from customers where sign_up_date > 25;

使用合适的索引类型

Postgres提供了多种索引类型，如B-tree、Hash、GIN等。选择最适合您数据和查询模式的类型。使用正确的索引类型可以带来显著差异。例如，在一个不常更新的表上为总是递增的字段(如orders表中的created_at)使用BRIN索引，通常会产生比等效的默认B-tree索引小10倍以上的索引，这能带来更好的可扩展性。

1
create index idx_orders_created_at ON customers using brin(created_at);

部分索引

对于频繁查询数据子集的情况，部分索引可能比索引整个列更快且更小。部分索引包含一个 where 子句来筛选包含在索引中的值。请注意，查询的 where 子句必须与索引匹配才能使用该索引。

1
2
create index idx_orders_status on orders (status)where status = 'shipped';

复合索引

如果需要对多列进行过滤或连接，复合索引可以避免Postgres在识别相关行时引用多个索引。

1
create index idx_customers_sign_up_date_priority on customers (sign_up_date, priority);

过度索引

避免对不常操作的列建立索引的冲动。虽然索引可以加速读取，但也会减慢写入速度，因此在做出索引决策时需要平衡这些因素。

统计信息

Postgres维护了一组关于表内容的统计信息。查询规划器使用这些统计信息来决定何时使用索引比扫描整个表更高效。如果收集的统计信息与现实偏离太远，查询规划器可能会做出错误的决策。为避免这种风险，您可以定期对表执行 analyze 操作。

1
analyze customers;

通过遵循本指南，您将能够识别哪些地方可以通过索引优化查询并提升Postgres性能。请记住每个数据库都是独特的，因此始终要考虑查询的具体上下文和使用场景。