Tables personnalisées WordPress : MyISAM vs InnoDB vs contraintes d’intégrité référentielle

Lorsqu'on développe nos propres fonctionnalités WordPress, il est intéressant d'ajouter des tables personnalisées dans la base de données WordPress. Mais avant de se lancer tête baissée, il y a des considérations techniques à prendre en compte lorsqu'on ajoute des tables à une base de données, peu importe si ces tables sont destinées à un site WordPress ou à un autre programme.

Une des considérations importantes est le moteur de stockage. En gros, le moteur de stockage est constitué des algorithmes qui permettent au système de gestion de bases de données de stocker et de récupérer les informations à partir de requêtes SQL.

▼Publicité

Nous n'entrerons pas ici dans les avantages et inconvénients de chacun des moteurs de stockage. Mentionnons simplement que les plus connus sont MyISAM, qui consitue généralement le choix par défaut du serveur MySQL, et InnoDB, qui est intéressant notamment pour gérer les contraintes d'intégrité référentielle.

Lors de la création d'une table, il est possible de spécifier le moteur de stockage à utiliser à l'aide de la commande suivante :

MySQL

CREATE TABLE ... (

    ...

) ENGINE=InnoDB;

Quel est le moteur de stockage privilégié par WordPress ?

Lors de la création de tables, WordPress ne spécifie pas quel moteur de stockage utiliser. Il utilise le moteur par défaut du serveur MySQL.

Ainsi, si vos tables sont créées avec le moteur MyISAM, c'est que votre serveur MySQL crée par défaut des tables MyISAM.

À titre indicatif, voici un extrait du code que WordPress utilise pour créer une table lors de l'installation initiale. Vous pouvez trouver le code complet dans le fichier wp-admin/includes/schema.php.

Extrait du fichier WordPress wp-admin/includes/schema.php (PHP)

$blog_tables = "CREATE TABLE $wpdb->terms (

 term_id bigint(20) unsigned NOT NULL auto_increment,

 name varchar(200) NOT NULL default '',

 slug varchar(200) NOT NULL default '',

 term_group bigint(10) NOT NULL default 0,

 PRIMARY KEY  (term_id),

 KEY slug (slug),

 KEY name (name)

) $charset_collate;

CREATE TABLE ... (

 ... 

) $charset_collate;\n";

On y voit bien que le moteur n'est pas spécifié puisqu'on ne retrouve pas le mot-clé ENGINE dans les CREATE TABLE. Il y a bien l'utilisation de la variable $charset_collate à la fin des CREATE TABLE mais cette variable contient de l'information du genre « DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ». Aucune référence au moteur de stockage.

Pourquoi ne pas forcer l'utilisation d'un moteur de stockage ?

Une des raisons qui justifie le choix de WordPress de ne pas forcer l'utilisation d'un moteur par rapport à un autre est la polyvalence. En effet, certains hébergeurs ne supportent que MyISAM alors les sites WordPress ne pourraient pas y être hébergés si l'utilisation de InnoDB était obligatoire.

Quel est l'impact sur notre site WordPress lorsqu'on utilise MyISAM plutôt que InnoDB ?

Les performances ne sont pas automatiquement meilleures avec MyISAM ni avec InnoDB. Tout dépend du contexte. Généralement, si une application fait principalement des lectures dans la base de données, MyISAM sera plus rapide. Lorsque l'application doit écrire dans les tables, InnoDB offre souvent de meilleures performances.

La taille de la base de données peut modifier les performances de l'un ou l'autre de ces moteurs. Les configurations du serveur également. Il est donc difficile à prime abord de déterminer quel moteur sera le plus rapide.

Nous allons donc regarder d'autres aspects qui pourraient varier selon le moteur de stockage utilisé.

  • Avec MyISAM, il n'y a pas de contraintes d'intégrité référentielle. Il pourrait donc arriver qu'une table contienne une clé étrangère dont la valeur n'existe pas comme clé primaire dans la table référencée.
  • Avec MyISAM, il n'y a pas de gestion de transactions. Il n'est donc pas possible d'effectuer un bloc de requêtes SQL en lot tout en en assurant que si l'une d'entre elles faisait défaut, les requêtes seraient défaites pour que la base de données revienne dans son état original. Ainsi, avec MyISAM, si un bloc de requêtes SQL ne pouvait pas être entièrement réalisé, les données pourraient se retrouver corrompues. L'intégrité des données sera ici aussi compromise.

Il est possible de monter un site WordPress avec des tables MyISAM. La majorité des sites WordPress est dans cette situation. Mais si vous avez une préoccupation pour l'intégrité des données, le moteur de stockage InnoDB est de loin préférable.

Comment transformer nos tables existantes pour qu'elles utilisent InnoDB ?

Tout d'abord, si vous désirez convertir vos tables pour qu'elles utilisent le moteur InnoDB, prenez le temps d'effectuer quelques opérations de préparation.

Copie de sécurité

La première étape consiste, comme toujours, à générer un bon script SQL qui permettra de recréer la base de données dans son état original en cas de besoin.

Index de type texte complet

Si certaines tables utilisent des index de type texte complet (FULLTEXT), il faudra d'abord détruire ces index. Ils ne seront recréés manuellement que lorsque la table sera convertie à InnoDB.

On peut utiliser la base de données information_schema pour retrouver la liste des index de type texte complet.

Ex :

MySQL

SELECT table_name, index_name

