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
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".
Top comments (0)