Heading

Neste artigo, vamos focar-nos nas dicas mais fáceis e melhores que os programadores podem implementar para os seus esforços diários de afinação do desempenho SQL. Este artigo centrar-se-á na afinação do desempenho de consultas MySQL, mas os mesmos conceitos podem ser aplicados a muitas outras bases de dados relacionais.

Agora mais do que nunca, os engenheiros de software precisam de ter vastos conhecimentos em afinação do desempenho SQL.
A mudança está a acontecer tanto em pequenas empresas em fase de arranque como em grandes empresas. Actualmente, são os programadores que escrevem as consultas SQL e a camada de acesso a bases de dados.

Não importa realmente se está a utilizar uma camada de abstracção de bases de dados (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, ou outros) ou a escrever consultas SQL nativas, acabará por ser desafiado a afinar as consultas que está a enviar para a sua base de dados.

O que pode fazer para optimizar as suas consultas SQL?

Criar índices, mas fá-lo sabiamente

Indexar é provavelmente a parte mais importante do processo de afinação das consultas. Portanto, primeiro, certifique-se de que está familiarizado com os diferentes aspectos que deve considerar ao escolher os índices óptimos. para a sua base de dados.

Ao pensar em que índices criar, deve prestar muita atenção à cláusula WHERE da consulta e à tabela JOINs, uma vez que essas afirmações incluem as partes críticas da consulta.

Também, os principais engarrafamentos podem ter origem nas partes GROUP BY e ORDER BY. Disse que, um potencial soluço será que poderá não ser possível indexá-los em alguns casos, como explicamos aqui. Portanto, poderá ter de repensar o desenho da sua consulta antes de criar os índices, para ter a certeza de escrever grandes consultas, mas também escrever consultas indexáveis.

Após a indexação ter sido calculada para uma consulta, não pare por aí. Alargue a sua visão e procure outras consultas importantes na sua aplicação. Certifique-se de combinar índices sempre que possível, e remova os índices que não são utilizados. Olhar para todo o âmbito da aplicação será sempre melhor do que olhar para o âmbito de uma única consulta.

Deve também ter em mente que a criação de mais índices do que aqueles de que necessita pode também fazer ricochetear, uma vez que podem atrasar as operações de escrita (tais como declarações INSERT / UPDATE). Portanto, crie índices para optimizar o seu desempenho de consultas SQL, mas faça-o sabiamente.

Não se interponha no caminho dos índices

Estamos a ser muito abordados por clientes que nos perguntam “porque é que a base de dados não utiliza o meu índice? Bem, essa é uma grande pergunta, com infinitas respostas possíveis. Mas, neste artigo, vamos tentar cobrir vários cenários comuns que vemos frequentemente, por isso, esperamos que os considere úteis para o seu próprio caso.

Exemplo #1 – Evitar envolver colunas indexadas com funções

Considerar esta pergunta, que conta o número de cachorros quentes comprados nos EUA em 2018. Caso esteja curioso, foram vendidos 18.000.000.000 de cachorros-quentes nos EUA em 2018.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Como pode ver, estamos a utilizar a função ANO para obter a parte do ano da coluna tempo_de_compra. Esta chamada de função impedirá que a base de dados possa utilizar um índice para a pesquisa da coluna compra_tempo, porque indexámos o valor de compra_tempo, mas não o valor de retorno do ANO(compra_tempo).

Para superar este desafio e afinar esta consulta SQL, pode indexar o resultado da função, utilizando as Colunas Geradas, que estão disponíveis a partir do MySQL 5.7.5.

Outra solução pode ser encontrar uma forma alternativa de escrever a mesma consulta, sem utilizar a chamada de função. Neste exemplo, podemos transformar essa condição para uma condição de intervalo de 2 vias, que retornará os mesmos resultados:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Exemplo #2 – evitar condições OU

Considerar esta consulta, que selecciona a quantidade de mensagens no Facebook publicadas após a véspera do ano novo, ou publicadas por um utilizador chamado Mark.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Dev>Dar um índice tanto no nome de utilizador como nas colunas post_time pode parecer útil, mas na maioria dos casos, a base de dados não a utilizará, pelo menos não na sua totalidade. A razão será a ligação entre as duas condições – o operador OR, que faz com que a base de dados vá buscar os resultados de cada parte da condição separadamente.

