Best Practices für die Datenbearbeitungssprache

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

WHERE-Klausel verwenden, um den Umfang von Sperren zu reduzieren

Sie führen DML-Anweisungen in Lese- und Schreibtransaktionen aus. Wenn Spanner Daten liest, werden freigegebene Lesesperren für begrenzte Teile der Zeilenbereiche erworben, die Sie lesen. Insbesondere werden diese Sperren nur für die Spalten übernommen, auf die Sie zugreifen. Die Sperren können Daten enthalten, die die Filterbedingung der WHERE-Klausel nicht erfüllen.

Wenn Spanner Daten mithilfe von DML-Anweisungen ändert, werden exklusive Sperren für die spezifischen Daten erworben, 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 ganze Tabelle umfasst, können die freigegebenen Sperren verhindern, dass andere Transaktionen parallel ausgeführt werden.

Wenn Sie Daten so effizient wie möglich ändern möchten, verwenden Sie eine WHERE-Klausel, mit der Spanner nur die erforderlichen Zeilen lesen kann. Sie können dafür einen Filter für den Primärschlüssel oder für den Schlüssel eines Sekundärindex nutzen. Die WHERE-Klausel schränkt den Umfang der freigegebenen Sperren ein 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önnten die folgende DML-Anweisung ausführen, aber dadurch wird Spanner gezwungen, die gesamte Tabelle zu scannen, und es werden freigegebene Sperren erworben, die die gesamte Tabelle abdecken. Daher muss Spanner mehr Daten als nötig 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ärschlü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 kein Index für FirstName oder LastName vorhanden ist, müssen Sie die gesamte Tabelle scannen, um die Zielsänger zu finden. Wenn Sie keinen sekundären Index hinzufügen möchten, um die Aktualisierung effizienter zu gestalten, fügen Sie die Spalte SingerId in die WHERE-Klausel ein.

Die Spalte SingerId ist die einzige Primärschlüsselspalte für die Tabelle Singers. Führen Sie dazu vor der Aktualisierungstransaktion SELECT in einer separaten, schreibgeschützten Transaktion aus: 


  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;

Vermeiden Sie die Verwendung von DML-Anweisungen und Mutationen in derselben Transaktion

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

Vermeiden Sie die Verwendung von DML-Anweisungen und Mutationen in derselben Transaktion. Wenn Sie beide in derselben Transaktion verwenden, müssen Sie die Ausführungsreihenfolge in Ihrem Clientbibliotheks-Code 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.

Wenn Sie beide verwenden, schreibt der Puffer nur ganz am Ende der Transaktion.

Funktion PENDING_COMMIT_TIMESTAMP zum Schreiben von Commit-Zeitstempeln verwenden

GoogleSQL

Sie verwenden Sie Funktion PENDING_COMMIT_TIMESTAMP, um den Commit-Zeitstempel in eine DML-Anweisung zu schreiben. Cloud Spanner wählt den Commit-Zeitstempel aus, wenn der Commit der Transaktion durchgeführt wird.

PostgreSQL

Sie verwenden Sie Funktion SPANNER.PENDING_COMMIT_TIMESTAMP(), um den Commit-Zeitstempel in eine DML-Anweisung zu schreiben. Cloud Spanner wählt den Commit-Zeitstempel aus, wenn der Commit der Transaktion durchgeführt wird.

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 Funktionen date oder timestamp verwenden, enthalten die geänderten Zeilen möglicherweise unterschiedliche Werte.

Latenz mit Batch-DML verbessern

Verwenden Sie Batch-DML, um mehrere DML-Anweisungen innerhalb eines einzigen Client-Server-Roundtrips an Spanner zu senden und so die Latenz zu reduzieren.

Batch-DML kann Optimierungen auf Gruppen von Anweisungen innerhalb eines Batches anwenden, 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 sie nicht gegen Datenabhängigkeiten verstoßen.

    Stellen Sie sich beispielsweise ein Szenario vor, in dem Sie eine große Anzahl neuer Zeilen in eine Tabelle mit dem Namen Albums einfügen möchten. Damit Spanner alle erforderlichen INSERT-Anweisungen in einer einzigen, effizienten serverseitigen Aktion optimieren kann, schreiben Sie zuerst eine entsprechende 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 zusammenhängend 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 vor der Ausführung in einen einzigen serverseitigen Vorgang.

  • Schreibvorgänge parallel ausführen

    Spanner optimiert zusammenhängende Gruppen von DML-Anweisungen automatisch, indem sie parallel ausgeführt werden, sofern dadurch keine Datenabhängigkeiten verletzt werden. Diese Optimierung bietet Leistungsvorteile für eine größere Anzahl von im Batch ausgeführten DML-Anweisungen, da sie auf eine Mischung aus DML-Anweisungstypen (INSERT, UPDATE und DELETE) sowie auf parametrisierte und 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. Die folgende zusammenhängende Gruppe von Anweisungen schreibt neue Zeilen in mehrere Tabellen und hat 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. Wenn eine Anweisung während der Ausführung fehlschlägt, wird die Batch-DML-Semantik beibehalten.

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 clientseitiges DML-Batching aktivieren. Der JDBC-Treiber hat ein Verbindungsattribut namens auto_batch_dml, das, wenn es 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 sie 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 Cloud Spanner gepufferte DML-Anweisungen als Batch, wenn eine Nicht-DML-Anweisung ausgeführt oder die aktuelle Transaktion festgeschrieben wird. Diese Property 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.

