Otimizador de consultas

O que é um otimizador de consultas?

O otimizador de consultas SQL do Cloud Spanner converte uma instrução SQL declarativa, que descreve quais dados a consulta quer, em um plano de execução imperativo, que descreve uma maneira de conseguir esses dados com precisão. O processo de transformação de uma instrução declarativa em um plano de execução de consulta envolve a execução de transformações em estruturas de árvore usadas para representar a consulta. No processo de produção de um plano de execução, o otimizador preserva o significado lógico da consulta SQL original para que as linhas corretas sejam retornadas.

Outra função importante do otimizador é produzir um plano de execução eficiente.

Como o otimizador do Cloud Spanner produz planos de execução eficientes?

O otimizador do Cloud Spanner usa uma combinação de heurística bem estabelecida e otimização baseada em custos para produzir planos eficientes. Algumas heurísticas são muito simples, como "executar filtros em máquinas remotas em vez de extrair dados para a máquina local". Outras heurísticas são mais complexas, mas ainda incorporam o princípio de mover a lógica que reduz o volume de dados mais perto dos dados. Esse princípio é particularmente importante em um sistema que fragmenta dados em várias máquinas.

Nem todas as decisões de execução podem ser tomadas de maneira eficaz usando essas regras fixas. Portanto, o otimizador do Cloud Spanner também toma decisões com base em um custo estimado de alternativas. Essas estimativas de custo são calculadas usando a estrutura da consulta, o esquema do banco de dados e as estimativas do volume de dados que serão produzidos por fragmentos da consulta. Por exemplo, o Cloud Spanner estimará quantas linhas da tabela de músicas qualificam o filtro SongGenre = "País" se esse filtro aparecer em uma consulta. Para ajudar no cálculo dessas estimativas, o Cloud Spanner coleta periodicamente estatísticas sobre os dados do usuário.

Para saber mais sobre planos de execução de consulta e como eles são usados pelo Cloud Spanner para executar consultas em um ambiente distribuído, consulte Planos de execução de consulta.

Controle de versão do otimizador de consultas

Com o tempo, o otimizador de consultas evolui, ampliando o conjunto de opções no plano de execução da consulta e melhorando a precisão das estimativas que informam essas opções. Isso leva a planos de execução de consulta mais eficientes.

O Cloud Spanner implementa as atualizações do otimizador como novas versões do otimizador de consultas. Por padrão, cada banco de dados começa a usar a versão mais recente do otimizador até 30 dias depois do lançamento dessa versão.

Para ter mais controle, apresentamos a capacidade de gerenciar a versão do Otimizador de consultas que suas consultas usam. Antes de se comprometer com a versão mais recente, você pode comparar perfis de desempenho de consulta entre versões mais antigas e mais recentes. Para saber mais, consulte Como gerenciar o otimizador de consultas.

Histórico de versões do otimizador de consultas

Veja a seguir um resumo das atualizações feitas no otimizador de consultas em cada versão.

Versão 3: 1o de agosto de 2021 (mais recente)

  • Adiciona um novo algoritmo de mesclagem, mesclagem de ativação, ativado usando um novo valor de dica de consulta JOIN METHOD.

    Dica para o extrato: SQL @{join_method=merge_join} SELECT ...

    Dica de participação: SQL SELECT ... FROM (...) JOIN@{join_method=merge_join} (...)

  • Adiciona um novo algoritmo de participação, envio de hash de transmissão push, ativado usando um novo valor de dica de consulta JOIN METHOD.

    Dica de participação: SQL SELECT ... FROM (...) JOIN@{join_method=push_broadcast_hash_join} (...)

  • Apresenta a união de mesclagem distribuída, que é ativada por padrão quando aplicável.

  • Uma pequena melhoria no desempenho de uma verificação em um GROUP BY quando não há agregação MAX ou MIN (ou HAVING MAX/MAX) na lista do SELECT. Antes dessa alteração, o Spanner carregava a coluna extra não agrupada, mesmo que ela não fosse exigida pela consulta.

    Exemplo:

    Considere a tabela a seguir.

    CREATE TABLE myTable(
      a INT64,
      b INT64,
      c INT64,
      d INT64)
    PRIMARY KEY (a, b, c);
    

    Antes dessa alteração, a consulta a seguir carregava a coluna c, mesmo que não seja exigida pela consulta.

    SELECT a, b
    FROM myTable
    GROUP BY a, b
    
  • Melhora o desempenho de algumas consultas com LIMIT quando há um operador CrossApply introduzido por junções e a consulta solicita resultados classificados com LIMIT. Após essa alteração, o otimizador aplica a classificação com o limite no lado de entrada do CrossApply primeiro.

    Exemplo:

    SELECT a2.*
    FROM Albums@{FORCE_INDEX=_BASE_TABLE} a1
    JOIN Albums@{FORCE_INDEX=_BASE_TABLE} a2 USING(SingerId)
    ORDER BY a1.AlbumId
    LIMIT 2;
    
  • Melhora o desempenho da consulta ao enviar mais cálculos por meio de JOIN.

    Envia mais cálculos, que podem incluir uma subconsulta ou construção de struct por meio da junção. Isso melhora o desempenho da consulta de algumas maneiras, como: mais cálculos podem ser feitos de maneira distribuída e mais operações que dependem dos cálculos enviados também podem ser enviadas para baixo. Por exemplo, a consulta tem um limite e a ordem de classificação depende desses cálculos, então o limite também pode ser enviado por meio de mesclagem.

    Exemplo:

    SELECT
      t.ConcertDate,
      (
        SELECT COUNT(*) FROM UNNEST(t.TicketPrices) p WHERE p > 10
      ) AS expensive_tickets,
      u.VenueName
    FROM Concerts t
    JOIN Venues u ON t.VenueId = u.VenueId
    ORDER BY expensive_tickets
    LIMIT 2;
    

