查询计划和时间轴

BigQuery 的查询作业中嵌入了诊断查询计划和耗时信息。这类似于在其他数据库和分析系统中由 EXPLAIN 等语句提供的信息。您可从 jobs.get 等方法的 API 响应中检索到这些信息。

对于长时间运行的查询，BigQuery 将定期更新这些统计信息。这些更新的发生与轮询作业状态的速率无关，但通常更新间隔不会小于 30 秒。此外，不使用执行资源的查询作业（例如试运行请求或可通过缓存结果提供的结果）将不包含额外的诊断信息，但可能存在其他统计信息。

背景

BigQuery 在执行查询作业时，会将声明性 SQL 语句转换为执行图。此执行图会分解为一系列查询阶段，而这些阶段本身是由多组更精细的执行步骤组成。BigQuery 会利用高度分布式的并行架构来运行这些查询，而这些阶段则会建立许多潜在工作器可能并行执行的工作单元模型。各阶段使用快速分布式 Shuffle 架构相互通信。

在查询计划中，“工作单元”和“工作器”这两个术语专门用于传达有关并行性的信息。在 BigQuery 的其他位置，您可能会遇到“槽”这个术语；该术语是查询执行的多个方面（包括计算、内存和 I/O 资源）的抽象表示。借助这种抽象核算方式，顶级作业统计信息可通过查询的 totalSlotMs 估算值提供单次查询的费用估算。

查询执行架构的另一个重要属性是它具有动态性，这意味着您可在查询运行时修改查询计划。在查询运行时引入的阶段通常用于改进所有查询工作器的数据分布。在发生这种情况的查询计划中，这些阶段通常会被标记为“重新分区阶段”。

除了查询计划之外，查询作业还会显示执行时间轴，该时间轴用于提供查询工作器内处于已完成、待处理和活跃状态的工作单元的核算信息。一个查询可能会同时经历多个具有活跃工作器的阶段，因此时间轴是用于显示查询的整体进度。

使用 Google Cloud 控制台查看信息

在 Google Cloud 控制台中，您可以通过点击执行详情按钮（靠近结果按钮）来查看已完成查询的查询计划的详情。

查询计划信息

在 API 响应中，查询计划表示为一系列查询阶段的列表。 列表中的每个项显示每个阶段的概览统计信息、详细步骤信息和阶段耗时分类。并非所有详细信息都会显示在 Google Cloud 控制台中，但它们全部存在于 API 响应中。

阶段概览

每个阶段的概览字段可能包括以下各项：

每个阶段的耗时分类

查询阶段还以相对和绝对形式提供阶段耗时分类。由于每个执行阶段表示由一个或多个独立工作器所执行的工作，因此会以平均耗时和最长耗时两种形式提供信息。这些时间代表一个阶段中所有工作器的平均性能以及指定分类下的长尾最慢工作器性能。平均耗时和最大耗时可进一步分为绝对表示法和相对表示法。对于基于比率的统计信息，数据以任何细分中任何工作器的最长耗时所占比例的形式提供。

Google Cloud 控制台使用相对耗时表示法来显示阶段耗时。

阶段耗时信息报告如下：

步骤概览

步骤包含阶段中每个工作器执行的操作，以有序操作列表的形式呈现。每个步骤操作都有一个类别，其中一些操作提供更详细的信息。查询计划中显示的操作类别包括以下各项：

了解步骤详情

BigQuery 提供步骤详情，用于说明每个步骤在阶段内执行的操作。了解阶段中的各个步骤对于找出查询性能问题的根源至关重要。

如需查找阶段的步骤详细信息，请按以下步骤操作：

1. 在查询结果窗格中，点击执行图。

   

2. 点击您感兴趣的阶段，打开包含阶段信息的面板。

3. 在包含阶段信息的面板中，前往步骤详细信息部分。

   

每个步骤都包含描述该步骤所执行操作的子步骤。子步骤使用变量来描述步骤之间的关系。变量以美元符号开头，后跟一个唯一编号。

下面是一个阶段的步骤详细信息示例，其中包含在步骤之间共享的变量：

