Práticas recomendadas de SQL

Conforme descrito em Planos de execução de consulta, o compilador SQL do Cloud Spanner transforma uma instrução SQL em um plano de execução usado para receber os resultados da consulta. Veja nesta página as práticas recomendadas para criar instruções SQL a fim de ajudar o Cloud Spanner a encontrar planos de execução eficientes.

As instruções SQL de exemplo mostradas nesta página usam o esquema de amostra abaixo:

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

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

Para ver todas as referências do SQL, consulte Sintaxe da instrução, Funções e operadores e Estrutura léxica e sintaxe.

Usar parâmetros de consulta para agilizar as consultas mais executadas

As consultas parametrizadas formam uma técnica de execução de consulta que separa uma string de consulta de valores do parâmetro de consulta. Por exemplo, suponhamos que o aplicativo precise recuperar cantores que tenham lançado álbuns com determinados títulos em um ano específico. É possível escrever uma instrução SQL como a do exemplo a seguir para recuperar todos os álbuns intitulados "Love" lançados em 2017:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

Em outra consulta, é possível alterar o valor do título do álbum para "Peace":

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Se o aplicativo precisar executar muitas consultas assim, em que apenas um valor literal muda em consultas subsequentes, você precisará usar um marcador de parâmetro para esse valor. A consulta paramétrica resultante pode ser armazenada em cache e reutilizada, o que reduz custos de compilação.

Por exemplo, a consulta reescrita abaixo substitui Love por um parâmetro chamado title:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Observações sobre o uso do parâmetro de consulta:

  • Uma referência de parâmetro na consulta usa o caractere @ seguido do nome do parâmetro, que pode conter uma combinação qualquer de letras, números e sublinhados.
  • Os parâmetros podem ser exibidos em qualquer lugar onde se espera um valor literal.
  • O mesmo nome de parâmetro pode ser usado mais de uma vez em uma única instrução SQL.
  • Especifique o parâmetro de consulta e o valor para vinculá-lo no campo params de ExecuteSQL ou da API de solicitação ExecuteStreamingSQL.
  • Saiba mais sobre a sintaxe do parâmetro de consulta em Estrutura léxica e sintaxe do SQL.

Em resumo, os parâmetros de consulta beneficiam a execução de consulta das seguintes maneiras:

  • Planos pré-otimizados: as consultas que usam parâmetros podem ser executadas mais rapidamente em cada invocação porque a parametrização facilita o armazenamento em cache do plano de execução pelo Cloud Spanner.
  • Composição de consulta simplificada: não é necessário inserir caractere de escape em valores de string ao fornecê-los em parâmetros de consulta. Os parâmetros de consulta também reduzem o risco de erros de sintaxe.
  • Segurança: os parâmetros de consulta deixam as consultas mais seguras, protegendo você de vários ataques de injeção SQL. Essa proteção é especialmente importante para consultas que você constrói com base na entrada do usuário.

Compreender como o Cloud Spanner executa consultas

O Cloud Spanner permite consultar bancos de dados usando instruções SQL declarativas que especificam quais dados você quer recuperar. Se quiser compreender também como o Cloud Spanner recebe os resultados, você precisará usar planos de execução da consulta. Um plano de execução da consulta exibe o custo associado a cada etapa da consulta. Usando os custos, é possível depurar os problemas de desempenho da consulta e otimizá-la.

É possível recuperar os planos de execução da consulta por meio do Console do GCP ou com as bibliotecas de cliente.

Para receber um plano de consulta usando o Console do GCP:

  1. Abra a página "Instâncias do Cloud Spanner".

    Acessar "Instâncias do Cloud Spanner"

  2. Clique no nome da instância do Cloud Spanner e no banco de dados que você quer consultar.

  3. Clique em Consulta.

  4. Digite a consulta no campo de texto e clique em Executar consulta.

  5. Clique em Explicação.
    O Console do GCP exibe um plano de execução visual para a consulta:

    Captura de tela do console de explicação na IU

Para ver a referência completa do plano de consulta, consulte Planos de execução da consulta.

Usar índices secundários para agilizar consultas comuns

