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

Il piano di query include dettagli sulle fasi e sui passaggi delle query. Questi dettagli possono ti aiutano a identificare 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 da elaborare utilizzando le opzioni descritte in le 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 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. Applicazione di una clausola LIMIT a un SELECT * query 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. Puoi invece eseguire una query solo sulle colonne che ti servono. 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:

  • Usa SELECT * EXCEPT. Esecuzione di query su un sottoinsieme di dati o utilizzo di SELECT * EXCEPT 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 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. 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:

  • 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. 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 il carattere jolly.

Evita lo sharding delle tabelle 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 con sharding per data, BigQuery deve conservare una copia dello schema e dei metadati per ogni con data specificata. Inoltre, quando vengono utilizzate tabelle con nome della data, BigQuery potrebbe essere necessario verificare le autorizzazioni per ogni tabella su cui è stata eseguita la query. Questa prassi aumenta anche l'overhead delle query e le prestazioni.

Evita il partizionamento eccessivo delle tabelle

Best practice: evita di creare troppi shard della 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 applichi lo sharding delle tabelle per data, utilizza tabelle partizionate nel 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 shard richiedono BigQuery per la manutenzione di schema, metadati e autorizzazioni per ogni shard. A causa dell'overhead aggiuntivo necessario mantenere le informazioni su ogni shard, l'oversharding delle tabelle può influire sulla query delle prestazioni.

La quantità e l'origine dei dati letti da una query possono influire sulla query in termini di prestazioni e costi.

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 pseudocolonna _PARTITIONTIME
  • Per le tabelle partizionate, come quelle basate su colonne nell'unità di tempo e l'intervallo di numeri interi, usa 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 dalla data intervallo. 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 un JOIN di una clausola mediante l'esecuzione di aggregazioni.

Utilizzare una clausola GROUP BY con funzioni aggregate richiede molta elaborazione, perché questi tipi di query riproduzione casuale. 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 qualsiasi 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 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ù veloci rispetto alle operazioni nelle colonne STRING o BYTE. Se possibile, utilizza una colonna utilizza uno di questi tipi di dati nella clausola WHERE per ridurre la quantità i dati restituiti dalla query.

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

Ad esempio, se usi 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. Esecuzione di query sulla tabella di destinazione senza l'aggiunta l'overhead 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. Lo strumento di ottimizzazione delle query tenta di rilevare parti query che può essere eseguita una sola volta, ma non 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. Dati ripetuti nidificati consente di ridurre l'impatto sulle prestazioni della larghezza di banda della comunicazione utilizzata da un join richiede. Ti permette inoltre di risparmiare i costi di I/O che devi sostenere ripetutamente leggere e scrivere gli stessi dati. Per ulteriori informazioni, vedi utilizza campi nidificati e ripetuti.

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

Materializzando i risultati delle sottoquery, migliora le prestazioni e riduce il traffico complessivo quantità di dati letti e scritti da BigQuery. Il modello di archiviazione dei dati materializzati supera l'impatto sulle prestazioni l'elaborazione ripetuta delle query e dell'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, considera l'ordine in cui vengono unendo i dati. Lo strumento di ottimizzazione delle query di GoogleSQL determina quale tabella da quale lato del join. Come best practice, inserisci la tabella con il maggior numero di righe, seguita dalla tabella con il minor numero di righe e poi posiziona le tabelle rimanenti diminuendo le dimensioni.

Quando hai una tabella grande come lato sinistro di JOIN e una piccola sulla Sul lato destro di 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 . È 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 clausole window. Esegui il push delle operazioni complesse alla fine della query. Il posizionamento di una clausola ORDER BY nel mezzo di una query ha un impatto significativo a meno che non venga usato 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 restituisce una Resources exceeded errore. 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 al 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.

    Dovresti invece limitare il set di dati prima di calcolare la funzione finestra Per migliorare il rendimento:

    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 il valore 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: utilizza query con più dichiarazioni funzionalità e procedure archiviate eseguire i calcoli studiati come una singola query complessa più piccole e più 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, di query interna diventa così complesso che BigQuery non è in grado 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ù utili quando questi risultati sono necessari in più di una posizione del query.

