Best practice di SQL

Come descritto in Piani di esecuzione delle query, il compilatore SQL trasforma un'istruzione SQL in un piano di esecuzione delle query, che viene utilizzato per ottenere i risultati della query. Questa pagina descrive le best practice per la creazione di istruzioni SQL per aiutare Spanner a trovare piani di esecuzione efficienti.

Le istruzioni SQL di esempio mostrate in questa pagina utilizzano lo schema di esempio seguente:

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 BirthDate  DATE
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ReleaseDate  DATE
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Per il riferimento completo a SQL, consulta Sintassi delle istruzioni, Funzioni e operatori, e Struttura lessicale e sintassi.

PostgreSQL

CREATE TABLE Singers (
 SingerId   BIGINT PRIMARY KEY,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024),
 SingerInfo BYTEA,
 BirthDate  TIMESTAMPTZ
);

CREATE TABLE Albums (
 SingerId        BIGINT NOT NULL,
 AlbumId         BIGINT NOT NULL,
 AlbumTitle      VARCHAR(1024),
 ReleaseDate     DATE,
 PRIMARY KEY(SingerId, AlbumId),
 FOREIGN KEY (SingerId) REFERENCES Singers(SingerId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Per saperne di più, consulta Il linguaggio PostgreSQL in Spanner.

Utilizzare parametri di ricerca

Spanner supporta parametri di ricerca per aumentare le prestazioni e contribuire a impedire SQL injection quando le query vengono costruite utilizzando l'input utente'utente. Puoi utilizzare parametri di ricerca come sostituti di espressioni arbitrarie, ma non come sostituti di identificatori, nomi di colonne, nomi di tabelle o altre parti della query.

I parametri possono essere visualizzati ovunque sia previsto un valore letterale. Lo stesso nome parametro può essere utilizzato più volte in un'unica istruzione SQL.

In sintesi, parametri di ricerca supportano l'esecuzione delle query nei seguenti modi:

• Piani preottimizzati: le query che utilizzano i parametri possono essere eseguite più velocemente su ogni chiamata perché la parametrizzazione consente a Spanner di memorizzare più facilmente nella cache il piano di esecuzione.
• Composiziono semplificata delle query: non è necessario applicare i caratteri di escape ai valori di stringa quando li fornisci nei parametri di ricerca. I parametri di query riducono anche il rischio di errori di sintassi.
• Sicurezza: i parametri di query rendono le query più sicure proteggendoti da vari attacchi di SQL injection. Questa protezione è particolarmente importante per le query che crei in base all'input utente dell'utente.

Informazioni su come Spanner esegue le query

Nota: Spanner Studio (in precedenza Query nella console Google Cloud ) supporta le operazioni SQL, DML e DDL in un unico editor. Per ulteriori informazioni, consulta Gestire i dati utilizzando la console Google Cloud .

Spanner ti consente di eseguire query sui database utilizzando istruzioni SQL declarative che specificano i dati che vuoi recuperare. Se vuoi capire in che modo Spanner ottiene i risultati, esamina il piano di esecuzione della query. Un piano di esecuzione della query mostra il costo computazionale associato a ciascun passaggio della query. Utilizzando questi costi, puoi eseguire il debug dei problemi di prestazioni delle query e ottimizzare la query. Per scoprire di più, consulta Piani di esecuzione delle query.

Puoi recuperare i piani di esecuzione delle query tramite la console Google Cloud o le librerie client.

Per ottenere un piano di esecuzione delle query per una query specifica utilizzando la console Google Cloud , volgi i seguenti passaggi:

1. Apri la pagina delle istanze Spanner.

   Vai alle istanze Spanner

2. Seleziona i nomi dell'istanza Spanner e del database su cui eseguire query.

3. Fai clic su Spanner Studio nel pannello di navigazione a sinistra.

4. Digita la query nel campo di testo e fai clic su Esegui query.

5. Fai clic su Spiegazione
   . La console Google Cloud mostra un piano di esecuzione visivo per la query.

   

Per saperne di più su come comprendere i piani visivi e utilizzarli per eseguire il debug delle query, consulta Ottimizzare una query utilizzando il visualizzatore del piano query.

Puoi anche visualizzare esempi di piani di query storici e confrontare il rendimento di una query nel tempo per determinate query. Per scoprire di più, consulta Piani di query campionati.

Utilizzare gli indici secondari

Come altri database relazionali, Spanner offre indici secondari, che puoi utilizzare per recuperare i dati utilizzando un'istruzione SQL o l'interfaccia di lettura di Spanner. Il modo più comune per recuperare i dati da un indice è utilizzare Spanner Studio. L'utilizzo di un indice secondario in una query SQL consente di specificare come vuoi che Spanner ottenga i risultati. La specifica di un indice secondario può velocizzare l'esecuzione delle query.

Ad esempio, supponiamo di voler recuperare gli ID di tutti i cantanti con un determinato cognome. Un modo per scrivere una query SQL di questo tipo è:

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

Questa query restituirà i risultati previsti, ma l'operazione potrebbe richiedere molto tempo. I tempi dipendono dal numero di righe nella tabella Singers e da quante soddisfano il predicato WHERE s.LastName = 'Smith'. Se non esiste un indice secondario che contiene la colonna LastName da leggere, il piano di query legge l'intera tabella Singers per trovare le righe corrispondenti al predicato. La lettura dell'intera tabella è chiamata scansione completa della tabella. Una ricerca completa della tabella è un modo costoso per ottenere i risultati quando la tabella contiene solo una piccola percentuale di Singers con quel cognome.

Puoi migliorare le prestazioni di questa query definendo un indice secondario sulla colonna del cognome:

CREATE INDEX SingersByLastName ON Singers (LastName);

Poiché l'indice secondario SingersByLastName contiene la colonna della tabella indicizzata LastName e la colonna della chiave primaria SingerId, Spanner può recuperare tutti i dati dalla tabella dell'indice molto più piccola anziché eseguire la scansione della tabella Singers completa.

In questo scenario, Spanner utilizza automaticamente l'indice secondario SingersByLastName durante l'esecuzione della query (a condizione che siano trascorsi tre giorni dalla creazione del database; consulta Una nota sui nuovi database). Tuttavia, è meglio indicare esplicitamente a Spanner di utilizzare l'indice specificando una direttiva di indice nella clausola FROM:

GoogleSQL

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

 SELECT s.SingerId
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Supponiamo ora che tu voglia recuperare anche il nome del cantante oltre all'ID. Anche se la colonna FirstName non è inclusa nell'indice, devi comunque specificare la direttiva di indice come prima:

GoogleSQL

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

SELECT s.SingerId, s.FirstName
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

L'utilizzo dell'indice offre comunque un vantaggio in termini di prestazioni perché Spanner non deve eseguire una scansione completa della tabella durante l'esecuzione del piano di query. Seleziona invece il sottoinsieme di righe che soddisfano il predicato dall'indice SingersByLastName e poi esegue una ricerca nella tabella di base Singers per recuperare il primo nome solo per quel sottoinsieme di righe.

Se non vuoi che Spanner debba recuperare righe dalla tabella di base, puoi archiviare una copia della colonna FirstName nell'indice stesso:

GoogleSQL

CREATE INDEX SingersByLastName ON Singers (LastName) STORING (FirstName);

PostgreSQL

CREATE INDEX SingersByLastName ON Singers (LastName) INCLUDE (FirstName);

L'utilizzo di una clausola STORING (per il dialetto GoogleSQL) o di una clausola INCLUDE (per il dialetto PostgreSQL) come questa comporta un costo aggiuntivo per lo spazio di archiviazione, ma offre i seguenti vantaggi:

• Le query SQL che utilizzano l'indice e selezionano le colonne memorizzate nella clausola STORING o INCLUDE non richiedono un'unione aggiuntiva alla tabella di base.
• Le chiamate di lettura che utilizzano l'indice possono leggere le colonne memorizzate nella clausola STORING o INCLUDE.

Gli esempi precedenti mostrano in che modo gli indici secondari possono velocizzare le query quando le righe scelte dalla clausola WHERE di una query possono essere identificate rapidamente utilizzando l'indice secondario.

Un altro scenario in cui gli indici secondari possono offrire vantaggi in termini di prestazioni riguarda determinate query che restituiscono risultati ordinati. Ad esempio, supponiamo di voler recuperare tutti i titoli degli album e le relative date di uscita in ordine crescente della data di rilascio e in ordine decrescente di titolo dell'album. Puoi scrivere una query SQL come segue:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Senza un indice secondario, questa query richiede un passaggio di ordinamento potenzialmente costoso nel piano di esecuzione. Puoi velocizzare l'esecuzione delle query definendo questo indice secondario:

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

Quindi, riscrivi la query in modo da utilizzare l'indice secondario:

GoogleSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

PostgreSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums /*@ FORCE_INDEX=AlbumsByReleaseDateTitleDesc */ AS s
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Questa definizione di query e indice soddisfa entrambi i seguenti criteri:

• Per rimuovere il passaggio di ordinamento, assicurati che l'elenco di colonne nella clausola ORDER BY sia un prefisso dell'elenco di chiavi dell'indice.
• Per evitare di eseguire nuovamente l'unione dalla tabella di base per recuperare le colonne mancanti, assicurati che l'indice copra tutte le colonne della tabella utilizzate dalla query.

Sebbene gli indici secondari possano velocizzare le query comuni, la loro aggiunta può aumentare la latenza delle operazioni di commit, perché ogni indice secondario richiede in genere il coinvolgimento di un nodo aggiuntivo in ogni commit. Per la maggior parte dei carichi di lavoro, è sufficiente avere alcuni indici secondari. Tuttavia, devi valutare se ti interessa di più la latenza in lettura o scrittura e quali operazioni sono più critiche per il tuo carico di lavoro. Esegui il benchmarking del tuo workload per assicurarti che funzioni come previsto.

Per il riferimento completo sugli indici secondari, consulta Indici secondari.

Ottimizzare le scansioni

Per alcune query Spanner potrebbe essere utile utilizzare un metodo di elaborazione orientato ai batch durante la scansione dei dati anziché il metodo di elaborazione più comune orientato alle righe. L'elaborazione delle analisi in batch è un modo più efficiente per elaborare contemporaneamente grandi volumi di dati e consente alle query di ottenere un utilizzo e una latenza della CPU inferiori.

L'operazione di scansione di Spanner avvia sempre l'esecuzione in modalità orientata alle righe. Durante questo periodo, Spanner raccoglie diverse metriche di runtime. Quindi, Spanner applica un insieme di controlli euristici basati sul risultato di queste metriche per determinare la modalità di scansione ottimale. Se opportuno, Spanner passa a una modalità di elaborazione batch per contribuire a migliorare il throughput e le prestazioni della scansione.

Casi d'uso comuni

Le query con le seguenti caratteristiche traggono generalmente vantaggio dall'utilizzo dell'elaborazione orientata ai batch:

• Scansioni di grandi dimensioni su dati aggiornati di rado.
• Esegue scansioni con predicati su colonne a larghezza fissa.
• Scansioni con un numero elevato di ricerche. Una ricerca utilizza un indice per recuperare i record.

Casi d'uso senza miglioramenti delle prestazioni

Non tutte le query beneficiano dell'elaborazione batch. I seguenti tipi di query hanno un rendimento migliore con l'elaborazione di scansioni orientate alle righe:

• Query di ricerca dei punti: query che recuperano una sola riga.
• Query di scansione piccole: scansioni delle tabelle che analizzano solo alcune righe, a meno che non abbiano un numero elevato di ricerche.
• Query che utilizzano LIMIT.
• Query che leggono dati con un tasso di abbandono elevato: query in cui più del 10% circa dei dati letti viene aggiornato di frequente.
• Query con righe contenenti valori di grandi dimensioni: le righe con valori di grandi dimensioni sono quelle contenenti valori superiori a 32.000 byte (precompressione) in una singola colonna.

Come controllare il metodo di scansione utilizzato da una query

Per verificare se la query utilizza l'elaborazione batch, l'elaborazione orientata alle righe o se passa automaticamente da un metodo di scansione all'altro:

1. Vai alla pagina Istanze di Spanner nella console Google Cloud .

   Vai alla pagina Istanze

2. Fai clic sul nome dell'istanza con la query che vuoi esaminare.

3. Nella tabella Database, fai clic sul database con la query che vuoi esaminare.

4. Nel menu di navigazione, fai clic su Spanner Studio.

5. Apri una nuova scheda facendo clic su Nuova scheda dell'editor SQL o Nuova scheda.

6. Quando viene visualizzato l'editor delle query, scrivi la query.

7. Fai clic su Esegui.

   Spanner esegue la query e mostra i risultati.

8. Fai clic sulla scheda Spiegazione sotto l'editor di query.

   Spanner mostra un visualizzatore del piano di esecuzione delle query. Ogni scheda del grafico rappresenta un iteratore.

9. Fai clic sulla scheda dell'iteratore Scansione tabella per aprire un riquadro informativo.

   Il riquadro delle informazioni mostra informazioni contestuali sulla scansione selezionata. Il metodo di scansione è mostrato in questa scheda. Automatico indica che Spanner determina il metodo di scansione. Altri valori possibili sono Vettore per l'elaborazione batch e Scalare per l'elaborazione basata su riga.

   

Come applicare il metodo di scansione utilizzato da una query

Per ottimizzare le prestazioni delle query, Spanner sceglie il metodo di scansione ottimale per la query. Ti consigliamo di utilizzare questo metodo di scansione predefinito. Tuttavia, in alcuni casi potrebbe essere necessario applicare un tipo specifico di metodo di scansione.

Come applicare la scansione basata su batch

Puoi applicare la scansione basata su batch a livello di tabella e di istruzioni.

Per applicare il metodo di scansione batch a livello di tabella, utilizza un suggerimento tabella nella query:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=BATCH} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=batch */ JOIN t2 on ...)
  WHERE ...

