Criar e gerenciar histogramas para colunas não indexadas

Comando

ANALYZE TABLE shop.big UPDATE HISTOGRAM ON status WITH 64 BUCKETS;
-- 8.4+: histogram refreshed automatically as data changes
ANALYZE TABLE shop.big UPDATE HISTOGRAM ON status WITH 64 BUCKETS AUTO UPDATE;

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
       HISTOGRAM->>'$."histogram-type"' AS type,
       JSON_LENGTH(HISTOGRAM->'$.buckets') AS buckets,
       HISTOGRAM->>'$."last-updated"' AS last_updated,
       HISTOGRAM->>'$."sampling-rate"' AS sampling_rate
FROM information_schema.COLUMN_STATISTICS;

ANALYZE TABLE shop.big DROP HISTOGRAM ON status;

O que faz

Armazena a distribuição de valores de uma coluna (singleton ou equi-height) no dicionário de dados; o otimizador estima corretamente a seletividade de filtros em colunas não indexadas, melhorando a ordem de join e a qualidade do plano.

Quando usar

Quando o EXPLAIN ANALYZE mostra grande diferença entre linhas estimadas e reais em colunas com distribuição desigual (por exemplo, status, tipo) onde não é desejável adicionar um índice.

Atenção

Em colunas indexadas o otimizador usa index dives, então um histograma geralmente não tem efeito (a menos que eq_range_index_dive_limit seja excedido). No modo padrão MANUAL o histograma não é atualizado conforme os dados mudam; agende ANALYZE ... UPDATE HISTOGRAM. Tabelas grandes são amostradas conforme histogram_generation_max_mem_size (sampling-rate < 1). ANALYZE TABLE obtém um metadata lock breve; atualizar um histograma não altera as estatísticas da tabela (cardinalidade dos índices).