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 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 gli articoli su sintassi delle istruzioni, funzioni e operatori e struttura e sintassi lexica.

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 prevenire l'SQL injection quando le query vengono create usando l'input utente. Puoi utilizzare parametri di ricerca come sostituti di espressioni arbitrarie, ma non di sostituti di identificatori, nomi di colonna, nomi di tabella o altre parti della query.

I parametri possono apparire ovunque sia previsto un valore letterale. Lo stesso nome parametro può essere utilizzato più di una volta in una singola istruzione SQL.

Per riassumere, 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 perché la parametrizzazione semplifica la memorizzazione nella cache del piano di esecuzione da parte di Spanner.
  • Composizione semplificata delle query: 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 query più sicure proteggendoti da vari attacchi di SQL injection. Questa protezione è particolarmente importante per le query create dallinput utente.

Scopri come Spanner esegue le query

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 visualizza 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 saperne 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 per una query specifica utilizzando la console Google Cloud, segui questi passaggi:

  1. Apri la pagina Istanze Spanner.

    Vai alle istanze Spanner

  2. Seleziona i nomi dell'istanza Spanner e del database su cui eseguire la 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
    . La console Google Cloud mostra un piano di esecuzione visiva per la query.

    Screenshot del piano di esecuzione visiva nella console Cloud

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 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 è utilizzare Spanner Studio. L'uso di un indice secondario in una query SQL consente di specificare in che modo vuoi che Spanner ottenga i risultati. La specifica di un indice secondario può velocizzare l'esecuzione delle 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 restituirà i risultati previsti, ma la restituzione potrebbe richiedere molto tempo. Il tempo dipende dal numero di righe nella tabella Singers e da quante soddisfano il predicato WHERE s.LastName = 'Smith'. Se non esiste un indice secondario che contenga la colonna LastName da cui leggere, il piano di query leggerà l'intera tabella Singers per trovare le righe che corrispondono al predicato. La lettura dell'intera tabella è chiamata scansione completa della tabella. 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 di chiave primaria SingerId, Spanner può recuperare tutti i dati dalla tabella dell'indice molto più piccola anziché analizzare la tabella Singers completa.

In questo scenario, Spanner utilizza automaticamente l'indice secondario SingersByLastName durante l'esecuzione della query (purché siano trascorsi tre giorni dalla creazione del database; consulta Una nota sui nuovi database). Tuttavia, è meglio comunicare esplicitamente a Spanner di utilizzare l'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';

Ora supponi di voler recuperare anche il nome del cantante oltre al ID. Anche se la colonna FirstName non è contenuta nell'indice, devi comunque specificare l'istruzione dell'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'uso dell'indice garantisce 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, quindi esegue una ricerca dalla tabella di base Singers per recuperare il primo nome solo per quel sottoinsieme di righe.

Se vuoi che Spanner non debba recuperare nessuna 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 di una clausola 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 prestazioni è 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 rispetto alla data di rilascio. 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 delle query definendo questo indice secondario:

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

Quindi, riscrivi la query per 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 soddisfano entrambi i seguenti criteri:

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

Sebbene gli indici secondari possano velocizzare le query comuni, l'aggiunta di indici secondari può aumentare la latenza delle operazioni di commit, poiché ogni indice secondario in genere richiede l'utilizzo di un nodo aggiuntivo in ogni commit. Per la maggior parte dei carichi di lavoro, è possibile avere alcuni indici secondari. Tuttavia, dovresti valutare se ti interessa di più la latenza di lettura o scrittura e quali operazioni sono più critiche per il tuo carico di lavoro. Confronta il tuo carico di lavoro per assicurarti che le prestazioni siano quelle previste.

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

Ottimizza le scansioni

Per alcune query Spanner potrebbe essere utile utilizzare un metodo di elaborazione orientata al 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 grandi volumi di dati contemporaneamente 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 riga. Durante questo periodo, Spanner raccoglie diverse metriche di runtime. Spanner applica quindi un insieme di euristiche basate sul risultato di queste metriche per determinare la modalità di scansione ottimale. Quando opportuno, Spanner passa a una modalità di elaborazione orientata al batch per migliorare la velocità effettiva e le prestazioni della scansione.

Casi d'uso comuni

