Ajuste suas consultas MySQL como um profissional

Otimizar suas consultas não é uma arte obscura; é apenas engenharia simples.

Muitas pessoas consideram o ajuste de consultas de banco de dados uma misteriosa “arte obscura” de um romance de Harry Potter; com o encantamento errado, seus dados passam de um recurso valioso a uma pilha de mingau.

Na realidade, ajustar consultas para um sistema de banco de dados relacional é uma engenharia simples e segue regras ou heurísticas fáceis de entender. O otimizador de consulta traduz a consulta enviada para uma instância do MySQL e determina a melhor maneira de obter os dados solicitados usando essas heurísticas combinadas com o que sabe sobre seus dados. Releia a última parte: "o que ele sabe sobre seus dados." Quanto menos o otimizador de consulta tiver que adivinhar onde seus dados estão localizados, melhor ele poderá criar um plano para entregar seus dados.

Para fornecer ao otimizador uma visão melhor sobre os dados, você pode usar índices e histogramas. Usados corretamente, eles podem aumentar bastante a velocidade de uma consulta ao banco de dados. Se você seguir a receita, você conseguirá algo que vai gostar. Mas se você adicionar seus próprios ingredientes a essa receita, poderá não conseguir o que deseja.

Otimizador baseado em custos

A maioria dos bancos de dados relacionais modernos usa um otimizador baseado em custos para determinar como recuperar seus dados do banco de dados. Esse custo é baseado na redução, tanto quanto possível, de leituras de disco muito caras. O código do otimizador de consulta dentro do servidor de banco de dados mantém estatísticas sobre a obtenção desses dados à medida que são encontrados e constrói um modelo histórico do que foi necessário para obter os dados.

Mas os dados históricos podem estar desatualizados. É como ir à loja comprar seu lanche preferido e ficar chocado com um aumento repentino de preço ou com o fechamento da loja. O processo de otimização do seu servidor pode fazer uma suposição errada com base em informações antigas e isso produzirá um plano de consulta ruim.

A complexidade de uma consulta pode prejudicar a otimização. O otimizador deseja entregar a consulta de menor custo dentre as opções disponíveis. Unir cinco tabelas diferentes significa que existem cinco fatoriais ou 120 combinações possíveis sobre as quais unir e o quê. Heurísticas são incorporadas ao código para tentar avaliar de forma rápida todas as opções possíveis. O MySQL deseja gerar um novo plano de consulta sempre que vê uma consulta, enquanto outros bancos de dados como o Oracle podem ter um plano de consulta bloqueado. É por isso que é vital fornecer informações detalhadas sobre seus dados ao otimizador. Para um desempenho consistente, é realmente útil ter informações atualizadas para o otimizador de consulta usar ao fazer planos de consulta.

Além disso, regras são incorporadas ao otimizador com suposições que provavelmente não correspondem à realidade dos seus dados. O otimizador de consulta assumirá que todos os dados em uma coluna estão distribuídos uniformemente entre todas as linhas, a menos que tenha outras informações. E o padrão será o menor dos dois índices possíveis se não encontrar alternativa. Embora o modelo baseado em custos para um otimizador possa tomar muitas decisões acertadas, você pode se deparar com casos em que não obterá um plano de consulta ideal.

Um plano de consulta?

Um plano de consulta é o que o otimizador irá gerar para o servidor executar a partir da consulta. A maneira de ver o plano de consulta é acrescentar a palavra EXPLAIN à sua consulta. Por exemplo, a consulta a seguir solicita o nome de uma cidade da tabela de cidades e o nome da tabela de países correspondente, e as duas tabelas são vinculadas pelo código exclusivo do país. Este caso está interessado apenas nas cinco principais cidades do Reino Unido em ordem alfabética:

SELECT city.name as 'City',
               country.name as 'Country'
FROM city
JOIN country ON (city.countrycode = country.code)
WHERE country.code = 'GBR'
LIMIT 5;

Acrescentar EXPLAIN na frente desta consulta fornecerá o plano de consulta gerado pelo otimizador. Ignorando tudo, exceto o final da saída, é fácil ver a consulta otimizada:

select `world`.`city`.`Name` AS `City`,
         	'United Kingdom' AS `Country`
from `world`.`city`
join `world`.`country`
where (`world`.`city`.`CountryCode` = 'GBR')
limit 5;

As grandes mudanças são que country.name as 'Country' foi alterado para 'United Kingdom' AS 'Country' e a cláusula WHERE passou de olhando na mesa do país para a mesa da cidade. O otimizador determinou que essas duas alterações fornecerão um resultado mais rápido que a consulta original.

Índices

