Verlaufsbasierte Optimierungen verwenden

In diesem Leitfaden wird beschrieben, wie Sie verlaufsbasierte Optimierungen für Abfragen aktivieren, deaktivieren und analysieren.

Verlaufsbasierte Optimierungen

Verlaufsbasierte Optimierungen verwenden Informationen aus bereits abgeschlossenen Ausführungen ähnlicher Abfragen, um zusätzliche Optimierungen anzuwenden und die Abfrageleistung, z. B. verbrauchte Slot-Zeit und Abfragelatenz, weiter zu verbessern. Wenn Sie beispielsweise die verlaufsbasierte Optimierung anwenden, kann die erste Abfrageausführung 60 Sekunden dauern. Die zweite Abfrageausführung dauert jedoch nur 30 Sekunden, wenn eine verlaufsbasierte Optimierung identifiziert wurde. Dieser Vorgang wird fortgesetzt, bis keine weiteren Optimierungen mehr hinzugefügt werden können.

Im Folgenden finden Sie ein Beispiel für die Funktionsweise verlaufsbasierter Optimierungen mit BigQuery:

Ausführungsanzahl	Genutzte Slot-Zeit für Abfrage	Hinweise
1	60	Ursprüngliche Ausführung.
2	30	Erste verlaufsbasierte Optimierung angewendet.
3	20	Zweite auf dem Verlauf basierende Optimierung angewendet.
4	21	Es können keine weiteren auf dem Verlauf basierenden Optimierungen angewendet werden.
5	19	Es können keine weiteren auf dem Verlauf basierenden Optimierungen angewendet werden.
6	20	Es können keine weiteren auf dem Verlauf basierenden Optimierungen angewendet werden.

Verlaufsbasierte Optimierungen werden nur angewendet, wenn es wahrscheinlich ist, dass sich die Abfrageleistung vorteilhaft auf die Abfrageleistung auswirkt. Wenn eine Optimierung die Abfrageleistung nicht erheblich verbessert, wird diese Optimierung widerrufen und in zukünftigen Ausführungen dieser Abfrage nicht verwendet.

Rollen und Berechtigungen

• Wenn Sie verlaufsbasierte Optimierungen aktivieren oder deaktivieren möchten, benötigen Sie die erforderlichen Berechtigungen zum Erstellen von BigQuery-Standardkonfigurationen. Anschließend müssen Sie die ALTER PROJECT-Anweisung verwenden, um verlaufsbasierte Optimierungen zu aktivieren. Nachdem Sie die verlaufsbasierten Optimierungen aktiviert haben, verwenden alle Jobs in diesem Projekt verlaufsbasierte Optimierungen, unabhängig davon, welcher Nutzer den Job erstellt hat. Weitere Informationen zu den erforderlichen Berechtigungen für Standardkonfigurationen finden Sie unter Erforderliche Berechtigungen für Standardkonfigurationen. Informationen zum Aktivieren verlaufsbasierter Optimierungen finden Sie unter Verlaufsbasierte Optimierungen aktivieren.

• Sie benötigen die erforderliche Rolle, um die verlaufsbasierten Optimierungen für einen Job mit der Ansicht INFORMATION_SCHEMA.JOBS zu prüfen. Weitere Informationen finden Sie unter Erforderliche Rolle für die Ansicht INFORMATION_SCHEMA.JOBS.

Verlaufsbasierte Optimierungen aktivieren

Wenn Sie verlaufsbasierte Optimierungen in einem Projekt verwenden möchten, fügen Sie folgenden Parameter in die ALTER PROJECT- oder ALTER ORGANIZATION-Anweisung ein: default_query_optimizer_options = 'adaptive=on' Beispiel:

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

Ersetzen Sie Folgendes:

• PROJECT_NAME ist der Name des Projekts.
• LOCATION: Der Standort des Projekts.

Hinweis: Nachdem Sie verlaufsbasierte Optimierungen aktiviert haben, können Sie die folgende Warnung ignorieren, die bei erfolgreicher ALTER PROJECT- oder ALTER ORGANIZATION-Anweisung gezeigt wird: ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.

Verlaufsbasierte Optimierungen deaktivieren

Wenn Sie verlaufsbasierte Optimierungen in einem Projekt deaktivieren möchten, fügen Sie den Parameter default_query_optimizer_options = 'adaptive=off' in die ALTER PROJECT- oder ALTER ORGANIZATION-Anweisung ein. Beispiel:

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

Ersetzen Sie Folgendes:

• PROJECT_NAME ist der Name des Projekts.
• LOCATION: Der Standort des Projekts.

Hinweis: Nachdem Sie verlaufsbasierte Optimierungen deaktiviert haben, können Sie die folgende Warnung ignorieren, die bei erfolgreicher ALTER PROJECT- oder ALTER ORGANIZATION-Anweisung gezeigt wird: ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.

Verlaufsbasierte Optimierungen für einen Job prüfen

Sie können eine SQL-Abfrage oder einen REST API-Methodenaufruf verwenden, um die verlaufsbasierten Optimierungen für einen Job zu prüfen.

SQL

Sie können eine Abfrage verwenden, um die verlaufsbasierten Optimierungen für einen Job abzurufen. Die Abfrage muss INFORMATION_SCHEMA.JOBS_BY_PROJECT und den Spaltennamen query_info.optimization_details enthalten.

Im folgenden Beispiel werden die Optimierungsdetails für einen Job namens sample_job zurückgegeben. Wenn keine verlaufsbasierten Optimierungen angewendet wurden, wird NULL für optimization_details erstellt:

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

Die Ergebnisse sehen in etwa so aus:

-- 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

Rufen Sie die Methode jobs.get auf, um die Optimierungsdetails für einen Job abzurufen.

Im folgenden Beispiel gibt die Methode jobs.get die Optimierungsdetails (optimizationDetails) in der vollständigen Antwort zurück:

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

Die Ergebnisse sehen in etwa so aus:

-- 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"
            }
          ]
        }
      }
    }
  }
}

Auswirkungen verlaufsbasierter Optimierungen schätzen

Um die Auswirkungen verlaufsbasierter Optimierungen zu schätzen, können Sie die folgende Beispiel-SQL-Abfrage verwenden, um Projektabfragen mit der größten geschätzten Verbesserung der Ausführungszeit zu ermitteln.

  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;

Das Ergebnis der vorherigen Abfrage sieht in etwa so aus, wenn verlaufsbasierte Optimierungen angewendet wurden:

  /*--------------+------------------------------+------------------+-----------------------*
   |    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 |
   *--------------+------------------------------+------------------+-----------------------*/

Die Ergebnisse dieser Abfrage sind nur eine Schätzung der Auswirkungen der verlaufsbasierten Optimierung. Viele Faktoren können die Abfrageleistung beeinflussen, darunter die Verfügbarkeit von Slots, Änderungen der Daten im Zeitverlauf, Definitionen von Ansichten oder UDFs und Unterschiede bei den Abfrageparameterwerten.

Wenn das Ergebnis dieser Beispielabfrage leer ist, haben keine Jobs verlaufsbasierte Optimierungen verwendet oder alle Abfragen wurden vor mehr als 30 Tagen optimiert.

Diese Abfrage kann auf andere Messwerte zur Abfrageleistung wie total_slot_ms und total_bytes_billed angewendet werden. Weitere Informationen finden Sie im Schema für INFORMATION_SCHEMA.JOBS.