使用以記錄為依據的最佳化功能

本指南說明如何啟用、停用及分析查詢的歷史資料最佳化功能。

關於以記錄為基礎的最佳化

以歷史資料為依據的最佳化功能會使用已完成執行的類似查詢所提供的資訊，套用其他最佳化方式，進一步改善查詢效能，例如使用時段時間和查詢延遲時間。舉例來說，如果您套用以歷史資料為依據的最佳化方式，第一次執行查詢可能需要 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,
    100 * 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  |           67.806850186245114 |             7087 |                 22014 |
   | sample_job2  |           66.485800412501987 |            10562 |                 31515 |
   | sample_job3  |           63.285605271764254 |            97668 |                266021 |
   | sample_job4  |           61.134141726887904 |           923384 |               2375823 |
   | sample_job5  |           55.381272089713754 |          1060062 |               2375823 |
   | sample_job6  |           45.396943168036479 |          2324071 |               4256302 |
   | sample_job7  |           38.227031526376024 |            17811 |                 28833 |
   | sample_job8  |           33.826608962725111 |            66360 |                100282 |
   | sample_job9  |           32.087813758311604 |            44020 |                 64819 |
   | sample_job10 |           28.356416319483539 |            19088 |                 26643 |
   *--------------+------------------------------+------------------+-----------------------*/

這項查詢的結果僅為根據歷史資料進行最佳化所產生的影響預估值。許多因素都可能會影響查詢效能，包括但不限於空格可用性、資料隨時間變化、檢視或 UDF 定義，以及查詢參數值的差異。

如果這個範例查詢的結果為空白，表示沒有任何工作使用以歷史資料為基礎的最佳化功能，或是所有查詢的最佳化時間超過 30 天。

這項查詢可套用至其他查詢成效指標，例如 total_slot_ms 和 total_bytes_billed。詳情請參閱 INFORMATION_SCHEMA.JOBS 的結構定義。