Planos de execução de consulta

Observação sobre a interface PostgreSQL:os exemplos neste tópico são destinados a bancos de dados de dialeto GoogleSQL.

Visão geral

Nesta página, explicamos os conceitos por trás dos planos de execução de consultas e como o Spanner os usa para realizar consultas em um ambiente distribuído. Para saber como recuperar um plano de execução para uma consulta específica usando o console doGoogle Cloud , consulte Noções básicas sobre como o Spanner executa consultas. Também é possível visualizar planos de consulta históricos de amostra para comparar o desempenho da consulta ao longo do tempo. Para saber mais, consulte Planos de consulta de amostra.

O Spanner usa instruções SQL declarativas para consultar os bancos de dados. As instruções SQL definem o que o usuário quer sem especificar como obter os resultados. Um plano de execução de consulta é o conjunto de etapas para alcançar os resultados. Para uma determinada instrução SQL, pode haver várias maneiras de conseguir os resultados. O otimizador de consultas do Spanner avalia diferentes planos de execução e escolhe o mais eficiente. Em seguida, o Spanner usa o plano de execução para recuperar os resultados. Os planos de execução são compatíveis com bancos de dados de dialeto GoogleSQL e PostgreSQL.

Para mais informações sobre o otimizador de consultas, consulte Visão geral do otimizador de consultas.

Conceitualmente, um plano de execução é uma árvore de operadores relacionais. Cada operador lê linhas das entradas e produz linhas de saída. O resultado do operador na raiz da execução é retornado como resultado da consulta SQL.

Por exemplo, considere a seguinte consulta:

SELECT s.SongName FROM Songs AS s;

Essa consulta resulta em um plano de execução que pode ser visualizado como:

Neste exemplo, o Spanner executa o plano de consulta de cima para baixo, e os dados fluem de baixo para cima. O operador distributed union é executado primeiro. As linhas azuis tracejadas indicam que esse operador pode fazer uma chamada remota. Seja local ou remotamente, ele invoca o operador serialize result e continua na árvore. Quando o operador table scan produz uma linha, ele retorna imediatamente o controle para o operador serialize result. Esse processo continua até que a consulta verifique todas as linhas.

As consultas e os planos de execução dessa página se baseiam no esquema de banco de dados abaixo:

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE
) PRIMARY KEY(SingerId);

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25)
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC), INTERLEAVE IN Albums;

CREATE INDEX SongsBySongName ON Songs(SongName);

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

É possível usar as seguintes instruções da linguagem de manipulação de dados (DML) para adicionar dados a estas tabelas:

INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate)
VALUES (1, "Marc", "Richards", "1970-09-03"),
       (2, "Catalina", "Smith", "1990-08-17"),
       (3, "Alice", "Trentor", "1991-10-02"),
       (4, "Lea", "Martin", "1991-11-09"),
       (5, "David", "Lomond", "1977-01-29");

INSERT INTO Albums (SingerId, AlbumId, AlbumTitle)
VALUES (1, 1, "Total Junk"),
       (1, 2, "Go, Go, Go"),
       (2, 1, "Green"),
       (2, 2, "Forever Hold Your Peace"),
       (2, 3, "Terrified"),
       (3, 1, "Nothing To Do With Me"),
       (4, 1, "Play");

INSERT INTO Songs (SingerId, AlbumId, TrackId, SongName, Duration, SongGenre)
VALUES (2, 1, 1, "Let's Get Back Together", 182, "COUNTRY"),
       (2, 1, 2, "Starting Again", 156, "ROCK"),
       (2, 1, 3, "I Knew You Were Magic", 294, "BLUES"),
       (2, 1, 4, "42", 185, "CLASSICAL"),
       (2, 1, 5, "Blue", 238, "BLUES"),
       (2, 1, 6, "Nothing Is The Same", 303, "BLUES"),
       (2, 1, 7, "The Second Time", 255, "ROCK"),
       (2, 3, 1, "Fight Story", 194, "ROCK"),
       (3, 1, 1, "Not About The Guitar", 278, "BLUES");

