O Problema do N + 1: como identificá-lo, corrigi-lo e ganhar desempenho em suas aplicações

Problema do N + 1

Você se importa com o Desempenho de suas aplicações? Então é fundamental que você entenda o que é o Problema do N + 1 e saiba como identificá-lo e corrigi-lo.

Essa é uma grande falha que muitos programadores iniciantes (e até intermediários) não conhecem, deixando as aplicações muito lentas e tendo uma grande perda de performance.

Continue lendo este artigo, que vou explicar em detalhes o que é o Problema do N + 1 e como solucioná-lo.

O Que é o Problema do N + 1

Para explicar o que é esse problema, vamos considerar um banco de dados com duas tabelas: uma tabela de usuários e uma tabela de posts, como mostrado na imagem a seguir.

Modelagem para exemplificar o Problema do N + 1

Modelagem para exemplificar o Problema do N + 1

A tabela Usuários armazena os dados dos usuários, como nome, email, senha etc. A tabela de posts, além dos dados dos posts, como título e conteúdo, também possui o campo user_id, que referencia o ID do usuário que criou o post.

Até aí tudo bem. Modelagem bem simples.

Agora imagine que você precisa montar uma tela onde serão exibidos todos os usuários e os títulos de todos os posts de cada um deles.

O que muitos fariam é o seguinte:

  1. Selecionar todos os usuários, com a consulta a seguir:
    SELECT id, nome FROM usuarios;
  2. Em seguida, para cada usuário, seria feita a consulta a seguir:
    SELECT titulo FROM posts WHERE user_id = id_do_usuario;

Supondo que temos 100 usuários, serão executadas todas estas consultas:

SELECT id, nome FROM usuarios;;
SELECT titulo FROM posts WHERE user_id = 1;
SELECT titulo FROM posts WHERE user_id = 2;
SELECT titulo FROM posts WHERE user_id = 3;
...
SELECT titulo FROM posts WHERE user_id = 100;

Veja que seriam feitas 101 consultas. E é aqui que você vai entender o motivo do N + 1.

Considerando N o número de usuários, nosso N vale 100. E N + 1 é 101, que foi o total de consultas realizadas.

Consultas a bancos de dados tomam tempo. Muito tempo. Por isso devemos usar sempre o mínimo de consultas.

E 101, definitivamente, não é o mínimo para chegarmos ao resultado esperado.

Esse número é BEM MENOR que 101… MUTO MENOR MESMO!

Você vai entender logo logo.

Como Identificar o Problema do N + 1

É simples identificar esse problema. Sempre que houver uma consulta dentro de um loop, é bem provável que ali esteja ocorrendo esse problema.

Vamos a um simples exemplo, usando PHP:

$PDO = new PDO( "dados de conexão aqui" );
$sql = "SELECT id, nome FROM usuarios";
$stmt = $PDO->prepare( $sql );
$result = $stmt->execute();
$users = $stmt->fetchAll( PDO::FETCH_ASSOC );
 
foreach ( $users as $user )
{
    $sql = "SELECT titulo FROM posts WHERE user_id = :user_id";
    $stmt = $PDO->prepare( $sql );
    $stmt->bindParam( ':user_id', $user['id'] );
    $result = $stmt->execute();
    $posts = $stmt->fetchAll( PDO::FETCH_ASSOC );
}

NOTA: Caso você não conheça PDO, recomendo ler este artigo.

Veja que existe uma consulta dentro do loop. Ou seja, serão feitas N consultas, sendo N o total de elementos do array $users (100, no nosso caso).

Como Resolver o Problema do N + 1

Resolver esse problema é muito simples!

Vamos reduzir o números de consultas de N + 1 para APENAS 2!

Vamos usar SOMENTE DUAS CONSULTAS!

A primeira vai continuar sendo a mesma de antes, responsável por buscar todos os usuários:

SELECT id, nome FROM usuarios

Porém, a segunda vai usar os dados dessa consulta e trazer todos os posts de todos os usuários. Depois basta iterar sobre o array, dentro da programação.

A consulta usará a função IN() na cláusula WHERE, desta forma:

SELECT titulo FROM posts WHERE user_id IN(1,2,3,4,5,...,100);

Vou modificar o código anterior e mostrar como resolver o Problema do N + 1 para o caso que analisamos.

