Otimize o cálculo de consultas

Este documento fornece as práticas recomendadas para otimizar o desempenho das consultas.

Quando executa uma consulta, pode ver o plano de consulta na Trusted Cloud consola. Também pode pedir detalhes de execução através das INFORMATION_SCHEMA.JOBS* visualizações ou do jobs.get método da API REST.

O plano de consulta inclui detalhes sobre as fases e os passos da consulta. Estes detalhes podem ajudar a identificar formas de melhorar o desempenho das consultas. Por exemplo, se reparar que uma fase escreve muito mais resultados do que outras fases, pode significar que tem de filtrar mais cedo na consulta.

Para saber mais sobre o plano de consulta e ver exemplos de como as informações do plano de consulta podem ajudar a melhorar o desempenho das consultas, consulte o artigo Obtenha estatísticas de desempenho das consultas. Depois de resolver os problemas de desempenho das consultas, pode otimizar ainda mais a consulta realizando as seguintes tarefas:

Reduza os dados tratados

Pode reduzir os dados que precisam de ser processados através das opções descritas nas secções seguintes.

Evite SELECT *

Prática recomendada: controle a projeção consultando apenas as colunas de que precisa.

A projeção refere-se ao número de colunas que são lidas pela sua consulta. A projeção de colunas em excesso incorre em E/S e materialização adicionais (desperdiçadas) (escrita de resultados).

  • Use as opções de pré-visualização de dados. Se estiver a experimentar ou a explorar dados, use uma das opções de pré-visualização de dados em vez de SELECT *.
  • Consultar colunas específicas. A aplicação de uma cláusula LIMIT a uma consulta SELECT * não afeta a quantidade de dados lidos. A leitura de todos os bytes na tabela completa é faturada, e a consulta é contabilizada para a quota do nível gratuito. Em vez disso, consulte apenas as colunas de que precisa. Por exemplo, use SELECT * EXCEPT para excluir uma ou mais colunas dos resultados.
  • Use tabelas particionadas. Se precisar de consultas em todas as colunas de uma tabela, mas apenas num subconjunto de dados, considere o seguinte:

  • Use SELECT * EXCEPT. Consultar um subconjunto de dados ou usar SELECT * EXCEPT pode reduzir significativamente a quantidade de dados lidos por uma consulta. Além da redução de custos, o desempenho é melhorado através da redução da quantidade de E/S de dados e da quantidade de materialização necessária para os resultados da consulta.

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

Evite tabelas com carateres universais excessivos

Prática recomendada: quando consultar tabelas de carateres universais, tem de usar o prefixo mais detalhado.

Use carateres universais para consultar várias tabelas com instruções SQL concisas. As tabelas com carateres universais são uma união de tabelas que correspondem à expressão com carateres universais. As tabelas com carateres universais são úteis se o seu conjunto de dados contiver os seguintes recursos:

  • Várias tabelas com nomes semelhantes e esquemas compatíveis
  • Tabelas fragmentadas

Quando consulta uma tabela de carateres universais, especifique um caráter universal (*) após o prefixo da tabela comum. Por exemplo, FROM bigquery-public-data.noaa_gsod.gsod194* consulta todas as tabelas da década de 1940.

Os prefixos mais detalhados têm um desempenho melhor do que os prefixos mais curtos. Por exemplo, FROM bigquery-public-data.noaa_gsod.gsod194* tem um desempenho melhor do que FROM bigquery-public-data.noaa_gsod.* porque menos tabelas correspondem ao caráter universal.

Evite tabelas divididas por data

Prática recomendada: não use tabelas divididas por data (também denominadas tabelas com nome de data) em vez de tabelas com partição de tempo.

As tabelas particionadas têm um desempenho melhor do que as tabelas com nomes de datas. Quando cria tabelas divididas por data, o BigQuery tem de manter uma cópia do esquema e dos metadados para cada tabela com nome de data. Além disso, quando são usadas tabelas com nomes de datas, o BigQuery pode ter de validar as autorizações para cada tabela consultada. Esta prática também aumenta a sobrecarga de consultas e afeta o desempenho das consultas.

Evite a divisão excessiva de tabelas

Prática recomendada: evite criar demasiados fragmentos de tabelas. Se estiver a dividir tabelas por data, use tabelas particionadas por tempo.

