Ottimizzare 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 visualizzazioni INFORMATION_SCHEMA.JOBS* o il metodo dell'API REST jobs.get.

Il piano di query include dettagli sulle fasi e sui passaggi della query. Questi dettagli possono aiutarti a identificare i 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 all'inizio della query.

Per scoprire di più sul piano di query e vedere esempi di come le informazioni sul piano di query possono aiutarti a migliorare le prestazioni delle query, consulta Ottenere informazioni sulle prestazioni delle query. Dopo aver esaminato gli approfondimenti sul rendimento delle query, puoi ottimizzare ulteriormente la query svolgendo le seguenti attività:

Riduci i dati elaborati

Puoi ridurre i dati che devono essere elaborati utilizzando le opzioni descritte nelle sezioni seguenti.

Evita SELECT *

Best practice:controlla la proiezione eseguendo query solo sulle colonne di cui hai bisogno.

La proiezione si riferisce al numero di colonne lette dalla query. La proiezione di colonne in eccesso comporta un'I/O aggiuntiva (sprecata) e la materializzazione (scrittura dei risultati).

  • Utilizza le opzioni di anteprima dei dati. Se stai conducendo esperimenti 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 query SELECT * 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 della quota del livello gratuito. Esegui invece query solo sulle colonne di cui hai bisogno. Ad esempio, utilizza SELECT * EXCEPT per escludere una o più colonne dai risultati.
  • Utilizza tabelle partizionate. Se hai bisogno di eseguire query su ogni colonna di una tabella, ma solo su un sottoinsieme di dati, valuta la possibilità di:

  • Utilizza SELECT * EXCEPT. L'esecuzione di query su un sottoinsieme di dati o l'utilizzo di SELECT * EXCEPT puoi ridurre notevolmente la quantità di dati letti da una query. Oltre al risparmio sui costi, le prestazioni vengono migliorate riducendo la quantità di I/O dei dati e la quantità di materializzazione necessaria per i risultati della query.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

Evita tabelle con troppi caratteri jolly

Best practice: quando esegui query sulle tabelle con funzione carattere jolly, devi utilizzare il prefisso più granulare.

Utilizza i caratteri jolly per eseguire query su più tabelle utilizzando istruzioni SQL concise. Le tabelle con caratteri jolly sono un'unione 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:

  • Più tabelle con nomi simili e 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 bigquery-public-data.noaa_gsod.gsod194* esegue query su tutte le tabelle degli anni '40.

I prefissi più granulari hanno un rendimento migliore rispetto ai prefissi più brevi. Ad esempio, FROM bigquery-public-data.noaa_gsod.gsod194* ha un rendimento migliore di FROM bigquery-public-data.noaa_gsod.* perché meno tabelle corrispondono al carattere jolly.

Evita le tabelle suddivise in parti per data

Best practice: non utilizzare tabelle suddivise per data (chiamate anche tabelle con nome della data) al posto di tabelle partizionate in base al tempo.

Le tabelle partizionate hanno un rendimento migliore rispetto alle tabelle con nomi di date. Quando crei tabelle suddivise 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 nomi di date, BigQuery potrebbe dover verificare le autorizzazioni per ogni tabella sottoposta a query. Questa pratica contribuisce anche all'overhead delle query e influisce sulle relative prestazioni.

Evita di eseguire lo sharding eccessivo delle tabelle

Best practice:evita di creare troppi shard di tabella. Se esegui lo sharding delle tabelle in base alla data, utilizza invece le tabelle partizionate in base al tempo.

Per sharding delle tabelle si intende la suddivisione di set di dati di grandi dimensioni in tabelle separate e l'aggiunta di un suffisso a ogni nome di tabella. Se esegui lo sharding delle tabelle per data, utilizza invece le tabelle partizionate in base al tempo.

A causa del basso costo dello spazio di archiviazione BigQuery, non è necessario ottimizzare le tabelle in base al costo come faresti in un sistema di database relazionale. La creazione di un numero elevato di frammenti di tabella ha un impatto sulle prestazioni che supera qualsiasi vantaggio in termini di costi.