Observação: é possível executar consultas e recuperar planos de execução, mesmo que as tabelas não tenham dados.

É difícil conseguir planos de execução eficientes porque o Spanner divide os dados em divisões. As divisões podem se mover independentemente umas das outras, e o Spanner pode atribuí-las a servidores diferentes, que podem estar em locais físicos diferentes. Para avaliar planos de execução em dados distribuídos, o Spanner executa as seguintes tarefas:

• Executa subplanos localmente em servidores que armazenam os dados.
• Orquestra e agrega várias execuções remotas com remoção agressiva de distribuição.

Para ativar esse modelo, o Spanner usa o operador distributed union, com as variantes distributed cross apply e distributed outer apply.

Como o Spanner executa consultas

Conceitualmente, a execução da consulta flui de cima para baixo, e os resultados da consulta fluem de baixo para cima. Este diagrama mostra esse padrão:

O Spanner compila uma consulta SQL em um plano de execução. Para minimizar o número de saltos até os dados consultados, o Spanner escolhe um servidor raiz inicial e envia o plano para ele. O servidor raiz realiza as seguintes ações:

• Inicia a execução remota de subplanos, se necessário.
• Aguarda os resultados das execuções remotas.
• Processa as etapas de execução local restantes, como a agregação de resultados.
• Retorna os resultados da consulta.

Um servidor remoto que recebe um subplano atua como servidor raiz do subplano, seguindo o mesmo modelo que o servidor raiz principal. Esse modelo de execução resulta em uma árvore de execuções remotas.

Principais métricas de desempenho

O visualizador de planos de consulta do Spanner Studio mostra métricas de resumo para a execução da consulta. As métricas de resumo representam o agregado de métricas de todos os operadores individuais executados durante a consulta. A tabela a seguir descreve as principais métricas de performance:

Métrica	Descrição
Tempo total decorrido	O tempo total de execução da consulta.
Tempo de CPU	O tempo total de CPU gasto em todos os servidores envolvidos na execução da consulta. Como algumas partes da execução da consulta podem continuar em paralelo, o tempo de CPU pode ser maior que o tempo decorrido total.

Planos de consulta de amostra

Com os planos de consulta de amostra do Spanner, é possível ver amostras de planos de consulta históricos e comparar o desempenho de uma consulta ao longo do tempo. Os planos de consulta de amostra não estão disponíveis para todas as consultas. O Spanner pode amostrar apenas consultas que consomem muita CPU. O Spanner retém amostras de planos de consulta por 30 dias. Para encontrar amostras de planos de consulta na página Query Insights do console doGoogle Cloud , consulte Ver amostras de planos de consulta.

A anatomia de um plano de consulta de amostra é igual à de um plano de execução de consulta comum. Para mais informações sobre como entender os planos visuais e usá-los para depurar suas consultas, consulte Um tour pelo visualizador de planos de consulta.

Casos de uso comuns para planos de consulta de amostra

Os casos de uso comuns de planos de consulta amostrados incluem:

• Observar mudanças no plano de consulta devido a mudanças no esquema (por exemplo, adicionar ou remover um índice).
• Observar mudanças no plano de consulta devido a uma atualização da versão do otimizador.
• Observar mudanças no plano de consulta devido a novas estatísticas do otimizador. O Spanner coleta essas estatísticas automaticamente a cada três dias, ou você pode atualizá-las manualmente usando o comando ANALYZE.

Se o desempenho da consulta variar significativamente ao longo do tempo ou se você quiser melhorar o desempenho, consulte as práticas recomendadas de SQL para criar instruções de consulta otimizadas que ajudem o Spanner a encontrar planos de execução eficientes.

Padrões comuns

Os exemplos a seguir ilustram padrões comuns em planos de execução do Spanner.

