Abfrageleistung optimieren

Dieses Dokument enthält Best Practices zur Optimierung der Abfrageleistung.

Nachdem die Abfrage abgeschlossen wurde, können Sie den Abfrageplan in der Google Cloud Console einsehen. Sie können Ausführungsdetails auch mit den INFORMATION_SCHEMA.JOBS*-Ansichten oder der jobs.get REST API-Methode anfordern.

Der Abfrageplan enthält Details zu Abfragephasen und -schritten. Anhand dieser Details können Sie Möglichkeiten zur Verbesserung der Abfrageleistung bestimmen. Beispiel: Wenn Sie eine Phase bemerken, in der viel mehr Ausgaben als in anderen Phasen geschrieben werden, bedeutet dies möglicherweise, dass Sie früher in der Abfrage filtern müssen.

Weitere Informationen zum Abfrageplan und Beispiele dazu, wie Sie anhand der Informationen des Abfrageplans die Abfrageleistung verbessern können, finden Sie unter Statistiken zur Abfrageleistung abrufen. Nachdem Sie die Statistiken zur Abfrageleistung beantwortet haben, können Sie die Abfrage weiter optimieren. Führen Sie dazu die folgenden Aufgaben aus:

Verarbeitete Daten reduzieren

Sie können die zu verarbeitenden Daten mithilfe der in den folgenden Abschnitten beschriebenen Optionen reduzieren.

Auf SELECT * verzichten

Best Practice: Steuern Sie die Projektion, indem Sie nur die benötigten Spalten abfragen.

Mit Projektion ist die Anzahl der Spalten gemeint, die bei der Abfrage gelesen werden. Durch das Projizieren unnötiger Spalten erhöhen sich der E/A-Umfang und die Schreibergebnisse.

  • Verwenden Sie die Optionen für eine Datenvorschau. Wenn Sie mit Daten experimentieren oder Daten untersuchen, verwenden Sie anstelle von SELECT * eine der Datenvorschauoptionen.
  • Abfrage bestimmter Spalten Mit einer LIMIT-Klausel in einer SELECT *-Abfrage beeinflussen Sie nicht die Menge der gelesenen Daten. Ihnen werden immer alle in der gesamten Tabelle gelesenen Byte in Rechnung gestellt. Außerdem wird die Abfrage auf Ihr kostenloses Kontingent angerechnet. Fragen Sie stattdessen nur die Spalten ab, die Sie benötigen. Zum Beispiel verwenden Sie SELECT * EXCEPT, um eine oder mehrere Spalten aus den Ergebnissen auszuschließen.
  • Partitionierte Tabellen verwenden Wenn Sie alle Spalten einer Tabelle, aber nur eine Teilmenge der darin enthaltenen Daten abfragen möchten, haben Sie folgende Möglichkeiten:

  • Verwenden Sie SELECT * EXCEPT. Wenn Sie nur eine Teilmenge der Daten abfragen oder für die Abfrage SELECT * EXCEPT verwenden, reduziert sich die von einer Abfrage gelesene Datenmenge in der Regel erheblich. Neben den Kosteneinsparungen profitieren Sie von der Leistungssteigerung, die Sie durch das Reduzieren des E/A-Umfangs und der Schreibergebnisse erreichen.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

Platzhaltertabellen sparsam verwenden

Best Practice: Verwenden Sie zum Abfragen von Platzhaltertabellen ein detailliertes Präfix.

Nutzen Sie Platzhalter, um mehrere Tabellen unter Verwendung kurzer SQL-Anweisungen abzufragen. Platzhaltertabellen stellen eine Zusammenführung aller Tabellen dar, die mit dem Platzhalterausdruck übereinstimmen. Sie sind nützlich, wenn Ihr Dataset die folgenden Ressourcen enthält:

  • Mehrere Tabellen mit ähnlichen Namen und kompatiblen Schemas
  • Fragmentierte Tabellen

Geben Sie zum Abfragen einer Platzhaltertabelle nach dem allgemeinen Tabellenpräfix einen Platzhalter (*) ein. Mit FROM bigquery-public-data.noaa_gsod.gsod194* werden beispielsweise alle Tabellen aus den 1940er-Jahren abgefragt.

Mit detailgenaueren Präfixen erzielen Sie bessere Ergebnisse als mit kurzen Präfixen. FROM bigquery-public-data.noaa_gsod.gsod194* ist beispielsweise effizienter als FROM bigquery-public-data.noaa_gsod.*, da weniger Tabellen dem Platzhalter entsprechen.

