Analisar o registo de consultas lentas do MySQL no alojamento: Dicas de otimização

Registo de consultas lentas do MySQL mostra-me, no alojamento, quais as consultas que consomem tempo, com que frequência ocorrem e por que razão tornam as coisas mais lentas. Mostro-lhe passos específicos sobre como ativar o registo, analisá-lo e reconstruir as consultas para que as páginas carreguem mais rapidamente e os recursos do servidor funcionem de forma mais eficiente.

Pontos centrais

• Ativação e definir os limiares de forma sensata
• Avaliação com pt-query-digest e mysqldumpslow
• Métricas interpretar: Query_time, Lock_time, Rows_examined
• Afinação através de índices, EXPLAIN e reescrita
• Automatização e acompanhamento no alojamento

O que é que o registo de consultas lentas faz no alojamento?

Hospedagem significa recursos partilhados, pelo que cada milissegundo por consulta conta. Utilizo o registo para encontrar consultas que se prolongam para além de um limite definido e vejo números-chave como Query_time, Lock_time, Rows_sent e Rows_examined para cada consulta. Estes valores mostram-me se existe um índice em falta, uma junção desfavorável ou uma pesquisa de tabela completa por detrás da consulta. Especialmente em servidores com vários sites, uma única consulta incorrecta pode sobrecarregar muito a CPU e o I/O. Em seguida, dou prioridade às consultas com o tempo total mais elevado, porque é aqui que reside a maior influência no tempo de carregamento e na carga do servidor.

Valores de ativação e limiares sensíveis

InícioPosso fazer runtime ou permanentemente via my.cnf, dependendo do acesso no hosting. Para testes rápidos, eu ligo o log temporariamente e defino long_query_time para um valor que corresponda ao tráfego e ao hardware. Eu costumo ir para 0,1 segundos para sites muito usados, mas fico de olho no tamanho do log para que o I/O não cresça desnecessariamente. Se os acessos diretos aos ficheiros forem limitados, utilizo as opções de esquema de desempenho da shell do MySQL para gerar relatórios. Após o ajuste fino, escrevo as definições finais no ficheiro de configuração e reinicio o serviço.

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

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Permanente Defino opções como log_throttle_queries_not_using_indexes e log_slow_admin_statements para que o registo continue a ser útil e não expluda. Eu documento cada valor, por exemplo, porque é que long_query_time é 0,5 ou 0,1 segundos. Isto permite-me aperfeiçoá-lo mais tarde. Em ambientes partilhados, discuto frequentemente a ativação com o fornecedor ou utilizo o seu painel. Associo cada ativação a uma data de início para poder comparar os efeitos na monitorização e nas métricas de forma clara.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1

Avaliar eficazmente o registo lento

Dados em bruto são ruidosos, por isso resumo-os com o pt-query-digest e ordeno-os pelo tempo total durante um período de tempo significativo. É assim que reconheço padrões, consultas altamente variáveis e famílias de consultas que variam apenas por parâmetro. Eu verifico a distribuição, não apenas a média, porque os outliers causam problemas reais ao utilizador. Para uma visão geral rápida, o mysqldumpslow ajuda-me a ver os dez grupos mais lentos. Obtenho mais profundidade utilizando janelas de tempo, filtros de bases de dados e uma exportação para uma análise de texto.

pt-query-digest /var/log/mysql/slow-query.log
pt-query-digest --since '24h' /var/log/mysql/slow-query.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Útil é também um olhar para outros registos quando a aplicação ou as funções PHP entram em jogo. Para tal, utilizo fluxos de trabalho de registo existentes e agrupo os resultados. Este guia fornece-me frequentemente uma introdução: Analisar os registos. Sincronizo os registos de data e hora para poder comparar os picos de tráfego com os picos de consulta. Isto permite-me ver se as falhas de cache, os trabalhos cron ou os trabalhos de importação estão a utilizar a base de dados ao mesmo tempo.

Interpretar corretamente as métricas

Tempo_de_consulta mostra-me o tempo de execução puro; dou prioridade às consultas com mais de um segundo. Lock_time indica os tempos de espera devido a bloqueios, que muitas vezes resultam de transacções desnecessariamente longas ou de grandes lotes. O rácio entre Rows_examined e Rows_sent indica-me se as consultas estão a visualizar demasiadas linhas e se faltam índices. Se o registo contiver muitas entradas „No index use“ (Sem utilização de índice), defino a limitação e examino mais de perto as tabelas afectadas. Continua a ser importante atacar sempre a causa e não o sintoma: Um índice na coluna correta é melhor do que qualquer atualização de hardware.

