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

Best Practice: Vermeiden Sie Self Joins. Verwenden Sie stattdessen eine Fensterfunktion.

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

Nutzen Sie statt eines Self Joins eine (analytische) Fensterfunktion, um die Anzahl der zusätzlichen Byte, die durch die Abfrage generiert werden, zu reduzieren.

Datenverzerrung

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 Pauschalpreisen können die Zahl der zugeordneten Slots erhöhen.

Wenn Sie in der Abfrageplanerlä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.

So vermeiden Sie Leistungsprobleme aufgrund unausgeglichener Joins:

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

Cross Joins (kartesisches Produkt)

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

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

Best Practice: Vermeiden Sie punktspezifische DML-Anweisungen, durch die jeweils nur eine Zeile aktualisiert oder eingefügt wird. 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 ausfü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