Best practice SQL

Dicembre 26, 2021Articles

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

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

CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX),) 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, fare riferimento asintassi di dichiarazione,funzioni e operatori, struttura lessicale e sintassi.

Utilizzare i parametri di query per accelerare le query eseguite di frequente

Le query parametrizzate sono una tecnica di esecuzione di query che separa una querystring dai valori dei parametri di query. Ad esempio, supponiamo che la tua applicazione necessiti di recuperare cantanti che hanno pubblicato album con determinati titoli in un determinato anno. Potresti scrivere un’istruzione SQL come il seguente esempio per recuperare tutti gli album intitolati “Love” che sono stati rilasciati in 2017:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

In un’altra query, è possibile modificare il valore del titolo dell’album in”Pace”:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Se l’applicazione deve eseguire molte query simili a questa,in cui solo un valore letterale cambia nelle query successive, è necessario utilizzare aparameter placeholder per quel valore. La query parametrica risultante può esserechiamato e riutilizzato, che riduce i costi di compilazione.

Ad esempio, la query riscritta di seguito sostituisce Love con un parametro denominatotitle:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Note sull’utilizzo dei parametri di query:

Un riferimento di parametro nella query utilizza il carattere @ seguito dal nome del parametro, che può contenere qualsiasi combinazione di lettere, numeri e underscores.
I parametri possono apparire ovunque sia previsto un valore letterale.
Lo stesso nome di parametro può essere utilizzato più di una volta in un singolo SQLstatement.
Specificare il parametro di query e il valore a cui associarlo nel campo paramsdell’API di richiestaExecuteSQL o ExecuteStreamingSQL.
Ulteriori informazioni sulla struttura lessicale e la sintassi dei parametri di query syntaxinSQL.

In sintesi, i parametri di query beneficiano dell’esecuzione delle query nei seguenti modi:

Piani pre-ottimizzati: le query che utilizzano i parametri possono essere eseguite più velocemente una chiamata ad ogni perché la parametrizzazione rende più facile forCloud Spanner per memorizzare nella cache il piano di esecuzione.
Composizione query semplificata: non è necessario sfuggire ai valori di stringa quando vengono forniti nei parametri di query. I parametri di query riducono anche il rischio dierrori syntax.
Sicurezza: i parametri delle query rendono le query più sicure proteggendoda vari attacchi SQL injection. Questa protezione è particolarmente importanteper le query che si costruiscono dall’input dell’utente.

Capire come Cloud Spanner esegue query

Cloud Spanner consente di interrogare i database utilizzando dichiarazioni SQL dichiarative che specificano quali dati si desidera recuperare. Se vuoi capire anche comecloud Spanner ottiene i risultati, dovresti usare i piani di esecuzione delle query. Aquery execution plan visualizza il costo computazionale associato ad ogni stepof la query. Utilizzando tali costi, è possibile eseguire il debug di problemi di prestazioni della query eottimizzare la query.

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

Per ottenere un piano di query utilizzando la console Cloud:

Aprire la pagina Istanze Cloud Spanner.

Vai alle istanze Cloud Spanner
Fare clic sul nome dell’istanza Cloud Spanner e del database che si desidera interrogare.
Fare clic su Query.
Digitare la query nel campo di testo, quindi fare clic su Esegui query.
Fare clic su Spiegazione.
La Console Cloud visualizza un piano di visualexecution per la query.

Per ulteriori informazioni sui piani visivi, vedere Ottimizzazione di una query utilizzando il visualizzatore piano query.

Per il riferimento completo al piano di query, fare riferimento ai piani di esecuzione di Query.

Utilizzare indici secondari per velocizzare le query comuni

Come altri database relazionali, Cloud Spanner offre indici secondari, che è possibile utilizzare per recuperare i dati utilizzando un’istruzione SQL o utilizzando l’interfaccia di lettura di Cloud Spanner. Il modo più comune per recuperare i dati da anindex è utilizzare l’interfaccia di query SQL. Utilizzo di un indice secondario in una query SQL consente di specificare come si desidera che Cloud Spanner ottenga i risultati.Specificare un indice secondario può accelerare l’esecuzione della query.

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

SELECT s.SingerIdFROM Singers AS sWHERE s.LastName = 'Smith';

