VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
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:
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:
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, porqueWHERE
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:
HAVING, a mesma restrição chega tarde demais para estreitar qualquer coisa:
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:
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 umSUM 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:
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:
Solução de problemas
Veja também
- Funções de janela no MySQL 8.4 — agregar sem colapsar as linhas
- Desempenho de JOIN no MySQL e erros comuns — por que um join um-para-muitos infla um total
- Quando uma CTE do MySQL supera uma tabela derivada — pré-agregar antes da consulta principal
- Erros comuns do MySQL — ERROR 1055 e ERROR 1111 em contexto
- GROUP BY — o tutorial, se você quiser a cláusula em si primeiro