Les moteurs de stockage de MySQL

Introduction du cours

MySQL est souvent décrié par de nombreux développeurs comme étant lent, ne gérant pas les relations ou les transactions, et est souvent considéré à tort comme un mauvais outil.
Mais comme tout outil, il faut savoir le manipuler pour en tirer réellement profit.

Nous allons découvrir ici quelques facettes de MySQL assez peu connues des débutants et qui peuvent dans certains cas nous changer la vie.

Ce tutoriel ne parle pas de la version Cluster de MySQL en raison de sa complexité et n'abordera donc pas le moteur de stockage NDBCluster qui lui est associé.
La documentation MySQL pourra vous renseigner largement sur ce moteur

MySQL et ses drôles de moteurs

On appelle moteur de stockage l'ensemble des algorithmes utilisés par un SGBDR pour stocker les informations et y accéder au moyen d'une requête SQL.

La plupart des SGBDR proposent un moteur unique, créé pour être le plus efficace possible dans tous les cas. MySQL et ses forks (dont les plus connus sont MariaDB et Drizzle) se démarquent de leurs homologues en proposant à l'administrateur de la base de choisir pour chaque table de sa base quel moteur il désire utiliser.
On se retrouve ainsi avec des bases où plusieurs moteurs peuvent coexister. C'est un choix de conception qui a ses avantages et inconvénients.
Le développeur est libre de choisir la manière dont ses données sont stockées, mais en revanche, s'il ne fait pas attention à ce qu'il fait, on peut très vite perdre pied sur de grosses bases de données et ne plus savoir au final quelle table utilise quel moteur.

Actuellement, MySQL dispose de nombreux moteurs, avec chacun une utilité particulière et des cas spécifiques d'utilisation.
Voici les principaux :

• MyISAM

• InnoDB

• MEMORY (anciennement HEAP)

• MERGE

• BLACKHOLE

• BerkeleyDB ou BDB

• ARCHIVE

• CSV

• FEDERATED

Trois autres moteurs existent mais sont soit inutilisables, soit très complexes à appréhender et sortent du cadre de ce tutoriel.

• Il s'agit pour le premier de Isam, moteur historique de MySQL 1.0 , et qui a été remplacé par MyISAM à partir de la version 3.23.0.
  Depuis MySQL 5.0, Isam n'est même plus inclus dans le code source de MySQL.

• Le second moteur est NDBCluster, qui est le type spécifique de la version clusterisée de MySQL. Comme précisé dans l'introduction, pour ne pas trop compliquer ce tutoriel je n'aborderai pas la MySQL Cluster et je vous renvois donc à la documentation de la version Cluster de MySQL pour de plus amples informations à ce sujet.

• Enfin, le moteur inutilisable est EXAMPLE, dont l'unique but est de permettre aux développeurs d'avoir un exemple de structure de code pour créer leur propre moteur de stockage.

De plus, par la nature modulaire des moteurs de stockages dans MySQL, des moteurs non officiels existent et peuvent être ajoutés à ceux présents de base comme s'il s'agissait de plug-ins.
C'est le cas par exemple de Percona-XtraDB. Ce moteur est l'évolution non officielle d'InnoDB et est utilisé comme son remplaçant dans MariaDB.
On citera aussi PBXT, OQGRAPH ou Aria (moteur par défaut de MariaDB).

Le mot clé ENGINE

Non, je ne parle pas ici d'un gros mal de gorge mais du mot anglais signifiant "moteur". :p

Voyons déjà quels sont les moteurs que MySQL met à notre disposition, ceci au moyen de la commande SHOW ENGINES;.
Cette commande nous affiche la liste complète des moteurs disponibles, une colonne Support nous signale si le moteur est activé dans MySQL (YES/NO), une colonne Comment nous renseigne sur les spécificités du moteur. Les versions les plus récentes de MySQL précisent aussi une colonne Transactions (YES/NO) si le moteur permet de gérer les transactions.

mysql> SHOW ENGINES; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | | MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | | BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | | CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         | | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | | FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | | ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         | | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 8 rows in set (0.00 sec)

