Uma tela lenta não demonstra que o banco de dados seja o problema, nem que um índice seja a solução. A mesma percepção pode ter origem em código PHP, saturação de conexões, uma chamada HTTP externa, serialização de respostas, bloqueios ou uma consulta que retorna dados demais. Saber como investigar consultas lentas em PHP consiste em reunir evidências antes de modificar o esquema ou adicionar otimizações que poderiam encarecer as escritas.
Separar a latência da aplicação da latência dos dados
Comece decompondo o tempo total de uma requisição. Registre uma rota ou comando identificável, o horário de início e fim, as consultas executadas, suas durações e as dependências externas. Não basta medir o tempo médio: uma API pode parecer saudável e, ainda assim, falhar para determinados filtros, clientes ou páginas profundas.
Para cada incidente, convém diferenciar:
- Tempo em PHP: transformação de coleções, loops, serialização JSON, geração de documentos ou uso excessivo de memória.
- Tempo de banco de dados: duração de cada consulta, espera por bloqueios, abertura de conexão e número de linhas transferidas.
- Tempo de rede e dependências: caches remotos, APIs de terceiros, armazenamento de arquivos, filas ou serviços de identidade.
- Tempo de fila: requisições que aguardam workers PHP, conexões disponíveis ou recursos do banco de dados.
Use traces ou logs estruturados com um identificador de requisição. Uma consulta isolada de 20 ms pode se transformar em um problema de segundos se for executada centenas de vezes na mesma resposta. Por outro lado, uma consulta de 500 ms pode não ser a causa principal se o processo permanecer aguardando um serviço externo por vários segundos.
Coletar evidências antes de alterar o código
Capture a consulta parametrizada e, separadamente, parâmetros representativos. Evite registrar segredos, dados pessoais completos ou valores que não sejam necessários para reproduzir o caso. Uma busca por um estado frequente não se comporta da mesma forma que uma busca por um identificador único; avaliar apenas o caso mais conveniente leva a decisões equivocadas.
A evidência mínima deve incluir:
- Rota, trabalho assíncrono ou comando afetado, além de sua frequência.
- Duração observada, percentis altos e momento de ocorrência.
- SQL, parâmetros tipados e número de execuções por requisição.
- Linhas retornadas e, quando possível, linhas lidas ou examinadas.
- Tamanho aproximado das tabelas e distribuição dos valores filtrados.
- Concorrência, operações de escrita simultâneas e bloqueios relevantes.
Os logs lentos do mecanismo ajudam a descobrir candidatos, mas não substituem um trace da aplicação: normalmente não indicam qual endpoint construiu uma consulta nem quantas vezes ela se repetiu. No MySQL e no PostgreSQL, combine essas informações com métricas de conexão, CPU, I/O e tempos de espera para não confundir um plano deficiente com uma infraestrutura temporariamente saturada.
Localizar padrões que multiplicam o custo
Antes de analisar uma instrução complexa, procure padrões frequentes. O N+1 surge quando uma listagem obtém suas linhas principais e, em seguida, executa uma consulta adicional para cada relação. Embora cada consulta individual seja rápida, o volume de viagens ao banco de dados, o trabalho de planejamento e a contenção crescem com o tamanho da página.
Também são suspeitos filtros de baixa seletividade, como estados muito comuns; funções aplicadas sobre colunas filtradas; conversões implícitas de tipo; buscas com curinga inicial; ordenações de conjuntos grandes e paginação com OFFSET elevado. Solicitar SELECT * pode aumentar a transferência, a memória e o trabalho de leitura, mesmo quando o plano já usa um índice.
A correção nem sempre é uma consulta única. Carregar relações de forma controlada pode resolver um N+1, mas um eager loading sem limites pode criar uma consulta ou resposta enorme. Defina quais relações a rota realmente precisa, limite suas colunas e meça o efeito com o tamanho de página esperado.
Interpretar o plano de execução com dados reais
Execute EXPLAIN para conhecer o plano proposto e use a variante que incorpora execução real quando for segura e adequada ao ambiente. No PostgreSQL, EXPLAIN ANALYZE executa a consulta; uma instrução de modificação não deve ser analisada dessa forma sem entender seu efeito. No MySQL, as modalidades disponíveis dependem da versão e da configuração, mas o objetivo é o mesmo: comparar estimativas com trabalho real.
Um scan sequencial não é automaticamente ruim. Se a consulta precisa de uma parte grande de uma tabela pequena ou pouco seletiva, percorrê-la pode custar menos do que alternar entre índice e tabela. Em contrapartida, investigue quando o plano mostra muito mais linhas reais do que as estimadas, ordenações custosas, leituras temporárias, joins sobre conjuntos amplos ou loops internos executados muitas vezes.
Perguntas úteis ao revisar um plano
- Quantas linhas o otimizador esperava e quantas processou de fato?
- Qual nó concentra mais tempo, leituras ou iterações?
- O filtro é aplicado cedo ou depois de combinar grandes conjuntos?
- A ordenação ocorre sobre mais linhas do que as necessárias para a resposta?
- As estatísticas refletem a distribuição atual dos dados?
As discrepâncias entre estimativa e realidade podem exigir a atualização de estatísticas ou a revisão de tipos e condições, e não a criação imediata de um índice. O plano é uma explicação de uma execução sob certos parâmetros e carga; não uma ordem automática de alteração.
Escolher entre reescrita, paginação, acesso a dados e índice
Reduza primeiro o trabalho inevitável. Selecione apenas as colunas necessárias, aplique limites razoáveis, elimine relações não utilizadas e evite transportar históricos completos para o PHP para filtrá-los depois. Se o caso de uso pede explorar um histórico crescente, substitua a paginação profunda por paginação por cursor ou chave: por exemplo, continuar a partir de uma combinação estável de data e identificador em vez de descartar milhares de linhas com OFFSET.
Revise joins e filtros para que comparem colunas compatíveis e expressem a condição com clareza. Às vezes, convém consultar uma relação em blocos; em outras, uma única consulta bem delimitada é preferível. A decisão depende de cardinalidade, volume de resposta e frequência, não de uma regra universal.
Um índice composto ajuda quando corresponde ao padrão de acesso. Sua ordem importa: normalmente, as colunas de igualdade e seletivas devem facilitar a filtragem antes das usadas para intervalo ou ordenação, mas a consulta específica e o mecanismo determinam o resultado. Um índice também pode ajudar a evitar uma ordenação se cobrir uma ordem compatível, embora nem toda combinação de WHERE e ORDER BY permita isso.
Evite indexar colunas apenas porque aparecem em uma condição. Os índices ocupam espaço, consomem memória e adicionam trabalho a INSERT, UPDATE e DELETE. Índices redundantes ou de baixa utilidade podem piorar um sistema com muitas escritas. Verifique também se o índice permite obter as colunas necessárias sem leituras adicionais, mas não adicione colunas de cobertura sem medir o custo.
Exemplo hipotético: um histórico de operações
Suponha uma listagem que mostra operações por conta, status e data. À medida que o histórico cresce, a página 200 se degrada. A primeira hipótese poderia ser criar um índice sobre a data. No entanto, o trace revela uma consulta principal seguida de uma consulta para cada operação a fim de obter o usuário responsável: há um N+1. O plano da consulta principal também lê muitas linhas para descartar as anteriores por meio de OFFSET.
A sequência razoável seria carregar os responsáveis de forma agrupada ou por meio de um join limitado às colunas necessárias, substituir a paginação profunda por cursor baseado em created_at e id, e medir novamente. Só então é avaliado um índice alinhado ao filtro de conta, à ordenação estável e ao cursor. A alteração de acesso pode reduzir mais trabalho do que um índice isolado, e o índice final também deve ser verificado em relação à criação de novas operações.
Validar sob carga e monitorar regressões de escrita
Compare antes e depois com parâmetros, distribuição de dados e concorrência representativos. Meça duração, linhas processadas, leituras, uso de CPU, memória em PHP, tamanho da resposta e número de consultas por requisição. Para um índice novo, meça também a latência e a capacidade das operações de escrita afetadas.
Defina limites de aceitação explícitos: por exemplo, uma redução verificável do percentil alto da rota sem aumentar de forma inaceitável o tempo de criação ou atualização. Teste casos vazios, valores muito frequentes, filtros raros, primeiras e últimas páginas, e permissões que alterem o escopo dos dados.
Implantar alterações de forma observável e reversível
Separe, quando possível, a implantação do código da criação do índice. A criação de índices pode competir por recursos ou adquirir bloqueios conforme o mecanismo, a operação e o ambiente. Planeje o momento, revise o método suportado pelo seu banco de dados e monitore a duração, os erros e a pressão sobre I/O.
Introduza a nova consulta gradualmente se a arquitetura permitir e mantenha uma reversão clara: restaurar a consulta anterior, desativar uma rota de acesso alternativa ou remover um índice que se mostre prejudicial. Uma reversão não substitui os backups nem a revisão de migrations, mas reduz o tempo de exposição diante de um comportamento inesperado.
Checklist para uma melhoria repetível
- Identifique a rota, o sintoma e os parâmetros que o reproduzem.
- Separe o tempo de PHP, banco de dados, rede e dependências externas.
- Quantifique repetições, linhas retornadas e linhas processadas.
- Procure N+1, paginação profunda, filtros pouco seletivos e ordenações.
- Revise o plano e confronte suas estimativas com a execução real.
- Teste redução de dados, reescrita e alteração de paginação antes de indexar.
- Projete o índice de acordo com o padrão completo de filtragem, ordenação e escrita.
- Valide leituras e escritas com carga representativa, observabilidade e reversão.
