Skip to content
Database

查询优化

Choosing indexes to improve your query performance.

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

🌐 When working with Postgres, or any relational database, indexing is key to improving query performance. Aligning indexes with common query patterns can speed up data retrieval by an order of magnitude.

本指南的目的是：

🌐 This guide is intended to:

  • 帮助识别查询中有潜力通过索引优化的部分
  • 引入工具来帮助识别有用的索引

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

🌐 This is not a comprehensive resource, but rather a helpful starting point for your optimization journey.

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

🌐 If you're new to query optimization, you may be interested in index_advisor, our tool for automatically detecting indexes that improve performance on a given query.

示例查询 #

🌐 Example query

考虑下面这个示例查询，它从两个表中获取客户名称和购买日期：

🌐 Consider the following example query that retrieves customer names and purchase dates from two tables:

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;

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

🌐 In this query, there are several parts that indexes could likely help in optimizing the performance:

where#

🌐 where clause:

where 子句根据特定条件筛选行，对相关列进行索引可以提升这个过程的效率：

🌐 The where clause filters rows based on certain conditions, and indexing the columns involved can improve this process:

  • 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#

🌐 join columns

在用来连接表的列上建立索引可以帮助 Postgres 在连接表时避免全表扫描。

🌐 Indexes on the columns used for joining tables can help Postgres avoid scanning tables in their entirety when connecting tables.

  • 给 a.id 和 b.customer_id 建索引可能会提高这个查询中连接的性能。
  • 注意，如果 a.id 是 customers 表的主键，它已经被建立索引了
1
create index idx_orders_customer_id on orders (customer_id);

order by#

🌐 order by clause

排序也可以通过建立索引来优化：

🌐 Sorting can also be optimized by indexing:

  • b.date_of_purchase上的索引可以改善排序过程，尤其是当使用limit子句返回部分行时特别有用。
1
create index idx_orders_date_of_purchase on orders (date_of_purchase);

关键概念 #

🌐 Key concepts

这里有一些概念和工具，可以帮助你找出最适合的指数，并衡量你的指数所产生的影响：

🌐 Here are some concepts and tools to keep in mind to help you identify the best index for the job, and measure the impact that your index had:

分析查询计划 #

🌐 Analyze the query plan

使用 explain 命令来了解查询的执行情况。注意查找慢的部分，比如顺序扫描或高成本的操作。如果创建索引并不能降低查询计划的成本，就把它删除。

🌐 Use the explain command to understand the query's execution. Look for slow parts, such as Sequential Scans or high cost numbers. If creating an index does not reduce the cost of the query plan, remove it.

例如：

🌐 For example:

1
explain select * from customers where sign_up_date > 25;

使用合适的索引类型 #

🌐 Use appropriate index types

Postgres 提供了各种索引类型，比如 B 树、哈希、GIN 等。选择最适合你数据和查询模式的类型。使用正确的索引类型可以带来显著的差异。例如，在一个总是递增且更新不频繁的字段上使用 BRIN 索引——就像 orders 表上的 created_at——通常会生成比默认 B 树索引小 10 倍以上的索引。这意味着更好的可扩展性。

🌐 Postgres offers various index types like B-tree, Hash, GIN, etc. Select the type that best suits your data and query pattern. Using the right index type can make a significant difference. For example, using a BRIN index on a field that always increases and lives within a table that updates infrequently - like created_at on an orders table - routinely results in indexes that are +10x smaller than the equivalent default B-tree index. That translates into better scalability.

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

部分索引 #

🌐 Partial indexes

对于经常针对数据子集的查询，部分索引可能比对整个列建立索引更快、更小。部分索引包含一个 where 子句，用于过滤包含在索引中的值。注意，查询的 where 子句必须与索引匹配，才能被使用。

🌐 For queries that frequently target a subset of data, a partial index could be faster and smaller than indexing the entire column. A partial index contains a where clause to filter the values included in the index. Note that a query's where clause must match the index for it to be used.

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

复合索引 #

🌐 Composite indexes

如果在多个列上进行筛选或连接，复合索引可以防止 Postgres 在查找相关行时参考多个索引。

🌐 If filtering or joining on multiple columns, a composite index prevents Postgres from referring to multiple indexes when identifying the relevant rows.

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

过度索引 #

🌐 Over-Indexing

尽量不要给很少操作的列建立索引。虽然索引可以加快读取速度，但也会减慢写入速度，所以在做索引决策时，平衡这些因素很重要。

🌐 Avoid the urge to index columns you operate on infrequently. While indexes can speed up reads, they also slow down writes, so it's important to balance those factors when making indexing decisions.

统计 #

🌐 Statistics

Postgres 会维护一组关于你表内容的统计信息。这些统计信息被查询规划器用来决定什么时候使用索引比扫描整个表更高效。如果收集到的统计信息与实际情况差距太大，查询规划器可能会做出不好的决策。为了避免这种风险，你可以定期对表进行 analyze。

🌐 Postgres maintains a set of statistics about the contents of your tables. Those statistics are used by the query planner to decide when it's is more efficient to use an index vs scanning the entire table. If the collected statistics drift too far from reality, the query planner may make poor decisions. To avoid this risk, you can periodically analyze tables.

1
analyze customers;

通过遵循本指南，你将能够分辨出在哪些地方索引可以优化查询并提升你的 Postgres 性能。记住，每个数据库都是独特的，所以总是要考虑你的查询的具体情况和使用场景。

🌐 By following this guide, you'll be able to discern where indexes can optimize queries and enhance your Postgres performance. Remember that each database is unique, so always consider the specific context and use case of your queries.