Métricas	O que estou a ver	Medida
Tempo_de_consulta elevado	Longo tempo de execução por versão	Verificar EXPLAIN, reescrever a consulta, adicionar índice
Lock_time high	Tempo de espera para fechaduras	Encurtar transacções, reduzir o tamanho do lote, isolamento adequado
Linhas_examinadas ≫ Linhas_enviadas	Demasiado digitalizado, pouco devolvido	Colunas de filtro de índice, criação de capacidade de cálculo
Nenhum índice utilizado	Pesquisa de tabela completa	Criar índice, evitar expressão em WHERE

Valores-limite Ajusto-o após a primeira semana para não me perder no meio do ruído. Reduzo long_query_time por fases até ter resultados suficientes para melhorias sistemáticas. Documentei cada ajuste com a data e o motivo. Isto mantém a avaliação focada. Os acertos valiosos poupam-me trabalho duplicado mais tarde.

Prática: Afinação de consultas passo a passo

EXPLICAR é o meu ponto de partida antes de alterar o código. Procuro por „type: ALL“, „rows“ com números grandes e „Using filesort“ ou „Using temporary“. As funções nas colunas em WHERE ou JOIN impedem frequentemente a utilização de índices. Em vez disso, formulo condições de cálculo e depois verifico o novo plano. Cada passo deve efetuar a redução de linhas antecipadamente e de forma intencional.

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Melhor:
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT * FROM encomendas
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

JOINs Optimizo verificando a ordem de junção e os índices correspondentes nas chaves de junção. Verifico se um índice composto cobre WHERE + ORDER BY para evitar filesort. Defino LIMIT quando apenas é necessária uma pré-visualização. Guardo o cache de resultados ao nível da aplicação para consultas idênticas e repetidas com uma baixa taxa de alteração. Pode encontrar uma introdução mais aprofundada aos índices e bloqueios aqui: Índices e bloqueio.

Estratégias de indexação para CMS e lojas

WordPress, Os sistemas WooCommerce ou de lojas criam padrões típicos: muita leitura, escrita selectiva, muitas vezes com tabelas de meta ou de produtos. Analiso os percursos mais comuns - página inicial, categoria, pesquisa, checkout - e coloco índices especificamente nas colunas de filtragem, ordenação e junção. Os índices de cobertura (por exemplo, (status, created_at, id)) poupam muito o recurso à tabela. Para procurar prefixos, utilizo formulários de índice adequados ou texto integral em vez de LIKE ‚%wort%‘. Meço cada alteração de índice antes e depois da execução em tempo real com os mesmos perfis de carga.

Crescimento Utilizo a cardinalidade e os histogramas para verificar os conjuntos de dados, de modo a não indexar valores raros. Mantenho o número de índices baixo para manter a carga de escrita e os requisitos de memória sob controlo. Os índices compostos consolidados substituem vários índices individuais. Eu regulo as tarefas do tipo autovacuum no MySQL analisando-as regularmente e reconstruindo-as apenas quando necessário. Isto mantém o optimizador fiável.

Definições do servidor, armazenamento em cache e memória

InnoDB Determino o tamanho do buffer pool com base nos registos de dados activos e nos tamanhos dos índices, e não de acordo com valores gerais. Aumento-o até que o tamanho do conjunto de trabalho esteja maioritariamente na memória e a taxa de page miss diminua. Defino tmp_table_size e max_heap_table_size para que menos tabelas temporárias acabem em disco. Para segurança e latência de escrita, equilibro innodb_flush_log_at_trx_commit adequadamente para a aplicação. Ao nível da aplicação, coloco em cache os resultados frequentes e utilizo o cache HTTP para que a base de dados veja menos pedidos.

Hardware e os efeitos de rede são incluídos no diagnóstico: A E/S lenta do armazenamento ou uma CPU sobrecarregada são imediatamente reconhecidas pelas consultas. Por isso, meço o IO-wait em paralelo com as métricas da base de dados. Se precisar de mais reservas, planeie o escalonamento vertical ou horizontal com um objetivo mensurável. Este guia fornece-lhe uma visão geral compacta dos estrangulamentos, afinação e recursos: Hardware e cache. Desta forma, certifico-me de que não estou a rodar cegamente o botão errado.

Concorrência e bloqueio no alojamento

