Types de bases (moteur de stockage):¶
InnoDB¶
Conseil: enable innodb_file_per_table = 1 option to put indexes and data for individual tables into distinct files.
Requête pour trouver les tables InnoDB parmi toutes les bases:
mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';
Constitution de Mysql (par défaut):
bruno@silverbook:/usr/local/var/mysql$
-rw-r----- 1 bruno admin 15114 28 nov 08:08 ib_buffer_pool
-rw-rw---- 1 bruno admin 50331648 3 déc 10:35 ib_logfile0
-rw-rw---- 1 bruno admin 50331648 24 oct 12:51 ib_logfile1
-rw-rw---- 1 bruno admin 79691776 3 déc 10:35 ibdata1
-rw-rw---- 1 bruno admin 12582912 28 nov 16:53 ibtmp1
-rw-rw---- 1 bruno admin 1272770 3 déc 10:35 silverbook.home.err
-rw-rw---- 1 bruno admin 5 28 nov 16:53 silverbook.home.pid
- ibdata1: fichier de 10Mo extensible. InnoDB y stocke tout (bases,tables, index...) pour éviter la fragmentation.
- ib_logfile0, ib_logfile1: 2 fichiers log de 5Mo.
- ibtmp1
- ib_buffer_pool:
- silverbook.home.err: fichier log des erreurs
http://forum.wgpower.net/technique/innodb-fichier-ibdata1-trop-37009_1.html
https://vdachev.net/2007/02/22/mysql-reducing-ibdata1/
https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
MyISAM¶
Connaitre le type de bases par défaut:¶
mysql> SHOW ENGINES;
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
InnoDB est le moteur par défaut.
ENGINE utilisé pour une table:¶
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database';
MariaDB [(none)]> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'wordpress';
+-----------------------+--------+
| TABLE_NAME | ENGINE |
+-----------------------+--------+
| wp_terms | InnoDB |
| wp_commentmeta | InnoDB |
| wp_term_taxonomy | InnoDB |
| wp_usermeta | InnoDB |
| wp_options | InnoDB |
| wp_users | InnoDB |
| wp_term_relationships | InnoDB |
| wp_links | InnoDB |
| wp_postmeta | InnoDB |
| wp_termmeta | InnoDB |
| wp_comments | InnoDB |
| wp_posts | InnoDB |
+-----------------------+--------+
Dernière mise à jour:
September 15, 2021