Best Practices für SQL

Wie in Abfrageausführungsplänen beschrieben, transformiert der SQL-Compiler eine SQL-Anweisung in einen Abfrageausführungsplan, der verwendet wird, um die Ergebnisse der Abfrage zu erhalten. Auf dieser Seite werden Best Practices für das Erstellen von SQL-Anweisungen beschrieben, die Spanner dabei helfen, effiziente Ausführungspläne zu finden.

Für die auf dieser Seite gezeigten SQL-Beispielanweisungen wird das folgende Beispielschema verwendet:

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 BirthDate  DATE
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ReleaseDate  DATE
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Die vollständige SQL-Referenz finden Sie unter Anweisungssyntax, Funktionen und Operatoren und Lexikalische Struktur und Syntax.

PostgreSQL

CREATE TABLE Singers (
 SingerId   BIGINT PRIMARY KEY,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024),
 SingerInfo BYTEA,
 BirthDate  TIMESTAMPTZ
);

CREATE TABLE Albums (
 SingerId        BIGINT NOT NULL,
 AlbumId         BIGINT NOT NULL,
 AlbumTitle      VARCHAR(1024),
 ReleaseDate     DATE,
 PRIMARY KEY(SingerId, AlbumId),
 FOREIGN KEY (SingerId) REFERENCES Singers(SingerId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Weitere Informationen finden Sie unter Die PostgreSQL-Sprache in Spanner.

Abfrageparameter verwenden

Spanner unterstützt Abfrageparameter, um die Leistung zu erhöhen und eine SQL-Injection zu verhindern, wenn Abfragen mit Nutzereingaben erstellt werden. Sie können Abfrageparameter als Ersatz für beliebige Ausdrücke verwenden, jedoch nicht als Ersatz für Kennzeichnungen, Spaltennamen, Tabellennamen oder andere Teile der Abfrage.

Parameter können überall dort vorkommen, wo ein Literalwert erwartet wird. Derselbe Parametername kann in einer einzelnen SQL-Anweisung mehrmals verwendet werden.

Zusammenfassend lässt sich sagen, dass Suchparameter die Ausführung von Abfragen auf folgende Arten unterstützen:

• Voroptimierte Pläne: Abfragen, die Parameter verwenden, können bei jedem Aufruf schneller ausgeführt werden, da Spanner durch die Parametrisierung das Speichern des Ausführungsplans im Cache erleichtert.
• Vereinfachte Abfragezusammensetzung: Stringwerte müssen nicht maskiert werden, wenn sie in Abfrageparametern bereitgestellt werden. Abfrageparameter verringern auch das Risiko von Syntaxfehlern.
• Sicherheit: Abfrageparameter machen Abfragen sicherer, da sie Sie vor verschiedenen SQL-Injection-Angriffen schützen. Dieser Schutz ist besonders wichtig für Abfragen, die Sie aus Nutzereingaben erstellen.

So führt Spanner Abfragen aus

Hinweis: Spanner Studio (in der Google Cloud Console früher als Query bezeichnet) unterstützt SQL-, DML- und DDL-Vorgänge in einem einzigen Editor. Weitere Informationen finden Sie unter Daten mit der Google Cloud Console verwalten.

Mit Spanner können Sie Datenbanken mithilfe von deklarativen SQL-Anweisungen abfragen, in denen angegeben ist, welche Daten abgerufen werden sollen. Wenn Sie wissen möchten, wie Spanner die Ergebnisse erhält, prüfen Sie den Ausführungsplan für die Abfrage. Ein Abfrageausführungsplan zeigt die mit jedem Schritt der Abfrage verbundenen Berechnungskosten an. Mit diesen Kosten können Sie Leistungsprobleme bei Abfragen beheben und Ihre Abfrage optimieren. Weitere Informationen finden Sie unter Abfrageausführungspläne.

Sie können Abfrageausführungspläne über die Google Cloud Console oder die Clientbibliotheken abrufen.

So rufen Sie über die Google Cloud Console einen Abfrageausführungsplan für eine bestimmte Abfrage ab:

1. Öffnen Sie die Seite mit den Spanner-Instanzen.

   Spanner-Instanzen aufrufen

2. Wählen Sie die Namen der Spanner-Instanz und der Datenbank aus, die Sie abfragen möchten.

3. Klicken Sie im linken Navigationsbereich auf Spanner Studio.

4. Geben Sie die Abfrage in das Textfeld ein und klicken Sie auf Abfrage ausführen.

5. Klicken Sie auf Erläuterung
   . In der Google Cloud Console wird ein visueller Ausführungsplan für die Abfrage angezeigt.

   

Weitere Informationen zum Verständnis visueller Pläne und deren Verwendung zum Debuggen Ihrer Abfragen finden Sie unter Abfragen mit der Abfrageplan-Visualisierung abstimmen.

Sie können sich auch Beispiele für bisherige Abfragepläne ansehen und die Leistung einer Abfrage im Zeitverlauf für bestimmte Abfragen vergleichen. Weitere Informationen finden Sie unter Stichproben für Abfragepläne.

Sekundäre Indexe verwenden

Wie andere relationale Datenbanken bietet Spanner sekundäre Indexe, mit denen Sie Daten entweder mit einer SQL-Anweisung oder über die Leseschnittstelle von Spanner abrufen können. Die gebräuchlichste Methode zum Abrufen von Daten aus einem Index ist die Verwendung von Spanner Studio. Mit einem sekundären Index in einer SQL-Abfrage können Sie angeben, wie Spanner die Ergebnisse erhalten soll. Das Angeben eines sekundären Index kann die Ausführung von Abfragen beschleunigen.

Angenommen, Sie möchten die IDs aller Sänger mit einem bestimmten Nachnamen abrufen. Hier ist eine Möglichkeit, eine solche SQL-Abfrage zu schreiben:

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

Diese Abfrage würde die erwarteten Ergebnisse zurückgeben. Es kann jedoch lange dauern. Die Dauer hängt von der Anzahl der Zeilen in der Tabelle Singers ab und davon, wie viele das Prädikat WHERE s.LastName = 'Smith' erfüllen. Wenn es keinen sekundären Index gibt, der die Spalte LastName enthält, aus der gelesen werden soll, liest der Abfrageplan die gesamte Tabelle Singers, um Zeilen zu finden, die mit dem Prädikat übereinstimmen. Das Lesen der gesamten Tabelle wird als vollständiger Tabellenscan bezeichnet. Ein vollständiger Tabellenscan ist eine teure Methode, um die Ergebnisse zu erhalten, wenn die Tabelle nur einen kleinen Prozentsatz von Singers mit diesem Nachnamen enthält.

Sie können die Leistung dieser Abfrage verbessern, wenn Sie einen sekundären Index für die Nachname-Spalte definieren:

CREATE INDEX SingersByLastName ON Singers (LastName);

Da der sekundäre Index SingersByLastName die indexierte Tabellenspalte LastName und die Primärschlüsselspalte SingerId enthält, kann Spanner alle Daten aus der viel kleineren Indextabelle abrufen, anstatt die gesamte Tabelle Singers zu scannen.

In diesem Szenario verwendet Spanner beim Ausführen der Abfrage automatisch den sekundären Index SingersByLastName (sofern seit der Datenbankerstellung drei Tage vergangen sind; siehe Hinweis zu neuen Datenbanken). Es empfiehlt sich jedoch, Spanner explizit anzuweisen, diesen Index zu verwenden, indem Sie in der FROM-Klausel eine Indexanweisung angeben:

GoogleSQL

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

 SELECT s.SingerId
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Angenommen, Sie möchten neben der ID auch den Vornamen des Sängers abrufen. Auch wenn die Spalte FirstName nicht im Index enthalten ist, sollten Sie die Indexanweisung wie zuvor angeben:

GoogleSQL

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

SELECT s.SingerId, s.FirstName
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Die Verwendung des Index bietet dennoch einen Leistungsvorteil, da Spanner beim Ausführen des Abfrageplans keinen vollständigen Tabellenscan ausführen muss. Stattdessen wählt er die Teilmenge der Zeilen aus, die das Prädikat aus dem Index SingersByLastName erfüllen, und führt dann eine Suche aus der Basistabelle Singers durch, um den ersten Namen nur für diese Teilmenge von Zeilen abzurufen.

Wenn Sie möchten, dass Spanner überhaupt keine Zeilen aus der Basistabelle abrufen muss, können Sie eine Kopie der Spalte FirstName im Index selbst speichern:

GoogleSQL

CREATE INDEX SingersByLastName ON Singers (LastName) STORING (FirstName);

PostgreSQL

CREATE INDEX SingersByLastName ON Singers (LastName) INCLUDE (FirstName);

Die Verwendung einer STORING-Klausel (für den GoogleSQL-Dialekt) oder einer INCLUDE-Klausel (für den PostgreSQL-Dialekt) wie diese kostet zwar zusätzlichen Speicher, bietet aber folgende Vorteile:

• SQL-Abfragen, die den Index verwenden und Spalten auswählen, die in der STORING- oder INCLUDE-Klausel gespeichert sind, benötigen keine zusätzliche Verknüpfung mit der Basistabelle.
• Leseaufrufe, die den Index verwenden, können in der STORING- oder INCLUDE-Klausel gespeicherte Spalten lesen.

Die Beispiele oben zeigen, wie sekundäre Indexe Abfragen beschleunigen können, wenn die von der WHERE-Klausel einer Abfrage ausgewählten Zeilen mit dem sekundären Index schnell identifiziert werden können.

Ein weiteres Szenario, in dem sekundäre Indexe Leistungsvorteile bieten können, sind bestimmte Abfragen, die geordnete Ergebnisse zurückgeben. Angenommen, Sie möchten alle Albumtitel und deren Veröffentlichungsdaten in aufsteigender und absteigender Reihenfolge nach Albumtiteln abrufen. Eine SQL-Abfrage könnte zum Beispiel so geschrieben werden:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Ohne einen sekundären Index erfordert diese Abfrage möglicherweise einen teuren Sortierschritt im Ausführungsplan. Sie können die Abfrageausführung beschleunigen, wenn Sie diesen sekundären Index definieren:

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

Schreiben Sie dann die Abfrage um, um den sekundären Index zu verwenden:

GoogleSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

PostgreSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums /*@ FORCE_INDEX=AlbumsByReleaseDateTitleDesc */ AS s
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Diese Abfrage und diese Indexdefinition erfüllen die beiden folgenden Kriterien:

• Wenn Sie den Sortierschritt entfernen möchten, muss die Spaltenliste in der ORDER BY-Klausel ein Präfix der Indexschlüsselliste sein.
• Achten Sie darauf, dass der Index alle Spalten der von der Abfrage verwendeten Spalten in der Tabelle abdeckt, damit keine fehlenden Spalten abgerufen werden.

Obwohl sekundäre Indexe häufige Abfragen beschleunigen können, kann das Hinzufügen sekundärer Indexe zu Latenz bei Ihren Commit-Vorgängen führen, da für jeden sekundären Index in der Regel ein zusätzlicher Knoten bei jedem Commit erforderlich ist. Bei den meisten Arbeitslasten ist es ausreichend, einige wenige sekundäre Indizes zu haben. Sie sollten jedoch überlegen, ob Ihnen Leselatenz oder Schreiblatenz wichtiger ist und welche Vorgänge für Ihre Arbeitslast am wichtigsten sind. Benchmarking der Arbeitslast, um sicherzustellen, dass sie die erwartete Leistung erbringt.

Die vollständige Referenz zu sekundären Indexen finden Sie unter Sekundäre Indexe.

Scans optimieren

Bestimmte Spanner-Abfragen können beim Scannen von Daten von der Verwendung einer Batch-orientierten Verarbeitungsmethode anstelle der gebräuchlicheren zeilenorientierten Verarbeitungsmethode profitieren. Die Verarbeitung von Scans in Batches ist eine effizientere Methode, um große Datenmengen gleichzeitig zu verarbeiten, und ermöglicht Abfragen, eine geringere CPU-Auslastung und -Latenz zu erzielen.

Der Spanner-Scanvorgang startet die Ausführung immer im zeilenorientierten Modus. Während dieser Zeit erfasst Spanner mehrere Laufzeitmesswerte. Dann wendet Spanner eine Reihe von Heuristiken an, die auf dem Ergebnis dieser Messwerte basieren, um den optimalen Scanmodus zu bestimmen. Gegebenenfalls wechselt Spanner in einen Batch-orientierten Verarbeitungsmodus, um den Scandurchsatz und die Leistung zu verbessern.

Gängige Anwendungsfälle

Abfragen mit den folgenden Eigenschaften profitieren im Allgemeinen von der Batch-orientierten Verarbeitung:

• Große Scans von selten aktualisierten Daten.
• Scannt mit Prädikaten für Spalten mit fester Breite.
• Scans mit vielen Suchläufen. Bei einer Suche wird ein Index zum Abrufen von Datensätzen verwendet.

Anwendungsfälle ohne Leistungssteigerungen

Nicht alle Abfragen profitieren von einer Batch-orientierten Verarbeitung. Die folgenden Abfragetypen funktionieren bei der zeilenorientierten Scanverarbeitung besser:

• Punktsuchabfragen: Abfragen, die nur eine Zeile abrufen.
• Kleine Scanabfragen: Tabellenscans, die nur wenige Zeilen scannen, sofern sie keine große Anzahl von Suchvorgängen haben.
• Abfragen, die LIMIT verwenden.
• Abfragen, die Daten mit hoher Abwanderung lesen: Abfragen, bei denen mehr als ~10% der gelesenen Daten häufig aktualisiert werden.
• Abfragen mit Zeilen, die große Werte enthalten: Zeilen mit großen Werten enthalten Werte, die in einer einzelnen Spalte größer als 32.000 Byte (Vorkomprimierung) sind.

Von einer Abfrage verwendete Scanmethode prüfen

So überprüfen Sie, ob die Abfrage die Batch-orientierte Verarbeitung oder die zeilenorientierte Verarbeitung verwendet oder automatisch zwischen den beiden Scanmethoden wechselt:

1. Rufen Sie in der Google Cloud Console die Seite Spanner-Instanzen auf.

   Zur Seite "VM-Instanzen"

2. Klicken Sie auf den Namen der Instanz mit der Abfrage, die Sie untersuchen möchten.

3. Klicken Sie unter der Tabelle „Datenbanken“ auf die Datenbank mit der Abfrage, die Sie untersuchen möchten.

4. Klicken Sie im Navigationsmenü auf Spanner Studio.

5. Öffnen Sie einen neuen Tab. Klicken Sie dazu auf Neuer SQL-Editor-Tab oder Neuer Tab.

6. Wenn der Abfrageeditor angezeigt wird, geben Sie Ihre Abfrage ein.

7. Klicken Sie auf Ausführen.

   Spanner führt die Abfrage aus und zeigt die Ergebnisse an.

8. Klicken Sie unter dem Abfrageeditor auf den Tab Erläuterung.

   Spanner zeigt eine Visualisierung des Ausführungsplans eines Abfrageplans. Jede Karte im Diagramm steht für einen Iterator.

9. Klicken Sie auf die Iteratorkarte Tabellenscan, um einen Informationsbereich zu öffnen.

   Im Informationsbereich werden Kontextinformationen zum ausgewählten Scan angezeigt. Die Scanmethode wird auf dieser Karte angezeigt. Automatisch bedeutet, dass Spanner die Scanmethode bestimmt. Weitere mögliche Werte sind Vectorized für die Batch-orientierte Verarbeitung und Scalar für die zeilenorientierte Verarbeitung.

   

Von einer Abfrage verwendete Scanmethode erzwingen

Zur Optimierung der Abfrageleistung wählt Spanner die optimale Scanmethode für die Abfrage aus. Wir empfehlen, diese Standardscanmethode zu verwenden. Es kann jedoch Szenarien geben, in denen Sie eine bestimmte Art von Scanmethode erzwingen möchten.

Batchorientiertes Scannen erzwingen

Sie können Batch-orientiertes Scannen auf Tabellen- und Anweisungsebene erzwingen.

Verwenden Sie einen Tabellenhinweis in Ihrer Abfrage, um die Batch-orientierte Scanmethode auf Tabellenebene zu erzwingen:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=BATCH} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=batch */ JOIN t2 on ...)
  WHERE ...

