数据库

查询优化

选择索引以提高查询性能。

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

本指南旨在

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

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

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

示例查询#

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

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

在此查询中，有几个部分可以通过索引优化性能

where 子句：#

where 子句根据某些条件过滤行，并且索引涉及的列可以改进此过程

  • a.sign_up_date：如果经常按 sign_up_date 过滤，则索引此列可以加快查询速度。
  • b.status：如果该列具有不同的值，则索引状态可能会有益。
1
create index idx_customers_sign_up_date on customers (sign_up_date);
2
3
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 命令来了解查询的执行情况。查找缓慢的部分，例如顺序扫描或高成本数字。如果创建索引不能降低查询计划的成本，请将其删除。

例如

1
explain select * from customers where sign_up_date > 25;

使用适当的索引类型#

Postgres 提供了各种索引类型，例如 B 树、哈希、GIN 等。选择最适合您的数据和查询模式的类型。使用正确的索引类型可以产生重大影响。例如，在始终递增且在很少更新的表内更新的字段上使用 BRIN 索引（例如 orders 表上的 created_at），通常会产生比等效的默认 B 树索引小 +10 倍的索引。这转化为更好的可扩展性。

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

部分索引#

对于经常针对数据子集进行查询的查询，部分索引可能比索引整个列更快、更小。部分索引包含一个 where 子句，用于过滤包含在索引中的值。请注意，只有当查询的 where 子句与索引匹配时，才能使用它。

1
create index idx_orders_status on orders (status)
2
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 性能。请记住，每个数据库都是唯一的，因此请始终考虑查询的具体上下文和用例。