...
logo de l'hébergement web

Fragmentation et réorganisation de l'index de la base de données : Guide ultime

Fragmentation de l'index ralentit les requêtes de manière mesurable, car l'ordre physique des pages d'index diffère de l'ordre logique, ce qui augmente les E/S, le CPU et les temps d'attente. Dans ce guide, je montre comment Réorganisation, La fragmentation peut être détectée de manière fiable et éliminée de manière durable grâce à l'utilisation d'un système de détection, de reconstruction, de remplissage et de surveillance.

Points centraux

  • Définition: Les arbres B* fragmentés génèrent plus d'E/S et des scans plus lents.
  • Causes: Page-splits, deletes, valeurs clés déplacées.
  • Seuils: Reorg à partir de ~5-30 %, Rebuild à partir de ~30 %.
  • Focus sur MySQL: respecter OPTIMIZE TABLE et les facteurs de remplissage.
  • Automatisation: travaux planifiés, opérations en ligne, métriques.

Que signifie techniquement la fragmentation de l'index ?

Je désigne par Fragmentation l'écart entre l'ordre logique des clés et la chaîne physique des pages d'un index B* Tree. En cas de nombreuses INSERT, UPDATE et DELETE, des lacunes, des splits et des pages Leaf désordonnées apparaissent, ce qui déclenche davantage de processus de lecture. Conséquence : les analyses sautent plus souvent, les occurrences de cache tampon diminuent et les coûts de CPU augmentent. Même les plans idéaux en pâtissent, car la mémoire fournit plus lentement les pages éparpillées. Je fais donc toujours attention au contexte de charge de travail, la taille des données et la disposition de la mémoire.

Types de fragmentation et leurs symptômes

Je fais une distinction pragmatique :

  • Fragmentation logiqueLes pages Leaf ne sont plus concaténées dans l'ordre des clés. Les scans de plage nécessitent des sauts supplémentaires, Read-Ahead intervient moins bien.
  • Fragmentation interneLes pages contiennent beaucoup d'espace inutilisé (faible taux de remplissage). Plus de pages doivent être lues par ligne de résultat ; la taille de l'index augmente sans utilité.
  • Fragmentation structurelle: Hauteur d'arbre défavorable, nœuds non équilibrés ou tas avec des enregistrements forwardés (par ex. dans SQL Server). Les accès deviennent plus indirects.

Cela se traduit par un plus grand nombre de pages lues par ligne, des latences plus élevées lors des scans de plage ou d'ordre, ainsi qu'une baisse du taux d'utilisation du cache. Je corrèle toujours les signaux avec les statistiques d'attente afin d'éviter toute confusion avec des problèmes de réseau ou de stockage.

les causes : Insertions, mises à jour, pages splittées

Les insertions fréquentes remplissent les pages jusqu'à la marge, puis une nouvelle clé impose un Page-split, qui laisse deux pages à moitié remplies. Les suppressions suppriment des entrées, mais l'espace libre reste réparti et ne s'utilise pas toujours localement lors de l'insertion suivante. Les mises à jour qui modifient les colonnes de clés déplacent les enregistrements et créent de nouveaux espaces. Les modèles de clés randomisés comme les GUID augmentent encore la dispersion et donc le désordre. Je minimise les splits en utilisant le Facteur de remplissage en fonction de la charge d'écriture.

Rendre les pertes de performance mesurables

Je ne mesure pas la fragmentation de manière isolée, mais en interaction avec les temps de requête, les log-reads, les page-reads et les classes d'attente. Si la latence moyenne des scans de plage augmente et si le CPU par requête augmente, je vérifie d'abord les indices physiques des index. Une fragmentation élevée augmente le nombre de pages lues par quantité égale de lignes et condense les temps d'attente sur les entrées/sorties. Une comparaison fondée avant et après Reorg ou Rebuild montre les avantages réels. Pour en savoir plus sur le verrouillage, les plans et les goulets d'étranglement, il vaut la peine de jeter un coup d'œil sur Performance de la base de données, pour classer correctement les symptômes.

