Introduction
Dans le domaine des bases de données MySQL, la fragmentation peut avoir un impact significatif sur les performances et l’efficacité. Ce blog expliquera ce qu’est la fragmentation, comment elle se produit, comment l’identifier et les méthodes pour défragmenter vos tables MySQL.
Qu’est-ce que la fragmentation dans MySQL ?
La fragmentation dans MySQL fait référence à l’utilisation inefficace de l’espace disque dans les tables de base de données, résultant d’opérations telles que les mises à jour, les suppressions et les insertions de données. Essentiellement, il s’agit du phénomène par lequel les données d’un tableau deviennent désorganisées ou dispersées, provoquant
Le moteur de stockage utilise plus d’espace disque que nécessaire et peut potentiellement ralentir les performances des requêtes.
Types de fragmentation:
1. Fragmentation interne: Cela se produit dans une seule page de données. Par exemple, si des lignes sont supprimées d’une page et que les lignes restantes ne remplissent pas complètement la page, l’espace inutilisé dans cette page entraîne une fragmentation interne.
2. Fragmentation externe: Cela se produit au niveau des tables ou des fichiers d’index. Par exemple, si une table est fréquemment redimensionnée ou si des enregistrements sont fréquemment ajoutés et supprimés, la présentation physique des données de la table peut devenir fragmentée.
Comment se produit la fragmentation
La fragmentation peut survenir pour plusieurs raisons :
● Mises à jour fréquentes: Lorsqu’une ligne est mise à jour et que les nouvelles données sont plus volumineuses que les anciennes données, la ligne peut être déplacée vers un nouvel emplacement, laissant de l’espace inutilisé.
● Suppressions de lignes: La suppression de lignes peut laisser des espaces dans les pages de données. Si ces espaces ne sont pas réutilisés efficacement, cela entraîne une perte d’espace.
● Insertions: L’ajout de nouvelles lignes, en particulier avec des tailles de lignes variables, peut conduire à une utilisation inefficace de l’espace.
Exemple:
Considérons une table nommée `employés`avec colonnes`identifiant‘, ‘nom‘, et ‘salaire`. Si des lignes sont insérées, mises à jour et supprimées fréquemment, le tableau peut se retrouver avec de nombreux espaces vides et de l’espace inutilisé dans les pages de données. Par exemple, si un `MISE À JOURL’opération ` augmente la taille du `nom`, la base de données peut déplacer la ligne vers une nouvelle page s’il n’y a pas assez d’espace sur l’ancienne page, provoquant une fragmentation.
Comment identifier la fragmentation des tables MySQL
Pour détecter la fragmentation, vous pouvez utiliser divers outils et commandes MySQL :
1. Vérifier l’état du tableau: Utilisez le `AFFICHER L’ÉTAT DE LA TABLE` pour obtenir des informations sur la table, y compris le `Data_free`, qui indique la quantité d’espace libre dans le tableau.
AFFICHER LE STATUT DE LA TABLE COMME « employés » ;
Regardez le `Data_free` pour comprendre s’il y a une quantité importante d’espace inutilisé.
2. Statistiques des tables InnoDB: Pour les tables InnoDB, vous pouvez utiliser `INFORMATION_SCHEMA` pour obtenir des statistiques détaillées.
SELECT table_name, data_free FROM information_schema.tables WHERE table_schema="your_database_name" AND engine="InnoDB";
3. Analyser le tableau: Utilisez le `TABLEAU D’ANALYSE` pour vérifier la structure et les statistiques de la table, ce qui peut également aider à identifier la fragmentation.
Employés de la TABLE D’ANALYSE ;
Défragmenter une table dans MySQL
Pour défragmenter une table et récupérer l’espace inutilisé, vous disposez de plusieurs options selon le moteur de stockage utilisé :
OPTIMISER LE TABLEAU
Cette commande reconstruit la table et récupère l’espace inutilisé. Cela fonctionne pour les tables InnoDB et MyISAM.
OPTIMISER les employés de TABLE ;
MODIFIER LE TABLEAU
Une autre méthode pour les tables InnoDB consiste à utiliser `MODIFIER LE TABLEAU‘avec le ‘MOTEUR=InnoDB`, qui reconstruit efficacement la table.
ALTER TABLE employés ENGINE=InnoDB;
TABLEAU DE RÉPARATION
Cette commande peut aider à reconstruire et défragmenter les tables MyISAM.
Employés de TABLE DE RÉPARATION ;
pt-online-changement-de-schéma
`pt-online-changement-de-schéma` est un outil puissant de la boîte à outils de Percona qui vous permet d’apporter des modifications au schéma de votre base de données MySQL avec un temps d’arrêt minimal. L’une de ses fonctionnalités utiles consiste à défragmenter les tables en les reconstruisant, ce qui peut améliorer les performances et
récupérer l’espace disque inutilisé. Lorsque vous devez défragmenter une table, vous souhaitez essentiellement la reconstruire pour récupérer de l’espace inutilisé et améliorer les performances. `pt-online-changement-de-schéma` peut y parvenir en :
1. Reconstruire la table: Il crée une nouvelle copie de la table (avec le même schéma), réorganisant efficacement les données.
2. Minimiser les temps d’arrêt: Il effectue l’opération sans verrouiller la table, ce qui est crucial pour les bases de données à fort trafic.
Exemple:
Disons que nous avons une table nommée `employés` et nous voulons le défragmenter. Voici comment vous pouvez utiliser `pt-online-changement-de-schéma` pour y parvenir.
Instructions étape par étape
1. Exécutez un essai à sec: Avant d’apporter des modifications, effectuez un essai à sec pour vous assurer que l’opération se déroulera comme prévu sur le serveur.
pt-online-schema-change h=localhost,D=your_database,t=employees –alter « ENGINE=InnoDB » –dry-run
2. Exécuter le changement de schéma: Si le test montre que tout est en ordre, exécutez la modification de schéma réelle pour défragmenter la table.
pt-online-schema-change --execute \ h=localhost,D=employees,t=table_name \ --alter=" ENGINE=InnoDB" \ --print --progress time,10 \ --set-vars transaction_isolation='READ-COMMITTED',lock_wait_timeout=60 \ --critical-load=50 \ --max-load=75
3. Surveiller les progrès: `pt-online-changement-de-schéma`fournit des commentaires en temps réel sur ses progrès. Surveillez le résultat de la commande pour vous assurer que le processus se termine
avec succès.
4. Vérifier les modifications: Une fois le processus terminé, vous pouvez vérifier l’état de la table pour confirmer que la table a été défragmentée et que tout l’espace a été récupéré.
AFFICHER LE STATUT DE LA TABLE COMME « employés » ;
En utilisant pt-online-schema-change, vous pouvez défragmenter des tables volumineuses sans affecter la disponibilité de la base de données. L’outil gère intelligemment la réplication des modifications, préserve l’intégrité des données et fournit des options de surveillance et de contrôle du processus de défragmentation.
Conclusion
La fragmentation dans MySQL est un problème courant qui peut avoir un impact sur les performances et l’efficacité du stockage. En comprenant ses causes et en utilisant les outils et commandes appropriés pour identifier et défragmenter les tables, vous pouvez maintenir des performances de base de données optimales. Une surveillance et une maintenance régulières sont essentielles pour garantir le bon fonctionnement et l’efficacité de vos bases de données MySQL.
Source link