Histogrammes MySQL – De meilleurs plans de requête sans index

Histogrammes MySQL fournissent à l'optimiseur des données de distribution réelles, afin qu'il puisse estimer correctement les sélectivités et générer des plans de requête plus rapides – souvent même sans index supplémentaire. Je vais vous montrer comment je configure et contrôle les histogrammes dans MySQL 8+ à l’aide de la commande ANALYZE TABLE, et comment je les utilise pour prendre de meilleures décisions concernant les jointures, les filtres et les balayages.

Points centraux

Focale courte: Les points clés suivants indiquent les aspects auxquels je prête particulièrement attention lorsque j'utilise des histogrammes.

• Sélectivité Au lieu de se fier à son intuition : des estimations de cardinalité plus réalistes
• Sans index plus rapide : meilleur choix de plan pour les distributions asymétriques
• types Comprendre : utiliser de manière ciblée le singleton et l'Equi-Height
• Seaux gérer : mettre en balance la suppression et les coûts liés aux métadonnées
• Soins À surveiller : mettre à jour, vérifier, supprimer si nécessaire

Pourquoi les histogrammes sans index sont-ils efficaces ?

J'utilise Histogrammes, car sinon l'optimiseur part souvent du principe que la distribution est uniforme et choisit donc de mauvais plans. Un histogramme représente la Répartition des valeurs il effectue une approximation sur une colonne et fournit ainsi des estimations réalistes de la sélectivité pour des prédicats tels que =, >, BETWEEN, IN ou IS NULL. L’optimiseur décide alors s’il est plus avantageux d’utiliser un balayage d’index par plage, un balayage de table ou une stratégie de jointure avec boucles imbriquées. Si, par exemple, une condition ne concerne que 0,1 % des lignes, je privilégie un accès ciblé plutôt qu’un balayage large. En revanche, si un filtre couvre la quasi-totalité des lignes, je renonce aux accès à l’index coûteux qui n’apportent aucun avantage et j’augmente ainsi la Efficacité de chaque plan.

Types d'histogrammes dans MySQL 8.0

Je distingue deux types: Singleton et Equi-Height. Les histogrammes de type Singleton regroupent les valeurs uniques fréquentes dans des tranches distinctes – une solution idéale pour les colonnes comportant peu de catégories dominantes telles que „ actif “, „ inactif “ ou „ archivé “. Les histogrammes « Equi-Height » répartissent la plage de valeurs de manière à ce que chaque tranche contienne un nombre similaire de Lignes ; cela convient aux distributions continues ou irrégulières, telles que les prix, les horodatages ou les plages d'identifiants „ lacunaires “. Ces deux variantes fournissent à l'optimiseur des taux de correspondance plus précis pour les filtres. Je choisis toujours le type en fonction des caractéristiques des données, et non selon mes préférences personnelles.

Principes techniques : contrôler le choix du type de données dans MySQL

C'est MySQL qui détermine la Variante d'histogramme automatiquement en fonction de la répartition des données. Concrètement, cela signifie que si le nombre de valeurs distinctes (NDV) est suffisamment faible par rapport au nombre de tranches, on obtient en fait un histogramme de type „ singleton “ ; dans le cas contraire, un histogramme de type « equi-height » est généré. Je « choisis » donc le type indirect, en définissant la colonne appropriée et un nombre de tranches adapté. Pour les colonnes comportant très peu de catégories, mais celles-ci étant très dominantes, je définis délibérément un petit nombre de buckets afin d’obtenir une précision de type « singleton » pour ces valeurs. Dans le cas de données continues et finement réparties, j’augmente progressivement le nombre de buckets jusqu’à ce qu’EXPLAIN affiche la Sélectivité reflète.

Important : les histogrammes sont sur une seule colonne. Les dépendances entre colonnes (par exemple « status » et « country ») ne peuvent pas être représentées directement. Dans ce genre de cas, il est utile de créer un histogramme pour la colonne la plus sélective et d'adapter l'ordre des jointures en conséquence.

Bien choisir ses seaux

Par défaut, MySQL utilise 100 Seaux, mais permet de définir une valeur comprise entre 1 et 1 024 via WITH N BUCKETS. Un nombre plus élevé de buckets augmente la résolution, mais entraîne également une augmentation des métadonnées et de la charge de travail liée à l'analyse. Je commence généralement par une valeur prudente, j'évalue l'impact à l'aide de la commande EXPLAIN, puis j'augmente progressivement ce nombre si le plan semble toujours inadapté. Lorsque les valeurs sont très concentrées (par exemple, 90 % pour un statut), quelques buckets suffisent souvent ; en cas de prix ou d’horodatages très dispersés, il vaut mieux utiliser davantage de buckets. L’objectif est d’obtenir une Granularité, ce qui a permis de réduire sensiblement les erreurs d'appréciation sans alourdir inutilement la charge administrative.

