mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12 |
+-----------+
1 row in set (0.00 sec)
源码方式安装的Mysql5.6中默认支持如下几种存储引擎,其中InnoDB是默认的存储引擎。
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary 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 |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
有些存储引擎暂时用不到,在编译的时候可以忽略掉。但那些存储引擎是必不可少的呢?
我们看到如下5种存储引擎是mysql自带的schema中使用到的。即便是在编译时指定忽略了某种存储引擎如:
-DWITHOUT_CSV_STORAGE_ENGINE=1 在安装后用show engines还是可以看到mysql支持CSV存储引擎。
mysql> select distinct TABLE_SCHEMA ,engine from information_schema.`TABLES`;
+--------------------+--------------------+
| TABLE_SCHEMA | engine |
+--------------------+--------------------+
| information_schema | MEMORY |
| information_schema | MyISAM |
| mysql | MyISAM |
| mysql | CSV |
| mysql | InnoDB |
| performance_schema | PERFORMANCE_SCHEMA |
+--------------------+--------------------+
因为在mysql这个schema中general_log, slow_log默认使用的是CSV存储引擎。
mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,ENGINE FROM information_schema.`TABLES` WHERE ENGINE='CSV';
+--------------+-------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+-------------+--------+
| mysql | general_log | CSV |
| mysql | slow_log | CSV |
+--------------+-------------+--------+
2 rows in set (0.01 sec)
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)