PHP e SQL Benchmark: Múltiplos joins VS várias queries menores

Contexto

Muitas vezes nos deparamos com queries que possuem vários "joins" e isto sempre dificulta a manutenção, legibilidade e muitas vezes nos impede de trazer os dados da forma com que gostaríamos.

Pensando nisso, muitas pessoas se perguntam, fazer várias queries menores ou uma query grande???

Visando responder esta pergunta, elaborei um exemplo com um benchmark de tempo de resposta, para descobrirmos o que é melhor em termos de manutenção e também de performance.

Repositório da aplicação de teste e vídeo no youtube

https://github.com/WillRy/varios-joins-vs-varias-queries

https://youtu.be/QhWm5-PMIH8

Resumo: Caso não queira ler tudo

Uso de queries menores facilita a manutenção e performance, o benchmark pode ser visto no final do artigo.

Exemplo usado nos testes

Uma aplicação que faz o retorno de um JSON com os usuários, junto de seus endereços, telefones e arquivos.

Ou seja, 1 usuário tem N endereços, N telefones e N arquivos.

JSON que deve ser retornado

[
  {
    "id": "1",
    "first_name": "John",
    "phones": [
      {
        "phone_id": "1",
        "phone": "1111-1111",
        "user_id": "1"
      },
      {
        "phone_id": "2",
        "phone": "2222-2222",
        "user_id": "1"
      }
    ],
    "addresses": [
      {
        "address_id": "1",
        "address": "Rua 4",
        "user_id": "1"
      }
    ],
    "files": [
      {
        "file_id": "1",
        "file": "documento.pdf",
        "extension": "pdf",
        "url": "documento1.pdf",
        "user_id": "1"
      }
    ]
  }
  //   outros usuarios
]

A aplicação está dividida em 3 principais arquivos:

Connect.php

Responsável por retornar um singleton com a instância do PDO

join.php

Arquivo que realiza uma query usando várias queries

queries.php

Arquivo que realiza várias queries menores

Análise de uma só query e seus problemas

O problema de ter uma query com vários JOINS, é que os JOINS
que trazem resultados com "1 para N" ou "N para N", causam a duplicação de resultados, o que torna necessário uma normalização da estrutura de dados para agrupar os registros.

No exemplo abaixo, é feito um foreach para normalizar estes dados, o que dificulta a manutenção do código.

<?php

require_once __DIR__."/../config.php";

require_once __DIR__."/../Connect.php";




