Comment optimiser les tables MySQL

Présentation

L'optimisation des tables MySQL aide à réorganiser les informations dans un serveur de stockage dédié pour améliorer les vitesses d'entrée et de sortie des données. Cependant, savoir quand utiliser chaque fonction d'optimisation et comment les appliquer à votre situation est la clé d'une maintenance de table viable.

Cet article fournit des conseils pratiques et des fonctions pour l'optimisation des tables MySQL.

Prérequis

• MySQL version 8.0 installé et configuré
• Accès à l'invite de commande ou à la ligne de commande
• Tables de base de données fragmentées

Pourquoi optimiser les tables MySQL ?

La raison principale d'une table MySQL non optimisée est la fréquence des requêtes de mise à jour et de suppression. À son tour, cela provoque une fragmentation et il y a des conséquences :

1. La table de la base de données prend plus d'espace que nécessaire.

2. L'interrogation des données prend plus de temps qu'il ne le devrait.

Les techniques d'optimisation de table MySQL traitent de la disposition des données dans une base de données. Le résultat est un stockage propre sans espace redondant et inutilisé, ce qui permet d'accélérer les requêtes.

Quand devez-vous optimiser les tables MySQL ?

Les tables où les informations d'une base de données sont continuellement mises à jour, telles que les bases de données transactionnelles, sont les candidats les plus susceptibles d'être optimisés.

Cependant, selon la taille de la base de données, la requête d'optimisation prend beaucoup de temps pour se terminer avec des tables plus grandes. Par conséquent, verrouiller une table pendant un long nombre d'heures n'est pas avantageux pour les systèmes transactionnels.

Au lieu d'optimiser une table tout de suite, envisagez d'essayer certaines de ces astuces pour les tables du moteur INNODB :

• Supprimer l'index, optimiser et rajouter l'index . Si une table fonctionne sans index pendant un certain temps, la suppression, l'optimisation et le rajout des index s'exécutent plus rapidement dans certains cas.
• Analyser quelle valeur bénéficie le plus du compactage . Parfois, l'index primaire n'est pas utile ou problématique. L'optimisation pour la mauvaise valeur entraîne une fragmentation avec des index secondaires.

Rechercher des tableaux pour l'optimisation

Il existe plusieurs façons d'afficher des tableaux et de les analyser pour les optimiser. Commencez par vous connecter à votre base de données MySQL :

use <database name>

Selon votre cas d'utilisation, filtrez les informations nécessaires en suivant l'un des conseils ci-dessous.

Astuce 1 :Afficher l'espace inutilisé dans un tableau

Vérifiez l'état de la table souhaitée avec :

show table status like "<table name>" \G

La sortie affiche des informations générales sur la table. Les deux chiffres suivants sont importants :

• Data_length représente la quantité d'espace que la base de données occupe au total.
• Data_free affiche les octets inutilisés alloués dans la table de la base de données. Ces informations permettent d'identifier les tables à optimiser et la quantité d'espace qui sera libérée par la suite.

Astuce 2 :Afficher l'espace inutilisé pour tous les tableaux

Le schéma d'information stocke des métadonnées sur un schéma de base de données. Pour vérifier les données inutilisées allouées pour toutes les tables d'un schéma sélectionné, exécutez :

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

La requête affiche le nom de la table, l'espace total et l'espace alloué inutilisé. Par défaut, la mémoire s'imprime en octets.

Astuce 3 :Afficher les données en mégaoctets

Imprimez les données en mégaoctets avec :

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Les exemples de tables ne sont pas fortement fragmentés. Cependant, en utilisant optimize table aide à libérer de l'espace de la test_table .

Optimiser les tableaux

Il existe plusieurs façons d'optimiser les tables par défragmentation. Cependant, les étapes générales que vous effectuez sont similaires.

Tout d'abord, le processus fait une copie temporaire de la table où l'optimisation se produit. Ensuite, lorsque l'opération est terminée, la fonction remplace la table d'origine par la table optimisée, en la renommant d'après l'original.

Astuce 1 :Optimisez une table à l'aide de MySQL

MySQL fournit une fonction pour optimiser une table. Pour optimiser un tableau, utilisez :

OPTIMIZE TABLE <table name>;

La sortie affiche un message d'état informatif sur les actions et les résultats de l'optimisation sous forme de tableau.

Astuce 2 :Optimisez plusieurs tableaux à la fois

Pour optimiser plusieurs tables à la fois, utilisez :

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

Le résultat affiche l'état de l'optimisation pour chaque table optimisée.

Astuce 3 :Optimisez les tableaux à l'aide du terminal

Effectuez l'optimisation des tables via le terminal Linux avec :

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Par exemple, pour effectuer la vérification sur un schéma nommé test et la table nommée test_table en utilisant la racine informations d'identification de l'utilisateur, exécutez :

sudo mysqlcheck -o test test_table -u root

Pour effectuer l'optimisation sur plusieurs tables, séparez chaque nom de table par un espace :

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Après optimisation

Les résultats de l'optimisation modifient les valeurs de data_length et data_free du tableau optimisé. Les deux valeurs sont abaissées, indiquant :

1. L'optimisation a libéré la mémoire allouée inutilisée.

2. La mémoire globale de la base de données est inférieure en raison de l'espace libéré.