Le query con le seguenti caratteristiche in genere traggono vantaggio dall'uso dell'elaborazione orientata ai batch:

  • Scansioni di grandi dimensioni su dati aggiornati raramente.
  • Scansioni con predicati su colonne a larghezza fissa.
  • Scansioni con conteggi lunghi. (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 hanno prestazioni migliori con l'elaborazione della scansione orientata alle righe:

  • Query di ricerca point: query che recuperano solo una riga.
  • Query di scansione ridotte: analisi 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 oltre il 10% circa dei dati letti viene aggiornato di frequente.
  • Query con righe contenenti valori grandi: le righe di valori grandi sono quelle che contengono valori superiori a 32.000 byte (precompressione) 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, l'elaborazione orientata alle righe o 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 di 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 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 dell'iteratore Scansione tabella per aprire un riquadro informativo.

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

    Screenshot di una scheda di scansione di una tabella che mostra il metodo di scansione Automatico

Come applicare in modo forzato il metodo di scansione utilizzato da una query

Per ottimizzare le prestazioni delle query, Spanner sceglie il metodo di analisi ottimale per la query. Ti consigliamo di utilizzare questo metodo di scansione predefinito. Tuttavia, potrebbero verificarsi casi in cui potresti voler applicare un tipo specifico di metodo di scansione.

Come applicare la scansione orientata al batch

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

Per applicare il metodo di scansione orientata al batch a livello di tabella, utilizza un hint di 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 orientata al batch a livello di istruzione, utilizza un hint istruzione 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 orientata alle righe

Anche se non è consigliabile disabilitare il metodo di analisi automatica impostato da Spanner, potresti decidere di disabilitarlo e utilizzare il metodo di analisi orientata alle righe per la risoluzione dei problemi, ad esempio per diagnosticare la latenza.

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

GoogleSQL 

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

PostgreSQL 

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

Ottimizza le ricerche di chiavi per l'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 un intervallo di chiavi:

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

    Ad esempio, se il tuo 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 UNNEST consente di suddividere un array di input in righe di elementi.

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

  • Se l'elenco delle chiavi è adiacente ed entro 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

    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 di chiavi è {1, 5, 1000}, non specificare i limiti inferiori e superiori come nella query precedente perché la query risultante analizza tutti i valori compresi tra 1 e 1000.

join di Optimize

Le operazioni di join possono essere costose perché possono aumentare significativamente il numero di righe di cui la query deve essere analizzata, il che si traduce in query più lente. Oltre alle tecniche abitualmente utilizzate in altri database relazionali per ottimizzare le query di join, ecco alcune best practice per un JOIN più efficiente quando utilizzi Spanner SQL:

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

    È garantito che le righe nella tabella con interleaving Albums siano archiviate fisicamente nelle stesse suddivisioni della riga padre in Singers, come discusso in Schema e modello dei dati. Pertanto, i join possono essere completati localmente senza inviare molti dati attraverso la rete.

  • Utilizza l'istruzione di 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 istruzione 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 perso opportunità di ricerca delle 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 probabilmente renderà la query più efficiente. Tuttavia, Google ti consiglia di testare diversi algoritmi di join. Scegliere l'algoritmo di join giusto può migliorare la latenza, il consumo di memoria o entrambi. Questa query dimostra 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 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 tabella di destra come probe. Allo stesso modo, per APPLY JOIN, Spanner sceglie la tabella laterale sinistra come esterna e la tabella laterale destra come interna. Scopri di più su questi tipi di join: Hash join e Applica join.

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

Evitare letture di grandi dimensioni all'interno delle 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 blocchi durante la lettura e la scrittura di righe nelle tabelle e negli indici. Per ulteriori informazioni sul blocco, consulta Ciclo di vita delle letture e delle scritture.

A causa del funzionamento del blocco 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 finché la transazione non viene confermata 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 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 di tabelle o operazioni di join massiccio) nelle transazioni, a meno che tu non voglia 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ù alta perché non utilizzano blocchi.
  2. (Facoltativo) Esegui le eventuali elaborazioni richieste per i dati che hai appena letto.
  3. Avviare una transazione di lettura/scrittura.
  4. Verifica che le righe critiche non abbiano modificato i valori dopo che hai eseguito la transazione di sola lettura nel passaggio 1.
    • Se le righe sono state modificate, esegui il rollback della transazione e ricomincia dal passaggio 1.
    • Se tutto sembra a posto, commetti le tue mutazioni.

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

Usa ORDER BY per assicurarti che l'ordine dei risultati SQL

Se prevedi un determinato ordinamento 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 il valore ORDER BY:

SELECT * FROM Singers;

Spanner non garantisce che i risultati di questa query saranno in ordine della chiave primaria. Inoltre, l'ordine dei risultati può cambiare in qualsiasi momento e non è garantito che sia coerente tra le chiamate. Se una query ha una clausola ORDER BY e Spanner utilizza un indice che fornisce l'ordine richiesto, Spanner non ordina in modo esplicito 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.

Utilizza STARTS_WITH al posto 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 il formato foo% (ad esempio, inizia con una stringa fissa e termina con una singola percentuale di carattere 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;

PostgreSQL 

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

Consigliato:

GoogleSQL 

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

Utilizza timestamp di commit

Se l'applicazione deve eseguire query su dati scritti dopo un determinato periodo di tempo, aggiungi colonne del 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 GoogleSQL o con i database di dialetti PostgreSQL.