Best practice di SQL

Come descritto nei 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 il seguente schema di esempio:

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 SQL completo, consulta le sezioni Sintassi dell'istruzione, Funzioni e operatori e Struttura e sintassi lessicali.

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.

Utilizza parametri di ricerca

Spanner supporta parametri di ricerca per aumentare le prestazioni e impedire l'SQL injection quando le query vengono create con l'input utente. Puoi utilizzare parametri di ricerca come sostituti di espressioni arbitrarie, ma non come sostituti di identificatori, nomi di colonna, 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ù di una volta in una singola istruzione SQL.

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

• Piani preottimizzati: le query che utilizzano parametri possono essere eseguite più velocemente a ogni chiamata poiché la parametrizzazione consente a Spanner di memorizzare nella cache il piano di esecuzione.
• Composizione delle query semplificata: non è necessario eseguire l'escape dei valori stringa quando li fornisci nei parametri di ricerca. I parametri di query riducono inoltre il rischio di errori di sintassi.
• Sicurezza: i parametri di query rendono le tue query più sicure proteggendoti da vari attacchi di SQL injection. Questa protezione è particolarmente importante per le query create dallinput utente.

Scopri in che modo Spanner esegue le query

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

Spanner ti consente di eseguire query sui database utilizzando istruzioni SQL dichiarative che specificano i dati da recuperare. Se vuoi capire in che modo Spanner ottiene i risultati, esamina il piano di esecuzione per la query. Un piano di esecuzione della query mostra il costo di calcolo associato a ogni passaggio della query. Utilizzando questi costi, puoi eseguire il debug dei problemi di prestazioni delle query e ottimizzarle. Per scoprire di più, consulta la sezione 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 per una query specifica utilizzando la console Google Cloud, segui questi passaggi:

1. Apri la pagina Istanze Spanner.

   Vai a Istanze Spanner

2. Seleziona i nomi dell'istanza Spanner e il database su cui vuoi eseguire una query.

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

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

5. Fai clic su Spiegazione
   . Nella console Google Cloud viene visualizzato un piano di esecuzione visiva per la query.

   

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

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

Utilizza 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 è usare Spanner Studio. L'utilizzo di un indice secondario in una query SQL ti consente di specificare come vuoi che Spanner ottenga i risultati. La specifica di un indice secondario può accelerare l'esecuzione della query.

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

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

Questa query restituirebbe i risultati previsti, ma potrebbe richiedere molto tempo. La tempistica dipende dal numero di righe nella tabella Singers e da quante soddisfano il predicato WHERE s.LastName = 'Smith'. Se non è presente alcun indice secondario che contiene la colonna LastName da cui leggere, il piano di query leggerà l'intera tabella Singers per trovare le righe corrispondenti al predicato. La lettura dell'intera tabella è chiamata scansione completa della tabella. L'analisi 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 nella colonna del cognome:

CREATE INDEX SingersByLastName ON Singers (LastName);

Poiché l'indice secondario SingersByLastName contiene la colonna LastName della tabella indicizzata e la colonna di chiave primaria SingerId, Spanner può recuperare tutti i dati dalla tabella degli indici molto più piccola anziché analizzare l'intera tabella Singers.

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 quell'indice specificando un'istruzione 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 è contenuta nell'indice, devi comunque specificare l'istruzione 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';

Puoi comunque ottenere un vantaggio in termini di prestazioni utilizzando l'indice perché Spanner non ha bisogno di 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, quindi esegue una ricerca dalla tabella di base Singers per recuperare il nome solo per quel sottoinsieme di righe.

Se vuoi che Spanner non debba recuperare alcuna riga 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 INCLUDE (per il dialetto PostgreSQL) come questa costa spazio di archiviazione aggiuntivo, ma offre i seguenti vantaggi:

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

Gli esempi precedenti mostrano come 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 rendimento è per determinate query che restituiscono risultati ordinati. Ad esempio, supponi di voler recuperare tutti i titoli degli album e le relative date di uscita, in ordine crescente e in ordine decrescente per data di rilascio e ora del titolo dell'album. Potresti 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 della 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 query e definizione dell'indice soddisfano 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 delle chiavi di indice.
• Per evitare di unire la tabella di base per recuperare eventuali colonne mancanti, assicurati che l'indice copra tutte le colonne della tabella utilizzata dalla query.

