Abfrageplan und Zeitachse

Im Rahmen von Abfragejobs enthält BigQuery einen Diagnoseabfrageplan und Zeitinformationen. Dies ähnelt den Informationen, die von Anweisungen wie EXPLAIN in anderen Datenbank- und Analysesystemen bereitgestellt werden. Diese Informationen können aus den API-Antworten von Methoden wie jobs.get abgerufen werden.

Bei Abfragen mit langer Ausführungszeit aktualisiert BigQuery diese Statistiken regelmäßig. Diese Aktualisierungen erfolgen zwar unabhängig von der Häufigkeit, mit der der Jobstatus abgefragt wird, aber in der Regel nicht häufiger als alle 30 Sekunden. Abfragejobs, die keine Ausführungsressourcen verwenden (zum Beispiel Probelaufanfragen oder Ergebnisse, die mithilfe von im Cache gespeicherten Ergebnissen bereitgestellt werden können), enthalten diese zusätzlichen Diagnoseinformationen nicht. Andere Statistiken können jedoch vorhanden sein.

Hintergrund

Bei der Ausführung eines Abfragejobs in BigQuery wird die deklarative SQL-Anweisung in eine Ausführungsgrafik umgewandelt, wobei sie in eine Reihe von Abfragephasen aufgeteilt wird, die sich wiederum aus detaillierten Sätzen von Ausführungsschritten zusammensetzen. BigQuery nutzt eine stark verteilte parallele Architektur zum Ausführen dieser Abfragen. In den Phasen werden dabei die Arbeitseinheiten modelliert, die viele potenzielle Worker parallel ausführen können. Die Phasen kommunizieren über eine schnelle verteilte Shuffle-Architektur miteinander.

Innerhalb des Abfrageplans werden die Begriffe Arbeitseinheiten und Worker verwendet, um Informationen zur Parallelisierung zu liefern. An anderer Stelle in BigQuery kann der Begriff Slot vorkommen, bei dem es sich um eine abstrahierte Darstellung mehrerer Aspekte der Abfrageausführung handelt, einschließlich Rechen-, Speicher- und E/A-Ressourcen. Jobstatistiken der obersten Ebene liefern eine Schätzung der Kosten einzelner Abfragen. Dafür wird die totalSlotMs-Schätzung der Abfrage unter Verwendung dieser abstrahierten Berechnung genutzt.

Eine weitere wichtige Eigenschaft der Architektur für die Abfrageausführung ist, dass sie dynamisch ist. Das bedeutet, dass der Abfrageplan während einer laufenden Abfrage geändert werden kann. Häufig werden während der Ausführung einer Abfrage Phasen eingeführt, um die Datenverteilung auf die Worker der Abfrage zu verbessern. Bei Abfrageplänen, in denen dies der Fall ist, werden diese Phasen normalerweise als Neupartitionierung bezeichnet.

Neben dem Abfrageplan stellen Abfragejobs auch eine Zeitachse für die Ausführung bereit, in der die abgeschlossenen, ausstehenden und aktiven Arbeitseinheiten der Abfrage-Worker aufgeführt sind. Eine Abfrage kann sich gleichzeitig in mehreren Phasen mit aktiven Workern befinden. Die Zeitachse dient dazu, den Gesamtfortschritt der Abfrage anzuzeigen.

Informationen in der Google Cloud Console ansehen