READ
$30:l_orderkey, $31:l_quantity
FROM lineitem

AGGREGATE
GROUP BY $100 := $30
$70 := SUM($31)

WRITE
$100, $70
TO __stage00_output
BY HASH($100)

示例的步骤详细信息执行以下操作：

1. 该阶段分别使用变量 $30 和 $31 从表 lineitem 中读取了列 l_orderkey 和 l_quantity。

2. 基于变量 $30 和 $31 进行汇总的阶段，分别将汇总结果存储到变量 $100 和 $70 中。

3. 相应阶段将变量 $100 和 $70 的结果写入 shuffle。该阶段使用 $100 对阶段的结果进行随机排序。

如果查询的执行图过于复杂，以至于在检索查询信息时提供完整的阶段步骤详细信息会导致有效负载大小问题，BigQuery 可能会截断步骤详细信息。

了解查询文本的步骤

若要在预览版期间获得支持，请发送电子邮件至 bq-query-inspector-feedback@google.com。

了解阶段的步骤与查询之间的关系可能具有挑战性。查询文本部分显示了某些步骤与原始查询文本之间的关系。

查询文本部分会突出显示原始查询文本的不同部分，并显示与突出显示的原始查询文本紧邻的查询文本对应的步骤。只有紧邻原始查询文本中突出显示部分的步骤才适用于突出显示的查询文本。

屏幕截图示例显示了以下映射：

• 步骤 AGGREGATE: GROUP BY $100 := $30 会映射回查询文本 select l_orderkey。

• 步骤 READ: FROM lineitem 会映射回查询文本 select ... from lineitem。

• 步骤 AGGREGATE: $70 := SUM($31) 会映射回查询文本 sum(l_quantity)。

并非所有步骤都可以映射回查询文本。

如果查询使用了视图，并且阶段的步骤与视图的查询文本有映射关系，则查询文本部分会显示视图名称和视图的查询文本及其映射关系。不过，如果视图被删除，或者您失去了对该视图的 bigquery.tables.get IAM 权限，则查询文本部分不会显示该视图的阶段步骤映射。

解读和优化步骤

以下部分介绍了如何解读查询计划中的步骤，并提供了优化查询的方法。

READ 步骤

READ 步骤表示某个阶段正在访问数据以进行处理。数据可以直接从查询中引用的表读取，也可以从 shuffle 内存读取。当读取前一阶段的数据时，BigQuery 会从 shuffle 内存中读取数据。使用按需槽位时，扫描的数据量会影响费用；使用预留时，扫描的数据量会影响性能。

潜在的性能问题

• 对未分区表进行大规模扫描：如果查询只需要少量数据，则可能表明表扫描效率低下。分区可能是一种不错的优化策略。
• 扫描具有较小过滤比率的大型表：这表明过滤条件无法有效地减少扫描的数据。请考虑修改过滤条件。
• 溢出到磁盘的 Shuffle 字节数：这表明数据未通过聚类等优化技术有效存储，而聚类可以将类似的数据保持在集群中。

优化

• 有针对性的过滤：有策略地使用 WHERE 子句，以便在查询中尽早过滤掉无关数据。这样可以减少查询需要处理的数据量。
• 分区和聚簇：BigQuery 使用表分区和聚簇来高效定位特定数据段。确保根据典型的查询模式对表进行分区和聚簇，以最大限度地减少在 READ 步骤中扫描的数据。
• 选择相关列：避免使用 SELECT * 语句。请选择特定列或使用 SELECT * EXCEPT，以避免读取不必要的数据。
• 具体化视图：具体化视图可以预先计算并存储常用的聚合，从而可能减少在查询使用这些视图的 READ 步骤中读取基本表的需求。

COMPUTE 步骤

在 COMPUTE 步骤中，BigQuery 会对您的数据执行以下操作：

• 评估查询的 SELECT、WHERE、HAVING 和其他子句中的表达式，包括计算、比较和逻辑运算。
• 执行内置 SQL 函数和用户定义的函数。
• 根据查询中的条件过滤数据行。

