使用基于历史记录的优化

本指南介绍了如何为查询启用、停用和分析基于历史记录的优化。

基于历史记录的优化简介

基于历史记录的优化使用来自已完成的类似查询执行的信息来应用其他优化，并进一步提高查询性能，例如使用的槽时间和查询延迟时间。例如，当您应用基于历史记录的优化时，第一次查询执行可能需要 60 秒，但如果识别到基于历史记录的优化，则第二次查询执行可能只需 30 秒。此过程会一直持续，直到没有其他优化可供添加为止。

以下示例说明了基于历史记录的优化如何与 BigQuery 搭配使用：

执行次数	使用的查询槽时间	备注
1	60	原始执行。
2	30	应用第一个基于历史记录的优化。
3	20	应用第二个基于历史记录的优化。
4	21	没有其他基于历史记录的优化可供应用。
5	19	没有其他基于历史记录的优化可供应用。
6	20	没有其他基于历史记录的优化可供应用。

基于历史记录的优化仅在十分确信优化将对查询性能有利时应用。此外，如果某项优化没有显著提高查询性能，则该优化会被撤消，并且不会在后续执行该查询中使用。

角色与权限

如需启用或停用基于历史记录的优化，您必须拥有创建 BigQuery 默认配置所需的权限，然后必须使用 ALTER PROJECT 语句启用基于历史记录的优化。启用基于历史记录的优化后，该项目中的所有作业都会使用基于历史记录的优化，无论作业由哪个用户创建都是如此。如需详细了解默认配置所需的权限，请参阅默认配置的所需权限。如需启用基于历史记录的优化，请参阅启用基于历史记录的优化。
如需使用 INFORMATION_SCHEMA.JOBS 视图查看作业的基于历史记录的优化，您必须具有所需的角色。如需了解详情，请参阅 INFORMATION_SCHEMA.JOBS 视图的所需角色。

启用基于历史记录的优化

如需在项目中使用基于历史记录的优化，请在 ALTER PROJECT 或 ALTER ORGANIZATION 语句中添加以下参数：default_query_optimizer_options = 'adaptive=on'。例如：

ALTER PROJECT PROJECT_NAME
SET OPTIONS (
  `region-LOCATION.default_query_optimizer_options` = 'adaptive=on'
);

替换以下内容：

PROJECT_NAME：项目的名称
LOCATION：项目的位置

停用基于历史记录的优化

如需在项目中停用基于历史记录的优化，请在 ALTER PROJECT 或 ALTER ORGANIZATION 语句中添加 default_query_optimizer_options = 'adaptive=off' 参数。例如：

ALTER PROJECT PROJECT_NAME
SET OPTIONS (
  `region-LOCATION.default_query_optimizer_options` = 'adaptive=off'
);

替换以下内容：

PROJECT_NAME：项目的名称
LOCATION：项目的位置

查看作业的基于历史记录的优化

如需查看作业的基于历史记录的优化，您可以使用 SQL 查询或 REST API 方法调用。

SQL

您可以使用查询来获取作业的基于历史记录的优化。查询必须包含 INFORMATION_SCHEMA.JOBS_BY_PROJECT 和 query_info.optimization_details 列名称。

在以下示例中，系统会为名为 sample_job 的作业返回优化详细信息。如果未应用基于历史记录的优化，则系统会为 optimization_details 生成 NULL：

SELECT
  job_id,
  query_info.optimization_details