Verwenden Sie in Ihrer Abfrage einen Anweisungshinweis, um die Batch-orientierte Scanmethode auf Anweisungsebene zu erzwingen:

GoogleSQL

  @{SCAN_METHOD=BATCH}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=batch */
  SELECT ...
  FROM ...
  WHERE ...

So deaktivieren Sie das automatische Scannen und erzwingen das zeilenorientierte Scannen

Obwohl wir nicht empfehlen, die von Spanner festgelegte automatische Scanmethode zu deaktivieren, können Sie sie deaktivieren und die zeilenorientierte Scanmethode zur Fehlerbehebung verwenden, z. B. zur Diagnose der Latenz.

Verwenden Sie einen Tabellenhinweis in Ihrer Abfrage, um die automatische Scanmethode zu deaktivieren und die Zeilenverarbeitung auf Tabellenebene zu erzwingen:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=ROW} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=row */ JOIN t2 on ...)
  WHERE ...

Verwenden Sie in Ihrer Abfrage einen Anweisungshinweis, um die Methode für den automatischen Scan zu deaktivieren und die Zeilenverarbeitung auf Anweisungsebene zu erzwingen:

GoogleSQL

  @{SCAN_METHOD=ROW}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=row */
  SELECT ...
  FROM ...
  WHERE ...