Per applicare il metodo di scansione orientato ai batch a livello di dichiarazione, utilizza un indicazione di dichiarazione nella query:

GoogleSQL

  @{SCAN_METHOD=BATCH}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=batch */
  SELECT ...
  FROM ...
  WHERE ...

Come disattivare la scansione automatica e applicare la scansione basata su riga

Sebbene non sia consigliabile disattivare il metodo di scansione automatica impostato da Spanner, puoi decidere di disattivarlo e utilizzare il metodo di scansione orientato alle righe per la risoluzione dei problemi, ad esempio per diagnosticare la latenza.

Per disattivare il metodo di scansione automatica e applicare l'elaborazione delle righe a livello di tabella, utilizza un suggerimento tabella nella query:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=ROW} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=row */ JOIN t2 on ...)
  WHERE ...

Per disattivare il metodo di scansione automatica e applicare l'elaborazione delle righe a livello di istruzione, utilizza un suggerimento di istruzione nella query:

GoogleSQL

  @{SCAN_METHOD=ROW}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=row */
  SELECT ...
  FROM ...
  WHERE ...

Ottimizza le ricerche delle chiavi di intervallo

Un utilizzo comune di una query SQL è leggere più righe da Spanner in base a un elenco di chiavi note.

Le best practice riportate di seguito ti aiutano a scrivere query efficienti quando recuperi i dati in base a una serie di chiavi:

• Se l'elenco di chiavi è sparso e non adiacente, utilizza parametri di ricerca e UNNEST per creare la query.

  Ad esempio, se l'elenco di chiavi è {1, 5, 1000}, scrivi la query come segue:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST (@KeyList)

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST ($1)

  Note:

  • L'operatore array UNNEST appiattisce un array di input in righe di elementi.

  • Il parametro di query, @KeyList per GoogleSQL e $1 per PostgreSQL, può velocizzare la query come discusso nella best practice precedente.

• Se l'elenco di chiavi è adiacente e rientra in un intervallo, specifica i limiti inferiore e superiore dell'intervallo di chiavi nella clausola WHERE.

  Ad esempio, se l'elenco di chiavi è {1,2,3,4,5}, costruisci la query come segue:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN @min AND @max

  Nota: in questo caso, @min e @max sono parametri di ricerca associati rispettivamente ai valori 1 e 5.

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN $1 AND $2

  Nota: in questo caso, $1 e $2 sono parametri di ricerca associati rispettivamente ai valori 1 e 5.

  Questa query è più efficiente solo se le chiavi nell'intervallo di chiavi sono adiacenti. In altre parole, se l'elenco di chiavi è {1, 5, 1000}, non specificare i limiti inferiore e superiore come nella query precedente perché la query risultante esaminerà tutti i valori compresi tra 1 e 1000.

