Best Practices für SQL

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

Die Beispiel-SQL-Anweisungen auf dieser Seite verwenden das folgende Beispielschema:

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
) 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.

Häufig ausgeführte Abfragen durch Abfrageparameter beschleunigen

Parametrisierte Abfragen sind eine Technik der Abfrageausführung, die eine Abfragezeichenfolge von Abfrageparameterwerten trennt. Angenommen, Ihre Anwendung muss Sänger abrufen, die in einem bestimmten Jahr Alben mit bestimmten Titeln veröffentlicht haben. Sie könnten eine SQL-Anweisung wie das folgende Beispiel schreiben, um alle Alben mit dem Titel "Love" abzurufen, die 2017 veröffentlicht wurden:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

In einer anderen Abfrage können Sie den Wert des Albumtitels in "Peace" ändern:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Wenn Ihre Anwendung viele ähnliche Abfragen ausführen muss, in denen sich nur ein Literalwert in nachfolgenden Abfragen ändert, sollten Sie einen Parameterplatzhalter für diesen Wert verwenden. Die resultierende parametrische Abfrage kann zwischengespeichert und wiederverwendet werden, wodurch die Kompilierungskosten reduziert werden.

Zum Beispiel ersetzt die neu geschriebene Abfrage Love durch einen Parameter mit dem Namen title:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Hinweise zur Verwendung von Abfrageparametern:

  • Eine Parameterreferenz in der Abfrage verwendet das Zeichen @, gefolgt vom Parameternamen, der eine beliebige Kombination aus Buchstaben, Zahlen und Unterstrichen enthalten kann.
  • Parameter können überall dort vorkommen, wo ein Literalwert erwartet wird.
  • Derselbe Parametername kann mehrmals in einer einzelnen SQL-Anweisung verwendet werden.
  • Den Abfrageparameter und den daran zu bindenden Wert geben Sie in der Anfrage-API ExecuteSQL oder ExecuteStreamingSQL im Feld params an.
  • Weitere Informationen zur Syntax von Abfrageparametern finden Sie im Abschnitt Lexikale Struktur und Syntax von SQL.

Zusammenfassend haben Abfrageparameter folgende Vorteile für die Abfrageausführung:

  • Voroptimierte Pläne: Abfragen, die Parameter verwenden, können bei jedem Aufruf schneller ausgeführt werden, da Cloud Spanner durch die Parametrisierung den Ausführungsplan besser zwischenspeichern kann.
  • Vereinfachte Abfragezusammensetzung: Es ist nicht notwendig, dass Sie Stringwerte ausschließen, wenn Sie sie in Abfrageparametern bereitstellen. 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 werden Abfragen von Cloud Spanner ausgeführt

Mit Cloud Spanner können Sie Datenbanken mithilfe deklarativer SQL-Anweisungen abfragen, die angeben, welche Daten abgerufen werden sollen. Wenn Sie außerdem wissen möchten, wie Cloud Spanner Ergebnisse erhält, sollten Sie Abfrageausführungspläne verwenden. 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.

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

So rufen Sie einen Abfrageplan mit der Cloud Console ab:

  1. Öffnen Sie die Seite "Cloud Spanner-Instanzen".

    Cloud Spanner-Instanzen aufrufen

  2. Klicken Sie auf die Namen der Cloud Spanner-Instanz und der Datenbank, die Sie abfragen möchten.

  3. Klicken Sie auf Abfrage.

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

  5. Klicken Sie auf Erläuterung.
    Die Cloud Console zeigt einen visuellen Ausführungsplan für die Abfrage an:

    Screenshot der Erklärungskonsole in der Nutzeroberfläche

Die vollständige Abfrageplanreferenz finden Sie unter Abfrageausführungspläne.

Mit sekundären Indexen häufige Abfragen beschleunigen

Wie andere relationale Datenbanken bietet Cloud Spanner sekundäre Indexe, mit denen Sie Daten entweder mithilfe einer SQL-Anweisung oder mithilfe der Leseoberfläche von Cloud Spanner abrufen können. Die gängigere Methode zum Abrufen von Daten aus einem Index ist die Verwendung der SQL-Abfrage-Schnittstelle. Mit einem sekundären Index in einer SQL-Abfrage können Sie angeben, wie Cloud 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. Das ist eine teure Methode, um die Ergebnisse zu erhalten, wenn die Tabelle nur einen kleinen Anteil 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 Cloud Spanner alle Daten aus der wesentlich kleineren Indextabelle abrufen, ohne die gesamte Tabelle Singers scannen zu müssen.