优化

查询计划可以揭示 COMPUTE 步骤中的瓶颈。查找计算量大或处理的行数多的阶段。

• 将 COMPUTE 步与数据量相关联：如果某个阶段显示出大量计算并处理大量数据，则该阶段可能是优化的理想对象。
• 倾斜数据：如果某个阶段的计算最大值远高于计算平均值，则表明该阶段花费了过多的时间来处理少量数据切片。考虑查看数据分布，以确定是否存在数据倾斜。
• 考虑数据类型：为列使用适当的数据类型。例如，使用整数、日期时间和时间戳而非字符串可以提高性能。

WRITE 步骤

WRITE 步骤适用于中间数据和最终输出。

• 写入 shuffle 内存：在多阶段查询中，WRITE 步骤通常涉及将处理后的数据发送到另一个阶段以进行进一步处理。这对于混洗内存来说很常见，因为混洗内存会合并或汇总来自多个来源的数据。此阶段写入的数据通常是中间结果，而不是最终输出。
• 最终输出：查询结果会写入目标表或临时表。

哈希分区

当查询计划中的某个阶段将数据写入哈希分区输出时，BigQuery 会写入输出中包含的列以及所选的分区键列。

优化

虽然 WRITE 步骤本身可能无法直接优化，但了解其作用有助于您确定早期阶段的潜在瓶颈：

• 尽量减少写入的数据：重点在于通过过滤和聚合来优化前面的阶段，以减少此步骤中写入的数据量。

• 分区：写入操作可从表分区中获益良多。如果您写入的数据仅限于特定分区，则 BigQuery 可以更快地执行写入操作。

  如果 DML 语句包含针对表分区列的静态条件 WHERE 子句，则 BigQuery 只会修改相关的表分区。

• 非规范化权衡：非规范化有时会导致中间 WRITE 步骤中的结果集更小。不过，这种方法也有缺点，例如会增加存储空间用量，并带来数据一致性方面的挑战。

JOIN 步骤

在 JOIN 步骤中，BigQuery 会合并来自两个数据源的数据。 联接可以包含联接条件。联接会消耗大量资源。在 BigQuery 中联接大型数据时，联接键会独立进行 shuffle 处理，以便在同一 slot 中对齐，从而在每个 slot 中本地执行联接。

JOIN 步骤的查询计划通常会显示以下详细信息：

• 联接模式：表示所用的联接类型。每种类型都定义了结果集中包含的联接表中的行数。
• 联接列：用于匹配数据源之间行的列。列的选择对于联接性能至关重要。

联接模式

• 广播联接：当一个表（通常是较小的表）可以容纳在单个工作节点或 slot 的内存中时，BigQuery 可以将其广播到所有其他节点，以高效地执行联接。在步骤详细信息中查找 JOIN EACH WITH ALL。
• 哈希联接：如果表很大或不适合使用广播联接，则可以使用哈希联接。BigQuery 使用哈希和 shuffle 操作对左表和右表进行 shuffle，以便匹配的键最终位于同一 slot 中，从而执行本地联接。哈希联接是一项开销较大的操作，因为需要移动数据，但它能够高效地跨哈希匹配行。在步数详情中查找 JOIN EACH WITH EACH。
• 自联接：一种 SQL 反面模式，其中表与自身联接。
• 交叉联接：一种 SQL 反模式，可能会导致严重的性能问题，因为其生成的输出数据比输入数据大。
• 倾斜联接：一个表中联接键的数据分布非常倾斜，可能会导致性能问题。查找查询计划中最大计算时间远大于平均计算时间的情况。如需了解详情，请参阅高基数联接和分区倾斜。

调试

• 数据量大：如果查询计划显示在 JOIN 步骤中处理了大量数据，请调查联接条件和联接键。请考虑过滤或使用更具选择性的联接键。
• 倾斜的数据分布：分析联接键的数据分布。如果某个表的倾斜度非常高，请考虑采用拆分查询或预过滤等策略。
• 高基数联接：生成的行数明显多于左侧和右侧输入行数的联接会大幅降低查询性能。避免使用会生成大量行的联接。
• 表排序不正确：请确保您已选择合适的联接类型（例如 INNER 或 LEFT），并根据查询的要求按从大到小的顺序对表进行排序。