Tempo_de_bloqueio cresce quando as transacções longas tocam em muitas linhas ou quando os trabalhos de limpeza são executados em horário nobre. Encurto as operações de escrita, divido as grandes actualizações em lotes mais pequenos e reduzo assim o tempo de espera dos bloqueios. Níveis de isolamento adequados reduzem os conflitos sem pôr em causa a consistência dos dados. Alivio os hotspots com índices secundários e condições WHERE adequadas para que menos linhas sejam afectadas. Programo tarefas em segundo plano em janelas de tempo de baixo tráfego para que as acções dos utilizadores tenham prioridade.

Impasses Analiso-os com base em padrões recorrentes: mesmas tabelas, mudança de sequência, linhas idênticas. Normalizo a sequência de acesso no código e nos procedimentos armazenados. A lógica de repetição com jitter resolve as colisões temporárias. Sempre que possível, isolo as operações mais dispendiosas em filas de trabalho. Isto reduz visivelmente a variação e aumenta a perceção do desempenho.

Alarmes e fluxos de trabalho automatizados

Rotina bate no acionismo: analiso o registo diariamente ou semanalmente, dependendo do tráfego e da frequência de lançamento. Um pequeno script conta os novos acessos nos últimos minutos e envia-me um e-mail se o valor limite aumentar. Também gero relatórios regulares de pt-query-digest e estou sempre atento aos 10 primeiros. Monitorizo mais de perto os dias de lançamento. Isto permite-me reconhecer as regressões antes de os utilizadores se aperceberem delas.

#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
THRESHOLD=100
RECENT_COUNT=$(awk -v cutoff="$(date -d '5 minutes ago' '+%Y-%m-%dT%H:%M')" '/^# Time:/ { if ($3 >= cutoff) count++ } END { print count+0 }' "$LOG_FILE")
if [ "$RECENT_COUNT" -gt "$THRESHOLD" ]; then
    echo "ALERTA: $RECENT_COUNT consultas lentas" | mail -s "Alerta MySQL" [email protected]
fi

Transparência Eu crio responsabilidades claras: Quem reage aos picos, quem ajusta os índices, quem testa os lançamentos. Resumo os resultados em pequenos registos de alterações. Desta forma, todos os membros da equipa compreendem por que razão foi feita uma alteração e qual o seu efeito. Um processo estruturado poupa tempo e evita falsos alarmes.

Imagens de erros e correcções rápidas

Completo As varreduras de tabelas provocam uma carga desproporcionalmente elevada. Primeiro, verifico se falta um índice adequado na coluna de filtro ou se uma expressão está a bloquear o índice. Elimino o elevado lock_time encurtando as transacções e igualando as operações concorrentes. Desfruto de logs transbordantes com log_throttle_queries_not_using_indexes e um long_query_time realista. Meço imediatamente cada correção em relação aos valores originais para que os sucessos permaneçam visíveis.

Armazenamento-Reconheço os estrangulamentos aumentando a espera de IO e a latência elevada do disco durante os picos de consulta. Em seguida, reduzo as operações de escrita desnecessárias, por exemplo, actualizando os campos imutáveis com menos frequência. Quando as tabelas crescem, planeio estratégias de arquivamento ou partição para que os dados quentes permaneçam na memória. Para os comandos de administração em alturas de pico, ligo o log_slow_admin_statements para identificar os factores de custo silenciosos. Pequenas correcções específicas compensam mais rapidamente do que grandes reorganizações.

Caraterísticas especiais em ambientes geridos e na nuvem

hospedagem gerenciada ou serviços em nuvem limitam frequentemente o acesso aos ficheiros. Nesses casos, defino log_output para TABLE e avalio o registo lento diretamente a partir da base de dados. No MySQL 8.0, também utilizo SET PERSIST para definir permanentemente as definições sem acesso direto ao my.cnf. Nos grupos de parâmetros da nuvem (por exemplo, para serviços geridos), introduzo as mesmas variáveis e programo uma janela de manutenção para o reinício.

-- Se permitido: Definições persistentes sem reiniciar
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;
SET PERSIST log_output = 'TABLE'; -- Alternativa a FILE para acesso restrito a ficheiros

-- Avaliação com log_output=TABLE
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 50;

NotaCom tráfego pesado, log_output=FILE pode ser mais eficiente, pois o registo em tabela gera sobrecarga adicional. Em ambientes restritivos, no entanto, TABLE é frequentemente o único caminho a seguir. Defino então limites mais apertados (por exemplo, min_examined_row_limit) para manter o volume controlável.

Rotação, armazenamento e proteção de dados

