Best practice di SQL

Come descritto nella sezione 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 Cloud Spanner a trovare piani di esecuzione efficienti.

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

SQL standard di Google

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, fai riferimento alla sintassi della dichiarazione, alla sezione Funzioni e operatori e alla struttura e sintassi letterale.

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 ulteriori informazioni, consulta Il linguaggio PostgreSQL in Cloud Spanner.

Utilizza i parametri di ricerca per velocizzare le query eseguite di frequente

Le query con parametri sono una tecnica di esecuzione delle query che separa una stringa di query dai valori dei parametri di ricerca. Ad esempio, supponiamo che la tua applicazione debba recuperare cantanti che hanno pubblicato album con determinati titoli in un dato anno. Potresti scrivere un'istruzione SQL simile all'esempio seguente per recuperare tutti gli album intitolati "Love" pubblicati dal 2017:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

In un'altra query, potresti cambiare il valore del titolo dell'album in "Peace":

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Se l'applicazione deve eseguire molte query simili a questa, in cui cambia solo un valore letterale nelle query successive, utilizza un segnaposto parametro per quel valore. La query parametrica risultante può essere memorizzata nella cache e riutilizzata, riducendo i costi di compilazione.

Ad esempio, la seguente query riscritta sostituisce Love con un parametro denominato title:

SQL standard di Google

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Un riferimento ai parametri nella query utilizza il carattere @ seguito dal nome del parametro, che può contenere qualsiasi combinazione di lettere, numeri e trattini bassi. Specifica il parametro di ricerca e il valore da associare nel campo params dell'API richiesta ExecuteSQL o ExecuteStreamingSQL. Scopri di più sulla sintassi dei parametri di ricerca in Struttura strutturale e sintassi SQL.

PostgreSQL

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = $1 AND a.ReleaseDate >= '2017-01-01'

Un riferimento ai parametri nella query utilizza il carattere $ seguito da un numero che rappresenta il parametro di posizione nell'istruzione.

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.

Per riassumere, i parametri di ricerca supportano l'esecuzione della query nei seguenti modi:

  • Piani pre-ottimizzati: le query che utilizzano i parametri possono essere eseguite più rapidamente a ogni chiamata perché la parametrizzazione rende più semplice per Cloud Spanner memorizzare nella cache il piano di esecuzione.
  • Composizione semplificata delle query: non è necessario eseguire l'escape dei valori delle stringhe quando li fornisci nei parametri di ricerca. I parametri di ricerca riducono anche il rischio di errori di sintassi.
  • Sicurezza: i parametri di ricerca aumentano la sicurezza delle query proteggendoti da vari attacchi di tipo SQL injection. Questa protezione è particolarmente importante per le query che crei a partire dall'input utente.

Comprendere come Cloud Spanner esegue le query

Cloud Spanner consente di eseguire query sui database utilizzando istruzioni SQL dichiarative che specificano i dati da recuperare. Se vuoi comprendere anche in che modo Cloud Spanner ottiene i risultati, utilizza i piani di esecuzione delle query. Un piano di esecuzione delle 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 ottimizzare la query.

Puoi recuperare i piani di esecuzione della query tramite la console o le librerie client.

Per ottenere un piano di query utilizzando la console:

  1. Apri la pagina delle istanze Cloud Spanner.

    Vai alle istanze Cloud Spanner

  2. Seleziona i nomi dell'istanza Cloud Spanner e il database per cui vuoi eseguire la query.

  3. Fai clic su Query 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
    . Nella Console è visualizzato un piano di esecuzione visiva per la tua query.

    Screenshot del piano di esecuzione visiva in Cloud Console

Per ulteriori informazioni sui piani visivi, consulta la sezione Ottimizzazione di una query utilizzando il visualizzatore del piano query.

Per il riferimento completo del piano di query, consulta Piani di esecuzione delle query.

Utilizzare indici secondari per velocizzare le query comuni

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