In diesem Szenario würde Cloud Spanner beim Ausführen der Abfrage wahrscheinlich automatisch den sekundären Index SingersByLastName verwenden. Als Best Practice hat es sich jedoch bewährt, Cloud Spanner diesen Index explizit anzugeben. Legen Sie dazu in der Klausel FROM eine Indexanweisung fest:

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

Beispiel: Sie wollten 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:

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

Sie erhalten immer noch einen Leistungsvorteil bei der Verwendung des Index, da Cloud Spanner beim Ausführen des Abfrageplans keinen vollständigen Tabellenscan machen muss. Stattdessen wählt es die Teilmenge der Zeilen aus, die die Bedingung aus dem Index SingersByLastName erfüllen, und führt dann einen Suchvorgang aus der Basistabelle Singers aus, um den ersten Namen nur für diese Teilmenge von Zeilen abzurufen.

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

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

Die Verwendung einer STORING-Klausel kostet zwar zusätzlichen Speicher, bietet jedoch die folgenden Vorteile für Abfragen und Leseaufrufe mit dem Index:

  • SQL-Abfragen, die den Index verwenden und in der Klausel STORING gespeicherte Spalten auswählen, benötigen keine zusätzliche Verknüpfung mit der Basistabelle.
  • Leseaufrufe, die den Index verwenden, können in der Klausel STORING 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 ihre Veröffentlichungsdaten abrufen und sie aufsteigend nach Veröffentlichungsdatum und absteigend nach Albumtitel zurückgeben. So könnten Sie eine SQL-Abfrage schreiben:

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 die Abfrage dann neu, um den sekundären Index zu verwenden:

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

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

  • Die Spaltenliste in der ORDER BY-Klausel ist ein Präfix der Indexschlüsselliste.
  • Alle Spalten in der Tabelle, die in der Abfrage verwendet werden, werden vom Index abgedeckt.

Da diese beiden Bedingungen erfüllt sind, entfernt der resultierende Abfrageplan den Sortierschritt und wird schneller ausgeführt.

Obwohl sekundäre Indizes häufige Abfragen beschleunigen können, sollten Sie beachten, dass das Hinzufügen von sekundären Indizes zu Verzögerungen bei den Commit-Vorgängen führen kann, 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. Sie sollten auch die Arbeitslast messen, damit sie tatsächlich erwartungsgemäß funktioniert.

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

Effiziente Abfragen für die Bereichsschlüssel-Suche schreiben

Die SQL-Abfrage wird häufig dafür verwendet, basierend auf einer Liste bekannter Schlüssel mehrere Zeilen aus Cloud Spanner zu lesen.

Das sind die besten Methoden zum 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:

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

    Hinweise:

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

    • @KeyList ist ein Abfrageparameter, mit dem Sie Ihre Abfrage beschleunigen können, wie oben unter Best Practices beschrieben.

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

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

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

    Dabei sind @min und @max Abfrageparameter, die jeweils an die Werte 1 und 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, sollten Sie die unteren und oberen Grenzen nicht wie in der obigen Abfrage angeben, da die resultierende Abfrage jeden Wert zwischen 1 und 1.000 prüfen würde.

Effiziente Abfragen für Joins schreiben

Join-Vorgänge können teuer sein. Dies liegt daran, dass JOINs die Anzahl der Zeilen, die Ihre Abfrage scannen muss, erheblich erhöhen können. Dies führt zu langsameren Abfragen. Zusätzlich zu den Methoden, die Sie in anderen relationalen Datenbanken zum Optimieren von Join-Abfragen verwenden, finden Sie hier einige Best Practices für einen effizienteren JOIN bei der Verwendung von Cloud 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 von JOIN erzwingen möchten. Beispiel:

    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=TRUE} weist Cloud 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 Reihenfolge, die Cloud Spanner auswählt, identisch. Sie sollten diese Join-Anweisung allerdings verwenden, wenn Sie im Abfrageplan bemerken, dass Cloud Spanner die Join-Reihenfolge geändert und unerwünschte Ergebnisse verursacht hat, z. B. dass größere Zwischenergebnisse erzielt oder dass Gelegenheiten zum Suchen von Zeilen verpasst wurden.

  • Wählen Sie mit einer JOIN-Anweisung einen JOIN-Implementierung aus. Wenn Sie den richtigen Join-Algorithmus für die Abfrage auswählen, können Latenz, Speicherverbrauch oder beides verbessert werden. Diese Abfrage zeigt die Syntax für die Verwendung einer JOIN-Anweisung mit dem JOIN_METHOD-Hinweis zum Auswählen eines HASH JOIN.

    SELECT *
    FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
    ON a.SingerId = a.SingerId
    
  • Wenn Sie einen HASH JOIN oder APPLY JOIN verwenden und eine WHERE-Klausel haben, die auf einer Seite Ihres JOIN sehr selektiv ist, legen Sie die Tabelle mit der geringsten Anzahl von Zeilen als erste Tabelle in der FROM-Klausel des Joins fest. Das liegt daran, dass Cloud Spanner in HASH JOIN immer die linke Tabelle als Build und die rechte Tabelle als Probe auswählt. Auf ähnliche Weise wählt Cloud Spanner für APPLY JOIN die linke Tabelle als Outer Join und die rechte Tabelle als Inner Join aus. Weitere Informationen zu diesen Join-Typen finden Sie unter Hash Join und Apply Join.

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. Cloud Spanner erhält Sperren beim Lesen und Schreiben von Zeilen in Ihren Tabellen und Indexen, um die Konsistenz Ihrer Daten zu gewähren. Mehr Informationen zu Sperren finden Sie unter Lebensdauer von Lese- und Schreibvorgängen.