Exemple pratique : workflow avec ANALYZE TABLE

Je suis une ligne directrice claire Flux de travail: Je commence par identifier les colonnes qui apparaissent souvent dans les conditions WHERE ou JOIN et qui présentent des distributions manifestement asymétriques. Ensuite, je génère un histogramme à l’aide de la commande ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS ; puis je le vérifie via INFORMATION_SCHEMA.COLUMN_STATISTICS. Après des mouvements de données, je rafraîchis à nouveau les statistiques avec ANALYZE TABLE. Si une statistique ne convient pas, je la supprime avec ANALYZE TABLE tbl DROP HISTOGRAM ON col;. Pour évaluer l'impact sur le plan d'exécution, je consulte Interpréter EXPLAIN ANALYZE et ces estimations par rapport aux chiffres réels Lignes à partir de

Ordres concrets et contrôle

Je procède de manière reproductible, en suivant quelques étapes claires, et je vérifie les statistiques JSON générées.

-- Créer des histogrammes sur des colonnes individuelles
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;

-- Plusieurs colonnes en une seule opération avec le même nombre de compartiments
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;

-- Supprimer des histogrammes de manière ciblée
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- Vérification visuelle des statistiques
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');

J'évalue immédiatement l'impact à l'aide de la commande EXPLAIN ANALYZE :

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
  AND created_at >= NOW() - INTERVAL 7 DAY;

L'estimation s'améliore-t-elle ? lignes Si l'on constate une différence notable et que le plan passe, par exemple, d'un « Full Scan » à un « Index-Range-Scan » ou modifie l'ordre des jointures, cela signifie que la mesure a porté ses fruits. Si l'écart reste important, j'augmente ou je réduis le nombre de buckets, puis je compare à nouveau.

Exemple : statut des commandes et valeurs rares

Dans un tableau „ orders “, le statut „ completed “ prédomine souvent, tandis que „ pending “ est assez fréquent et « canceled » très rare ; ceci déséquilibre sans histogramme, cela conduit facilement à des sélectivités erronées. Si une API interroge „ canceled “, l'optimiseur peut choisir à tort un balayage complet de la table, alors qu'un accès par index plus précis suffirait. Grâce à un histogramme singleton, MySQL détecte que „ canceled “ ne représente qu’une infime partie et opte pour un balayage d’intervalle d’index ou optimise l’ordre des jointures. Cela réduit la latence, et je n’ai pas besoin d’un index supplémentaire pour chaque Variante d'un filtre. Dans les tableaux de bord dotés de SLO stricts, cette correction apporte souvent des gains de réactivité notables.

Séries chronologiques et horodatages

Dans le cas des séries chronologiques, il y a beaucoup de Accès sur des données récentes ; les plages horaires plus anciennes restent généralement inactives. Un histogramme « Equi-Height » sur les colonnes `created_at` ou `updated_at` permet de distinguer les plages horaires très fréquentées de celles qui le sont rarement. L’optimiseur évalue alors correctement s’il est judicieux d’effectuer un balayage par plage (Range Scan) ou si un balayage de table (Table Scan) permet d’atteindre plus rapidement l’objectif. Je constate notamment, lors de filtrages temporels partiels sur de grandes tables, des changements de plan significatifs et une réduction des coûts d’E/S. Je considère que la Statistiques ici, les mises à jour sont plus fréquentes, car les priorités évoluent en fonction de l'activité quotidienne.

Partitions, types de données et collations

Je examine la répartition des données sur des tables partitionnées sur toutes les partitions. Des écarts importants (par exemple, sur plusieurs mois) peuvent lisser les histogrammes globaux. Si certaines partitions sont extrêmement sélectives ou extrêmement larges, je vérifie également, à l'aide de filtres de « partition pruning » dans la clause WHERE, si la qualité du plan reste satisfaisante. Dans l’ensemble, je veille à formuler les filtres de manière à ce que MySQL identifie les partitions dès le début exclure peut.

Les histogrammes fonctionnent mieux avec des types de données scalaires et comparables (nombres, dates/heures, VARCHAR/CHAR avec une collation appropriée). Dans le cas de Données LOB/JSON je mise plutôt sur Colonnes générées avec des valeurs extraites et typées, et les accompagne, si nécessaire, d'histogrammes ou d'indices. Pour les chaînes de caractères, la collation la logique de comparaison ; selon la collation, des valeurs peuvent coïncider (par exemple, en tenant compte de la casse). Je veille à ce que la collation soit cohérente avec les requêtes afin d'obtenir des sélectivités réalistes.

