Les Moteurs de Stockage MySQL

I► Dans cet article, nous allons voir quels sont les principaux moteurs de stockage de données dans le serveur MySQL et quels sont les avantages et les inconvénients de chacun d’entre eux ..
Le moteur stocke les informations sur les disques et en mémoire vive…

—————————————————————————————————————————–

I ? Les différents moteurs de stockage MySQL :

—————————————————————————————————————————–

MySQL propose une architecture pluggable storage engine, ce qui permet de choisir le type de moteur de stockage au niveau de chaque table en fonction des données et des requêtes à traiter.
Le moteur de stockage est choisi à la création de la table mais il peut être modifié plus tard…

Commençons par voir quels moteurs sont supportés par notre serveur :

Capture

 

exemple :
mysql> CREATE TABLE table_clients (id_client INT, nom_client TEXT) ENGINE=MyISAM;
mysql> ALTER TABLE table_clients ENGINE=InnoDB;

la base de données information_schema donne une foule d’informations sur les tables, les vues, etc … la table TABLES nous donne des informations sur :

  • le nom des tables : TABLE_NAME
  • le moteur : ENGINE
  • la date de création
  • le jeu d’encodage …

? Voici une liste des différents moteurs de stockage MySQL :

Choisir le moteur de stockage dépend donc de beaucoup de paramètres comme :

  • La façon de stocker les données.
  • Les verrous.
  • Les sauvegardes et restaurations.
  • Les transactions.
  • Les index, les full-text, les clés étrangères.

—————————————————————————————————————————–

MyIsam :

MyIsam était le moteur de stockage par défaut pour toutes les versions de MySQL inférieures à MySQL 5.5.? Il convient bien aux tables statiques ou pratiquement statiques.

  • Il ne gère ni les relations ni les transactions SQL.
  • Il bloque les tables automatiquement lors d’insertion, de modification ou de suppression de données.
  • Il permet l’indexation des champs et est capable de’optimiser au maximum ses recherches textes avec l’index FULLTEXT (bien plus rapide qu’une clause LIKE %…)
  • Il est extrêmement rapide en lecture.

» Une table MyISAM peut avoir 3 formats :

  • statique (FIXED) :
    Ce type de table ne contient aucun champ de type dynamique (VARCHAR, VARBINARY, TEXT ou BLOB.
    Tous les enregistrements sont de la même taille, les tables sont plus rapides
    La restauration des données est optimisée !
    L’espace disque est plus conséquent qu’en table dynamique, c’est le point négatif !
  • dynamique (DYNAMIC)
    Les types de données VARCHAR, TEXT et BLOB sont autorisés.
    OPTIMIZE TABLE et myisamchk sont nécessaires pour défragmenter les tables
    La restauration des données est plus contraignante qu’avec les tables statiques.

  • compressée (COMPRESSED)
    myisampack permet de compresser les données et les index et de diminuer l’espace disque au maximum de 70 %
    UPDATE, DELETE ou INSERT ne fonctionnent plus évidemment.
    Il faudra décompresser la table avec myisamchk.
    ? Une application tournant sur CD/clé USB aura tout intérêt à utiliser une table MyISAM compressée.

Capture

On peut à la création imposer le type row_format ou plus trad avec alter table …

 

—————————————————————————————————————————–

InnoDB :

InnoDB est un moteur transactionnel et relationnel. Il est adapté aux tables dynamiques (mises à jour très régulièrement avec beaucoup de transactions)
Il s’assure de la cohérence des enregistrements connexes lors d’opérations sur les tables.
Le verrouillage des données s’effectue uniquement au niveau de la ligne concernée et non de la table (MyIsam).
Il permet de restaurer automatiquement les données après un crash système grâce aux fichiers basé sur les journaux de transactions : ib_logfile0 et ib_logfile1 

Il est plus lent que MyIsam mais plus sécurisé (grâce aux relations). Ceci tend à devenir faux depuis la version 5.5…

Une table InnoDB est décrite sur une unité de disque par un fichier dont l’extension est .frm. (ceci est commun à tous les moteurs MySQL)
? Le fichier .frm contient la structure de la table.
? Les données et les index de toutes les tables InnoDB du serveur sont stockés dans le tablespace, décrit sur une unité de disque par un fichier (par défaut ibdata1).

On peut modifier le fait de stocker toutes les données dans ibdata grâce à l’option innodb_file_per_table. my.cnf ou my.ini : innodb_file_per_table=1
Cette option force le serveur MySQL à générer un fichier dont l’extension .ibd et qui contient les données et les index de la table.

Capture

Capture1

 

? Stockage des infos en mémoire et sur disque :

  • Les transactions en cours sont écrites en mémoire dans le log buffer (option de my.cnf ou my.ini innodb_log_buffer_size
  • Les données et les index sont écrits en mémoire dans le buffer pool (option de my.cnf ou my.ini  innodb_buffer_pool_size
  • Le checkpoint permet d’écrire les informations des 2 buffers sur le disque 1x/seconde, lors du checkpoint, dans l’un des deux journaux des transactions (ib_logfile0 ou ib_logfile1) pour le log buffer et dans le tablespace pour le buffer pool.
  • Le log buffer est spécifiquement écrit sur le disque à chaque validation (commit) de transaction.(modifiable avec l’option innodb_flush_log_at_trx_commit)

Options des journaux de transactions :

  • innodb_log_files_in_group : nombre de journaux de transactions
  • taille avec innodb_log_file_size: taille
  •  innodb_data_file_path. : tablespace : localisation, taille et nom

—————————————————————————————————————————–

Memory (heap) :

Memory est un moteur de stockage qui crée les tables directement en RAM.
Il est le plus rapide des moteurs mais extrêmement dangereux en cas de plantage du système (les données sont perdues).
Il est utilisé en développement Web pour stocker des informations relatives à la session (Panier, compte client , mail …) également proposé chez les hébergeurs en mutualisé 

Dans ce cas il faut remplir les tables Memory régulièrement depuis une source de données persistante (fichier ou table d’un autre moteur).

Capture

Capture

 

—————————————————————————————————————————–

Merge :

Merge est un moteur qui permet de réunir plusieurs tables de données indépendantes en une seule même table.
Les tables fusionnées peuvent appartenir à plusieurs bases de données mais celles-ci doivent être physiquement présentes sur le même serveur.
Les colonnes et les types doivent être strictement identiques.

On peut créer une table par mois et fusionner le tout en une table annuelle.
On peut bien sûr travailler indépendamment sur chaque mois (table) et requêter sur l’année entière ou en cours de construction.

—————————————————————————————————————————–

BlackHole :

Le trou noir ( -> /dev/null ) est un moteur qui permet de faire des simulations car tout ce qui est écrit est détruit au final, ce qui évite de supprimer (drop) les tables au final.
—————————————————————————————————————————–

Archive :

Archive est un moteur de stockage qui permet de stocker d’énormes quantité de données. en effet, les données sont compressées à leur insertion.
Ni les relations, ni les transactions, ni les index ne sont autorisées. on ne peut faire que des requêtes d’insert et de select !

ARCHIVE est principalement utilisé pour stocker des données brutes. Un exemple d’application pour archive est l’enregistrements de logs.

—————————————————————————————————————————–

CSV :

Les données sont stockées dans des fichiers textes séparées par une virgule; il permet une grande compatibilité avec les tableurs.

—————————————————————————————————————————–

Michel BOCCIOLESI