Métriques, temps d'attente et efficacité des pages en détail

Dans la pratique, j'observe en plus

  • Pages par scan: Combien de pages Leaf lisent un scan de zone typique ? Si la valeur augmente pour une même quantité de résultats, cela indique une fragmentation ou un taux de remplissage trop faible.
  • Coup de cœur Read-Ahead: les chaînes fragmentées sabotent les pré-extractions séquentielles ; l'effet est moindre sur les SSD, mais pas nul, car le CPU, les latches et le cache continuent de souffrir.
  • Classes d'attentePAGEIOLATCH/IO-Waits (SQL Server), db file sequential/scattered read (Oracle) ou augmentation des latences de lecture InnoDB (MySQL) augmentent en cas de sauts plus importants dans l'index.
  • Qualité de la cacheSi le taux d'utilisation du buffer pool diminue parallèlement à la fragmentation, il vaut presque toujours la peine de procéder à une reconstruction, en particulier pour les scans à grande échelle.

Analyser la fragmentation : SQL Server, MySQL, Oracle

Je démarre toujours l'analyse avec un indice fiable. Instantané de la santé de l'index et filtre les petits index dont l'utilisation des pages varie statistiquement. Dans SQL Server, sys.dm_db_index_physical_stats fournit le degré de fragmentation en même temps que page_count, afin que je puisse pondérer les valeurs aberrantes. Les valeurs supérieures à 5-30 % indiquent une réorganisation, les fortes valeurs aberrantes supérieures à 30 % indiquent une reconstruction, surtout si page_count est grand. Dans MySQL, je vérifie SHOW TABLE STATUS ou les vues INFORMATION_SCHEMA et j'observe la longueur des données et des index dans le temps. Dans Oracle, je vérifie également si une reconstruction en ligne est disponible pour Temps d'arrêt d'éviter.

Interrogations pratiques et pondération

Je travaille avec des requêtes simples et réutilisables et je pondère en fonction de la taille des pages et de leur pertinence :

  • Serveur SQL: je détermine la fragmentation et je filtre les petits indices. 
    SELECT DB_NAME() AS db, OBJECT_NAME(i.object_id) AS obj, i.name AS idx,
       ips.index_type_desc, ips.page_count, ips.avg_fragmentation_in_percent
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED') ips
WHERE ips.page_count >= 100
ORDER BY ips.avg_fragmentation_in_percent DESC, ips.page_count DESC ;
  • MySQL (InnoDB): Je regarde la taille de l'index, l'espace libre et le taux de changement. 
    SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, INDEX_LENGTH, DATA_FREE
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB'.
  ET INDEX_LENGTH > 0
ORDER BY (DATA_FREE) DESC ;

    Parallèlement, je compare les valeurs dans le temps (par ex. quotidiennement) afin de séparer les vraies tendances des valeurs aberrantes. Pour les statistiques, j'utilise ANALYZE TABLE avec parcimonie lorsque l'optimiseur prend des cardinalités erronées.

  • OracleJe vérifie les statistiques des segments (espaces libres, extents) et la disponibilité de REBUILD ONLINE afin de pouvoir planifier les fenêtres de maintenance.

Il est important pour moi de ne considérer que les index à forte utilisation. Un index fragmenté mais inutilisé est davantage un candidat à la suppression qu'à la réorganisation.

Réorganisation vs. Reconstruction : Matrice de décision

Je choisis la méthode en fonction du degré Fragmentation et des fenêtres de fonctionnement, car tous les environnements ne supportent pas des pics d'E/S intenses. Reorganisation réorganise les pages de feuilles, réduit les sauts logiques, comprime au facteur de remplissage et reste généralement en ligne. Rebuild recrée l'index, nettoie complètement, rend de la mémoire et met à jour les statistiques, mais exige du CPU, de l'E/S et souvent des verrous plus longs. Les petits index de moins de 100 pages environ en profitent rarement beaucoup, tandis que les grandes structures gagnent nettement à partir de 30 % de fragmentation. Je justifie la décision par des chiffres clés afin que l'effet reste compréhensible et que le Plan d'entretien correspond.

