Um destes dias, enquanto tentava perceber porque razão um dos meus servidores ficava subitamente tão lento (e que por muito pouco não foi formatado e reinstalado), percebi que existia uma query bastante lenta a ser executada.
Depois de mais alguma análise, percebi que essa query era do um software que estava a utilizar, e que o request era feito pelo Googlebot. Trata-se de um software opensource, que não vou identificar, até porque o site em que estava a utilizar esse software já foi entretanto migrado para a Mason Framework.
Mas, depois de uma breve vista de olhos à query em questão, foi fácil perceber qual o problema. Trata-se de uma query com um GROUP BY seguindo de um HAVING, e ainda um JOIN implicito (feito colocando as várias tabelas no FROM, sem a clausula JOIN). A titulo de exemplo, digamos que a query era qualquer coisa do tipo:
SELECT username FROM user, post, votes GROUP by user.id HAVING votes_user_id=user_id AND post_id=votes_post_id AND post_status='online' AND votes_date<post_publish_date
Vejamos quais são os problemas desta query…
- Primeiro temos o JOIN. O que acontece quando metemos várias tabelas no FROM de uma query, daquela maneira, é que o MySQL (ou qualquer outro motor de SQL que suporte esta sintaxe) agarra em todos de cada uma das tabelas e combina-os, criando uma tabela gigante, com uma linha por cada conjunto de registos de cada uma dessas tabelas.
- Depois temos o HAVING… repararam que as condições no HAVING são as condições do JOIN das tabelas? Mas, mais do que isso, repararam que vários do campos utilizados no Having não fazem parte do group by, nem da tabela pela qual o group é feito?
Vejamos o que acontece quando esta query é executada.
Join Cartesiano
Antes de mais, temos a questão das várias tabelas no FROM, sem o JOIN. Exemplifiquemos o que acontece:
Suponhamos que temos duas tabelas com um campo cada, tab1 e tab2:
tab1
tab1_c1
1
2
3
tab2
tab2_c1
a
b
c
Neste caso o resultset terá 9 linhas:
tab1_c1, tab2_c1
1, 1
1, 2
1, 3
2, 1
2, 2
2, 3
3, 1
3, 2
3, 3
Inner JOIN
Inicialmente (querendo isto dizer há muito tempo atrás), a única forma de fazer JOINs em MySQL era esta, utilizando depois condições no WHERE para filtrar a listagem. MAs há já muito tempo que isto mudou. O MySQL permite utilizar a clausula JOIN para unir tabelas.
Imaginemos, no caso acima, que queremos apenas da segunda tabela os registos em que o campo tab2_c1 tem o mesmo valor que o campo tab1_c1 (da primeira tabela). Nesse caso, com um JOIN:
SELECT tab1_c1, tab2_c1 FROM tab1 JOIN tab2 ON tab1.tab1_c1=tab2.tab2_c1;
E deste SELECT resultam apenas 3 linhas:
tab1_c1, tab2_c1
1,1
2,2
3,3
A isto chamamos INNER JOIN.
GROUP by
Em SQL a clausula GROUP BY agarra num resultset (ou nos registos de uma tabela) e agrupa-os de acordo com o valor de um campo (ou de uma lista de campos), criando apenas um registo por cada grupo de linhas que tenham os campos indicados.
A grande questão com o GROUP by é o que acontece com os campos que não são utilizados no GROUP by.
Muitos motores de SQL (como o do Oracle) apenas permitem utilizar num group campos que estejam no group by e funções de grupo (como o SUM, MAX, MIN, etc). O MySQL assume que sabemos o que estamos a fazer e permite-nos utilizar qualquer campo das tabelas utilizadas na query.
O que acontece, no entanto, é que se um dos campos utilizados tiver mais do que um valor dentro do grupo, o valor que aparece no resultset é um qualquer dos vários possível.
Na verdade não é um qualquer, e conhecendo várias coisas acerca das tabelas envolvidas (como a Engine utilizada -InnoDB, MyISAM, etc), a ordem porque os registos estão no disco, e outras, uma pessoa que conheça bem o MySQL consegue prever qual o valor que vai aparecer para cada grupo, mas para o comum dos mortais o valor é práticamente imprevisivel.
Claro que se estamos a fazer um join de várias tabelas, em que um campo (único) de uma delas é o valor porque estamos a agrupar, todos os restantes campos dessa tabela são garantidos. Mas isso não acontece se o GROUP by utilizar um campo não único (que tenha valores repetidos), ou se os campos que estivermos a colocar no SELECT forem de outra tabela.
Imaginemos que nas nossas tabelas de exemplo, fazemos o seguinte:
SELECT tab1_c1, tab2_c1 FROM tab1, tab2 GROUP by tab1_c1;
Deste query podem resultar vários resultados, sendo um dos possíveis (e dos mais prováveis se criarmos as tabelas de novos e inserirmos os valores por ordem, sem deletes ou updates):
tab1_c1, tab2_c1
1,1
2,1
3,1
Por aqui já conseguimos facilmente perceber que nos estamos a desviar do caminho que pretendiamos com o join que demonstramos antes.
HAVING
O SQL prevê duas clausulas distintas que permitem filtrar um dataset. A mais conhecida, obviamente, é o WHERE. A segunda é o HAVING.
A diferença entre o WHERE e o HAVING é o momento em que cada uma delas é executada, o momento em que as condições por elas definidas são aplicadas.
O WHERE é aplicado ao criar o result set, e o HAVING é aplicado sobre o result set final. Em MySQL o HAVING pode ser utilizado numa query sem GROUP by. A titulo de exemplo, assumindo que o campo tab1_c1 e o campo tab2_c1 são PRIMARY KEYs de cada uma das tabelas em questão, utilizar o WHERE para filtrar estas tabelas utiliza o indice, utilizar o HAVING não utiliza nenhum indice e, pior, é aplicado sobre o resultset final, filtrando registo a registo.
Mas quando se utiliza o GROUP by e um HAVING em campos que não são únicos no resultset antes de agrupado, o resultado é ainda mais… imprevisível.
Voltemos ao JOIN acima, e vamos fazer as clausulas no Having, depois de um group by:
SELECT tab1_c1, tab2_c1 GROUP by tab1_c1 HAVING tab1_c1=tab2_c1;
Se não tivessemos visto o resultado do GROUP by acima ou não soubessemos quando é que o HAVING , poderiamos achar que chegariamos ao resultado que pretendemos (o do JOIN, acima). Mas a verdade é o result set a que chegamos é:
tab1_c1, tab2_c1
1,1
Ops, não era bem isto que queriamos. Para chegar ao resultset que queriamos, as condições têm, obviamente, que ser executadas antes do GROUP by… logo, têm de ser executados num WHERE, e não num HAVING.
Leitura e Execução
O SQL tem uma coisa fantástica… os comandos são executados pela ordem que se encontram na query (e, claro, não podem ser colocados na query pela ordem errada – um HAVING não pode aparecer antes de um WHERE).
Se retirarmos uma qualquer parte final da query, mantendo toda a query inalterada até essa remoção, se a query continuar válida, tudo o que faz até aí continua a ser feito. Claro que há situações em que o que aparece depois pode alterar a performance (positiva ou negativamente) do que está antes.
Por exemplo, um WHERE que utilize indices pode reduzir o tamanho do resultset e o tempo que a query demora a terminar. Isto é especialmente verdade com JOINs de várias tabelas, e quando mais no inicio do JOIN se encontrar a tabela filtrada, mais a rapidez do query melhora (por consequência da redução do resultset criado).
Nota final
O software em que encontrei a query que serve de inspiração a este post é razoavelmente utilizado, e tratava-se de uma versão de desenvolvimento. Obviamente o query não chegou à versão 1.0, que pode ser encontrada no site do software hoje.
Mas ainda assim achei que era um tema relevante.