Limites et erreurs

Les histogrammes évaluent principalement les colonnes individuelles avec Constantes C'est vrai ; ils ne reflètent que de manière limitée les dépendances entre plusieurs colonnes. Ils atteignent leurs limites lorsque les colonnes sont fortement corrélées ou qu'il s'agit de paramètres dynamiques (par exemple, renseignés par l'application). Les champs booléens ou les colonnes présentant une distribution quasi uniforme tirent rarement profit de statistiques supplémentaires. Un nombre trop élevé de tranches et une maintenance excessive peuvent, à leur tour, allonger le temps consacré à l’administration et à l’analyse. C’est pourquoi j’utilise les histogrammes de manière ciblée et je vérifie régulièrement la Effet sur des modèles réels.

Vérification et mise à jour de l'optimiseur

Je vérifie le Utilisation des histogrammes via ANALYZE TABLE et des options pertinentes de l'optimiseur, afin que le planificateur utilise les statistiques de manière pertinente. Dans les systèmes très sollicités, je planifie la mise à jour pendant les périodes creuses ou par lots après des chargements importants. Avant et après, je compare les résultats d’EXPLAIN et d’EXPLAIN ANALYZE afin d’évaluer les modifications apportées à l’ordre des jointures, aux étapes de filtrage et aux modèles de coûts. En cas d’effets négatifs, je réagis immédiatement et je rétablis la statistique précédente. Pour un contrôle plus approfondi de la Options de l'optimiseur je veille à ce que les dépendances avec d'autres statistiques ne donnent pas lieu, à mon insu, à des Hypothèses produire.

Surveillance, protection contre la régression et guide opérationnel

Je me fabrique un modèle léger Guide tactique pour l'exploitation en production :

• Définir une base de référence : avant d'effectuer des modifications, exécuter EXPLAIN ANALYZE, noter la durée d'exécution, le nombre de „ lignes examinées “ et le compteur du gestionnaire.
• Créer/modifier un histogramme : cibler les colonnes de filtrage, utiliser des tranches conservatrices.
• Mesurer immédiatement après : plan, nombre de lignes estimé par rapport au nombre réel ; un écart supérieur à 10 est pour moi un signal d'alerte.
• Réglage fin : augmenter/diminuer le nombre de tranches ; si nécessaire, modifier l'ordre des filtres dans la requête.
• Préparer une restauration : exécuter la commande DROP HISTOGRAM si les latences augmentent.
• Automatisation : exécution de la commande ANALYZE après les chargements ETL ou les vagues importantes d'opérations DML, pendant les fenêtres de maintenance.

Pour analyser les causes, j'utilise Traces de l'optimiseur et EXPLAIN ANALYZE pour vérifier si le planificateur, sur la base des histogrammes, met bien en avant la table sélective appropriée. Pour les tests A/B, je fixe à titre d'essai l'ordre des jointures (STRAIGHT_JOIN) ou j'impose/interdis l'utilisation de certains index afin d'évaluer de manière isolée l'effet des statistiques.

D'un point de vue organisationnel, un bref Journal des changements Par tableau : colonne, nombre de tranches, date et heure, valeurs mesurées avant/après. Cela facilite les corrections ultérieures et évite les interactions ambiguës.

Aspects opérationnels : blocages, coûts, portabilité

ANALYZE TABLE effectue une Blocage des métadonnées sur la table, mais ne bloque pas de manière permanente les opérations habituelles de lecture/écriture. Pour les très grandes tables, je prévois suffisamment de temps ; la génération d’histogrammes fonctionne par échantillonnage et est limitée par la mémoire (mot-clé : mémoire interne pour le calcul). L'espace requis par les statistiques elles-mêmes reste modéré : quelques dizaines à quelques centaines de kilo-octets par colonne comportant 100 à 256 compartiments constituent une valeur indicative réaliste. Au total, je fais tout de même le calcul, car de nombreuses colonnes multipliées par de nombreuses tables donnent métadonnées visibles.

À l'adresse suivante : Dumps logiques (mysqldump) les histogrammes ne sont pas inclus dans les données ; après une restauration, je les recrée de manière ciblée. Lors d’une mise à niveau sur place, ils sont conservés. Côté serveur, j’ai besoin de privilèges suffisants pour exécuter ANALYZE TABLE sur les objets concernés ; dans les environnements strictement réglementés, j’intègre cette tâche dans des pipelines de maintenance.

Quand les histogrammes ne servent à rien

