La commande SHOW TABLE STATUS de MySQL et phpMyAdmin fournissent tous deux un aperçu de l’utilisation de chacune de vos tables. Cet aperçu présente néanmoins souvent des différences (importantes) par rapport à l’espace qui est réellement utilisé sur le disque, et ce pour de multiples raisons, voire une combinaison de celles-ci :
Index
Lors de la création d’une table, vous avez la possibilité de définir un ou plusieurs index. Un index contient une partie ou la totalité des données d’une colonne spécifique, permettant de filtrer les résultats avec des références aux informations complètes de l’enregistrement. Cela signifie qu’une partie des données peut être dupliquée sur le disque.
Fragmentation ou espaces ‘vides’ dans le fichier de données
MySQL ne supprime pas réellement les données après avoir supprimé un enregistrement au sein d’une table, mais le marque néanmoins comme ‘supprimé’ afin que de nouvelles données puissent réutiliser l’espace ainsi libéré. Dans les cas où cela ne se produit pas, les données occupent toujours de l’espace disque, ce qui explique pourquoi votre table occupe plus d’espace disque que ce qu’indique MySQL.
Au fil du temps, pour certains types de tables contenant des données volatiles (il s’agit principalement des tables utilisées pour stocker des données en cache), l’espace occupé par les enregistrements supprimés finit par augmenter de manière rapide et non négligeable. Celui-ci peut en effet parfois atteindre 50 %, voire plus de 100 % du volume occupé par les données réellement utiles.
Le ‘lazy counting’ de MySQL
Étant donné que la lecture de la totalité du fichier de données sur le disque pour en calculer l’utilisation demande beaucoup de ressources, MySQL agit de manière astucieuse en estimant certains chiffres sur la base d’un échantillon de données contenues dans la table. Si ce n’était pas le cas, les performances de vos requêtes MySQL en souffriraient, ce qui entraînerait une latence élevée et des sites web lents.
Au fil du temps, cet échantillon de données peut cependant devenir très peu représentatif de la réalité, conduisant à une sous-estimation de l’utilisation réelle du disque.
Nombre de tables
En plus d’avoir des requêtes intelligentes et d’ajouter les index appropriés, la structure de la base de données est également un point très important. N’ajoutez que les tables dont vous avez réellement besoin. Chaque table crée en effet un peu de surcharge, et plus il y a de tables, plus la surcharge est importante.
Solutions
La réanalyse ou l’optimisation des tables suspectées d’utiliser plus d’espace disque que ce que MySQL rapporte s’avère souvent utile. Et il existe plusieurs façons de faire cela :
- Pour résoudre le problème du ‘lazy counting’, il peut être suffisant de simplement exécuter la commande ANALYZE TABLE <tablename>;
- Pour résoudre le problème de fragmentation (qui peut également se produire au niveau des index), vous pouvez déclencher une reconstruction en utilisant l’une des deux méthodes suivantes :
- OPTIMIZE TABLE <tablename>;
- ALTER TABLE <tablename> ENGINE=InnoDB; (méthode à utiliser uniquement s’il s’agit d’une table InnoDB ; la méthode précédente est plus sûre, car elle fonctionne pour tous les types de tables)
Les avantages d’une base de données optimisée
En plus d’utiliser moins d’espace disque, une base de données optimisée présente quelques autres avantages.
- L’accès à des bases de données plus volumineuses requiert davantage de mémoire et sollicite davantage le processeur du système, alors que ces ressources pourraient être utilisées pour d’autres processus importants.
- La sauvegarde d’une base de données optimisée nécessite moins de temps, ce qui a pour effet d’améliorer les performances générales du serveur pendant les opérations de sauvegarde.
- L’interrogation de tables non optimisées peut ralentir votre site web.