Les différents moteurs de stockage de MySQL

InnoDB, est le moteur transactionnel le plus utilisé à l'heure actuelle dans les secteurs dit sensibles, c'est-à-dire nécessitant une cohérence et une grande intégrité des données.
Jusqu'à la version 5.1 incluse, c'est le seul moteur supportant les contraintes de clés étrangères (intégrité référentielle).

Il n'est pas concevable d'avoir des informations faisant référence à quelque chose d'inexistant. Peut-on imaginer un numéro de sécurité sociale qui ne soit pas associé à une personne ou un code postal associé à aucune ville ?
Il y a des domaines d'application où les données doivent être fiables à 100%.

Au-delà de l'intégrité référentielle, InnoDB propose des mécanismes transactionnelles présentant une grande compatibilité aux critères ACID.

Avec une base de données composée de tables utilisant le moteur InnoDB, il est important de ne pas utiliser les mêmes méthodes qu'avec une base contenant uniquement des tables MyISAM.
Avec les tables utilisant le moteur MyISAM, il est facile de copier, supprimer une base de données : il suffit de copier le répertoire se trouvant dans le répertoire /Data/ portant le même nom que la base de données.
De là, il est possible de le déplacer vers un autre serveur, de réaliser une autre base de donnés à partir de celle-ci, d'effectuer des sauvegardes.
Par contre, si la base de données comporte des tables utilisant le moteur InnoDB, il faudra faire plus attention.
En effet, toutes les données de toutes les tables de toutes les bases sont stockées dans un espace de tables commun. De ce fait, la base devient un peu plus rigide.

CREATE TABLE t_user (
    iduser  int(20) NOT NULL auto_increment,
    idgroup int(10) NOT NULL,
    name    varchar(32) NOT NULL default '',    
    PRIMARY KEY  (id)
    KEY idgroup
)
ENGINE=MyISAM;

CREATE TABLE t_group (
    idgroup int(10) NOT NULL auto_increment,
    name    varchar(32) NOT NULL default '',    
    PRIMARY KEY  (id)    
)
ENGINE=MyISAM;

INSERT INTO t_group (name) VALUES ('invité');
INSERT INTO t_user (idgroup, name) VALUES (12, 'Charle');

SELECT * FROM t_user
-------------------------------------
iduser | idgroup | name
1      | 12      | Charle



SELECT * FROM t_group
-------------------------------------
idgroup | name
1       | invité

SELECT a.*, b.* FROM t_user AS a INNER JOIN t_user AS b ON a.idgroupe = b.idgroupe;

Cette dernière requête ne vous retournera aucun enregistrement, pour la simple raison que Charles n'est associé à aucun groupe existant dans la table t_group.
Dans cet exemple, vous observez bien qu'il y a une donnée fausse, du moins, qui ne fait référence à rien.

En effet, idgroup vaut 12 dans la table t_user mais elle n'existe pas dans la table t_group. La base de données n'est pas cohérente.
Nous pouvons dire "Il suffit de faire un SELECT pour vérifier l'existence de cet ID". Oui, c'est une solution, mais elle révèle que vous utilisez une astuce pour pallier à un problème. Si vous devez effectuer des tests à chaque requête, le serveur sera très sollicité.

Pour une société, cela n'est pas acceptable. Elle a besoin d'avoir confiance dans les données présentes, que le système soit cohérent.

Pour être sûr que l'utilisateur fait bien partie d'un groupe existant, nous devons utiliser une référence liée à la table t_group via le mot clé FOREIGN KEY, qui veut dire clé étrangère.
Il faut comprendre par l'utilisation d'un élément étranger à la table.

CREATE TABLE t_group (
    id INT(10) NOT NULL auto_increment,
    name    VARCHAR(32) NOT NULL default '',    
    PRIMARY KEY  (id)    
)
ENGINE=InnoDb;

CREATE TABLE t_user (
    iduser  INT(20) NOT NULL auto_increment,
    idgroup INT(10) NOT NULL,
    name    VARCHAR(32) NOT NULL default '',    
    PRIMARY KEY  (id)
    KEY idgroup,
    FOREIGN KEY(idgroup) REFERENCES t_group(id)
)
ENGINE=InnoDb;
INSERT INTO t_group (name) VALUES ('webmaster');
INSERT INTO t_user (idgroup, name) VALUES (LAST_INSERT_ID(), 'Leonardo');