A divisão de tabelas refere-se à divisão de grandes conjuntos de dados em tabelas separadas e à adição de um sufixo a cada nome de tabela. Se estiver a dividir tabelas por data, use tabelas particionadas por tempo em alternativa.

Devido ao baixo custo do armazenamento do BigQuery, não precisa de otimizar as tabelas em função do custo, como faria num sistema de base de dados relacional. A criação de um grande número de fragmentos de tabelas tem impactos no desempenho que superam quaisquer vantagens de custo.

As tabelas divididas requerem que o BigQuery mantenha o esquema, os metadados e as autorizações para cada divisão. Devido à sobrecarga adicional necessária para manter informações em cada fragmento, a fragmentação excessiva de tabelas pode afetar o desempenho das consultas.

A quantidade e a origem dos dados lidos por uma consulta podem afetar o desempenho e o custo da consulta.

Reduza as consultas particionadas

Prática recomendada: quando consultar uma tabela particionada, para filtrar com partições em tabelas particionadas, use as seguintes colunas:

  • Para tabelas particionadas por tempo de ingestão, use a pseudocoluna _PARTITIONTIME
  • Para tabelas particionadas, como as baseadas em colunas de unidades de tempo e intervalos de números inteiros, use a coluna de partição.

Para tabelas particionadas por unidades de tempo, a filtragem de dados com _PARTITIONTIME ou a coluna de particionamento permite-lhe especificar uma data ou um intervalo de datas. Por exemplo, a cláusula WHERE seguinte usa a pseudocoluna _PARTITIONTIME para especificar partições entre 1 de janeiro de 2016 e 31 de janeiro de 2016:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

A consulta processa os dados apenas nas partições indicadas pelo intervalo de datas. Filtrar as partições melhora o desempenho das consultas e reduz os custos.

Reduza os dados antes de usar um JOIN

Prática recomendada: reduza a quantidade de dados processados antes de uma cláusula JOIN efetuando agregações.

A utilização de uma cláusula GROUP BY com funções de agregação é computacionalmente intensiva, uma vez que estes tipos de consultas usam a função shuffle. Como estas consultas exigem muitos cálculos, tem de usar uma cláusula GROUP BY apenas quando for necessário.

Para consultas com GROUP BY e JOIN, faça a agregação mais cedo na consulta para reduzir a quantidade de dados processados. Por exemplo, a seguinte consulta executa uma JOIN em duas tabelas grandes sem qualquer filtragem prévia:

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;

Esta consulta pré-agrega as contagens de comentários, o que reduz a quantidade de dados lidos para o 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;

Use a cláusula WHERE

Prática recomendada: use uma cláusulaWHERE para limitar a quantidade de dados devolvidos por uma consulta. Sempre que possível, use as colunas BOOL, INT64, FLOAT64 ou DATE na cláusula WHERE.

As operações nas colunas BOOL, INT64, FLOAT64 e DATE são normalmente mais rápidas do que as operações nas colunas STRING ou BYTE. Sempre que possível, use uma coluna que use um destes tipos de dados na cláusula WHERE para reduzir a quantidade de dados devolvidos pela consulta.

Use vistas materializadas

Prática recomendada: use vistas materializadas para pré-calcular os resultados de uma consulta para aumentar o desempenho e a eficiência.

As vistas materializadas são vistas pré-calculadas que armazenam em cache periodicamente os resultados de uma consulta para aumentar o desempenho e a eficiência. O BigQuery tira partido dos resultados pré-calculados das vistas materializadas e, sempre que possível, lê apenas as alterações das tabelas base para calcular resultados atualizados. Pode consultar diretamente as vistas materializadas ou o otimizador do BigQuery pode usá-las para processar consultas às tabelas base.

Use o BI Engine

Prática recomendada: use o BigQuery BI Engine para acelerar as consultas ao colocar em cache os dados que usa com maior frequência.

Considere adicionar uma reserva do BI Engine ao projeto onde as consultas estão a ser calculadas. O BigQuery BI Engine usa um motor de consultas vetorizado para acelerar o SELECTdesempenho das consultas.

Use índices de pesquisa

Prática recomendada: use índices de pesquisa para pesquisas de linhas eficientes quando precisar de encontrar linhas de dados individuais em tabelas grandes.

Um índice de pesquisa é uma estrutura de dados concebida para permitir uma pesquisa muito eficiente com a função SEARCH, mas também pode acelerar as consultas que usam outros operadores e funções, como os operadores igual (=), IN ou LIKE e determinadas funções de string e JSON.