In der Google Cloud Console können Sie Details zum Abfrageplan für eine abgeschlossene Abfrage anzeigen lassen. Klicken Sie hierzu auf die Schaltfläche Ausführungsdetails (in der Nähe der Schaltfläche Ergebnisse.

Informationen des Abfrageplans

Innerhalb der API-Antwort werden Abfragepläne als Liste von Abfragephasen dargestellt. Jedes Element in der Liste enthält eine Übersichtsstatistik pro Phase, detaillierte Informationen zu jedem Schritt und zeitliche Klassifizierungen der Phasen. In der Google Cloud Console werden nicht alle Details gerendert, können aber in den API-Antworten enthalten sein.

Phasenübersicht

Die Übersichtsfelder für jede Phase können Folgendes enthalten:

API-Feld	Beschreibung
id	Eindeutige numerische ID für die Phase.
name	Einfacher zusammenfassender Name für die Phase. Die steps innerhalb der Phase liefern zusätzliche Details zu den Ausführungsschritten.
status	Ausführungsstatus der Phase. Folgende Status sind möglich: PENDING, RUNNING, COMPLETE, FAILED und CANCELLED.
inputStages	Eine Liste der IDs, die die Abhängigkeitsgrafik der Phase bilden. Eine JOIN-Phase benötigt zum Beispiel oft zwei abhängige Phasen, die die Daten auf der linken und rechten Seite der JOIN-Beziehung vorbereiten.
startMs	Zeitstempel in Millisekunden der Epoche, der angibt, wann der erste Worker in der Phase mit der Ausführung begonnen hat.
endMs	Zeitstempel in Millisekunden der Epoche, der angibt, wann der letzte Worker die Ausführung abgeschlossen hat.
steps	Detaillierte Liste der Ausführungsschritte innerhalb der Phase. Weitere Informationen finden Sie im nächsten Abschnitt.
recordsRead	Eingabegröße der Phase als Anzahl der Datensätze aller Worker der Phase.
recordsWritten	Ausgabegröße der Phase als Anzahl der Datensätze aller Worker der Phase.
parallelInputs	Anzahl der parallelisierbaren Arbeitseinheiten für die Phase. Je nach Phase und Abfrage kann hiermit die Anzahl der Spaltensegmente innerhalb einer Tabelle oder die Anzahl der Partitionen innerhalb eines Zwischen-Shuffles dargestellt sein.
completedParallelInputs	Anzahl der Arbeitseinheiten innerhalb der Phase, die abgeschlossen wurden. Bei einigen Abfragen müssen nicht alle Eingaben in einer Phase abgeschlossen sein, damit die Phase abgeschlossen werden kann.
shuffleOutputBytes	Stellt die Gesamtzahl der Byte dar, die in allen Workern in einer Abfragephase geschrieben wurden.
shuffleOutputBytesSpilled	Abfragen, die erhebliche Datenmengen zwischen Phasen übertragen, müssen möglicherweise auf eine laufwerkbasierte Übertragung zurückgreifen. Die Statistik zur Menge der übergebenen Byte informiert darüber, wie viele Daten an das Laufwerk übergeben wurden. Hängt von einem Optimierungsalgorithmus ab, sodass er nicht für jede Abfrage deterministisch ist.

Zeitliche Klassifizierung pro Phase

Die Abfragephasen ermöglichen auch eine zeitliche Klassifizierung, sowohl in absoluter als auch relativer Form. Da jede Ausführungsphase Aktivitäten von einem oder mehreren unabhängigen Workern beinhaltet, werden sowohl die durchschnittliche als auch die längste Zeitdauer angegeben. Diese Werte geben die durchschnittliche Leistung aller Worker einer Phase sowie die Leistung des langsamsten Workers einer bestimmten Klassifizierung wieder. Die durchschnittliche und maximale Zeitdauer wird darüber hinaus absolut und relativ dargestellt. Bei den verhältnisbasierten Statistiken werden die Daten als Anteil der längsten Zeit geliefert, die ein Worker in einem Segment verbracht hat.

In der Google Cloud Console werden die zeitlichen Informationen der Phasen mithilfe relativer Zeitdarstellungen angegeben.

Die zeitlichen Informationen der Phasen werden folgendermaßen dargestellt:

Relative Zeitdauer	Absolute Zeitdauer	Verhältniszähler
waitRatioAvg	waitMsAvg	Zeit, die der durchschnittliche Worker auf die Planung gewartet hat.
waitRatioMax	waitMsMax	Zeit, die der langsamste Worker auf die Planung gewartet hat.
readRatioAvg	readMsAvg	Zeit, die der durchschnittliche Worker mit dem Lesen von Eingabedaten verbracht hat.
readRatioMax	readMsMax	Zeit, die der langsamste Worker mit dem Lesen von Eingabedaten verbracht hat.
computeRatioAvg	computeMsAvg	Zeit, die der durchschnittliche Worker CPU-gebunden verbracht hat.
computeRatioMax	computeMsMax	Zeit, die der langsamste Worker CPU-gebunden verbracht hat.
writeRatioAvg	writeMsAvg	Zeit, die der durchschnittliche Worker mit dem Schreiben von Ausgabedaten verbracht hat.
writeRatioMax	writeMsMax	Zeit, die der langsamste Worker mit dem Schreiben von Ausgabedaten verbracht hat.

Schrittübersicht

Schritte enthalten die Vorgänge, die jeder Worker innerhalb einer Phase ausführt. Diese werden als sortierte Liste von Vorgängen dargestellt. Jeder Schrittvorgang hat eine Kategorie, wobei einige Vorgänge ausführlichere Informationen liefern. Im Abfrageplan können folgende Kategorien von Vorgängen vorhanden sein:

Schrittkategorie	Beschreibung
READ	Ein Lesevorgang von einer oder mehreren Spalten aus einer Eingabetabelle oder einem Zwischen-Shuffle. In den Schrittdetails werden nur die ersten sechzehn Spalten, die gelesen werden, zurückgegeben.
WRITE	Ein Schreibvorgang von mindestens einer Spalte in eine Ausgabetabelle oder ein Zwischen-Shuffle. Bei HASH-partitionierten Ausgaben aus einer Phase umfasst dies auch die Spalten, die als Partitionsschlüssel verwendet werden.
COMPUTE	Auswertung von Ausdrücken und SQL-Funktionen.
FILTER	Wird in WHERE-, OMIT IF- und HAVING-Klauseln verwendet.
SORT	ORDER BY-Vorgang, der die Spaltenschlüssel und die Sortierreihenfolge enthält.
AGGREGATE	Hier werden unter anderem Aggregationen für Klauseln wie GROUP BY oder COUNT implementiert.
LIMIT	Implementiert die LIMIT-Klausel.
JOIN	Implementiert Joins für unter anderem Klauseln wie JOIN. Enthält den Join-Typ und gegebenenfalls die Join-Bedingungen.
ANALYTIC_FUNCTION	Ein Aufruf einer Fensterfunktion (auch als "Analysefunktion" bezeichnet).
USER_DEFINED_FUNCTION	Ein Aufruf einer benutzerdefinierten Funktion.

Schritte auswerten und optimieren

In den folgenden Abschnitten wird erläutert, wie Sie die Schritte in einem Abfrageplan interpretieren und wie Sie Ihre Abfragen optimieren können.

Schritt READ

Der Schritt READ bedeutet, dass eine Phase auf Daten zur Verarbeitung zugreift. Daten können direkt aus den Tabellen gelesen werden, auf die in einer Abfrage verwiesen wird, oder aus dem Zufallsspeicher. Wenn Daten aus einer vorherigen Phase gelesen werden, liest BigQuery Daten aus dem Zufallsmix-Speicher. Die Menge der gescannten Daten wirkt sich bei der Verwendung von On-Demand-Slots auf die Kosten und bei der Verwendung von Reservierungen auf die Leistung aus.

Potenzielle Leistungsprobleme

• Großer Scan einer nicht partitionierten Tabelle:Wenn für die Abfrage nur ein kleiner Teil der Daten benötigt wird, kann dies darauf hindeuten, dass ein Tabellenscan ineffizient ist. Partitionieren könnte eine gute Optimierungsstrategie sein.
• Scan einer großen Tabelle mit einem kleinen Filterverhältnis:Dies deutet darauf hin, dass der Filter die Anzahl der gescannten Daten nicht effektiv reduziert. Überprüfen Sie gegebenenfalls die Filterbedingungen.
• Shuffle-Byte-Überlauf auf das Laufwerk:Dies deutet darauf hin, dass die Daten nicht effektiv mithilfe von Optimierungstechniken wie Clustering gespeichert werden, mit denen ähnliche Daten in Clustern aufbewahrt werden könnten.

Optimieren

• Zielgerichtetes Filtern:Verwenden Sie WHERE-Klauseln strategisch, um irrelevante Daten so früh wie möglich in der Abfrage herauszufiltern. Dadurch wird die Menge der Daten reduziert, die von der Abfrage verarbeitet werden müssen.
• Partitionierung und Clustering:In BigQuery werden Tabellenpartitionierung und ‑clustering verwendet, um bestimmte Datensegmente effizient zu finden. Achten Sie darauf, dass Ihre Tabellen basierend auf Ihren typischen Abfragemustern partitioniert und gruppiert sind, um die Menge der während der READ-Schritte gescannten Daten zu minimieren.
• Relevante Spalten auswählen:Vermeiden Sie die Verwendung von SELECT *-Anweisungen. Wählen Sie stattdessen bestimmte Spalten aus oder verwenden Sie SELECT * EXCEPT, um das Lesen unnötiger Daten zu vermeiden.
• Materialisierte Ansichten:Mithilfe von materialisierten Ansichten können häufig verwendete Aggregationen vorab berechnet und gespeichert werden. Dadurch muss bei Abfragen, die diese Ansichten verwenden, möglicherweise seltener in Basistabellen gelesen werden.READ

Schritt COMPUTE

Im Schritt COMPUTE führt BigQuery die folgenden Aktionen an Ihren Daten aus:

• Ausdrücke in den SELECT-, WHERE-, HAVING- und anderen Klauseln der Abfrage werden ausgewertet, einschließlich Berechnungen, Vergleichen und logischen Operationen.
• Führt integrierte SQL-Funktionen und benutzerdefinierte Funktionen aus.
• Filtert Datenzeilen anhand von Bedingungen in der Abfrage.

Optimieren

Der Abfrageplan kann Engpässe im Schritt COMPUTE aufzeigen. Suchen Sie nach Phasen mit umfangreichen Berechnungen oder einer großen Anzahl verarbeiteter Zeilen.

• COMPUTE-Schritt mit dem Datenvolumen in Beziehung setzen:Wenn eine Phase eine erhebliche Berechnung aufweist und große Datenmengen verarbeitet, ist sie möglicherweise ein guter Kandidat für die Optimierung.
• Schiefe Daten: Bei Phasen, in denen das maximale Rechenvolumen deutlich über dem durchschnittlichen Rechenvolumen liegt, wurde in der Phase ein unverhältnismäßig großer Teil der Zeit für die Verarbeitung weniger Datensätze aufgewendet. Sehen Sie sich die Datenverteilung an, um festzustellen, ob es Abweichungen gibt.
• Datentypen:Verwenden Sie für Ihre Spalten geeignete Datentypen. So kann die Leistung beispielsweise durch die Verwendung von Ganzzahlen, Datumsangaben und Zeitstempeln anstelle von Strings verbessert werden.

Schritt WRITE

WRITE Schritte für Zwischendaten und Endausgabe.

• Schreiben in den Zufallsmix-Speicher:Bei einer mehrstufigen Abfrage umfasst der Schritt WRITE häufig das Senden der verarbeiteten Daten zur weiteren Verarbeitung an eine andere Phase. Das ist typisch für den Zufallsmix-Speicher, in dem Daten aus mehreren Quellen kombiniert oder aggregiert werden. Die in dieser Phase geschriebenen Daten sind in der Regel ein Zwischenergebnis und nicht die endgültige Ausgabe.
• Endgültige Ausgabe:Das Abfrageergebnis wird entweder in die Zieltabelle oder in eine temporäre Tabelle geschrieben.

Hash-Partitionierung

Wenn eine Phase im Abfrageplan Daten in eine hash-partitionierte Ausgabe schreibt, schreibt BigQuery die in der Ausgabe enthaltenen Spalten und die Spalte, die als Partitionsschlüssel ausgewählt wurde.

Optimieren

Der Schritt WRITE selbst kann zwar nicht direkt optimiert werden, aber wenn Sie seine Rolle verstehen, können Sie potenzielle Engpässe in früheren Phasen erkennen:

• Schriftvorgang minimieren:Konzentrieren Sie sich darauf, die vorherigen Phasen durch Filtern und Aggregation zu optimieren, um die Menge der bei diesem Schritt geschriebenen Daten zu reduzieren.

• Partitionierung:Schreibvorgänge profitieren stark von der Tabellenpartitionierung. Wenn die von Ihnen geschriebenen Daten auf bestimmte Partitionen beschränkt sind, kann BigQuery schneller schreiben.

  Wenn die DML-Anweisung eine WHERE-Klausel mit einer statischen Bedingung für eine Tabellenpartitionsspalte enthält, werden in BigQuery nur die entsprechenden Tabellenpartitionen geändert.

• Vor- und Nachteile der Denormalisierung:Die Denormalisierung kann manchmal zu kleineren Ergebnismengen im Zwischenschritt WRITE führen. Es gibt jedoch Nachteile wie eine erhöhte Speichernutzung und Probleme mit der Datenkonsistenz.

Schritt JOIN

Im Schritt JOIN werden in BigQuery Daten aus zwei Datenquellen kombiniert. Zusammenführungen können Zusammenführungsbedingungen enthalten. Zusammenführungen sind ressourcenintensiv. Beim Zusammenführen großer Datenmengen in BigQuery werden die Join-Schlüssel unabhängig voneinander neu angeordnet, damit sie sich auf denselben Steckplatz ausrichten. So wird der Join lokal auf jedem Steckplatz ausgeführt.

Der Abfrageplan für den Schritt JOIN enthält in der Regel die folgenden Details:

• Join-Muster:Gibt den verwendeten Join-Typ an. Mit jedem Typ wird festgelegt, wie viele Zeilen aus den zusammengeführten Tabellen in den Ergebnissatz aufgenommen werden.
• Join-Spalten:Mit diesen Spalten werden Zeilen zwischen den Datenquellen abgeglichen. Die Auswahl der Spalten ist entscheidend für die Join-Leistung.

Muster zusammenführen

• Broadcast-Join: Wenn eine Tabelle, in der Regel die kleinere, auf einem einzelnen Arbeitsknoten oder -slot in den Arbeitsspeicher passt, kann BigQuery sie an alle anderen Knoten übertragen, um den Join effizient auszuführen. Suchen Sie in den Schrittdetails nach JOIN EACH WITH ALL.
• Hash-Join:Wenn Tabellen groß sind oder ein Broadcast-Join nicht geeignet ist, kann ein Hash-Join verwendet werden. BigQuery verwendet Hash- und Zufallsmix-Vorgänge, um die linke und rechte Tabelle so zu mischen, dass die übereinstimmenden Schlüssel im selben Steckplatz landen, um einen lokalen Join auszuführen. Hash-Joins sind ein teurer Vorgang, da die Daten verschoben werden müssen. Sie ermöglichen jedoch eine effiziente Übereinstimmung von Zeilen über Hashes hinweg. Suchen Sie in den Schrittdetails nach JOIN EACH WITH EACH.
• Selbstverknüpfung: Ein SQL-Anti-Muster, bei dem eine Tabelle mit sich selbst verknüpft wird.
• Kreuzjoin: Ein SQL-Antipattern, das zu erheblichen Leistungsproblemen führen kann, da es mehr Ausgabedaten als Eingaben generiert.
• Schiefer Join: Die Datenverteilung über den Join-Schlüssel in einer Tabelle ist sehr schief und kann zu Leistungsproblemen führen. Suchen Sie nach Fällen, in denen die maximale Rechenzeit viel länger ist als die durchschnittliche Rechenzeit im Abfrageplan. Weitere Informationen finden Sie unter Join mit hoher Kardinalität und Partitionsschiefverteilung.

Debugging

• Großes Datenvolumen:Wenn der Abfrageplan eine erhebliche Menge an Daten anzeigt, die während des Schritts JOIN verarbeitet werden, prüfen Sie die Join-Bedingung und die Join-Schlüssel. Sie können Filter verwenden oder selektivere Zusammenführungsschlüssel verwenden.
• Schiefe Datenverteilung: Analysieren Sie die Datenverteilung von Join-Schlüsseln. Wenn eine Tabelle stark verzerrt ist, können Sie Strategien wie die Aufteilung der Abfrage oder die Vorabfilterung ausprobieren.
• Joins mit hoher Kardinalität:Joins, die wesentlich mehr Zeilen erzeugen als die Anzahl der linken und rechten Eingabezeilen, können die Abfrageleistung drastisch beeinträchtigen. Vermeiden Sie Joins, die eine sehr große Anzahl von Zeilen erzeugen.
• Falsche Tabellensortierung:Achten Sie darauf, den richtigen Join-Typ wie INNER oder LEFT auszuwählen und die Tabellen gemäß den Anforderungen Ihrer Abfrage von der größten zur kleinsten zu sortieren.

Optimieren

• Selektive Join-Schlüssel:Verwenden Sie nach Möglichkeit INT64 anstelle von STRING für Join-Schlüssel. STRING-Vergleiche sind langsamer als INT64-Vergleiche, da bei ihnen jedes Zeichen in einem String verglichen wird. Bei Ganzzahlen ist nur ein Vergleich erforderlich.
• Vor dem Zusammenführen filtern:Wenden Sie vor dem Zusammenführen WHERE-Klauseln auf einzelne Tabellen an. Dadurch wird die Datenmenge reduziert, die für den Zusammenführungsvorgang erforderlich ist.
• Vermeiden Sie Funktionen in Join-Spalten:Funktionen in Join-Spalten sollten nicht aufgerufen werden. Standardisieren Sie Ihre Tabellendaten stattdessen während der Datenaufnahme oder nach der Datenaufnahme mithilfe von ELT-SQL-Pipelines. Bei diesem Ansatz müssen Join-Spalten nicht mehr dynamisch geändert werden. Dies ermöglicht effizientere Joins, ohne die Datenintegrität zu gefährden.
• Self Joins vermeiden:Self Joins werden häufig verwendet, um zeilenabhängige Beziehungen zu berechnen. Selbstbezüge können jedoch die Anzahl der Ausgabezeilen potenziell vervierfachen, was zu Leistungsproblemen führen kann. Verwenden Sie stattdessen Fensterfunktionen (analytische Funktionen).
• Große Tabellen zuerst:Auch wenn der SQL-Abfrageoptimierer festlegen kann, welche Tabelle auf welcher Seite der Verknüpfung stehen sollte, sollten Sie die verknüpften Tabellen entsprechend sortieren. Am sinnvollsten ist es, die größte Tabelle an die erste Stelle zu setzen, gefolgt von der kleinsten, und dann in absteigender Reihenfolge fortzufahren.
• Denormalisierung: In einigen Fällen kann die strategische Denormalisierung von Tabellen (Hinzufügen redundanter Daten) Joins vollständig eliminieren. Dieser Ansatz hat jedoch Nachteile in Bezug auf Speicherplatz und Datenkonsistenz.
• Partitionierung und Clustering: Wenn Sie Tabellen basierend auf Join-Schlüsseln partitionieren und gemeinsam gespeicherte Daten clustern, können Sie Joins erheblich beschleunigen, da BigQuery dann relevante Datenpartitionen ansteuern kann.
• Optimierung von verzerrten Joins:Um Leistungsprobleme aufgrund von verzerrten Joins zu vermeiden, sollten Sie Daten aus der Tabelle so früh wie möglich vorfiltern oder die Abfrage in zwei oder mehr Abfragen aufteilen.

Schritt AGGREGATE

Im Schritt AGGREGATE werden Daten in BigQuery zusammengefasst und gruppiert.

Debugging

• Details zur Phase:Hier sehen Sie die Anzahl der Eingabezeilen für die Aggregation und die Ausgabezeilen aus der Aggregation sowie die Größe des Zufallsmixes. So können Sie ermitteln, wie stark die Daten durch den Aggregationsschritt reduziert wurden und ob ein Zufallsmix verwendet wurde.
• Shuffle-Größe:Eine große Shuffle-Größe kann darauf hinweisen, dass während der Aggregation eine erhebliche Menge an Daten zwischen Worker-Knoten verschoben wurde.
• Datenverteilung prüfen:Achten Sie darauf, dass die Daten gleichmäßig auf die Partitionen verteilt sind. Eine verzerrte Datenverteilung kann zu ungleich verteilten Arbeitslasten im Aggregationsschritt führen.
• Aggregationen überprüfen:Prüfen Sie die Aggregationsklauseln, um sicherzustellen, dass sie erforderlich und effizient sind.

Optimieren

• Clustering:Sie können Ihre Tabellen nach Spalten gruppieren, die häufig in GROUP BY, COUNT oder anderen Aggregationsklauseln verwendet werden.
• Partitionierung:Wählen Sie eine Partitionierungsstrategie aus, die zu Ihren Abfragemustern passt. Sie können Tabellen verwenden, die nach Aufnahmezeit partitioniert sind, um die Menge der bei der Aggregation gescannten Daten zu reduzieren.
• Früher aggregieren:Führen Sie nach Möglichkeit Aggregationen früher in der Abfragepipeline durch. So lässt sich die Menge der Daten reduzieren, die bei der Aggregation verarbeitet werden müssen.
• Zufallsmix optimieren:Wenn das Zufallsmixen ein Engpass ist, suchen Sie nach Möglichkeiten, ihn zu minimieren. Sie können beispielsweise Tabellen denormalisieren oder Clustering verwenden, um relevante Daten zusammenzuführen.

Sonderfälle

• DISTINCT-Aggregationen:Abfragen mit DISTINCT-Aggregationen können rechenaufwendig sein, insbesondere bei großen Datenmengen. Für ungefähre Ergebnisse können Sie Alternativen wie APPROX_COUNT_DISTINCT verwenden.
• Große Anzahl von Gruppen:Wenn die Abfrage eine große Anzahl von Gruppen liefert, kann dies viel Arbeitsspeicher beanspruchen. In solchen Fällen sollten Sie die Anzahl der Gruppen begrenzen oder eine andere Aggregationsstrategie verwenden.

Schritt REPARTITION

Sowohl REPARTITION als auch COALESCE sind Optimierungstechniken, die BigQuery direkt auf die zufällig sortierten Daten in der Abfrage anwendet.

• REPARTITION:Mit diesem Vorgang soll die Datenverteilung auf den Arbeitsknoten neu ausgeglichen werden. Angenommen, nach dem Zufallsmix landet ein Workerknoten mit einer unverhältnismäßig großen Datenmenge. Im Schritt REPARTITION werden die Daten gleichmäßiger verteilt, damit kein einzelner Worker zu einem Engpass wird. Das ist besonders wichtig für rechenintensive Vorgänge wie Joins.
• COALESCE:Dieser Schritt wird ausgeführt, wenn nach dem Zufallsmix viele kleine Datenblöcke vorhanden sind. Im Schritt COALESCE werden diese Bucket in größere Bucket kombiniert, um den Overhead bei der Verwaltung zahlreicher kleiner Daten zu reduzieren. Das kann besonders bei sehr kleinen Zwischenergebnismengen von Vorteil sein.

Wenn Sie REPARTITION- oder COALESCE-Schritte in Ihrem Abfrageplan sehen, bedeutet das nicht unbedingt, dass ein Problem mit Ihrer Abfrage vorliegt. Dies ist oft ein Zeichen dafür, dass BigQuery die Datenverteilung proaktiv für eine bessere Leistung optimiert. Wenn diese Vorgänge jedoch wiederholt auftreten, kann das darauf hindeuten, dass Ihre Daten von Natur aus verzerrt sind oder dass Ihre Abfrage zu einem übermäßigen Daten-Shuffle führt.

Optimieren

So verringern Sie die Anzahl der REPARTITION-Schritte:

• Datenverteilung:Achten Sie darauf, dass Ihre Tabellen effektiv partitioniert und gruppiert sind. Durch gut verteilte Daten wird die Wahrscheinlichkeit von erheblichen Ungleichgewichten nach dem Zufallsmix verringert.
• Abfragestruktur: Analysieren Sie die Abfrage auf potenzielle Quellen für Datenabweichungen. Gibt es beispielsweise sehr selektive Filter oder Joins, die dazu führen, dass nur eine kleine Teilmenge von Daten auf einem einzelnen Worker verarbeitet wird?
• Join-Strategien:Testen Sie verschiedene Join-Strategien, um festzustellen, ob sie zu einer ausgewogeneren Datenverteilung führen.

So verringern Sie die Anzahl der COALESCE-Schritte:

• Aggregationsstrategien:Sie sollten Aggregationen früher in der Abfragepipeline ausführen. So lässt sich die Anzahl kleiner Zwischenergebnismengen reduzieren, die zu COALESCE-Schritten führen können.
• Datenvolumen:Bei sehr kleinen Datenmengen ist COALESCE möglicherweise kein großes Problem.

Übertreiben Sie es nicht mit der Optimierung. Eine vorzeitige Optimierung kann Ihre Abfragen komplexer machen, ohne dass Sie dadurch nennenswerte Vorteile erzielen.

Erläuterung zu föderierten Abfragen

Mit föderierten Abfragen können Sie eine Abfrageanweisung mithilfe der Funktion EXTERNAL_QUERY an eine externe Datenquelle senden. Föderierte Abfragen unterliegen der Optimierungstechnik, die als SQL-Pushdown bezeichnet wird. Der Abfrageplan zeigt gegebenenfalls Vorgänge an, die an die externe Datenquelle übergeben wurden. Nehmen wir beispielsweise an, dass Sie die folgende Abfrage ausführen:

SELECT id, name
FROM EXTERNAL_QUERY("<connection>", "SELECT * FROM company")
WHERE country_code IN ('ee', 'hu') AND name like '%TV%'

Der Abfrageplan zeigt die folgenden Phasenschritte an:

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, country_code
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
  WHERE in(country_code, 'ee', 'hu')
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

In diesem Plan steht table_for_external_query_$_0(...) für die Funktion EXTERNAL_QUERY. In den Klammern sehen Sie die Abfrage, die von der externen Datenquelle ausgeführt wird. Daraus ergeben sich folgende Erkenntnisse:

• Eine externe Datenquelle gibt nur drei ausgewählte Spalten zurück.
• Eine externe Datenquelle gibt nur Zeilen zurück, für die country_code entweder 'ee' oder 'hu' ist.
• Der Operator LIKE wird nicht per Push übertragen und wird aber von BigQuery ausgewertet.

Wenn keine Push-downs vorhanden sind, zeigt der Abfrageplan die folgenden Phasenschritte an:

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, description, country_code, primary_address, secondary address
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

Dieses Mal gibt eine externe Datenquelle alle Spalten und Zeilen aus der Tabelle company zurück und BigQuery führt die Filterung durch.

Metadaten der Zeitachse

Die Abfragezeitachse zeigt den Fortschritt zu bestimmten Zeitpunkten und bietet aktuelle Ansichten des gesamten Abfragefortschritts. Die Zeitachse wird als eine Reihe von Stichproben dargestellt, die folgende Details enthalten:

Feld	Beschreibung
elapsedMs	Seit Beginn der Abfrageausführung verstrichene Millisekunden.
totalSlotMs	Eine kumulative Darstellung der von der Abfrage verwendeten Slot-Millisekunden.
pendingUnits	Gesamtzahl der geplanten Arbeitseinheiten, die auf Ausführung warten.
activeUnits	Gesamtzahl der aktiven Arbeitseinheiten, die derzeit von den Workern verarbeitet werden.
completedUnits	Gesamtzahl der Arbeitseinheiten, die während der Ausführung dieser Abfrage abgeschlossen wurden.

Beispielabfrage

Die folgende Abfrage zählt die Anzahl der Zeilen im öffentlichen Shakespeare-Dataset und hat eine zweite bedingte Zählung, deren Ergebnisse auf Zeilen beschränkt sind, die auf "Hamlet" verweisen:

SELECT
  COUNT(1) as rowcount,
  COUNTIF(corpus = 'hamlet') as rowcount_hamlet
FROM `publicdata.samples.shakespeare`

Klicken Sie auf Ausführungsdetails, um den Abfrageplan anzuzeigen:

Die Farbindikatoren zeigen die relative Zeitdauer für alle Schritte in allen Phasen an.

Um mehr über die Schritte der Ausführungsphasen zu erfahren, klicken Sie auf das , woraufhin die Details für die Phase maximiert werden:

In diesem Beispiel war die längste Zeit in einem Segment die Zeit, die der einzelne Worker in Phase 01 auf den Abschluss von Phase 00 gewartet hat. Dies liegt daran, dass Phase 01 von der Eingabe der Phase 00 abhängig war und erst gestartet werden konnte, nachdem die Ausgabedaten der ersten Phase in den Zwischen-Shuffle geschrieben wurden.

Fehlerberichte

Abfragejobs können während der Ausführung fehlschlagen. Da die Informationen des Abfrageplans regelmäßig aktualisiert werden, können Sie sehen, an welcher Stelle innerhalb der Ausführungsgrafik der Fehler aufgetreten ist. In der Google Cloud Console wird durch ein Häkchen bzw. ein Ausrufezeichen neben den Phasennamen angezeigt, ob eine Phase erfolgreich war oder fehlgeschlagen ist.

Weitere Informationen zum Interpretieren und Beheben von Fehlern finden Sie in der Anleitung zur Fehlerbehebung.

API-Beispieldarstellung

Die Informationen des Abfrageplans sind in der Jobantwort enthalten und können durch Aufrufen von jobs.get abgerufen werden. Der folgende Code ist ein Auszug aus einer JSON-Antwort für einen Job, der die Beispielabfrage zu Hamlet zurückgibt und Informationen sowohl zum Abfrageplan als auch zur Zeitachse anzeigt.

"statistics": {
  "creationTime": "1576544129234",
  "startTime": "1576544129348",
  "endTime": "1576544129681",
  "totalBytesProcessed": "2464625",
  "query": {
    "queryPlan": [
      {
        "name": "S00: Input",
        "id": "0",
        "startMs": "1576544129436",
        "endMs": "1576544129465",
        "waitRatioAvg": 0.04,
        "waitMsAvg": "1",
        "waitRatioMax": 0.04,
        "waitMsMax": "1",
        "readRatioAvg": 0.32,
        "readMsAvg": "8",
        "readRatioMax": 0.32,
        "readMsMax": "8",
        "computeRatioAvg": 1,
        "computeMsAvg": "25",
        "computeRatioMax": 1,
        "computeMsMax": "25",
        "writeRatioAvg": 0.08,
        "writeMsAvg": "2",
        "writeRatioMax": 0.08,
        "writeMsMax": "2",
        "shuffleOutputBytes": "18",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "164656",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$1:corpus",
              "FROM publicdata.samples.shakespeare"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$20 := COUNT($30)",
              "$21 := COUNTIF($31)"
            ]
          },
          {
            "kind": "COMPUTE",
            "substeps": [
              "$30 := 1",
              "$31 := equal($1, 'hamlet')"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$20, $21",
              "TO __stage00_output"
            ]
          }
        ]
      },
      {
        "name": "S01: Output",
        "id": "1",
        "startMs": "1576544129465",
        "endMs": "1576544129480",
        "inputStages": [
          "0"
        ],
        "waitRatioAvg": 0.44,
        "waitMsAvg": "11",
        "waitRatioMax": 0.44,
        "waitMsMax": "11",
        "readRatioAvg": 0,
        "readMsAvg": "0",
        "readRatioMax": 0,
        "readMsMax": "0",
        "computeRatioAvg": 0.2,
        "computeMsAvg": "5",
        "computeRatioMax": 0.2,
        "computeMsMax": "5",
        "writeRatioAvg": 0.16,
        "writeMsAvg": "4",
        "writeRatioMax": 0.16,
        "writeMsMax": "4",
        "shuffleOutputBytes": "17",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "1",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$20, $21",
              "FROM __stage00_output"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$10 := SUM_OF_COUNTS($20)",
              "$11 := SUM_OF_COUNTS($21)"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$10, $11",
              "TO __stage01_output"
            ]
          }
        ]
      }
    ],
    "estimatedBytesProcessed": "2464625",
    "timeline": [
      {
        "elapsedMs": "304",
        "totalSlotMs": "50",
        "pendingUnits": "0",
        "completedUnits": "2"
      }
    ],
    "totalPartitionsProcessed": "0",
    "totalBytesProcessed": "2464625",
    "totalBytesBilled": "10485760",
    "billingTier": 1,
    "totalSlotMs": "50",
    "cacheHit": false,
    "referencedTables": [
      {
        "projectId": "publicdata",
        "datasetId": "samples",
        "tableId": "shakespeare"
      }
    ],
    "statementType": "SELECT"
  },
  "totalSlotMs": "50"
},