Option last_statement verwenden, um die DML-Latenz zu reduzieren

Wenn die letzte Anweisung in einer Lese-/Schreibtransaktion eine DML-Anweisung ist, können Sie die Abfrageoption last_statement verwenden, um die Latenz zu reduzieren. Diese Option ist in den Abfrage-APIs executeSql und executeStreamingSql verfügbar.

Wenn Sie diese Option verwenden, werden einige Validierungsschritte, z. B. die Validierung eindeutiger Einschränkungen, bis zum Commit der Transaktion verschoben. Wenn Sie last_statement verwenden, werden nachfolgende Vorgänge wie Lesevorgänge, Abfragen und DML in derselben Transaktion abgelehnt. Diese Option ist nicht mit Mutationen kompatibel. Wenn Sie Mutationen in dieselbe Transaktion einbeziehen, gibt Spanner einen Fehler zurück.

Die Option last_statement wird in den folgenden Clientbibliotheken unterstützt:

  • Go in Version 1.77.0 oder höher
  • Java in Version 2.27.0 oder höher
  • Python in Version 3.53.0 oder höher
  • PGAdapter in Version 0.45.0 oder höher

Sie wird standardmäßig unterstützt und aktiviert, wenn der Autocommit-Modus in den folgenden Treibern verwendet wird:

Go

GoogleSQL 

import (
    "context"
    "fmt"
    "io"

    "cloud.google.com/go/spanner"
)

// Updates a row while also setting the update DML as the last
// statement.
func updateDmlWithLastStatement(w io.Writer, db string) error {
    ctx := context.Background()
    client, err := spanner.NewClient(ctx, db)
    if err != nil {
        return err
    }
    defer client.Close()

    _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // other statements for the transaction if any.

        updateStmt := spanner.Statement{
            SQL: `UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54213`,
        }
        opts := spanner.QueryOptions{LastStatement: true}
        updateRowCount, err := txn.UpdateWithOptions(ctx, updateStmt, opts)
        if err != nil {
            return err
        }
        fmt.Fprintf(w, "%d record(s) updated.\n", updateRowCount)
        return nil
    })
    if err != nil {
        return err
    }

    return nil
}

PostgreSQL 

import (
    "context"
    "fmt"
    "io"

    "cloud.google.com/go/spanner"
)

// Updates a row while also setting the update DML as the last
// statement.
func pgUpdateDmlWithLastStatement(w io.Writer, db string) error {
    ctx := context.Background()
    client, err := spanner.NewClient(ctx, db)
    if err != nil {
        return err
    }
    defer client.Close()

    _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // other statements for the transaction if any.

        updateStmt := spanner.Statement{
            SQL: `UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214`,
        }
        opts := spanner.QueryOptions{LastStatement: true}
        updateRowCount, err := txn.UpdateWithOptions(ctx, updateStmt, opts)
        if err != nil {
            return err
        }
        fmt.Fprintf(w, "%d record(s) updated.\n", updateRowCount)
        return nil
    })
    if err != nil {
        return err
    }

    return nil
}

Java

GoogleSQL 

static void UpdateUsingLastStatement(DatabaseClient client) {
    client
        .readWriteTransaction()
        .run(
            transaction -> {
            // other statements for the transaction if any

            // Pass in the `lastStatement` option to the last DML statement of the transaction.
            transaction.executeUpdate(
                Statement.of(
                    "UPDATE Singers SET Singers.LastName = 'Doe' WHERE SingerId = 54213\n"),
                Options.lastStatement());
            System.out.println("Singer last name updated.");

            return null;
            });
}

PostgreSQL 

static void UpdateUsingLastStatement(DatabaseClient client) {
    client
        .readWriteTransaction()
        .run(
            transaction -> {
            // other statements for the transaction if any.

            // Pass in the `lastStatement` option to the last DML statement of the transaction.
            transaction.executeUpdate(
                Statement.of("UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214\n"),
                Options.lastStatement());
            System.out.println("Singer last name updated.");

            return null;
            });
}

Python

GoogleSQL 

def dml_last_statement_option(instance_id, database_id):
"""Updates using DML where the update set the last statement option."""
# [START spanner_dml_last_statement]
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

def update_singers(transaction):
    # other statements for the transaction if any.

    update_row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54213",
        last_statement=True)

    print("{} record(s) updated.".format(update_row_ct))

database.run_in_transaction(update_singers)

PostgreSQL 

def dml_last_statement_option(instance_id, database_id):
"""Updates using DML where the update set the last statement option."""
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

def update_singers(transaction):
    # other statements for the transaction if any.

    update_row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214",
        last_statement=True)

    print("{} record(s) updated.".format(update_row_ct))

database.run_in_transaction(update_singers)