Ottimizzare i join

Le operazioni di join possono essere costose perché possono aumentare notevolmente il numero di righe che la query deve analizzare, con conseguente rallentamento delle query. Oltre alle tecniche che utilizzi solitamente in altri database relazionali per ottimizzare le query join, ecco alcune best practice per un JOIN più efficiente quando utilizzi Spanner SQL:

• Se possibile, unisci i dati nelle tabelle interlacciate in base alla chiave primaria. Ad esempio:

  SELECT s.FirstName, a.ReleaseDate
  FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;

  Le righe della tabella interlacciata Albums vengono memorizzate fisicamente nelle stesse suddivisioni della riga principale in Singers, come discusso in Schema e modello di dati. Pertanto, le unioni possono essere completate localmente senza inviare molti dati sulla rete.

• Utilizza la direttiva join se vuoi forzare l'ordine dell'unione. Ad esempio:

  GoogleSQL

  SELECT *
  FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

  PostgreSQL

  SELECT *
  FROM Singers AS s JOIN/*@ FORCE_JOIN_ORDER=TRUE */ Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

  La direttiva join FORCE_JOIN_ORDER indica a Spanner di utilizzare l'ordine di join specificato nella query (ovvero Singers JOIN Albums, non Albums JOIN Singers). I risultati restituiti sono gli stessi, indipendentemente dall'ordine scelto da Spanner. Tuttavia, ti consigliamo di utilizzare questa direttiva di join se nel piano di query noti che Spanner ha modificato l'ordine di join e ha causato conseguenze indesiderate, ad esempio risultati intermedi più grandi o ha perso opportunità di ricerca delle righe.