Ausführungsinformationen verwenden

BigQuery-Abfragepläne liefern Informationen darüber, wie der Dienst Abfragen ausführt. Da es sich um einen verwalteten Dienst handelt, sind die Möglichkeiten, einige Details direkt zu verwerten, jedoch begrenzt. Viele Optimierungen erfolgen automatisch durch Nutzung des Dienstes. Dies kann sich von anderen Umgebungen unterscheiden, in denen Optimierung, Bereitstellung und Monitoring spezialisiertes, fachkundiges Personal erfordern.

Informationen zu konkreten Verfahren, die die Ausführung und Leistung von Abfragen verbessern können, finden Sie in der Dokumentation zu Best Practices. Anhand des Abfrageplans und der Zeitachsenstatistiken können Sie nachvollziehen, ob die Ressourcenauslastung in bestimmten Phasen besonders hoch ist. Eine JOIN-Phase, in der weit mehr Ausgabezeilen als Eingabezeilen generiert werden, könnte zum Beispiel darauf hindeuten, dass früher in der Abfrage gefiltert werden sollte.

Darüber hinaus können Sie mithilfe der Informationen der Zeitachse feststellen, ob eine bestimmte Abfrage langsam ist, weil sie isoliert ausgeführt wird oder weil sie mit anderen Abfragen um dieselben Ressourcen konkurriert. Wenn die Anzahl der aktiven Einheiten während der gesamten Lebensdauer der Abfrage begrenzt ist, die Menge der in der Warteschlange enthaltenen Arbeitseinheiten jedoch hoch bleibt, spricht dies möglicherweise dafür, dass durch Reduzierung der Anzahl gleichzeitiger Abfragen die Ausführungszeit für bestimmte Abfragen erheblich verkürzt werden kann.

Hinweis :Einige Abfragen werden außerhalb des Kontexts einer bestimmten Phase verarbeitet. In einigen Fällen kann es zu einer erheblichen Latenz oder Slotnutzung kommen, bevor die erste Phase gesendet oder die letzte Phase abgeschlossen wird. Beispiele hierfür sind einige Formen der Partitionsbereinigung, verschiedene Metadatenvorgänge und die Kompensation für eine übermäßige Steckplatznutzung.