Mysql: moteurs de stockage

MySQL dispose de nombreux moteurs, Voici les principaux :

  1. MyISAM
  2. InnoDB
  3. MEMORY (anciennement HEAP)
  4. MERGE
  5. BLACKHOLE
  6. BerkeleyDB ou BDB
  7. ARCHIVE
  8. CSV
  9. FEDERATED


Pour vérifier les moteurs de stockage disponibles :

mysql –defaults-file=/etc/mysql/debian.cnf

mysql> use mysql;

mysql> show engines;

+—————————————+—————+——————————————————————————–+——————+—–+—————-+
| Engine                                     | Support    | Comment                                                                                  | Transactions | XA | Savepoints |
+—————————————+—————+——————————————————————————–+——————+—–+—————-+
| MRG_MYISAM                       | YES          | Collection of identical MyISAM tables                                      | NO                | NO | NO              |
| CSV                                        | YES          | CSV storage engine                                                                 | NO                | NO | NO              |
| MyISAM                                  | YES          | MyISAM storage engine                                                           | NO                | NO | NO              |
| BLACKHOLE                          | YES          | /dev/null storage engine (anything you write to it disappears) | NO                 | NO | NO             |
| MEMORY                                | YES          | Hash based, stored in memory, useful for temporary tables   | NO                | NO | NO             |
| InnoDB                                    | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES               | YES | YES            |
| ARCHIVE                                | YES          | Archive storage engine                                                             | NO                | NO | NO             |
| PERFORMANCE_SCHEMA   | YES          | Performance Schema                                                             | NO                | NO | NO              |
| FEDERATED                           | NO           | Federated MySQL storage engine                                           | NULL            | NULL | NULL           |
+—————————————+—————+——————————————————————————–+——————+—–+—————-+
9 rows in set (0.00 sec)


Innodb est le moteur de stockage par défaut à partir de MySQL 5.5.5


Comment vérifier le moteur de stockage d’une table


en ligne de commande de MySQL:


mysql> select TABLE_NAME,ENGINE from TABLES where TABLE_SCHEMA=”nom_base”;
+———————+————+
| TABLE_NAME  | ENGINE |
+———————+————+
| table    | InnoDB |
| table    | Myisam |
| table    | InnoDB |
| table    | InnoDB |
| table    | InnoDB |
| table    | InnoDB |
| table    | InnoDB |
| table    | InnoDB |
+———————+————+


Ou encore:

mysql> use nom_base;
mysql> show TABLE status\G

+———————13. row ———————
Name: table
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 203
Data_length: 948
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 136
Auto_increment: 27
Create_time: 2014-02-19 11:09:21
Update_time: 2018-12-21 09:13:43
Check_time: 2017-01-31 12:30:49
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: pack_keys=0


Structure d’une table Myisam:

MySQL stocke les tables MyISAM dans un dossier portant le nom de la base de donnée.


chaque table est matérialisée par 3 fichiers:


Le fichier .frm: contient la structure de la table (la liste des colonnes, les types…)

Le fichier .MYD: contient les données de la table (MyData)

Le fichier .MYI: contient les indexes


Structure d’une table innodb:

InnoDB stocke tout dans un seul et même fichier ibdata1

InnoDB utilise aussi les fichiers de logs ib_logfile0 et ib_logfile1 et les fichiers de définitions de table .frm (dans un dossier portant le nom de la base de donnée.)