On voir dans l'exemple ci-dessus que FEDERATED est présent mais pas actif, qu'InnoDB est le seul moteur présent à gérer les transactions et que MyISAM est le moteur défini par défaut.
Maintenant que nous connaissons les moteurs disponibles sur notre système, nous allons pouvoir utiliser ceux qui nous interessent.

Les moteurs de stockage se spécifient à l'aide du mot clé ENGINE=xxxxx soit au moment de créer la table, soit à l'aide d'un ALTER TABLE :

/* A la création de la table */ CREATE TABLE maTable( ... )ENGINE=MonMoteurDeStockage; /* En modifiant une table déjà créée */ ALTER TABLE maTable ENGINE=UnAutreMoteur;

Pour des raisons de compatibilité avec les anciennes versions de MySQL, on rencontre parfois le mot clé TYPE à la place de ENGINE.

Parfois, certaines moteurs utilisent d'autres mots clés pour spécifier des paramètres supplémentaires, comme COMMENT. Ces cas seront signalés plus tard dans le descriptif de chaque moteur.

Et faut le faire pour chaque table ou on peut dire à MySQL qu'on veut toujours utiliser un moteur en particulier par défaut ?

Il est possible de définir un moteur par défaut pour les nouvelles tables en le spécifiant dans le fichier de configuration de MySQL. Soit définitivement, soit pour la session active seulement.
Pour la session active seulement, il faudra utiliser

SET storage_engine=NomDuMoteur;

Pour le faire définitivement, cela se fait au moyen de la directive suivante du fichier de configuration :

[mysqld] default-storage-engine = NomDuMoteur

• Pour les versions inférieures à MySQL 5.5 le moteur par défaut est MyISAM.

• Pour les versions supérieures ou égales à 5.5, le moteur par défaut est InnoDB.

Deux grandes familles de moteurs

Comme nous l'a montré la commande SHOW ENGINES, certains moteurs gèrent les transactions là où d'autres ne le font pas.

Les moteurs transactionnels sont plus surs que les moteurs non transactionnels, car ils assurent qu'une opération s'est exécutée du début à la fin sans être interrompue, et permettent d'annuler l'opération entière au cas où un incident serait survenu.

Les moteurs transactionnels offrent les sécurités suivantes :

• Si un problème matériel ou électrique survient pendant une opération et que celle-ci ne peut se terminer, les anciennes données sont récupérables et ne sont pas corrompues avec des fragments de nouvelles données.

• Vous pouvez grouper les instructions exécutées par MySQL. Il est plus simple pour lui d'effectuer plusieurs opérations identiques à la suite que d'effectuer de nombreuses opérations différentes.

• Si une mise à jour échoue, les changements sont annulés

• Les moteurs transactionnels obtiennent de meilleures performances pour les accès concurrents en lecture

Les moteurs non transactionnels offrent en contrepartie de meilleures performances, car ils ne sont pas soumis à des vérifications nombreuses

• Plus rapides

• Moins de place utilisée sur le disque

• Moins de mémoire consommée

Je vous renvois au tutoriel de Tortue Facile sur les transactions si vous voulez plus d'informations.

Même s'il est possible de le faire, il est très fortement déconseillé de mélanger des tables à moteurs transactionnels et des tables à moteurs non transactionnels au sein d'une même transaction.
Les tables non transactionnelles ne vont pas le devenir par magie. Si l'opération échoue, seules les tables transactionnelles pourront revenir à un état précédent, les tables non transactionnelles seront corrompues avec des données incohérentes sans possibilité de retour en arrière.

MyISAMPrésentation de MyISAM

MyISAM est disponible dans toutes les versions de MySQL à partir de la 3.23.0.
Pour les versions antérieures à la 5.5, c'est le moteur par défaut de MySQL.

Il s'agit d'un moteur non transactionnel assez rapide en écriture et très rapide en lecture. Ceci vient en grande partie du fait qu'il ne gère pas les relations ni les transactions et évite donc des contrôles gourmands en ressources mais perd en sûreté ce qu'il gagne en vitesse.
Il gère l'indexation des contenus, accélérant les recherches, et il est le seul moteur de MySQL permettant de créer des index FULLTEXT sur les champs de type TEXT, rendant les recherches beaucoup plus efficaces qu'avec LIKE %.
Ceci en fait le moteur de prédilection pour les fonctions de recherche avancées.