Bereichsschlüsselsuchen optimieren

Eine häufige Verwendung einer SQL-Abfrage besteht darin, mehrere Zeilen aus Spanner basierend auf einer Liste bekannter Schlüssel zu lesen.

Die folgenden Best Practices helfen Ihnen beim Schreiben effizienter Abfragen beim Abrufen von Daten mit einer Reihe von Schlüsseln:

• Wenn die Liste der Schlüssel kurz ist und keine benachbarten Schlüssel enthält, verwenden Sie Abfrageparameter und UNNEST zur Erstellung der Abfrage.

  Wenn die Schlüsselliste beispielsweise {1, 5, 1000} lautet, schreiben Sie die Abfrage so:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST (@KeyList)
  

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST ($1)
  

  Hinweise:

  • Der Operator Array UNNEST vereinfacht ein Eingabe-Array so, dass es in Zeilen von Elementen vorliegt.

  • Der Abfrageparameter @KeyList für GoogleSQL und $1 für PostgreSQL kann die Abfrage beschleunigen, wie in der vorherigen Best Practice beschrieben.

• Wenn die Liste der Schlüssel benachbarte Schlüssel enthält und innerhalb eines Bereichs liegt, geben Sie die untere und höhere Grenzwerte des Schlüsselbereichs in der WHERE-Klausel an.

  Wenn die Schlüsselliste beispielsweise {1,2,3,4,5} lautet, erstellen Sie die Abfrage so:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN @min AND @max
  

  Hinweis :Hier sind @min und @max Abfrageparameter, die an die Werte 1 bzw. 5 gebunden sind.

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN $1 AND $2
  

  Hinweis :Hier sind $1 und $2 Abfrageparameter, die an die Werte 1 bzw. 5 gebunden sind.

  Diese Abfrage ist nur effizienter, wenn die Schlüssel im Schlüsselbereich benachbart sind. Mit anderen Worten, wenn Ihre Schlüsselliste {1, 5, 1000} ist, geben Sie die unteren und höheren Limits nicht wie in der vorherigen Abfrage an, da die resultierende Abfrage jeden Wert zwischen 1 und 1.000 durchsuchen würde.

