Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
Um GROUP BY que retorna em milissegundos em uma tabela de teste pode levar minutos em uma tabela real, e o texto da consulta é idêntico. A diferença está em o servidor conseguir percorrer um índice na ordem dos grupos ou ter de construir uma tabela temporária primeiro. Este guia é sobre essa diferença, e sobre as escolhas que decidem qual das duas você obtém. Se você está aprendendo as próprias cláusulas, o tutorial as ensina com um banco de dados de exemplo: funções de agregação, GROUP BY, HAVING, COUNT DISTINCT, GROUP_CONCAT e WITH ROLLUP. Cada consulta e plano desta página veio de 1 servidor com o banco de dados de exemplo Sakila. Os custos e as estimativas de linhas vêm das estatísticas amostradas do InnoDB e das suas configurações de optimizer_switch, então os seus números serão diferentes. A forma de cada plano é a parte que se mantém.

Agrupar sem um índice constrói uma tabela temporária

Para agrupar linhas, o servidor precisa reunir as linhas de cada grupo. Sem um índice na coluna de agrupamento, ele faz isso construindo uma tabela temporária:
Using temporary é a linha a procurar. Em 1.000 linhas, ela não custa nada. A tabela é mantida em memória enquanto cabe, e vai para o disco quando não cabe, que é quando um relatório agrupado deixa de ser rápido. Qual configuração limita essa memória depende do engine que o servidor usa para tabelas temporárias internas. Verifique a sua antes de mudar qualquer coisa:
Elas fazem trabalhos diferentes e todas se aplicam com TempTable. temptable_max_ram e temptable_max_mmap limitam o que o engine pode manter em todas as sessões. tmp_table_size limita cada tabela temporária interna individualmente: em 1 KB, uma consulta agrupada sobre 1.000 linhas levou Created_tmp_disk_tables de 0 a 1, e em 64 MB a mesma consulta permaneceu em memória. Observe esse contador em vez de adivinhar:
Um índice na coluna de agrupamento elimina a etapa por completo, porque o índice já está nessa ordem:
Sem Using temporary e sem Using filesort. O servidor lê o índice em ordem, conta cada sequência de valores iguais e emite uma linha. Using index significa que ele nunca tocou na tabela em si. Portanto, a primeira coisa a verificar em um GROUP BY lento é se um índice começa com as colunas de agrupamento, na mesma ordem em que você as agrupou. Um índice composto em (a, b) atende a GROUP BY a e a GROUP BY a, b. Para GROUP BY b, o servidor ainda pode percorrê-lo como um índice de cobertura, reportado como type: index com Using index, mas as linhas chegam fora da ordem dos grupos, então Using temporary volta com elas.

WHERE e HAVING não são uma escolha de estilo

Ambos podem expressar um filtro. Eles custam de forma diferente, porque WHERE executa antes do agrupamento e HAVING executa depois dele. Uma condição sobre a própria coluna de agrupamento pode ser escrita de qualquer uma das duas formas e significa a mesma coisa, o que faz dela a comparação honesta. Em WHERE, o servidor estreita o intervalo do índice primeiro e agrupa somente o que leu:
Em HAVING, a mesma restrição chega tarde demais para estreitar qualquer coisa:
As mesmas linhas na saída, 4 vezes o custo estimado. A versão com WHERE lê uma estimativa de 2.710 entradas de índice; a versão com HAVING lê o índice inteiro e descarta a maioria dos grupos depois. Essas estimativas vêm das estatísticas amostradas do InnoDB e das constantes de custo em mysql.server_cost, então os seus números serão diferentes destes. A forma dos 2 planos é a parte que se mantém. A regra: coloque uma condição em WHERE sempre que ela testar uma linha. Use HAVING somente para o que testa um grupo, o que na prática significa qualquer coisa que contenha uma agregação. Uma cláusula HAVING sem agregação é quase sempre uma cláusula WHERE no lugar errado, e WHERE com uma agregação é ERROR 1111 (HY000): Invalid use of group function.

ONLY_FULL_GROUP_BY, e a válvula de escape

only_full_group_by está ativado por padrão e rejeita uma consulta que seleciona uma coluna que não é nem agrupada nem agregada, com ERROR 1055. O motivo é que tal consulta não tem resposta definida: o grupo contém muitas linhas e nada diz qual delas mostrar. ANY_VALUE() diz ao servidor que você aceita uma linha arbitrária, e a consulta então executa:
Nenhuma saída é mostrada aqui de propósito. Qual título volta não está definido, então qualquer resultado impresso nesta página seria um valor que você não consegue reproduzir. Use ANY_VALUE() quando a coluna for funcionalmente dependente da coluna de agrupamento e o servidor não conseguir provar isso, que é o caso legítimo. Não o use para silenciar o erro em uma coluna que de fato varia dentro do grupo: o valor que você obtém não é o primeiro, o menor, nem qualquer outra coisa em que você possa confiar, e ele pode mudar quando o plano muda. MIN() ou MAX() diz o que você quis dizer e custa o mesmo. Desativar only_full_group_by globalmente converte cada um desses erros em uma resposta errada que ninguém vê. É a correção errada. A verificação enxerga através de uma expressão construída sobre uma coluna agrupada, então SELECT COALESCE(rating, 'unrated') ... GROUP BY rating é aceito. Ela não enxerga através de uma expressão construída sobre uma expressão agrupada. Agrupe por DATE_FORMAT(payment_date, '%Y-%m') e selecione COALESCE(DATE_FORMAT(payment_date, '%Y-%m'), 'ALL'), e o mesmo ERROR 1055 nomeia payment_date, porque o servidor compara as 2 expressões em vez das colunas dentro delas. Esse é o caso comum em relatórios, e a saída é agrupar em uma tabela derivada e envolver o resultado fora dela:

Agregando um join um-para-muitos

Fazer o join antes de agrupar multiplica as linhas, então um SUM sobre o resultado conta em dobro. O sintoma é um total alto demais por um fator que ninguém consegue explicar. Agregue cada lado em sua própria subconsulta e faça o join dos resultados, em vez de fazer o join primeiro e agrupar depois:
A agregação de cada subconsulta é sobre as linhas às quais ela pertence. Fazer o join das duas tabelas primeiro e agrupar depois multiplica pagamentos por locações e infla ambas as contagens. Desempenho de JOIN no MySQL e erros comuns cobre a multiplicação em si.

Quando você não quer as linhas colapsadas

GROUP BY substitui as linhas por 1 linha por grupo. Quando você quer a agregação ao lado de cada linha, uma função de janela faz isso sem colapsar nada, e geralmente sem a tabela temporária:
Funções de janela no MySQL 8.4 as cobre em detalhe.

Solução de problemas

Veja também