Guia SQL: Como escrever consultas melhor (parte 2)

Artigo continuado Guia SQL: Como escrever consultas melhor (parte 1)

Da solicitação aos planos de execução

Saber que os antipatterns não são estáticos e evoluem à medida que você cresce como desenvolvedor de SQL, e o fato de haver muitas coisas a considerar ao pensar em alternativas também significa que evitar antipatterns e reescrever consultas pode ser bastante difícil tarefa. Qualquer ajuda pode ser útil, e é por isso que uma abordagem mais estruturada para a otimização de consultas usando algumas ferramentas pode ser mais eficaz.

Também deve ser observado que alguns dos antipadrões mencionados na última seção estão enraizados em problemas de desempenho, como os operadores AND , OR e NOT e sua ausência ao usar índices. Pensar no desempenho requer não apenas uma abordagem mais estruturada, mas também uma abordagem mais profunda.

No entanto, essa abordagem estruturada e aprofundada será baseada principalmente no plano de consulta, que, como você se lembra, é o resultado de uma consulta analisada primeiro em uma "árvore de análise" ou "árvore de análise" e determina exatamente qual algoritmo usado para cada operação e como sua execução é coordenada.

Otimização de consulta

Conforme você lê a introdução, pode ser necessário verificar e configurar os planos que são compilados manualmente pelo otimizador. Nesses casos, você precisará analisar sua solicitação novamente, observando o plano de solicitação.

Para acessar esse plano, você deve usar as ferramentas fornecidas pelo sistema de gerenciamento de banco de dados. As seguintes ferramentas podem estar à sua disposição:

• Alguns pacotes contêm ferramentas que geram uma representação gráfica do plano de consulta. Considere o seguinte exemplo:
  
  
  
• Outras ferramentas fornecerão uma descrição textual do plano de consulta. Um exemplo é a instrução EXPLAIN PLAN no Oracle, mas o nome da instrução depende do DBMS com o qual você está trabalhando. Em outros lugares, você pode encontrar EXPLAIN (MySQL, PostgreSQL) ou EXPLAIN QUERY PLAN (SQLite).

Observe que, ao trabalhar com o PostgreSQL, você pode fazer uma distinção entre EXPLAIN , onde você simplesmente obtém uma descrição que informa como o planejador pretende executar a consulta sem executá-la, enquanto EXPLAIN ANALYZE realmente executa a consulta e retorna a análise. planos de solicitação esperados e reais. De um modo geral, um plano de execução real é um plano no qual uma solicitação é realmente executada, enquanto um plano de execução de avaliação determina o que ele fará sem atender à solicitação. Embora isso seja logicamente equivalente, o plano de execução real é muito mais útil, pois contém informações e estatísticas adicionais sobre o que realmente aconteceu quando a solicitação foi executada.

No restante desta seção, você aprenderá mais sobre EXPLAIN e ANALYZE , além de como usá-los para obter mais informações sobre o plano de consulta e seu possível desempenho. Para fazer isso, comece com alguns exemplos nos quais você trabalhará com duas tabelas: one_million e half_million .

Você pode obter as informações atuais da tabela one_million usando EXPLAIN ; Coloque-o diretamente acima da solicitação e, após executá-la, ele retornará o plano de consulta para você:

 EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18584.82 rows=1025082 width=36) (1 row) 

Nesse caso, você vê que o custo da solicitação é 0.00..18584.82 e o número de linhas é 1025082 . A largura do número de colunas é 36 .

Além disso, você pode atualizar as estatísticas usando ANALYZE .

 ANALYZE one_million; EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (1 row) 

Além de EXPLAIN e ANALYZE , você também pode obter o tempo de execução real com EXPLAIN ANALYZE :

 EXPLAIN ANALYZE SELECT * FROM one_million; QUERY PLAN ___________________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.015..1207.019 rows=1000000 loops=1) Total runtime: 2320.146 ms (2 rows) 

A desvantagem de usar EXPLAIN ANALYZE é que a consulta é realmente executada, portanto, tenha cuidado com isso!