Questa query restituirebbe i risultati che ci si aspetta, ma potrebbe richiedere molto tempo per restituire i risultati. I tempi dipenderebbero dal numero di rigenella tabella Singerse da quanti soddisfano il predicatoWHERE s.LastName = 'Smith'. Se non esiste un indice secondario che contiene la colonna LastName da leggere, il piano di query leggerà la tabella entire Singers per trovare le righe che corrispondono al predicato. La lettura di entiretable è chiamata scansione completa della tabella e una scansione completa della tabella è un modo costoso per ottenere i risultati se la tabella contiene solo una piccola percentuale diSingers con quel cognome.

È possibile migliorare le prestazioni di questa query definendo un indice secondario sulla colonna cognome:

CREATE INDEX SingersByLastName on Singers (LastName);

Poiché l’indice secondario SingersByLastName contiene la tablecolumn indicizzata LastName e la colonna della chiave primaria SingerId, Cloud Spanner può recuperare tutti i dati dalla tabella dell’indice molto più piccola invece di scansionare la tabella completa Singers.

In questo scenario, Cloud Spanner probabilmente utilizzerà automaticamente secondaryindex SingersByLastName durante l’esecuzione della query. Tuttavia, è meglio dire esplicitamente a Cloud Spanner di utilizzare quell’indice specificando una direttiva di indice nella clausola FROM :

SELECT s.SingerIdFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

Ora supponiamo che anche tu volessi prendere il nome del cantante oltre a theID. Anche se la colonna FirstName non è contenuta nell’indice, è necessario specificare la direttiva index come prima:

SELECT s.SingerId, s.FirstNameFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

Si ottiene comunque un vantaggio in termini di prestazioni dall’utilizzo dell’indice perché Cloud Spannerdoesn non è necessario eseguire una scansione completa della tabella durante l’esecuzione del piano di query. Invece, itselects il sottoinsieme di righe che soddisfano il predicato dall’indice SingersByLastName, quindi esegue una ricerca dalla tabella di base Singers per recuperare il primo nomeper solo quel sottoinsieme di righe.

Se si vuole evitare di Cloud Chiave da dover recuperare tutte le righe dalla basetable a tutti, è possibile scegliere di memorizzare una copia di FirstName colonna theindex stesso:

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

Utilizzando un STORING clausola del genere i costi di archiviazione aggiuntivo, ma in quanto offre i seguenti vantaggi per le query e leggere le chiamate utilizzando l’indice:

Query SQL che utilizza l’indice e selezionare le colonne memorizzati in STORINGclausola di non necessitare di un ulteriore join alla tabella di base.
Leggi le chiamate che utilizzano l’indice possono leggere le colonne memorizzate nella clausola STORING.

Gli esempi precedenti illustrano come gli indici secondari possono accelerare le query quando le righe scelte dalla clausola WHERE di una query possono essere rapidamente identificate utilizzando l’indice secondario. Un altro scenario in cui gli indici secondari possono offrire vantaggi di performance è per determinate query che restituiscono risultati ordinati. Per esempio, supponiamo che tu voglia recuperare tutti i titoli degli album e le loro date di rilascio e restituirli in ordine crescente di data di rilascio e in ordine decrescente per titolo dell’album. Potresti scrivere una query SQL come questa:

SELECT a.AlbumTitle, a.ReleaseDateFROM Albums AS aORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Senza un indice secondario, questa query richiede un sortingstep potenzialmente costoso nel piano di esecuzione. Si potrebbe accelerare l’esecuzione di query mediante la definizione di thissecondary indice:

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

Quindi riscrivere la query per utilizzare l’indice secondario:

SELECT a.AlbumTitle, a.ReleaseDateFROM [email protected]{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS aORDER BY a.ReleaseDate, a.AlbumTitle DESC;

si noti che questa query e definizione dell’indice soddisfare i seguenti criteri:

L’elenco delle colonne nella ORDER BY clausola è un prefisso della chiave di indice elenco.
Tutte le colonne della tabella utilizzata nella query sono coperte dall’indice.

Poiché entrambe queste condizioni sono soddisfatte, il piano di query risultante rimuove la fase di ordinamento ed esegue più velocemente.

Mentre gli indici secondari possono velocizzare le query comuni, tenere presente che addingsecondary indexes può aggiungere latenza alle operazioni di commit, poiché in genere ogni indice secondario richiede il coinvolgimento di un nodo aggiuntivo in ogni commit. Formost carichi di lavoro, avere alcuni indici secondari va bene. Tuttavia, si shouldconsider se si cura di più di lettura o scrittura latenza, e considerare whichoperations sono più critici per il carico di lavoro. Dovresti anche confrontare yourworkload per assicurarti che funzioni come ti aspetti.

Per il riferimento completo agli indici secondari, fare riferimento agli indici secondari.

Scrivi query efficienti per la ricerca di chiavi intervallo

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

Queste sono le migliori pratiche per scrivere query efficienti quando si recuperano dati daun intervallo di chiavi:

Se l’elenco delle chiavi è sparso e non adiacente, utilizzare i parametri di query eUNNEST per costruire la query.

Ad esempio, se il tuo elenco di chiavi è {1, 5, 1000}, scrivi la query in questo modo:
```
SELECT *FROM Table AS tWHERE t.Key IN UNNEST (@KeyList)
```
Note:
- L’operatore array UNNEST appiattisce un array di input in righe di elementi.
- @KeyList è un parametro di query, che può accelerare la query come discusso nella best practice precedente.
Se l’elenco delle chiavi è adiacente e all’interno di un intervallo, specificare il limite inferiore e il limite superiore dell’intervallo di chiavi nella clausola WHERE.

Ad esempio, se il tuo elenco di chiavi è {1,2,3,4,5}, costruisci la query likethis:
```
SELECT *FROM Table AS tWHERE t.Key BETWEEN @min AND @max
```
Dove @min e @max sono parametri di query associati rispettivamente ai valori 1 e 5.

Si noti che questa query è più efficiente solo se le chiavi nell’intervallo di chiavi sono adiacenti. In altre parole, se il tuo elenco di chiavi è {1, 5, 1000}, non dovresti specificare i limiti inferiore e superiore come nella query precedente perché la query risultante scansionerebbe ogni valore compreso tra 1 e 1000.

Scrivere query efficienti per join

Le operazioni di join possono essere costose. Questo perché JOIN s può aumentare in modo significativoaumentare il numero di righe che la query deve eseguire la scansione, il che si traduce in slowerqueries. Oltre alle tecniche che sei abituato a utilizzare in altri database relazionali per ottimizzare le query di join, ecco alcune best practice per un JOIN più efficiente quando si utilizza Cloud Spanner SQL:

Se possibile, unire i dati nelle tabelle interleaved per chiave primaria. Biru:
```
SELECT s.FirstName, a.ReleaseDateFROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
```
Le righe nella tabella interleaved Albums sono garantite per essere fisicamente memorizzate nelle stesse suddivisioni della riga padre in Singers, come discusso nello Schema e nel modello di dati. Pertanto, JOIN s può esserecompletato localmente senza inviare molti dati attraverso la rete.
Utilizzare la direttiva join se si desidera forzare l’ordine di JOIN. Ad esempio:
```
SELECT *FROM Singers AS s [email protected]{FORCE_JOIN_ORDER=TRUE} Albums AS aON s.SingerId = a.SingeridWHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
```
La direttiva join @{FORCE_JOIN_ORDER=TRUE} indica a Cloud Spanner di utilizzare l’ordine di join specificato nella query (ovvero Singers JOIN Albums, nonAlbums JOIN Singers). I risultati restituiti sono gli stessi indipendentemente dall’ordine scelto da Cloud Spanner. Tuttavia, è possibile utilizzare questo joindirective se si nota nel piano di query che Cloud Spanner ha modificato l’ordine di join e ha causato risultati indesiderati come risultati intermedi più grandi o ha perso opportunità per la ricerca di righe.
Utilizzare una direttiva join per scegliere un’implementazione join. Scegliere il joinalgorithm giusto per la tua query può migliorare latenza, consumo di memoria, orboth. Questa query mostra la sintassi per l’usouna direttiva JOIN con il suggerimento JOIN_METHODper scegliere un HASH JOIN:
```
SELECT *FROM Singers s [email protected]{JOIN_METHOD=HASH_JOIN} Albums AS aON a.SingerId = a.SingerId
```
Se stai usando un HASH JOIN o APPLY JOIN e se hai una clausola WHEREaltamente selettiva su un lato del tuo JOIN, metti la tabella che produce il numero più piccolo di righe come prima tabella nel FROM clauseof join. Questo perché attualmente in HASH JOIN, Cloud Spanner alwayspicks il tavolo laterale sinistro come build e il tavolo laterale destro come asprobe. Allo stesso modo, per APPLY JOIN, Cloud Spanner sceglie il lato sinistro come esterno e il lato destro come interno. Vedi maggiori informazioni su questi tipi di join: Hash join e Apply join.

Evita letture di grandi dimensioni all’interno di transazioni di lettura-scrittura

Le transazioni di lettura-scrittura consentono una sequenza di query zero o morereads o SQL e possono includere una serie di mutazioni, prima di una chiamata a commit. Al fine di mantenere la coerenza dei dati, Cloud Spanneracquisisce i blocchi durante la lettura e la scrittura di righe nelle tabelle e negli indici (leggi maggiori dettagli sul blocco nella vita delle letture e delle scritture).

A causa del modo in cui il blocco funziona in Cloud Spanner, eseguire una lettura o SQLquery che legge un numero elevato di righe (ad esempio SELECT * FROM Singers) significa che nessun’altra transazione può scrivere nelle righe lette fino a quando yourtransaction non viene commit o abortito. Inoltre, poiché yourtransaction sta elaborando un numero elevato di righe, è probabile che richieda più tempo di una transazione che legge un intervallo di righe molto più piccolo (ad esempio SELECTLastName FROM Singers WHERE SingerId = 7), che aggrava ulteriormente il problemae riduce il throughput del sistema.

Quindi, dovresti cercare di evitare letture di grandi dimensioni (ad esempio: scansioni di tabelle complete o operazioni di join massive) all’interno delle transazioni, a meno che non si sia disposti ad accettare un throughput di scrittura inferiore. In alcuni casi, il seguente modello può produrrerisultati migliori:

Fai la tua grande lettura all’interno di una transazione di sola lettura. (Nota che le transazioni di sola lettura non utilizzano blocchi e quindi consentono un throughput più elevato.)
Se hai bisogno di eseguire qualsiasi elaborazione sui dati che hai appena letto, fallo.
Avvia una transazione di lettura-scrittura.
Verifica che le righe critiche a cui tieni non abbiano modificato i valori dal momento in cui hai eseguito la transazione di sola lettura nel passaggio 1.
1. Se le righe sono cambiate, ripristina la transazione e ricomincia al passo 1.
2. Se tutto sembra a posto, commetti le tue mutazioni.

Un modo per assicurarti di evitare letture di grandi dimensioni all’interno di read-writetransactions è guardare i piani di esecuzione generati da yourqueries.

Utilizzare ORDER BY per garantire l’ordine dei risultati SQL

Se si prevede un determinato ordine per i risultati di una query SELECT, è necessario includere esplicitamente la clausola ORDER BY. Ad esempio: se si desidera elencare tutti i cantanti in ordine di chiave primaria, utilizzare questa query:

SELECT * FROM SingersORDER BY SingerId;

Si noti che Cloud Spanner garantisce l’ordinamento dei risultati solo se la clausola ORDER BY è presente nella query. In altre parole, considera questa query senza il ORDERBY:

SELECT * FROM Singers;

Cloud Spanner non garantisce che i risultati di questa query saranno inordine chiave primaria. Inoltre, l’ordine dei risultati potrebbe cambiare in qualsiasi momentoe non è garantito che sia coerente da invocazione a invocazione.

Utilizzare STARTS_WITH invece di LIKE per accelerare le query SQL parametrizzate

Poiché Cloud Spanner non valuta i pattern parametrizzati LIKE fino al tempo di esecuzione, Cloud Spanner deve leggere tutte le righe e valutarle rispetto all’espressione LIKE per filtrare le righe che non corrispondono.

Nei casi in cui un pattern LIKE cerca le corrispondenze che si trovano all’inizio di avalue e la colonna è indicizzata, utilizzare STARTS_WITHinvece di LIKE. Thisallows Cloud Spanner per ottimizzare in modo più efficace il piano di esecuzione delle query.

Non raccomandato:

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

Consigliato:

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