Consultas agregadas

Uma consulta de agregação implementa a lógica GROUP BY.

Por exemplo, a consulta a seguir calcula as contagens de músicas por cantor:

SELECT s.SingerId, COUNT(*) AS SongCount
FROM Songs AS s
WHERE s.SingerId < 100
GROUP BY s.SingerId;

Estes são os resultados:

+----------+-----------+
| SingerId | SongCount |
+----------+-----------+
|        3 |         1 |
|        2 |         8 |
+----------+-----------+

Conceitualmente, o plano de execução é o seguinte:

O Spanner envia o plano para um servidor raiz que coordena a execução da consulta e distribui os subplanos de forma remota.

Esse plano de execução começa com um operador distributed union que distribui subplanos para servidores remotos com divisões que atendem a SingerId < 100. Depois que a verificação em divisões individuais é concluída, o operador stream aggregate agrega linhas para receber as contagens de cada SingerId. O operador serialize result serializa o resultado. Por fim, o distributed union combina todos os resultados e retorna os resultados da consulta.

Para mais informações sobre agregações, consulte Operadores de agregação.

Consultas de junções colocalizadas

Em tabelas intercaladas, o Spanner armazena fisicamente as linhas relacionadas juntas. Uma junção colocalizada é uma junção entre tabelas intercaladas. As mesclagens colocalizadas podem melhorar o desempenho em comparação com as que exigem índices ou mesclagens reversas.

Por exemplo, pense na seguinte consulta:

SELECT al.AlbumTitle, so.SongName
FROM Albums AS al, Songs AS so
WHERE al.SingerId = so.SingerId AND
      al.AlbumId = so.AlbumId;

Essa consulta pressupõe que Songs está intercalado em Albums.

Estes são os resultados:

+-----------------------+--------------------------+
| AlbumTitle            | SongName                 |
+-----------------------+--------------------------+
| Nothing To Do With Me | Not About The Guitar     |
| Green                 | The Second Time          |
| Green                 | Starting Again           |
| Green                 | Nothing Is The Same      |
| Green                 | Let's Get Back Together  |
| Green                 | I Knew You Were Magic    |
| Green                 | Blue                     |
| Green                 | 42                       |
| Terrified             | Fight Story              |
+-----------------------+--------------------------+

Este é o plano de execução:

Esse plano de execução começa com um operador distributed union que distribui subplanos para servidores remotos, que contêm divisões da tabela Albums. Como Songs é intercalado em Albums, cada servidor remoto executa todo o subplano localmente sem realizar uma mesclagem em servidores diferentes.

Os subplanos contêm um operador cross apply. Cada operador cross apply executa um table scan na tabela Albums para recuperar SingerId, AlbumId e AlbumTitle. Em seguida, o operador cross apply mapeia a saída da verificação de tabela para a saída de uma verificação de índice no índice SongsBySingerAlbumSongNameDesc. Esse índice está sujeito a um filtro, que exige que o SingerId no índice corresponda ao SingerId do resultado da verificação de tabela. Cada operador cross apply envia os resultados para um operador serialize result, que serializa os dados de AlbumTitle e SongName e retorna os resultados para os "distributed union" locais. O distributed union agrega os resultados dos distributed union locais e os retorna como resultado da consulta.

Consultas de índice e junção de volta

O exemplo anterior usa uma mesclagem em duas tabelas, em que uma tabela é intercalada na outra. Se duas tabelas, ou uma tabela e um índice, não estiverem intercaladas, os planos de execução serão mais complexos e menos eficientes.

Considere um índice criado com o seguinte comando:

CREATE INDEX SongsBySongName ON Songs(SongName);

Para usar esse índice em uma consulta, selecione uma das seguintes opções de dialeto de banco de dados:

GoogleSQL

SELECT s.SongName, s.Duration
FROM Songs@{force_index=SongsBySongName} AS s
WHERE STARTS_WITH(s.SongName, "B");

