Best Practices für die Datenbearbeitungssprache

Auf dieser Seite werden die Best Practices für die Verwendung der Datenbearbeitungssprache (Data Manipulation Language – DML) und der partitionierten DML für Datenbanken mit GoogleSQL-Dialekt und Datenbanken mit PostgreSQL-Dialekt beschrieben.

Mit einer WHERE-Klausel die Anzahl der gesperrten Zeilen reduzieren

Sie führen DML-Anweisungen in Lese- und Schreibtransaktionen aus. Wenn Spanner Daten liest, werden gemeinsame Lesesperren für begrenzte Teile der gelesenen Zeilenbereiche übernommen. Insbesondere werden diese Sperren nur für die Spalten übernommen, auf die Sie zugreifen. Die Sperren können auch Daten beinhalten, die nicht mit der Filterbedingung der WHERE-Klausel übereinstimmen.

Beim Ändern von Daten durch Spanner mithilfe von DML-Anweisungen werden exklusive Sperren speziell für die Daten übernommen, die Sie ändern. Darüber hinaus werden gemeinsame Sperren auf die gleiche Weise wie beim Lesen von Daten angewendet. Wenn Ihre Anfrage große Zeilenbereiche oder eine komplette Tabelle beinhaltet, verhindern die gemeinsamen Sperren möglicherweise, dass andere Transaktionen parallel ausgeführt werden.

Um Daten so effizient wie möglich zu ändern, verwenden Sie eine WHERE-Klausel, mit der Spanner nur die erforderlichen Zeilen liest. Sie können dafür einen Filter für den Primärschlüssel oder für den Schlüssel eines Sekundärindex nutzen. Die Klausel WHERE beschränkt den Umfang der gemeinsamen Sperren und ermöglicht es Spanner, die Aktualisierung effizienter zu verarbeiten.

Angenommen, einer der Musiker in der Tabelle Singers ändert seinen Vornamen und Sie müssen den Namen in Ihrer Datenbank aktualisieren. Sie können dafür die im Folgenden dargestellte DML-Anweisung ausführen. Diese führt aber dazu, dass Spanner die komplette Tabelle prüft und gemeinsame Sperren übernimmt, die die komplette Tabelle betreffen. Spanner muss dann mehr Daten als erforderlich lesen und gleichzeitige Transaktionen können die Daten nicht parallel ändern:

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";

Für eine effizientere Aktualisierung nehmen Sie die Spalte SingerId in die WHERE-Klausel auf. Die Spalte SingerId ist die einzige primäre Schlüsselspalte für die Tabelle Singers:

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"

Wenn es keinen Index für FirstName oder LastName gibt, müssen Sie die gesamte Tabelle scannen, um die Zielinterpreten zu finden. Wenn Sie keinen sekundären Index hinzufügen möchten, um die Aktualisierung effizienter zu gestalten, nehmen Sie die Spalte SingerId in die WHERE-Klausel auf.

Die Spalte SingerId ist die einzige primäre Schlüsselspalte für die Tabelle Singers. Führen Sie SELECT in einer separaten, schreibgeschützten Transaktion vor der Aktualisierungstransaktion aus, um sie zu finden:

  SELECT SingerId
  FROM Singers
  WHERE FirstName = "Marc" AND LastName = "Richards"

  -- Recommended: Including a seekable filter in the where clause

  UPDATE Singers SET FirstName = "Marcel"
  WHERE SingerId = 1;

DML-Anweisungen und Mutationen nicht in derselben Transaktion verwenden

Spanner speichert Einfügungen, Aktualisierungen und Löschungen, die mit DML-Anweisungen serverseitig ausgeführt wurden, im Zwischenspeicher. Die Ergebnisse sind für nachfolgende SQL- und DML-Anweisungen in derselben Transaktion sichtbar. Dieses Verhalten unterscheidet sich von der Mutation API, bei der Spanner die Mutationen auf der Clientseite puffert und sie serverseitig als Teil des Commit-Vorgangs sendet. Folglich sind Mutationen in der Commit-Anfrage für SQL- oder DML-Anweisungen in derselben Transaktion nicht sichtbar.