Assim como acontece com outros bancos de dados relacionais, o Cloud Spanner oferece índices secundários, que você pode usar para recuperar dados usando uma instrução SQL ou a interface de leitura do Cloud Spanner. A maneira mais comum de buscar dados de um índice é usar a interface de consulta SQL. Com um índice secundário em uma consulta SQL, é possível especificar como você quer que o Cloud Spanner receba os resultados. A especificação de um índice secundário pode acelerar a execução da consulta.

Por exemplo, suponhamos que você queira buscar os códigos de todos os cantores com um sobrenome específico. Uma maneira de escrever uma consulta SQL é:

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

Essa consulta retornaria os resultados que você espera, mas talvez levasse muito tempo para fazer isso. O tempo dependeria do número de linhas na tabela Singers e de quantos satisfazem o predicado WHERE s.LastName = 'Smith'. Sem um índice secundário que contenha a coluna LastName disponível para leitura, o plano de consulta faria a leitura de toda a tabela Singers para encontrar linhas que correspondessem ao predicado. Ler a tabela inteira é chamado de verificação completa da tabela. Essa ação é uma maneira cara de conseguir os resultados se a tabela contém apenas uma pequena porcentagem de Singers com esse sobrenome.

É possível melhorar o desempenho dessa consulta definindo um índice secundário na coluna de sobrenome:

CREATE INDEX SingersByLastName on Singers (LastName);

Como o índice secundário SingersByLastName contém a coluna de tabela indexada LastName e a coluna de chave primária SingerId, o Cloud Spanner pode buscar todos os dados da tabela com índice muito menor em vez de verificar toda a tabela Singers.

Nesse cenário, o Cloud Spanner provavelmente usaria o índice secundário SingersByLastName automaticamente ao executar a consulta. No entanto, é melhor dizer explicitamente ao Cloud Spanner para usar esse índice especificando uma diretiva de índice na cláusula FROM:

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

Agora, suponha que você também queria buscar o nome do cantor além do ID. Mesmo que a coluna FirstName não esteja contida no índice, especifique a diretiva de índice como antes:

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

Você continua recebendo um benefício de desempenho ao usar o índice porque o Cloud Spanner não precisa fazer uma verificação completa da tabela ao executar o plano de consulta. Em vez disso, ele seleciona o subconjunto de linhas que atendem ao predicado do índice SingersByLastName e, em seguida, faz uma pesquisa na tabela base Singers para buscar o nome apenas desse subconjunto de linhas.

Se quiser evitar que o Cloud Spanner precise buscar linhas da tabela base, você também poderá armazenar uma cópia da coluna FirstName no próprio índice:

CREATE INDEX SingersByLastName on Singers (LastName) STORING (FirstName);

Usar uma cláusula STORING como essa custa armazenamento extra, mas oferece as seguintes vantagens para chamadas de leitura e consultas usando o índice:

  • As consultas SQL que usam o índice e selecionam as colunas armazenadas na cláusula STORING não exigem uma vinculação extra à tabela base.
  • As chamadas de leitura que usam o índice podem ler colunas armazenadas na cláusula STORING.

Os exemplos anteriores ilustram como índices secundários podem agilizar consultas quando as linhas escolhidas pela cláusula WHERE podem ser identificadas rapidamente usando o índice secundário. Outro cenário em que índices secundários podem oferecer benefícios de desempenho é para determinadas consultas que retornam resultados ordenados. Por exemplo, suponhamos que você queira buscar todos os títulos de álbuns e as datas de lançamento deles e retorná-los em ordem crescente na data de lançamento e em ordem decrescente por título do álbum. Você poderia escrever uma consulta SQL assim:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Sem um índice secundário, esta consulta requer uma etapa de classificação potencialmente cara no plano de execução. Você pode agilizar a execução da consulta definindo esse índice secundário:

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

Em seguida, reescreva a consulta para usar o índice secundário:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Essa consulta e a definição de índice atendem aos seguintes critérios:

  • A lista de colunas na ORDER BY é um prefixo da lista de chaves do índice.
  • Todas as colunas da tabela usadas na consulta são cobertas pelo índice.

Essas duas condições são atendidas. Dessa maneira, o plano de consulta resultante remove a etapa de classificação e é executado mais rapidamente.