FROM `project_name.region-LOCATION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'sample_job'
LIMIT 1;

结果类似于以下内容：

-- The JSON in optimization_details has been formatted for readability.
/*------------+-----------------------------------------------------------------*
 | job_id     | optimization_details                                            |
 +------------+-----------------------------------------------------------------+
 | sample_job | {                                                               |
 |            |   "optimizations": [                                            |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "web_sales.web_date,RIGHT"         |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "catalog_sales.catalog_date,RIGHT" |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "store_sales.store_date,RIGHT"     |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "join_commutation": "web_returns.web_item"                |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "parallelism_adjustment": "applied"                       |
 |            |     },                                                          |
 |            |   ]                                                             |
 |            | }                                                               |
 *------------+-----------------------------------------------------------------*/

API

如需获取作业的优化详细信息，您可以调用 jobs.get 方法。

在以下示例中，jobs.get 方法会在完整响应中返回优化详细信息 (optimizationDetails)：

{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "sample_job"
  }
}

结果类似于以下内容：

-- The unrelated parts in the full response have been removed.
{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "sample_job",
    "location": "US"
  },
  "statistics": {
    "query": {
      "queryInfo": {
        "optimizationDetails": {
          "optimizations": [
            {
              "semi_join_reduction": "web_sales.web_date,RIGHT"
            },
            {
              "semi_join_reduction": "catalog_sales.catalog_date,RIGHT"
            },
            {
              "semi_join_reduction": "store_sales.store_date,RIGHT"
            },
            {
              "join_commutation": "web_returns.web_item"
            },
            {
              "parallelism_adjustment": "applied"
            }
          ]
        }
      }
    }
  }
}

估算基于历史记录的优化的影响

如需估算基于历史记录的优化的影响，您可以使用以下 SQL 查询示例来识别执行时间预计改善最大的项目查询。

  WITH
    jobs AS (
      SELECT
        *,
        query_info.query_hashes.normalized_literals AS query_hash,
        TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms,
        IFNULL(
          ARRAY_LENGTH(JSON_QUERY_ARRAY(query_info.optimization_details.optimizations)) > 0,
          FALSE)
          AS has_history_based_optimization,
      FROM region-LOCATION.INFORMATION_SCHEMA.JOBS_BY_PROJECT
      WHERE EXTRACT(DATE FROM creation_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    ),
    most_recent_jobs_without_history_based_optimizations AS (
      SELECT *
      FROM jobs
      WHERE NOT has_history_based_optimization
      QUALIFY ROW_NUMBER() OVER (PARTITION BY query_hash ORDER BY end_time DESC) = 1
    )
  SELECT
    job.job_id,
    SAFE_DIVIDE(
      original_job.elapsed_ms - job.elapsed_ms,
      original_job.elapsed_ms) AS percent_execution_time_saved,
    job.elapsed_ms AS new_elapsed_ms,
    original_job.elapsed_ms AS original_elapsed_ms,
  FROM jobs AS job
  INNER JOIN most_recent_jobs_without_history_based_optimizations AS original_job
    USING (query_hash)
  WHERE
    job.has_history_based_optimization
    AND original_job.end_time < job.start_time
  ORDER BY percent_execution_time_saved DESC
  LIMIT 10;

如果应用了基于历史记录的优化，则上述查询的结果如下所示：

  /*--------------+------------------------------+------------------+-----------------------*
   |    job_id    | percent_execution_time_saved | new_execution_ms | original_execution_ms |
   +--------------+------------------------------+------------------+-----------------------+
   | sample_job1  |           0.6780685018624512 |             7087 |                 22014 |
   | sample_job2  |           0.6648580041250198 |            10562 |                 31515 |
   | sample_job3  |          0.63285605271764256 |            97668 |                266021 |
   | sample_job4  |            0.611341417268879 |           923384 |               2375823 |
   | sample_job5  |           0.5538127208971375 |          1060062 |               2375823 |
   | sample_job6  |           0.4539694316803648 |          2324071 |               4256302 |
   | sample_job7  |          0.38227031526376026 |            17811 |                 28833 |
   | sample_job8  |          0.33826608962725113 |            66360 |                100282 |
   | sample_job9  |          0.32087813758311606 |            44020 |                 64819 |
   | sample_job10 |           0.2835641631948354 |            19088 |                 26643 |
   *--------------+------------------------------+------------------+-----------------------*/

此查询的结果仅是对基于历史记录的优化影响的估计。许多因素可能会影响查询性能，包括但不限于槽位可用性、数据随时间的变化、视图或 UDF 定义以及查询参数值的差异。

如果此示例查询的结果为空，则表示没有作业使用过基于历史记录的优化，或者所有查询都是在超过 30 天前优化的。

此查询可应用于其他查询性能指标，例如 total_slot_ms 和 total_bytes_billed。如需了解详情，请参阅 INFORMATION_SCHEMA.JOBS 的架构。