Optimize-Verknüpfungen

Join-Vorgänge können teuer sein, da sie die Anzahl der Zeilen, die Ihre Abfrage scannen muss, erheblich erhöhen können, was zu langsameren Abfragen führt. Neben den Techniken, die Sie in anderen relationalen Datenbanken zur Optimierung von Join-Abfragen verwenden, finden Sie hier einige Best Practices für einen effizienteren JOIN bei Verwendung von Spanner SQL:

• Verbinden Sie nach Möglichkeit Daten in verschränkten Tabellen mit dem Primärschlüssel. Beispiel:

  SELECT s.FirstName, a.ReleaseDate
  FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
  

  Die Zeilen in der verschränkten Tabelle Albums werden in denselben Splits wie die übergeordnete Zeile in Singers gespeichert, wie in Schema und Datenmodell erläutert. Daher können Joins lokal abgeschlossen werden, ohne viele Daten über das Netzwerk zu senden.

• Verwenden Sie die Join-Anweisung, wenn Sie die Reihenfolge des Join erzwingen möchten. Beispiel:

  GoogleSQL

  SELECT *
  FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
  

  PostgreSQL

  SELECT *
  FROM Singers AS s JOIN/*@ FORCE_JOIN_ORDER=TRUE */ Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
  

  Die Join-Anweisung FORCE_JOIN_ORDER weist Spanner an, die in der Abfrage angegebene Join-Reihenfolge zu verwenden (d. h. Singers JOIN Albums, nicht Albums JOIN Singers). Die zurückgegebenen Ergebnisse sind unabhängig von der von Spanner gewählten Reihenfolge. Sie können diese Join-Anweisung verwenden, wenn Sie im Abfrageplan feststellen, dass Spanner die Join-Reihenfolge geändert und unerwünschte Folgen verursacht hat, z. B. größere Zwischenergebnisse oder verpasste Möglichkeiten zum Suchen von Zeilen.