Méthode Besoin en ressources Utilisation typique Effet principal
Réorganisation Faible à moyen ~5-30 Fragmentation % Réorganisation, compression vers le facteur de remplissage
Reconstruction Haute > 30 % Fragmentation Recréation complète, libération de la mémoire

Options en ligne, verrouillages et effets de page

Pour un fonctionnement sans interruption, j'utilise - là où c'est disponible - des Reconstructions en ligne de l'année. Ce faisant, je fais attention

  • Édition/Version: Les fonctionnalités en ligne varient en fonction de la base de données et de l'édition. Je vérifie chaque environnement séparément.
  • Verrouillage temporaire des métadonnéesMême “en ligne” exige généralement des blocages au début/à la fin. Je les programme sciemment dans des phases calmes.
  • Plages de température/de travail: des options comme SORT_IN_TEMPDB (SQL Server) allègent le fichier de données principal, mais demandent de l'espace mémoire supplémentaire.
  • RéplicationLes reconstructions augmentent le volume des logs. J'observe le replica lag et j'effectue des drops si nécessaire pour éviter les retards.

Pour les tas SQL Server, je tiens compte des éléments suivants Forwarded Records; ici, une reconstruction de table aide à supprimer les redirections. Dans Oracle, j'utilise REBUILD ONLINE ou MOVE PARTITION (avec UPDATE INDEXES) pour réduire les temps d'arrêt.

Facteur de remplissage, Page-Splits et mémoire

Trouver un Facteur de remplissage entre 70 et 90 % pour les tables à forte écriture, afin que les futurs inserts puissent utiliser l'espace libre localement. Si je diminue trop le facteur de remplissage, l'index croît plus rapidement et utilise plus de mémoire ; si je le fixe trop haut, les fractionnements et la fragmentation augmentent. J'observe donc la relation entre l'utilisation des pages, la charge d'écriture et le modèle d'insertion sur plusieurs cycles. Lors des reconstructions, je définis délibérément le facteur de remplissage par index et non de manière globale pour l'ensemble de la base de données. Un contrôle régulier permet d'éviter qu'un bon niveau de remplissage initial ne soit pas atteint. compromis bascule des mois plus tard.

Comprendre les facteurs de remplissage par plateforme

  • Serveur SQLFILLFACTOR est une propriété d'index qui prend effet lors de la reconstruction/création. Pour les index secondaires très volatils, je définis une valeur plus faible, pour les structures à forte charge de lecture, une valeur plus élevée. Je documente la valeur choisie par indice et recalibre après les changements de profil de charge.
  • MySQL (InnoDB): Avec innodb_fill_factor j'influence l'espace libre qu'InnoDB laisse lors des (re)constructions. Il ne s'applique pas aux DML de tous les jours, mais avec OPTIMIZE/ALTER, il aide à atténuer les splits à l'avenir. En outre, je planifie les hotspots (clés monotones) de manière à réduire la concurrence des latchs et les splits.
  • Oracle & PostgreSQL: Paramètre STORAGE ou. FILLFACTOR (Postgres) donnent de la marge pour l'air libre dans les pages. Pour les tableaux Write-heavy, j'utilise des degrés de remplissage conservateurs et je compense le surplus de mémoire par des temps de scan mesurablement meilleurs.

Spécifique à MySQL et WordPress

Dans MySQL, je peux OPTIMIZE TABLE chez InnoDB, de réorganiser les tables et les index associés et de restituer l'espace libre. Les charges de travail très fragmentées avec de nombreux suppressions bénéficient en outre de la recréation périodique d'index secondaires critiques. Dans les installations WordPress, je réduis les ballasts tels que les révisions et les commentaires de spam avant d'optimiser afin de réduire le nombre de pages à réorganiser. Je combine ces étapes avec une stratégie d'indexation propre pour wp_postmeta et les tables similaires qui déclenchent souvent des scans. Pour une entrée en matière pratique, voir le guide sur Optimiser les index de WordPress, Il s'agit d'un livre qui aborde les points d'achoppement typiques.