Je m'épargne Histogrammes sur les colonnes qui contiennent très peu de valeurs et qui sont de toute façon bien estimées. Même lorsque l'un des bons index couvre déjà des ensembles de résultats minimaux, un histogramme apporte rarement un gain supplémentaire. Les distributions uniformes ne nécessitent pas une granularité poussée. Dans les systèmes hautement dynamiques et à forte intensité d’écriture, la maintenance peut générer une charge inutile si je la lance trop fréquemment. Dans de telles situations, j’utilise la Énergie plutôt dans les stratégies d'indexation, la conception des requêtes et la mise en cache.

Aide-mémoire sous forme de tableau

J'utilise le suivant Vue d'ensemble pour prendre rapidement des décisions : quel type d'histogramme choisir, comment définir les tranches (buckets) et quels sont les coûts associés. Ce tableau sert d’aide-mémoire lors de l’analyse des requêtes problématiques. Je le mets à jour en fonction des enseignements tirés d’EXPLAIN ANALYZE et des métriques de production. Ce faisant, je tiens compte du fait que les distributions des données évoluent et que les hypothèses historiques deviennent obsolètes. L’essentiel reste de qualité de la planification à confirmer par des mesures réelles.

Aspect	Recommandation	Avantages	compromis	Exemple
Type	Singleton en présence de quelques valeurs dominantes	Taux de réussite précis pour les catégories courantes	Peu utile pour les zones continues	statut_de_la_commande
Type	Equi-Height pour des données continues déformées	Meilleure estimation sur l'ensemble de la plage de valeurs	Davantage de métadonnées lorsque le nombre de compartiments est élevé	created_at, price
Seaux	Commencer à 100, puis ajuster	Une résolution équilibrée	Charge d'analyse et de stockage plus élevée entre 512 et 1 024	AVEC 100 SEAUX
Soins	Après des modifications importantes des données, exécutez la commande ANALYZE	Sélectivités actuelles	Planifier une fenêtre de maintenance	ANALYZE TABLE … UPDATE HISTOGRAM
Contrôle	Vérifier via COLUMN_STATISTICS	Transparence et audit	Interprétation JSON requise	INFORMATION_SCHEMA.COLUMN_STATISTICS

Intégration dans le projet global de tuning

Je traite Histogrammes en tant qu'élément constitutif, au même titre que les index, la conception des requêtes, la mise en cache et les paramètres matériels. Souvent, un bon histogramme modifie l'ordre des jointures, réduit les E/S et garantit des temps de réponse constants. Cela ne remplace toutefois pas des stratégies d'indexation bien conçues ni un schéma efficace. Ceux qui examinent de plus près les décisions de planification tirent profit de Comprendre les plans d'exécution et compare les modèles de coûts aux durées réelles. Je vérifie régulièrement si les Charges de travail correspondent encore aux statistiques ou s'il faut procéder à des ajustements.

Scénarios de joint avancés

Les histogrammes s'avèrent particulièrement utiles lorsque plusieurs tableaux comportant des filtres sont impliqués. Exemple :

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;

Sans histogrammes, l'optimiseur risque de sous-estimer la sélectivité de `o.status=’canceled‘` ou de surestimer la proportion d'utilisateurs allemands. Avec un histogramme sur et pays et sans statut (le cas échéant, également sur o.created_at), le planificateur se rend généralement compte que cette combinaison est extrêmement sélective. Dans la pratique, je constate alors que MySQL détermine d’abord le sous-ensemble le plus petit (par exemple via un index sur users(country) ou orders(status, created_at)) et n’effectue la jointure qu’ensuite – au lieu de parcourir la grande table. Cela permet d’économiser des opérations d’E/S, de la mémoire tampon et de la puissance CPU, tout en stabilisant la latence, même sous charge.

Parce que les histogrammes ne sont que sur une seule colonne les stratégies d'index restent importantes : un index composite sur (status, created_at) peut accélérer encore davantage le balayage de plage. L'histogramme sert ici principalement à garantir que l'optimiseur Stratégie qu'il juge globalement avantageux.

Résumé pour la pratique

Je mets MySQL- J'utilise des histogrammes lorsque l'optimiseur se trompe en se basant sur les statistiques par défaut et que des distributions asymétriques génèrent des plans d'exécution erronés. Avec ANALYZE TABLE, je crée, mets à jour et supprime de manière ciblée les statistiques sur les colonnes qui prédominent dans les filtres et les jointures. Je choisis entre « Singleton » et « Equi-Height » en fonction des données, et je calibre le nombre de compartiments à l’aide de mesures. Grâce à EXPLAIN ANALYZE, je vérifie si l’ordre des jointures, la position des filtres et les balayages évoluent comme souhaité. C’est ainsi que j’obtiens, avec peu Overhead des requêtes nettement plus rapides – souvent sans index supplémentaires.