Présentation des principaux moteurs MySQL, avec leur avantages et leurs inconvénients.

InnoDB

• Gère les transactions

Vous pouvez démarrer une transaction via les opérations BEGIN et END, puis faire des COMMIT et des ROLLBACK.

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

L’intégrité entre les clés étrangères est gérée, les contraintes peuvent être définies à la construction de la table.

CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_structure INT,
    ...
    FOREIGN KEY(id_structure) REFERENCES structure(id)
);

• Accès concurrents avancés

Les locks se font sur les lignes plutôt que sur la table complète.

• Occupe plus d’espace disque
• Optimisation des fichiers

Afin d’améliorer les performances, il est de bon augure de disperser les tables sur plusieurs disques physiques différents.

MyISAM

Quelques spécificités des tables enregistrées au format myISAM :

• Les INSERT sont en moyenne 20% plus rapides que sur des tables InnoDB.
• Des index plus sophistiqués

Les index peuvent être placés sur des champs de type BLOB, TEXT. MyISAM permet également les index de type fulltext

• Perte du contrôle d’intégrité.

Alors que les tables InnoDB sont bien protégées contre la corruption des données, les tables MyISAM sont souvent corrompues et nécessitent un REPAIR TABLE. On peut cependant améliorer cet état, en lançant périodiquement une requête de vérification de la table via mysqlcheck :

mysqlcheck [options] db_name [tables]

Ou mieux, déclarer un contrôle checksum pour chaque nouvelle insertion ou update sur la table :

CREATE TABLE employee (
...
) ENGINE=MYISAM CHECKSUM=1;

Cela fait perdre environ 5% de rapidité aux requêtes, mais peut faire gagner, en fin de compte, un temps infini, le jour du crash.

Memory

• Les tables résident en mémoire

Cela signifie que si le serveur est coupé uo redémarré pour une raison x ou y, les tables disparaissent de la circulation. Ce genre de table est utile pour des données qui ne nécessitent pas d’archivage, et dont les tables sont très sollicitées.

• Accès à très grande vitesse : 50% plus rapide que InnoDB, 33% plus rapide que MyISAM.

Pas d’accès disque, cela nécessite également beaucoup de mémoire RAM disponible.

CREATE TABLE (
...
) ENGINE=MEMORY;

Merge

Ce moteur de stockage un peu particulier peut présenter de grands avantages pour certains cas d’utilisations. Admettons qu’on gère des données dans le temps. Généralement, on cherchera dans les dernière données, mais il peut arriver qu’on veuille également chercher sur des périodes beaucoup plus longues. Avec ce type de table, on peut choisir d’effectuer la requête sur une petite partie, ou sur un ensemble de tables (qui ont la même structure).

• Ne regroupe que des tables de type MyISAM.

Avec les avantages et les inconvénients que cela suppose.

Voici un exemple inspiré de la doc MySQL :

CREATE TABLE total (
  id INT AUTO_INCREMENT PRIMARY KEY,
  date DATE NOT NULL,
  message CHAR(20),
) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Les tables t1 et t2 doivent exister et avoir la même structure définie par la table total.