Quando desenvolvemos aplicações PHP que interagem com bancos de dados, a performance das consultas é um dos fatores mais críticos para a escalabilidade e a experiência do usuário. Uma aplicação que funciona bem em ambiente de desenvolvimento pode se tornar extremamente lenta em produção quando o volume de dados cresce. Nesta aula, vamos explorar técnicas e boas práticas para otimizar o acesso ao banco de dados, focando em problemas comuns como o N+1, na criação de índices eficientes, no carregamento antecipado de relacionamentos (eager loading) e no uso de ferramentas de profiling para identificar gargalos.

O objetivo é que você, como desenvolvedor, consiga diagnosticar e resolver problemas de performance de forma sistemática, utilizando as ferramentas adequadas e escrevendo consultas mais eficientes. Vamos abordar desde conceitos teóricos até exemplos práticos com código PHP e SQL, sempre com foco em aplicações reais.

N+1 problem

O problema N+1 é uma das causas mais comuns de lentidão em aplicações que utilizam ORMs (Object-Relational Mapping) como Eloquent (Laravel), Doctrine ou até mesmo em consultas manuais. Ele ocorre quando você carrega uma lista de registros (por exemplo, usuários) e, para cada registro, executa uma nova consulta para buscar dados relacionados (por exemplo, os pedidos de cada usuário). Se você tem N usuários, fará 1 consulta para buscar os usuários e mais N consultas para buscar os pedidos, totalizando N+1 consultas. Isso pode degradar seriamente a performance, especialmente quando N é grande.

Vamos ilustrar com um exemplo. Suponha que você tenha duas tabelas: users e orders, onde um usuário pode ter muitos pedidos. Em uma abordagem ingênua, você poderia fazer o seguinte:

$users = DB::table('users')->get(); // consulta 1

foreach ($users as $user) {
    $orders = DB::table('orders')->where('user_id', $user->id)->get(); // consulta N
    // processa os pedidos
}

Se você tiver 100 usuários, executará 101 consultas ao banco. Isso não é eficiente. A solução é carregar os dados relacionados em uma única consulta usando JOIN ou, se estiver usando um ORM, utilizar o recurso de eager loading (que veremos a seguir). No caso de consultas manuais, você pode usar um JOIN:

$usersWithOrders = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.*')
    ->get();

Assim, você faz apenas uma consulta e obtém todos os dados. Porém, com JOIN, os dados dos usuários se repetem para cada pedido, o que pode ser tratado no PHP. O mais importante é entender que o N+1 é um problema de eficiência e que devemos evitá-lo sempre que possível.

Índices (visão geral)

Índices são estruturas de dados que o banco de dados utiliza para acelerar a busca e a ordenação de registros. Sem índices, o banco precisa fazer uma varredura completa na tabela (full scan) para encontrar os dados, o que é lento em tabelas grandes. Com índices, o banco pode localizar rapidamente as linhas que atendem a uma condição, reduzindo drasticamente o tempo de resposta.

Em PHP, quando você escreve consultas SQL, é crucial que as colunas usadas em cláusulas WHERE, ORDER BY e JOIN sejam indexadas. Por exemplo, se você frequentemente busca usuários por email, crie um índice na coluna email:

CREATE INDEX idx_users_email ON users(email);

No entanto, índices não são gratuitos: eles ocupam espaço em disco e podem tornar as operações de INSERT, UPDATE e DELETE mais lentas, pois o índice também precisa ser atualizado. Portanto, é importante criar índices apenas nas colunas que são realmente usadas em consultas frequentes. Uma boa prática é analisar as consultas da sua aplicação e identificar os padrões de acesso.

Além disso, índices compostos podem ser muito úteis quando você filtra por múltiplas colunas. Por exemplo, se você busca pedidos por user_id e status, um índice composto em (user_id, status) pode ser mais eficiente do que índices separados. A ordem das colunas no índice composto importa: coloque primeiro a coluna com maior seletividade (ou seja, que reduz mais o número de linhas).

Em PHP, você pode usar migrations (no Laravel) ou comandos SQL diretos para criar índices. Sempre verifique se os índices estão sendo usados pelo banco de dados usando ferramentas como EXPLAIN (que veremos em profiling).

Eager loading

Eager loading é uma técnica usada em ORMs para carregar relacionamentos de uma só vez, evitando o problema N+1. Em vez de executar uma consulta para cada registro, você especifica quais relacionamentos deseja carregar junto com a consulta principal, e o ORM gera uma única consulta (ou um número reduzido de consultas) usando JOINs ou consultas adicionais em lote.

No Laravel (Eloquent), por exemplo, você pode usar o método with() para fazer eager loading:

$users = User::with('orders')->get();

Isso executará duas consultas: uma para buscar todos os usuários e outra para buscar todos os pedidos desses usuários (usando um WHERE IN). O Eloquent então associa os pedidos aos respectivos usuários em memória. Isso é muito mais eficiente do que executar N+1 consultas.

Você também pode carregar relacionamentos aninhados, como User::with('orders.items')->get(), para carregar os itens de cada pedido. No Doctrine (PHP), você pode usar QueryBuilder com leftJoin e select para fazer eager loading. A ideia é sempre buscar todos os dados necessários em poucas consultas.

