Lors de la suppression massive de données d'une table volumineuse (occupant initialement plus de 400 Mo), il est courant de constater que l'espcae disque n'est pas libéré même après avoir vidé les enregistrements. Voici comment vérifier l'utilisation de l'espace :

-- Vérifier l'occupation par base de données
SELECT db_name AS TABLE_SCHEMA,
       CONCAT(FORMAT(SUM(data_length)/1024/1024, 2), ' Mo') AS taille_données,
       CONCAT(FORMAT(SUM(index_length)/1024/1024, 2), ' Mo') AS taille_index
FROM information_schema.tables
GROUP BY db_name
ORDER BY SUM(data_length) DESC;

-- Examiner l'occupation des tables dans une base spécifique
SELECT table_nom AS TABLE_NAME,
       nb_lignes AS TABLE_ROWS,
       CONCAT(FORMAT(data_length/1024/1024, 2), ' Mo') AS taille_données,
       CONCAT(FORMAT(index_length/1024/1024, 2), ' Mo') AS taille_index
FROM information_schema.tables
WHERE db_name = 'ma_base'
GROUP BY table_nom, nb_lignes, data_length, index_length
ORDER BY data_length DESC;

Pour récupérer l'espace non utilisé, exécutez l'opération suivanet :

OPTIMIZE TABLE ma_base.ma_table;

L'OPTIMIZE TABLE est recommandé après une suppression importante ou des modifications fréquentes sur des tables avec colonnes de longueur variable (VARCHAR, BLOB, TEXT). Cette commande réorganise les données, libère l'espace fragmenté et réutilise les emplacements inoccupés. Notez qu'elle verrouille la table pendant l'exécution et s'applique principalement aux moteurs MyISAM, BDB et InnoDB. Dans la plupart des cas, une exécution hebdomadaire ou mensuelle suffit.

La base information_schema fournit un accès aux métadonnées du système MySQL. Ses tables incluent :

• SCHEMATA : répertorie toutes les bases de données disponibles.
• TABLES : contient des détails sur les tables, telles que leur moteur et leur taille.
• COLUMNS : décrit les colonnes de chaque table.
• STATISTICS : référence les index des tables.
• USER_PRIVILEGES : affiche les privilèges globaux des utilisateurs.
• CHARACTER_SETS : liste les jeux de caractères supportés.
• INNODB_TRX : fournit des informations sur les transactions InnoDB.

La table TABLES offre des colonnes cruciales pour l'analyse :

DESCRIBE information_schema.tables;

Parmi ces colonnes, notons TABLE_SCHEMA (nom de la base), TABLE_NAME (nom de la table), DATA_LENGTH (espace utilisé par les données en octets) et INDEX_LENGTH (taille des index). Une requête simple pour explorer ces métadonnées :

SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
LIMIT 10;