MySQL: Otimizando consultas com JOINS

Escrito porJackson F. de A. Mafra

junho 22, 2008

Muitos programadores não se preocupam muito em utilizar JOIN em sistemas de pequeno porte, porém é uma das regras mais importantes na otimização de consultas no guia da própria MySQL AB. Vamos entender nosso ambiente inicial descrevendo nossas tabelas, o objetivo é criar uma base dados para armazenarmos veículos, para ilustrar, esse modelo poderia ser parte de um sistema de uma revendedora de veículos:

Descrição: Cadastraremos Veículos em nosso sistema, esses veículos possuirão atributos como um código único (id), um modelo (id_modelo), uma cor (id_cor) e um ano do modelo (ano_modelo).

Nossos Modelos de veículos terão um código único (id) e serão atrelados a uma montadora (id_marca), que chamaremos popularmente de marca, os veículos possuirão também um tipo (id_tipo), para classificarmos os diferentes tipos de veículos encontrados atualmente, por fim outro atributo será o nome do modelo (nome).

Na tabela Marcas armazenaremos um código único (id) além do nome da marca (nome) e sua origem (origem).

Os Tipos de modelos são bem simples e a principio terão como atributo somente um código único (id) e uma descrição (desc).

Por fim, os veículos possuem Cores, que serão armazenadas em sua tabela com um código único (id), seu nome (nome) e um campo de seleção para separarmos cores metálicas das cores sólidas (metalica) onde setaremos com Sim (S) ou Não (N).

Normas: As normas de banco de dados possuem uma série de passos para que ao final se obtenha maior desempenho e melhor manipulação das tabelas, vamos verificar quais são esses passos básicos da chamada normalização de banco de dados:

Primeira forma normal: Os dados devem estar na forma de tabelas, e todos os campos devem sem atômicos (únicos, sem conjuntos).Criamos uma tabela para armazenas os dados não atômicos de cores e de modelos, que poderiam assumir valores repetitivos ao longo do armazenamento dos dados.
Segunda forma nomal: Deve-se estar na primeira forma normal, e os atributos de uma tabela devem ser totalmente dependentes de sua chave.Todos os atributos de nossas tabelas possuem dependência total de suas chaves, na ausência das mesmas os dados não fazem sentido lógico.
Terceira forma normal: Deve-se estar na segunda forma normal, e nenhum atributo que não seja uma chave não deve depender de outro atributo também não chave.Nenhum atributo de nossas tabelas depende de outro que não seja única e exclusivamente sua chave.

Portanto nosso sistema passou pelas regras e está devidamente normalizado.

Prática: Vamos a parte prática deste artigo, as consultas!

Primeiro cenário: Um usuário visita o site da revendedora de veículos e deseja efetuar uma busca para saber que marcas de veículos temos disponíveis e quantos veículos de cada marca temos cadastrados.

Apesar de termos várias marcas cadastradas, para o usuário é interessante saber SOMENTE marcas de veículos que realmente temos disponíveis, ou seja, se cadastramos a marca Ferrari e não temos nenhum veículo dessa marca cadastrada no momento não existe motivos para exibir a marca com um zero na frente. Pensando dessa forma vamos a consulta:

Código

SELECT m.nome, COUNT(m.id) AS total_veiculos FROM marcas m

INNER JOIN modelos mo ON mo.id_marca = m.id

INNER JOIN veiculos v ON v.id_modelo = mo.id

GROUP BY m.id

Resultado

Explanação

No MySQL, podemos e devemos utilizar apelidos para tabelas, dessa forma podemos fazer um SELECT mais simples, um apelido pode ser criado utilizando a palavra reservada AS, dessa forma:

SELECT v.id, c.nome FROM veiculos AS v, cor AS c;

Essa consulta retornaria o campo id da tabela veiculos e o campo nome da tabela cor; isso acaba com aquela mania dos programadores antigos que utilizam na confecção de tabelas todos os campos de chaves primárias seguirem a sintaxe codNomedatabela, por exemplo codVeiculo ao invés de somente cod, ou id como preferir. Se utilizarmos essa lógica antiga nossas consultas seriam redundantes, veja:

SELECT v.codVeiculo, c.nome FROM veiculos AS v, cor AS c;

Ora, se queremos o código de um veículo, e estamos obtendo ele na própria tabela de veículos v.cod ou v.id no nosso caso bastaria.

Mais voltando aos apelidos, se podemos utilizar AS para apelidar, porque eu não utilizei? Fácil, o MySQL facilita ainda mais o sistema de apelidos, fazendo com que tudo o que estiver após uma informação de campo se torne apelido, assim não é necessário utilizar o AS, basta informar o apelido logo na frente do campo:

SELECT v.id codigo_do_veiculo_apelidado, c.nome FROM veiculos v, cor c;

Importante: Esse sistema pode ser utilizado tanto nas tabelas, quanto nos campos.

Bom, estamos entendidos quanto aos apelidos, agora vamos entender a consulta; queremos exibir 2 campos, o primeiro é o nome da marca, o segundo a quantidade de veículos cadastrados dessa marca, o primeiro é fácil, basta referenciar a tabela, o segundo é um pouco mais complicado, pois depende das junções de outras tabelas, detalhando:

SELECT m.nome, COUNT(m.id) AS total_veiculos FROM marcas m

/* Pega o campo nome na tabela de marcas, faz a contagem dos resultados de m.id e apelida a contagem de total_veiculos */

INNER JOIN modelos mo ON mo.id_marca = m.id

/* Faz a junção das tabelas utilizando a referência id_marca da tabela de modelos com o campo id da tabela de marcas, assim cruzamos os registros nas duas tabelas */

INNER JOIN veiculos v ON v.id_modelo = mo.id

/* a segunda junção faz com que o modelo do veículo seja cruzado com a tabela de modelos, assim somente veiculos que possuem modelos da marca solicitada é retornado, como não fizemos nenhum filto, todos os modelos da base de dados seão varridos pela consulta. */

GROUP BY m.id

/* Para uma exibição correta, agrupamos os resultados por marcas, sem isso a consulta não funciona, apesar de simples é um ponto chave da mesma */

GROUP BY m.id

/* Agrupa os resultados pela chave id da tabela de marcas */

Bom, este é o fim do primeiro artigo desta série, espero que tenham compreendido, qualquer dúvida utilizem os comentários ou ainda e-mail para breno@sipvox.com.br.

Recomendo a todos a praticar e ler a documentação do MySQL, isso é fundamental!