优化查询计算

本文档提供了优化查询性能的最佳实践。

查询完成后，您可以在 Google Cloud 控制台中查看查询计划。还可使用 INFORMATION_SCHEMA.JOBS* 视图或 jobs.get REST API 方法请求执行详细信息。

查询计划包括有关查询阶段和步骤的详细信息。这些详细信息可帮助您确定改善查询性能的方法。例如，如果您发现某个阶段写入的输出比其他阶段多得多，则可能意味着您需要在查询的早期阶段进行过滤。

如需详细了解查询计划并通过示例了解查询计划信息如何帮助您改善查询性能，请参阅获取查询性能数据分析。根据查询性能数据分析解决问题后，您可以通过执行以下任务来进一步优化查询：

• 减少要处理的数据
• 优化查询操作
• 减少查询的输出
• 使用 BigQuery BI Engine 预留
• 避免反 SQL 模式
• 指定表架构中的限制条件

减少处理的数据量

您可以使用以下各部分中介绍的选项来减少需要处理的数据。

避免使用 SELECT *

最佳做法：通过仅查询所需的列来控制投影。

投影是指您的查询所读取的列数。 投影过多列会导致额外（浪费）的 I/O 和实体化（写入结果）。

• 使用数据预览选项。如果您要进行数据实验或探索数据，请使用某一数据预览选项，而不要使用 SELECT *。
• 查询特定列。对 SELECT * 查询应用 LIMIT 子句不会影响读取的数据量。您需要支付读取整个表中的所有字节而产生的费用，并且查询读取的数据量会占用您的免费层级配额。 因此，更好的做法是仅查询所需的列。例如，使用 SELECT * EXCEPT 从结果中排除一个或多个列。
• 使用分区表。如果您确实需要查询表中的所有列，但只需要查询部分数据，请考虑使用以下方法：
  • 将查询结果实体化到一个目标表中，然后对该表执行查询。
  • 对表进行分区，并查询相关分区。例如，使用 WHERE _PARTITIONDATE="2017-01-01" 以仅查询 2017 年 1 月 1 日的分区。

• 使用 SELECT * EXCEPT。 查询部分数据或使用 SELECT * EXCEPT 可大幅减少查询所读取的数据量。除了节约费用外，减少数据 I/O 量以及查询结果所需的实体化作业量还可改善性能。

  SELECT * EXCEPT (col1, col2, col5)
  FROM mydataset.newtable
  

避免过多通配符表

最佳做法：查询通配符表时，您必须使用最详细的前缀。

借助精简的 SQL 语句使用通配符查询多个表。通配符表是指符合通配符表达式的表集合。如果数据集包含以下资源，则通配符表会非常有用：

• 多个具备兼容架构且命名类似的表
• 分片表

注意：如果您的数据允许，请使用时间分区表而不是分片表。如需了解详情，请参阅避免对表过度分片。

查询通配符表时，请在常用的表前缀后面指定通配符 (*)。例如，FROM bigquery-public-data.noaa_gsod.gsod194* 会查询自 20 世纪 40 年代起的所有表。

与使用较短前缀相比，使用更详细的前缀的效果要更好。例如，FROM bigquery-public-data.noaa_gsod.gsod194* 的效果要优于 FROM bigquery-public-data.noaa_gsod.*，因为与该通配符匹配的表更少。

避免按日期分片的表

最佳做法：请勿使用按日期分片的表（也称为以日期命名的表）代替时间分区表。

分区表的性能优于以日期命名的表。如果您创建按日期分片的表，BigQuery 就必须为每个以日期命名的表保留架构和元数据的副本。此外，使用以日期命名的表时，BigQuery 可能需要分别为每个要查询的表验证权限。该做法也会增加查询开销，影响查询性能。

避免对表过度分片

最佳做法：避免创建过多的表分片。如果您要按日期对表分片，请改为使用时间分区表。