$PDO = new PDO( "sua conexão aqui" );
$sql = "SELECT id, nome FROM usuarios";
$stmt = $PDO->prepare( $sql );
$result = $stmt->execute();
$users = $stmt->fetchAll( PDO::FETCH_ASSOC );
 
$userIDs = array_column( $users, 'id' );
$sqlIDs = implode( ',', $userIDs );
$sql = sprintf( "SELECT titulo FROM posts WHERE user_id IN(%s)", $sqlIDs );
$stmt = $PDO->prepare( $sql );
$result = $stmt->execute();
$posts = $stmt->fetchAll( PDO::FETCH_ASSOC );

Voilà!

Note que, até a criação da variável $users, nada mudou. Depois disso é que apareceram as novidades. Vamos analisá-las com mais calma.

Criei a variável $userIDs, que é um array com todos os valores do campo id que estavam no array $users. Isso ficou fácil usando a função array_column.

Em seguida criei a variável $sqlIDs, usando a função implode. Essa variável é uma string com todos os IDs separados por vírgula, de forma que possa ser usada no parâmetro da função IN da SQL.

Depois criei a variável $sql, que vai buscar todos os posts com uma única consulta.

Analisando o Ganho de Desempenho

Vamos fazer um simples teste de desempenho, para comparar os dois códigos que vimos anteriormente.

A análise é simples: pegar a hora de início do script, a hora final e subtrair. Para isso, usarei a função microtime, para pegar o tempo atual em micro-segundos, e a função number_format, para formatar o tempo de maneira a ficar mais facilmente legível.

A ideia geral, em termos de código é a seguinte:

$inicio = microtime( true );
 
// código cujo desempenho queremos testar
 
$fim = microtime( true );
$diff = number_format( $fim - $inicio, 15, ',', '.' );
 
echo "Tempo total: " . $diff . PHP_EOL;

Para testar, criei 100 registros na tabela de usuários, com valores aleatórios. Criei 1000 registros, também aleatórios, na tabela de posts.

Vamos aos resultados…

O script contendo o Problema do N + 1 foi executado em 0,0198240280 segundos.

Já o script sem o problema foi executado em 0,0015311241 segundos. Ou seja, aproximadamente 13 vezes mais rápido.

13 VEZES MAIS RÁPIDO!

E olha que o exemplo tinha poucos dados. Um sistema real geralmente tem muito mais que 100 usuários e mais de 1000 registros relacionados a eles.

Por que não usei JOIN em vez do IN

É possível resolver esse problema com apenas uma consulta, usando JOIN em vez de IN, desta forma:

SELECT u.id, u.nome, p.titulo FROM usuarios u INNER JOIN posts p ON p.user_id = u.id

Mas isso não significa que será mais eficiente.

O foco aqui é Desempenho, não apenas menos consultas.

Se cada usuário tivesse apenas um post, poderia ser mais eficiente usar JOIN. Mas se, por exemplo, cada usuário tiver, em média, 100 posts, haverá milhares de dados repetidos trafegando entre o banco de dados e sua aplicação. E isso tomará tempo e memória.

Já que estamos fazendo testes práticos, vamos comparar os tempos de execução de dois scripts, um usando o IN (que é o mesmo código que mostrei anteriormente) e outro usando JOIN.

O código usando JOIN é este:

$PDO = new PDO( "conexão aqui" );
$sql = "SELECT u.id, u.nome, p.titulo FROM usuarios u INNER JOIN posts p ON p.user_id = u.id";
$stmt = $PDO->prepare( $sql );
$result = $stmt->execute();
$posts = $stmt->fetchAll( PDO::FETCH_ASSOC );

Vou mostrar os resultados para um conjunto de 100 usuários e 1000 posts. Em seguida, vou inserir mais 4000 posts, resultando em 100 usuários e 5000 posts.

Alguns resultados de execução para 100 usuários e 1000 posts:

$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,002864837646484
  Tempo total com IN: 0,002188920974731
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,002944946289063
  Tempo total com IN: 0,001436948776245
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,003201961517334
  Tempo total com IN: 0,001392841339111
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,002935886383057
  Tempo total com IN: 0,001415014266968
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,002933025360107
  Tempo total com IN: 0,001442193984985

