Maintenance des tables d'une base de données MySQL

Les tables d'une base de données MySQL impliquent nécessairement une fragmentation des fichiers à l'utilisation (suppressions/ajouts d'entrées, ou modifications de champs dynamique VARCHAR, BLOB ou TEXT). 

Les lignes effacées sont, par exemple, conservées dans une liste et les prochaines opérations d'INSERT réutilisent les vieilles positions de lignes, entraînant des espaces de "vide" dans les fichiers de table.

Plus les tables sont fragmentées et plus les temps d'accès aux informations peuvent en être impactés.

Afin de récupérer ces espaces et d'optimiser le contenu des tables, il est nécessaire d'effectuer des opérations de maintenance, en fonction de leur fragmentation.

Pour se faire, il existe plusieurs solutions, dont l'utilisation de l'interface phpMyAdmin qui propose, depuis la fenêtre de sélection des tables, d'effectuer ces opérations de maintenance. Cette solution implique évidemment d'avoir, au préalable, installé l'outil sur votre serveur.

Autrement, je vous propose un petit script PHP très simple vous permettant de lancer des opérations de maintenance de base (analyze, check... optimize) sur vos tables de manière automatique (ne s'applique qu'aux moteurs de table MyISAM).

<?php
/*
Syntaxe:
web: http://votre-url/script.php?pass=password&action=idaction [&option=all]
shell: php script.php password idaction [all]


idaction={

• "1" ou "analyze" : (analyse et stocke les clés de distribution)
• "2" ou "check" : (vérifie l'intégrité des tables)
• "3" ou "checksum" : (calcule la somme de contrôle des tables)
• "4" ou "repair" (répare les tables corrompues)
• "5" ou "optimize" (défragmente, trie et met à jour les stats)
• "all" : effectue toutes les opérations

}

l'ajout du paramètre supplémentaire "all" permet de forcer l'opération sur toutes les tables (sinon uniquement les tables avec perte).

Source: http://y-thot.blogspot.com/

*/

//variables à définir
$script_password="password";


$mysql_host="localhost";
$mysql_user="root";
$mysql_pass="";
$mysql_bdd="votrebasededonnee"; //base de donnée contenant les tables

echo "Script d'optimisation des tables: lancement $mode_cli";

if ( $pass != $script_password ) die("Done. $mode_cli"); //message de quit à personnaliser

if (is_array($argv)) { //accès par shell php
//cli: "php /etc/www/optimisealltables.php pass action option"
    if ($argv[1]) $pass = $argv[1];
    else $pass="";
    if ($argv[2]) $action = $argv[2];
    else $action="5";
    if ($argv[3] == "all" ) $query = "SHOW TABLE STATUS";
    else $query = "SHOW TABLE STATUS WHERE Data_free >0";
   
    $mode_cli="\n";
}
else { //accès par apache
    if ( $_GET['pass'] ) $pass = $_GET['pass'];
    else $pass = "";
    if ( $_GET['action'] ) $action=$_GET['action'];
    else $action = "5";
    if ( $_GET['option'] == "all" ) $query = "SHOW TABLE STATUS";
    else $query = "SHOW TABLE STATUS WHERE Data_free >0";
    $mode_cli="<br />";
   ini_set ('max_execution_time', 60*30); //30 minutes max d'exécution du script, mettre à 0 si pas de limite
}
  
if ( mysql_connect($mysql_host,$mysql_user,$mysql_pass) ) echo "Connexion à la base $mode_cli";
else die("Echec de la connexion");

if ( mysql_select_db($mysql_bdd) ) echo "Sélection de la bdd $mode_cli";
else die("Echec du choix de la bdd");

$result = mysql_query($query) or die("error query");
while($line = mysql_fetch_array($result)) {
    if ( $line[1] != "MyISAM" ) continue;
   
    if (( $action == "all" ) || ( $action == "analyze" ) || ( $action == "1" )) {
        echo "Analyze de la table ".$line[0]." : ";
        mysql_query("ANALYZE TABLE `".$line[0]."`"); //analyse et stocke les clés de distribution
    }
    if (( $action == "all" ) || ( $action == "check" ) || ( $action == "2" )) {
        echo "Check de la table ".$line[0]." : ";
        mysql_query("CHECK TABLE `".$line[0]."` EXTENDED"); //verifie intégrité des tables
    }
    if (( $action == "all" ) || ( $action == "checksum" ) || ( $action == "3" )) {
        echo "Checksum de la table ".$line[0]." : ";
        mysql_query("CHECKSUM TABLE `".$line[0]."` EXTENDED"); //calcule la somme de contrôle des tables
    }
    if (( $action == "all" ) || ( $action == "repair" ) || ( $action == "4" )) {
        echo "Repair de la table ".$line[0]." : ";
        mysql_query("REPAIR TABLE `".$line[0]."` EXTENDED"); //répare les tables corrompues
    }
    if (( $action == "all" ) || ( $action == "optimize" ) || ( $action == "5" )) {
        echo "Optimisation de la table ".$line[0]." : "; //défragmente, trie et met à jour les stats
        mysql_query("OPTIMIZE TABLE `".$line[0]."`");
    }

    echo "effectué. $mode_cli";
}

echo "Script d'optimisation des tables: terminé $mode_cli";
?>

La récupération des variables d'entrée permet d'utiliser le script soit à distance (méthode GET, Apache installé), soit de lancer directement le script en local sur votre serveur (linux ou windows) par la commande "php" (debian: package "php-cli" à installer). Cette seconde méthode servira, en outre, à pouvoir automatiser cette fonction de manière autonome (voir post Backup automatique de serveur LAMP).

Bref, à configurer/modifier/optimiser/personnaliser selon vos besoins.