Pratique MySQL : OPTIMIZE, partitions et effets secondaires

Pour InnoDB, je remarque en plus

  • OPTIMIZE TABLE reconstruit la table (et les index) et peut, selon la version, fonctionner en grande partie “inplace”, mais nécessite toujours des méta-locks et un espace libre pour les logs. Je prévois des plages horaires dédiées à cet effet.
  • Partitionnement permet une maintenance ciblée : OPTIMIZE PARTITION uniquement pour les zones chaudes ou fortement effacées réduit les pics d'E/S et le temps de fonctionnement.
  • RéplicationLes gros rebuild génèrent du volume binlog et peuvent retarder les réplicas. Je répartis la maintenance sur plusieurs nuits ou je travaille de manière partitionnée.
  • ANALYSE TABLE renouvelle les statistiques dont l'Optimizer a besoin pour de meilleurs plans - en particulier après des changements structurels massifs.

Dans les environnements WordPress, je réduis au préalable transients, Les révisions et les messages supprimés, afin qu'OPTIMIZE déplace moins de données. Pour wp_postmeta, je vérifie si les requêtes sont ciblées sur des index composites appropriés afin d'éviter les balayages larges.

Les spécificités de PostgreSQL en bref

Même si l'accent est mis ici sur MySQL, je tiens compte des environnements hétérogènes :

  • VACUUM/Autovacuum évite le gonflement, mais ne remplace pas REINDEX si les structures B-Tree sont très fragmentées.
  • REINDEX CONCURRENTLY permet de construire en grande partie en ligne de nouveaux index avec des blocages limités.
  • fillfactor par table/index contrôle l'air libre pour les futures INSERTs/UPDATEs. Les tables Write-heavy bénéficient de valeurs plus faibles.
  • Partitions par période allègent les fenêtres de maintenance ; REINDEX peut être appliqué de manière ciblée par partition.

Maintenance automatisée et seuils

J'automatise la réorg et la reconstruction à l'aide d'outils robustes. Seuils et n'active que les index avec un page_count suffisant pour éviter le bruit. Les jobs s'exécutent dans des fenêtres de maintenance, tandis que j'exécute de longues opérations via des options en ligne, si possible sans temps d'arrêt. Une approche échelonnée reporte les grandes reconstructions à des périodes calmes et permet aux petites réorgs de s'exécuter plus souvent. J'actualise les statistiques après des changements profonds afin que l'optimiseur choisisse de meilleurs plans en temps réel. Des alertes interviennent dès que la fragmentation ou les latences dépassent des limites prédéfinies, afin que j'agisse avant les plaintes des utilisateurs.

