JUS DE CITRON

MyISAM
C'est le moteur par défaut de MySQL. C'est le successeur du moteur ISAM voué à disparaitre.
C'est le plus simple à utiliser et à mettre en œuvre.
Il utilise plusieurs fichiers qui grandissent au fur et à mesure que la base grossit.
Il ne supporte ni les transactions, ni les clefs étrangères.
MyISAM utilise un mécanisme de verrouillage des tables pour optimiser plusieurs opérations de lecture et d'écriture simultanées. En échange, il faut exécuter la commande OPTIMIZE TABLE de temps en temps pour récupérer l'espace occupé inutilement.
Il prend en charge les index FULLTEXT.

Memory (Heap)
Ce moteur stockent les tables uniquement en mémoire.
La structure de la base est stockée sur le disque dur mais les données sont stockées dans la RAM, donc si la machine serveur redémarre, les données seront perdues.
Cependant, étant donné qu'il n'y a plus d'accès disque, une requête de modification (UPDATE, INSERT, ...) s'exécutera sensiblement plus rapidement.

InnoDB
Ce moteur a été créé et est maintenu par InnoBase (racheté par Oracle en 2005).
Il gère les transactions et les clefs étrangères (et donc une gestion d'intégrité de table).
En contrepartie, les bases qui l'utilisent occupent bien plus d'espace sur le disque.

Blackhole
Ce moteur réceptionne les données, les transfère mais ne les stocke pas.
(Ce type de base ne fera pas partie de mes tests comparatifs)

Archive
Ce moteur est adapté à l'archivage de données.
Les lignes sont compressées au fur et à mesure de leur insertion.
Les requêtes de recherches sont alors sensiblement plus lentes.

CSV
Ce moteur utilise des fichiers textes (au format CSV) comme stockage.

Federated
Ce moteur permet d'accéder à des tables d'une base de données distantes plutôt que dans les fichiers locaux.
(Ce type de base ne fera pas partie de mes tests comparatifs)

MRG_MyISAM ou MERGE
Ce moteur est fait pour fusionner plusieurs tables qui doivent être identiques.
(Ce type de base ne fera pas partie de mes tests comparatifs)


Comparatif des moteurs
Pour faire des tests, je crée des tables de même structure dont la seule différence sera le moteur.
Petites infos tout de même:

• Le type MEMORY ne supporte pas les champs de type BLOB/TEXT, je ferais donc un champ de type VARCHAR(255).
• Les types ARCHIVES et CSV ne supportent pas l'auto-increment, ni les clés , par conséquent pas de champ auto-incrémenté ou de clé primaire sur ces tables de test
• Le type FEDERATED necessite un commentaire de table précisant le serveur distant auquel cotre serveur client va se connecter .
  Ce commentaire doit être du type "scheme://user_name[:password]@host_name[:port_num]:/db_name/tbl_name", mais comme je le précisais ci dessus, je ne ferais pas de test sur ce type de base.

Voici le script de création de la table, avec #### à remplacer par le nom du moteur parmi la liste suivante: myisam, innodb, archive, csv et memory.

CREATE TABLE `table_####` (
`id` INT( 6 )  NULL ,
`description` TEXT NULL ,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
) ENGINE =####

Première comparaison: la taille des tables
La comparaison se fait sur :

• les tables vides
• les tables avec un peu de données (1000 lignes)
• les tables avec beaucoup de données (99999 lignes)

Le contenu est identique dans chaque table.
La requête d'insertion est la suivante (avec $i allant de 1 à 99999):

INSERT INTO table_#### (id, description) VALUES ($i, 'champ texte $i auto rempli histoire de voir un peu ce que ça donne ');

Pour connaitre la taille (en octet) des tables, en mode console, vous avez l'instruction suivante:

misslemon:~# mysql -h localhost -u login -p -e "SHOW TABLE STATUS FROM database_name LIKE 'table_name'\G" | grep Data_length
Enter password:

On constate que la taille de table_csv est de zéro octets. Logique, puisque les données sont stockées en fichiers csv.
Au final, la table memory ne comporte que 21580 lignes. Je suppose que la taille mémoire permise a été atteinte, mais sur ce point si quelqu'un sait pourquoi, je veux bien la réponse.

On peut aussi regarder directement la taille des fichiers sur le disque.

du -sh /var/lib/mysql/nom_de_ma_base/

La base fait : 18M

Dans le dossier /var/lib/mysql/nom_de_ma_base/, on trouve des fichiers table_####.***
Les extensions de fichiers existantes sont les suivantes:

• .frm: Ces fichiers stockent les définitions de table pour tous les types de tables
• .MYD et .MYI: Ce sont les fichiers de données (MYD = MYData) et les fichiers d'indexs (MYI = MYIndex) pour les tables MyIsam
• .ARM et .ARZ: pour les tables archive
• .CSV: pour les tables csv

Regardont donc la place que chaque table prend sur le disque.

ls /var/lib/mysql/nom_de_ma_base/table_####.* | xargs du -csh

Pour le stockage des tables csv, comme les données sont stockées sur des fichiers, il est logique que l'on retrouve ici le véritable espace utilisé.
Pour le stockage des tables memory, comme les données sont stockées en mémoire, il est logique de retrouver un tout petit 12K d'espace occupé sur le disque.
Pour le stockage des tables InnoDB, il faut rergarder un autre fichier, nommé ibdata1

du -sh /var/lib/mysql/ibdata1

J'obtiens cette fois 27Mo, plus logique que les 12Ko trouvés ci dessus.

Deuxième comparaison: le temps de réponse (sur les tables remplies)
Requête 1: SELECT COUNT(*) FROM table_####;
Requête 2: SELECT id FROM table_#### WHERE description LIKE '%5%';
Requête 3: UPDATE table_#### SET description = 'new description' WHERE description LIKE '%5%';


Conclusion
Les tables qui stockent en mémoire (MEMORY) sont sensiblement plus rapide, mais c'est évidement volatile.
Les tables qui stockent dans des fichiers (CSV) donnent des temps de traitement bien plus longs, par contre vous retrouvez toutes vos données en CSV, ce qui peut , peut être , être utile pour des export de données.

Pour un projet web, le véritable choix doit se faire entre InnoDB et MyISAM.
InnoDB prend plus de place, est plus lent. Alors quel serait l'avantage de prendre InnoDB plutôt qur MyISAM puisque les tests ci dessus ne lui donnent pas d'avantages.
Et bien, c'est le fait qu'il soit TRANSACTIONNELS (BEGIN, COMMIT, ROLLBACK) et qu'il puisse gérer les clés étrangères (FOREIGN KEY).

Admettons 2 tables, une table AUTEUR et une table LIVRE, créés en InnoDB.

CREATE TABLE auteur (
    id_auteur tinyint(2) NOT NULL AUTO_INCREMENT,  
    nom_auteur varchar(20) NOT NULL DEFAULT ''
    PRIMARY KEY  (id_auteur)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE livre(
    id_livre tinyint( 2 ) NOT NULL AUTO_INCREMENT ,
    titre_livre varchar( 40 ) NOT NULL DEFAULT '',
    id_auteur tinyint( 2 ) NOT NULL,
    PRIMARY KEY ( id_livre ) ,
    FOREIGN KEY (id_auteur) REFERENCES auteur(id_auteur) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT =1 ; 

Dans le cas de tables MyISAM, pour supprimer un auteur et tous les livres qui lui sont associés, il faudra faire la requête sur la table LIVRE PUIS sur la table AUTEUR.
Dans le cas de tables InnoDB, une seule requête sur la table AUTEUR suffira à tout supprimer.

Source:
Wikipedia
Zdnet.fr
Developpez.com
Browardphp.com
Le PHP facile
MySQL Performance Blog
Apprendre-php.com
Tux-Planet
PHP Team