优化

• 选择性联接键：对于联接键，请尽可能使用 INT64 而不是 STRING。STRING 比较比 INT64 比较慢，因为前者会比较字符串中的每个字符。整数只需要进行一次比较。
• 联接前过滤：在联接之前，对各个表应用 WHERE 子句过滤条件。这样可以减少联接操作涉及的数据量。
• 避免在联接列上使用函数：避免在联接列上调用函数。相反，您可以使用 ELT SQL 流水线在提取或提取后过程中标准化表数据。这种方法无需动态修改联接列，从而可以在不影响数据完整性的前提下实现更高效的联接。
• 避免自联接：自联接通常用于计算依赖于行的关系。不过，自联接可能会导致输出行数翻四倍，从而导致性能问题。请考虑使用窗口（分析）函数，而不是依赖自联接。
• 先处理大型表：即使 SQL 查询优化器可以确定哪个表应位于联接的哪一侧，也请适当地对联接的表进行排序。最佳做法是先放置最大的表，然后放置最小的表，接下来再按从大到小的顺序放置。
• 反规范化：在某些情况下，有策略地对表进行反规范化（添加冗余数据）可以完全消除联接。不过，这种方法在存储和数据一致性方面存在一些权衡取舍。
• 分区和聚簇：根据联接键对表进行分区，并对同位数据进行聚簇，这样可让 BigQuery 定位相关的数据分区，从而显著加快联接速度。
• 优化倾斜联接：为避免与倾斜联接相关的性能问题，请尽早预先过滤表中的数据，或将查询拆分为两个或更多个查询。

AGGREGATE 步骤

在 AGGREGATE 步骤中，BigQuery 会汇总和分组数据。

调试

• 阶段详细信息：检查聚合的输入行数和输出行数，以及 shuffle 大小，以确定聚合步骤实现了多少数据缩减，以及是否涉及数据 shuffle。
• Shuffle 大小：较大的 Shuffle 大小可能表明，在聚合期间，大量数据在工作器节点之间移动。
• 检查数据分布：确保数据在各个分区中均匀分布。数据分布偏差可能会导致汇总步骤中的工作负载不平衡。
• 检查聚合：分析聚合子句，确认它们是否必要且高效。

优化

• 聚类：根据 GROUP BY、COUNT 或其他聚合子句中经常使用的列对表进行聚类。
• 分区：选择与查询模式相符的分区策略。考虑使用提取时间分区表来减少聚合期间扫描的数据量。
• 提早聚合：如果可能，请在查询流水线中提早执行聚合。这样可以减少在聚合期间需要处理的数据量。
• Shuffle 优化：如果 shuffle 是瓶颈，请探索尽可能减少 shuffle 的方法。例如，对表进行非规范化处理，或使用聚簇功能将相关数据共置。

边缘用例

• DISTINCT 聚合函数：包含 DISTINCT 聚合函数的查询可能需要大量计算资源，尤其是在大型数据集上。考虑使用 APPROX_COUNT_DISTINCT 等替代方案来获得近似结果。
• 大量分组：如果查询生成大量分组，则可能会消耗大量内存。在这种情况下，请考虑限制组的数量或使用其他汇总策略。

REPARTITION 步骤

REPARTITION 和 COALESCE 都是 BigQuery 直接应用于查询中混杂数据的优化技术。

• REPARTITION：此操作旨在重新平衡各个工作器节点之间的数据分布。假设在混排后，某个工作器节点最终获得的数据量过大。REPARTITION 步骤可更均匀地重新分配数据，防止任何单个工作器成为瓶颈。对于联接等计算密集型操作，这一点尤为重要。
• COALESCE：当您在混洗后有许多小数据桶时，会发生此步骤。COALESCE 步骤会将这些桶合并为更大的桶，从而减少与管理大量小数据块相关的开销。在处理非常小的中间结果集时，这尤其有用。

