Créer et gérer des histogrammes pour les colonnes non indexées

Commande

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;

Ce que fait la commande

Stocke la distribution des valeurs d'une colonne (singleton ou equi-height) dans le dictionnaire de données ; l'optimiseur estime correctement la sélectivité des filtres sur les colonnes non indexées, ce qui améliore l'ordre des jointures et la qualité du plan.

Quand l'utiliser

Quand EXPLAIN ANALYZE montre un grand écart entre lignes estimées et réelles sur des colonnes à distribution asymétrique (par ex. status, type) où l'ajout d'un index n'est pas souhaitable.

Attention

Sur les colonnes indexées, l'optimiseur utilise les index dives, donc un histogramme n'a généralement aucun effet (sauf si eq_range_index_dive_limit est dépassé). En mode MANUAL par défaut, l'histogramme n'est pas actualisé quand les données changent ; planifiez ANALYZE ... UPDATE HISTOGRAM. Les grandes tables sont échantillonnées selon histogram_generation_max_mem_size (sampling-rate < 1). ANALYZE TABLE prend un bref metadata lock ; la mise à jour d'un histogramme ne modifie pas les statistiques de la table (cardinalité des index).