查询优化

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

本指南旨在：

  • 帮助识别查询中可能通过索引改进的部分
  • 引入工具以帮助识别有用的索引

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

如果您不熟悉查询优化，可能会对 index_advisor 感兴趣，这是我们用于自动检测可提高给定查询性能的索引的工具。

示例查询

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

1select
2  a.name,
3  b.date_of_purchase
4from
5  customers a
6  join orders b
7    on a.id = b.customer_id
8where
9  a.sign_up_date > '2023-01-01'
10  and b.status = 'shipped'
11order by
12  b.date_of_purchase;
13limit 10

在此查询中，索引可能有助于优化性能的几个部分：

where 子句:#

where 子句根据某些条件筛选行，对所涉及的列编制索引可以改进此过程：

  • a.sign_up_date: 如果按sign_up_date筛选很常见，则索引此列可以加快查询速度。
  • b.status: 如果列具有不同的值，则对状态进行索引可能会有所帮助。
1create index idx_customers_sign_up_date on customers (sign_up_date);
2
3create index idx_orders_status on orders (status);

联接列

用于联接表的列的索引可以帮助 Postgres 避免在连接表时扫描整个表。

  • 索引和b.customer_id可能会提高此查询中联接的性能。
  • 请注意，如果 a.id 是 customers 表的主键，则该表已编制索引
1create index idx_orders_customer_id on orders (customer_id);

order by Clause#

还可以通过索引来优化排序：

  • b.date_of_purchase索引可以改进排序过程，当使用限制子句返回行的子集时，该索引特别有用。
1create index idx_orders_date_of_purchase on orders (date_of_purchase);

关键概念

以下是一些需要牢记的概念和工具，可帮助您确定作业的最佳索引，并衡量索引的影响：

分析查询计划

使用 explain 命令了解查询的执行情况。寻找速度较慢的部件，例如顺序扫描或高成本数字。如果创建索引不能降低查询计划的成本，请将其删除。

例如:

1explain select * from customers where sign_up_date > 25;

使用适当的索引类型

Postgres 提供各种索引类型，如 B-tree, Hash, GIN, etc 等。选择最适合您的数据和查询模式的类型。使用正确的索引类型可以产生重大影响。例如，对始终增加且位于不经常更新的表中的字段（如订单表上的created_at）使用 BRIN 索引通常会导致索引比等效的默认 B 树索引小 +10 倍。这意味着更好的可扩展性。

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

部分索引

对于经常以数据子集为目标的查询，部分索引可能比为整个列编制索引更快、更小。部分索引包含一个 where 子句，用于筛选索引中包含的值。请注意，查询的 where 子句必须与索引匹配才能使用。

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

Composite indexes#

如果对多个列进行筛选或联接，则复合索引会阻止 Postgres 在标识相关行时引用多个索引。

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

Over-Indexing#

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

统计

Postgres 维护一组有关表内容的统计信息。查询规划器使用这些统计信息来决定何时使用索引与扫描整个表更有效。如果收集的统计信息与实际情况相差太远，查询规划器可能会做出错误的决策。为了避免这种风险，您可以定期分析表。

1analyze customers;

通过遵循本指南，您将能够辨别索引可以在哪些方面优化查询并增强 Postgres 性能。请记住，每个数据库都是唯一的，因此请始终考虑查询的特定上下文和用例。