Ottimizza il calcolo delle query
Questo documento fornisce le best practice per ottimizzare le prestazioni delle query.
Al termine della query, puoi visualizzare il piano di query nella console Google Cloud. Puoi anche richiedere i dettagli di esecuzione utilizzando le viste INFORMATION_SCHEMA.JOBS*
o il metodo API REST jobs.get
.
Il piano di query include dettagli sulle fasi e sui passaggi delle query. Questi dettagli possono aiutarti a identificare modi per migliorare le prestazioni delle query. Ad esempio, se noti una fase che scrive molto più output rispetto ad altre fasi, potrebbe significare che devi applicare un filtro prima della query.
Per saperne di più sul piano di query e vedere esempi di come le relative informazioni possono aiutarti a migliorare le prestazioni delle query, consulta Ottenere insight sulle prestazioni delle query. Dopo aver esaminato gli insight sulle prestazioni delle query, puoi ottimizzare ulteriormente la query eseguendo le attività seguenti:
- Ridurre i dati da elaborare
- Ottimizzare le operazioni di query
- Riduci l'output della query
- Utilizzare una prenotazione BigQuery BI Engine
- Evitare i pattern anti-SQL
- Specifica i vincoli nello schema della tabella
Riduci i dati elaborati
Puoi ridurre i dati da elaborare utilizzando le opzioni descritte nelle sezioni seguenti.
Evita SELECT *
Best practice: controlla la proiezione eseguendo una query solo sulle colonne necessarie.
La proiezione si riferisce al numero di colonne lette dalla query. La proiezione di colonne in eccesso comporta ulteriori (sprechi) I/O e materializzazione (scrittura dei risultati).
- Utilizza le opzioni di anteprima dei dati. Se stai sperimentando con i dati o esplorando i dati, utilizza una delle opzioni di anteprima dei dati anziché
SELECT *
. - Esegui query su colonne specifiche. L'applicazione di una clausola
LIMIT
a una querySELECT *
non influisce sulla quantità di dati letti. Ti viene addebitato il costo per la lettura di tutti i byte dell'intera tabella e la query viene conteggiata ai fini del calcolo della quota del livello gratuito. Puoi invece eseguire una query solo sulle colonne che ti servono. Ad esempio, utilizzaSELECT * EXCEPT
per escludere una o più colonne dai risultati. - Utilizza tabelle partizionate. Se richiedi query su ogni colonna di una tabella, ma solo su un
sottoinsieme di dati, considera:
- Materializzazione dei risultati in una tabella di destinazione e esecuzione di query su quella tabella.
- Partizionamento delle tabelle ed esecuzione di query sulla partizione pertinente.
Ad esempio, utilizza
WHERE _PARTITIONDATE="2017-01-01"
per eseguire query solo sulla partizione del 1° gennaio 2017.
Usa
SELECT * EXCEPT
. L'esecuzione di query su un sottoinsieme di dati o l'utilizzo diSELECT * EXCEPT
può ridurre notevolmente la quantità di dati letti da una query. Oltre al risparmio sui costi, le prestazioni sono migliorate riducendo la quantità di dati I/O e la quantità di materializzazione necessaria per i risultati delle query.SELECT * EXCEPT (col1, col2, col5) FROM mydataset.newtable
Evita un numero eccessivo di tabelle con caratteri jolly
Best practice: quando esegui query su tabelle con caratteri jolly, devi utilizzare il prefisso più granulare.
Utilizzare caratteri jolly per eseguire query su più tabelle con istruzioni SQL concise. Le tabelle con caratteri jolly sono un insieme di tabelle che corrispondono all'espressione con caratteri jolly. Le tabelle con caratteri jolly sono utili se il set di dati contiene le seguenti risorse:
- Tabelle multiple con nomi simili con schemi compatibili
- Tabelle con sharding
Quando esegui una query su una tabella con funzione carattere jolly, specifica un carattere jolly (*
) dopo il prefisso della tabella comune. Ad esempio, FROM
esegue una query su tutte le tabelle degli anni '40.bigquery-public-data.noaa_gsod.gsod194*
I prefissi più granulari hanno prestazioni migliori di quelli più brevi. Ad esempio, FROM
ha un rendimento migliore di bigquery-public-data.noaa_gsod.gsod194*
FROM
, perché meno tabelle corrispondono al carattere jolly.bigquery-public-data.noaa_gsod.*
Evita lo sharding delle tabelle per data
Best practice: non utilizzare le tabelle con sharding per data (chiamate anche tabelle con nome con data) al posto delle tabelle partizionate nel tempo.
Le tabelle partizionate hanno un rendimento migliore rispetto alle tabelle con nome in base alla data. Quando crei tabelle con sharding per data, BigQuery deve mantenere una copia dello schema e dei metadati per ogni tabella con nome della data. Inoltre, quando vengono utilizzate tabelle con nome, BigQuery potrebbe essere necessario verificare le autorizzazioni per ogni tabella sottoposta a query. Questa prassi aumenta anche l'overhead delle query e ne influisce sulle prestazioni.
Evita il partizionamento eccessivo delle tabelle
Best practice: evita di creare troppi shard della tabella. Se applichi lo sharding delle tabelle per data, utilizza le tabelle partizionate in base al tempo.
Lo sharding delle tabelle si riferisce alla suddivisione di set di dati di grandi dimensioni in tabelle separate e all'aggiunta di un suffisso a ogni nome di tabella. Se applichi lo sharding delle tabelle in base alla data, utilizza invece le tabelle partizionate nel tempo.
A causa del basso costo dell'archiviazione di BigQuery, non è necessario ottimizzare le tabelle per i costi come faresti in un sistema di database relazionale. La creazione di un numero elevato di shard delle tabelle ha un impatto sulle prestazioni che supera eventuali vantaggi in termini di costi.
Le tabelle shard richiedono a BigQuery di mantenere schema, metadati e autorizzazioni per ogni shard. A causa dell'overhead aggiuntivo necessario per la manutenzione delle informazioni su ogni shard, l'oversharding delle tabelle può influire sulle prestazioni delle query.
La quantità e l'origine dei dati letti da una query possono influire sulle prestazioni e sui costi delle query.
Elimina query partizionate
Best practice: quando esegui una query su una tabella partizionata, per filtrare con partizioni nelle tabelle partizionate, utilizza le seguenti colonne:
- Per le tabelle partizionate in fase di importazione, utilizza la pseudo-colonna
_PARTITIONTIME
- Per le tabelle partizionate, come quelle basate su colonne nell'unità di tempo e l'intervallo di numeri interi, utilizza la colonna di partizionamento.
Per le tabelle partizionate in base all'unità di tempo, puoi filtrare i dati con _PARTITIONTIME
o con la
colonna di partizionamento per specificare una data o un intervallo di date. Ad esempio, la seguente clausola WHERE
utilizza la pseudo colonna _PARTITIONTIME
per specificare le partizioni tra il 1° gennaio 2016 e il 31 gennaio 2016:
WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")
La query elabora i dati solo nelle partizioni indicate dall'intervallo di date. Filtrare le partizioni migliora le prestazioni delle query e riduce i costi.
Riduci i dati prima di utilizzare un JOIN
Best practice: riduci la quantità di dati elaborati prima di una clausola JOIN
eseguendo aggregazioni.
L'utilizzo di una clausola GROUP BY
con funzioni aggregate
è intensivo di calcolo, poiché questi tipi di query utilizzano lo
shuffling.
Poiché queste query richiedono un'elevata intensità di calcolo, devi utilizzare una clausola GROUP BY
solo quando necessario.
Per le query con GROUP BY
e JOIN
, esegui l'aggregazione in precedenza nella query
per ridurre la quantità di dati elaborati.
Ad esempio, la seguente query esegue un JOIN
su due tabelle di grandi dimensioni senza
applicare alcun filtro in anticipo:
WITH users_posts AS ( SELECT * FROM `bigquery-public-data`.stackoverflow.comments AS c JOIN `bigquery-public-data`.stackoverflow.users AS u ON c.user_id = u.id ) SELECT user_id, ANY_VALUE(display_name) AS display_name, ANY_VALUE(reputation) AS reputation, COUNT(text) AS comments_count FROM users_posts GROUP BY user_id ORDER BY comments_count DESC LIMIT 20;
Questa query preaggrega il conteggio dei commenti, riducendo la quantità di dati letti per JOIN
:
WITH comments AS ( SELECT user_id, COUNT(text) AS comments_count FROM `bigquery-public-data`.stackoverflow.comments WHERE user_id IS NOT NULL GROUP BY user_id ORDER BY comments_count DESC LIMIT 20 ) SELECT user_id, display_name, reputation, comments_count FROM comments JOIN `bigquery-public-data`.stackoverflow.users AS u ON user_id = u.id ORDER BY comments_count DESC;
Utilizza la clausola WHERE
Best practice: utilizza una
clausola WHERE
per limitare la quantità di dati restituiti da una query. Se possibile, utilizza le colonne BOOL
, INT
, FLOAT
o DATE
nella clausola WHERE
.
Le operazioni nelle colonne BOOL
, INT
, FLOAT
e DATE
sono in genere più veloci
delle operazioni nelle colonne STRING
o BYTE
. Se possibile, utilizza una colonna che
utilizza uno di questi tipi di dati nella clausola WHERE
per ridurre la quantità di
dati restituiti dalla query.
Ottimizza le operazioni di query
Puoi ottimizzare le operazioni di query utilizzando le opzioni descritte nelle sezioni seguenti.
Evita di trasformare ripetutamente i dati
Best practice: se utilizzi SQL per eseguire operazioni ETL, evita le situazioni in cui trasformi ripetutamente gli stessi dati.
Ad esempio, se utilizzi SQL per tagliare le stringhe o estrarre dati mediante espressioni regolari, è più efficace materializzare i risultati trasformati in una tabella di destinazione. Funzioni come le espressioni regolari richiedono un ulteriore calcolo. L'esecuzione di query sulla tabella di destinazione senza l'overhead di trasformazione aggiunto è molto più efficiente.
Evita più valutazioni degli stessi CTE
Best practice: utilizza il linguaggio procedurale, le variabili, le tabelle temporanee e le tabelle con scadenza automatica per mantenere i calcoli e utilizzarli in un secondo momento nella query.
Quando la query contiene espressioni di tabella comuni (CTE) utilizzate in più punti della query, potrebbero finire per essere valutate ogni volta che viene loro fatto riferimento. Lo strumento di ottimizzazione delle query tenta di rilevare parti della query che potrebbero essere eseguite una sola volta, ma potrebbe non essere sempre possibile. Di conseguenza, l'utilizzo di una CTE potrebbe non aiutare a ridurre la complessità delle query interne e il consumo delle risorse.
Puoi memorizzare il risultato di una CTE in una variabile scalare o in una tabella temporanea a seconda dei dati restituiti dalla CTE.
Evita join e sottoquery ripetuti
Best practice: evita di unire ripetutamente le stesse tabelle e utilizzare le stesse sottoquery.
Anziché unire ripetutamente i dati, potrebbe essere più efficace utilizzare dati ripetuti nidificati per rappresentare le relazioni. I dati ripetuti nidificati ti consentono di ridurre l'impatto sulle prestazioni della larghezza di banda di comunicazione richiesta da un join. Inoltre, consente di risparmiare i costi di I/O sostenuti per la lettura e la scrittura ripetute degli stessi dati. Per ulteriori informazioni, consulta Utilizzare campi nidificati e ripetuti.
Analogamente, la ripetizione delle stesse sottoquery influisce sulle prestazioni mediante l'elaborazione di query ripetitiva. Se utilizzi le stesse sottoquery in più query, valuta la possibilità di materializzare i risultati della sottoquery in una tabella. Quindi utilizza i dati materializzati nelle tue query.
Materializzare i risultati delle sottoquery migliora le prestazioni e riduce la quantità complessiva di dati letti e scritti da BigQuery. Il piccolo costo di archiviazione dei dati materializzati supera l'impatto sulle prestazioni dell'elaborazione di query e I/O ripetuti.
Ottimizza i tuoi pattern di unione
Best practice: per le query che uniscono i dati di più tabelle, ottimizza i pattern di join iniziando dalla tabella più grande.
Quando crei una query utilizzando una clausola JOIN
, considera l'ordine in cui unisci i dati. Lo strumento di ottimizzazione delle query GoogleSQL
determina quale tabella deve trovarsi su quale lato del join. Come best practice, posiziona prima la tabella con il maggior numero di righe, seguita dalla tabella con il minor numero di righe, quindi posiziona le tabelle rimanenti diminuendo le dimensioni.
Se hai una tabella grande sul lato sinistro di JOIN
e una piccola
sul lato destro di JOIN
, viene creato un join di trasmissione. Un join di trasmissione invia tutti i dati della tabella più piccola a ogni slot che elabora la tabella più grande. È consigliabile eseguire prima il join alla trasmissione.
Per visualizzare le dimensioni delle tabelle in JOIN
, consulta
Ottenere informazioni sulle tabelle.
Ottimizza la clausola ORDER BY
Best practice: quando utilizzi la clausola ORDER BY
, assicurati di seguire
le best practice.
Utilizza
ORDER BY
nella query più esterna o all'interno delle clausole finestra. Esegui il push delle operazioni complesse alla fine della query. L'inserimento di una clausolaORDER BY
nel mezzo di una query influisce notevolmente sulle prestazioni, a meno che non venga utilizzata in una funzione finestra.Un'altra tecnica per ordinare la query è il push di operazioni complesse, come espressioni regolari e funzioni matematiche, alla fine della query. Questa tecnica riduce i dati da elaborare prima dell'esecuzione delle operazioni complesse.
Utilizza una clausola
LIMIT
. Se stai ordinando un numero molto elevato di valori ma non è necessario che vengano restituiti tutti, utilizza una clausolaLIMIT
. Ad esempio, la seguente query ordina un set di risultati molto grande e genera un erroreResources exceeded
. La query viene ordinata in base alla colonnatitle
inmytable
. La colonnatitle
contiene milioni di valori.SELECT title FROM `my-project.mydataset.mytable` ORDER BY title;
Per rimuovere l'errore, utilizza una query come la seguente:
SELECT title FROM `my-project.mydataset.mytable` ORDER BY title DESC LIMIT 1000;
Usa una funzione finestra. Se stai ordinando un numero molto elevato di valori, utilizza una funzione finestra e limita i dati prima di chiamare la funzione finestra. Ad esempio, la seguente query elenca i dieci utenti meno recenti di Stack Overflow e il relativo ranking, con l'account meno recente il ranking più basso:
SELECT id, reputation, creation_date, DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank FROM bigquery-public-data.stackoverflow.users ORDER BY user_rank ASC LIMIT 10;
L'esecuzione di questa query richiede circa 15 secondi. Questa query utilizza
LIMIT
alla fine della query, ma non nella funzione finestraDENSE_RANK() OVER
. Per questo motivo, la query richiede che tutti i dati siano ordinati su un singolo nodo worker.Limita il set di dati prima di calcolare la funzione finestra al fine di migliorare le prestazioni:
WITH users AS ( SELECT id, reputation, creation_date, FROM bigquery-public-data.stackoverflow.users ORDER BY creation_date ASC LIMIT 10) SELECT id, reputation, creation_date, DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank FROM users ORDER BY user_rank;
L'esecuzione di questa query richiede circa 2 secondi, mentre vengono restituiti gli stessi risultati della query precedente.
Un'avvertenza è che la funzione
DENSE_RANK()
classifica i dati entro anni, quindi per classificare i dati che si estendono su più anni, queste query non forniscono risultati identici.
Suddividi le query complesse in query più piccole
Best practice: sfrutta le funzionalità di query con più istruzioni e le procedure archiviate per eseguire i calcoli progettati come un'unica query complessa sotto forma di più query semplici e più piccole.
Le query complesse, le funzioni REGEX
e le sottoquery o i join a livelli possono essere lenti
e richiedono molte risorse. Cercare di inserire tutti i calcoli in un'unica istruzione SELECT
di grandi dimensioni, ad esempio per renderla una vista, a volte è un anti-pattern e può comportare una query lenta e che richiede molte risorse. In casi estremi, il piano di query interno diventa così complesso che BigQuery non è in grado di eseguirlo.
La suddivisione di una query complessa consente di materializzare i risultati intermedi in variabili o tabelle temporanee. Puoi quindi utilizzare questi risultati intermedi in altre parti della query. È sempre più utile quando questi risultati sono necessari in più di una posizione della query.
Spesso ti consente di esprimere meglio il vero intento di parti della query in cui le tabelle temporanee rappresentano i punti di materializzazione dei dati.
Utilizza campi nidificati e ripetuti
Per informazioni su come denormalizzare l'archiviazione dei dati utilizzando campi nidificati e ripetuti, consulta Utilizzare campi nidificati e ripetuti.
Utilizza INT64
tipi di dati nei join
Best practice: utilizza i tipi di dati INT64
nei join anziché i tipi di dati STRING
per ridurre i costi e migliorare le prestazioni del confronto.
BigQuery non indicizza le chiavi primarie come i database
tradizionali, quindi più ampia è la colonna di join, maggiore è il tempo necessario. Di conseguenza, l'utilizzo di tipi di dati INT64
nei join è più economico ed efficiente rispetto all'utilizzo di tipi di dati STRING
.
Riduci gli output delle query
Puoi ridurre gli output delle query utilizzando le opzioni descritte nelle sezioni che seguono.
Materializzazione di grandi insiemi di risultati
Best practice: valuta la possibilità di materializzare set di risultati di grandi dimensioni in una tabella di destinazione. Scrivere set di risultati di grandi dimensioni ha un impatto su prestazioni e costi.
BigQuery limita i risultati memorizzati nella cache a circa 10 GB compressi. Le query che restituiscono risultati più grandi superano questo limite e spesso generano il seguente errore: Response too large
.
Questo errore si verifica spesso quando selezioni un numero elevato di campi da una tabella con una quantità considerevole di dati. I problemi nella scrittura dei risultati memorizzati nella cache possono verificarsi anche nelle query di tipo ETL che normalizzano i dati senza riduzione o aggregazione.
Puoi evitare il limite delle dimensioni dei risultati memorizzati nella cache utilizzando le seguenti opzioni:
- Utilizzare i filtri per limitare l'insieme di risultati
- Utilizza una clausola
LIMIT
per ridurre il set di risultati, soprattutto se usi una clausolaORDER BY
- Scrivi i dati di output in una tabella di destinazione
Puoi sfogliare i risultati utilizzando l'API REST di BigQuery. Per maggiori informazioni, consulta la sezione Pasing dei dati della tabella.
Utilizzo di BI Engine
Per accelerare ulteriormente le query SQL memorizzando nella cache i dati che utilizzi più spesso, ti consigliamo di aggiungere una prenotazione BI Engine al progetto in cui vengono calcolate le query.
BigQuery BI Engine utilizza un motore di query vettoriale per accelerare le prestazioni delle query SELECT
.
Evita i pattern anti-SQL
Le best practice riportate di seguito forniscono indicazioni su come evitare gli anti-pattern delle query che influiscono sulle prestazioni in BigQuery.
Evita i self-join
Best practice: anziché utilizzare i self-join, utilizza una
funzione finestra (analitica)
o l'operatore PIVOT
.
In genere, i self-join vengono utilizzati per calcolare le relazioni dipendenti dalle righe. Il risultato dell'utilizzo di un self-join è che potenzialmente eleva il numero di righe di output al quadrato. Questo aumento dei dati di output può causare uno scarso rendimento.
Evita i cross join
Best practice: evita i join che generano più output che input. Quando è
necessario un CROSS JOIN
, preaggrega i dati.
I cross join sono query in cui ogni riga della prima tabella è unita a ogni riga della seconda tabella, con chiavi non univoche su entrambi i lati. L'output peggiore è il numero di righe nella tabella a sinistra moltiplicato per il numero di righe nella tabella a destra. In casi estremi, la query potrebbe non terminare.
Se il job di query viene completato, la spiegazione del piano di query mostra le righe di output rispetto alle righe di input. Per confermare un prodotto cartesiano, modifica la query in modo da stampare il numero di righe su ciascun lato della clausola JOIN
, raggruppate in base alla chiave di join.
Per evitare problemi di prestazioni associati ai join che generano più output rispetto agli input:
- Utilizza una clausola
GROUP BY
per preaggregare i dati. - Usa una funzione finestra. Le funzioni finestra sono spesso più efficienti dell'uso di un cross join. Per ulteriori informazioni, consulta la sezione Funzioni finestra.
Evita istruzioni DML che aggiornano o inseriscono singole righe
Best practice: evita istruzioni DML che aggiornano o inseriscono singole righe. Raggruppa gli aggiornamenti e gli inserimenti.
L'uso di istruzioni DML specifiche per ogni punto rappresenta un tentativo di considerare BigQuery come un sistema di elaborazione delle transazioni online (OLTP). BigQuery si concentra sull'elaborazione analitica online (OLAP) utilizzando analisi delle tabelle anziché ricerche per punto. Se hai bisogno di un comportamento di tipo OLTP (aggiornamenti o inserimenti su riga singola), prendi in considerazione un database progettato per supportare casi d'uso di OLTP, ad esempio Cloud SQL.
Le istruzioni DML di BigQuery sono destinate agli aggiornamenti collettivi. Le istruzioni DML UPDATE
e DELETE
in BigQuery sono orientate alle riscritture periodiche dei dati, non alle mutazioni di una singola riga. L'istruzione DML INSERT
deve essere utilizzata con parsimonia. Gli inserimenti consumano le stesse quotas di modifica dei job di caricamento. Se il tuo caso d'uso prevede l'inserimento frequente di una sola riga, valuta invece la possibilità di eseguire il flussi di dati per i dati.
Se raggruppando le istruzioni UPDATE
in batch si ottengono molte tuple in query molto lunghe,
potresti avvicinarti al limite di lunghezza delle query di 256 kB. Per aggirare il limite di lunghezza delle query, valuta se gli aggiornamenti possono essere gestiti in base a criteri logici invece che a una serie di sostituzioni dirette di tuple.
Ad esempio, puoi caricare il set di record sostitutivi in un'altra tabella, quindi scrivere l'istruzione DML per aggiornare tutti i valori nella tabella originale se le colonne non aggiornate corrispondono. Ad esempio, se i dati originali si trovano nella tabella t
e
gli aggiornamenti vengono inseriti temporaneamente nella tabella u
, la query sarà simile alla seguente:
UPDATE dataset.t t SET my_column = u.my_column FROM dataset.u u WHERE t.my_key = u.my_key
Utilizzare nomi alias per colonne con nomi simili
Best practice: utilizza gli alias di colonna e tabella quando utilizzi colonne con nomi simili nelle query, incluse le sottoquery.
Gli alias aiutano a identificare le colonne e le tabelle a cui viene fatto riferimento in aggiunta al riferimento iniziale della colonna. L'utilizzo di alias può aiutarti a comprendere e risolvere i problemi della query SQL, inclusa la ricerca delle colonne utilizzate nelle sottoquery.
Specifica i vincoli nello schema della tabella
Se i dati della tabella contengono vincoli, specifica questi ultimi nello schema della tabella. Il motore di query può ottimizzare i piani di query utilizzando vincoli delle tabelle.
Specifica i vincoli di chiave primaria e esterna
Devi specificare i vincoli delle chiavi nello schema della tabella se i dati della tabella soddisfano i requisiti di integrità dei dati dei vincoli di chiave esterna primaria o esterna. Il motore di query può utilizzare i vincoli chiave per ottimizzare i piani di query. Puoi trovare informazioni dettagliate nel blog post Unire le ottimizzazioni con le chiavi primarie e le chiavi esterne di BigQuery.
BigQuery non controlla automaticamente l'integrità dei dati, quindi devi assicurarti che i dati soddisfino i vincoli specificati nello schema della tabella. Se non mantieni l'integrità dei dati nelle tabelle con vincoli specificati, i risultati della query potrebbero essere imprecisi.
Passaggi successivi
- Scopri come ottimizzare i costi.
- Scopri come ottimizzare lo spazio di archiviazione.
- Scopri come ottimizzare le funzioni.