Le tabelle suddivise in parti richiedono a BigQuery di gestire lo schema, i metadati e le autorizzazioni per ogni shard. A causa del sovraccarico aggiuntivo necessario per mantenere le informazioni su ogni shard, le tabelle con un numero eccessivo di shard possono influire sulle prestazioni delle query.

La quantità e l'origine dei dati letti da una query possono influire sulle prestazioni e sui costi della query.

Elimina le query partizionate

Best practice:quando esegui una query su una tabella partizionata, per filtrare con le partizioni sulle tabelle partizionate, utilizza le seguenti colonne:

  • Per le tabelle partizionate per data di importazione, utilizza la pseudocolonna _PARTITIONTIME
  • Per le tabelle partizionate, ad esempio quelle basate su colonne di unità di tempo e intervallo di interi, utilizza la colonna di partizionamento.

Per le tabelle partizionate in base a unità di tempo, filtrare i dati con _PARTITIONTIME o con la colonna di partizionamento ti consente di specificare una data o un intervallo di date. Ad esempio, la seguente clausola WHERE utilizza la pseudocolonna _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 richiede un'elaborazione intensa, perché questi tipi di query utilizzano il riordinamento. Poiché queste query richiedono un'elaborazione intensa, devi utilizzare una clausola GROUP BY solo quando necessario.

Per le query con GROUP BY e JOIN, esegui l'aggregazione all'inizio della query per ridurre la quantità di dati elaborati. Ad esempio, la seguente query esegue un JOIN su due tabelle di grandi dimensioni senza alcun filtro preliminare:

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 i conteggi dei commenti, il che riduce 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;

Utilizzare 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 sulle colonne BOOL, INT, FLOAT e DATE sono in genere più rapide delle operazioni sulle colonne STRING o BYTE. Se possibile, utilizza una colonna che utilizzi uno di questi tipi di dati nella clausola WHERE per ridurre la quantità di dati restituiti dalla query.

Ottimizzare 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 situazioni in cui trasformi ripetutamente gli stessi dati.

Ad esempio, se utilizzi SQL per tagliare le stringhe o estrarre i dati utilizzando espressioni regolari, è più efficace materializzare i risultati trasformati in una tabella di destinazione. Funzioni come le espressioni regolari richiedono calcoli aggiuntivi. Eseguire query sulla tabella di destinazione senza l'overhead aggiuntivo della trasformazione è molto più efficiente.

Evitare più valutazioni delle stesse CTE

Best practice: utilizza un linguaggio procedurale, variabili, tabelle temporanee e tabelle con scadenza automatica per mantenere i calcoli e utilizzarli in seguito nella query.

Quando la query contiene espressioni tabella comuni (CTE) che vengono utilizzate in più punti della query, potrebbero essere valutate ogni volta che viene fatto riferimento a esse. L'ottimizzatore delle query tenta di rilevare le parti della query che potrebbero essere eseguite una sola volta, ma ciò potrebbe non essere sempre possibile. Di conseguenza, l'utilizzo di un CTE potrebbe non contribuire a ridurre la complessità delle query interne e il consumo di 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 unioni e sottoquery ripetute

Best practice:evita di unire ripetutamente le stesse tabelle e di utilizzare le stesse subquery.

Invece di unire ripetutamente i dati, potrebbe essere più efficace utilizzare dati ripetuti nidificati per rappresentare le relazioni. I dati ripetuti nidificati ti consentono di risparmiare l'impatto sulle prestazioni della larghezza di banda di comunicazione richiesta da un join. Inoltre, ti consente di risparmiare sui costi di I/O sostenuti leggendo e scrivendo ripetutamente gli stessi dati. Per ulteriori informazioni, consulta la sezione Utilizzare campi nidificati e ripetuti.

Analogamente, la ripetizione delle stesse sottoquery influisce sul rendimento tramite l'elaborazione ripetitiva delle query. Se utilizzi le stesse sottoquery in più query, considera la possibilità di materializzare i risultati delle sottoquery in una tabella. Poi utilizza i dati materializzati nelle query.

La materializzazione dei 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 ripetuta di query e I/O.

Ottimizza i pattern di join

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, tieni presente l'ordine in cui fondi i dati. L'ottimizzatore delle query GoogleSQL determina quale tabella deve trovarsi su quale lato dell'unione. Come best practice, posiziona prima la tabella con il maggior numero di righe, poi la tabella con il minor numero di righe e infine le tabelle rimanenti in ordine decrescente di dimensioni.

