Truques para depurar problemas de performance no MySQL

1. Preparando o Ambiente de Diagnóstico

Antes de qualquer análise, é fundamental configurar o ambiente para capturar informações relevantes sem impacto excessivo no servidor de produção. Comece habilitando o slow_query_log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

O performance_schema oferece visibilidade granular sobre eventos do servidor. Ative-o no arquivo de configuração (my.cnf) e reinicie o MySQL:

[mysqld]
performance_schema=ON

Para testes controlados, o general_log pode ser útil, mas use com cautela em produção:

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/tmp/mysql-general.log';

2. Analisando Queries com EXPLAIN

O EXPLAIN é a ferramenta mais poderosa para entender como o MySQL executa uma consulta. Exemplo prático:

EXPLAIN SELECT u.nome, COUNT(p.id) as total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON u.id = p.usuario_id
WHERE u.ativo = 1
GROUP BY u.id;

Interpretação dos campos críticos:
- type: ALL indica scan completo (péssimo), ref ou range são bons
- key: mostra qual índice foi usado (NULL significa sem índice)
- rows: estimativa de linhas examinadas
- Extra: Using where; Using index é ideal; Using filesort indica ordenação sem índice

Para detalhes avançados, use EXPLAIN FORMAT=JSON:

EXPLAIN FORMAT=JSON
SELECT * FROM pedidos WHERE data_criacao BETWEEN '2024-01-01' AND '2024-12-31';

3. Identificando e Otimizando Índices

Índices mal projetados são a causa mais comum de lentidão. Detecte índices redundantes consultando o information_schema:

SELECT table_name, index_name, column_name, seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'meu_banco';

Use SHOW INDEX FROM para ver detalhes de uma tabela específica:

SHOW INDEX FROM pedidos;

Crie índices compostos colocando colunas mais seletivas primeiro. Por exemplo, se filtramos por status (poucos valores) e data_criacao (muitos valores):

CREATE INDEX idx_pedidos_status_data ON pedidos (data_criacao, status);

Teste com EXPLAIN se o índice está sendo utilizado. Se não, considere reordenar as colunas.

4. Monitorando Bloqueios e Deadlocks

Deadlocks podem paralisar aplicações inteiras. Use SHOW ENGINE INNODB STATUS para encontrar a última ocorrência:

SHOW ENGINE INNODB STATUS\G

Procure pela seção LATEST DETECTED DEADLOCK. Para logar todos os deadlocks automaticamente:

SET GLOBAL innodb_print_all_deadlocks = 'ON';

Configure um timeout razoável para bloqueios:

SET GLOBAL innodb_lock_wait_timeout = 50;

Para identificar bloqueios ativos via performance_schema:

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

5. Utilizando Ferramentas de Profiling e Logging

O profiling interno do MySQL permite medir o tempo real de cada etapa:

SET profiling = 1;
SELECT * FROM pedidos WHERE valor > 1000;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

Para análise em massa de logs lentos, o pt-query-digest é indispensável:

pt-query-digest /var/log/mysql/mysql-slow.log > analise_queries.txt

Entenda o volume de operações internas com Handler:

SHOW STATUS LIKE 'Handler%';

Valores altos em Handler_read_rnd_next indicam scans completos desnecessários.

6. Verificando Configurações do Buffer e Cache

O innodb_buffer_pool_size é a configuração mais crítica para performance. Ajuste-o para 70-80% da RAM disponível em servidores dedicados:

SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024; -- 8GB

Monitore a eficiência do buffer pool:

SHOW ENGINE INNODB STATUS\G

Procure por Buffer pool hit rate — abaixo de 95% indica necessidade de aumentar o buffer.

O Query Cache foi removido no MySQL 8.0, mas em versões anteriores pode causar contenção. Desabilite-o:

SET GLOBAL query_cache_type = 0;

Ajuste a memória temporária para evitar gravação em disco:

SET GLOBAL tmp_table_size = 64 * 1024 * 1024; -- 64MB
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024;

7. Otimizando Consultas com Estratégias Avançadas

Subconsultas podem ser lentas. Reescreva-as como JOIN:

-- Lento
SELECT * FROM usuarios WHERE id IN (SELECT usuario_id FROM pedidos WHERE valor > 1000);

-- Rápido
SELECT DISTINCT u.* FROM usuarios u
JOIN pedidos p ON u.id = p.usuario_id
WHERE p.valor > 1000;

Para testar planos alternativos temporariamente:

SELECT * FROM pedidos FORCE INDEX (idx_data) WHERE data_criacao > '2024-01-01';

Evite filesort ordenando por colunas indexadas:

-- Pode causar filesort
SELECT * FROM pedidos ORDER BY data_criacao DESC, valor ASC;

-- Otimizado se existir índice (data_criacao, valor)
-- A ordenação deve seguir a ordem do índice

Use LIMIT com índices para evitar scans desnecessários:

SELECT id, nome FROM usuarios ORDER BY id LIMIT 1000, 20;

Com índice em id, o MySQL evita examinar todas as linhas.

Referências