表分片指将大型数据集分割为多个单独的表，并向每个表名称添加一个后缀。如果您要按日期对表分片，请改为使用时间分区表。

由于 BigQuery 存储空间的费用较低，因此与使用关系型数据库系统不同，您无需出于费用考虑而对表进行优化。任何费用优势均无法弥补因创建大量表分片而造成的的性能影响。

分片表需要 BigQuery 保留每个分片的架构、元数据和权限信息。由于为每个分片保留信息需要增加开销，因此对表过度分片可能会影响查询性能。

查询读取的数据量和数据源可能会影响查询性能和费用。

删减分区查询

最佳做法：查询分区表时，如需过滤分区表上的分区，请使用以下列：

• 对于注入时间分区表，请使用伪列 _PARTITIONTIME
• 对于诸如时间单位列和整数范围等分区表，请使用分区列。

对于时间单位分区表，您可以通过 _PARTITIONTIME 或分区列过滤数据，以指定日期或日期范围。例如，以下 WHERE 子句使用 _PARTITIONTIME 伪列指定 2016 年 1 月 1 日和 2016 年 1 月 31 日之间的分区：

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

该查询仅处理日期范围所指示的分区中的数据。过滤分区可提升查询性能并降低费用。

使用 JOIN 前减少数据

最佳做法：通过执行聚合，减少执行 JOIN 子句之前所处理的数据量。

将 GROUP BY 子句与聚合函数搭配使用会导致计算密集型查询，因为这些类型的查询使用 shuffle。由于这些查询是计算密集型查询，因此您必须仅在必要时使用 GROUP BY 子句。

对于包含 GROUP BY 和 JOIN 的查询，请在查询前期执行聚合，以减少处理的数据量。例如，以下查询对两个大型表执行 JOIN，而不预先进行任何过滤：

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

此查询会预先聚合注释计数，从而减少为 JOIN 读取的数据量：

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

注意：具有通用表表达式 (CTE) 的 WITH 子句用于查询可读性，而非性能。无法保证添加 WITH 子句会导致 BigQuery 具体化临时中间表并重复使用临时结果以用于多个引用。在一个查询中，WITH 子句可能会多次计算，具体取决于查询优化器决策。

使用 WHERE 子句

最佳实践：使用 WHERE 子句来限制查询返回的数据量。 尽可能在 WHERE 子句中使用 BOOL、INT、FLOAT 或 DATE 列。

对 BOOL、INT、FLOAT 和 DATE 列执行的操作通常比对 STRING 或 BYTE 列的操作更快。如有可能，在 WHERE 子句使用的列应使用这些数据类型之一，以减少查询返回的数据量。

优化查询操作

您可以使用以下部分中介绍的选项来优化查询操作。

避免重复转换数据

最佳做法：如果您使用 SQL 执行 ETL 操作，请避免重复转换相同数据的情况。

例如，如果您在使用 SQL 通过正则表达式修剪字符串或提取数据，那么在目标表中将转换的结果实体化将会改善性能。诸如正则表达式之类的函数需要额外的计算。在不增加转换开销的情况下查询目标表会更有效率。

避免对相同 CTE 多次求值

最佳实践：使用过程语言、变量、临时表以及自动到期的表来保留计算并稍后在查询中使用这些计算。

如果您的查询包含在查询中多个位置使用的通用表表达式 (CTE)，则每次引用它们时可能都会对其求值。查询优化器会尝试检测可以只执行一次的查询部分，但这并非总是可行。因此，使用 CTE 可能有助于降低内部查询的复杂性和资源消耗量。

您可以根据 CTE 返回的数据将 CTE 的结果存储在标量变量或临时表中。

避免重复的联接和子查询

最佳做法：避免反复联接相同的表和使用相同的子查询。

与反复联接数据相比，使用嵌套的重复数据来表示相应关系可能会获得更高性能。嵌套的重复数据可以降低联接所需的通信带宽对性能的影响，还可以为您节省因反复读写相同数据而产生的 I/O 费用。如需了解详情，请参阅使用嵌套和重复字段。