• Utilizza una direttiva join per scegliere un'implementazione dell'unione. Quando utilizzi SQL per eseguire query su più tabelle, Spanner utilizza automaticamente un metodo di join che ha maggiori probabilità di rendere la query più efficiente. Tuttavia, Google ti consiglia di eseguire il test con algoritmi di join diversi. La scelta dell'algoritmo di join corretto può migliorare la latenza, il consumo di memoria o entrambi. Questa query mostra la sintassi per l'utilizzo di un'istruzione JOIN con il suggerimento JOIN_METHOD per scegliere un HASH JOIN:

  GoogleSQL

  SELECT *
  FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
  ON a.SingerId = a.SingerId

  PostgreSQL

  SELECT *
  FROM Singers s JOIN/*@ JOIN_METHOD=HASH_JOIN */ Albums AS a
  ON a.SingerId = a.SingerId

• Se utilizzi un HASH JOIN o un APPLY JOIN e hai una clausola WHERE molto selettiva su un lato del JOIN, inserisci la tabella che genera il numero più piccolo di righe come prima tabella nella clausola FROM della join. Questa struttura è utile perché al momento in HASH JOIN, Spanner seleziona sempre la tabella a sinistra come build e la tabella a destra come sonda. Analogamente, per APPLY JOIN, Spanner sceglie la tabella a sinistra come esterna e la tabella a destra come interna. Scopri di più su questi tipi di join: join con hash e join applicato.

• Per le query fondamentali per il tuo carico di lavoro, specifica il metodo di join e l'ordine di join più performanti nelle istruzioni SQL per un rendimento più coerente.

Evita letture di grandi dimensioni all'interno di transazioni di lettura/scrittura

Le transazioni di lettura/scrittura consentono una sequenza di zero o più letture o query SQL e possono includere un insieme di mutazioni prima di una chiamata al commit. Per mantenere la coerenza dei dati, Spanner acquisisce i blocchi durante la lettura e la scrittura delle righe nelle tabelle e negli indici. Per maggiori informazioni sulla chiusura, consulta Vita di letture e scritture.

A causa del funzionamento dei blocchi in Spanner, l'esecuzione di una query SQL o di lettura che legge un numero elevato di righe (ad esempio SELECT * FROM Singers) significa che nessun'altra transazione può scrivere nelle righe che hai letto fino a quando la transazione non viene confermata o interrotta.