Runbook (livre de course) : Séquence d'étapes pour des résultats durables

  1. Identifier: Snapshot des indices Top-N par taille et fragmentation, filtrer les petits indices.
  2. Donner la priorité: classer par criticité de la charge de travail, page_count et charge de balayage.
  3. Planifier: Ordonnancer Reorg/Rebuild en fonction de valeurs seuils, calculer les options en ligne et les besoins en Temp/Log.
  4. Exécuter: échelonner les gros objets, étrangler les E/S, observer le lag de réplication.
  5. Statistiques: Après Rebuild/OPTIMIZE, actualiser les statistiques (ou s'assurer que cela se fait automatiquement).
  6. Valider: Mesurer avant/après : Latence, pages lues, temps d'attente, débit de la mémoire cache.
  7. Calibrer: vérifier les facteurs de remplissage et les seuils, documenter les Lessons Learned.

Hosting-Tuning : Règles pratiques

Dans les environnements d'hébergement, je planifie des analyses hebdomadaire, Réguler la fenêtre d'E/S de la maintenance et combiner avec la mise en cache afin de conserver les hotsets en mémoire. Les paramètres TempDB/Redo/Binlog et les supports de stockage influencent nettement les effets perçus de la défragmentation. J'évalue également si les index superflus ne font que générer des coûts, car chaque index supplémentaire augmente le travail d'écriture et les chances de fragmentation. Avant chaque nouvel index, je vérifie les modèles de charge de travail, les cardinalités et la couverture existante. Dans cette vue d'ensemble, j'esquisse les écueils typiques à éviter. Les pièges de l'index dans MySQL, Il s'agit d'un outil qui permet d'éviter les erreurs d'appréciation.

Coûts/bénéfices et quand je ne fais rien consciemment

Toute fragmentation ne vaut pas une maintenance. Je m'abstiens délibérément si

  • L'objet est petit (p. ex. moins de 100 pages) et varie fortement - c'est là que l'utilité s'évanouit.
  • Les requêtes sont ponctuelles (principalement des recherches par clé) et qu'aucune analyse de plage n'est en cours.
  • la charge de travail est transitoire (fenêtre de migration, archivage prochain) - alors je ne prévois qu'une reconstruction finale.

Au lieu de cela, j'investis alors dans de meilleurs indices, moins de redondance et une sélection propre des clés, afin que les futurs fractionnements soient moins fréquents.

Quand réorganiser, quand attendre ?

Je résous une Réorganisation si le degré de fragmentation augmente modérément et si le nombre de pages concernées est suffisant pour avoir un impact réel. Après des suppressions ou des archivages en masse, une redistribution ordonnée apporte souvent des gains de scarge sensibles. En cas de fortes aberrations ou de besoin de mémoire, je prévois une reconstruction, de préférence en ligne, afin de ne guère perturber le fonctionnement. Je laisse plus souvent intacts les petits index de moins de 100 pages, car leur mise en page varie fortement et leur utilité reste faible. Je documente ma décision avec des chiffres avant/après afin de mieux planifier les cycles futurs.

Prévention à long terme grâce au design

Bon Conception de schémas réduit la fragmentation avant même le premier insert, en assurant la cohérence du choix des clés, des types de données et de la normalisation. J'évite les lignes trop larges, qui permettent moins d'enregistrements par page et favorisent les fractionnements. Le partitionnement sépare les données froides des données chaudes et réduit les effets latéraux lors de la maintenance et des sauvegardes. Une optimisation minutieuse des requêtes réduit la dépendance à l'égard d'analyses coûteuses et aligne les index sur des modèles réels. Lorsque les charges de travail changent, j'adapte progressivement les définitions d'index au lieu de rejeter des structures entières ad hoc.

Choix de la clé et modèle d'insert

Le choix de la clé primaire détermine en grande partie le comportement de split :

  • Clés monotones (par ex. AUTO_INCREMENT, IDs basés sur le temps) regroupent les inserts sur le bord droit, réduisent la dispersion et les splits, mais peuvent générer des hotspots. J'égalise les hotspots avec le buffering/batching.
  • Clés randomisées (p. ex. GUID/UUID v4) répartissent la charge, mais augmentent la probabilité de fractionnement. Les variantes séquentielles (par ex. les UUID basés sur le temps) équilibrent mieux la répartition et l'ordre.
  • Largeur de la clé augmentent l'index et le nombre de pages nécessaires. Les clés légères et sélectives sont plus durables.

De plus, la compression des lignes et des pages atténue le taux de fractionnement, car il y a plus d'entrées par page. Mais je vérifie toujours le coût de l'unité centrale et la disponibilité des licences/fonctions avant d'activer la compression.

En bref, nous avons résumé : Des étapes qui ont un impact

Je commence par une approche focalisée Analyse des indices les plus grands et les plus fragmentés, en donnant la priorité au page_count et à la criticité de la charge de travail. Ensuite, je mets en œuvre des mesures échelonnées : réorganiser les cas modérés, reconstruire les cas lourds, réajuster les facteurs de remplissage par index. Des tâches automatisées maintiennent l'ordre sans intervention manuelle permanente, tandis que des alertes se déclenchent de manière fiable en cas de dérives. Les environnements MySQL et WordPress profitent sensiblement de la réduction préalable des déchets de données et de la conservation des index pertinents. Avec un monitoring cohérent, des seuils clairs et des playbooks répétables, il est possible d'éviter les erreurs. Performance stable - même lorsque les données augmentent rapidement.

Derniers articles