1) Diagnóstico baseado em logs, planos de execução e índices

Meu approach inicia pelo isolamento de gargalos com base em evidências. A combinação de logs, análise de planos de execução e o estado dos índices permite separar rapidamente gargalos de I/O, CPU e contenção de bloqueios.

• Habilitar e interpretar o slow query log para identificar consultas que excedem o tempo aceitável.
• Comparar o plano esperado (EXPLAIN) com a execução real (EXPLAIN ANALYZE quando disponível).
• Verificar uso e cobertura de índices: índices ausentes, colunas em funções, ordens de filtro que prejudicam a selectividade.
• Executar análise de estatísticas da tabela (ANALYZE TABLE) para casos de cardinalidade desatualizada.

-- Bloco de código relevante: ativação de logs de consultas lentas e uma consulta de exemplo

-- Habilita logs de consultas lentas
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5; -- segundos
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 1;

-- Verifique as configurações ativas
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- Exemplo de EXPLAIN ANALYZE (quando disponível)
EXPLAIN ANALYZE
SELECT u.id, u.email
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.status = 'ACTIVE'
  AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

Dicas rápidas: mantenha o tempo de consulta lenta realista para o seu workload; valores muito baixos geram excesso de logs. Use EXPLAIN ANALYZE para ver o custo real por operação, não apenas o plano estimado.

2) Instrumentação com Performance Schema

A Performance Schema oferece um conjunto robusto de métricas para entender o que realmente está consumindo tempo de CPU, espera de I/O e a exceção de locks. A estratégia é coletar dados de alto nível, depois mergulhar nos digests de consultas mais onerosas.

• Certifique-se que o Performance Schema está ativo e com consumidores apropriados habilitados.
• Identifique consultas com maior tempo agregado usando digest (digest_text) e as estatísticas por digest.
• Use as tabelas de eventos_waits e statements para correlacionar waits com queries específicas.

-- Habilitar Performance Schema (se necessário)
SET GLOBAL performance_schema = ON;

-- Verificar se está ativo
SHOW VARIABLES LIKE 'performance_schema';

-- Exemplo de consulta para encontrar as consultas mais lentas por digest
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS calls,
  SUM_TIMER_WAIT/1000000000 AS total_seconds
FROM
  performance_schema.events_statements_summary_by_digest
ORDER BY total_seconds DESC
LIMIT 10;

A mágica está em transformar dados de alto nível em ações concretas: identifique o digest crítico, revise a query associada e aplique índices que melhorem a cobertura.

3) Análise de bloqueios, deadlocks e concorrência

Quando a contenção é o gargalo, a observação direta de locks é essencial. Recolha informações de transações no InnoDB e examine o que está bloqueando o progresso das queries.

• Verifique o estado atual das transações e locks com informações do InnoDB e views de information_schema.
• Use SHOW ENGINE INNODB STATUS para obter o stack trace de deadlocks recentes e identificar as queries envolvidas.
• Considere ajustar timeouts de lock e refatorar queries para evitar bloqueios simultâneos em tabelas de alto tráfego.

-- Exemplo de consultas para inspecionar locks ativos
SELECT
  r.trx_id AS trx_id,
  r.trx_state AS trx_state,
  r.trx_started AS started,
  l.lock_id AS lock_id,
  l.lock_table AS locked_table,
  l.lock_mode AS lock_mode
FROM information_schema.innodb_lock_waits AS w
JOIN information_schema.innodb_locks AS l ON w.requested_lock_id = l.lock_id
JOIN information_schema.innodb_trx AS r ON l.lock_trx_id = r.trx_id
ORDER BY started ASC;

-- Obter status do InnoDB (deadlocks)
SHOW ENGINE INNODB STATUS;

Dicas rápidas: prefira queries que acessem índices existentes antes de bloquear grandes porções da tabela. Em cenários de alta concorrência, considere particionamento ou leitura escalonada (read replicas) para reduzir contenção.

4) Fluxo de diagnóstico e melhoria de queries

Este é o passo a passo que sigo para transformar um problema em um conjunto de mudanças com impacto mensurável.

• Defina um reproduzível: crie um conjunto de dados ou um caso de uso com carga previsível.
• Isolamento da causa: elimine I/O, CPU e locks em camadas separadas, começando pela mais provável.
• Teste de hipóteses com alterações pequenas e reversíveis: adicione ou ajuste índices, reescreva consultas com foco em cobertura de índice.
• Valide com métricas: compare tempo de resposta, CPU, leituras I/O e contagem de bloqueios antes/depois.
• Documente as mudanças e atualize os planos de indexação para evitar regressões futuras.

Exemplos de estratégias comuns de melhoria

• Usar índices compostos para cobrir condições de filtro e ordenação (por exemplo, [user_id, status, created_at]).
• Evitar ORs que dificultem o uso de índices; dividir em queries com UNION/IN quando apropriado.
• Reduzir o conjunto de dados com filtros mais seletivos primeiro (push predicates).
• Aplicar particionamento para reduzir o volume de leitura em grandes tabelas históricas.