Nach Datum fragmentierte Tabellen vermeiden

Best Practice: Verwenden Sie zeitpartitionierte Tabellen anstelle von Tabellen, die nach Datum fragmentiert sind (auch als datumsbenannte Tabellen bezeichnet).

Partitionierte Tabellen bieten eine bessere Leistung als datumsbenannte Tabellen. Wenn Sie nach Datum fragmentierte Tabellen verwenden, muss BigQuery für jede datumsbenannte Tabelle eine Kopie des Schemas und der Metadaten verwalten. Außerdem muss BigQuery möglicherweise für jede abgefragte datumsbenannte Tabelle die Berechtigungen überprüfen. Dieser Mehraufwand bei der Durchführung der Abfrage wirkt sich direkt auf die Abfrageleistung aus.

Übermäßige Tabellenfragmentierung vermeiden

Best Practice: Beschränken Sie die Anzahl von Tabellen-Shards. Wenn Sie Tabellen nach Datum fragmentieren, verwenden Sie stattdessen zeitpartitionierte Tabellen.

Bei der Fragmentierung werden große Datasets als eigene Tabellen abgetrennt und der Tabellenname erhält ein Suffix. Wenn Sie Tabellen nach Datum fragmentieren, verwenden Sie stattdessen zeitpartitionierte Tabellen.

Aufgrund der geringen Kosten für BigQuery-Speicher müssen Sie Ihre Tabellen nicht wie bei einem relationalen Datenbanksystem auf Kosten optimieren. Die Erstellung einer großen Anzahl von Tabellen-Shards hat Auswirkungen auf die Leistung, welche die etwaigen Kostenvorteile überwiegen.

Fragmentierte Tabellen benötigen BigQuery, um Schemas, Metadaten und Berechtigungen für jede Fragmentierung zu verwalten. Aufgrund des Mehraufwands, der zur Verwaltung der Informationen für jede Fragmentierung erforderlich ist, können zu viele Tabellenfragmentierungen die Abfrageleistung beeinträchtigen.

Die Menge und Quelle der von einer Abfrage gelesenen Daten kann sich auf die Abfrageleistung und die Kosten auswirken.

Partitionierte Abfragen bereinigen

Best Practice: Verwenden Sie beim Abfragen einer partitionierten Tabelle zum Filtern von Partitionen in partitionierten Tabellen die folgenden Spalten:

  • Verwenden Sie für nach Aufnahmezeit partitionierte Tabellen die Pseudospalte _PARTITIONTIME.
  • Verwenden Sie für partitionierte Tabellen wie die nach Zeiteinheit spaltenbasierte und die des Ganzzahlbereichs die Partitionierungsspalte.

Bei nach Zeiteinheit partitionierten Tabellen können Sie durch Filtern der Daten mit _PARTITIONTIME oder Partitionierungsspalten ein Datum oder einen Datumsbereich angeben. In der folgenden WHERE-Klausel wird zum Beispiel die Pseudospalte _PARTITIONTIME genutzt, um Partitionen zwischen dem 1. Januar 2016 und dem 31. Januar 2016 anzugeben:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

Die Abfrage verarbeitet nur Daten in den Partitionen, die durch den Zeitraum angegeben sind. Durch das Filtern der Partitionen verbessern Sie die Abfrageleistung und senken die Kosten.

Datenmenge vor einem JOIN verkleinern

Best Practice: Verringern Sie die Menge der Daten, die vor einer JOIN-Klausel verarbeitet werden, indem Sie Aggregationen durchführen.

Die Verwendung einer GROUP BY-Klausel mit Aggregatfunktionen ist rechenintensiv, da diese Abfragetypen Shuffle verwenden. Da diese Abfragen rechenintensiv sind, müssen Sie die GROUP BY-Klausel nur bei Bedarf verwenden.

Führen Sie für Abfragen mit GROUP BY und JOIN eine Aggregation früher in der Abfrage durch, um die Menge der verarbeiteten Daten zu reduzieren. Die folgende Abfrage führt beispielsweise eine JOIN für zwei große Tabellen ohne vorherige Filterung durch:

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

In dieser Abfrage werden die Anzahl der Kommentare vorab zusammengefasst, wodurch die für JOIN gelesene Datenmenge reduziert wird:

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

