mysql存储引擎

---

一、mysq查看存储引擎

mysql> show engines;//查看mysql所支持的存储引擎，以及mysql默认的存储引擎
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| 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     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.01 sec)

innodb存储引擎： 面向oltp(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeaable级别（可重复读）通过next-keylocking策略避免幻读、插入缓冲、二次写、自适应哈希索引、预读

myisam存储引擎：不支持事务、表锁、全文索引、适合olap（在线分析处理），其中myd:放数据文件，myi:放索引文件

memory存储引擎：数据存放在内存中，表锁，并发性能差，默认使用哈希索引

ndb存储引擎：集群存储引擎，share nothing，可提高可用性

archive存储引擎：只支持insert和select zlib算法压缩1：10，适合存储归档数据如日志等、行锁

maria存储引擎：目的取代myisam、缓存数据和索引、行锁、mvcc

---

//查看某个数据库中某一张表使用的存储引擎
//show  table   status  from  database  where  name="tablename";
mysql> show  table   status  from  jol  where  name="users";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| users | MyISAM |      10 | Dynamic    |  227 |            136 |       30980 | 281474976710655 |         5120 |         0 |           NULL | 2015-11-03 09:26:24 | 2015-11-03 09:26:24 | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

二、各存储引擎对比