Anche se gli indici secondari possono velocizzare le query comuni, l'aggiunta di indici secondari può aggiungere latenza alle operazioni di commit, poiché ogni indice secondario richiede in genere il coinvolgimento di un nodo aggiuntivo in ogni commit. Per la maggior parte dei carichi di lavoro, è consentito avere alcuni indici secondari. Tuttavia, dovresti valutare se la latenza di lettura o scrittura ti interessa di più e valutare quali operazioni sono più fondamentali per il tuo carico di lavoro. Confronta il tuo carico di lavoro per assicurarti che funzioni come previsto.

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

Ottimizza scansioni

Alcune query di Spanner potrebbero trarre vantaggio dall'utilizzo di un metodo di elaborazione orientato al batch durante la scansione dei dati, anziché del metodo più comune di elaborazione orientato alle righe. L'elaborazione delle scansioni in batch è un modo più efficiente per elaborare contemporaneamente grandi volumi di dati e consente alle query di ridurre l'utilizzo e la latenza della CPU.

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 set di euristiche basate sul risultato di queste metriche per determinare la modalità di scansione ottimale. Se opportuno, Spanner passa a una modalità di elaborazione orientata al batch per migliorare le prestazioni e la velocità effettiva di scansione.

Casi d'uso comuni

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

• Scansioni di grandi dimensioni su dati aggiornati raramente.
• 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 traggono vantaggio dall'elaborazione orientata al batch. I seguenti tipi di query funzionano meglio con l'elaborazione della scansione orientata alle righe:

• Query di ricerca per punto: query che recuperano solo una riga.
• Query di scansione piccole: le scansioni delle tabelle analizzano solo poche righe, a meno che il numero di ricerche non sia elevato.
• Query che utilizzano LIMIT.
• Query che leggono i dati relativi ad alto tasso di abbandono: query in cui oltre il 10% circa dei dati letti viene aggiornata di frequente.
• Query con righe contenenti valori di grandi dimensioni: le righe con valori grandi sono quelle che contengono valori superiori a 32.000 byte (pre-compressione) in una singola colonna.

Come verificare il metodo di scansione utilizzato da una query

Per verificare se la query utilizza l'elaborazione orientata al batch e quella 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 query, scrivi la query.

7. Fai clic su Run (Esegui).

   Spanner esegue la query e mostra i risultati.

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

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

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

   Il riquadro mostra informazioni contestuali sulla scansione selezionata. Il metodo di scansione viene mostrato su questa scheda. Automatica indica che Spanner determina il metodo di analisi. Altri valori possibili includono Vectorized per l'elaborazione orientata al batch e Scalar per l'elaborazione orientata alle righe.

   

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, potrebbero esserci scenari in cui potrebbe essere opportuno applicare un tipo specifico di metodo di scansione.

Come applicare la scansione orientata al batch

Puoi applicare l'analisi orientata al batch a livello di tabella e di istruzione.

Per applicare il metodo di scansione orientato al batch a livello di tabella, utilizza un suggerimento per la 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 al batch a livello di istruzione, utilizza un hint dell'istruzione nella query:

GoogleSQL

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

PostgreSQL

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

Come disabilitare la scansione automatica e applicare la scansione orientata alle righe

Anche se sconsigliamo di disabilitare il metodo di scansione automatica impostato da Spanner, potresti decidere di disabilitarlo e utilizzare il metodo di scansione orientato alle righe per la risoluzione dei problemi, ad esempio per la diagnosi della latenza.

Per disabilitare 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 disabilitare il metodo di scansione automatica e applicare l'elaborazione delle righe a livello di istruzione, utilizza un hint delle istruzioni nella query:

GoogleSQL

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

PostgreSQL

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

Ottimizza le ricerche chiave dell'intervallo

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

Le seguenti best practice ti aiutano a scrivere query efficienti durante il recupero dei dati tramite 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 in questo modo:

  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 di array UNNEST appiattisce un array di input in righe di elementi.

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

• Se l'elenco delle chiavi è adiacente e all'interno di 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}, crea la query come segue:

  GoogleSQL

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

  Nota: qui, @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: qui, $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 inferiori e superiori come nella query precedente, perché la query risultante analizzerà ogni valore compreso tra 1 e 1000.

Unioni di Optimize