WHERE-Klausel verwenden

Best Practice: Verwenden Sie eine WHERE-Klausel, um die Datenmenge zu begrenzen, die eine Abfrage zurückgibt. Verwenden Sie nach Möglichkeit die Spalten BOOL, INT, FLOAT oder DATE in der WHERE-Klausel.

Vorgänge für die Spalten BOOL, INT, FLOAT und DATE sind in der Regel schneller als Vorgänge für die Spalten STRING oder BYTE. Verwenden Sie nach Möglichkeit eine Spalte, die einen dieser Datentypen in der WHERE-Klausel verwendet, um die von der Abfrage zurückgegebene Datenmenge zu reduzieren.

Abfragevorgänge optimieren

Sie können Ihre Abfragevorgänge mithilfe der in den folgenden Abschnitten beschriebenen Optionen optimieren.

Wiederholtes Transformieren von Daten vermeiden

Best Practice: Vermeiden Sie beim Verwenden von SQL für ETL-Vorgänge Situationen, in denen Sie wiederholt die gleichen Daten transformieren.

Wenn Sie zum Beispiel SQL verwenden, um mit regulären Ausdrücken Strings zu verkürzen oder Daten zu extrahieren, ist es sinnvoller, die transformierten Ergebnisse in einer Zieltabelle zu erfassen. Funktionen wie reguläre Ausdrücke erfordern zusätzlichen Rechenaufwand. Das Abfragen der Zieltabelle ohne den zusätzlichen Transformationsaufwand ist viel effizienter.

Mehrere Auswertungen derselben CTEs vermeiden

Best Practice: Verwenden Sie die prozedurale Sprache, Variablen, temporäre Tabellen und automatisch ablaufende Tabellen, um Berechnungen zu speichern und sie später in der Abfrage zu verwenden.

Wenn Ihre Abfrage Allgemeine Tabellenausdrücke (CTEs) enthält, die an mehreren Stellen in der Abfrage verwendet werden, werden sie möglicherweise jedes Mal ausgewertet, wenn sie referenziert werden. Die Abfrageoptimierung versucht, Teile der Abfrage zu erkennen, die nur einmal ausgeführt werden können. Dies ist jedoch nicht immer möglich. Daher kann die Verwendung eines CTE möglicherweise nicht dazu beitragen, die interne Abfragekomplexität und den Ressourcenverbrauch zu reduzieren.

Sie können das Ergebnis einer CTE in einer skalaren Variable oder einer temporären Tabelle speichern, abhängig von den Daten, die die CTE zurückgibt.

Wiederholte Joins und Unterabfragen vermeiden

Best Practice: Vermeiden Sie wiederholte Joins derselben Tabellen sowie die Verwendung derselben Unterabfragen.

Anstatt Daten wiederholt zu verknüpfen, können Sie Beziehungen vielleicht durch verschachtelte wiederkehrende Daten darstellen und dadurch die Leistung steigern. Mithilfe verschachtelter wiederkehrender Daten lassen sich die Leistungseinbußen vermeiden, die durch die zusätzliche Datenübertragung bei der Verwendung von Joins entstehen. Außerdem sparen Sie sich die E/A-Kosten, die durch das wiederholte Lesen und Schreiben derselben Daten verursacht werden. Weitere Informationen finden Sie unter Verschachtelte und wiederkehrende Felder verwenden.

Auch die Verwendung der immer gleichen Unterabfragen wirkt sich aufgrund der sich wiederholenden Abfrageverarbeitung negativ auf die Leistung aus. Wenn Sie dieselben Unterabfragen in mehreren Abfragen verwenden, empfiehlt es sich, die Ergebnisse der Unterabfrage in einer Tabelle zu materialisieren. Auf diese Weise können Sie in den Abfragen einfach die Tabellendaten verwenden.

Die Materialisierung der Abfrageergebnisse verbessert die Leistung und verringert die Gesamtmenge an Daten, die von BigQuery gelesen und geschrieben werden muss. Die geringen Kosten für die Speicherung der materialisierten Daten sind leichter zu verkraften als die Auswirkung einer wiederholten E/A- und Abfrageverarbeitung auf die Leistung.

Join-Muster optimieren

Best Practice: Optimieren Sie Ihre Join-Muster für Abfragen, die Daten aus mehreren Tabellen miteinander verbinden, indem Sie mit der größten Tabelle beginnen.