De plus, MyISAM garde en cache des métadonnées sur la table et ses index, comme le nombre de lignes, la taille perdue à cause de la fragmentation, etc.
Ainsi, une requete SELECT COUNT(*) FROM maTable; sera extrêmement rapide avec MyISAM car le résultat est déjà obtenu à l'avance.

Ceci ne fonctionne que sur les COUNT() opérant sur la table entière (pas de WHERE, LIMIT ou autres restrictions)

Il est conseillé pour les tables MyISAM n'ayant pas besoin de recherches FULLTEXT d'utiliser des champs de taille fixe (utilisation de CHAR au lieu de VARCHAR, pas de BLOB ou de TEXT) afin d'améliorer la vitesse de lecture.
Ce gain, pouvant aller jusqu'à 30% de gain de performances dans des cas particuliers (attendez vous plutôt à un gain de 15% en moyenne), est contrebalancé par une taille plus importante occupée sur le disque dur.
De plus, il n'est sensible que sur des tables contenant de TRES NOMBREUX enregistrements (plusieurs millions) ou comportant de nombreuses colonnes.

Si une table comporte des champs CHAR et des champs VARCHAR, les champs CHAR seront automatiquement convertis en VARCHAR si leur taille est supérieure à CHAR(3).
Si une table contient des VARCHAR d'une taille inférieure à VARCHAR(3), ils seront automatiquement convertis en CHAR. La première règle de conversion surpasse celle-ci, si au moins un des champs VARCHAR de la table est supérieur à VARCHAR(3) les autres champs VARCHAR ne sont pas convertis.
Ceci vient du fait que les champs VARCHAR comportent des informations supplémentaires pour déterminer la taille du champ, qui sont stockées sur 2 octets. Dans le cas où un VARCHAR est inférieur à 3 octets, le CHAR est plus économique car il ne comporte pas ces informations.

La table possède un verrouillage en lecture/écriture au niveau de la table entière, ce qui fait que lorsqu'un utilisateur est en train d'écrire dans la table, les personnes essayant d'y accéder en lecture doivent attendre que l'écriture soit finie, et ce même si elles veulent lire une ligne autre que celle qui est en train d'être écrite.

Exemple d'utilisation

Voici l'exemple d'une base de données où nous désirons enregistrer des informations sur des personne qui pourront se connecter à notre application.
Nous créons donc une table personne qui contiendra les informations sur cette personne.

CREATE TABLE Personne( Personne_id BIGINT NOT NULL PRIMARY KEY auto_increment, Personne_username CHAR(100) UNIQUE NOT NULL, Personne_password CHAR(40) NOT NULL, Personne_nom CHAR(100) NOT NULL, Personne_prenom CHAR(100) NOT NULL, Personne_adresse CHAR(200) NOT NULL, Personne_codepostal CHAR(5) NOT NULL, Personne_email CHAR(200) NOT NULL, Personne_ville CHAR(100) NOT NULL, INDEX('Personne_nom', 'Personne_prenom') )ENGINE=MyISAM;

Ici, un exemple sur les index FULLTEXT

/* on crée une table contenant des news */ CREATE TABLE news( news_id bigint not null primary key auto_increment, news_titre varchar(200), news_texte TEXT, FULLTEXT(news_titre, news_text) )ENGINE=MyISAM; /* pour effectuer une recherche sur un mot on utilse la commande suivante */ SELECT * FROM news WHERE MATCH (news_titre, news_texte) AGAINST ('database'); /* ceci nous renverra tous les résultats où le mot 'database' apparait au moins une fois soit dans le titre, soit dans le corps du texte. */

Des modificateurs permettent d'effectuer des recherches plus complexes par associations de termes. Voir documentation MySQL sur la recherche Fulltext pour plus d'informations

Avantages et inconvénients

Avantages :

• Très rapide en lecture