Quando hai una tabella grande sul lato sinistro del JOIN e una piccola sul lato destro del JOIN, viene creato un join di trasmissione. Un join di tipo broadcast invia tutti i dati della tabella più piccola a ogni slot che elabora la tabella più grande. Ti consigliamo di eseguire prima l'unione 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 window. Sposta le operazioni complesse alla fine della query. L'inserimento di una clausola ORDER BY al centro di una query influisce notevolmente sul rendimento, a meno che non venga utilizzata in una funzione finestra.

    Un'altra tecnica per ordinare la query consiste nel spostare le operazioni complesse, come le espressioni regolari e le funzioni matematiche, alla fine della query. Questa tecnica riduce i dati da elaborare prima di eseguire le operazioni complesse.

  • Utilizza una clausola LIMIT. Se ordini un numero molto elevato di valori, ma non è necessario che vengano restituiti tutti, utilizza una clausola LIMIT. Ad esempio, la seguente query ordina un set di risultati molto grande e genera un errore Resources exceeded. La query ordina in base alla colonna title in mytable. La colonna title 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;

  • Utilizza una funzione finestra. Se ordini un numero molto elevato di valori, utilizza una funzione finestra e limita i dati prima di chiamarla. Ad esempio, la seguente query elenca i dieci utenti di Stack Overflow più antichi e il relativo ranking, con l'account più antico in fondo alla classifica:

    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 finestra DENSE_RANK() OVER. Per questo motivo, la query richiede che tutti i dati vengano ordinati su un singolo nodo worker.

    Per migliorare le prestazioni, dovresti invece limitare il set di dati prima di calcolare la funzione finestra:

    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 e restituisce gli stessi risultati della query precedente.

    Un'avvertenza è che la funzione DENSE_RANK() assegna un ranking ai dati all'interno degli anni, pertanto per i dati del ranking 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 una query complessa come più query più piccole e semplici.

Le query complesse, le funzioni REGEX e le unioni o le sottoquery a più livelli possono essere lente e richiedere molte risorse. A volte, provare a inserire tutti i calcoli in un unico statement SELECT enorme, ad esempio per creare una vista, è un antipattern 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 visualizzare 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 un punto della query.

Spesso ti consente di esprimere meglio l'intenzione reale di parti della query con le tabelle temporanee come punti di materializzazione dei dati.

Utilizzare campi nidificati e ripetuti

Per informazioni su come denormalizzare lo spazio di archiviazione dei dati utilizzando campi nidificati e ripetuti, consulta Utilizzare campi nidificati e ripetuti.

Utilizzare i tipi di dati INT64 nelle unioni

Best practice:utilizza i tipi di dati INT64 nelle unioni 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, più lungo è il tempo necessario per il confronto. Pertanto, l'utilizzo di tipi di dati INT64 nelle unioni è più economico ed efficiente rispetto all'utilizzo di tipi di dati STRING.

Ridurre gli output delle query

Puoi ridurre le uscite delle query utilizzando le opzioni descritte nelle sezioni seguenti.

Materializzare set di risultati di grandi dimensioni

Best practice:valuta la possibilità di materializzare set di risultati di grandi dimensioni in una tabella di destinazione. La scrittura di set di risultati di grandi dimensioni ha un impatto sulle prestazioni e sui 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 di scrittura dei risultati memorizzati nella cache possono verificarsi anche nelle query in stile ETL che normalizzano i dati senza riduzione o aggregazione.

Puoi superare il limite relativo alle dimensioni dei risultati memorizzati nella cache utilizzando le seguenti opzioni:

  • Utilizzare i filtri per limitare il set di risultati
  • Utilizza una clausola LIMIT per ridurre il set di risultati, soprattutto se utilizzi una clausola ORDER BY
  • Scrivere i dati di output in una tabella di destinazione

Puoi sfogliare i risultati utilizzando l'API REST BigQuery. Per ulteriori informazioni, consulta Eseguire la paginazione dei dati della tabella.

Utilizzare BI Engine