Rotação evita que os registos encham o disco. Faço a rotação diariamente ou por tamanho, comprimo os ficheiros antigos e mantenho uma política de retenção clara (por exemplo, 14 dias). Após a rotação, desencadeio uma descarga de registos para que o MySQL escreva de forma limpa no novo ficheiro. Isto mantém a análise e o funcionamento estáveis.

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
  diariamente
  rodar 14
  tamanho 100M
  comprimir
  missingok
  notifempty
  criar 640 mysql adm
  postrotate
    test -x /usr/bin/mysqladmin || exit 0
    /usr/bin/mysqladmin flush-logs
  endscript
}

Proteção de dados é obrigatório: os registos lentos podem conter valores de parâmetros. Limito rigorosamente o acesso (permissões de ficheiros, grupos) e verifico se estão a ser registados dados sensíveis. Se necessário, trabalho com a ligação de parâmetros na aplicação para que não apareça texto simples personalizado no registo. Para a partilha em equipa, prefiro partilhar relatórios agregados em vez de registos em bruto.

Utilizar o esquema de desempenho e o esquema sistémico

Regime de desempenho fornece métricas mesmo sem um registo lento ativado. Ativo os consumidores relevantes para as declarações e, em seguida, analiso as vistas do sistema. Vantagem: posso ver os principais resumos e a distribuição da latência quase em tempo real, agrupados em consultas semelhantes.

-- Ativar o consumidor para o histórico de instruções (tanto quanto possível em tempo de execução)
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_statements_history', 'events_statements_history_long');

-- Visão geral rápida dos grupos de consulta dispendiosos
SELECT schema_name, digest_text, count_star,
       ROUND(sum_timer_wait/1e12, 3) AS total_s,
       ROUND(avg_timer_wait/1e9, 3) AS avg_ms,
       ROUND(max_timer_wait/1e9, 3) AS pmax_ms
FROM sys.statement_analysis
ORDER BY sum_timer_wait DESC
LIMIT 10;

Combinação O sistema de registo de lentidão (outliers lentos) e o esquema de desempenho (largura, frequência) mostram-me tanto os casos individuais como os factores de custo sistemáticos. Comparo ambas as visualizações com os padrões de tráfego para criar tarefas prioritárias.

EXPLAIN ANALYZE e Traço do Optimizador

EXPLAIN ANALYZE (a partir do MySQL 8.0.18) complementa as estimativas com os tempos medidos. Comparo as estimativas das linhas com os valores reais e descubro os erros de avaliação do optimizador. No caso de planos contraditórios, analiso o rastreio do optimizador para ver porque é que um índice não foi selecionado.

-- Plano com valores medidos
EXPLICAR ANALISAR
SELECT o.id, o.created_at
FROM encomendas o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

-- Rastrear decisões do optimizador
SET optimizer_trace="enabled=on";
SELECT ...; -- consulta a ser analisada
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimiser_trace="enabled=off";

ResultadoSe as estimativas estiverem muito erradas, actualizo as estatísticas (ANALYZE TABLE), adiciono histogramas ou reformulo os índices/consultas para que a seletividade tenha efeito mais cedo.

Reescrever padrões que quase sempre funcionam

OU para UNION ALLAs condições OR múltiplas em colunas diferentes impedem frequentemente a utilização do índice. Separo-as em duas consultas selectivas e junto os resultados se for possível excluir os duplicados.

-- Antes:
SELECT * FROM t WHERE a = ? OR b = ?;

-- Melhor:
(SELECT * FROM t WHERE a = ?)
UNION ALL
(SELECT * FROM t WHERE b = ? AND a  ?);

PaginaçãoO OFFSET/LIMIT torna-se dispendioso com o aumento do OFFSET. Mudo para a paginação por conjunto de teclas e utilizo uma chave de ordenação adequada (idealmente indexada e monotónica).

-- Caro:
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

-- Melhor (conjunto de chaves):
SELECT id, title
FROM posts
WHERE created_at < :cursor
ORDER BY data_criada DESC
LIMIT 50;

Índices compostos: Contagens de ordem. Eu ordeno as colunas no índice de acordo com a seletividade e o padrão de consulta (filtro WHERE primeiro, depois ordeno as colunas). O objetivo é um índice de cobertura que evite filesorts e pesquisas em tabelas.

Índices funcionais e gerados no MySQL 8

Expressões em WHERE/JOIN muitas vezes bloqueiam os índices. No MySQL 8.0, eu indexei especificamente expressões ou trabalhei com colunas geradas para criar a possibilidade de cálculo. Isto é particularmente útil para CASTs para meta-valores numéricos ou campos JSON.

