Questa pagina descrive le best practice per l'utilizzo di Data Manipulation Language (DML) e DML partizionato per i database con dialetto GoogleSQL e PostgreSQL.
Utilizza una clausola WHERE per ridurre l'ambito dei blocchi
Le istruzioni DML vengono eseguite all'interno di transazioni di lettura/scrittura. Quando Spanner legge i dati, acquisisce blocchi di lettura condivisi su porzioni limitate degli intervalli di righe che leggi. In particolare, acquisisce questi blocchi solo sulle colonne a cui accedi. I blocchi possono includere dati che non soddisfano la condizione di filtro della clausola WHERE.
Quando Spanner modifica i dati utilizzando istruzioni DML, acquisisce blocchi esclusivi sui dati specifici che stai modificando. Inoltre, acquisisce blocchi condivisi nello stesso modo in cui vengono acquisiti quando leggi i dati. Se la richiesta include intervalli di righe di grandi dimensioni o un'intera tabella, i blocchi condivisi potrebbero impedire l'avanzamento di altre transazioni in parallelo.
Per modificare i dati nel modo più efficiente possibile, utilizza una clausola WHERE che consenta a Spanner di leggere solo le righe necessarie. Puoi raggiungere questo obiettivo con un filtro sulla chiave primaria o sulla chiave di un indice secondario. La clausola WHERE limita l'ambito dei blocchi condivisi e consente a Spanner di elaborare l'aggiornamento in modo più efficiente.
Ad esempio, supponiamo che uno dei musicisti nella tabella Singers cambi il proprio nome e che tu debba aggiornare il nome nel database. Puoi eseguire la seguente istruzione DML, ma questa forza Spanner a scansionare l'intera tabella e acquisisce blocchi condivisi che coprono l'intera tabella. Di conseguenza, Spanner deve leggere più dati del necessario e le transazioni simultanee non possono modificare i dati in parallelo:
-- 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";
Per rendere l'aggiornamento più efficiente, includi la colonna SingerId nella clausola WHERE. La colonna SingerId è l'unica colonna della chiave primaria per la tabella Singers:
-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE
UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"
Se non è presente un indice su FirstName o LastName, devi eseguire la scansione dell'intera tabella per trovare i cantanti target. Se non vuoi aggiungere un indice secondario per rendere l'aggiornamento più efficiente, includi la colonna SingerId nella clausola WHERE.
La colonna SingerId è l'unica colonna di chiave primaria per la tabella Singers. Per trovarlo, esegui SELECT in una transazione di sola lettura separata prima della transazione di aggiornamento:
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;
Evita di utilizzare istruzioni DML e mutazioni nella stessa transazione
Spanner memorizza nel buffer gli inserimenti, gli aggiornamenti e le eliminazioni eseguiti utilizzando le istruzioni DML sul lato server e i risultati sono visibili alle istruzioni SQL e DML successive all'interno della stessa transazione. Questo comportamento è diverso dall'API Mutation, in cui Spanner memorizza nel buffer le mutazioni sul lato client e le invia sul lato server nell'ambito dell'operazione di commit. Di conseguenza, le mutazioni nella richiesta di commit non sono visibili alle istruzioni SQL o DML all'interno della stessa transazione.
Evita di utilizzare sia istruzioni DML che mutazioni nella stessa transazione. Se utilizzi entrambi nella stessa transazione, devi tenere conto dell'ordine di esecuzione nel codice della libreria client. Se una transazione contiene sia istruzioni DML sia mutazioni nella stessa richiesta, Spanner esegue le istruzioni DML prima delle mutazioni.
Se li utilizzi entrambi, il buffer scrive solo alla fine della transazione.
Utilizza la funzione PENDING_COMMIT_TIMESTAMP per scrivere i timestamp di commit
GoogleSQL
Utilizza la funzione PENDING_COMMIT_TIMESTAMP per scrivere il timestamp di commit in un'istruzione DML. Spanner seleziona il timestamp di commit quando viene eseguito il commit della transazione.
PostgreSQL
Utilizza la funzione SPANNER.PENDING_COMMIT_TIMESTAMP() per scrivere il timestamp di commit in un'istruzione DML. Spanner seleziona il timestamp di commit quando viene eseguito il commit della transazione.
DML partizionato e funzioni di data e timestamp
Il DML partizionato utilizza una o più transazioni che potrebbero essere eseguite e sottoposte a commit in momenti diversi. Se utilizzi le funzioni date o timestamp, le righe modificate potrebbero contenere valori diversi.
Migliora la latenza con DML in batch
Per ridurre la latenza, utilizza il DML batch per inviare più istruzioni DML a Spanner in un unico round trip client-server.
Il DML in batch può applicare ottimizzazioni a gruppi di istruzioni all'interno di un batch per consentire aggiornamenti dei dati più rapidi ed efficienti.
Esegui le scritture con una singola richiesta
Spanner ottimizza automaticamente i gruppi contigui di istruzioni raggruppate
INSERT,UPDATEoDELETEsimili con valori parametro diversi, se non violano le dipendenze dai dati.Ad esempio, considera uno scenario in cui vuoi inserire un ampio insieme di nuove righe in una tabella denominata
Albums. Per consentire a Spanner di ottimizzare tutte le istruzioniINSERTrichieste in un'unica azione lato server efficiente, inizia scrivendo un'istruzione DML appropriata che utilizzi i parametri di query SQL:INSERT INTO Albums (SingerId, AlbumId, AlbumTitle) VALUES (@Singer, @Album, @Title);Invia quindi a Spanner un batch DML che richiama questa istruzione ripetutamente e in modo contiguo, con le ripetizioni che differiscono solo per i valori che associ ai tre parametri di ricerca dell'istruzione. Spanner ottimizza queste istruzioni DML strutturalmente identiche in un'unica operazione lato server prima di eseguirla.
Esegui le scritture in parallelo
Spanner ottimizza automaticamente i gruppi contigui di istruzioni DML eseguendoli in parallelo quando ciò non viola le dipendenze dai dati. Questa ottimizzazione offre vantaggi in termini di prestazioni a un insieme più ampio di istruzioni DML in batch, perché può essere applicata a una combinazione di tipi di istruzione DML (
INSERT,UPDATEeDELETE) e a istruzioni DML con o senza parametri.Ad esempio, il nostro schema di esempio contiene le tabelle
Singers,AlbumseAccounts.Albumsè intercalata inSingerse memorizza informazioni sugli album perSingers. Il seguente gruppo contiguo di istruzioni scrive nuove righe in più tabelle e non presenta dipendenze dai dati complesse.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 ottimizza questo gruppo di istruzioni DML eseguendole in parallelo. Le scritture vengono applicate nell'ordine delle istruzioni nel batch e mantengono la semantica DML batch se un'istruzione non va a buon fine durante l'esecuzione.
Abilita il raggruppamento lato client in JDBC
Per le applicazioni Java che utilizzano un driver JDBC supportato da Spanner, puoi ridurre la latenza attivando il batching DML lato client. Il driver JDBC ha una
proprietà di connessione
denominata auto_batch_dml che, se abilitata, memorizza nel buffer le istruzioni DML sul client
e le invia a Spanner come un unico batch. In questo modo è possibile ridurre il numero di round trip al server e migliorare le prestazioni complessive.
Per impostazione predefinita, auto_batch_dml è impostato su false. Puoi attivarla impostandola su true nella stringa di connessione JDBC.
Ad esempio:
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
}
Con questa proprietà di connessione abilitata, Spanner invia le istruzioni DML memorizzate nel buffer come batch quando viene eseguita un'istruzione non DML o quando viene eseguito il commit della transazione corrente. Questa proprietà si applica solo alle transazioni di lettura/scrittura; le istruzioni DML in modalità autocommit vengono eseguite direttamente.
Per impostazione predefinita, il conteggio degli aggiornamenti per le istruzioni DML con buffer è impostato su 1. Puoi modificare questa impostazione impostando la variabile di connessione auto_batch_dml_update_count su un valore diverso. Per saperne di più, consulta Proprietà di connessione supportate da JDBC.
Utilizza l'opzione last_statement per ridurre la latenza DML
Quando l'ultima istruzione in una transazione di lettura/scrittura è un'istruzione DML, puoi utilizzare l'opzione di query last_statement per ridurre la latenza. Questa opzione è disponibile nelle API di query executeSql e executeStreamingSql.
L'utilizzo di questa opzione rimanda alcuni passaggi di convalida, come la convalida dei vincoli univoci, fino al commit della transazione. Quando utilizzi last_statement,
le operazioni successive, come letture, query e DML, nella stessa transazione
vengono rifiutate. Questa opzione non è compatibile con le mutazioni. Se includi mutazioni nella stessa transazione, Spanner restituisce un errore.
L'opzione last_statement è supportata nelle seguenti librerie client:
- Go versione 1.77.0 o successive
- Java nella versione 2.27.0 o successive
- Python nella versione 3.53.0 o successive
- PGAdapter versione 0.45.0 o successive
È supportato e abilitato per impostazione predefinita quando utilizzi la modalità di commit automatico nei seguenti driver:
- Driver JDBC nella versione 6.87.0 o successive
- Driver database/sql di Go nella versione 1.11.2 o successive
Driver Python dbapi nella versione 3.53.0 o successive
Vai
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)