Os índices secundários podem agilizar consultas comuns, mas lembre que adicionar índices secundários pode agregar latência às operações de commit, porque cada índice secundário normalmente requer o envolvimento de um nó extra em cada commit. Na maioria das cargas de trabalho, não há problemas em ter alguns índices secundários. No entanto, você precisa considerar se é mais importante a latência de leitura ou de gravação e quais operações são as mais críticas para a carga de trabalho. Você também precisa comparar a carga de trabalho para garantir que ela esteja funcionando conforme o esperado.

Para a referência completa de índices secundários, consulte Índices secundários.

Escrever consultas eficientes para pesquisa de chave de intervalo

Um uso comum da consulta SQL é ler várias linhas do Cloud Spanner com base em uma lista de chaves conhecidas.

Estas são as práticas recomendadas para escrever consultas eficientes durante a busca de dados por um intervalo de chaves:

  • Se a lista de chaves for esparsa e não adjacente, use parâmetros de consulta e UNNEST para construir a consulta.

    Por exemplo, se a lista de chaves for {1, 5, 1000}, escreva a consulta assim:

    SELECT *
    FROM Table AS t
    WHERE t.Key IN UNNEST (@KeyList)
    

    Observações:

    • O operador UNNEST da matriz nivela uma matriz de entrada em linhas de elementos.

    • @KeyList é um parâmetro de consulta que pode agilizar a consulta conforme abordado na prática recomendada anterior.

  • Se a lista de chaves for adjacente e estiver dentro de um intervalo, especifique os limites mínimo e máximo do intervalo de chaves na cláusula WHERE.

    Por exemplo, se a lista de chaves for {1,2,3,4,5}, construa a consulta assim:

    SELECT *
    FROM Table AS t
    WHERE t.Key BETWEEN @min AND @max
    

    Em que @min e @max são parâmetros de consulta vinculados aos valores um e cinco, respectivamente.

    Essa consulta só será mais eficiente se as chaves no intervalo forem adjacentes. Em outras palavras, se a lista de chaves for {1, 5, 1000}, você não precisará especificar os limites mínimo e máximo, como na consulta anterior, porque a consulta resultante verificaria todos os valores entre 1 e 1.000.

Escrever consultas eficientes para vinculações

As operações de vinculação podem ser caras. Isso acontece porque JOINS podem aumentar significativamente o número de linhas que a consulta precisa verificar, o que resulta em consultas mais lentas. Além das técnicas que você está acostumado a usar em outros bancos de dados relacionais para otimizar consultas de vinculação. Aqui estão algumas práticas recomendadas para um JOIN mais eficiente durante o uso de SQL do Cloud Spanner:

  • Se possível, vincule dados em tabelas intercaladas por chave principal. Por exemplo:

    SELECT s.FirstName, a.ReleaseDate
    FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
    

    As linhas na tabela intercalada Albums serão armazenadas fisicamente nas mesmas divisões da linha pai em Singers, conforme abordado em Modelo de dados e esquema. Por isso, JOINs podem ser concluídos localmente sem enviar muitos dados pela rede.

  • Use a diretiva de vinculação se você quiser forçar a ordem de JOIN. Por exemplo:

    SELECT *
    FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
    ON s.SingerId = a.Singerid
    WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
    

    A diretiva de vinculação @{FORCE_JOIN_ORDER=TRUE} diz ao Cloud Spanner para usar a ordem de vinculação especificada na consulta (ou seja, Singers JOIN Albums, e não Albums JOIN Singers). Os resultados retornados são os mesmos, independentemente da ordem escolhida pelo Cloud Spanner. No entanto, convém usar essa diretiva de vinculação se você observar no plano de consulta que o Cloud Spanner alterou a ordem de vinculação e causou resultados indesejáveis, como resultados intermediários maiores, ou perdeu oportunidades de procurar linhas.

  • Use uma diretiva de vinculação para escolher um JOIN type. Escolher o algoritmo de vinculação certo para a consulta pode melhorar a latência, o consumo de memória ou ambos. Esta consulta demonstra a sintaxe para usar uma diretiva JOIN para escolher um HASH JOIN:

    SELECT *
    FROM Singers s JOIN@{JOIN_TYPE=HASH_JOIN} Albums AS a
    ON a.SingerId = a.SingerId
    
  • Se você está usando um HASH JOIN ou APPLY JOIN e tem uma cláusula WHERE altamente seletiva em uma lado de seu JOIN, coloque a tabela que produz o menor número de linhas como a primeira tabela na cláusula FROM da mesclagem. Isso ocorre porque, atualmente em HASH JOIN, o Cloud Spanner sempre escolhe a tabela no lado esquerdo como versão e a tabela no lado direito como sondagem. Da mesma forma, para APPLY JOIN, o Cloud Spanner seleciona a tabela do lado esquerdo como externo e a tabela do lado direito como interno. Veja mais informações sobre esses tipos de mesclagem em Hash e Apply.