Ad esempio, supponiamo che tu voglia 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 restituisce i risultati previsti, ma potrebbe essere necessario molto tempo per restituirli. La tempistica dipenderà dal numero di righe nella tabella Singers e da quante soddisfano il predicato WHERE s.LastName = 'Smith'. Se non esiste un indice secondario contenente 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 è denominata scansione della tabella completa. Una scansione 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 della tabella indicizzata LastName e la colonna della chiave primaria SingerId, Cloud Spanner può recuperare tutti i dati dalla tabella dell'indice molto più piccola anziché analizzare l'intera tabella Singers.

In questo scenario, Cloud Spanner utilizza automaticamente l'indice secondario SingersByLastName durante l'esecuzione della query (se sono trascorsi tre giorni dalla creazione del database; consulta la nota sui nuovi database). Tuttavia, è meglio chiedere esplicitamente a Cloud Spanner di utilizzare quell'indice specificando un'istruzione di indicizzazione nella clausola FROM:

SQL standard di Google

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

Ora supponiamo che tu voglia recuperare il nome del cantante oltre all'ID. Anche se la colonna FirstName non è contenuta nell'indice, devi comunque specificare l'istruzione di indicizzazione come prima:

SQL standard di Google

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 trarre un vantaggio in termini di prestazioni utilizzando l'indice perché Cloud 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, quindi esegue una ricerca dalla tabella di base Singers per recuperare il primo nome solo per quel sottoinsieme di righe.

Se non vuoi che Cloud Spanner recuperi alcuna riga dalla tabella di base, puoi archiviare una copia della colonna FirstName nell'indice stesso:

SQL standard di Google

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 SQL standard di Google) o di una clausola INCLUDE (per il dialetto PostgreSQL) come questa comporta costi aggiuntivi, ma offre i seguenti vantaggi per le query e le chiamate di lettura mediante l'indice:

  • Le query SQL che utilizzano l'indice e selezionano le colonne memorizzate nella clausola STORING o INCLUDE non richiedono l'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 illustrano il modo in cui gli indici secondari possono velocizzare le query quando è possibile identificare rapidamente le righe scelte dalla clausola WHERE di una query 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 di data di uscita e in ordine decrescente. Potresti scrivere una query SQL nel seguente modo:

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. Per velocizzare l'esecuzione della query, definisci questo indice secondario:

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

Quindi, riscrivi la query per utilizzare l'indice secondario:

SQL standard di Google

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 indicizzazione soddisfa entrambi i seguenti criteri:

  • L'elenco delle colonne nella clausola ORDER BY è un prefisso dell'elenco delle chiavi indice.
  • L'indice copre tutte le colonne della tabella utilizzate dalla query.

Poiché entrambe queste condizioni sono soddisfatte, il piano di query risultante rimuove il passaggio di ordinamento e viene eseguito più velocemente.

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

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

Scrivi query efficaci per la ricerca di chiavi di intervalli

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

Le seguenti best practice ti aiuteranno a scrivere query efficienti quando recuperi i dati in base a una serie di chiavi:

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

    Ad esempio, se l'elenco delle chiavi è {1, 5, 1000}, scrivi la query in questo modo:

    SQL standard di Google

    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 riunisce una matrice di input in righe di elementi.

    • Il parametro di ricerca, @KeyList per SQL standard di Google e $1 per PostgreSQL, può velocizzare la query come descritto nella best practice precedente.

  • Se l'elenco di 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 delle chiavi è {1,2,3,4,5}, crea la query come segue:

    SQL standard di Google

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

    PostgreSQL

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

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

Scrivi query efficienti per le unioni