如果您在查询计划中看到 REPARTITION 或 COALESCE 步骤，这并不一定意味着您的查询存在问题。这通常表明 BigQuery 正在主动优化数据分布，以提高性能。不过，如果您反复看到这些操作，可能表明您的数据本身存在偏差，或者您的查询导致了过多的数据混洗。

优化

如需减少REPARTITION步数，请尝试以下操作：

• 数据分布：确保您的表已进行有效的分区和聚类。分布良好的数据可降低在随机混排后出现严重不平衡的可能性。
• 查询结构：分析查询是否存在潜在的数据倾斜来源。 例如，是否存在高度选择性的过滤条件或联接，导致单个工作器仅处理一小部分数据？
• 联接策略：尝试使用不同的联接策略，看看它们是否能带来更均衡的数据分布。

如需减少COALESCE步数，请尝试以下操作：

• 聚合策略：考虑在查询流水线中更早地执行聚合。这有助于减少可能导致 COALESCE 步骤的小型中间结果集数量。
• 数据量：如果您处理的是非常小的数据集，COALESCE可能不是一个需要特别关注的问题。

不要过度优化。过早优化可能会使查询变得更加复杂，而不会带来显著的好处。

联合查询的说明

联合查询可让您使用 EXTERNAL_QUERY 函数将查询语句发送到外部数据源。联合查询依赖于被称为 SQL 下推的优化方法，查询计划会显示下推到外部数据源的操作（如有）。例如，如果您运行以下查询：

SELECT id, name
FROM EXTERNAL_QUERY("<connection>", "SELECT * FROM company")
WHERE country_code IN ('ee', 'hu') AND name like '%TV%'

查询计划将显示以下阶段步骤：

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, country_code
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
  WHERE in(country_code, 'ee', 'hu')
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

在此计划中，table_for_external_query_$_0(...) 代表 EXTERNAL_QUERY 函数。在括号中，您可以看到外部数据源执行的查询。根据这些信息，您可以看到：

• 外部数据源仅返回 3 个选定的列。
• 外部数据源仅返回 country_code 为 'ee' 或 'hu' 的行。
• LIKE 运算符没有下推，并且由 BigQuery 进行计算。

为进行比较，如果没有下推，查询计划将显示以下阶段步骤：

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, description, country_code, primary_address, secondary address
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

这次，外部数据源返回 company 表中的所有列和所有行，并且 BigQuery 执行过滤。

时间轴元数据

查询时间轴用于在特定时间点报告进度，同时提供总体查询进度的快照视图。时间轴由一系列示例表示，这些示例报告了以下详细信息：

查询示例

以下查询会计算 Shakespeare 公开数据集内的行数，然后再运行第二个条件计数，将结果限制为引用“hamlet”的行数：

SELECT
  COUNT(1) as rowcount,
  COUNTIF(corpus = 'hamlet') as rowcount_hamlet
FROM `publicdata.samples.shakespeare`

点击执行详细信息，以查看查询计划：

颜色指示器显示了所有阶段中所有步骤的相对耗时。

如需详细了解执行阶段的步骤，请点击 arrow_drop_down 以展开该阶段的详细信息：

在此示例中，任何细分中的最长耗时即为阶段 01 中的单个工作器等待阶段 00 完成的时间。这是因为阶段 01 依赖于阶段 00 的输入，仅当第一个阶段将其输出写入中间 Shuffle 后才能启动阶段 01。

错误报告

查询作业在执行过程中可能会失败。由于计划信息会定期更新，您可以查看在执行图表中的哪个位置发生故障。在 Google Cloud 控制台中，成功或失败的阶段的名称旁边会标示对勾或感叹号图标。

如需详细了解如何解读和处理错误，请参阅问题排查指南。

API 表示示例

查询计划信息将嵌入到作业响应信息中，您可以通过调用 jobs.get 进行检索。例如，以下是返回 hamlet 查询示例的作业的 JSON 响应摘录，其中显示了查询计划和时间轴信息。