function listar() {
    $stmt = Connect::getInstance()->prepare("
        SELECT 
            u.id,
            u.first_name,
            p.id as phone_id,
            p.number as phone,
            a.id as address_id,
            a.name as address,
            f.id as file_id,
            f.name as file,
            f.extension,
            f.url as url
        fROM (
        SELECT 
            * 
        fROM users as u
        LIMIT 10 OffSET 0) as u
        join address as a on a.user_id = u.id
        join phone as p on p.user_id = u.id
        join files as f on f.user_id = u.id
    ");
    $stmt->execute();
    $usuarios = $stmt->fetchall(PDO::FETCH_OBJ);

    $resultados = [];
    /** Normalizar dados duplicados de relacionamentos ( 1 paRa N ) */
    foreach($usuarios as $usuario) {
        $resultados[$usuario->id]["id"] = $usuario->id;
        $resultados[$usuario->id]["first_name"] = $usuario->first_name;

        if (!empty($usuario->phone_id)) {
            $resultados[$usuario->id]["phones"][$usuario->phone_id] = [
                "phone_id" => $usuario->phone_id,
                "phone" => $usuario->phone,
                "user_id" => $usuario->id
            ];
        }

        if (!empty($usuario->address_id)) {
            $resultados[$usuario->id]["addresses"][$usuario->address_id] = [
                "address_id" => $usuario->address_id,
                "address" => $usuario->address,
                "user_id" => $usuario->id,
            ];
        }

        if (!empty($usuario->file_id)) {
            $resultados[$usuario->id]["files"][$usuario->file_id] = [
                "file_id" => $usuario->file_id,
                "file" => $usuario->file,
                "extension" => $usuario->extension,
                "url" => $usuario->url,
                "user_id" => $usuario->id,
            ];
        }
    }

    return $resultados;
}

$resultados = listar();

header('Content-Type: application/json; charset=utf-8');
echo json_encode($resultados);

Análise de várias queries menores

Ao realizar várias queries menores, podemos delegar para queries menores, a busca de dados "1 para N" e "N para N", facilitando o agrupamento dos dados.

CUIDADO
É importante que as queries menores, busquem os dados através de um where in usando os IDS.

Observem o exemplo abaixo, onde é possível ver que o foreach no método listar é usado somente para filtrar os arrays com os resultados das consultas menores

<?php

require_once __DIR__."/../config.php";

require_once __DIR__."/../Connect.php";



function listarEnderecos($idUsuarios = []) {
    $inQuery = implode(',', array_fill(0, count($idUsuarios), '?'));
    //$inQuery = ?,?,?,?
    $stmt = Connect::getInstance()->prepare("
        SELECT 
            a.id AS address_id,
            a.name AS address,
            a.user_id
        FROM address as a
        where a.user_id in ({$inQuery})
    ");
    foreach ($idUsuarios as $key => $id) {
        $stmt->bindValue(($key + 1), $id);
    }

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

function listarTelefones($idUsuarios = []) {
    $inQuery = implode(',', array_fill(0, count($idUsuarios), '?'));
    //$inQuery = ?,?,?,?
    $stmt = Connect::getInstance()->prepare("
        SELECT 
           p.id AS phone_id,
           p.number AS phone,
           p.user_id
        FROM phone as p
        where p.user_id in ({$inQuery})
    ");
    foreach ($idUsuarios as $key => $id) {
        $stmt->bindValue(($key + 1), $id);
    }

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

function listarArquivos($idUsuarios = []) {
    $inQuery = implode(',', array_fill(0, count($idUsuarios), '?'));
    //$inQuery = ?,?,?,?
    $stmt = Connect::getInstance()->prepare("
        SELECT 
           f.id AS file_id,
           f.name AS file,
           f.extension,
           f.url,
           f.user_id
        FROM files as f
        where f.user_id in ({$inQuery})
    ");
    foreach ($idUsuarios as $key => $id) {
        $stmt->bindValue(($key + 1), $id);
    }

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

function listar() {
    $stmt = Connect::getInstance()->prepare("
        SELECT 
            u.id,
            u.first_name
        FROM users as u
        LIMIT 10 OFFSET 0
    ");
    $stmt->execute();
    $usuarios = $stmt->fetchAll(PDO::FETCH_OBJ);

    $idUsuarios = array_column($usuarios, 'id');


    /** buscar dados de relacionamentos */
    $enderecosTodosUsuarios = listarEnderecos($idUsuarios);
    $telefonesTodosUsuarios = listarTelefones($idUsuarios);
    $arquivosTodosUsuarios = listarArquivos($idUsuarios);

    /** Normalizar dados duplicados de relacionamentos ( 1 PARA N ) */
    foreach($usuarios as $usuario) {
        $usuario->phones = array_values(array_filter($telefonesTodosUsuarios, function ($item) use ($usuario) {
            return $item->user_id === $usuario->id;
        }));
        $usuario->addresses = array_values(array_filter($enderecosTodosUsuarios, function ($item) use ($usuario) {
            return $item->user_id === $usuario->id;
        }));
        $usuario->files = array_values(array_filter($arquivosTodosUsuarios, function ($item) use ($usuario) {
            return $item->user_id === $usuario->id;
        }));
    }

    return $usuarios;
}

$usuarios = listar();

header('Content-Type: application/json; charset=utf-8');
echo json_encode($usuarios);

Performance

Em questão de performance, podemos medir o tempo de resposta as duas listagens, o benchmark realizado nestes exemplos foi feito com o Apache Benchmark.

O teste força 3000 mil requests com 50 requests concorrentes, forçando uma carga similar a de um site de médio porte

Executar um servidor da aplicaçao

php -S localhost:8000 -t public

Teste da query grande

ab -n 3000 -c 50 -k http://localhost:8000/join.php

Teste de várias queries

ab -n 3000 -c 50 -k http://localhost:8000/queries.php

Resultado

É possível observar que várias queries menores, além de facilitar a manutenção do código, também são mais performáticas, tendo um tempo de resposta muito menor.

Separando em queries menores, podemos ter métodos reutilizáveis para buscar parte das informações.

Vários joins

Requisições por segundo: 522.56 (por segundo)

Tempo por resposta: 95.682 (ms médio)

Várias queries

Requisições por segundo: 981.85 (por segundo)

Tempo por resposta: 50.924 (ms)

Conclusão: Quando usar vários joins ou várias queries?

É possível mesclar as duas soluções, relacionamentos de "1 para 1" podem ser trazidos na mesma query sem problemas, sendo mais adequado queries menores para relacionamentos "1 para N" ou "N para N".

Blog