价格
登录 开始使用
文档 学习
课程
Metabase 教程
提升您的数据技能
备忘单

SQL 性能调优：更快、更经济的查询技术

提高查询性能的技巧和窍门：索引、物化视图等。

SQL 性能调优到底是什么？

SQL 性能调优旨在使您的数据库查询运行更快并使用更少的资源。可以将其视为调整您的汽车，使其行驶更顺畅，油耗更低——只不过在这里，您是在确保您的 SQL 查询不会拖慢一切。

优化查询有什么好处？

  • 加快大型数据库中的数据检索速度
  • 减轻服务器负载，防止其被拖垮。
  • 通过出色的性能让您的用户满意。
  • 通过减少计算能力的使用来节省资金，尤其是在云端。

随着数据增长和查询变得更加复杂，优化查询可以极大地影响您的应用程序或分析工具的性能。

如何使用 EXPLAIN 查看查询执行方式

Explain plan diagram

在 SQL 中，您指定要获取什么数据，但您不指定_如何_获取数据。每个数据仓库都有一个查询规划器，其工作是确定如何执行查询。这个查询规划器根据执行时间、使用的资源和/或数据库预设来确定运行查询的最佳方式。例如，查询规划器将决定是应该读取表中的所有行，还是根据特定条件将搜索范围缩小到特定的一组行。

要查看数据库将如何执行您的查询，您可以在查询前加上 EXPLAIN。查询规划器将列出它将执行的操作、执行顺序、使用的索引等。还有 EXPLAIN ANALYZE，它为您提供实际的查询执行统计信息。但要获取这些统计信息，数据库将_实际执行查询_，这对于修改数据的查询来说可能会耗费时间甚至不合需要。

为了看到 EXPLAIN 的实际操作，我们首先在 PostgreSQL 数据库中创建一些虚假数据（其他数据库可能也可以，但您可能需要调整代码）。运行以下语句（您需要数据仓库的写入权限）。

DROP TABLE IF EXISTS data_table;
CREATE TABLE data_table (
    id SERIAL PRIMARY KEY,
    date_field DATE NOT NULL,
    category TEXT NOT NULL CHECK (category IN ('A', 'B', 'C')),
    numeric_value NUMERIC NOT NULL
);

INSERT INTO data_table (date_field, category, numeric_value)
SELECT
    CURRENT_DATE - (random() * 365)::INT, -- Random date within the last year
    CASE FLOOR(random() * 3)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        ELSE 'C'
    END, -- Random category
    (random() * 1000)::NUMERIC -- Random numeric value between 0 and 1000
FROM generate_series(1, 50000000);

此查询可能需要一段时间。要检查它是否工作，请运行

SELECT
  *
FROM
  data_table
LIMIT
  10;

您会看到类似以下内容

| id  | date_field        | category | numeric_value |
| --- | ----------------- | -------- | ------------- |
| 1   | August 2, 2024    | A        | 874.17        |
| 2   | October 31, 2024  | A        | 762.03        |
| 3   | August 23, 2024   | A        | 718.73        |
| 4   | February 6, 2025  | C        | 334.45        |
| 5   | August 28, 2024   | A        | 59.4          |
| 6   | August 8, 2024    | A        | 972.74        |
| 7   | October 18, 2024  | B        | 296.99        |
| 8   | November 27, 2024 | B        | 858.26        |
| 9   | September 5, 2024 | C        | 137.84        |
| 10  | February 24, 2025 | C        | 701.68        |

让我们按 category 对 numeric_value 进行简单求和，看看需要多长时间。查询如下

SELECT
  category AS "Category",
  SUM(numeric_value) AS "Sum"
FROM
  data_table
GROUP BY
  category
ORDER BY
  category ASC

您会看到类似以下内容

| Category | Sum              |
| -------- | ---------------- |
| A        | 8,336,275,140.07 |
| B        | 8,330,139,598.5  |
| C        | 8,334,188,258.65 |

