使用基于历史记录的优化
若要在预览版期间获得支持，请发送电子邮件至 bigquery-history-based-optimization-support@google.com。
本指南介绍了如何为查询启用、停用和分析基于历史记录的优化。
基于历史记录的优化简介
基于历史记录的优化使用来自已完成的类似查询执行的信息来应用其他优化，并进一步提高查询性能，例如使用的槽时间和查询延迟时间。例如，当您应用基于历史记录的优化时，第一次查询执行可能需要 60 秒，但如果识别到基于历史记录的优化，则第二次查询执行可能只需 30 秒。此过程会一直持续，直到没有其他优化可供添加为止。
以下示例说明了基于历史记录的优化如何与 BigQuery 搭配使用：
| 执行次数 | 使用的查询槽时间 | 备注 |
|---|---|---|
| 1 | 60 | 原始执行。 |
| 2 | 30 | 应用第一个基于历史记录的优化。 |
| 3 | 20 | 应用第二个基于历史记录的优化。 |
| 4 | 21 | 没有其他基于历史记录的优化可供应用。 |
| 5 | 19 | 没有其他基于历史记录的优化可供应用。 |
| 6 | 20 | 没有其他基于历史记录的优化可供应用。 |
基于历史记录的优化仅在十分确信优化将对查询性能有利时应用。此外，如果某项优化没有显著提高查询性能，则该优化会被撤消，并且不会在后续执行该查询中使用。
启用基于历史记录的优化
如需在项目中使用基于历史记录的优化，请在 ALTER PROJECT 或 ALTER ORGANIZATION 语句中添加以下参数：default_query_optimizer_options = 'adaptive=on'
示例：
ALTER PROJECT `user_project` SET OPTIONS ( `region-us.default_query_optimizer_options` = 'adaptive=on' );
停用基于历史记录的优化
如需在项目中停用基于历史记录的优化，请在 ALTER PROJECT 或 ALTER ORGANIZATION 语句中添加 default_query_optimizer_options = 'adaptive=off' 参数。
示例：
ALTER PROJECT `user_project` SET OPTIONS ( `region-us.default_query_optimizer_options` = 'adaptive=off' );
查看作业的基于历史记录的优化
如需查看作业的基于历史记录的优化，您可以使用 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-us`.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-us.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 定义。
- 此查询可以应用于其他查询性能指标，如
total_slot_ms和total_bytes_billed。如需了解详情，请参阅INFORMATION_SCHEMA.JOBS的架构。 - 如果此示例查询的结果为空，则表示没有任何作业使用基于历史记录的优化或所有查询在 30 天前都经过优化。
角色与权限
如需选择启用基于历史记录的优化，您必须拥有创建 BigQuery 默认配置所需的权限，然后必须使用
ALTER PROJECT语句启用基于历史记录的优化。启用基于历史记录的优化后，该项目中的所有作业都会使用基于历史记录的优化，无论作业由哪个用户创建都是如此。如需详细了解默认配置所需的权限，请参阅默认配置的所需权限。如需启用基于历史记录的优化，请参阅启用基于历史记录的优化。如需使用
INFORMATION_SCHEMA.JOBS视图查看作业的基于历史记录的优化，您必须具有所需的角色。如需了解详情，请参阅INFORMATION_SCHEMA.JOBS视图的所需角色。