Até o momento, todos os algoritmos que você viu são Seq Scan (Sequential Scan) ou Full Table Scan: essa é uma varredura realizada em um banco de dados em que cada linha da tabela varrida é lida em ordem serial e as colunas encontradas são verificadas quanto a conformidade com a condição ou não. Em termos de desempenho, as verificações sequenciais definitivamente não são o melhor plano de execução, porque você ainda está fazendo uma verificação completa da tabela. No entanto, isso não é tão ruim quando a tabela não cabe na memória: as leituras sequenciais são bem rápidas, mesmo em discos lentos.

Você aprenderá mais sobre isso mais tarde, quando falarmos sobre verificação de índice.

No entanto, existem outros algoritmos. Tome, por exemplo, este plano de consulta para uma conexão:

 EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN _________________________________________________________________ Hash Join (cost=15417.00..68831.00 rows=500000 width=42) (actual time=1241.471..5912.553 rows=500000 loops=1) Hash Cond: (one_million.counter = half_million.counter) -> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1) -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB -> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.008..601.128 rows=500000 loops=1) Total runtime: 6468.337 ms 

Você vê que o otimizador de consultas escolheu Hash Join aqui! Lembre-se desta operação, pois você precisará dela para avaliar a complexidade de tempo de sua solicitação. Por enquanto, observe que não há índice em half_million.counter , que adicionamos no seguinte exemplo:

 CREATE INDEX ON half_million(counter); EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN ________________________________________________________________ Merge Join (cost=4.12..37650.65 rows=500000 width=42) (actual time=0.033..3272.940 rows=500000 loops=1) Merge Cond: (one_million.counter = half_million.counter) -> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1) -> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5) (actual time=0.010..683.674 rows=500000 loops=1) Total runtime: 3833.310 ms (5 rows) 

Você percebe que, ao criar o índice, o otimizador de consulta decidiu agora usar a Merge join ao varrer o Index Scan Índice.

Observe a diferença entre as verificações de índice e as de tabela completa ou verificações seqüenciais: a primeira, também chamada de “verificações de tabela”, verifica os dados ou as páginas de índice para encontrar os registros correspondentes, enquanto a segunda verifica cada linha da tabela.

Você vê que o tempo de execução geral diminuiu e o desempenho deve ser melhor, mas há duas varreduras de índice, o que torna a memória mais importante aqui, especialmente se a tabela não se encaixar nela. Nesses casos, você deve primeiro executar uma varredura completa do índice, que é realizada usando leituras sequenciais rápidas e não é um problema, mas você tem muitas operações de leitura aleatória para selecionar linhas pelo valor do índice. Essas são operações de leitura aleatória que geralmente são várias ordens de magnitude mais lentas que as seqüenciais. Nesses casos, uma verificação completa da tabela realmente acontece mais rapidamente que uma verificação completa do índice.

Dica: se você quiser saber mais sobre EXPLAIN ou considerar exemplos com mais detalhes, leia a seção Compreendendo o Explique de Guillaume Lelarge.

Complexidade de tempo e Big O

Agora que você analisou brevemente o plano de consulta, pode começar a se aprofundar e pensar em desempenho em termos mais formais usando a teoria da complexidade computacional. Este é um campo da ciência da computação teórica, que, entre outras coisas, se concentra na classificação de problemas computacionais, dependendo de sua complexidade; Esses problemas computacionais podem ser algoritmos, mas também consultas.

No entanto, para consultas, elas não são necessariamente classificadas de acordo com sua complexidade, mas, dependendo do tempo necessário para concluí-las e obter resultados. Isso é chamado de complexidade de tempo e você pode usar a grande notação O para formular ou medir esse tipo de complexidade.

Com a designação O grande, você expressa o tempo de execução em termos da rapidez com que cresce em relação à entrada, à medida que a entrada se torna arbitrariamente grande. A notação O grande exclui coeficientes e membros de ordem inferior, para que você possa se concentrar na parte importante do tempo de execução da sua consulta: sua taxa de crescimento. Quando expressos dessa maneira, descartando os coeficientes e os termos de ordem inferior, eles dizem que a complexidade do tempo é descrita assintoticamente. Isso significa que o tamanho da entrada vai para o infinito.

Em uma linguagem de banco de dados, a complexidade determina quanto tempo leva para concluir uma consulta conforme o tamanho das tabelas de dados e, portanto, o banco de dados aumenta.