PostgreSQL

SELECT s.SongName, s.Duration
FROM Songs /*@ force_index=SongsBySongName */ AS s
WHERE STARTS_WITH(s.SongName, "B");

Estes são os resultados:

+----------+----------+
| SongName | Duration |
+----------+----------+
| Blue     |      238 |
+----------+----------+

Este é o plano de execução:

O plano de execução resultante é complexo porque o índice SongsBySongName não contém a coluna Duration. Para receber o valor Duration, o Spanner mescla os resultados indexados à tabela Songs. Essa é uma mesclagem, mas não é colocalizada porque a tabela Songs e o índice global SongsBySongName não estão intercalados. Se os dados não estiverem colocados, o Spanner vai realizar otimizações para acelerar a execução. Essas otimizações tornam o plano de execução mais complexo do que o exemplo anterior de mesclagem colocalizada.

O operador principal é um distributed cross apply. O lado de entrada desse operador recebe lotes de linhas do índice SongsBySongName que satisfazem o predicado STARTS_WITH(s.SongName, "B"). O operador distributed cross apply mapeia esses lotes para servidores remotos que contêm divisões com os dados de Duration. Para recuperar a coluna Duration, os servidores remotos realizam uma verificação de tabela. Essa verificação usa a condição de filtro Condition:($Songs_key_TrackId' = $batched_Songs_key_TrackId), que une TrackId da tabela Songs às colunas TrackId das linhas agrupadas do índice SongsBySongName.

O Spanner agrega os resultados na resposta final da consulta. Por sua vez, o lado de entrada do operador distributed cross apply contém um par distributed union ou local distributed union para avaliar as linhas do índice que atendem ao predicado STARTS_WITH.

Considere uma consulta um pouco diferente que não selecione a coluna s.Duration:

GoogleSQL

SELECT s.SongName
FROM Songs@{force_index=SongsBySongName} AS s
WHERE STARTS_WITH(s.SongName, "B");

PostgreSQL

SELECT s.SongName
FROM Songs /*@ force_index=SongsBySongName */ AS s
WHERE STARTS_WITH(s.SongName, "B");

O plano de execução a seguir mostra que essa consulta usa totalmente o índice:

O plano de execução não requer uma mesclagem de retorno porque o índice contém todas as colunas solicitadas pela consulta.

Verificação sequencial versus buscas dispersas

Ler um intervalo contíguo de linhas é significativamente mais rápido do que ler o mesmo número de linhas dispersas.

Por exemplo, a consulta a seguir soma a duração das músicas de um conjunto especificado de cantores. Essa consulta realiza buscas para cada ID de cantor especificado. Para ver quantas buscas a consulta realizou, consulte a estatística Contagem de buscas.

SELECT SUM(Duration)
FROM Songs
WHERE SingerId IN (1, 3, 9, 10, 15);

No entanto, se você estruturar a consulta para ler uma sequência contígua de IDs de cantores, poderá melhorar o desempenho realizando uma leitura sequencial da tabela Songs. A tabela Songs contém SingerId como parte da chave primária.

SELECT SUM(Duration)
FROM Songs
WHERE SingerId BETWEEN 1 AND 5;

Essa diferença é relevante ao escolher um método de mesclagem e ao tomar decisões de design de esquema, como escolher uma chave primária. Para mais informações sobre como escolher um método de mesclagem, consulte Como as mesclagens interagem com as verificações.

Como compartilhar uniões distribuídas

Cada tabela ou índice em uma consulta precisa ter um operador distribuído correspondente. Esse operador é distributed union ou distributed apply. Se duas tabelas ou índices forem intercalados no mesmo pai, o otimizador de consultas poderá usar um único operador distributed union para as duas tabelas ou índices.

A seguir

• Saiba mais sobre os operadores de execução de consulta.
• Saiba mais sobre o otimizador de consultas do Spanner.
• Saiba como gerenciar o otimizador de consultas.