同样，重复相同的子查询也会因重复的查询处理而影响性能。如果您要在多个查询中使用相同的子查询，请考虑将子查询结果具体化为一个表。然后在查询中使用已具体化的数据。

将子查询结果具体化可以提高性能，并减少 BigQuery 读写的总数据量。已具体化数据的存储费用远远低于重复的 I/O 和查询处理给性能带来的影响。

优化联接模式

最佳做法：对于联接多个表中的数据的查询，通过从最大表开始来优化联接模式。

当您使用 JOIN 子句创建查询时，请考虑合并数据的顺序。GoogleSQL 查询优化器会确定哪个表应位于联接的哪一侧。最佳做法是先放置行数最多的表，然后再放置行数最少的表，接下来再按从大到小的顺序放置剩余表。

将大型表放在 JOIN 的左侧并将小型表放在 JOIN 的右边后，将创建一个广播联接。广播联接将较小表中的所有数据发送到处理较大表的每个槽。建议先执行广播联接。

如需查看 JOIN 中表的大小，请参阅获取有关表的信息。

优化 ORDER BY 子句

最佳做法：使用 ORDER BY 子句时，请确保遵循最佳做法：

• 在最外层查询或窗口子句中使用 ORDER BY。将复杂的操作推到查询的末尾处。 在查询的中间放入 ORDER BY 子句会极大地影响性能，除非是在窗口函数中使用该子句。

  对查询进行排序的另一个方法是将复杂的操作（如正则表达式和数学函数）推到查询的末尾处。 此方法可在执行复杂操作之前减少要处理的数据。

• 使用 LIMIT 子句。如果您要对大量值进行排序，但不需要返回所有值，请使用 LIMIT 子句。例如，以下查询对非常大的结果集进行排序并引发 Resources exceeded 错误。该查询按 mytable 中的 title 列排序。title 列包含数百万个值。

  SELECT
  title
  FROM
  `my-project.mydataset.mytable`
  ORDER BY
  title;
  

  要移除此错误，请使用如下查询：

  SELECT
  title
  FROM
  `my-project.mydataset.mytable`
  ORDER BY
  title DESC
  LIMIT
  1000;
  

• 使用窗口函数。如果您要对大量值进行排序，请使用窗口函数，并在调用窗口函数之前限制数据。例如，以下查询列出了 10 个最早的 Stack Overflow 用户及其排名，最早的账号的排名最低：

  SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY user_rank ASC
  LIMIT 10;
  

  此查询大约需要 15 秒才能完成运行。此查询在查询末尾使用 LIMIT，但不在 DENSE_RANK() OVER 窗口函数中使用。因此，查询要求在单个工作器节点上对所有数据进行排序。

  相反，您应该在计算窗口函数之前限制数据集以提高性能：

  WITH users AS (
  SELECT
  id,
  reputation,
  creation_date,
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY creation_date ASC
  LIMIT 10)
  SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
  FROM users
  ORDER BY user_rank;
  

  此查询运行约 2 秒，同时返回与上一查询相同的结果。

  需要注意的是，DENSE_RANK() 函数会对数年内的数据进行排名，因此在对跨多个年份的数据进行排名时，这些查询不会提供相同的结果。

将复杂的查询拆分为较小的查询

最佳实践：利用多语句查询功能和存储过程，将设计为一个复杂查询的计算改为多个更小、更简单的查询。

复杂的查询REGEX 函数和分层子查询或联接可能运行缓慢，并且需要大量资源。尝试将所有计算放入一个大型 SELECT 语句（例如，使其成为视图）有时是一种反模式，并且可能会导致查询运行缓慢且消耗大量资源。在极端情况下，内部查询计划会变得非常复杂，以致 BigQuery 无法执行。

拆分复杂查询可以将变量或临时表中的中间结果具体化。然后，您可以在查询的其他部分中使用这些中间结果。如果在查询的多个位置需要这些结果，则会更为有用。