Agora vamos a alguns resultados usando 100 usuários e 5000 posts:

$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,011201143264771
  Tempo total com IN: 0,002874851226807
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,010854005813599
  Tempo total com IN: 0,002540111541748
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,010561943054199
  Tempo total com IN: 0,002592086791992
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,010941982269287
  Tempo total com IN: 0,002531051635742
 
$ php select_join.php && php select_in.php
Tempo total com JOIN: 0,010515928268433
  Tempo total com IN: 0,002762794494629

Fiz cinco execuções de cada script para cada conjunto de dados.

De todos os 10 testes, 9 resultaram em desempenho superior na versão usando IN. O uso do JOIN ficou superior apenas na primeira execução com 1000 posts.

Não estou falando para abandonar o JOIN e usar só o IN. Tudo depende da sua modelagem e da quantidade de dados. Para poucos dados ou relação 1 Para 1, o JOIN será melhor. Mas em relação 1 Para Muitos, o IN tende a ter melhor desempenho.

Conclusão

Melhorias de desempenho são sempre muito bem-vindas. Qualquer ajuste que dê 5% de ganho já é válido.

E considerando que aqui tivemos 13 vezes mais (ou seja, 1300%), você definitivamente precisa saber identificar e resolver o Problema do N + 1 nas aplicações que você desenvolve ou dá manutenção.

É uma medida simples e que dá resultados fantásticos!

Otimizações Avançadas

Há inúmeras outras formas mais avançadas de otimizar seu banco de dados.

Para conhecer essas técnicas, recomendo o curso Tuning MySQL (curso em português), especialmente voltado para o MySQL, um dos mais usados do Mercado.

Você vai aprender passo-a-passo como instalar e configurar o MySQL da forma mais otimizada para a sua aplicação, conhecendo cada um dos arquivos de configuração e suas diretivas. Também vai descobrir como realizar análises de desempenho e testes de stress, para comparar o desempenho padrão e o desempenho obtido após o tuning.

Clique Aqui e conheço o Curso Tuning MySQL

 

Aprenda Ainda Mais

15 Dicas, Boas Práticas e Fundamentos do PHP

Conheça Dicas FUNDAMENTAIS para programar em PHP de forma Profissional

Não se considere Programador PHP sem antes ler este guia e adotar estas práticas!

Baixe gratuitamente este guia com 15 Dicas de PHP