Inoltre, poiché la transazione elabora un numero elevato di righe, è probabile che impieghi più tempo di una transazione che legge un intervallo di righe molto più piccolo (ad esempio SELECT LastName FROM Singers WHERE SingerId = 7), il che esacerba ulteriormente il problema e riduce la velocità effettiva del sistema.

Pertanto, cerca di evitare letture di grandi dimensioni (ad esempio scansioni complete della tabella o operazioni di join massicce) nelle tue transazioni, a meno che tu non sia disposto ad accettare un throughput di scrittura inferiore.

In alcuni casi, il seguente pattern può produrre risultati migliori:

1. Esegui le letture di grandi dimensioni all'interno di una transazione di sola lettura. Le transazioni di sola lettura consentono una maggiore velocità effettiva aggregata perché non utilizzano i blocchi.
2. (Facoltativo) Esegui l'elaborazione richiesta sui dati appena letti.
3. Avvia una transazione di lettura/scrittura.
4. Verifica che le righe critiche non abbiano modificato i valori da quando hai eseguito la transazione di sola lettura nel passaggio 1.
   • Se le righe sono cambiate, esegui il rollback della transazione e ricomincia dal passaggio 1.
   • Se è tutto a posto, esegui il commit delle mutazioni.

Un modo per assicurarti di evitare letture di grandi dimensioni nelle transazioni di lettura/scrittura è esaminare i piani di esecuzione generati dalle query.

Utilizza ORDER BY per garantire l'ordinamento dei risultati SQL

Se prevedi un determinato ordinamento per i risultati di una query SELECT, inclusa esplicitamente la clausola ORDER BY. Ad esempio, se vuoi elencare tutti i cantanti in ordine di chiave primaria, utilizza questa query:

SELECT * FROM Singers
ORDER BY SingerId;

Spanner garantisce l'ordinamento dei risultati solo se la clausola ORDER BY è presente nella query. In altre parole, considera questa query senza ORDER BY:

SELECT * FROM Singers;

Spanner non garantisce che i risultati di questa query siano in ordine di chiave primaria. Inoltre, l'ordinamento dei risultati può cambiare in qualsiasi momento e non è garantito che sia coerente da un'invocazione all'altra. Se una query contiene una clausola ORDER BY e Spanner utilizza un indice che fornisce l'ordine richiesto, Spanner non ordina esplicitamente i dati. Pertanto, non preoccuparti dell'impatto sul rendimento dell'inclusione di questa clausola. Puoi controllare se un'operazione di ordinamento esplicita è inclusa nell'esecuzione esaminando il piano di query.

Utilizza STARTS_WITH anziché LIKE

Poiché Spanner non valuta i pattern LIKE con parametri fino al momento dell'esecuzione, deve leggere tutte le righe e valutarle in base all'espressione LIKE per filtrare le righe che non corrispondono.

Quando un pattern LIKE ha il formato foo% (ad esempio, inizia con una stringa fissa e termina con una singola percentuale di caratteri jolly) e la colonna è indicizzata, utilizza STARTS_WITH anziché LIKE. Questa opzione consente a Spanner di ottimizzare in modo più efficace il piano di esecuzione delle query.

Sconsigliato:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

Nota: questo esempio presuppone che @like_clause sia associato a 'Love%'.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE $1;

Nota: questo esempio presuppone che $2 sia associato a 'Love%'.

Consigliato:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);

Nota: questo esempio presuppone che @prefix sia associato a 'Love'. Questa query è più efficiente della query precedente. Esegue più velocemente se è definito un indice su Albums.AlbumTitle.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, $2);

Nota: questo esempio presuppone che $1 sia associato a 'Love'. Questa query è più efficiente della query precedente. Esegue più velocemente se è definito un indice su Albums.AlbumTitle.

Utilizzare i timestamp di commit

Se la tua applicazione deve eseguire query sui dati scritti dopo un determinato momento, aggiungi colonne di timestamp dei commit alle tabelle pertinenti. I timestamp di commit consentono un'ottimizzazione di Spanner che può ridurre le operazioni di I/O delle query le cui clausole WHERE limitano i risultati alle righe scritte più di recente rispetto a un'ora specifica.

Scopri di più su questa ottimizzazione con database in dialetto GoogleSQL o con database in dialetto PostgreSQL.