Dans cet exemple, la table t_user est liée à la table t_group via le champ idgroup. Il n'est pas autorisé de placer un idgroup inexistant. Le champ idgroup est lié à la table t_group.

Vous ne pouvez pas référencer un champ n'existant pas encore.

Habituellement, lorsqu'une commande permettant de modifier les informations dans une table (INSERT, UPDATE, DELETE), il n'est plus possible de faire marche arrière.

Si vous estimez que votre programme est fiable à 100%, cela ne pose pas de problème. Mais nous ne sommes jamais à l'abri d'une erreur qui peut avoir un effet boule de neige sur toute la base de données. C'est pour cela qu'InnoDB intègre une notion d'annulation. En résumé, vous pouvez faire un CTRL+Z dans votre base de données.

Si nous reprenons l'exemple précédent avec la table t_group et t_user, nous allons placer volontairement un idgroup inexistant.

SET @idGroup = 123;

CREATE TABLE t_group (
    id INT(10) NOT NULL auto_increment,
    name    VARCHAR(32) NOT NULL default '',    
    PRIMARY KEY  (id)    
) ENGINE=InnoDb;

CREATE TABLE t_user ( 
	iduser INT(20) NOT NULL auto_increment, 
	idgroup INT(10) NOT NULL, 
	name VARCHAR(32) NOT NULL default '', 
	PRIMARY KEY (iduser), 
	FOREIGN KEY (idgroup) REFERENCES t_group (id) 
) ENGINE=InnoDb;


INSERT INTO t_group (name) VALUES ('webmaster');
INSERT INTO t_user (idgroup, name) VALUES (@idGroup, 'Leonardo');

Cette requête retourne une erreur car la valeur d'idGroup devrait valoir 1 et non pas 123.
Par contre, le groupe est déjà existant, ce que nous ne souhaitions pas au début si cela fait partie d'une suite de processus.

DELETE FROM t_group;

SET @idGroup = 123;

SET AUTOCOMMIT = 0;

START TRANSACTION;

INSERT INTO t_group (name) VALUES ('webmaster');
Query OK, 1 row affected (0.00 sec)

INSERT INTO t_user (idgroup, name) VALUES (@idGroup, 'Leonardo');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_user`, CONSTRAINT `t_user_ibfk_1` FOREIGN KEY (`idgroup`) REFERENCES `t_group` (`id`))

ROLLBACK;

Dans le code ci-dessus, il y a des mots clés ayant été ajoutés par rapport à l'exemple précédent.

• AUTOCOMMIT = 0 : Dit à MySQL de ne pas valider les requêtes d'écriture.
• START TRANSACTION : Démarre la zone de la mise en tampon.
• ROLLBACK: : Donne l'ordre à MySQL de tout annuler depuis START TRANSACTION.

S'il n'y avait pas eu d'erreur, il aurait fallu faire un COMMIT au lieu du ROLLBACK. L'ensemble de la transaction aurait alors été validée.

Enfin, toute transaction ouverte et non validée (COMMIT) annulera l'opération à la déconnexion. MySQL effectuera un ROLLBACK.

• Verrouillage de ligne.
• Gestion du COMMIT/ROLLBACK
• Gère les gros volumes de données.
• Gestion des clés étrangères.
• Grande panoplie d'éléments de configuration du moteur.
• Gestion du backup sans bloquer une base en production.
• Couramment disponible chez les hébergeurs en mutualisé.

• Lenteur de certaines opérations telles que le SELECT COUNT(*) FROM maTable.
• TRUNCATE n'est que le synonyme de DELETE.
• Les statistiques envoyées ne sont pas forcément précises : ce ne sont que des estimations.

Dans des domaines comme l'e-commerce, InnoDB saura répondre aux exigences en termes de fiabilité et de sécurité des données dans une base de données, dans la mesure où il sera utilisé avec les outils de transaction et d'intégrité référentielle. Sans cela, il sera préférable d'utiliser le moteur MyISAM.