-- Exemplo: ordenação numérica num campo de texto
ALTER TABLE product ADD COLUMN price_num DECIMAL(10,2)
  GENERATED ALWAYS AS (CAST(price AS DECIMAL(10,2))) STORED;
CREATE INDEX idx_product_price_num ON product(price_num);

-- Consulta sem CAST e com índice
SELECT * FROM produto
WHERE price_num BETWEEN 10 AND 50
ORDER BY price_num;

PráticaTesto se o novo índice funciona realmente (EXPLAIN) e meço o efeito no registo lento. As colunas geradas também ajudam a filtrar os prefixos ou as variantes normalizadas (LOWER(email)) de forma eficiente.

Uma abordagem mais direcionada para os padrões CMS/loja

Meta-tabelas (por exemplo, wp_postmeta) beneficiam de índices combinados em (post_id, meta_key) ou (meta_key, meta_value). Para filtros frequentes em meta_value_numeric, utilizo colunas geradas como acima, em vez de fazer CAST em cada consulta. Acelero as páginas de pesquisa eliminando as redundâncias (desnormalização ligeira) e tornando o acesso de leitura mais fácil para o índice.

-- Típico para WordPress: acesso rápido aos metadados de um post
CREATE INDEX idx_postmeta_postid_metakey ON wp_postmeta(post_id, meta_key);
CREATE INDEX idx_postmeta_metakey_metavalue ON wp_postmeta(meta_key, meta_value(100));

Finalizar a compra-Optimizo os percursos para minimizar os tempos de bloqueio: transacções curtas, apenas as linhas necessárias e índices exactos para as condições WHERE utilizadas. No caso dos relatórios, planeio a agregação assíncrona (tabelas intermédias) para que os fluxos de utilizadores não sofram atrasos.

Limites do registo lento e métricas suplementares

Muitas consultas pequenas e rápidas não são perceptíveis no registo lento, mas contribuem para a carga. Por isso, também controlo o rendimento (consultas/seg.), os percentis 95/99 e a proporção de consultas sem índice. Nas ferramentas Performance Schema ou APM, reconheço padrões N+1, que depois resolvo especificamente através de junções, processos de carregamento em lote ou caching.

Amostragem é útil quando os registos se tornam demasiado grandes. Aumento ligeiramente o long_query_time ou defino o min_examined_row_limit para incluir apenas as consultas relevantes. Importante: Sempre anote as alterações para que as séries de tempo permaneçam comparáveis.

Método de trabalho: Dos resultados à melhoria sustentável

Linha de base Primeiro: guardo um relatório anterior (janela temporal, tráfego, configuração). Em seguida, optimizo uma família de consultas após a outra e comparo janelas de tempo idênticas. Cada correção é documentada no repositório (O quê? Porquê? Valor medido antes/depois?). Desta forma, os sucessos permanecem rastreáveis e à prova de regressão.

# Procedimento grosseiro (exemplo)
1) pt-query-digest --since '7d' slow-query.log > baseline.txt
2) Selecionar os 3 melhores digests de consultas (por tempo total)
3) EXPLAIN/EXPLAIN ANALYZE, trabalhar o índice e reescrever as propostas
4) Gerar dados de teste, simular o perfil de carga
5) Rollout com monitorização (limites mais apertados durante 48h)
6) Relatório de comparação: pt-query-digest --since '48h' > after.txt
7) Documentar o resultado, planear a próxima fase

Estabilidade do plano Acompanho o optimizador ao longo do tempo: se os planos mudam (novas versões, estatísticas alteradas), verifico os histogramas, ANALYZE TABLE e o panorama dos índices. Só defino dicas seletivamente e de forma documentada para não sobrecarregar permanentemente o optimizador.

Resumo em etapas claras

Início significa: ativar o registo, definir valores-limite sensatos, recolher a primeira semana de dados. Em seguida, faço um resumo com o pt-query-digest, estabeleço prioridades de acordo com o tempo total e a variância e selecciono os principais drivers. Optimizo as consultas com o EXPLAIN, condições de cálculo e índices adequados e controlo o bloqueio com transacções mais curtas. No lado do servidor, defino buffers, tabelas temporárias e estratégias de descarga de forma adequada. Finalmente, automatizo os alarmes e repito o ciclo regularmente - isto mantém a base de dados rápida, mesmo quando o tráfego e os volumes de dados aumentam.