Este artigo explora técnicas de otimização de consultas no MySQL, com foco na criação e uso de índices para melhorar o desempenho. Analisaremos diferentes cenários e estratégias de otimização.

Cenário Inicial e Consulta Lenta

Consideremos um banco de dados MySQL 5.6 com as seguintes tabelas:

• Course (Curso): 100 registros
• Student (Aluno): 70.000 registros
• SC (Notas de Alunos): 700.000 registros

O objetivo é encontrar os alunos que tiraram 100 em "Português". A consulta inicial é:

SELECT s.*
FROM Student s
WHERE s.s_id IN (SELECT s_id FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100);
   

O tempo de execução desta consulta foi de 30248.271 segundos.

Análise do Plano de Execução e Criação de Índices

Ao analisar o plano de execução com EXPLAIN, observamos que nenhum índice foi utilizado (type: ALL). A primeira etapa da otimização é criar índices nas colunas usadas nas cláusulas WHERE.

Criamos índices nas colunas c_id e score da tabela SC:

CREATE INDEX sc_c_id_index ON SC(c_id);
CREATE INDEX sc_score_index ON SC(score);
   

Após a criação dos índices, a mesma consulta levou apenas 1.054 segundos, uma melhoria de mais de 30.000 vezes.

Otimização Adicional: JOIN vs. Subconsulta

Apesar da melhoria significativa, 1 segundo ainda pode ser considerado longo. Analisando o plano de execução otimizado, percebemos que o MySQL pode transformar a subconsulta em uma cláusula EXISTS com subconsultas dependentes (DEPENDENT SUBQUERY), o que pode levar à execução externa primeiro, seguida pela interna em loop, resultando em ineficiência.

Vamos testar a reescrita da consulta usando um INNER JOIN:

SELECT s.*
FROM Student s
INNER JOIN SC sc ON sc.s_id = s.s_id
WHERE sc.c_id = 0 AND sc.score = 100;
   

Inicialmente, removendo os índices criados anteriormente para reavaliar, esta consulta demorou 0.057 segundos. A análise do plano de execução indicou a necessidade de um índice na coluna s_id da tabela SC para otimizar o JOIN.

Criando o índice sc_s_id_index:

CREATE INDEX sc_s_id_index ON SC(s_id);
   

A consulta JOIN, após a criação do índice sc_s_id_index, levou 1.076 segundos, o que é mais lento. Isso sugere que o otimizador do MySQL pode ter uma estratégia mais eficiente por padrão.

Para garantir que a filtragem ocorra antes da junção (especialmente em tabelas grandes), podemos reescrever a consulta explicitamente:

SELECT s.*
FROM (
   SELECT *
   FROM SC sc
   WHERE sc.c_id = 0 AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id;
   

Com os índices sc_c_id_index e sc_score_index já existentes, esta consulta otimizada levou apenas 0.001 segundos.

O plano de execução mostra que a subconsulta (filtragem na tabela SC) é executada primeiro, utilizando os índices, seguida pela junção com a tabela Student.

Otimização com Índices Compostos

Em um cenário com dados de produção maiores (3 milhões de registros na tabela SC) e distribuições de dados mais dispersas, a eficiência pode diminuir. Testando novamente:

SELECT s.*
FROM Student s
INNER JOIN SC sc ON sc.s_id = s.s_id
WHERE sc.c_id = 81 AND sc.score = 84;
   

Esta consulta levou 0.061 segundos. A análise do plano de execução mostrou a operação INTERSECT, indicando que o MySQL estava usando dois índices separadamente e combinando os resultados.

Quando a cardinalidade de colunas individuais não é alta, mas a combinação delas é, um índice composto é mais eficiente. Removemos os índices individuais e criamos um índice composto:

ALTER TABLE SC DROP INDEX sc_c_id_index;
ALTER TABLE SC DROP INDEX sc_score_index;
CREATE INDEX sc_c_id_score_index ON SC(c_id, score);
   

A mesma consulta agora leva 0.007 segundos, uma melhoria significativa.

Otimização de Índices Adicionais

Índices de Coluna Única vs. Compostos

Para uma consulta como:

SELECT * FROM user_test_copy WHERE sex = 2 AND type = 2 AND age = 10;
   

Com índices de coluna única em sex, type e age (3 milhões de registros), a consulta levou 0.415 segundos. O plano de execução mostrou INDEX_MERGE.

Criando um índice composto:

CREATE INDEX user_test_index_sex_type_age ON user_test(sex, type, age);
   

A mesma consulta agora leva 0.032 segundos, mais de 10 vezes mais rápido. A eficiência aumenta com a cardinalidade do índice composto.

Princípio da "Menor Chave à Esquerda" (Leftmost Key Prefix)

Um índice composto como (sex, type, age) pode ser utilizado para consultas que filtram apenas por sex, ou por sex e type, ou por sex, type e age. O primeiro campo do índice (sex) deve estar presente na cláusula WHERE para que o índice seja utilizado eficientemente.

Índices de Cobertura (Covering Indexes)

Quando todos os campos necessários para a consulta estão incluídos no índice, o MySQL não precisa acessar a tabela principal, acelerando a recuperação dos dados.

SELECT sex, type, age
FROM user_test
WHERE sex = 2 AND type = 2 AND age = 10;
   

Com o índice composto (sex, type, age), esta consulta levou 0.003 segundos, muito mais rápido do que selecionar todos os campos.

Otimização de Ordenação (ORDER BY)

Para melhorar o desempenho de ordenação:

SELECT *
FROM user_test
WHERE sex = 2 AND type = 2
ORDER BY user_name;
   

Esta consulta levou 0.139 segundos. Criar um índice na coluna de ordenação (user_name) pode otimizar esta operação.

Resumo das Estratégias de Otimização

1. Prefira tipos de dados numéricos e com o menor comprimento possível.
2. Utilize índices de coluna única quando apropriado.
3. Crie índices compostos quando a combinação de colunas aumentar significativamente a cardinalidade e a eficiência da consulta.
4. Implemente índices de cobertura para consultas que selecionam um subconjunto de colunas já indexadas.
5. Crie índices nas colunas usadas em junções (JOIN).
6. Crie índices nas colunas usadas em cláusulas WHERE.
7. Crie índices nas colunas usadas em ORDER BY e GROUP BY.
8. Evite o uso de funções em colunas nas cláusulas WHERE, pois isso pode invalidar o uso de índices.
9. Analise sempre o plano de execução (EXPLAIN) para entender como o MySQL está executando suas consultas e identificar gargalos.