Evitar grandes leituras em transações de leitura e gravação

Transações de leitura/gravação permitem uma sequência de zero ou mais leituras ou consultas SQL e podem incluir um conjunto de mutações, antes de uma chamada para commit. Para manter a consistência dos dados, o Cloud Spanner adquire bloqueios ao ler e gravar linhas nas tabelas e nos índices (leia mais detalhes sobre como bloquear em Vida útil de leituras e gravações).

Por causa da maneira como o bloqueio funciona no Cloud Spanner, executar uma consulta de leitura ou SQL que lê um grande número de linhas (por exemplo, SELECT * FROM Singers) significa que nenhuma outra transação pode gravar nas linhas lidas até que a transação seja confirmada ou cancelada. Além disso, a transação está processando um grande número de linhas. Dessa maneira, é provável que demore mais do que uma transação que lê um intervalo muito menor de linhas (por exemplo SELECT LastName FROM Singers WHERE SingerId = 7), o que exarceba ainda mais o problema e reduz a capacidade do sistema.

Por isso, você precisa tentar evitar leituras grandes (por exemplo: verificações de tabela completa ou operações de vinculação em massa) dentro das transações, a menos que esteja disposto a aceitar uma capacidade de gravação menor. Em alguns casos, o seguinte padrão pode render resultados melhores:

  1. Realize a leitura maior em uma transação somente leitura. As transações somente leitura não usam bloqueios e, portanto, proporcionam uma capacidade agregada maior.
  2. [Opcional] Se precisar fazer algum processamento nos dados recém-lidos, faça-o.
  3. Inicie uma transação de leitura/gravação.
  4. Verifique se as linhas essenciais para você não têm valores alterados desde a vez em que você executou a transação somente leitura na etapa 1.
    1. Se houve mudança, reverta sua transação e comece novamente na etapa 1.
    2. Se tudo estiver certo, confirme as mutações.

Uma maneira de garantir que você esteja evitando leituras grandes dentro de transações de leitura/gravação é examinar os planos de execução gerados pelas consultas.

Usar ORDER BY para garantir a ordem dos resultados SQL

Se estiver esperando uma determinada ordem dos resultados de uma consulta SELECT, você precisará incluir explicitamente a cláusula ORDER BY. Por exemplo: se você quiser listar todos os cantores na ordem de chave principal, use esta consulta:

SELECT * FROM Singers
ORDER BY SingerId;

O Cloud Spanner não apenas garante a ordem de resultados se a cláusula ORDER BY estiver presente na consulta. Em outras palavras, considere essa consulta sem ORDER BY:

SELECT * FROM Singers;

O Cloud Spanner não garante que os resultados dessa consulta estejam em ordem de chave primária. Além disso, a ordem dos resultados pode mudar a qualquer momento, e não há garantia de consistência de invocação para invocação.

Usar STARTS_WITH em vez de LIKE para acelerar consultas SQL parametrizadas

Como o Cloud Spanner não avalia padrões LIKE parametrizados até o momento da execução, ele precisa ler todas as linhas e avaliá-las na expressão LIKE para filtrar aquelas que não correspondem.

Nos casos em que um padrão LIKE procura correspondências que estão no início de um valor e a coluna está indexada, use STARTS_WITH em vez de LIKE. Isso permite que o Cloud Spanner otimize com mais eficácia o plano de execução de consultas.

Não recomendado:

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

Recomendação:

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Documentação do Cloud Spanner