Le operazioni di join possono essere costose perché possono aumentare notevolmente il numero di righe di cui la tua query deve eseguire la scansione, il che si traduce in query più lente. Oltre alle tecniche che sei abituato a utilizzare in altri database relazionali per ottimizzare le query di join, ecco alcune best practice per ottenere un join più efficiente quando utilizzi Cloud Spanner SQL:

  • Se possibile, unisci i dati nelle tabelle con interfoliazione per chiave primaria. Ad esempio:

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

    È assicurata l'archiviazione fisica delle righe nella tabella con interfoliazione Albums nelle stesse suddivisioni della riga principale in Singers, come discusso in Schema e modello dei dati. Di conseguenza, le unioni possono essere completate localmente senza inviare molti dati nella rete.

  • Utilizza l'istruzione di join se vuoi forzare l'ordine del join. Ad esempio:

    SQL standard di Google

    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 Cloud 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 Cloud Spanner. Tuttavia, potresti utilizzare questa istruzione di unione se nel piano di query noti che Cloud Spanner ha modificato l'ordine di unione e ha generato risultati indesiderati, ad esempio risultati intermedi più ampi, oppure ha perso opportunità di ricercare righe.

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

    SQL standard di Google

    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 stai utilizzando una HASH JOIN o una APPLY JOIN e se hai una causale WHERE altamente selettiva su un lato dei tuoi JOIN, inserisci la tabella che produce il minor numero di righe come prima tabella nella clausola FROM dell'unione. Questa struttura è utile perché, al momento in HASH JOIN, Cloud Spanner sceglie sempre il tavolino sinistro come build e la tabella laterale destra come probe. Analogamente, per APPLY JOIN, Cloud Spanner sceglie la tabella laterale sinistra come esterna e la tabella laterale destra come interna. Scopri di più su questi tipi di unione: Hash join e Apply join.

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

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 set di mutazioni, prima di una chiamata all'impegno. Per mantenere la coerenza dei tuoi dati, Cloud Spanner acquisisce i blocchi durante la lettura e la scrittura di righe nelle tabelle e negli indici. Per ulteriori informazioni sul blocco, consulta l'articolo vita di letture e scritture.

A causa del funzionamento del blocco in Cloud Spanner, l'esecuzione di una query di lettura o SQL che legge un numero elevato di righe (ad esempio SELECT * FROM Singers) significa che non è possibile scrivere altre transazioni nelle righe lette finché la transazione non viene eseguita o interrotta.

Inoltre, poiché la transazione sta elaborando 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 aggrava ulteriormente il problema e riduce la velocità effettiva di sistema.

Perciò, cerca di evitare letture di grandi dimensioni (ad esempio, scansioni complete di tabelle o operazioni di join di massa) nelle transazioni, a meno che tu non sia disposto ad accettare una velocità effettiva di scrittura inferiore.

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

  1. Esegui 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 utilizzano blocchi.
  2. (Facoltativo) Esegui eventuali operazioni di elaborazione sui dati appena letti.
  3. Avvia una transazione di lettura/scrittura.
  4. Verifica che le righe critiche non abbiano modificato valori poiché 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 non ci sono problemi, esegui il commit delle mutazioni.

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

Utilizza ORDER BY per garantire l'ordinamento dei risultati SQL

Se ti aspetti un determinato ordine per i risultati di una query SELECT, includi esplicitamente la clausola ORDER BY. Ad esempio, se vuoi elencare tutti i cantanti nell'ordine della chiave primaria, utilizza questa query:

SELECT * FROM Singers
ORDER BY SingerId;

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

SELECT * FROM Singers;

Cloud Spanner non garantisce che i risultati di questa query siano in ordine chiave principale. Inoltre, l'ordinamento dei risultati può cambiare in qualsiasi momento e non è garantito che siano coerenti da una chiamata all'altra.

Utilizza STARTS_WITH invece di like per accelerare le query SQL parametrizzate

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

Quando un pattern LIKE cerca corrispondenze all'inizio di un valore e la colonna viene indicizzata, utilizza STARTS_WITH invece di LIKE. Questa opzione consente a Cloud Spanner di ottimizzare più efficacemente il piano di esecuzione delle query.

Sconsigliato:

SQL standard di Google

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

PostgreSQL

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

Consigliato:

SQL standard di Google

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

PostgreSQL

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

Usa i timestamp di commit per eseguire query sui dati recenti

Se la tua applicazione deve eseguire query su dati scritti dopo un determinato periodo di tempo, aggiungi colonne relative al 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 momento specifico.

Scopri di più su questa ottimizzazione con i database dialetto SQL standard di Google o con i database dialetto SQL PostgreSQL.