在我们的 PostgreSQL 17（2 个 CPU 核心和 2GB 内存）上运行此查询大约需要 8 秒——相当长的时间。

现在让我们在查询上运行 EXPLAIN。EXPLAIN 将输出查询计划，以及其成本和有关查询的其他数据。只需在查询前加上 EXPLAIN 即可

EXPLAIN
    SELECT
        category AS "Category",
        SUM(numeric_value) AS "Sum"
    FROM
        data_table
    GROUP BY
        category
    ORDER BY
        category ASC;

您将看到以下输出（具体取决于您当前的部署，输出可能会有所不同）

QUERY PLAN
Finalize GroupAggregate  (cost=631972.08..631972.87 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=631972.08..631972.78 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=630972.06..630972.07 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=630972.00..630972.04 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Seq Scan on data_table  (cost=0.00..526805.33 rows=20833333 width=13)
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN 的输出应从下到上阅读。此查询计划表示

  1. 数据库将首先在可用的两个核心上并行 (Workers Planned: 2) 顺序扫描 (Seq Scan) 表中的所有记录。

  2. 然后，数据库将聚合 (HashAggregate) 数据，并在每个 worker 上单独执行 (Partial)。

    数据库必须进行聚合，因为我们包含了 GROUP BY 子句和聚合函数 SUM。聚合可以并行完成，因为求和是一种可以并行化的操作：对两个分区求和的总和与对所有数据求和的结果相同。

  3. 然后，数据库将对每个 worker 上的聚合结果进行单独排序 (Sort)。

    Sort 出现在这里，令人惊讶的是，_不是_因为我们包含了 ORDER BY 子句——如果您删除 ORDER BY 子句，Sort 步骤仍然存在。数据库对聚合结果进行排序，因为它需要合并来自两个 worker 的结果，并且在结果已排序时这样做效率更高。

    如果不是 ORDER BY category，而是 ORDER BY "Sum"，查询计划中将有_两个_排序操作——在 GroupAggregate 之后还有一个额外的排序操作。

  4. 接下来，数据库将合并来自两个 worker 的结果 (Gather Merge)。

    此时，数据库尚未汇总每个类别的总和。我们知道它还没有，因为聚合后 rows 值为 3：每个工作进程有三行，每行代表一个类别（A、B 和 C），但在 Gather Merge 之后，我们有 6 rows，所以数据库尚未合并每个工作进程的总和。

  5. 最后，数据库将对来自两个 worker 的结果进行分组和聚合 (GroupAggregate)。

从性能角度来看，此输出告诉我们

  • 查询处理器使用了 2 个核心 (Workers Planned: 2)。
  • 成本最高的操作是 Parallel Seq Scan (0.00..526805.33)。
  • 其他操作对整体查询时间的影响可以忽略不计（例如，HashAggregate 的成本为 630972.00..630972.04——两个数字之间的差异，即成本，与 Parallel Seq Scan 的成本相比非常小。）

那么，我们如何才能让这个查询在人们每天早上查看仪表板时不再拖垮数据库呢？数据库_必须_读取表中的每一行来求和，所以我们无法减少扫描的总行数。我们能做的最好的是加快扫描速度。我们的选择包括

  • 增加 CPU 核心数量，从而使每个核心需要处理的行数更少。
  • 切换到针对单列顺序扫描进行优化的数据库架构（如列式数据库）。

如果基础设施更改是不可能的，您可以提前预计算聚合结果——如果人们经常运行查询，这会很有用——并使用缓存结果或物化视图。

为昂贵的 JOIN、CTE 和视图创建物化视图

A view

物化视图是您可以_在数据仓库中_保存的预计算查询。物化视图通过存储计算结果供人们查询，而不是每次查询数据库时都计算行，从而减轻了数据库的压力。您可以定期刷新物化视图以使其结果与新数据保持同步。

让我们用我们之前做的查询来创建一个物化视图

CREATE MATERIALIZED VIEW preprocessed_sum_of_numeric_value_by_category AS
    SELECT
        category AS "Category",
        SUM(numeric_value) AS "Sum"
    FROM
        data_table
    GROUP BY
        category
    ORDER BY
        category ASC

这将保存查询结果

-- preprocessed_sum_of_numeric_value_by_category

| Category | Sum              |
| -------- | ---------------- |
| A        | 8,336,275,140.07 |
| B        | 8,330,139,598.5  |
| C        | 8,334,188,258.65 |

现在我们所要做的就是从这个物化视图中选择来获取每个类别的总和

SELECT
  *
FROM
  preprocessed_sum_of_numeric_value_by_category;

查询物化视图可以将时间从几秒缩短到几毫秒。

根据您的数据库，物化视图可能会或可能不会在底层数据更改时更新。例如，如果您使用的是 PostgreSQL，您需要更新您的物化视图。要重新计算结果，您需要一个脚本以一定的频率运行 REFRESH MATERIALIZED VIEW preprocessed_sum_of_numeric_value_by_category。您刷新结果的频率取决于底层数据更改的频率以及人们检查结果的频率。

以下是一些您应该考虑物化视图的情况

连接

表连接是关系数据库世界中最美妙的事情之一（至少我们是这样认为的）。连接允许您将业务组织成模块化部分，只需一个简单的关键字即可将其组合在一起。但是连接也可能非常危险：对一个庞大的表进行连接可能会严重拖慢您的数据仓库。如果您每次查询都需要连接两三个以上的表，请考虑将带有连接的基础查询物化为物化视图。

CTEs

CTEs 非常适合模块化您的代码。但是，您的数据仓库必须在每次运行这些查询时计算这些 CTE。如果您发现自己每天都在运行相同的充满 CTE 的查询，那么是时候以这样的方式对您的数据进行建模，即您的查询只是一个简单的 SELECT * FROM table，其中 table 是一个物化视图或您使用这些 CTE 的结果创建的表。

视图（非物化视图）

不必每次查询都编写数百行 SQL，您只需简单地从保存的视图中选择行即可，瞧：您得到相同的结果。视图只是抽象；它们不提供任何性能优势。当您想要降低查询的复杂性时，请使用视图。如果您经常使用这些结果，请考虑物化视图。

如何通过索引常用查询列来加快查询速度

An index

您可能在某个时候听说过索引。您的查询很慢，一位数据库专家告诉您创建索引，然后：查询变快了。索引就像一个查找表，可以帮助您的数据库快速找到特定行，而无需扫描整个表。就像书籍的索引帮助您查找主题而无需阅读每一页一样，数据库索引直接指向数据存储的位置。当您需要查找_部分_数据，而不是_所有_数据时，它们效果很好。

当您为一个或多个列添加索引时，数据库将创建这些列的副本，该副本针对查找特定值进行了优化，并且还包含一个指向从其他（未索引）列获取值的指针。

例如，假设您有一个包含客户数据（包含客户 ID、姓名、注册日期等）的表。如果您想查找具有特定姓名的客户，数据库将不得不扫描每一条记录，并将姓名与您请求的姓名进行比较，直到找到匹配项。但是，如果您在姓名列上放置索引，数据库将有其他方法来执行此操作。索引的具体实现可能有所不同，但大致来说，您可以将索引视为所有姓名的_排序_列表，与指向数据库中具有该姓名的精确记录的指针一起存储。由于索引中的姓名列表已排序，数据库查找您请求的姓名所需的时间要少得多。一旦找到姓名，它将使用索引中存储的指针来检索该客户的其余数据。

您的数据库将始终在主键上拥有索引（这就是为什么通过 ID 查找记录通常比例如通过名字和姓氏查找更快）。

让我们回到前面的例子：表中所有类别的 SUM(numeric_value)。索引能让这个问题更快吗？绝对不能，因为这个问题仍然需要遍历整个表才能计算结果。

现在，我们假设有人想要执行一个只查找表一部分的查询：按类别划分的数值总和，但仅限于过去 60 天。我们来试试看

SELECT
  category AS "Category",
  SUM(numeric_value) AS "Sum"
FROM
  data_table
WHERE
  date_field <= CURRENT_DATE + INTERVAL '1' DAY
  AND date_field >= CURRENT_DATE - INTERVAL '60' DAY
GROUP BY
  category
ORDER BY
  category ASC

那花了一段时间！让我们运行 EXPLAIN 看看发生了什么

QUERY PLAN
Finalize GroupAggregate  (cost=857409.42..857410.21 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=857409.42..857410.12 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=856409.40..856409.41 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=856409.34..856409.38 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Seq Scan on data_table  (cost=0.00..839305.33 rows=3420801 width=13)
                          Filter: ((date_field <= (CURRENT_DATE + '1 day'::interval day)) AND (date_field >= (CURRENT_DATE - '60 days'::interval day)))
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

我们再次看到耗时的顺序扫描操作。在 date_field 上创建索引会有帮助吗？

创建索引

让我们在 date_field 列上创建一个索引

CREATE INDEX idx_data_table_date
ON data_table (date_field);

数据库需要一些时间来创建索引，并且在索引完成之前，它会锁定表以防止任何写入操作。

您可以使用查询检查索引

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE tablename = 'data_table'

编写利用索引的查询

一旦索引建立，我们再次运行查询，看看它是否使用了索引并缩短了返回结果所需的时间。

如果您再次运行 EXPLAIN，您会发现索引甚至没有被使用，并且您仍然得到并行顺序扫描（查询仍然很慢）。

让我们看看是否可以通过指定非相对日期来让数据库使用索引。

EXPLAIN
SELECT
  category AS "Category",
  SUM(numeric_value) AS "Sum"
FROM
  data_table
WHERE
  date_field BETWEEN DATE '2025-01-01' AND DATE '2025-03-01'
GROUP BY
  category
ORDER BY
  category ASC;

这给了我们

QUERY PLAN
Finalize GroupAggregate  (cost=649104.46..649105.24 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=649104.46..649105.16 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=648104.44..648104.44 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=648104.38..648104.41 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Seq Scan on data_table  (cost=0.00..630972.00 rows=3426475 width=13)
                          Filter: ((date_field >= '2025-01-01'::date) AND (date_field <= '2025-03-01'::date))
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

使用特定日期将成本从 cost=0.00..839305.33 降至 cost=0.00..630972.00。

但是，引擎_仍然_在进行全表扫描吗？是的，它正在进行（Parallel Seq Scan on data_table）。为了让数据库使用索引，我们需要缩小时间范围。我们将 BETWEEN 条件更改为将时间范围缩小到两周，例如 2025-03-01 到 2025-03-15。再次运行 EXPLAIN。您会看到类似以下内容

QUERY PLAN
Finalize GroupAggregate  (cost=633296.94..633297.72 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=633296.94..633297.64 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=632296.92..632296.92 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=632296.86..632296.89 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Bitmap Heap Scan on data_table  (cost=27900.15..628034.34 rows=852503 width=13)
                          Recheck Cond: ((date_field >= '2025-03-01'::date) AND (date_field <= '2025-03-15'::date))
                          ->  Bitmap Index Scan on idx_data_table_date  (cost=0.00..27388.65 rows=2046008 width=0)
                                Index Cond: ((date_field >= '2025-03-01'::date) AND (date_field <= '2025-03-15'::date))
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

看到 Index Cond 了吗？_现在_我们正在利用我们创建的索引。以前是顺序扫描，现在是索引扫描了。

看起来很神奇，对吧？嗯……没那么神奇。因为正如您已经看到的，如果您查询的时间跨度足够长，您仍然会触发全表扫描。这其中是有原因的。

我们来想象一下以下场景：您有一本一千页的书。您是一个狂热的读者，每天阅读一百页。现在假设您想在书中查找_特定行_。您不能使用索引来查找这些行；您需要扫描整本书。现在我们假设，您不是要查找特定行，而是需要查找涵盖某个主题的章节：在这种情况下，索引就派上用场了。

等等，但这些是电脑，对吧？就像，超快的阅读器？我不能为每一列都创建一个索引，以便它涵盖所有可能的情况吗？嗯，您当然_可以_，但所有这些索引都会阻碍表的写入能力：每次有新行时，引擎都必须更新所有索引。此外，数据库将需要越来越多的空间来保存这些索引。

所以索引可以提供很大帮助，但它们有其局限性。以下是一些加快查询速度的其他方法。

其他提高 SQL 查询性能的技术

只请求您需要的数据

更多的行意味着更慢的查询（大多数时候）。就像一本教科书，书的页数越多，您阅读它所需的时间就越多。数据库的工作方式相同。因此，只请求您需要的数据。减少行数的一个常见方法是按较小的日期范围进行过滤。

缓存结果

缓存只是保存查询结果，以便您将来可以检索结果。您决定结果保持有效的时间，直到它们需要通过另一次对数据库的调用来刷新。检查您的 BI 工具以获取缓存选项。

根据您当前的需求（而非永久需求）建模您的数据

这里的数据建模是指您如何将数据组织成表，以及这些表如何相互关联。您的分析需求将与您的操作需求不同（您的应用程序的数据模型可能不是分析查询的最佳模型）。建模良好的数据可以真正提高性能，但没有完美的模型。一个好的数据模型是能够解决您今天（以及未来几个月）遇到的问题的模型。存活下来的公司将随着时间的推移而发展和演变，因此您过去做出的设计选择可能不再适合未来。

查看数据库的工作负载管理功能

数据库被设计为同时为多个客户端运行查询。不过，有一个问题：对于数据库引擎来说，一个大型查询和一个快速查询具有相同的优先级，因此您可能会遇到大型查询耗尽所有服务器资源，而快速查询则耐心等待资源释放的情况。

一些数据库引擎提供了工作负载管理功能，允许您将查询分配到不同的队列，这可以减轻那些大型查询对服务器的冲击。

避免在您的应用程序生产数据库上运行查询

除非您是一个只有少数人使用您的应用程序的初创公司，否则我们不建议您在应用程序在生产中使用的同一数据库上运行查询。昂贵的查询可能会在您的日常操作中造成混乱。我们建议您至少创建一个单独的只读数据仓库（通常称为只读副本，因为它复制了您的生产数据库），并将您的分析工具连接到它。

对于数据湖，使用分区键

如果您正在使用数据湖，这里概述的基本原则同样适用。此外，如果您的数据湖具有分区功能，您应该使用分区键来帮助您避免大量读取。为了最大限度地利用并行化，您需要在所有查询中使用分区键。

考虑列式数据仓库

传统的关​​系型数据仓库，如 PostgreSQL，是出色的全能型产品，它们可以带您走很远的分析之路。但是，如果查询时间变得令人难以忍受——并且您已经尝试了本文中的所有技术——请考虑将您的分析工作负载转移到列式引擎。列式数据仓库专为分析工作负载而设计，因此它们可以为您带来巨大的性能提升（当然，价格不菲）。

有关列式存储的性能提示，您需要参考特定列式引擎的文档，因为每个引擎都有其独特的特点和定价。

您无法让所有查询都快如闪电

您可以做的是在以下几点之间取得平衡

  • 基础设施成本：即使您投入更多核心和内存来解决问题，您的查询速度也会停滞不前。
  • 您创建的索引：如果索引过多，会降低写入速度。
  • 数据的陈旧程度：如果您不需要实时更新数据，则可以保存和重用结果。

延伸阅读

这有帮助吗？

感谢您的反馈！
分析师每周技巧
获取可行的见解
关于 AI 和数据的资讯，直接发送到您的收件箱
© . This site is unofficial and not affiliated with Metabase, Inc.