MySQL: Identifique e Crie Índices Perdidos para Otimizar Queries

Introdução à Otimização de Queries e o Problema dos Índices Perdidos

A performance de um banco de dados é frequentemente o gargalo principal em ambientes de VPS, especialmente quando a infraestrutura compartilha recursos com outros serviços ou quando a carga de trabalho cresce exponencialmente. Em stacks modernas que utilizam MySQL, MariaDB ou até mesmo PostgreSQL, a diferença entre uma consulta responsiva e um timeout crítico muitas vezes reside na presença (ou ausência) de índices adequados.

O conceito de índices perdidos refere-se à situação em que o otimizador de consultas do banco de dados decide não utilizar um índice existente, optando por uma varredura completa da tabela (full table scan). Isso ocorre geralmente porque as estatísticas da tabela estão desatualizadas, a consulta possui padrões que tornam o uso do índice custoso ou porque o índice foi criado para um cenário de carga diferente do atual. Para sysadmins e desenvolvedores responsáveis pela administração de VPS, identificar e corrigir esses problemas é essencial para manter a latência baixa e evitar o consumo excessivo de CPU e I/O.

Neste tutorial, abordaremos como detectar consultas lentas, analisar o plano de execução, identificar índices que estão sendo ignorados e criar novas estruturas de indexação para otimizar seu banco de dados. O foco principal será no ecossistema MySQL/MariaDB, mas os conceitos se aplicam amplamente.

Etapa 1: Habilitando e Configurando o Slow Query Log

O primeiro passo para qualquer exercício de tuning de banco de dados é ter visibilidade sobre o que está acontecendo. Não adianta otimizar o que você não mede. A ferramenta nativa mais poderosa para isso em MySQL e MariaDB é o slow_query_log.

Você deve acessar seu servidor via SSH e editar o arquivo de configuração principal do banco de dados. Em sistemas Debian/Ubuntu, o caminho comum é /etc/mysql/my.cnf ou /etc/mysql/mariadb.conf.d/50-server.cnf. No CentOS/RHEL, pode ser /etc/my.cnf.

Adicione ou modifique as seguintes linhas na seção [mysqld]:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 0

Explicação dos parâmetros:

• slow_query_log = 1: Ativa o log de consultas lentas.
• long_query_time = 2: Define que qualquer consulta que leve mais de 2 segundos será registrada. Ajuste esse valor conforme a necessidade do seu negócio (ex: 0.5s para APIs sensíveis).
• log_queries_not_using_indexes = 0: Quando ativado (1), registra todas as consultas que ignoram índices, mesmo que sejam rápidas. Isso é crucial para encontrar índices perdidos. Para não sobrecarregar o disco em ambientes de alta produção, mantenha como 0 inicialmente e ative apenas durante janelas de manutenção.

Crie o diretório do log e defina as permissões corretas:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo systemctl restart mysql

Etapa 2: Analisando Consultas Lentas com mysqldumpslow

Com o log ativo, deixe seu sistema rodar por um período significativo (horas ou dias, dependendo do tráfego). Em seguida, analise os logs para identificar as queries mais custosas.

O utilitário mysqldumpslow, incluído no pacote MySQL/MariaDB, ajuda a resumir os logs. Execute o seguinte comando no terminal:

sudo mysqldumpslow -s t -t 20 /var/log/mysql/slow-query.log

O parâmetro -s t ordena pelo tempo total de execução, e -t 20 limita a saída às 20 consultas mais lentas. Você verá um resumo agrupado das queries, com variáveis substituídas por placeholders (como N para números e S para strings).

Se você ativou o log_queries_not_using_indexes, procure no arquivo de log as linhas que contêm a mensagem "Query_time" seguidas de indicações de varredura completa. Muitas vezes, usar ferramentas visuais como o MySQL Workbench ou o phpMyAdmin com parsers de slow log facilita muito a leitura desses arquivos.

Etapa 3: Usando EXPLAIN para Entender o Plano de Execução

Depois de identificar uma query problemática no log, o passo seguinte é entender por que ela está lenta. Para isso, utilizamos o comando EXPLAIN. Ele mostra ao desenvolvedor ou DBA como o MySQL planeja executar a consulta.

Sintaxe básica:

EXPLAIN SELECT * FROM usuarios WHERE email = '[email protected]';

A saída do EXPLAIN contém colunas críticas para a análise de performance SQL:

1. type: Indica o tipo de junção ou acesso. Os melhores valores são system, const, eq_ref e ref. Valores ruins incluem index (varredura no índice) e ALL (varredura completa na tabela). Se você vê ALL em uma tabela grande, há um problema grave de indexação.
2. key: Mostra qual índice o otimizador escolheu usar. Se for NULL, nenhum índice foi utilizado.
3. rows: Estima quantas linhas o banco precisa verificar para encontrar as resultados desejados. Quanto menor, melhor.
4. Extra: Informações adicionais. Procure por Using filesort (indica ordenação lenta em disco) e Using temporary (uso de tabela temporária, geralmente custoso).

Dica Pro: Se você estiver usando MariaDB 10.5+ ou MySQL 8.0+, pode usar o EXPLAIN ANALYZE. Diferente do EXPLAIN tradicional, que apenas estima, o ANALYZE executa a consulta e retorna os tempos reais de execução, proporcionando dados muito mais precisos para o tuning.

Etapa 4: Identificando Índices Perdidos ou Ineficientes

Muitas vezes, as tabelas possuem índices, mas eles não estão sendo usados. Isso pode acontecer por vários motivos:

• Funções nas colunas da cláusula WHERE: Ex: WHERE YEAR(data_criacao) = 2023. O uso de funções impede o uso do índice na coluna data_criacao.
• Conversão implícita de tipos: Se a coluna é VARCHAR e você passa um número inteiro sem aspas, o banco converte a coluna, invalidando o índice.
• Estatísticas desatualizadas: O otimizador acha que ler a tabela inteira é mais barato do que buscar no índice e depois na tabela (index lookup).

Para corrigir o problema de estatísticas obsoletas, execute o comando ANALYZE TABLE:

ANALYZE TABLE usuarios;

Isso atualiza as estatísticas do mecanismo de armazenamento (InnoDB), permitindo que o otimizador tome decisões mais acertadas. Se após isso a query ainda não usar o índice, é provável que o índice seja ineficiente para aquela consulta específica ou que ele simplesmente não exista.

Etapa 5: Criando Índices Adequados

Agora que identificamos as consultas lentas e entendemos o plano de execução, podemos criar os índices necessários. A regra geral é indexar colunas usadas em WHERE, JOIN, ORDER BY e GROUP BY.

Criar um índice simples:

CREATE INDEX idx_email ON usuarios (email);

Para consultas que filtram por múltiplas colunas, considere índices compostos. A ordem das colunas no índice é crucial. Coloque as colunas de igualdade primeiro e as de range (como BETWEEN, >) por último.

CREATE INDEX idx_status_data ON pedidos (status, data_pedido);

Se a sua consulta é SELECT id, nome FROM usuarios WHERE status = 'ativo' ORDER BY data_cadastro DESC, um índice composto em (status, data_cadastro) pode permitir que o banco resolva a consulta apenas lendo o índice (index only scan), sem precisar acessar os dados da tabela principal.

Atenção: Índices não são gratuitos. Cada índice adicionado aumenta o espaço em disco e desacelera operações de INSERT, UPDATE e DELETE, pois o banco precisa manter todas as estruturas de índice atualizadas. Crie apenas índices para queries que realmente precisam de velocidade.

Etapa 6: Monitoramento Contínuo e Manutenção em VPS

A otimização de banco de dados não é um evento único, mas um processo contínuo. Em ambientes de VPS, onde os recursos são limitados, a vigilância é ainda mais importante.

Mantenha o performance_schema habilitado no MySQL para monitorar o consumo de CPU e memória das consultas. Você pode consultar tabelas como events_statements_summary_by_digest para ver quais queries consomem mais tempo na média.

SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
    AVG_TIMER_WAIT/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Além disso, agende tarefas de manutenção rotineiras:

• Execute OPTIMIZE TABLE periodicamente em tabelas InnoDB que sofrem muitas exclusões e inserções. Isso reclaim espaço e reorganiza os dados, melhorando a localidade física no disco.
• Revise o slow_query_log semanalmente para detectar novas queries lentas introduzidas por atualizações de código.

Conclusão

A identificação e correção de índices perdidos é uma das ações mais impactantes que um administrador de sistemas ou desenvolvedor pode realizar para melhorar a performance SQL. Ao dominar o uso do slow_query_log, do EXPLAIN e da criação estratégica de índices, você garante que sua aplicação rode de forma fluida, mesmo sob alta carga.

Lembre-se: nunca crie um índice sem entender a query que ele pretende otimizar. Use dados reais, analise o plano de execução antes e depois da alteração e monitore os resultados. Uma VPS bem tunada não apenas economiza recursos, mas também melhora drasticamente a experiência do usuário final.