Optimiser les tables et les bases de données dans MySQL

1. Quand et pourquoi optimiser les tables dans MySQL
2. Optimiser les tables dans MySQL
3. Utiliser MySQL Shell dans le système d’exploitation Windows/Ubuntu 20.04 pour optimiser une ou plusieurs tables
4. Utiliser le terminal Windows pour optimiser le tableau
5. Utiliser le terminal Ubuntu 20.04 pour optimiser le tableau
6. Optimiser toutes les tables dans une seule base de données MySQL
7. Optimiser toutes les bases de données dans MySQL

Ce tutoriel est un guide complet sur la façon dont nous pouvons optimiser les tables et les bases de données dans MySQL. Nous utiliserons deux systèmes d’exploitation, Windows et Linux (Ubuntu 20.04).

Il renseigne également sur l’importance de l’optimisation dans MySQL.

Quand et pourquoi optimiser les tables dans MySQL

Il est plus probable que les fichiers de données dans MySQL soient fragmentés si notre application effectue de nombreuses opérations DELETE et UPDATE sur une base de données. Il en résulte un espace inutilisé qui peut également affecter les performances.

Il est absolument nécessaire que nous défragmentions nos tables MySQL de manière continue et réclamions l’espace inutilisé. C’est là que nous avons besoin de l’optimisation des tables dans MySQL, qui prend en charge la réorganisation des données dans un serveur de stockage dédié, ce qui améliore en fin de compte les performances et la vitesse d’entrée et de sortie des données.

Maintenant, comment savons-nous quelle table devons-nous optimiser ? Nous devrions optimiser les tables où les informations (données) sont continuellement mises à jour ; par exemple, les bases de données transactionnelles sont les candidats parfaits pour l’optimisation des tables.

Cependant, les requêtes d’optimisation peuvent prendre plus de temps en fonction de la taille des tables et de la base de données. Par conséquent, il n’est pas bon pour un système de transaction de verrouiller une table pendant de nombreuses heures.

Au lieu de cela, nous pouvons essayer quelques astuces dans la table du moteur INNODB. Certaines des astuces sont répertoriées ci-dessous:

1. Parfois, l’optimisation d’une valeur incorrecte peut entraîner une fragmentation avec des index secondaires. Il est donc important d’analyser comment tirer davantage parti du compactage d’une valeur particulière. Cela signifie que l’identification de la valeur correcte pour l’optimisation est très importante.
2. Une autre méthode consiste à supprimer l’index, à optimiser les tables et à rajouter les index. Cette méthode n’est applicable que si la table peut fonctionner sans index pendant une courte période.

Optimiser les tables dans MySQL

Premièrement, nous devons analyser les tables que nous voulons optimiser. Nous devons être connectés à notre base de données à l’aide de la commande suivante.

Exemple de code :

-- Syntax: Use your_database_name;
mysql> USE test;

Une fois connecté à la base de données souhaitée, utilisez la requête suivante pour obtenir le statut de la table.

Exemple de code :

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

PRODUCTION:

Nous avons deux propriétés importantes pour savoir si nous devons ou non optimiser cette table.

1. Le Data_length parle de l’espace occupé par une base de données.
2. Le Data_free indique les octets alloués mais inutilisés dans une table de base de données.

Ces informations nous guident pour identifier quelle table doit être optimisée et quelle quantité d’espace nous allons récupérer par la suite.

Nous pouvons obtenir ces deux nombres (Data_length et Data_free) pour toutes les tables d’une base de données particulière en utilisant la requête suivante. Actuellement, nous n’avons qu’une seule table nommée test_table dans la base de données test.

Exemple de code :

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

PRODUCTION:

La requête ci-dessus imprime les noms de la table, l’espace total en octets et l’espace inutilisé alloué en octets. Si vous êtes à l’aise avec le travail en mégaoctets, vous pouvez utiliser la requête suivante pour obtenir une sortie en mégaoctets.

Exemple de code :

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

Bien que l’exemple de table donné ne soit pas fortement fragmenté, nous pouvons récupérer de l’espace à l’aide de la commande OPTIMIZE TABLE.

Nous apprendrons à optimiser des tables ou des bases de données simples/multiples à l’aide du shell et du terminal MySQL.

Utiliser MySQL Shell dans le système d’exploitation Windows/Ubuntu 20.04 pour optimiser une ou plusieurs tables

Exemple de code :

mysql> OPTIMIZE TABLE test_table;

PRODUCTION:

Pour optimiser plusieurs tables, utilisez la requête suivante.

Exemple de code :

mysql> OPTIMIZE TABLE tableName1, tableName2;

Maintenant, utilisez la commande suivante pour confirmer que la table souhaitée est optimisée.

Exemple de code :

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

PRODUCTION:

Les mêmes requêtes fonctionneront sur le shell MySQL dans le système d’exploitation Linux (Ubuntu 20.04).

Utiliser le terminal Windows pour optimiser le tableau

Exemple de code :

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

Une fois que nous écrivons la commande donnée ci-dessus et que nous appuyons sur Enter, il nous sera demandé d’entrer le mot de passe root MySQL. Entrez simplement cela.

Il faut être dans le dossier bin pour exécuter la requête (voir l’exemple suivant).

Exemple de code :

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

Utiliser le terminal Ubuntu 20.04 pour optimiser le tableau

Si nous sommes connectés en tant que superuser à l’aide de la commande sudo su, exécutez la requête comme indiqué ci-dessous. Il ne vous demandera qu’un mot de passe root MySQL.

Exemple de code :

mysqlcheck -o test test_table -u root -p

Si nous ne sommes pas connectés en tant que superutilisateur, nous exécutons la commande mysqlcheck. Ici, il nous sera demandé d’entrer le mot de passe root du système et le mot de passe root MySQL.

Exemple de code :

sudo mysqlcheck -o test test_table -u root -p

Optimiser toutes les tables dans une seule base de données MySQL

Optimisez toutes les tables à l’aide de la ligne de commande Windows :

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

Optimisez toutes les tables à l’aide du terminal Ubuntu :

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

Optimiser toutes les bases de données dans MySQL

Optimisez toutes les bases de données à l’aide de la ligne de commande Windows :

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

Optimisez toutes les bases de données à l’aide du terminal Ubuntu :

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p