Você ouvirá índices e chaves usados de forma intercambiável no MySQL-verse. No entanto, os índices são compostos de chaves, e as chaves são uma forma de identificar um registro, esperançosamente de forma única. Se uma coluna for projetada como uma chave, o otimizador poderá pesquisar uma lista dessas chaves para encontrar o registro desejado sem precisar ler a tabela inteira. Sem um índice, o servidor precisa começar na primeira linha da primeira coluna e ler cada linha de dados. Se a coluna foi criada como um índice exclusivo, o servidor poderá acessar aquela linha de dados e ignorar o restante. Quanto mais exclusivo for o valor do índice (também conhecido como cardinalidade), melhor. Lembre-se de que estamos procurando maneiras mais rápidas de obter os dados.

O mecanismo de armazenamento InnoDB padrão do MySQL deseja que sua tabela tenha uma chave primária e armazenará seus dados em uma árvore B+ por essa chave. Um recurso adicionado recentemente do MySQL são colunas invisíveis – colunas que não retornam dados a menos que a coluna seja explicitamente nomeada na consulta. Por exemplo, SELECT * FROM foo; não fornece colunas designadas como ocultas. Esse recurso oferece uma maneira de adicionar uma chave primária a tabelas antigas sem recodificar todas as consultas para incluir a nova coluna.

Para tornar isso ainda mais complicado, existem muitos tipos de índices, como funcionais, espaciais e compostos. Existem até casos em que você pode criar um índice que fornecerá todas as informações solicitadas para uma consulta para que não haja necessidade de acessar a tabela de dados.

A descrição dos vários índices está além do escopo deste artigo; portanto, pense em um índice como um atalho para o registro ou registros que você deseja. Você pode criar um índice em uma ou mais colunas ou em parte dessas colunas. O sistema do meu médico pode consultar meus registros pelas três primeiras letras do meu sobrenome e data de nascimento. O uso de múltiplas colunas requer o uso do campo mais exclusivo primeiro, depois o segundo mais exclusivo e assim por diante. Um índice ano-mês-dia funciona para pesquisas ano-mês-dia, ano-mês e ano, mas não funciona para pesquisas dia, mês-dia ou ano-dia. Ajuda projetar seus índices de acordo com como você deseja usar seus dados.

Histogramas

Um histograma é uma distribuição de seus dados. Se você estivesse colocando as pessoas em ordem alfabética pelo sobrenome, poderia usar um "balde lógico" para as pessoas com sobrenomes começando com as letras de A a F, depois outro para G a J e assim por diante. O otimizador assume que os dados estão distribuídos uniformemente dentro da coluna, mas isso raramente acontece no uso prático.

O MySQL fornece dois tipos de histogramas: altura igual, onde todos os dados são divididos igualmente entre os intervalos, e singleton, onde um único valor está em um intervalo. Você pode ter até 1.024 buckets. A quantidade de intervalos a serem escolhidos para sua coluna de dados depende de muitos fatores, incluindo quantos valores distintos você tem, quão distorcidos estão seus dados e quão alta sua precisão realmente precisa ser. Depois de uma certa quantidade de buckets, há retornos decrescentes.

Este comando criará um histograma de 10 buckets na coluna c1 da tabela t:

ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS;

Imagine que você vende meias pequenas, médias e grandes, e cada tamanho tem sua própria caixa para armazenamento. Para encontrar o tamanho que você precisa, vá até a lixeira desse tamanho. O MySQL tem histogramas desde que o MySQL 8.0 foi lançado há três anos, mas eles não são tão conhecidos quanto os índices. Ao contrário dos índices, não há sobrecarga para inserir, atualizar ou excluir um registro. Para atualizar um índice, um comando ANALYZE TABLE deve ser atualizado. Essa é uma boa abordagem quando os dados não mudam muito e alterações frequentes nos dados reduzem a eficiência.

Índices ou histogramas?

Use índices para itens exclusivos onde você precisa acessar os dados diretamente. Há sobrecarga para atualizações, exclusões e inserções, mas você obtém acesso rápido se seus dados forem arquitetados corretamente. Use histogramas para dados que não são atualizados com frequência, como resultados trimestrais dos últimos doze anos.

Pensamentos de despedida

Este artigo surgiu de uma apresentação recente na conferência Open Source 101. E essa apresentação surgiu de um workshop em uma conferência PHP no Reino Unido. O ajuste de consultas é um assunto complexo e cada vez que apresento índices e histogramas, encontro maneiras de refinar minha apresentação. Mas cada apresentação também mostra que muitas pessoas no mundo do software não conhecem bem os índices e tendem a usá-los incorretamente. Os histogramas não existem há tempo suficiente (espero) para terem sido mal utilizados de forma semelhante.