É importante usar eager loading com moderação, pois carregar muitos relacionamentos de uma vez pode resultar em consultas muito complexas e com muitos dados duplicados. Analise os requisitos da sua aplicação e carregue apenas o que for necessário. Você também pode usar o método load() para carregar relacionamentos após a consulta inicial, se necessário.

Profiling de queries

Profiling é o processo de medir o desempenho das suas consultas SQL para identificar gargalos. Não adianta otimizar às cegas; é preciso saber quais consultas são lentas e por quê. Existem várias ferramentas e técnicas para fazer profiling em PHP e no banco de dados.

Uma das formas mais simples é habilitar o log de consultas no seu framework. No Laravel, você pode usar o método DB::enableQueryLog() e depois recuperar as consultas executadas:

DB::enableQueryLog();

// suas consultas aqui

$queries = DB::getQueryLog();
print_r($queries);

Isso mostra todas as consultas executadas, com os parâmetros e o tempo de execução. Em outros frameworks, você pode usar bibliotecas de logging ou até mesmo o log do MySQL.

Outra ferramenta essencial é o EXPLAIN do MySQL (ou EXPLAIN ANALYZE no PostgreSQL). Ele mostra como o banco de dados executa uma consulta, incluindo quais índices usa, quantas linhas examina e se há operações custosas. Por exemplo:

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

O resultado mostra colunas como type, possible_keys, key, rows, etc. Se a coluna type for ALL, significa que está fazendo uma varredura completa (full scan), o que é um sinal de que um índice pode ser necessário. Se rows for muito alto, pode indicar que a consulta está examinando muitas linhas.

Além disso, você pode usar ferramentas de monitoramento como o mysqldumpslow (para MySQL) ou o pg_stat_statements (para PostgreSQL) para identificar consultas lentas em produção. No PHP, você pode usar bibliotecas como o Laravel Debugbar ou o Clockwork para visualizar as consultas em tempo real durante o desenvolvimento.

Uma boa prática é criar um ambiente de staging com dados de produção (ou uma amostra) para executar testes de performance. Isso ajuda a detectar problemas que só aparecem com volumes maiores de dados.

Boas práticas e observações finais

Além das técnicas específicas, algumas boas práticas gerais podem ajudar a manter o acesso ao banco eficiente:

  • Evite consultas dentro de loops; sempre que possível, busque os dados em lote.
  • Use paginação para limitar o número de registros retornados.
  • Selecione apenas as colunas que você realmente precisa, em vez de SELECT *.
  • Considere o uso de cache (como Redis ou Memcached) para consultas que não mudam com frequência.
  • Monitore constantemente o desempenho da sua aplicação em produção.

Lembre-se de que a otimização é um processo contínuo. Sempre meça o impacto das suas alterações e não otimize prematuramente. Use as ferramentas de profiling para tomar decisões baseadas em dados.

Referências

Exercícios

  1. **Identifique o problema N+1** em um código PHP que usa PDO para listar todos os usuários e seus pedidos. Escreva uma solução usando JOIN para resolver o problema.

    ✓ Resposta: O código original executaria uma consulta para cada usuário. A solução com JOIN é:
    $stmt = $pdo->query('SELECT users.*, orders.* FROM users LEFT JOIN orders ON users.id = orders.user_id');
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Agrupar pedidos por usuário em PHP
  2. **Crie um índice** apropriado para a tabela orders considerando que você frequentemente consulta por user_id e status. Escreva o comando SQL e explique por que um índice composto é melhor que dois índices separados.

    ✓ Resposta: 
    CREATE INDEX idx_orders_user_status ON orders(user_id, status);
    Um índice composto permite que o banco use o índice para filtrar por ambas as colunas em uma única estrutura, evitando a necessidade de combinar resultados de dois índices separados. Além disso, a ordem das colunas importa; coloque a coluna com maior seletividade primeiro.
  3. **Explique a diferença** entre lazy loading e eager loading em ORMs, e dê um exemplo de como usá-los no Laravel.

    ✓ Resposta: Lazy loading carrega o relacionamento somente quando é acessado, resultando em múltiplas consultas (N+1). Eager loading carrega o relacionamento antecipadamente usando uma consulta extra. No Laravel: $users = User::with('orders')->get(); para eager loading, e para lazy loading basta acessar $user->orders após a consulta inicial.
  4. **Use o EXPLAIN** para analisar a seguinte consulta e explique o que você observa: EXPLAIN SELECT * FROM orders WHERE user_id = 1; (assuma que há um índice em user_id).

    ✓ Resposta: O EXPLAIN deve mostrar que o índice idx_orders_user_id é usado (coluna key preenchida), o type será ref e rows será o número de pedidos do usuário 1. Isso indica que a consulta está eficiente, usando o índice para buscar apenas as linhas relevantes.
  5. **Escreva um script PHP** que habilite o log de consultas no Laravel e exiba todas as consultas executadas durante uma requisição.

    ✓ Resposta: 
    DB::enableQueryLog();

// executar suas consultas

$queries = DB::getQueryLog();
foreach ($queries as $query) {
    echo $query['query'] . ' - ' . $query['time'] . 'ms
    ';
}