Você se preocupa com o desempenho das suas aplicações? Então é imprescindível que você saiba o que é o problema do N + 1 e também conheça como identificá-lo e corrigi-lo. Essa é uma grave falha que diversos programadores iniciantes (e até intermediários, por incrível que pareça) não conhecem, causando grande perda de performance em suas aplicações.
O que é o problema do N + 1
Vamos considerar uma base de dados com duas tabelas: uma de usuários e outra de posts, como ilustrado na imagem a seguir.
A tabela Usuários armazena as informações 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 básica. Agora considere que você necessita desenvolver um painel onde serão exibidos todos os usuários e os títulos de todos os posts de cada um deles. O que alguns programadores 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;
Imaginando que temos 100 usuários, serão executadas todas as consultas abaixo:
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;
Note que seriam executadas 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. Logo, devemos usar sempre o mínimo de consultas, que forneçam o melhor desempenho. E 101, definitivamente, não é o mínimo para chegarmos ao resultado esperado de forma eficiente.
Como identificar o problema do N + 1
Sempre que existir uma consulta dentro de um loop, é bem provável que ali haja esse problema. Vamos a um simples exemplo, usando PHP:
$PDO = new PDO( "dados de conexão" );
$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 um SELECT dentro do loop. Ou seja, serão executadas N consultas, sendo N o total de elementos do array $users (100, no nosso caso).
Como resolver o problema do N + 1
Vamos reduzir o números de consultas de N + 1 para apenas duas! É uma bela redução, né? 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);
Modificando o código anterior, podemos resolver o Problema do N + 1 para o caso que analisamos.
$PDO = new PDO( "dados de conexão" ); $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 );
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 $usersIDs, 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. E olha que o exemplo tinha poucos dados. Um sistema real geralmente tem muito mais que 100 usuários e mais de 1.000 registros relacionados a eles. Portanto, o ganho de desempenho pode ser ainda maior que 13 vezes, dependendo da aplicação.
Por que não usei JOIN em vez do IN
Outra alternativa para resolver esse problema seria usar JOIN em vez de IN. Teríamos apenas uma consulta em vez de duas.
SELECT <a href=”http://u.id” target=”_blank”>u.id</a>, u.nome, p.titulo FROM usuarios u INNER JOIN posts p ON p.user_id = <a href=”http://u.id” target=”_blank”>u.id</a>
Mas isso não significa que será mais eficiente. O foco aqui é desempenho, não somente 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 <a href="http://u.id" target="_blank">u.id</a>, u.nome, p.titulo FROM usuarios u INNER JOIN posts p ON p.user_id = <a href="http://u.id" target="_blank">u.id</a>"; $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 1.000 posts. Em seguida, vou inserir mais 4.000 posts, resultando em 100 usuários e 5000 posts. Alguns resultados de execução para 100 usuários e 1.000 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 5.000 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, 1.300%), você definitivamente precisa saber identificar e resolver o Problema do N + 1 nas aplicações que você desenvolve ou dá manutenção. Sobre usar IN ou JOIN, é sempre bom analisar sua modelagem e fazer alguns testes, para chegar à conclusão ideal para seu caso.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?