Ottimizza il calcolo delle query

Questo documento fornisce le best practice per ottimizzare le prestazioni delle query.

Al termine della query, puoi visualizza 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 che scrive molto più output rispetto ad altre, potrebbe significare dover filtrare prima nella query.

Per scoprire di più sul piano di query e vedere esempi di come il piano di query possono aiutarti a migliorare le prestazioni delle query, consulta Ottieni insight sulle prestazioni delle query. Dopo aver esaminato gli insight sulle prestazioni delle query, puoi ottimizzare ulteriormente eseguendo queste operazioni:

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 una query solo sulle colonne necessaria.

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 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 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 del calcolo la quota del livello gratuito. Esegui invece query solo sulle colonne di cui hai bisogno. Ad esempio, usa SELECT * EXCEPT per escludere una o più colonne dai risultati.
  • Utilizza tabelle partizionate. Se hai la necessità di eseguire query su ogni colonna di una tabella, ma solo su un un sottoinsieme di dati, considera:

  • 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 a il risparmio sui costi, le prestazioni vengono migliorate riducendo la quantità di dati I/O 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. Carattere jolly sono un insieme di tabelle che corrispondono all'espressione con caratteri jolly. 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. Ad esempio, FROM bigquery-public-data.noaa_gsod.gsod194* esegue query su tutte le tabelle degli anni '40.

I prefissi più granulari hanno prestazioni migliori di quelli 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 le tabelle con sharding per data (chiamate anche con nome con data tabelle partizionate in base al tempo.

Le tabelle partizionate hanno un rendimento migliore rispetto a 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 il partizionamento eccessivo delle tabelle

Best practice: evita di creare troppi shard di tabella. Se esegui lo sharding per data, usa tabelle partizionate nel tempo.

Lo sharding delle tabelle si riferisce alla divisione di set di dati di grandi dimensioni in tabelle separate e all'aggiunta un suffisso per ogni nome di tabella. Se esegui lo sharding delle tabelle in base alla data, utilizza invece le tabelle partizionate in base al tempo.

Dato il basso costo dell'archiviazione BigQuery, non è necessario ottimizzare i costi delle tabelle come faresti in un sistema di database relazionale. La creazione di un numero elevato di shard delle tabelle influisce notevolmente sulle prestazioni vantaggi 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 sulla query in termini di prestazioni e costi.

Elimina le query partizionate

Best practice: quando esegui una query su una tabella partizionata, per filtrare con le partizioni nelle 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 all'unità di tempo, puoi filtrare i dati con _PARTITIONTIME o colonna di partizionamento consente di specificare una data o un intervallo di date. Ad esempio: la seguente clausola WHERE utilizza la pseudocolonna _PARTITIONTIME per specificare 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 molte risorse di calcolo, devi usare un'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 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 il conteggio dei commenti riducendo la quantità di dati lettura 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 un Clausola WHERE per limitare la quantità di dati restituiti da una query. Se possibile, usa BOOL, INT, FLOAT o DATE colonne 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 in le 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 dati utilizzando espressioni regolari, è più efficace materializzare genera una tabella di destinazione. Le funzioni come le espressioni regolari richiedono calcolo aggiuntivo. Eseguire query sulla tabella di destinazione senza l'overhead aggiuntivo della trasformazione è molto più efficiente.

Evita più valutazioni degli stessi CTE

Best practice: utilizza il linguaggio procedurale, variabili, tabelle temporanee, e tabelle con scadenza automatica per mantenere i calcoli e utilizzarli in un secondo momento la query.

Quando la query contiene espressioni di tabella comuni (CTE). utilizzati in più punti della query, potrebbero finire per essere valutati a ogni riferimento. 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 una CTE potrebbe non aiutare a ridurre le query interne la complessità e il consumo di risorse.

Puoi memorizzare il risultato di una CTE in una variabile scalare o 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 delle sottoquery.

Invece di unire ripetutamente i dati, potrebbe essere più efficace e usare dati ripetuti e 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.

Materializzare i risultati delle sottoquery migliora le prestazioni e riduce il traffico complessivo quantità 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 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, 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 trasmissione invia tutti i dati nella tabella più piccola a ogni slot che elabora il più grande tabella. 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 è il push di operazioni complesse come espressioni regolari e funzioni matematiche, alla fine della query. Questa tecnica riduce i dati da elaborare prima delle operazioni complesse in esecuzione.

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

  • Usa una funzione finestra. Se ordini 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 le dieci meno recenti Utenti di Stack Overflow e loro ranking, con l'account meno recente in base al 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 finestra DENSE_RANK() OVER. Per questo motivo, la query richiede che tutti i dati vengano ordinati in base a con 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() classifica i dati entro anni, quindi per il ranking dei 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 una query complessa come più query più piccole e semplici.

Le query complesse, le funzioni REGEX e le sottoquery o i join a livelli possono essere lenti e che richiedono molte risorse. Cercare di inserire tutti i calcoli in un'unica grande L'affermazione SELECT, ad esempio per renderla un'immagine, a volte è un anti-pattern, e può generare query lente e con 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 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'intento reale di parti della query con le tabelle temporanee come punti di materializzazione dei dati.

Utilizza campi nidificati e ripetuti

Per informazioni su come denormalizzare l'archiviazione dei dati utilizzando consulta Utilizzare i campi nidificati e ripetuti.

Utilizza INT64 tipi di dati nei join

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, maggiore sarà 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 offre prestazioni e costi impatti.

BigQuery limita i risultati memorizzati nella cache a circa 10 GB è compresso. 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 notevole quantità 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 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 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, i self-join vengono utilizzati per calcolare le relazioni dipendenti dalle righe. Il risultato dell'utilizzo di un'unione interna è che potenzialmente raddoppia il numero di righe di output. Questo aumento dei dati di output può causare uno scarso rendimento.

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 per stampare il numero di righe su ogni lato di JOIN raggruppata per 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.
  • Usa una funzione finestra. Le funzioni finestra sono spesso più efficienti dell'utilizzo un cross join. 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. Raggruppa gli aggiornamenti e inseriti.

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. UPDATE e DELETE istruzioni DML in BigQuery sono orientate verso riscritture periodiche dei dati, non mutazioni di una singola riga. DML INSERT è da usare con parsimonia. Gli inserti consumano lo stesso quote di modifica come 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 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 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 sono inseriti in un'area intermedia 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

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 aiutano a identificare le colonne e le tabelle a cui viene fatto riferimento oltre alle il riferimento iniziale della colonna. L'utilizzo degli alias può aiutarti a comprendere risolvere 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 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 chiave primaria o di chiave esterna. Il motore di query può utilizzare i vincoli principali per ottimizzare i piani di query. Puoi 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 non essere accurati.

Passaggi successivi