Best practice per Data Manipulation Language

Questa pagina descrive le best practice per l'utilizzo di DML (Data Manipulation Language) e DML partizionato per i database con dialetto GoogleSQL e PostgreSQL.

Utilizza una clausola WHERE per ridurre l'ambito dei blocchi

Esegui istruzioni DML 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 le istruzioni DML, acquisisce blocchi esclusivi sui dati specifici che stai modificando. Inoltre, acquisisce blocchi condivisi nello stesso modo in cui quando leggi i dati. Se la richiesta include intervalli di righe di grandi dimensioni o un'intera tabella, i blocchi condivisi potrebbero impedire ad altre transazioni di avanzare 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 nome e tu debba aggiornarlo nel tuo database. Potresti eseguire la seguente istruzione DML, ma Spanner è costretto 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 di 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 alcun indice su FirstName o LastName, devi scansionare l'intera tabella per trovare i cantanti di destinazione. Se non vuoi aggiungere un indice secondario per rendere più efficiente l'aggiornamento, 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 separata di sola lettura 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 in buffer inserimenti, aggiornamenti ed eliminazioni eseguiti utilizzando istruzioni DML lato server e i risultati sono visibili a istruzioni SQL e DML successive all'interno della stessa transazione. Questo comportamento è diverso dall'API Mutation, in cui Spanner memorizza nel buffer le mutazioni lato client e le invia lato server nell'ambito dell'operazione di commit. Di conseguenza, le modifiche 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 li 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.

Per le operazioni supportate solo tramite mutazioni, potresti voler combinare istruzioni DML e mutazioni nella stessa transazione, ad esempio insert_or_update.

Se utilizzi entrambi, il buffer scrive solo alla fine della transazione.

Utilizzare la funzione PENDING_COMMIT_TIMESTAMP per scrivere i timestamp dei commit

GoogleSQL

Utilizzi la funzione PENDING_COMMIT_TIMESTAMP per scrivere il timestamp del commit in un'istruzione DML. Spanner seleziona il timestamp di commit quando la transazione viene eseguita.

Nota:dopo aver chiamato la funzione PENDING_COMMIT_TIMESTAMP, la tabella e qualsiasi indice derivato non sono leggibili per le istruzioni SQL future nella transazione. Per questo motivo, lo stream delle modifiche non può estrarre il valore precedente per la colonna con un timestamp di commit in attesa, se la colonna viene modificata di nuovo in un secondo momento nella stessa transazione. Devi scrivere i timestamp di commit come ultima istruzione in una transazione per evitare la possibilità di tentare di leggere la tabella. Se provi a leggere la tabella, Spanner genera un errore.

PostgreSQL

Utilizzi la funzione SPANNER.PENDING_COMMIT_TIMESTAMP() per scrivere il timestamp del commit in un'istruzione DML. Spanner seleziona il timestamp di commit quando la transazione viene eseguita.

Nota: dopo aver chiamato la funzione SPANNER.PENDING_COMMIT_TIMESTAMP(), la tabella e qualsiasi indice derivato non sono leggibili per le istruzioni SQL successive nella transazione. Devi scrivere i timestamp di commit come ultima istruzione di una transazione per evitare la possibilità di tentare di leggere la tabella. Se provi a leggere la tabella, Spanner restituisce un errore.

DML partizionato e funzioni di data e timestamp

DML partizionato utilizza una o più transazioni che potrebbero essere eseguite e sottoposte a commit in momenti diversi. Se utilizzi le funzioni data o timestamp, le righe modificate potrebbero contenere valori diversi.

Migliorare la latenza con DML batch

Per ridurre la latenza, utilizza DML batch per inviare più istruzioni DML a Spanner in un unico round trip client-server.

Le istruzioni DML batch possono applicare ottimizzazioni a gruppi di istruzioni all'interno di un batch per consentire aggiornamenti dei dati più rapidi ed efficienti.

• Eseguire scritture con una singola richiesta

  Spanner ottimizza automaticamente i gruppi contigui di istruzioni batch INSERT, UPDATE o DELETE simili che hanno valori di parametri diversi, se non violano le dipendenze dei 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 istruzioni INSERT richieste in un'unica azione lato server efficiente, inizia scrivendo un'istruzione DML appropriata che utilizzi i parametri della query SQL:

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

  Poi, invia 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 trparametri di ricercary dell'istruzione. Spanner ottimizza queste istruzioni DML strutturalmente identiche in una singola operazione lato server prima di eseguirle.

• Eseguire scritture in parallelo

  Spanner ottimizza automaticamente i gruppi contigui di istruzioni DML eseguendole in parallelo quando ciò non viola le dipendenze dei dati. Questa ottimizzazione offre vantaggi in termini di prestazioni a un insieme più ampio di istruzioni DML batch perché può essere applicata a un mix di tipi di istruzione DML (INSERT, UPDATE e DELETE) e a istruzioni DML con o senza parametri.

  Ad esempio, il nostro schema di esempio contiene le tabelle Singers, Albums e Accounts. Albums è intercalato in Singers e memorizza informazioni sugli album per Singers. Il seguente gruppo contiguo di istruzioni scrive nuove righe in più tabelle e non presenta dipendenze complesse dai dati.

  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 viene eseguita correttamente.

Attiva il batch lato client in JDBC

Per le applicazioni Java che utilizzano un driver JDBC supportato da Spanner, puoi ridurre la latenza attivando il batch DML lato client. Il driver JDBC ha una proprietà di connessione denominata auto_batch_dml che, se attivata, memorizza nel buffer le istruzioni DML sul client e le invia a Spanner come singolo batch. Ciò può ridurre il numero di round trip al server e migliorare le prestazioni complessive.

Per impostazione predefinita, auto_batch_dml è impostato su false. Puoi attivarlo impostando 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
}

Se questa proprietà di connessione è abilitata, Spanner invia 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 memorizzate nel 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ù, vedi Proprietà di connessione JDBC supportate.