Observe que o tamanho do seu banco de dados aumenta não apenas com o aumento da quantidade de dados nas tabelas, mas o fato de que existem índices também desempenham um papel importante no tamanho.

Estimando a complexidade de tempo do seu plano de consulta

Como você viu anteriormente, o plano de execução, entre outras coisas, determina qual algoritmo é usado para cada operação, o que permite expressar logicamente cada tempo de execução da consulta como uma função do tamanho da tabela incluída no plano de consulta, que é chamada de função de complexidade. Em outras palavras, você pode usar a grande notação O e o plano de execução para avaliar a complexidade e o desempenho da consulta.

Nas seções a seguir, você terá uma visão geral dos quatro tipos de complexidade de tempo e verá alguns exemplos de como a complexidade de tempo das consultas pode variar, dependendo do contexto em que é executada.

Dica: os índices fazem parte dessa história!

No entanto, deve-se observar que existem diferentes tipos de índices, diferentes planos de execução e diferentes implementações para diferentes bancos de dados; portanto, as dificuldades temporárias listadas abaixo são muito gerais e podem variar dependendo de configurações específicas.

O (1): Tempo constante

Eles dizem que um algoritmo funciona em tempo constante se precisar da mesma quantidade de tempo, independentemente do tamanho dos dados de entrada. Quando se trata de uma consulta, ela será executada em tempo constante se a mesma quantidade de tempo for necessária, independentemente do tamanho da tabela.

Esse tipo de consulta não é realmente comum, mas aqui está um exemplo:

 SELECT TOP 1 t.* FROM t 

A complexidade do tempo é constante, pois uma linha arbitrária é selecionada na tabela. Portanto, o período de tempo não deve depender do tamanho da tabela.

Tempo Linear: O (n)

Eles dizem que o algoritmo funciona em tempo linear, se o tempo de execução for diretamente proporcional ao tamanho dos dados de entrada, ou seja, o tempo aumenta linearmente com o tamanho dos dados de entrada. Para bancos de dados, isso significa que o tempo de execução será diretamente proporcional ao tamanho da tabela: à medida que o número de linhas na tabela aumenta, o tempo de execução da consulta aumenta.

Um exemplo é uma consulta com uma WHERE para uma coluna não indexada: será necessária uma varredura de tabela completa ou Seq Scan , o que levará à complexidade do tempo O (n). Isso significa que cada linha deve ser lida para encontrar a linha com o identificador (ID) desejado. Você não possui nenhuma restrição, portanto, é necessário contar todas as linhas, mesmo que a primeira linha corresponda à condição.

Considere também o exemplo de consulta a seguir, que terá complexidade O (n) se não houver índice no campo i_id :

 SELECT i_id FROM item; 

• O precedente também significa que outras consultas, como consultas para calcular o número de linhas COUNT (*) FROM TABLE; terá complexidade de tempo O (n) , pois será necessária uma verificação completa da tabela porque o número total de linhas não foi salvo para a tabela. Caso contrário, a complexidade do tempo seria semelhante a O (1) .
  

O tempo de execução linear está intimamente relacionado ao tempo de execução dos planos que possuem junções de tabela. Aqui estão alguns exemplos:

• A junção de hash tem a complexidade esperada de O (M + N) .O algoritmo clássico de junção de hash para unir internamente duas tabelas primeiro prepara a tabela de hash da tabela menor. As entradas da tabela de hash consistem em um atributo de conexão e sua sequência. A tabela de hash é acessada aplicando a função hash ao atributo de conexão. Depois que a tabela de hash é criada, uma tabela grande é varrida e as linhas correspondentes da tabela menor são encontradas pesquisando a tabela de hash.
• As junções de mesclagem geralmente têm complexidade O (M + N), mas isso depende muito dos índices da coluna de junção e, se não houver índice, se as linhas são classificadas de acordo com as chaves usadas na junção:
  • Se as duas tabelas forem classificadas de acordo com as chaves usadas na junção, a consulta terá uma complexidade de tempo de O (M + N).
  • Se ambas as tabelas tiverem um índice para colunas unidas, o índice já suportará essas colunas na ordem e a classificação não será necessária. A dificuldade será O (M + N).
  • Se nenhuma das tabelas tiver um índice nas colunas conectadas, você deve primeiro classificar as duas tabelas, para que a complexidade pareça O (M log M + N log N).
  • Se apenas uma das tabelas tiver um índice nas colunas conectadas, somente a tabela que não possui um índice deverá ser classificada antes que a etapa de junção ocorra, para que a complexidade se pareça com O (M + N log N).
