Vimos na primeira parte deste artigo que uma das melhores formas de optimizar o acesso à informação no MySQL era o uso de índices. Para além do uso de índices, a optimização de joins entre tabelas é também bastante influente na performance do MySQL. Assim, este artigo tentará explicar como se comporta o MySQL na presença de joins e de que forma estes podem ser melhorados. Tudo em prol da optimização, claro! :)
Só para garantir que todos sabem do que estamos a falar, vou começar por definir um join. Um join, em termos matemáticos, é a intersecção de dois conjuntos. O que isto significa é que dois conjuntos são combinados e todos os valores comuns a ambos constituem o resultado do join.
Entendida a definição de join, é importante referir que ao efectuar um join é sempre necessário especificar uma condição de join. Caso contrário, o resultado obtido será o produto cartesiano das duas tabelas envolvidas no join, ou seja, cada registo na tabela A irá aparecer uma vez para cada registo da tabela B. Isto é o mesmo que dizer que o resultado vai ser composto por X * Y linhas, onde X é o número de registos da primeira tabela e Y o número de registos da segunda tabela.
Para completar as definições: a condição de join especifica quais as colunas em cada uma das tabelas que vão ser comparadas e como vão ser comparadas (na maioria dos casos as colunas são comparadas pela igualdade, mas nem sempre isto acontece).
Para ilustrar o que foi dito, deixo um pequeno exemplo:
Tabela de empregados (emp)
| id |
nome |
dpto |
| 1 |
Hugo Durães |
3 |
| 2 |
Joaquim Ferreira |
2 |
| 3 |
Filipa Gonçalves |
3 |
| 4 |
André Cardoso |
4 |
Tabela de departamentos (dpto)
| id |
nome |
| 1 |
Marketing |
| 2 |
Vendas |
| 3 |
Técnico |
Resultado do join
A operação de join da tabelas de empregados com a tabela de departamentos, pelo campo dpto.id produziria o seguinte resultado:
| emp.id |
emp.nome |
dpto.id |
dpto.nome |
| 1 |
Hugo Durães |
3 |
Técnico |
| 2 |
Joaquim Ferreira |
2 |
Vendas |
| 3 |
Filipa Gonçalves |
3 |
Técnico |
O query SQL para obter este resultado seria:
SELECT emp.id, emp.nome, dpto.id, dpto.nome
FROM emp, dept
WHERE emp.dpto = dpto.id
Optimização de joins
A primeira coisa a ter em conta acerca da optimização da performance de joins é que esta pode diminuir consideravelmente à medida que a terceira, quarta ou mesmo quinta tabela é adicionada à consulta. Quanto mais tabelas são adicionadas, mais importante é garantir que a consulta está optimizada e testada.
Existem diversas razões para este decréscimo de performance:
- Quanto mais tabelas estiverem envolvidas, mais registos terão de ser lidos e a probabilidade de estes ficarem em cache será cada vez menor, o que implica leituras a partir do disco rígido;
- Quanto mais tabelas estiverem envolvidas, mais filtragens e ordenações têm de ser feitas. Estas são operações intensivas que consomem bastante CPU e memória, provocando assim um decréscimo da performance;
- À medida que o tamando do resultado (número de linhas e colunas a serem devolvidos) aumenta, é cada vez menos provável que as operações de ordenação possam ser feitas em memória e mais provável que os dados tenham de ser escritos em disco rígido para que possam ser ordenados. O acesso ao disco é relativamente mais lento e consome também muito CPU;
- Num ambiente interactivo, quanto mais tabelas estiverem envolvidas e quanto mais tempo demorar uma consulta, maior é a probabilidade de alguém efectuar actualizações aos registos nas tabelas que estão a ser lidas, o que faz com que porções do resultado tenham de ser lidos novamente de forma a garantir uma visualização consistente dos dados.
Construir bases de dados com bom desempenho
Relativamente aos joins, existem duas formas de maximizar a performance. Uma delas é desenhar a estrutura da base de dados de forma a minimizar o uso de joins. A outra forma consiste em maximizar a performance, optimizando as consultas.
Tanto de uma forma como outra, é bom saber de que forma determinada estrutura de tabelas e determinada consulta poderão afectar a performance da nossa aplicação. Para percebermos o que está a acontecer à medida que decorre o desenvolvimento, existe o comando EXPLAIN do MySQL. Este comando examina uma dada consulta, para verificar se esta está o mais optimizada possível, usando índices sempre que estes podem ser usados.
A sintaxe deste comando é a seguinte:
EXPLAIN SELECT *
FROM tabA, tabB, tabC
WHERE tabA.col1 = tabB.col1 AND tabA.col2 = tabC.col1;
Este comando devolve uma tabela de informação semelhante à representada em seguida:
+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------------+
| tabA | ALL | NULL | NULL | NULL | NULL | 1000 | |
| tabB | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| tabC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+
Mas o que significam todas as colunas devolvidas?
- table – mostra o nome da tabela à qual o resultado diz respeito (para quando são efectuados JOINs entre tabelas);
- type – tipo de join usado. Do melhor para o pior tipo temos: system, const, eq_ref, ref, range, index, all;
- possible_keys – indica quais os índices que o MySQL pode usar para encontrar resultados nesta tabela;
- key – índice usado na consulta, ou NULL caso não tenham sido usados índices;
- key_len – tamanho do índice usado, caso exista;
- ref – coluna(s) usada(s) com a key para devolver resultados;
- rows – número de registos que o MySQL tem que examinar para executar a consulta;
- extra – informação adicional acerca de como o MySQL vai executar a consulta. A evitar o aparecimento de “using filesort” e “using temporary“.
Sabendo isto, e analisando o resultado obtido, podemos dizer que a consulta efectuada está muito pouco optimizada. Não existem chaves possíveis (possible_keys) para usar, o que implica que o MySQL tenha de percorrer todos os registos da tabela (1000 no exemplo, mas imagine uma tabela com milhões de resultados).
De acordo com o manual do MySQL, as tabelas são listadas na mesma ordem que são lidas enquanto a consulta é processada. O MySQL resolve todos os joins através de um método multi-join que percorre todos os registos. Isto significa que o MySQL lê o primeiro registo da primeira tabela (tabA), de seguida procura um registo correspondente na segunda tabela (tabB), fazendo o mesmo para a terceira tabela (tabC) e assim sucessivamente. Após todas as tabelas serem processadas o MySQL devolve os registos encontrados e volta atrás. Não para a tabA, mas sim para a tabB para procurar se existem mais registos que façam match com a tabA. Caso existam, vai procurar novamente registos da tabC.
É importante reter que os valores da tabela precendente (como listado no resultado do EXPLAIN) são usados para pesquisa na tabela actual, ou seja, o MySQL usa os valores da tabA para encontrar registos na tabB e assim sucessivamente. Sabendo isto, onde adicionar índices para ajudar o MySQL nesta pesquisa? Para responder a esta pergunta é necessário saber o que é que o MySQL procura. O join da tabA com a tabB é feito pela condição “tabA.col1=tabB.col1″. O MySQL já possui o valor para o campo tabA.col1, logo precisa de saber o valor de tabB.col1 de modo a terminar a equação. Assim, um índice deverá ser criado neste campo. O resultado do EXPLAIN após a criação deste campo é o seguinte:
+-------+------+---------------+----------+---------+-----------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+----------+---------+-----------+------+-------------+
| tabA | ALL | NULL | NULL | NULL | NULL | 1000 | |
| tabB | ref | idx_col1 | idx_col1 | 5 | tabA.col1 | 1 | Using where |
| tabC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
+-------+------+---------------+----------+---------+-----------+------+-------------+
Como é visível, o MySQL usa o idx_col1 para efectuar o join entre tabA e tabB. Agora, ao procurar ocorrências de tabA.col1 na tabB, em vez de percorrer todos os registos como anteriormente, usa o idx_col1 para apanhar apenas os valores pretendidos. Para a pesquisa na tabC continua a ser precisa a pesquisa em todos os registos, mas esta questão pode ser resolvida da mesma forma que a anterior. Neste caso a condição de join é “tabA.col2=tabC.col1″, o que faz com que o campo a ser indexado seja o tabC.col1, uma vez que o valor de tabA.col2 já é conhecido. Após a criação deste índice, o resultado do EXPLAIN é:
+-------+------+---------------+----------+---------+-----------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+----------+---------+-----------+------+-------------+
| tabA | ALL | NULL | NULL | NULL | NULL | 1000 | |
| tabB | ref | idx_col1 | idx_col1 | 5 | tabA.col1 | 1 | Using where |
| tabC | ref | idx_col1 | idx_col1 | 5 | tabA.col2 | 1 | Using where |
+-------+------+---------------+----------+---------+-----------+------+-------------+
Concluindo…
A tarefa de fazer com que os joins complexos passem a funcionar de uma forma simples, e com que a indexação seja feita nos campos correctos, torna-se relativamente fácil se forem tomados em conta os seguintes pontos:
- Independentemente da complexidade da consulta, apenas é necessário abordar o join analisando duas tabelas de cada vez, na ordem pela qual estas são listadas pelo EXPLAIN;
- Os valores das tabelas precedentes já são conhecidos; o esforço tem de ser concentrado em ajudar o MySQL a usar esses valores com índices das tabelas actuais, com vista a encontrar registos.
Para mais informações acerca do comando EXPLAIN, consultar o tópico respectivo do manual do MySQL.