FROM information_schema.statistics

WHERE table_schema = 'ma_bd_wordpress'

AND index_type LIKE 'FULLTEXT%'

On pourra ensuite détruire les index trouvés :

MySQL

ALTER TABLE nom_table DROP INDEX nom_index;

Configurations d'optimisation

Pour optimiser les performances du site, il pourrait arriver que certaines configurations soient nécessaires, notamment au niveau de la gestion de la mémoire.

Ex :

Fichier my.ini

innodb_buffer_pool_size=...

Conversion des tables

Une fois ces opérations réalisées, il est possible de convertir les tables à l'aide de la commande :

MySQL

ALTER TABLE nom_table ENGINE InnoDB;

Cette commande devra être utilisée pour chacune des tables.

Note : il est possible de tirer profit des tables de métadonnées afin de générer la liste de commandes qui permettront de changer le moteur pour chacune des tables de la BD1 :

MySQL

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;')

FROM Information_schema.TABLES

WHERE TABLE_SCHEMA = 'ma_bd_wordpress'

AND ENGINE = 'MyISAM'

AND TABLE_TYPE = 'BASE TABLE';

Comment créer de nouvelles tables utilisant InnoDB ?

Lorsque vous créerez vos tables personnalisées, vous devrez effectuer quelques opérations pour assurer que les nouvelles tables utilisent le même moteur que le reste de la base de données. Ainsi, si vous avez pris les précautions pour tout transformer votre base de données en InnoDB, vos nouvelles tables utiliseront également InnoDB. Par contre, si votre code fait partie d'une extension que vous distribuez, les nouvelles tables utiliseront le même moteur que le reste de la base de données.

Je vous propose ici un algorithme qui se chargera de retrouver le moteur de stockage de la table wp_posts et qui utilisera ce moteur pour les tables que vous aller créer.

WordPress (PHP)

// retrouver le moteur de stockage utilisé par les tables WordPress

$requete = "SELECT engine FROM information_schema.tables WHERE table_schema = '$wpdb->dbname' AND table_name = '$wpdb->posts'";

$nom_moteur = $wpdb->get_var( $requete );

 

if ( is_null( $nom_moteur ) ) {

    $nom_moteur = "MyISAM";// moteur par défaut si on ne réussit pas à le retrouver

}

$engine = "ENGINE $nom_moteur";

 

// retrouver le jeu de caractères et l'interclassement

$charset_collate = $wpdb->get_charset_collate();   // chaine au format DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

 

$table_matable = $wpdb->prefix . 'monprefixebd_matable';

$sql = "CREATE TABLE $table_matable (

    ...

) $engine $charset_collate;";

 

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

dbDelta( $sql );

Attention : vous n'avez aucune garantie que les tables créées par un nouveau thème ou une nouvelle extension utiliseront le même moteur de stockage. À vous d'effectuer les vérifications qui s'imposent lorsque vous installez un thème ou une extension qui crée de nouvelles tables.

Comment créer des contraintes d'intégrité référentielles ?

Les instructions de contraintes d'intégrité référentielles ne devront être exécutées que si la table utilise le moteur InnoDB. De plus, elles doivent être exécutées après que les deux tables impliquées soient créées.

WordPress (PHP)

// création des contraintes seulement si InnoDB et si contrainte pas déjà créée

if ( strtoupper( $nom_moteur ) == 'INNODB' ) {

    $requete = "SELECT COUNT(*) FROM information_schema.key_column_usage WHERE table_name = '$table_matable' AND column_name = 'champ1' AND referenced_table_name = '$table_autretable' AND referenced_column_name = 'champ2' AND table_schema = '$wpdb->dbname'";

    $presence_contrainte = $wpdb->get_var( $requete );

 

    if ( is_null( $presence_contrainte ) ) {

        $presence_contrainte = 0;    // valeur si on ne réussissait pas à retrouver l'info dans la BD

    }

   

    if ( ! $presence_contrainte ) {

        $requete = "ALTER TABLE $table_matable ADD CONSTRAINT FOREIGN KEY (champ1) REFERENCES $table_autretable (champ2)";

 

        if ( ! $wpdb->query( $requete ) ) {

            // réagir en cas de problème

            monprefixe_log_me( $wpdb->last_error );

            // ...

        }

    }

}  

Source

1. « Convert MyISAM to InnoDB ». rtCamp Solutions. https://rtcamp.com/tutorials/mysql/myisam-to-innodb/

Pour plus d'information

« Les moteurs de stockage de MySQL ». Open Classrooms. https://openclassrooms.com/courses/les-moteurs-de-stockage-de-mysql-2

« Convert MyISAM to InnoDB ». rtCamp Solutions. https://rtcamp.com/tutorials/mysql/myisam-to-innodb/

« What are the main differences between InnoDB and MyISAM? ». Stack Exchange. http://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194

« Which is faster, InnoDB or MyISAM? ». Stack Exchange. http://dba.stackexchange.com/questions/17431/which-is-faster-innodb-or-myisam?lq=1

« Chapter 15 Alternative Storage Engines ». MySQL. https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

« WordPress Performance Tuning ». Store Locator Plus. http://www.storelocatorplus.com/wordpress-performance-tuning/

« 14.2.2 InnoDB Startup Options and System Variables - innodb_buffer_pool_size ». MySQL. https://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

Merci de partager ! Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInPin on PinterestShare on StumbleUponEmail this to someone
Catégories