• Wählen Sie mit einer Join-Anweisung eine Join-Implementierung aus. Wenn Sie mithilfe von SQL mehrere Tabellen abfragen, verwendet Spanner automatisch eine Join-Methode, die die Abfrage wahrscheinlich effizienter macht. Google empfiehlt jedoch, mit verschiedenen Join-Algorithmen zu testen. Wenn Sie den richtigen Join-Algorithmus auswählen, können Latenz, Speicherverbrauch oder beides verbessert werden. Diese Abfrage zeigt die Syntax für die Verwendung einer JOIN-Anweisung mit dem Hinweis JOIN_METHOD zur Auswahl einer HASH JOIN:

  GoogleSQL

  SELECT *
  FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
  ON a.SingerId = a.SingerId
  

  PostgreSQL

  SELECT *
  FROM Singers s JOIN/*@ JOIN_METHOD=HASH_JOIN */ Albums AS a
  ON a.SingerId = a.SingerId
  

• Wenn Sie ein HASH JOIN oder APPLY JOIN verwenden und eine WHERE-Klausel haben, die auf einer Seite Ihrer JOIN hochselektiv ist, legen Sie die Tabelle, die die kleinste Anzahl von Zeilen erzeugt, als erste Tabelle in die FROM-Klausel des Joins ein. Diese Struktur ist hilfreich, da Spanner in HASH JOIN immer die linke Tabelle als Build und die rechte Tabelle als Prüfung auswählt. In ähnlicher Weise wählt Spanner für APPLY JOIN die linke Tabelle als äußere und die rechte Tabelle als innere Tabelle aus. Weitere Informationen zu diesen Join-Typen finden Sie unter Hash Join und Join anwenden.