The following two tabs change content below.
Graduado em Ciência da Computação, pela Universidade Federal do Paraná (UFPR), é desenvolvedor de software desde 2008, com foco em Desenvolvimento Web com PHP.
  • Gabriel Pedro

    É uma boa dica para iniciantes/intermediários. Mas por que não apresentou uma solução com INNER JOIN? Reduziria para uma consulta só.

  • Pedro Gabriel

    Tenta depois com uma subconsulta no IN
    acredito que melhoraria ainda mais o desempenho

  • Marcos

    Mas vejamos que, para você criar o segundo select (SELECT titulo FROM posts WHERE user_id IN(%s)) você terá que percorrer a lista de usuários 2 vezes, ou então ficar percorrendo o array. E no caso de sua modelagem, se user_id for obrigatório, seria o mesmo que simplesmente colocar “select titulo from posts”, sem cláusula where. Em ambos os casos teria que ser avaliado o desempenho (complexidade do algoritmo) aplicado nas funções de busca de array (para pegar exatamente o nome do usuário com o título de cada post). E outra, se tiver 2000 usuários, mas apenas 100 postaram, teríamos 1900 linhas de usuários carregadas (e sendo repassadas para a segunda consulta) desnecessariamente. Os bancos relacionais otimizam as consultas quando utilizando o join (e ainda mais com os índices corretos). Teria que avaliar bem antes de optar por uma abordagem ou outra.

  • Jonathan Hayama

    Bastante interessante, mas tem uma pequena diferença nos códigos produzidos…
    Na primeira versão há um agrupamento de postagens por usuário, o que não acontece na segunda versão.

    Outro problema é que na segunda versão você não utilizou o bindParam, tudo bem que seus dados vieram do banco direto, mas, porem, todavia contudo, talvez fosse interessante…

    Ainda uma última informação, não cheguei a testar, mas acredito que já pode haver um ganho de desempenho se você utilizar o prepare() antes do loop, mais ou menos assim:

    ...
    $users = $stmt->fetchAll( PDO::FETCH_ASSOC );
    $sql = "SELECT titulo FROM posts WHERE user_id = :user_id";
    $stmt = $PDO->prepare( $sql );
    foreach ( $users as $user )
    {
    $result = $stmt->execute( array( 'user_id' => $user['id'] ) );
    ...
    }

  • Diogo Oliveira Mascarenhas

    Parabéns pelo artigo, uma sugestão:
    Seria interessante adicionar a performance (com as sub-consultas e com o JOIN adicionando um índice no [Posts.user_id]).

    Um Abraço!

  • David

    Parabéns pelo post! Só lembrando que o IN no Oracle por exemplo tem um limite de 1000 itens e caso seja uma aplicação com muitos “usuários” ser tornará inviável o uso da clausula IN.

  • Jackson Lemos Moreira

    O resultado impressiona. Muito bom.
    Obrigado

  • Jackson Lemos Moreira

    Testei aqui com campos VARCHAR deu um erro.
    Mas funcionou com campos INT.
    Será algum detalhe no sprintf para funcionar com VARCHAR?

    Alias aproveitando…
    Tive problemas recentemente ao tentar deletar vários registros com IN utilizando campos VARCHAR.

    Alguma sugestão?
    Obrigado! Abs

    • Se o campo for Varchar, é preciso colocar o valor entre aspas simples. Pode ser esse o problema.

      • Jackson Lemos Moreira

        Depois do implode obtenho assim ‘cafe,milho,arroz’
        Para usar com o IN no SQL, qual a dica para vir separado ‘cafe’,’milho’,’arroz’?

        Obrigado

        • Nesse caso, é melhor montar a SQL em um loop.

          Veja este meu comentário: http://rberaldo.com.br/inserindo-multiplos-registros-em-tabela-de-banco-de-dados/#comment-2362499911

          • Jackson Lemos Moreira

            Legal, vou ler!
            Obrigado

          • Jackson Lemos Moreira

            Li, mas confesso que não consegui relacionar com a minha dúvida, sobre utilizar string com o IN.

            No caso do campo de relacionamento ser string, ao invés de INT, é melhor fazer a consulta dentro do loop, é isso?

            Outra coisa, a array_column somente lista os ids do usuário para utilização com WHERE. Para utilizar outros campos do usuário na listagem, nome por exemplo, com a técnica é possível?
            Obrigado

            • Existe uma enorme diferença entre **montar** a consulta dentro do loop e **executar** a consulta dentro do loop. Eu mostrei como montar a consulta no loop. Mas ele só será executada uma vez. Qualquer consulta executada dentro de um loop é sinal de implementação mal feita e problemas de desempenho.

              Não entendi sua dúvida com array_columns. Se seu array tem outras colunas, use os nomes delas. Tudo vai depender da estrutura do seu array. A ideia geral é sempre a mesma

              • Jackson Lemos Moreira

                Ops, desculpa a demora do retorno.
                Legal, entendi sobre montar a consulta.
                Vou estudar mais array, e volto com as dúvidas. Abs

  • Iván Gabriel Sosa

    Muy buen artículo y super últil. Unas pocas pruebas y debo admitir que siempre se puede mejorar un poco más el código. Saludos y gracias!!

  • Araújo Junior

    Muito bom Beraldo, obrigado por compartilhar.

  • Bruno Ramon de Almeida

    O desenho da modelagem está incorreto. O desenho mostra um relacionamento usuário(n) -> post(1), quando na verdade deveria ser o contrário.

    Fora isso, belo post, seu blog é muito bom.

  • Rafael Rezende

    Classe A, desempenho é extremamente importante, parabéns, dicas simples e eficientes!

    • Obrigado!
      Desempenho é fundamental, mesmo. E às vezes uma simples dica pode trazer ganhos muito significativos :)

  • Davidson

    Muito bom, parabéns pelas ótimas dicas, sem duvidas o desempenho deve ser levado em conta na hora, já tinha visto este post, voltei só pra rever, parabéns novamente!

    • Obrigado! :)
      De fato, desempenho é sempre fundamental. E o usuário fica mais feliz! ;)