Berücksichtigen Sie beim Erstellen einer Abfrage mit einer JOIN-Klausel die Reihenfolge, in der Sie die Daten zusammenführen. Das Google SQL-Abfrageoptimierungstool bestimmt, welche Tabelle auf welcher Seite der Verknüpfung stehen sollte. Als Best Practice sollten Sie die Tabelle mit der größten Anzahl an Zeilen zuerst platzieren, gefolgt von der Tabelle mit den wenigsten Zeilen und die übrigen Tabellen nach unten ordnen.

Wenn eine große Tabelle links von JOIN und eine kleine rechts von JOIN steht, wird ein Broadcast-Join erstellt. Dabei werden alle Daten in der kleineren Tabelle an die Slots gesendet, die die größere Tabelle verarbeiten. Sie sollten den Broadcast-Join zuerst ausführen.

Informationen zur Anzeige der Tabellengröße in Ihrem JOIN finden Sie unter Informationen zu Tabellen abrufen.

ORDER BY-Klausel optimieren

Best Practice: Achten Sie bei Verwendung der ORDER BY-Klausel darauf, dass Sie die Best Practices befolgen:

  • Verwenden Sie ORDER BY in der äußersten Abfrage oder in Fensterklauseln. Verschieben Sie komplexe Operationen an das Ende der Abfrage. In der Mitte einer Abfrage führt die ORDER BY-Klausel zu einer erheblichen Leistungsbeeinträchtigung, wenn sie nicht in einer Fensterfunktion verwendet wird.

    Eine weitere Methode zum Sortieren einer Abfrage besteht darin, komplexe Vorgänge wie reguläre Ausdrücke und mathematische Funktionen an das Ende zu stellen. Dieses Verfahren reduziert die zu verarbeitenden Daten, bevor die komplexen Vorgänge ausgeführt werden.

  • Verwenden Sie eine LIMIT-Klausel. Verwenden Sie eine LIMIT-Klausel, wenn Sie eine sehr große Anzahl an Werten bestellen, aber nicht alle zurückgeben müssen. Die folgende Abfrage ordnet beispielsweise eine sehr große Ergebnismenge an und gibt den Fehler Resources exceeded zurück. Die Abfrage sortiert die Spalte title in mytable. Die Spalte title enthält mehrere Millionen Werte.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    Damit der Fehler nicht auftritt, formulieren Sie die Abfrage so:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • Verwenden Sie eine Fensterfunktion. Wenn Sie eine sehr große Anzahl an Werten bestellen, sollten Sie eine Fensterfunktion verwenden und die Daten vor dem Aufrufen der Fensterfunktion einschränken. Die folgende Abfrage listet z. B. die zehn ältesten Stack Overflow-Nutzer und deren Ranking auf, wobei das älteste Konto am niedrigsten eingestuft wird:

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    Diese Abfrage dauert ca.15 Sekunden. Diese Abfrage verwendet LIMIT am Ende der Abfrage, jedoch nicht in der Fensterfunktion DENSE_RANK() OVER. Aus diesem Grund müssen für die Abfrage alle Daten auf einen Worker-Knoten sortiert werden.

    Stattdessen sollten Sie das Dataset vor der Berechnung der Fensterfunktion beschränken, um die Leistung zu verbessern:

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

    Die Abfrage dauert ungefähr zwei Sekunden. Dabei werden dieselben Ergebnisse wie in der vorherigen Abfrage zurückgegeben.

    Ein Nachteil ist, dass die Funktion DENSE_RANK() die Daten innerhalb von Jahren sortiert. Bei Rankings von Daten, die sich über mehrere Jahre erstrecken, liefern diese Abfragen also keine identischen Ergebnisse.

Komplexe Abfragen in kleinere aufteilen

Best Practice: Nutzen Sie Abfragen mit mehreren Anweisungen und gespeicherte Prozeduren, um Berechnungen, die als eine komplexe Abfrage konzipiert waren, stattdessen als mehrere kleinere und einfachere Abfragen durchzuführen.

Komplexe Abfragen, REGEX-Funktionen und mehrschichtige Unterabfragen oder Joins können langsam und ressourcenintensiv sein. Der Versuch, alle Berechnungen in eine große SELECT-Anweisung aufzunehmen, z. B. zur Ansicht, ist manchmal ein Antimuster und kann zu einer langsamen, ressourcenintensiven Abfrage führen. In extremen Fällen wird der interne Abfrageplan so komplex, dass BigQuery ihn nicht ausführen kann.