Spesso ti consente di esprimere meglio il vero intento di parti la 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 consulta Utilizzare i campi nidificati e ripetuti.

Utilizza INT64 tipi di dati nei join

Best practice: utilizza i tipi di dati INT64 nei join anziché i dati STRING per ridurre i costi e migliorare il rendimento dei confronti.

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 INT64 tipi di dati nei join è più economico ed efficiente rispetto all'utilizzo STRING tipi di dati.

Riduci gli output delle query

Puoi ridurre gli output delle query utilizzando le opzioni descritte di seguito le sezioni.

Materializzazione di grandi insiemi di risultati

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 comportano 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. Possono verificarsi anche problemi nella scrittura dei risultati memorizzati nella cache in query di tipo ETL che normalizzano i dati senza riduzione o aggregazione.

Puoi evitare il limite delle dimensioni dei risultati memorizzati nella cache utilizzando: opzioni:

  • Utilizzare i filtri per limitare l'insieme di risultati
  • Utilizza una clausola LIMIT per ridurre il set di risultati, soprattutto se usi una clausola ORDER BY
  • Scrivi i dati di output in una tabella di destinazione

Puoi sfogliare i risultati utilizzando l'API REST di BigQuery. Per ulteriori 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 usi di più di frequente, prendi in considerazione l'aggiunta di un BI Engine nel progetto in cui vengono calcolate le query. BigQuery BI Engine utilizza un motore di query vettoriale per accelerare SELECT le prestazioni della query.

Evita i pattern anti-SQL

Le best practice riportate di seguito forniscono indicazioni su come evitare gli anti-pattern di query che influiscono sulle prestazioni in BigQuery.

Evita i self-join

Best practice: anziché utilizzare i self-join, usa una funzione finestra (analitica) o il PIVOT.

In genere, i self-join vengono utilizzati per calcolare le relazioni dipendenti dalle righe. La il risultato dell'utilizzo di un self-join è che potenzialmente al quadrato il numero di righe. 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 È obbligatorio specificare il campo CROSS JOIN. Preaggrega i dati.

I cross join sono query in cui ogni riga della prima tabella è unita a ogni riga di comando nella seconda tabella, con chiavi non univoche su entrambi i lati. Il peggiore case output è 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. 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 rispetto agli 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 la sezione Funzioni finestra.

Evita istruzioni DML che aggiornano o inseriscono singole righe

Best practice: evita DML che aggiornano o inseriscono singole righe. Raggruppa gli aggiornamenti e inseriti.

L'uso di istruzioni DML specifiche per singoli punti è un tentativo BigQuery come un sistema di elaborazione delle transazioni online (OLTP). BigQuery si concentra sull'elaborazione analitica online (OLAP) utilizzando scansioni delle tabelle e non le ricerche per punto. Per un comportamento di tipo OLTP (riga singola aggiornamenti o inserimenti), prendere in considerazione un database progettato per supportare casi d'uso di OLTP come 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 frequenti inserimenti di una sola riga, considera in modalità flusso di 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 la query di lunghezza massima, considera se gli aggiornamenti possono essere gestiti in base a una anziché una serie di sostituzioni di tuple dirette.

Ad esempio, puoi caricare il set di record sostitutivi in un'altra tabella quindi scrivi 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

Utilizzare nomi alias per colonne con nomi simili

Best practice:utilizza gli alias di colonna e tabella quando lavori in modo simile e colonne con nome 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.

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 chiave nello schema della tabella se i dati della tabella soddisfano sull'integrità dei dati Requisiti dei vincoli di chiave esterna primaria o 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 deve garantire 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