• Para junções aninhadas, a complexidade é geralmente O (MN). Essa associação é efetiva quando uma ou ambas as tabelas são extremamente pequenas (por exemplo, menos de 10 registros), o que é uma situação muito comum na avaliação de consultas, pois algumas subconsultas são gravadas para retornar apenas uma linha.

Lembre-se: uma junção aninhada é uma junção que compara cada registro em uma tabela com cada registro em outra.

Tempo logarítmico: O (log (n))

Diz-se que um algoritmo funciona em tempo logarítmico se o tempo de execução for proporcional ao logaritmo do tamanho da entrada; Para consultas, isso significa que elas serão executadas se o tempo de execução for proporcional ao logaritmo do tamanho do banco de dados.

Essa complexidade de tempo logarítmica é válida para planos de consulta em que uma Index Scan ou um índice em cluster é verificado. Um índice clusterizado é um índice em que o nível final do índice contém as linhas reais da tabela. Um índice em cluster é semelhante a qualquer outro índice: é definido em uma ou mais colunas. Eles formam uma chave de índice. A chave de cluster é a coluna de chaves de um índice em cluster. A varredura de um índice em cluster é basicamente a operação de ler seu DBMS para uma linha ou linhas de cima para baixo em um índice em cluster.

Considere o seguinte exemplo de consulta, em que há um índice para i_id e que geralmente resulta em complexidade de O (log (n)):

 SELECT i_stock FROM item WHERE i_id = N; 

Observe que sem um índice, a complexidade do tempo seria O (n).

Tempo quadrático: O (n ^ 2)

Acredita-se que o algoritmo seja executado em tempo quadrático, se o tempo de execução for proporcional ao quadrado do tamanho da entrada. Novamente, para bancos de dados, isso significa que o tempo de execução da consulta é proporcional ao quadrado do tamanho do banco de dados.

Um possível exemplo de uma consulta quadrática de complexidade de tempo é o seguinte:

 SELECT * FROM item, author WHERE item.i_a_id=author.a_id 

A complexidade mínima pode ser O (n log (n)), mas a complexidade máxima pode ser O (n ^ 2) com base nas informações de índice dos atributos de conexão.

Para resumir, você também pode dar uma olhada na seguinte folha de dicas para avaliar o desempenho da consulta com base na complexidade do tempo e na eficácia:

Ajuste de SQL

Dado o plano de execução da consulta e a complexidade do tempo, você pode personalizar ainda mais sua consulta SQL. Você pode começar concentrando-se nos seguintes pontos:

• Substitua varreduras completas desnecessárias de tabela por varreduras de índice;
• Certifique-se de que a ordem de junção ideal seja aplicada.
• Verifique se os índices são usados ​​da melhor maneira. E
• Armazenamento em cache de verificações de texto completo de tabelas pequenas (verificações de tabela completa de tabela pequena em cache.) É usado.

Uso adicional do SQL

Parabéns! Você chegou ao final deste artigo, que apenas deu uma pequena olhada no desempenho das consultas SQL. Espero que você tenha mais informações sobre antipadrões, o otimizador de consulta e as ferramentas que você pode usar para analisar, avaliar e interpretar a complexidade do seu plano de consulta. No entanto, você ainda tem muito a descobrir! Se você quiser saber mais, leia o livro “Database Management Systems” de R. Ramakrishnan e J. Gehrke.

Por fim, não quero negar o StackOverflow de você nesta citação:

Meu antipadrão favorito não verifica seus pedidos.

No entanto, é aplicável quando:

• Sua consulta fornece mais de uma tabela.
• Você acha que possui o design ideal para a solicitação, mas não tenta verificar suas suposições.
• Você aceita a primeira solicitação de trabalho, sem saber o quão perto está do ideal.