通常，您可以使用临时表作为数据具体化点来更好地表达查询部分的实际意图。

使用嵌套和重复字段

如需了解如何使用嵌套和重复字段对数据存储空间进行反规范化，请参阅使用嵌套和重复字段。

在联接中使用 INT64 数据类型

最佳做法：在联接中使用 INT64 数据类型而不是 STRING 数据类型，以降低费用并提高比较性能。

BigQuery 不像传统数据库那样将主键编入索引，因此联接列越宽，比较操作所需的时间就越长。因此，在联接中使用 INT64 数据类型比使用 STRING 数据类型更便宜，也更高效。

减少查询输出

您可以使用下列部分中介绍的选项来减少查询输出。

将大型结果集具体化

最佳做法：考虑将大型结果集具体化为一个目标表。写入大型结果集会影响性能和成本。

BigQuery 将缓存结果大小上限设为大约 10 GB（压缩后大小）。如果查询返回的结果数据量超过了此限制，通常会导致以下错误：Response too large。

从包含大量数据的表中选择大量字段时，常常会发生这种错误。如果 ETL 样式的查询在未减少或汇总数据的情况下对数据进行规范化，写入缓存结果时也会发生问题。

您可以使用以下选项克服对缓存结果大小的限制：

• 使用过滤条件限制结果集
• 使用 LIMIT 子句减少结果集中的数据量，尤其是在使用 ORDER BY 子句时
• 将输出数据写入目标表

您可以使用 BigQuery REST API 逐页浏览结果。如需了解详情，请参阅对表数据进行分页。

注意：将非常大的结果集写入目标表会影响查询性能 (I/O)。此外，存储目标表会产生少量费用。您可以利用数据集的默认表过期时间自动删除大型目标表。如需了解详情，请参阅存储最佳实践中的使用过期时间设置。

使用 BI Engine

如需通过缓存您最常使用的数据来进一步加速 SQL 查询，请考虑将 BI Engine 预留添加到计算查询的项目。BigQuery BI Engine 使用矢量化查询引擎来加快 SELECT 查询性能。

避免反 SQL 模式

以下最佳做法提供了相关指导，说明如何避免在 BigQuery 中使用会影响性能的查询反模式。

避免自联接

最佳做法：使用窗口（分析）函数，而不是使用自联接。

通常情况下，自联接用于计算依赖于行的关系。如果使用自联接，它可能会对输出行数进行平方运算。输出数据的增加可能会导致性能变差。

如需减少查询生成的额外字节数，请使用窗口（分析）函数。

避免交叉联接

最佳做法：避免使用产生的输出多于输入的联接。如果需要 CROSS JOIN，请预先聚合您的数据。

交叉联接是指第一个表中的每行与第二个表中的每行相联接，并且两侧都存在非唯一键的查询。最差情况输出是左表中的行数乘以右表中的行数。在极端情况下，查询可能无法完成。

如果查询作业完成，则查询计划说明会显示输出行与输入行的对比情况。您可以修改查询以显示 JOIN 子句两侧的行数（按联接键分组），从而确认笛卡尔积。

为了避免与产生的输出多于输入的联接相关的性能问题，请采取以下措施：

• 使用 GROUP BY 子句预先聚合数据。
• 使用窗口函数。窗口函数通常比使用交叉联接更高效。如需了解详情，请参阅窗口函数。

避免用于更新或插入单行的 DML 语句

最佳做法：避免使用更新或插入单行的 DML 语句。批量处理您的更新和插入。

使用特定于点的 DML 语句是尝试将 BigQuery 视为在线事务处理 (OLTP) 系统。BigQuery 使用表扫描而不是点查询，侧重于在线分析处理 (OLAP)。如果您需要类似 OLTP 的行为（单行更新或插入），请考虑使用旨在支持 OLTP 用例的数据库，例如 Cloud SQL。

