Bonnes pratiques relatives au langage de manipulation de données

Cette page décrit les bonnes pratiques d'utilisation du langage de manipulation de données (LMD) et du LMD partitionné pour les bases de données utilisant le dialecte GoogleSQL et celles utilisant le dialecte PostgreSQL.

Utiliser une clause WHERE pour réduire le champ d'application des verrous

Les instructions LMD sont exécutées dans les transactions de lecture-écriture. Lorsque Spanner lit des données, il acquiert des verrous de lecture partagés sur des parties limitées des plages de lignes que vous lisez. Plus précisément, il n'acquiert ces verrous que sur les colonnes auxquelles vous accédez. Les verrous peuvent inclure des données qui ne remplissent pas la condition de filtre de la clause WHERE.

Lorsque Spanner modifie des données à l'aide d'instructions LMD, il acquiert des verrous exclusifs sur les données spécifiques que vous modifiez. De plus, il acquiert des verrous partagés de la même manière que lorsque vous consultez des données. Si votre requête inclut de grandes plages de lignes ou une table entière, les verrous partagés peuvent empêcher d'autres transactions de progresser en parallèle.

Pour modifier les données aussi efficacement que possible, utilisez une clause WHERE qui permet à Spanner de ne lire que les lignes nécessaires. Pour ce faire, appliquez un filtre sur la clé primaire ou sur la clé d'un index secondaire. La clause WHERE limite le champ d'application des verrous partagés et permet à Spanner de traiter la mise à jour plus efficacement.

Par exemple, imaginons que l'un des musiciens de la table Singers change de prénom et que vous deviez mettre à jour ce prénom dans votre base de données. Vous pouvez exécuter l'instruction LMD suivante, mais elle force Spanner à analyser l'intégralité de la table et acquiert des verrous partagés qui couvrent l'intégralité de la table. Par conséquent, Spanner doit lire plus de données que nécessaire, et les transactions simultanées ne peuvent pas modifier les données en parallèle :

-- 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";

Pour optimiser l'opération de mise à jour, incluez la colonne SingerId dans la clause WHERE. La colonne SingerId est la seule colonne de clé primaire pour la table Singers :

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

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

S'il n'y a pas d'index sur FirstName ou LastName, vous devez analyser l'intégralité de la table pour trouver les chanteurs cibles. Si vous ne souhaitez pas ajouter d'index secondaire pour rendre la mise à jour plus efficace, incluez la colonne SingerId dans la clause WHERE.

La colonne SingerId est la seule colonne de clé primaire de la table Singers. Pour le trouver, exécutez SELECT dans une transaction distincte en lecture seule avant la transaction de mise à jour :

  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;

Éviter d'utiliser des instructions LMD et des mutations dans la même transaction

Spanner met en mémoire tampon les insertions, les mises à jour et les suppressions effectuées à l'aide d'instructions LMD côté serveur. Les résultats sont visibles par les instructions SQL et LMD ultérieures au sein de la même transaction. Ce comportement est différent de celui de l'API Mutation, où Spanner met en mémoire tampon les mutations côté client et les envoie côté serveur dans le cadre de l'opération de commit. Par conséquent, les mutations de la requête de commit ne sont pas visibles par les instructions SQL ou LMD de la même transaction.

Évitez d'utiliser à la fois des instructions LMD et des mutations dans la même transaction. Si vous utilisez les deux dans la même transaction, vous devez tenir compte de l'ordre d'exécution dans le code de votre bibliothèque cliente. Si une transaction contient à la fois des instructions LMD et des mutations dans la même requête, Spanner exécute les instructions LMD avant les mutations.

Si vous utilisez les deux, le tampon n'écrit qu'à la toute fin de la transaction.

Utiliser la fonction PENDING_COMMIT_TIMESTAMP pour écrire des codes temporels de commit

GoogleSQL

Pour écrire le code temporel de commit dans une instruction LMD, utilisez la fonction PENDING_COMMIT_TIMESTAMP. Cloud Spanner sélectionne le code temporel de commit lorsque la transaction est validée.

Remarque : Une fois la fonction PENDING_COMMIT_TIMESTAMP appelée, la table et les éventuels index dérivés sont illisibles pour les instructions SQL ultérieures de la transaction. Par conséquent, le flux de modifications ne peut pas extraire la valeur précédente de la colonne qui comporte un code temporel de commit en attente, si la colonne est à nouveau modifiée ultérieurement dans la même transaction. Écrivez les codes temporels de commit dans la dernière instruction d'une transaction afin d'éviter les tentatives de lecture de la table. Si vous essayez de lire la table, Spanner affiche une erreur.

PostgreSQL

Pour écrire le code temporel de commit dans une instruction LMD, utilisez la fonction SPANNER.PENDING_COMMIT_TIMESTAMP(). Cloud Spanner sélectionne le code temporel de commit lorsque la transaction est validée.

Remarque : Une fois la fonction SPANNER.PENDING_COMMIT_TIMESTAMP() appelée, la table et les éventuels index dérivés sont illisibles pour les instructions SQL ultérieures de la transaction. Écrivez les codes temporels de commit dans la dernière instruction d'une transaction afin d'éviter les tentatives de lecture de la table. Si vous essayez de lire la table, Spanner affiche une erreur.