"statistics": {
  "creationTime": "1576544129234",
  "startTime": "1576544129348",
  "endTime": "1576544129681",
  "totalBytesProcessed": "2464625",
  "query": {
    "queryPlan": [
      {
        "name": "S00: Input",
        "id": "0",
        "startMs": "1576544129436",
        "endMs": "1576544129465",
        "waitRatioAvg": 0.04,
        "waitMsAvg": "1",
        "waitRatioMax": 0.04,
        "waitMsMax": "1",
        "readRatioAvg": 0.32,
        "readMsAvg": "8",
        "readRatioMax": 0.32,
        "readMsMax": "8",
        "computeRatioAvg": 1,
        "computeMsAvg": "25",
        "computeRatioMax": 1,
        "computeMsMax": "25",
        "writeRatioAvg": 0.08,
        "writeMsAvg": "2",
        "writeRatioMax": 0.08,
        "writeMsMax": "2",
        "shuffleOutputBytes": "18",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "164656",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$1:corpus",
              "FROM publicdata.samples.shakespeare"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$20 := COUNT($30)",
              "$21 := COUNTIF($31)"
            ]
          },
          {
            "kind": "COMPUTE",
            "substeps": [
              "$30 := 1",
              "$31 := equal($1, 'hamlet')"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$20, $21",
              "TO __stage00_output"
            ]
          }
        ]
      },
      {
        "name": "S01: Output",
        "id": "1",
        "startMs": "1576544129465",
        "endMs": "1576544129480",
        "inputStages": [
          "0"
        ],
        "waitRatioAvg": 0.44,
        "waitMsAvg": "11",
        "waitRatioMax": 0.44,
        "waitMsMax": "11",
        "readRatioAvg": 0,
        "readMsAvg": "0",
        "readRatioMax": 0,
        "readMsMax": "0",
        "computeRatioAvg": 0.2,
        "computeMsAvg": "5",
        "computeRatioMax": 0.2,
        "computeMsMax": "5",
        "writeRatioAvg": 0.16,
        "writeMsAvg": "4",
        "writeRatioMax": 0.16,
        "writeMsMax": "4",
        "shuffleOutputBytes": "17",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "1",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$20, $21",
              "FROM __stage00_output"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$10 := SUM_OF_COUNTS($20)",
              "$11 := SUM_OF_COUNTS($21)"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$10, $11",
              "TO __stage01_output"
            ]
          }
        ]
      }
    ],
    "estimatedBytesProcessed": "2464625",
    "timeline": [
      {
        "elapsedMs": "304",
        "totalSlotMs": "50",
        "pendingUnits": "0",
        "completedUnits": "2"
      }
    ],
    "totalPartitionsProcessed": "0",
    "totalBytesProcessed": "2464625",
    "totalBytesBilled": "10485760",
    "billingTier": 1,
    "totalSlotMs": "50",
    "cacheHit": false,
    "referencedTables": [
      {
        "projectId": "publicdata",
        "datasetId": "samples",
        "tableId": "shakespeare"
      }
    ],
    "statementType": "SELECT"
  },
  "totalSlotMs": "50"
},

利用执行情况信息

BigQuery 查询计划提供有关该服务如何执行查询的信息，但该服务的托管特性限制了某些详细信息的可直接操作性。使用该服务可自动执行很多优化，这可能不同于其他需要由知识丰富的专业人员来完成调整、预配和监控的环境。

如需了解可以完善查询执行和性能的特定做法，请参阅最佳实践文档。查询计划和时间轴统计信息可以帮助您了解某些阶段是否在资源耗用上占据大头。例如，生成的输出行远超输入行的 JOIN 阶段可能表示有必要在查询的早期阶段进行过滤。

此外，时间轴信息可以帮助确定某个查询速度缓慢是因为其自身原因，还是受到了其他查询争用相同资源的影响。如果您发现活跃单元的数量在查询的整个生命周期中始终有限，但排队的工作单元数量一直很大，这可能意味着减少并发查询数量将显著缩短某些查询的总体执行时间。