Versão 2: 1o de março de 2020 (padrão)

  • Adiciona otimizações na seleção do índice.
  • Melhora o desempenho dos predicados REGEXP_CONTAINS e LIKE em determinadas circunstâncias.
  • Aprimora o desempenho de uma verificação em um GROUP BY em determinadas situações.

Versão 1: 18 de junho de 2019

  • Inclui muitas otimizações baseadas em regras, como pushdown de predicado, pushdown de limite, junção redundante e remoção de expressão redundante, para citar alguns.

  • Usa estatísticas nos dados do usuário para selecionar qual índice usar para acessar cada tabela.

Pacotes de estatísticas do otimizador de consultas

O Cloud Spanner mantém estatísticas sobre a distribuição de dados de colunas da tabela para ajudar a estimar quantas linhas serão produzidas por uma consulta. O otimizador de consultas usa essas estimativas para ajudar a escolher o melhor plano de execução de consulta. Essas estatísticas são atualizadas periodicamente pelo Cloud Spanner. Como as estatísticas são usadas para escolher planos de execução de consulta, quando as estatísticas são atualizadas, é possível que o Cloud Spanner altere o plano de consulta usado para uma consulta.

Por padrão, os bancos de dados usam automaticamente o pacote de estatísticas mais recente gerado. Você pode fixar seu banco de dados em uma versão anterior do pacote de estatísticas. Você também tem a opção de executar consultas individuais com um pacote de estatísticas que não seja o mais recente.

Coleta de lixo de pacotes de estatísticas

O Cloud Spanner atualiza os pacotes de estatísticas a cada três dias. Os pacotes mais antigos são mantidos por um período de 30 dias a partir da criação, após o qual estão sujeitos à coleta de lixo.

A tabela integrada INFORMATION_SCHEMA.SPANNER_STATISTICS do Cloud Spanner contém uma lista de pacotes de estatísticas disponíveis. Cada linha nessa tabela lista um pacote de estatísticas por nome, e o nome contém o carimbo de data/hora de criação do pacote fornecido. Cada entrada também contém um campo chamado ALLOW_GC, que define se um pacote pode ser coletado ou não como lixo.

É possível fixar todo o banco de dados em qualquer um dos pacotes listados nessa tabela. O pacote de estatísticas fixadas não será coletado como lixo e o valor de ALLOW_GC será ignorado, desde que o banco de dados esteja fixado nesse pacote. Para usar um determinado pacote de estatísticas para uma consulta individual, ele precisa ser listado com ALLOW_GC=FALSE ou fixado. Isso impede que as consultas falhem depois que o pacote de estatísticas é coletado da lixeira. O valor de ALLOW_GC pode ser alterado usando a instrução DDL ALTER STATISTICS.

Retenção de pacote e PII

Um pacote de estatísticas contém histogramas dos dados da coluna, de acordo com a prática padrão do setor. Isso ajuda o otimizador de consultas a selecionar os planos de consulta ideais. O histograma é construído usando uma pequena amostra de valores. Esse pequeno conjunto de dados pode conter PIIs.

O Cloud Spanner cria um novo pacote de estatísticas regularmente e o mantém por 30 dias, por padrão. Assim, uma pequena amostra de valores excluídos do banco de dados pode ser retida por mais 30 dias em histogramas de estatísticas. Pacotes de estatísticas fixados com a opção de banco de dados optimizer_statistics_package ou pacotes com a opção ALLOW_GC=FALSE não serão coletados como lixo. Os histogramas nesses pacotes podem conter valores excluídos do banco de dados por um período mais longo. Além disso, o conteúdo dos pacotes de estatísticas é incluído nos backups do banco de dados.

As estatísticas do otimizador são armazenadas criptografadas da mesma forma que os dados do usuário.

A quantidade total de armazenamento necessária para esses pacotes geralmente é inferior a 100 MB. Ela é contabilizada nos custos totais de armazenamento.

A seguir