Le operazioni di join possono essere costose perché possono aumentare notevolmente il numero di righe da scansionare, il che comporta query più lente. Oltre alle tecniche che sei abituato a usare in altri database relazionali per ottimizzare le query di join, ecco alcune best practice per creare un JOIN più efficiente quando utilizzi SQL Spanner:

• Se possibile, unisci i dati in tabelle con interleaving 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 nella tabella con interleaving Albums devono essere archiviate fisicamente nelle stesse suddivisioni della riga padre in Singers, come discusso in Schema e modello dei dati. Di conseguenza, i join possono essere completati localmente senza inviare molti dati attraverso la rete.

• Utilizza l'istruzione join se vuoi forzare l'ordine del join. 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%';
  

  L'istruzione di 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 noti nel piano di query che Spanner ha modificato l'ordine di join e ha causato conseguenze indesiderate, ad esempio risultati intermedi più grandi o ha mancate opportunità per la ricerca di righe.

• Utilizza un'istruzione di join per scegliere un'implementazione di join. Quando esegui query SQL per eseguire query su più tabelle, Spanner utilizza automaticamente un metodo di join che potrebbe rendere la query più efficiente. Tuttavia, Google ti consiglia di eseguire dei test con algoritmi di join diversi. Scegliere l'algoritmo di join giusto può migliorare latenza, consumo di memoria o entrambi. Questa query mostra la sintassi per utilizzare 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 HASH JOIN o APPLY JOIN e hai una clausola WHERE molto selettiva su un lato di JOIN, inserisci la tabella che produce il numero più basso di righe come prima tabella nella clausola FROM del join. Questa struttura è utile perché, attualmente in HASH JOIN, Spanner sceglie sempre la tabella laterale sinistra come build e la destra come probe. Allo stesso modo, per APPLY JOIN, Spanner sceglie la tabella laterale sinistra come esterna e la destra come interna. Scopri di più su questi tipi di join: Hash join e Apply join.

• Per le query critiche per il tuo carico di lavoro, specifica il metodo di join più performante e l'ordine di join nelle istruzioni SQL per prestazioni più coerenti.

Evita operazioni di lettura/scrittura di grandi dimensioni all'interno delle transazioni di lettura/scrittura

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

Per via del funzionamento del blocco in Spanner, l'esecuzione di una query di lettura o SQL che legge un numero elevato di righe (ad esempio SELECT * FROM Singers) significa che nessun'altra transazione potrà scrivere nelle righe che hai letto finché non viene eseguito il commit o l'interruzione della transazione.

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

Quindi, cerca di evitare letture di grandi dimensioni (ad esempio scansioni complete delle tabelle o operazioni di join di massa) nelle transazioni, a meno che tu non voglia accettare una velocità effettiva di scrittura più bassa.

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 velocità effettiva aggregata più elevata perché non usano blocchi.
2. (Facoltativo) Esegui le elaborazioni richieste sui dati che hai appena letto.
3. Avvia una transazione di lettura/scrittura.
4. Verifica che i valori delle righe critiche non siano stati modificati 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, commetti le tue mutazioni.

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

Utilizza ORDER BY per garantire l'ordine dei risultati SQL

Se ti aspetti un ordine specifico per i risultati di una query SELECT, includi 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 nella query è presente la clausola ORDER BY. In altre parole, considera questa query senza ORDER BY:

SELECT * FROM Singers;

Spanner non garantisce che i risultati di questa query saranno in ordine di chiave primaria. Inoltre, l'ordine dei risultati può cambiare in qualsiasi momento e non è garantito che sia coerente dalla chiamata alla chiamata. Se una query ha 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 sulle prestazioni derivante dall'inclusione di questa clausola. Puoi verificare se un'operazione di ordinamento esplicita è inclusa nell'esecuzione, esaminando il piano di query.

Usa STARTS_WITH invece di LIKE

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

Quando un pattern LIKE ha la forma foo% (ad esempio, inizia con una stringa fissa e termina con una singola percentuale di caratteri jolly) e la colonna viene 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: in questo esempio si presuppone che @like_clause sia associato a 'Love%'.

PostgreSQL

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

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

Consigliato:

GoogleSQL

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

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

PostgreSQL

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

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

Usa timestamp di commit

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

Scopri di più su questa ottimizzazione con i database di dialetti SQL o con i database di dialetti PostgreSQL.