Saltar para o conteúdo 
Histogramas do MySQL fornecem ao otimizador dados reais sobre a distribuição, para que este possa estimar corretamente as seletividades e produzir planos de consulta mais rápidos – muitas vezes, até sem necessidade de um índice adicional. Vou mostrar como configuro e verifico histogramas no MySQL 8+ com o comando ANALYZE TABLE e como os utilizo para tomar melhores decisões em junções, filtragens e varreduras.
Pontos centrais
Foco curto: Os pontos-chave que se seguem mostram aquilo a que presto especial atenção quando utilizo histogramas.
- Seletividade Em vez de intuição: estimativas de cardinalidade mais realistas
- Sem índice mais rápido: melhor seleção de planos em caso de distribuições assimétricas
- tipos Compreender: Utilizar de forma seletiva o Singleton e o Equi-Height
- Baldes controlar: ponderar a dissolução face aos custos dos metadados
- Cuidados Em destaque: atualizar, verificar e, se necessário, eliminar
Por que razão os histogramas sem índice têm efeito
Eu uso Histogramas, porque, caso contrário, o otimizador parte frequentemente do princípio de que a distribuição é uniforme e, por isso, escolhe planos inadequados. Um histograma representa a Distribuição de valores começa a analisar uma coluna de forma aproximada, fornecendo assim estimativas realistas de seletividade para predicados como =, >, BETWEEN, IN ou IS NULL. O otimizador decide então se é mais vantajoso um «index-range-scan», um «table-scan» ou uma estratégia de junção com «nested loops». Se, por exemplo, uma condição abranger apenas 0,1 % das linhas, prefiro um acesso direcionado em vez de uma varredura ampla. Por outro lado, se um filtro abranger quase todas as linhas, dispenso os acessos a índices dispendiosos que não trazem vantagens, aumentando assim a Eficiência de cada plano.
Tipos de histogramas no MySQL 8.0
Distingo duas tipos: Singleton e Equi-Height. Os histogramas Singleton agrupam valores únicos que ocorrem com frequência em intervalos separados – ideais para colunas com poucas categorias dominantes, como „ativo“, „inativo“ ou „arquivado“. Os histogramas Equi-Height dividem o intervalo de valores de forma a que cada intervalo contenha um número semelhante de Linhas contém; isto é adequado para distribuições contínuas ou irregulares, como preços, carimbos de data/hora ou intervalos de identificação „com lacunas“. Ambas as variantes fornecem ao otimizador taxas de correspondência mais precisas para os filtros. Escolho sempre o tipo de acordo com as características dos dados, e não com base em preferências pessoais.
Noções técnicas básicas: controlar a seleção de tipos no MySQL
O MySQL determina a forma concreta Variante do histograma automaticamente com base na distribuição dos dados. Na prática, isto significa que, se o número de valores distintos (NDV) for suficientemente pequeno em relação ao número de intervalos, resulta efetivamente num histograma singleton; caso contrário, é gerado um histograma de altura igual. Por isso, „escolho“ o tipo indireta, definindo a coluna adequada e um número adequado de intervalos. Para colunas com muito poucas categorias, mas fortemente dominantes, defino deliberadamente poucos buckets, de modo a obter uma precisão do tipo «singleton» para esses valores. No caso de dados contínuos e bem dispersos, aumento os buckets gradualmente até que o EXPLAIN apresente o resultado desejado Seletividade reflete.
Importante: os histogramas são em coluna única. Não é possível representar diretamente as dependências entre colunas (por exemplo, «status» e «country»). Nesses casos, é útil aplicar um histograma à coluna mais seletiva e definir a ordem das junções em conformidade.
Escolher os baldes de forma correta
Por predefinição, o MySQL utiliza 100 Baldes, mas permite valores entre 1 e 1024 através da opção WITH N BUCKETS. Um maior número de buckets aumenta a resolução, mas também aumenta os metadados e o esforço de análise. Normalmente começo de forma conservadora, avalio o impacto no EXPLAIN e vou aumentando gradualmente se o plano continuar a parecer inadequado. No caso de valores altamente concentrados (por exemplo, 90 % num único estado), bastam frequentemente poucos buckets; no caso de preços ou carimbos de data/hora bem dispersos, vale a pena utilizar mais buckets. O objetivo é uma Granularidade, o que reduziu significativamente os erros de avaliação, sem aumentar desnecessariamente a carga administrativa.
Exemplo prático: Fluxo de trabalho com ANALYZE TABLE
Sigo uma linha clara Fluxo de trabalho: Em primeiro lugar, identifico as colunas que aparecem frequentemente em condições WHERE ou JOIN e que apresentam distribuições visivelmente assimétricas. Em seguida, crio um histograma com ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS; e verifico-o através de INFORMATION_SCHEMA.COLUMN_STATISTICS. Após transferências de dados, atualizo novamente com ANALYZE TABLE. Se uma estatística não estiver correta, elimino-a com ANALYZE TABLE tbl DROP HISTOGRAM ON col;. Para avaliar o impacto no plano de execução, consulto Interpretar EXPLAIN ANALYZE e estimativas semelhantes em comparação com os valores reais Linhas de.
Ordens concretas e controlo
Trabalho de forma reprodutível, seguindo poucos passos claros, e verifico as estatísticas JSON geradas.
-- Criar histogramas em colunas individuais
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;
-- Várias colunas numa única execução com o mesmo número de buckets
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;
-- Eliminar histogramas de forma seletiva
ANALYZE TABLE orders DROP HISTOGRAM ON status;
-- Análise visual das estatísticas
SELECT
SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
JSON_PRETTY(HISTOGRAM) AS histogram
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
AND TABLE_NAME = 'orders'
AND COLUMN_NAME IN ('status','created_at');
Avalio o impacto diretamente com o comando EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
AND created_at >= NOW() - INTERVAL 7 DAY;
A estimativa melhora? linhas Se for percetível e o plano mudar, por exemplo, de «Full Scan» para «Index-Range-Scan», ou se alterar a ordem das junções, a medida foi bem-sucedida. Se o desvio continuar a ser grande, aumentei ou reduzi o número de buckets e comparo novamente.
Exemplo: Estado da encomenda e valores pouco frequentes
Numa tabela de encomendas, o estado „completed“ é frequentemente o mais comum, enquanto „pending“ é relativamente frequente e „canceled“ é muito raro; isto desequilíbrio Sem um histograma, isto pode facilmente levar a seletividades incorretas. Se uma API consultar „canceled“, o otimizador pode, por engano, optar por uma varredura completa da tabela, embora bastasse um acesso restrito ao índice. Com um histograma singleton, o MySQL reconhece que „canceled“ representa apenas uma percentagem ínfima e opta por uma varredura de intervalo de índice ou otimiza a ordem das junções. Desta forma, a latência diminui e não preciso de um índice adicional para cada Variante de um filtro. Nos painéis com SLOs rígidos, esta correção traz frequentemente vantagens percetíveis em termos de resposta.
Séries temporais e carimbos de data/hora
No caso das séries temporais, há muitos Acessos com base em dados recentes; os intervalos de tempo mais antigos permanecem, na maioria das vezes, inativos. Um histograma de altura equidistante (Equi-Height) com base em `created_at` ou `updated_at` distingue os intervalos de tempo mais movimentados dos que são raramente utilizados. O otimizador avalia então corretamente se uma varredura de intervalo (Range-Scan) faz sentido ou se uma varredura de tabela (Table-Scan) conduz mais rapidamente ao resultado pretendido. Especialmente no caso de filtros temporais parciais em tabelas de grande dimensão, noto mudanças significativas no plano de execução e custos de E/S mais baixos. Considero que a Estatísticas aqui é atualizado com mais frequência, porque o foco muda de acordo com as atividades do dia-a-dia.
Partições, tipos de dados e colações
Nas tabelas particionadas, analiso a distribuição dos dados em todas as partições. Diferenças acentuadas (por exemplo, por meses) podem suavizar os histogramas globais. Caso algumas partições sejam extremamente seletivas ou extremamente amplas, faço ainda um teste com filtros de «partition pruning» na cláusula WHERE para verificar se a qualidade do plano de execução continua a ser adequada. De um modo geral, procuro formular os filtros de forma a que o MySQL identifique as partições numa fase inicial excluir pode.
Os histogramas funcionam melhor com tipos de dados escalares e comparáveis (números, valores de data/hora, VARCHAR/CHAR com colação adequada). No caso de Dados LOB/JSON prefiro apostar em Colunas geradas com valores extraídos e tipificados e, se necessário, acompanhe-os com histogramas ou índices. No caso das cadeias de caracteres, a Colacionamento a lógica de comparação; dependendo da ordenação, os valores podem coincidir (por exemplo, maiúsculas/minúsculas). Mantenho a ordenação consistente com as consultas, para obter seletividades realistas.
Limites e erros
Os histogramas estimam, sobretudo, colunas individuais com Constantes Bem; representam as dependências entre várias colunas apenas de forma limitada. No caso de colunas fortemente correlacionadas ou de parâmetros dinâmicos (por exemplo, preenchidos pela aplicação), atingem os seus limites. Os campos booleanos ou as colunas com distribuição quase uniforme raramente beneficiam de estatísticas adicionais. Por outro lado, um número excessivo de intervalos e uma manutenção excessiva podem, por sua vez, aumentar o tempo dedicado à gestão e à análise. Por isso, utilizo os histogramas de forma seletiva e verifico regularmente a Efeito com base em exemplos reais.
Verificação e atualização do Optimizer
Eu controlo o Utilização desde histogramas até ao comando ANALYZE TABLE e opções relevantes do otimizador, para que o planeador utilize as estatísticas de forma eficaz. Em sistemas com elevado volume de trabalho, planeio a atualização em intervalos de tempo mais calmos ou em lote, após carregamentos de grande dimensão. Antes e depois, comparo os resultados do EXPLAIN e do EXPLAIN ANALYZE para avaliar alterações nas sequências de junções, etapas de filtragem e modelos de custos. Em caso de efeitos negativos, reajo imediatamente e reverto uma estatística. Para um controlo mais aprofundado do Opções do otimizador tenho o cuidado de garantir que as dependências com outras estatísticas não passem despercebidas e não resultem em erros Pressupostos produzir.
Monitorização, proteção contra regressão e manual de procedimentos
Estou a construir um leve Manual de estratégias para o ambiente de produção:
- Definir valor de referência: antes de efetuar alterações, executar EXPLAIN ANALYZE e registar o tempo de execução, o número de „linhas analisadas“ e o contador do handler.
- Criar/alterar histograma: com foco nas colunas de filtro, intervalos conservadores.
- Medir imediatamente a seguir: plano, linhas estimadas vs. reais; um desvio com um fator >10 é, para mim, um sinal de alerta.
- Ajuste fino: aumentar/diminuir os buckets; se necessário, ajustar a ordem dos filtros na consulta.
- Manter um rollback pronto: executar DROP HISTOGRAM caso as latências aumentem.
- Automatização: executar o comando ANALYZE durante as janelas de manutenção, após carregamentos ETL ou grandes ondas de DML.
Para a análise das causas, utilizo Rastreios do otimizador e EXPLAIN ANALYZE, para verificar se o planeador, com base nos histogramas, coloca a tabela seletiva correta „na frente“. Para testes A/B, fixo a ordem das junções (STRAIGHT_JOIN) a título experimental ou forço/desativo índices específicos, para avaliar o efeito das estatísticas de forma isolada.
Do ponto de vista organizacional, uma breve Registo de alterações Por tabela: coluna, número de intervalos, momento, valores medidos antes/depois. Isto facilita correções posteriores e evita interações pouco claras.
Aspectos operacionais: bloqueios, custos, portabilidade
A função ANALYZE TABLE recebe uma Bloqueio de metadados na tabela, mas não bloqueia de forma permanente as operações habituais de leitura/gravação. Em tabelas muito grandes, prevejo tempo suficiente; a geração do histograma funciona com amostras e está limitada pela memória (palavra-chave: memória interna para o cálculo). O espaço ocupado pelas próprias estatísticas permanece moderado: algumas dezenas a algumas centenas de kilobytes por coluna com 100–256 buckets é um valor de referência realista. No entanto, faço o cálculo total, pois muitas colunas multiplicadas por muitas tabelas resultam em metadados visíveis.
Em Dumps lógicos (mysqldump) os histogramas não são incluídos como dados; após uma restauração, recrio-os especificamente. Numa atualização no local (in-place upgrade), estes são mantidos. Do ponto de vista dos direitos, necessito de privilégios suficientes para executar o comando ANALYZE TABLE nos respetivos objetos; em ambientes rigorosamente regulamentados, integro a manutenção em pipelines de manutenção.
Quando os histogramas não servem de nada
Vou poupar-me Histogramas em colunas que contêm muito poucos valores e que, de qualquer forma, são bem estimadas. Mesmo nos casos em que um bom índice já abrange conjuntos mínimos de resultados, um histograma raramente traz benefícios adicionais. As distribuições uniformes não requerem um nível de detalhe excessivo. Em sistemas altamente dinâmicos e com grande volume de gravações, a atualização pode gerar uma carga desnecessária se for iniciada com demasiada frequência. Nessas situações, recorro à Energia prefiro as estratégias de indexação, a conceção de consultas e o armazenamento em cache.
Ficha de referência em forma de tabela
Utilizo o seguinte Visão geral para decisões rápidas: que tipo de histograma é o mais adequado, como definir os intervalos e quais são os custos envolvidos. A tabela serve como auxílio de memória nas análises de consultas problemáticas. Atualizo-a com base no que aprendi com o EXPLAIN ANALYZE e nas métricas de produção. Ao fazê-lo, tenho em conta que as distribuições de dados mudam e que as hipóteses históricas ficam desatualizadas. O que continua a ser decisivo é a Qualidade do plano confirmar através de medições reais.
| Aspeto | Recomendação | Benefício | compromisso | Exemplo |
|---|---|---|---|---|
| Tipo | Singleton no caso de poucos valores dominantes | Taxas de acerto precisas para categorias frequentes | Pouco útil em áreas contínuas | estado_da_encomenda |
| Tipo | Equi-Height em dados contínuos e distorcidos | Melhor estimativa ao longo do intervalo de valores | Mais metadados quando há muitos buckets | created_at, preço |
| Baldes | Começar por 100 e, depois, ajustar | Resolução equilibrada | Maior carga de análise e armazenamento entre 512 e 1024 | COM 100 BALDE |
| Cuidados | Após alterações significativas nos dados, executar o comando ANALYZE | Seletividades atuais | Planear janelas de manutenção | ANALYZE TABLE … UPDATE HISTOGRAM |
| Controlo | Verificar através de COLUMN_STATISTICS | Transparência e auditoria | É necessária a interpretação de JSON | INFORMATION_SCHEMA.COLUMN_STATISTICS |
Integração no panorama geral do tuning
Eu trato Histogramas como um elemento fundamental, a par dos índices, do desenho das consultas, do armazenamento em cache e dos parâmetros de hardware. Muitas vezes, um bom histograma altera a ordem das junções, reduz as operações de E/S e garante tempos de resposta constantes. No entanto, não substituo com isso estratégias de indexação bem definidas nem um esquema eficiente. Quem analisar mais profundamente as decisões de planeamento beneficia de Compreender os planos de execução e compara modelos de custos com prazos reais. Verifico regularmente se o Cargas de trabalho se ainda se enquadram nas estatísticas ou se é necessário fazer ajustes.
Cenários avançados de junção
Os histogramas revelam-se particularmente úteis quando estão envolvidas várias tabelas com filtros. Exemplo:
SELECT o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
AND o.status = 'canceled'
AND o.created_at >= NOW() - INTERVAL 30 DAY;
Sem histogramas, o otimizador pode subestimar a seletividade de o.status=’canceled‘ ou sobrestimar a percentagem de utilizadores alemães. Com um histograma em e país e sem estado (se for o caso, também em o.created_at) o planeador geralmente percebe que a combinação é extremamente seletiva. Na prática, vejo então que o MySQL determina primeiro o subconjunto mais pequeno (por exemplo, através de um índice em users(country) ou orders(status, created_at)) e só depois executa a junção – em vez de analisar a tabela grande. Isto poupa E/S, memória tampão e CPU e estabiliza a latência, mesmo sob carga.
Porque os histogramas apenas em coluna única as estratégias de índice continuam a ser importantes: um índice composto com base em (status, created_at) pode acelerar ainda mais a varredura de intervalo. O histograma garante, neste caso, sobretudo que o otimizador utilize este Estratégia considera, de facto, que é barato.
Resumo para a prática
Eu fixo MySQL-Utilizo histogramas quando o otimizador se engana com as estatísticas padrão e as distribuições assimétricas geram planos errados. Com o ANALYZE TABLE, crio, atualizo e elimino estatísticas de forma seletiva nas colunas que predominam nos filtros e nas junções. A escolha entre «Singleton» e «Equi-Height» é feita com base nos dados, e o número de buckets é calibrado com medições. Através do comando EXPLAIN ANALYZE, verifico se as sequências de junções, as posições dos filtros e as varreduras se alteram conforme desejado. Assim, consigo, com poucos Despesas gerais consultas visivelmente mais rápidas – muitas vezes sem necessidade de índices adicionais.