• Geben Sie für Abfragen, die für Ihre Arbeitslast entscheidend sind, die leistungsstärkste Join-Methode und Join-Reihenfolge in Ihren SQL-Anweisungen an, um eine konsistente Leistung zu erzielen.

Große Lesevorgänge in Lese-Schreib-Transaktionen vermeiden

Lese-Schreib-Transaktionen ermöglichen eine Sequenz von null oder mehr Lesevorgängen oder SQL-Abfragen und können eine Reihe von Mutationen vor einem Aufruf zum Commit enthalten. Zum Aufrechterhalten der Konsistenz Ihrer Daten erhält Spanner Sperren beim Lesen und Schreiben von Zeilen in Ihren Tabellen und Indexen. Weitere Informationen zum Sperren finden Sie unter Lebensdauer von Lese- und Schreibvorgängen.

Aufgrund der Funktionsweise von Sperren in Spanner bedeutet das Ausführen einer Lese- oder SQL-Abfrage, die eine große Anzahl von Zeilen liest (z. B. SELECT * FROM Singers), dass keine anderen Transaktionen in die von Ihnen gelesenen Zeilen schreiben können, bis entweder ein Commit oder der Abbruch der Transaktion durchgeführt wird.

Da Ihre Transaktion eine große Anzahl von Zeilen verarbeitet, dauert sie wahrscheinlich länger als eine Transaktion, die einen viel kleineren Zeilenbereich liest (z. B. SELECT LastName FROM Singers WHERE SingerId = 7). Dadurch wird das Problem weiter verschärft und der Systemdurchsatz reduziert.

Vermeiden Sie daher große Lesevorgänge (z. B. vollständige Tabellenscans oder massive Join-Vorgänge) in Ihren Transaktionen, es sei denn, Sie sind bereit, einen niedrigeren Durchsatz für Schreibvorgänge zu akzeptieren.

In einigen Fällen kann das folgende Muster bessere Ergebnisse liefern:

1. Führen Sie Ihre großen Lesevorgänge in einer schreibgeschützten Transaktion aus. Schreibgeschützte Transaktionen ermöglichen einen höheren Gesamtdurchsatz, da sie keine Sperren verwenden.
2. Optional: Führen Sie eine Verarbeitung der gerade gelesenen Daten durch.
3. Starten Sie eine Lese-Schreib-Transaktion.
4. Prüfen Sie, ob sich die kritischen Zeilen nicht geändert haben, seit Sie die schreibgeschützte Transaktion in Schritt 1 ausgeführt haben.
   • Wenn sich die Zeilen geändert haben, führen Sie einen Rollback der Transaktion durch und beginnen Sie noch einmal bei Schritt 1.
   • Wenn alles in Ordnung ist, können Sie einen Commit Ihrer Mutationen durchführen.

Um große Lesevorgänge in Lese-Schreib-Transaktionen zu vermeiden, sollten Sie sich die Ausführungspläne ansehen, die von Ihren Abfragen generiert werden.

Verwenden Sie ORDER BY, um die Reihenfolge Ihrer SQL-Ergebnisse sicherzustellen

Wenn Sie eine bestimmte Reihenfolge für die Ergebnisse einer SELECT-Abfrage erwarten, fügen Sie explizit die ORDER BY-Klausel ein. Wenn Sie beispielsweise alle Sänger in Primärschlüsselreihenfolge auflisten möchten, verwenden Sie diese Abfrage:

SELECT * FROM Singers
ORDER BY SingerId;

Spanner garantiert die Reihenfolge der Ergebnisse nur, wenn die ORDER BY-Klausel in der Abfrage vorhanden ist. Mit anderen Worten, betrachten Sie diese Abfrage ohne ORDER BY:

SELECT * FROM Singers;

Spanner garantiert nicht, dass die Ergebnisse dieser Abfrage in der Primärschlüsselreihenfolge vorliegen. Außerdem kann sich die Reihenfolge der Ergebnisse jederzeit ändern und es ist nicht garantiert, dass sie von Aufruf zu Aufruf konsistent ist. Wenn eine Abfrage eine ORDER BY-Klausel hat und Spanner einen Index verwendet, der die erforderliche Reihenfolge angibt, sortiert Spanner die Daten nicht explizit. Machen Sie sich daher keine Gedanken über die Auswirkungen auf die Leistung, wenn diese Klausel hinzugefügt wird. Im Abfrageplan können Sie prüfen, ob ein expliziter Sortiervorgang in der Ausführung enthalten ist.

STARTS_WITH statt LIKE verwenden

Da parametrisierte LIKE-Muster erst bei der Ausführung ausgewertet werden, muss Spanner alle Zeilen lesen und mit dem Ausdruck LIKE vergleichen, um nicht übereinstimmende Zeilen herauszufiltern.

Wenn ein LIKE-Muster das Format foo% hat (z. B. wenn es mit einem festen String beginnt und mit einem einzelnen Platzhalterprozentsatz endet) und die Spalte indexiert ist, verwenden Sie STARTS_WITH anstelle von LIKE. Mit dieser Option kann Spanner den Plan für die Abfrageausführung effektiver optimieren.

Nicht empfohlen:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

Hinweis :In diesem Beispiel wird davon ausgegangen, dass @like_clause an 'Love%' gebunden ist.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE $1;

Hinweis :In diesem Beispiel wird davon ausgegangen, dass $2 an 'Love%' gebunden ist.

Empfohlen:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);

Hinweis :In diesem Beispiel wird davon ausgegangen, dass @prefix an 'Love' gebunden ist. Diese Abfrage ist effizienter als die vorherige. Sie wird schneller ausgeführt, wenn in Albums.AlbumTitle ein Index definiert ist.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, $2);

Hinweis :In diesem Beispiel wird davon ausgegangen, dass $1 an 'Love' gebunden ist. Diese Abfrage ist effizienter als die vorherige. Sie wird schneller ausgeführt, wenn in Albums.AlbumTitle ein Index definiert ist.

Commit-Zeitstempel verwenden

Wenn Ihre Anwendung Daten abfragen muss, die nach einem bestimmten Zeitpunkt geschrieben wurden, fügen Sie den entsprechenden Tabellen Commit-Zeitstempelspalten hinzu. Commit-Zeitstempel ermöglichen eine Spanner-Optimierung, die die E/A von Abfragen reduzieren kann, deren WHERE-Klauseln Ergebnisse auf Zeilen beschränken, die vor einer bestimmten Zeit geschrieben wurden.

Weitere Informationen zu dieser Optimierung mit GoogleSQL-Dialekt-Datenbanken oder mit PostgreSQL-Dialekt-Datenbanken