LMD partitionné, et fonctions de date et d'horodatage

Le LMD partitionné utilise une ou plusieurs transactions pouvant être exécutées et enregistrées à des moments différents. Si vous utilisez les fonctions date ou timestamp, les lignes modifiées peuvent contenir des valeurs différentes.

Améliorer la latence avec le LMD par lot

Pour réduire la latence, utilisez le LMD par lot pour envoyer plusieurs instructions LMD à Spanner en un seul aller-retour client-serveur.

Le LMD par lot peut appliquer des optimisations à des groupes d'instructions au sein d'un lot pour permettre des mises à jour de données plus rapides et plus efficaces.

• Exécuter des écritures avec une seule requête

  Spanner optimise automatiquement les groupes contigus d'instructions INSERT, UPDATE ou DELETE similaires par lot qui ont des valeurs de paramètre différentes, s'ils ne violent pas les dépendances aux données.

  Par exemple, imaginons que vous souhaitiez insérer un grand ensemble de nouvelles lignes dans une table appelée Albums. Pour permettre à Spanner d'optimiser toutes les instructions INSERT requises en une seule action efficace côté serveur, commencez par écrire une instruction LMD appropriée qui utilise des paramètres de requête SQL :

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

  Ensuite, envoyez à Spanner un lot LMD qui appelle cette instruction de manière répétée et contiguë, les répétitions ne différant que par les valeurs que vous liez aux trois paramètres de requête de l'instruction. Spanner optimise ces instructions LMD structurellement identiques en une seule opération côté serveur avant de l'exécuter.

• Exécuter des écritures en parallèle

  Spanner optimise automatiquement les groupes contigus d'instructions LMD en les exécutant en parallèle lorsque cela ne va pas à l'encontre des dépendances de données. Cette optimisation améliore les performances d'un plus grand nombre d'instructions LMD par lot, car elle peut s'appliquer à une combinaison de types d'instructions instruction LMD (INSERT, UPDATE et DELETE) et à des instructions LMD paramétrées ou non paramétrées.

  Par exemple, notre exemple de schéma comporte les tables Singers, Albums et Accounts. Albums est entrelacée dans Singers et stocke des informations sur les albums pour Singers. Le groupe d'instructions contigu suivant écrit de nouvelles lignes dans plusieurs tables et ne présente pas de dépendances de données complexes.

  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 optimise ce groupe d'instructions LMD en les exécutant en parallèle. Les écritures sont appliquées dans l'ordre des instructions du lot et conservent la sémantique LMD du lot si une instruction échoue pendant l'exécution.

Activer le traitement par lot côté client dans JDBC

Pour les applications Java utilisant un pilote JDBC compatible avec Spanner, vous pouvez réduire la latence en activant le traitement par lot LMD côté client. Le pilote JDBC possède une propriété de connexion appelée auto_batch_dml qui, lorsqu'elle est activée, met en mémoire tampon les instructions LMD sur le client et les envoie à Spanner sous la forme d'un seul lot. Cela peut réduire le nombre d'allers-retours vers le serveur et améliorer les performances globales.

Par défaut, auto_batch_dml est défini sur false. Vous pouvez l'activer en le définissant sur true dans votre chaîne de connexion JDBC.

Exemple :

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
}

Lorsque cette propriété de connexion est activée, Spanner envoie les instructions LMD mises en mémoire tampon sous forme de lot lorsqu'une instruction non LMD est exécutée ou lorsque la transaction en cours est validée. Cette propriété ne s'applique qu'aux transactions en lecture-écriture. Les instructions LMD en mode autocommit sont exécutées directement.

Par défaut, le nombre de mises à jour pour les instructions LMD mises en mémoire tampon est défini sur 1. Vous pouvez modifier ce paramètre en définissant la variable de connexion auto_batch_dml_update_count sur une autre valeur. Pour en savoir plus, consultez Propriétés de connexion compatibles avec JDBC.

Utiliser l'option last_statement pour réduire la latence LMD

Lorsque la dernière instruction d'une transaction en lecture-écriture est une instruction LMD, vous pouvez utiliser l'option de requête last_statement pour réduire la latence. Cette option est disponible dans les API de requête executeSql et executeStreamingSql.

Cette option permet de différer certaines étapes de validation, telles que la validation de contrainte unique, jusqu'à ce que la transaction soit validée. Lorsque vous utilisez last_statement, les opérations ultérieures, telles que les lectures, les requêtes et les instructions LMD, sont rejetées dans la même transaction. Cette option n'est pas compatible avec les mutations. Si vous incluez des mutations dans la même transaction, Spanner renvoie une erreur.

L'option last_statement est compatible avec les bibliothèques clientes suivantes :

• Go 1.77.0 ou version ultérieure
• Java dans la version 2.27.0 ou ultérieure
• Python 3.53.0 ou version ultérieure
• PGAdapter version 0.45.0 ou ultérieure

Il est compatible et activé par défaut lorsque vous utilisez le mode de commit automatique dans les pilotes suivants :

• Pilote JDBC en version 6.87.0 ou ultérieure
• Pilote database/sql Go dans la version 1.11.2 ou ultérieure

• Pilote dbapi Python version 3.53.0 ou ultérieure

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)