Uma forma alternativa de olhar para esta consulta pode ser ‘dividir’ a condição OR e ‘combiná-la’ usando uma cláusula UNION. Esta alternativa permitir-lhe-á indexar cada uma das condições separadamente, pelo que a base de dados utilizará os índices para procurar os resultados e depois combinar os resultados com a cláusula UNION.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Por favor note que se não se importar de duplicar registos no seu conjunto de resultados, também pode utilizar UNION ALL (que terá um desempenho melhor que o padrão UNION DISTINCT).

Exemplo #3 – Evite ordenar com uma ordem mista

Consulte esta consulta, que selecciona todas as mensagens do Facebook e ordena-as pelo nome de utilizador numa ordem ascendente, e depois pela data da mensagem numa ordem descendente.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (e tantas outras bases de dados relacionais), não pode utilizar índices ao ordenar com uma ordem mista (tanto ASC como DESC na mesma cláusula ORDER BY). Isto mudou com o lançamento da funcionalidade de índices invertidos e MySQL 8.x.

Então o que pode fazer se ainda não tiver actualizado para a última versão do MySQL? Primeiro, recomendamos que reconsidere a classificação por ordem mista. Precisa mesmo dela? Se não, evite-o.

Então decidiu que precisava dele, ou o seu gestor de produto disse: “Não há maneira de conseguirmos gerir sem ele”? Outra opção será utilizar colunas Geradas (disponíveis no MySQL 5.7.5+) para criar uma coluna invertida e ordenar nessa coluna em vez da original. Como exemplo, suponha que está a ordenar numa coluna numérica, pode criar uma coluna gerada com o valor numérico negativo que se correlaciona com o número original e ordenar nessa nova coluna na ordem oposta. Dessa forma, todas as colunas terão a mesma ordem de ordenação na cláusula ORDER BY, mas a ordenação ocorrerá como originalmente definido pelos requisitos do seu produto.

A última solução potencial não será sempre uma opção, pelo que o seu último recurso será a actualização para a última versão do MySQL que suporta ordenação por ordem mista utilizando índices.

Exemplo #4 – Evitar condições com diferentes tipos de colunas

Considerar esta consulta, que selecciona o número de frutos vermelhos de uma floresta.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

Assumindo que o tipo de coluna fruta_color é VARCHAR, ou qualquer coisa não-numérica, a indexação dessa coluna não será muito útil, pois o elenco implícito exigido impedirá a base de dados de utilizar o índice para o processo de filtragem.

Então, como se pode afinar esta consulta SQL? Tem duas opções para optimizar esta consulta. A primeira seria comparar a coluna com um valor constante que corresponda ao tipo da coluna, portanto, se for uma coluna VARCHAR, compará-la com ‘5’ (com aspas simples) e não com 5 (que é uma comparação numérica que resultará num molde implícito).

Uma melhor opção será ajustar o tipo da coluna para corresponder ao tipo mais adequado para os valores que a coluna contém. Neste exemplo, a coluna deve ser alterada para um tipo INT. Note que alterar o tipo de coluna pode ser uma tarefa complicada, por isso leia sobre os desafios dessa tarefa antes de se dirigir para ela.

Evite pesquisas LIKE com prefixo wildcards

Consulte esta pesquisa, que pesquisa todos os posts do Facebook a partir de um nome de utilizador que inclui a string ‘Mar’, por isso estamos a pesquisar todos os posts escritos por utilizadores chamados Mark, Marcus, Almar, etc.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Salvar um wildcard ‘%’ no início do padrão impedirá a base de dados de utilizar um índice para a pesquisa desta coluna. Tais pesquisas podem demorar um pouco…

Neste caso, há duas opções para melhorar o desempenho desta consulta. A primeira é trivial – considerar se o wildcard de prefixo é suficientemente importante. Se se conseguir passar sem ele, desfaça-se dele.

Outra opção será usar índices de texto completo. Note, no entanto, que estes índices e o MATCH … CONTRA a sintaxe não estão livres de desafios e têm algumas diferenças quando comparados com as expressões familiares LIKE no MySQL.

Conclusion

Nesta primeira parte da nossa série de optimização de consultas SQL, cobrimos a importância de uma indexação sábia, passamos por vários exemplos de possíveis obstáculos ao utilizar colunas indexadas em consultas, e também detalhamos várias outras dicas e truques que podem ser úteis para um melhor desempenho das consultas. Vemo-nos no próximo post.