Vermeiden Sie es, sowohl DML-Anweisungen als auch Mutationen in derselben Transaktion zu verwenden. Wenn Sie beide in derselben Transaktion verwenden, müssen Sie die Reihenfolge der Ausführung in Ihrem Client-Bibliothekscode berücksichtigen. Wenn eine Transaktion sowohl DML-Anweisungen als auch Mutationen in derselben Anfrage enthält, führt Spanner die DML-Anweisungen vor den Mutationen aus.

Bei Vorgängen, die nur mit Mutationen unterstützt werden, kann es sinnvoll sein, DML-Anweisungen und Mutationen in derselben Transaktion zu kombinieren, z. B. insert_or_update.

Wenn Sie beide verwenden, sollten Sie Schreibvorgänge nur am Ende der Transaktion puffern.

Mit der Funktion PENDING_COMMIT_TIMESTAMP Commit-Zeitstempel schreiben

GoogleSQL

Schreiben Sie mit der Funktion PENDING_COMMIT_TIMESTAMP den Commit-Zeitstempel in eine DML-Anweisung. Spanner wählt den Commit-Zeitstempel aus, wenn der Commit der Transaktion ausgeführt wird.

Hinweis:Nach dem Aufruf der Funktion PENDING_COMMIT_TIMESTAMP sind die Tabelle und alle abgeleiteten Indexe für zukünftige SQL-Anweisungen in der Transaktion nicht lesbar. Aus diesem Grund kann der Änderungsstream den vorherigen Wert für die Spalte mit einem ausstehenden Commit-Zeitstempel nicht extrahieren, wenn die Spalte später in derselben Transaktion noch einmal geändert wird. Sie müssen einen Commit-Zeitstempel als letzte Anweisung in einer Transaktion schreiben, um zu verhindern, dass die Tabelle möglicherweise gelesen wird. Wenn Sie versuchen, die Tabelle zu lesen, gibt Spanner einen Fehler zurück.

PostgreSQL

Schreiben Sie mit der Funktion SPANNER.PENDING_COMMIT_TIMESTAMP() den Commit-Zeitstempel in eine DML-Anweisung. Spanner wählt den Commit-Zeitstempel aus, wenn der Commit der Transaktion ausgeführt wird.

Hinweis:Nach dem Aufruf der Funktion SPANNER.PENDING_COMMIT_TIMESTAMP() sind die Tabelle und alle abgeleiteten Indexe für nachfolgende SQL-Anweisungen in der Transaktion nicht lesbar. Sie müssen einen Commit-Zeitstempel als letzte Anweisung in einer Transaktion schreiben, um zu verhindern, dass die Tabelle möglicherweise gelesen wird. Wenn Sie versuchen, die Tabelle zu lesen, gibt Spanner einen Fehler zurück.

Partitionierte DML-, Datums- und Zeitstempelfunktionen

Partitionierte DML verwendet eine oder mehrere Transaktionen, die möglicherweise zu unterschiedlichen Zeitpunkten ausgeführt und bereitgestellt werden. Wenn Sie die Datums- oder Zeitstempelfunktion verwenden, können die geänderten Zeilen andere Werte enthalten.

Latenz mit Batch-DML verbessern

Um die Latenz zu verringern, verwenden Sie Batch-DML, um mehrere DML-Anweisungen in einem einzigen Client-Server-Roundtrip an Spanner zu senden.

Bei Batch-DML können Optimierungen auf Gruppen von Anweisungen innerhalb eines Batches angewendet werden, um schnellere und effizientere Datenaktualisierungen zu ermöglichen.