Otimize as operações de consulta

Pode otimizar as operações de consulta através das opções descritas nas secções seguintes.

Evite transformar dados repetidamente

Prática recomendada: se estiver a usar SQL para realizar operações de ETL, evite situações em que transforma repetidamente os mesmos dados.

Por exemplo, se estiver a usar SQL para cortar strings ou extrair dados através de expressões regulares, é mais eficiente materializar os resultados transformados numa tabela de destino. As funções como as expressões regulares requerem cálculos adicionais. Consultar a tabela de destino sem a sobrecarga de transformação adicionada é muito mais eficiente.

Evite várias avaliações dos mesmos CTEs

Prática recomendada: use linguagem processual, variáveis, tabelas temporárias e tabelas com expiração automática para manter os cálculos e usá-los mais tarde na consulta.

Quando a sua consulta contém expressões de tabelas comuns (CTEs) que são usadas em vários locais na consulta, podem acabar por ser avaliadas sempre que são referenciadas. O otimizador de consultas tenta detetar partes da consulta que podem ser executadas apenas uma vez, mas isto pode nem sempre ser possível. Como resultado, a utilização de uma CTE pode não ajudar a reduzir a complexidade das consultas internas e o consumo de recursos.

Pode armazenar o resultado de uma CTE numa variável escalar ou numa tabela temporária, consoante os dados que a CTE devolve.

Evite uniões e subconsultas repetidas

Prática recomendada: evite juntar repetidamente as mesmas tabelas e usar as mesmas subconsultas.

Em vez de juntar os dados repetidamente, pode ser mais eficiente usar dados repetidos aninhados para representar as relações. Os dados repetidos aninhados evitam o impacto no desempenho da largura de banda de comunicação que uma junção requer. Também lhe permite poupar os custos de E/S que incorre ao ler e escrever repetidamente os mesmos dados. Para mais informações, consulte o artigo Use campos aninhados e repetidos.

Da mesma forma, a repetição das mesmas subconsultas afeta o desempenho através do processamento repetitivo de consultas. Se estiver a usar as mesmas subconsultas em várias consultas, considere materializar os resultados das subconsultas numa tabela. Em seguida, consuma os dados materializados nas suas consultas.

A materialização dos resultados da subconsulta melhora o desempenho e reduz a quantidade geral de dados que o BigQuery lê e escreve. O pequeno custo de armazenar os dados materializados compensa o impacto no desempenho da E/S repetida e do processamento de consultas.

Otimize os padrões de junção

Prática recomendada: para consultas que juntam dados de várias tabelas, otimize os padrões de junção começando pela tabela maior.

Quando cria uma consulta com uma cláusula JOIN, considere a ordem em que está a unir os dados. O otimizador de consultas GoogleSQL determina que tabela deve estar em que lado da junção. Como prática recomendada, coloque primeiro a tabela com o maior número de linhas, seguida da tabela com o menor número de linhas e, em seguida, coloque as tabelas restantes por ordem decrescente do tamanho.

Quando tem uma tabela grande no lado esquerdo do JOIN e uma pequena no lado direito do JOIN, é criada uma junção de transmissão. Uma junção de transmissão envia todos os dados na tabela mais pequena para cada ranhura que processa a tabela maior. É aconselhável participar primeiro na transmissão.

Para ver o tamanho das tabelas no seu JOIN, consulte Obtenha informações sobre tabelas.

Especifique restrições de chaves principais e chaves externas

Prática recomendada: especifique restrições de chaves no esquema da tabela quando os dados da tabela satisfazem os requisitos de integridade de dados das restrições de chaves primárias ou chaves externas. O motor de consultas pode usar as restrições de chaves para otimizar os planos de consultas.

O BigQuery não verifica automaticamente a integridade dos dados, pelo que tem de garantir que os seus dados cumprem as restrições especificadas no esquema da tabela. Se não mantiver a integridade dos dados nas tabelas com restrições especificadas, os resultados da consulta podem ser imprecisos.

Otimize a cláusula ORDER BY