Das Aufteilen einer komplexen Abfrage ermöglicht die Materialisierung von Zwischenergebnissen in Variablen oder temporären Tabellen. Sie können diese Zwischenergebnisse dann in anderen Teilen der Abfrage verwenden. Es ist immer nützlicher, wenn diese Ergebnisse an mehreren Stellen der Abfrage benötigt werden.

Häufig können Sie den tatsächlichen Intent von Teilen der Abfrage besser ausdrücken, indem Sie temporäre Tabellen als Punkte der Datenmaterialisierung verwenden.

Verschachtelte und wiederkehrende Felder verwenden

Informationen zum Denormalisieren von Datenspeichern mit verschachtelten und wiederkehrenden Feldern finden Sie unter Verschachtelte und wiederkehrende Felder verwenden.

INT64-Datentypen in Joins verwenden

Best Practice: Verwenden Sie INT64-Datentypen anstelle von STRING-Datentypen, um die Kosten zu senken und die Vergleichsleistung zu verbessern.

BigQuery indexiert keine Primärschlüssel wie herkömmliche Datenbanken. Je größer daher die Join-Spalte ist, desto länger dauert der Vergleich. Deshalb ist die Verwendung von INT64-Datentypen in Joins günstiger und effizienter als die Verwendung von STRING-Datentypen.

Ausgabe von Abfragen reduzieren

Sie können die Abfrageausgaben reduzieren. Dazu verwenden Sie die in den folgenden Abschnitten beschriebenen Optionen.

Große Ergebnismengen erfassen

Best Practice: Überlegen Sie, ob Sie eine Materialisierung umfangreicher Ergebnismengen in einer Zieltabelle durchführen möchten. Das Schreiben großer Ergebnismengen wirkt sich sowohl auf die Leistung als auch auf die Kosten aus.

BigQuery begrenzt die im Cache gespeicherten Ergebnisse auf ca. 10 GB in komprimierter Form. Abfragen, die größere Ergebnismengen zurückgeben, überschreiten dieses Limit, was häufig zu folgendem Fehler führt: Response too large.

Dieser Fehler tritt auf, wenn aus einer Tabelle, die eine große Datenmenge enthält, eine große Zahl von Feldern ausgewählt wird. Probleme beim Schreiben von zwischengespeicherten Ergebnissen können auch bei ETL-ähnlichen Abfragen auftreten, bei denen Daten ohne Verkleinerung oder Zusammenfassung normalisiert werden.

Mit den folgenden Optionen können Sie die Einschränkung der im Cache gespeicherten Ergebnisgröße umgehen:

  • Schränken Sie die Ergebnismenge mithilfe von Filtern ein.
  • Verkleinern Sie die Ergebnismenge mithilfe einer LIMIT-Klausel, insbesondere wenn Sie auch eine ORDER BY-Klausel verwenden.
  • Schreiben Sie die Ausgabedaten in eine Zieltabelle.

Sie können mit der BigQuery REST API in den Ergebnissen suchen. Weitere Informationen finden Sie unter In Tabellendaten suchen.

BI Engine verwenden

Wenn Sie Ihre SQL-Abfragen weiter beschleunigen möchten, indem Sie die Daten, die Sie am häufigsten verwenden, im Cache speichern, sollten Sie dem Projekt, in dem die Abfragen berechnet werden, eine BI Engine-Reservierung hinzufügen. BigQuery BI Engine verwendet eine vektorisierte Abfrage-Engine, um die SELECT-Abfrageleistung zu beschleunigen.

Anti-SQL-Muster vermeiden

Die folgenden Best Practices enthalten Informationen darüber, wie Sie nachteilige Designmuster in Abfragen, die die Leistung von BigQuery beeinträchtigen, vermeiden können.

Self Joins vermeiden

Best Practice: Nutzen Sie statt Self Joins eine (analytische) Fensterfunktion oder den PIVOT-Operator.

Self Joins werden üblicherweise eingesetzt, um zeilenabhängige Beziehungen zu berechnen. Dies kann jedoch dazu führen, dass sich die Zahl der Ausgabezeilen quadriert, was wiederum die Leistung beeinträchtigen kann.

Cross Joins vermeiden

Best Practice: Vermeiden Sie Joins, die mehr Ausgaben als Eingaben erzeugen. Fassen Sie Ihre Daten für CROSS JOIN vorab zusammen.