Per accelerare ulteriormente le query SQL memorizzando nella cache i dati che utilizzi più spesso, ti consigliamo di aggiungere una prenotazione di BI Engine al progetto in cui vengono calcolate le query. BigQuery BI Engine utilizza un motore di query vettore per accelerare il rendimento delle query SELECT.

Evitare gli anti-pattern SQL

Le seguenti best practice forniscono indicazioni su come evitare antipattern di 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, le unioni con se stessi vengono utilizzate per calcolare le relazioni dipendenti dalle righe. Il risultato dell'utilizzo di un join autonomo è che potenzialmente raddoppia il numero di righe di output. Questo aumento dei dati di output può causare prestazioni scadenti.

Evita le unioni crociate

Best practice:evita le unioni che generano più output rispetto agli input. Quando è richiesto un valore CROSS JOIN, preaggrega i dati.

I join incrociati sono query in cui ogni riga della prima tabella viene unita a ogni riga della seconda tabella, con chiavi non univoche su entrambi i lati. L'output peggiore equivale al numero di righe nella tabella di sinistra moltiplicato per il numero di righe nella tabella di destra. In casi estremi, la query potrebbe non essere completata.

Se il job di query viene completato, la spiegazione del piano di query mostra le righe di output rispetto alle righe di input. Puoi confermare un prodotto cartesiano modificando 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 che input:

  • Utilizza una clausola GROUP BY per preaggregare i dati.
  • Utilizza una funzione finestra. Le funzioni finestra sono spesso più efficienti dell'utilizzo di un join tra tabelle. Per ulteriori informazioni, consulta le funzioni finestra.

Evita le istruzioni DML che aggiornano o inseriscono singole righe

Best practice:evita le istruzioni DML che aggiornano o inseriscono singole righe. Esegui in batch gli aggiornamenti e gli inserimenti.

L'utilizzo di istruzioni DML specifiche per punto è un tentativo di trattare BigQuery come un sistema Online Transaction Processing (OLTP). BigQuery si concentra sull'elaborazione analitica online (OLAP) utilizzando le scansioni delle tabelle e non le ricerche puntuali. Se hai bisogno di un comportamento simile a OLTP (aggiornamenti o inserimenti di righe singole), valuta la possibilità di utilizzare un database progettato per supportare casi d'uso OLTP come 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 singole righe. L'istruzione DML INSERT deve essere utilizzata con parsimonia. Gli inserimenti utilizzano le stesse quote di modifica dei job di caricamento. Se il tuo caso d'uso prevede inserimenti frequenti di righe singole, valuta la possibilità di trasmettere i dati in streaming.

Se l'aggregazione delle istruzioni UPDATE genera molte tuple in query molto lunghe, potresti avvicinarti al limite di lunghezza della query di 256 KB. Per aggirare il limite di lunghezza della query, valuta se gli aggiornamenti possono essere gestiti in base a criteri logici anziché a una serie di sostituzioni di tuple dirette.

Ad esempio, puoi caricare l'insieme di record di sostituzione 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 pianificati nella tabella u, la query sarà la seguente:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

Utilizza nomi di alias per le colonne con nomi simili

Best practice:utilizza gli alias di colonne e tabelle quando lavori con colonne con nomi simili nelle query, incluse le sottoquery.

Gli alias consentono di identificare le colonne e le tabelle a cui viene fatto riferimento oltre al riferimento iniziale della colonna. L'utilizzo di alias può aiutarti a comprendere e risolvere i problemi nella query SQL, ad esempio trovare le colonne utilizzate nelle sottoquery.

Specificare i vincoli nello schema della tabella

Quando i dati della tabella contengono vincoli, specificali nello schema della tabella. Il motore di query può ottimizzare i piani di query utilizzando i vincoli delle tabelle.

Specifica i vincoli della chiave primaria e della chiave esterna

Devi specificare i vincoli delle chiavi nello schema della tabella quando i dati della tabella soddisfano i requisiti di integrità dei dati dei vincoli di chiavi primarie o chiavi esterne. Il motore delle query può utilizzare le limitazioni delle chiavi per ottimizzare i piani di query. Puoi trovare informazioni dettagliate nel post del blog Ottimizzazioni dell'unione con le chiavi principali 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 non essere accurati.

Passaggi successivi