Prática recomendada: quando usar a cláusula ORDER BY, certifique-se de que segue as práticas recomendadas:

  • Use ORDER BY na consulta mais externa ou em cláusulas de janela. Envie operações complexas para o final da consulta. A colocação de uma cláusula ORDER BY no meio de uma consulta afeta significativamente o desempenho, a menos que esteja a ser usada numa função de janela.

    Outra técnica para ordenar a sua consulta é enviar operações complexas, como expressões regulares e funções matemáticas, para o final da consulta. Esta técnica reduz os dados a serem processados antes de as operações complexas serem realizadas.

  • Use uma cláusula LIMIT. Se estiver a ordenar um número muito grande de valores, mas não precisar de que todos sejam devolvidos, use uma cláusula LIMIT. Por exemplo, a consulta seguinte ordena um conjunto de resultados muito grande e gera um erro Resources exceeded. A consulta ordena pela coluna title em mytable. A coluna title contém milhões de valores.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    Para remover o erro, use uma consulta como a seguinte:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • Use uma função de janela. Se estiver a ordenar um número muito grande de valores, use uma função de janela e limite os dados antes de chamar a função de janela. Por exemplo, a seguinte consulta lista os dez utilizadores mais antigos do Stack Overflow e a respetiva classificação, sendo que a conta mais antiga é classificada como a mais baixa:

    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;

    Esta consulta demora aproximadamente 15 segundos a ser executada. Esta consulta usa LIMIT no final da consulta, mas não na função de janela DENSE_RANK() OVER. Por este motivo, a consulta requer que todos os dados sejam ordenados num único nó de trabalho.

    Em alternativa, deve limitar o conjunto de dados antes de calcular a função de janela para melhorar o desempenho:

    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;

    Esta consulta demora aproximadamente 2 segundos a ser executada, ao mesmo tempo que devolve os mesmos resultados que a consulta anterior.

    Uma ressalva é que a função DENSE_RANK() classifica os dados ao longo dos anos, pelo que, para classificar dados que abrangem vários anos, estas consultas não dão resultados idênticos.

Divida consultas complexas em consultas mais pequenas

Prática recomendada: tire partido das capacidades de consultas com várias declarações e dos procedimentos armazenados para realizar os cálculos concebidos como uma consulta complexa em vez de várias consultas mais pequenas e simples.

As consultas complexas, as funções REGEX e as subconsultas ou as junções em camadas podem ser lentas e exigir muitos recursos para serem executadas. Tentar ajustar todos os cálculos numa enorme declaração SELECT, por exemplo, para a tornar numa vista, é, por vezes, um antipadrão e pode resultar numa consulta lenta e com utilização intensiva de recursos. Em casos extremos, o plano de consulta interno torna-se tão complexo que o BigQuery não consegue executá-lo.

Dividir uma consulta complexa permite materializar resultados intermédios em variáveis ou tabelas temporárias. Em seguida, pode usar estes resultados intermédios noutras partes da consulta. É cada vez mais útil quando estes resultados são necessários em mais do que um local da consulta.

Muitas vezes, permite-lhe expressar melhor a verdadeira intenção de partes da consulta, sendo as tabelas temporárias os pontos de materialização de dados.

Use campos aninhados e repetidos

Para obter informações sobre como desnormalizar o armazenamento de dados através de campos aninhados e repetidos, consulte o artigo Use campos aninhados e repetidos.

Use tipos de dados INT64 em associações

Prática recomendada: use tipos de dados INT64 em junções em vez de tipos de dados STRING para reduzir o custo e melhorar o desempenho da comparação.

O BigQuery não indexa chaves primárias como as bases de dados tradicionais. Por isso, quanto mais ampla for a coluna de união, mais tempo demora a comparação. Por conseguinte, os tipos de dados INT64 nas junções são mais baratos e eficientes do que os tipos de dados STRING.

Reduza os resultados das consultas

Pode reduzir os resultados da consulta através das opções descritas nas secções seguintes.

Materialize conjuntos de resultados grandes

Prática recomendada: considere materializar grandes conjuntos de resultados numa tabela de destino. A gravação de grandes conjuntos de resultados tem impactos no desempenho e nos custos.

O BigQuery limita os resultados em cache a aproximadamente 10 GB comprimidos. As consultas que devolvem resultados maiores ultrapassam este limite e, frequentemente, resultam no seguinte erro: Response too large.

Este erro ocorre frequentemente quando seleciona um grande número de campos de uma tabela com uma quantidade considerável de dados. Também podem ocorrer problemas na escrita de resultados em cache em consultas no estilo ETL que normalizam os dados sem redução nem agregação.