Cross Joins sind Abfragen, bei denen jede Zeile aus der ersten Tabelle mit jeder Zeile in der zweiten Tabelle verknüpft ist. (Auf beiden Seiten gibt es nicht eindeutige Schlüssel.) Im schlimmsten Fall ist die Ausgabe die Zahl der Zeilen in der linken Tabelle multipliziert mit der Zahl der Zeilen in der rechten Tabelle. In extremen Fällen kann die Abfrage nicht beendet werden.

Wenn die Abfrage abgeschlossen ist, zeigt die Abfrageplanerläuterung einen Vergleich der Ausgabe- und Eingabezeilen. Sie können prüfen, ob es sich um ein kartesisches Produkt handelt. Ändern Sie zu diesem Zweck die Abfrage so, dass die Anzahl der Zeilen auf jeder Seite der JOIN-Klausel ausgegeben wird, gruppiert nach Join-Schlüssel.

So vermeiden Sie Leistungsprobleme aufgrund von Verknüpfungen, die mehr Ausgaben als Eingaben generieren:

  • Verwenden Sie eine GROUP BY-Klausel, um die Daten vorab zusammenzufassen.
  • Verwenden Sie eine Fensterfunktion. Diese ist häufig effizienter als ein Cross Join. Weitere Informationen finden Sie unter Fensterfunktionen.

DML-Anweisungen, die einzelne Zeilen aktualisieren oder einfügen, vermeiden

Best Practice:Vermeiden Sie DML-Anweisungen, die einzelne Zeilen aktualisieren oder einfügen. Verarbeiten Sie stattdessen Updates und Einfügungen stapelweise.

Mit punktspezifischen DML-Anweisungen soll BigQuery wie ein OLTP-System (Online Transaction Processing) eingesetzt werden. BigQuery konzentriert sich aber auf OLAP (Online Analytical Processing) anhand von Tabellenscans anstatt von Punktabrufen. Wenn Sie OLTP-ähnliche Prozesse durchführen möchten (Aktualisieren oder Einfügen einzelner Zeilen), sollten Sie eine Datenbank verwenden, die OLTP-Anwendungsfälle unterstützt, etwa Cloud SQL.

BigQuery-DML-Anweisungen eignen sich für Sammelaktualisierungen. Die DML-Anweisungen UPDATE und DELETE in BigQuery können Sie verwenden, um Ihre Daten regelmäßig neu zu schreiben, nicht aber für Mutationen einzelner Zeilen. Die DML-Anweisung INSERT sollten Sie sparsam einsetzen. Für Einfügungen gelten dieselben Änderungskontingente wie für Ladejobs. Wenn Ihr Anwendungsfall das häufige Einfügen einzelner Zeilen umfasst, sollten Sie stattdessen Ihre Daten streamen.

Wenn die Stapelverarbeitung Ihrer UPDATE-Anweisungen viele Tupel in sehr langen Abfragen liefert, können diese die Längenbeschränkung von 256 KB erreichen. Zum Umgehen dieser Beschränkung sollten Sie prüfen, ob die Aktualisierungen auf der Grundlage eines logischen Kriteriums statt einer Reihe direkter Tupelersetzungen durchgeführt werden können.

Sie können beispielsweise die Ersatzdatensätze in eine andere Tabelle laden und dann eine DML-Anweisung zum Aktualisieren aller Werte in der ursprünglichen Tabelle schreiben, wenn die nicht aktualisierten Spalten übereinstimmen. Im folgenden Beispiel befinden sich die ursprünglichen Daten in Tabelle t und die Aktualisierungen in Tabelle u. Die Abfrage sieht dann so aus:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

Daten nach verzerrten Daten filtern

Best Practice: Falls Ihre Abfrage Schlüssel verarbeitet, die durch wenige Werte stark verzerrt sind, filtern Sie Ihre Daten so früh wie möglich.

Eine Partitionsverzerrung, auch als Datenverzerrung bezeichnet, tritt auf, wenn Daten in sehr ungleich große Partitionen aufgeteilt werden. Dadurch entsteht ein Ungleichgewicht bei der Menge der zwischen Slots gesendeten Daten. Sie können Partitionen nicht zwischen Slots teilen. Wenn eine Partition also besonders groß ist, kann sie zu einer Verlangsamung oder sogar zum Absturz des Slots führen, der die zu große Partition verarbeitet.