Aufgrund der Funktionsweise von Sperren in Cloud Spanner führt das Ausführen einer Lese- oder SQL-Abfrage mit einer großen Anzahl von Zeilen (z. B. SELECT * FROM Singers) dazu, dass keine anderen Transaktionen in die gelesenen Zeilen schreiben können, bis Ihre Transaktion in einem Commit-Vorgang ausgeführt oder abgebrochen wird. Da Ihre Transaktion eine große Anzahl von Zeilen verarbeitet, dauert sie wahrscheinlich länger als eine Transaktion, die einen kleinen Zeilenbereich liest (z. B.SELECT LastName FROM Singers WHERE SingerId = 7). Dadurch wird das Problem weiter verschärft und der Systemdurchsatz reduziert.

Daher sollten Sie versuchen, große Lesevorgänge (z. B. vollständige Tabellenscans oder massive Verknüpfungsvorgänge) innerhalb Ihrer Transaktionen zu vermeiden, es sei denn, Sie sind bereit, einen niedrigeren Schreibdurchsatz zu akzeptieren. In einigen Fällen kann das folgende Muster bessere Ergebnisse liefern:

  1. Führen Sie die großen Lesevorgänge in einer schreibgeschützten Transaktion aus. (Beachten Sie, dass schreibgeschützte Transaktionen keine Sperren verwenden und daher einen höheren Gesamtdurchsatz ermöglichen.)
  2. [Optional] Wenn es notwendig ist, dass Sie die gerade gelesenen Daten bearbeiten, tun Sie dies.
  3. Starten Sie eine Lese-Schreib-Transaktion.
  4. Prüfen Sie, ob die wichtigsten Zeilen ihre Werte nicht geändert haben, seit Sie die schreibgeschützte Transaktion in Schritt 1 ausgeführt haben.
    1. Wenn sich die Zeilen geändert haben, führen Sie einen Rollback der Transaktion durch und beginnen Sie noch einmal bei Schritt 1.
    2. Wenn alles in Ordnung ist, können Sie einen Commit Ihrer Mutationen durchführen.

Sie können große Lesevorgänge innerhalb von Lese-Schreib-Transaktionen vermeiden, wenn 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, sollten Sie die ORDER BY-Klausel explizit einschließen. Beispiel: Wenn Sie alle Sänger in Primärschlüsselreihenfolge auflisten möchten, verwenden Sie diese Abfrage:

SELECT * FROM Singers
ORDER BY SingerId;

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

SELECT * FROM Singers;

Die Ergebnisse dieser Abfrage kommen in Cloud Spanner nicht garantiert in der Primärschlüsselreihenfolge vor. Außerdem kann sich die Reihenfolge der Ergebnisse jederzeit ändern und es ist nicht sicher, dass sie in mehreren Aufrufen konsistent ist.

STARTS_WITH anstelle von LIKE zur Beschleunigung parametrisierter SQL-Abfragen verwenden

Da parametrisierte LIKE-Muster von Cloud Spanner erst bei der Ausführung ausgewertet werden, ist es notwendig, dass alle Zeilen gelesen und mit dem Ausdruck LIKE zur Ausfilterung nicht übereinstimmender Zeilen verglichen werden.

Wenn vom Muster LIKE nach Treffern zu Beginn eines Werts gesucht wird und die Spalte indexiert ist, verwenden Sie STARTS_WITH anstelle von LIKE. So kann der Abfrageausführungsplan von Cloud Spanner effektiver optimiert werden.

Nicht empfohlen:

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

Empfohlen:

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