BigQuery DML 语句适用于批量更新。BigQuery 中的 UPDATE 和 DELETE DML 语句用于定期重写数据，而不是单行修改。请谨慎使用 INSERT DML 语句。插入操作所占用的修改配额与加载作业相同。如果您的使用场景涉及频繁进行单行插入，不妨考虑改为对您的数据进行流式处理。

如果批量处理 UPDATE 语句产生非常长的包含很多元组的查询，就可能接近 256 KB 的查询长度上限。为了避免发生查询长度上限问题，请考虑是否可以根据逻辑标准（而不是一系列直接元组替换）来处理您的更新。

例如，您可以将替换记录集加载到另一个表中，然后编写 DML 语句在未更新的列匹配时更新原始表中的所有值。例如，如果原始数据位于表 t 中，而更新暂存在表 u 中，则查询将如下所示：

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

过滤偏差数据

最佳做法：如果您的查询处理的键严重偏向一些值，请尽早过滤您的数据。

分区倾斜（有时称为数据倾斜）是指数据分入大小极不均匀的分区这种情况。这会导致槽之间发送的数据量不平衡。您不能在槽之间共享分区，因此如果一个分区特别大，就可能减慢处理该分区的槽的速度，甚至可能导致槽的崩溃。

如果分区键有一个值出现的频率高于其他任何值，则分区就会变大。例如，按 user_id 字段分组，其中 guest 或 NULL 的条目有很多。

如果某个槽的资源不足以满足需求，则会出现 resources exceeded 错误。达到槽的 Shuffle 上限（压缩后所占内存为 2TB）也会导致 Shuffle 写入磁盘并进一步影响性能。采用基于容量的价格的客户可以增加分配槽数。

如果您查看查询执行图，发现计算时间的平均值与最大值之间存在明显差异，则说明可能存在数据倾斜。

为了避免数据倾斜引起的性能问题，请采取以下措施：

• 使用近似聚合函数（如 APPROX_TOP_COUNT）确定是否存在数据倾斜。
• 尽早过滤您的数据。

不平衡联接

在您使用 JOIN 子句时，也可能会出现数据倾斜。因为 BigQuery 对联接两侧的数据执行 Shuffle 操作，所以具有相同联接键的所有数据都会进入同一分片。这种 Shuffle 操作可能会导致槽过载。

为避免与不平衡联接相关的性能问题，您可以执行以下任务：

• 预先过滤表中具有不平衡键的行。
• 如有可能，将查询拆分为两个查询。

• 在 WHERE 子句中指定子查询时使用 SELECT DISTINCT 语句，以便评估唯一字段值一次。

  例如，不使用以下包含 SELECT 语句的子句：

  table1.my_id NOT IN (
    SELECT my_id
    FROM table2
    )
  

  而改用包含 SELECT DISTINCT 语句的子句：

  table1.my_id NOT IN (
    SELECT DISTINCT my_id
    FROM table2
    )
  

为名称相似的列使用别名

最佳做法：处理查询（包括子查询）中具有相似名称的列时，使用列和表别名。

别名可帮助您识别除了初始引用列以外引用的列和表。使用别名有助于您了解并解决 SQL 查询中的问题，包括查找子查询中使用的列。

在表架构中指定限制条件

当表数据包含限制条件时，请在表架构中指定限制条件。查询引擎可以使用表限制条件来优化查询计划。

指定主键和外键限制条件

当表数据满足主键或外键限制条件的数据完整性要求时，您应在表架构中指定键限制条件。查询引擎可以使用关键限制条件来优化查询计划。您可以在使用 BigQuery 主键和外键联接优化博文中找到详细信息。

BigQuery 不会自动检查数据完整性，因此您必须确保您的数据满足表架构中指定的限制条件。如果未在具有指定限制条件的表中维护数据完整性，则查询结果可能不准确。

后续步骤

• 了解如何优化费用。
• 了解如何优化存储。
• 了解如何优化函数。