Pode ultrapassar a limitação do tamanho dos resultados em cache através das seguintes opções:

  • Utilize filtros para limitar o conjunto de resultados
  • Use uma cláusula LIMIT para reduzir o conjunto de resultados, especialmente se estiver a usar uma cláusula ORDER BY
  • Escrever os dados de saída numa tabela de destino

Pode paginar os resultados através da API REST do BigQuery. Para mais informações, consulte o artigo Paginação de dados de tabelas.

Evite padrões anti-SQL

As seguintes práticas recomendadas fornecem orientações sobre como evitar padrões de consultas que afetam o desempenho no BigQuery.

Evite associações próprias

Prática recomendada: em vez de usar junções automáticas, use uma função de janela (analítica) ou o operador PIVOT.

Normalmente, as junções automáticas são usadas para calcular relações dependentes das linhas. O resultado da utilização de uma junção automática é que potencialmente eleva ao quadrado o número de linhas de saída. Este aumento nos dados de saída pode causar um desempenho fraco.

Evite cruzamentos de uniões

Prática recomendada: evite junções que gerem mais resultados do que entradas. Quando é necessário um CROSS JOIN, pré-agregue os seus dados.

As junções cruzadas são consultas em que cada linha da primeira tabela é unida a todas as linhas da segunda tabela, com chaves não únicas em ambos os lados. O pior resultado é o número de linhas na tabela à esquerda multiplicado pelo número de linhas na tabela à direita. Em casos extremos, a consulta pode não terminar.

Se a tarefa de consulta for concluída, a explicação do plano de consulta mostra as linhas de saída em comparação com as linhas de entrada. Pode confirmar um produto cartesiano modificando a consulta para imprimir o número de linhas de cada lado da cláusula JOIN, agrupadas pela chave de junção. Também pode verificar as estatísticas de desempenho no gráfico de execução de consultas para uma junção de cardinalidade elevada.

Para evitar problemas de desempenho associados a uniões que geram mais saídas do que entradas:

  • Use uma cláusula GROUP BY para agregar previamente os dados.
  • Use uma função de janela. As funções de janela são muitas vezes mais eficientes do que usar uma união cruzada. Para mais informações, consulte o artigo sobre funções de janela.

Evite declarações DML que atualizam ou inserem linhas únicas

Prática recomendada: evite declarações DML que atualizam ou inserem linhas únicas. Agrupe as atualizações e as inserções.

A utilização de declarações DML específicas de pontos é uma tentativa de tratar o BigQuery como um sistema de processamento de transações online (OLTP). O BigQuery foca-se no processamento analítico online (OLAP) através de análises de tabelas e não de pesquisas de pontos. Se precisar de um comportamento semelhante ao OLTP (atualizações ou inserções de uma única linha), considere uma base de dados concebida para suportar exemplos de utilização de OLTP, como o Cloud SQL.

As declarações DML do BigQuery destinam-se a atualizações em massa. UPDATE e as declarações DML no BigQuery estão orientadas para reescritas periódicas dos seus dados e não para mutações de linhas únicas.DELETE A declaração INSERTDML destina-se a ser usada com moderação. As inserções consomem as mesmas quotas de modificação que as tarefas de carregamento. Se o seu exemplo de utilização envolver inserções frequentes de linhas únicas, considere fazer o streaming dos seus dados.

Se o processamento em lote das suas declarações UPDATE gerar muitas tuplas em consultas muito longas, pode aproximar-se do limite de comprimento da consulta de 256 KB. Para contornar o limite de comprimento da consulta, pondere se as suas atualizações podem ser processadas com base num critério lógico em vez de uma série de substituições diretas de tuplos.

Por exemplo, pode carregar o conjunto de registos de substituição noutra tabela e, em seguida, escrever a declaração DML para atualizar todos os valores na tabela original se as colunas não atualizadas corresponderem. Por exemplo, se os dados originais estiverem na tabela t e as atualizações forem preparadas na tabela u, a consulta teria o seguinte aspeto:

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

Use nomes de alias para colunas com nomes semelhantes

Prática recomendada: use alias de colunas e tabelas quando trabalhar com colunas com nomes semelhantes em várias consultas, incluindo subconsultas.

Os alias ajudam a identificar as colunas e as tabelas referenciadas, além da referência inicial da coluna. A utilização de aliases pode ajudar a compreender e resolver problemas na sua consulta SQL, incluindo encontrar as colunas que são usadas em subconsultas.

O que se segue?