Partitionen werden dann groß, wenn der Partitionierungsschlüssel einen Wert hat, der häufiger auftritt als jeder andere. Dies ist beispielsweise bei der Gruppierung nach dem Feld user_id mit vielen Einträgen für guest oder NULL der Fall.

Sind die Ressourcen eines Slots überlastet, tritt der Fehler resources exceeded auf. Wenn das Shuffle-Limit für einen Slot erreicht ist (2 TB komprimierter Speicher), erfolgt das Schreiben nach dem Zufallsprinzip auf Festplatte, was die Leistung weiter beeinträchtigt. Kunden mit kapazitätsbasierten Preisen können die Anzahl der zugewiesenen Slots erhöhen.

Wenn Sie in der Abfragegrafikerläuterung einen erheblichen Unterschied zwischen der durchschnittlichen und maximalen Rechenzeit feststellen, sind Ihre Daten wahrscheinlich verzerrt.

So vermeiden Sie Leistungsprobleme aufgrund einer Datenverzerrung:

  • Verwenden Sie eine Näherungs-Aggregatfunktion wie APPROX_TOP_COUNT, um festzustellen, ob die Daten verzerrt sind.
  • Filtern Sie Ihre Daten so früh wie möglich.

Unausgeglichene Joins

Eine Datenverzerrung kann auch auftreten, wenn Sie JOIN-Klauseln verwenden. Da BigQuery Daten auf beiden Seiten des Joins sortiert, werden alle Daten mit demselben Join-Schlüssel dem gleichen Shard zugeordnet. Dadurch kann der Slot überlastet werden.

Führen Sie die folgenden Aufgaben aus, um Leistungsprobleme aufgrund von unausgeglichenen Joins zu vermeiden:

  • Filtern Sie Zeilen aus der Tabelle mit dem unausgeglichenen Schlüssel vorab.
  • Teilen Sie die Abfrage wenn möglich auf.

  • Verwenden Sie die Anweisung SELECT DISTINCT, wenn Sie in der Klausel WHERE eine Unterabfrage angeben, um eindeutige Feldwerte nur einmal auszuwerten.

    Statt die folgende Klausel zu verwenden, die eine SELECT-Anweisung enthält, haben Sie folgende Möglichkeiten:

    table1.my_id NOT IN (
  SELECT my_id
  FROM table2
  )

    Verwenden Sie stattdessen eine Klausel, die eine SELECT DISTINCT-Anweisung enthält:

    table1.my_id NOT IN (
  SELECT DISTINCT my_id
  FROM table2
  )

Aliasnamen für ähnlich benannte Spalten verwenden

Best Practice: Verwenden Sie Spalten- und Tabellenaliasse, wenn Sie für Abfragen, einschließlich Unterabfragen, mit ähnlich benannten Spalten arbeiten.

Mit Aliassen können Sie zusätzlich zur ersten Referenz der Spalte ermitteln, auf welche Spalten und Tabellen verwiesen wird. Mithilfe von Aliassen können Sie Probleme in Ihrer SQL-Abfrage nachvollziehen und beheben sowie die in Unterabfragen verwendeten Spalten ermitteln.

Einschränkungen im Tabellenschema angeben

Wenn Tabellendaten Einschränkungen enthalten, geben Sie die Einschränkungen im Tabellenschema an. Das Abfragemodul kann Abfragepläne mithilfe von Tabelleneinschränkungen optimieren.

Beschränkungen für Primärschlüssel und Fremdschlüssel angeben

Sie sollten Schlüsseleinschränkungen im Tabellenschema angeben, wenn Tabellendaten die Datenintegritätsanforderungen von Einschränkungen für Primärschlüssel oder Fremdschlüssel erfüllen. Die Abfrage-Engine kann die Schlüsseleinschränkungen verwenden, um Abfragepläne zu optimieren. Ausführliche Informationen finden Sie im Blogpost Join optimizations with BigQuery primary keys and foreign keys.

BigQuery prüft die Datenintegrität nicht automatisch. Daher müssen Sie sicherstellen, dass Ihre Daten die im Tabellenschema angegebenen Einschränkungen erfüllen. Wenn Sie die Datenintegrität in Tabellen mit angegebenen Einschränkungen nicht aufrechterhalten, können Ihre Abfrageergebnisse ungenau sein.

Nächste Schritte