• Schreibvorgänge mit einer einzelnen Anfrage ausführen

  Spanner optimiert automatisch zusammenhängende Gruppen ähnlicher INSERT-, UPDATE- oder DELETE-Batchanweisungen mit unterschiedlichen Parameterwerten, sofern keine Datenabhängigkeiten verletzt werden.

  Stellen Sie sich beispielsweise ein Szenario vor, in dem Sie eine große Anzahl neuer Zeilen in eine Tabelle namens Albums einfügen möchten. Damit Spanner alle erforderlichen INSERT-Anweisungen in eine einzige, effiziente serverseitige Aktion optimieren kann, schreiben Sie zuerst eine geeignete DML-Anweisung, die SQL-Abfrageparameter verwendet:

  INSERT INTO Albums (SingerId, AlbumId, AlbumTitle) VALUES (@Singer, @Album, @Title);
  

  Senden Sie dann einen DML-Batch an Spanner, der diese Anweisung wiederholt und fortlaufend aufruft. Die Wiederholungen unterscheiden sich nur in den Werten, die Sie an die drei Abfrageparameter der Anweisung binden. Spanner optimiert diese strukturell identischen DML-Anweisungen in einen einzelnen serverseitigen Vorgang, bevor sie ausgeführt werden.

• Schreibvorgänge parallel ausführen

  Spanner optimiert automatisch zusammenhängende Gruppen von DML-Anweisungen, indem sie parallel ausgeführt werden, sofern dadurch keine Datenabhängigkeiten verletzt werden. Diese Optimierung bietet Leistungsvorteile für eine größere Anzahl von Batch-DML-Anweisungen, da sie auf eine Mischung aus DML-Anweisungstypen (INSERT, UPDATE und DELETE) und auf parametrisierte oder nicht parametrisierte DML-Anweisungen angewendet werden kann.

  Unser Beispielschema enthält beispielsweise die Tabellen Singers, Albums und Accounts. Albums ist in Singers verschachtelt und speichert Informationen zu Alben für Singers. Mit der folgenden zusammenhängenden Gruppe von Anweisungen werden neue Zeilen in mehrere Tabellen geschrieben. Es gibt keine komplexen Datenabhängigkeiten.

  INSERT INTO Singers (SingerId, Name) VALUES(1, "John Doe");
  INSERT INTO Singers (SingerId, Name) VALUES(2, "Marcel Richards");
  INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (1, 10001, "Album 1");
  INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (1, 10002, "Album 2");
  INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (2, 10001, "Album 1");
  UPDATE Accounts SET Balance = 100 WHERE AccountId = @AccountId;
  

  Spanner optimiert diese Gruppe von DML-Anweisungen, indem die Anweisungen parallel ausgeführt werden. Die Schreibvorgänge werden in der Reihenfolge der Anweisungen im Batch angewendet. Die Semantik von Batch-DML wird beibehalten, wenn eine Anweisung während der Ausführung fehlschlägt.

Clientseitige Batchverarbeitung in JDBC aktivieren

Bei Java-Anwendungen, die einen von Spanner unterstützten JDBC-Treiber verwenden, können Sie die Latenz reduzieren, indem Sie das clientseitige DML-Batching aktivieren. Der JDBC-Treiber hat eine Verbindungseigenschaft namens auto_batch_dml, die, wenn sie aktiviert ist, DML-Anweisungen auf dem Client puffert und als einzelnen Batch an Spanner sendet. Dadurch kann die Anzahl der Roundtrips zum Server reduziert und die Gesamtleistung verbessert werden.

Standardmäßig ist auto_batch_dml auf false festgelegt. Sie können die Funktion aktivieren, indem Sie sie in Ihrem JDBC-Verbindungsstring auf true setzen.

Beispiel:

String url = "jdbc:cloudspanner:/projects/my-project/instances/my-instance/databases/my-database;auto_batch_dml=true";
try (Connection connection = DriverManager.getConnection(url)) {
    // Include your DML statements for batching here
}

Wenn diese Verbindungseigenschaft aktiviert ist, sendet Spanner gepufferte DML-Anweisungen als Batch, wenn eine Nicht-DML-Anweisung ausgeführt oder die aktuelle Transaktion übernommen wird. Diese Eigenschaft gilt nur für Lese-/Schreibtransaktionen. DML-Anweisungen im Autocommit-Modus werden direkt ausgeführt.

Standardmäßig ist die Anzahl der Aktualisierungen für gepufferte DML-Anweisungen auf 1 festgelegt. Sie können dies ändern, indem Sie die Verbindungsvariable auto_batch_dml_update_count auf einen anderen Wert festlegen. Weitere Informationen finden Sie unter Von JDBC unterstützte Verbindungseigenschaften.