MySQL之存储引擎InnoDB，MyISAM和MEMORY

1、查看mysql支持的存储引擎：
show engines;

   mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| 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         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| 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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

2、查看已有表的存储引擎
show create table 表名;

mysql> show create table Vendors\G;
*************************** 1. row ***************************
       Table: Vendors
Create Table: CREATE TABLE `Vendors` (
  `vend_id` char(10) NOT NULL,
  `vend_name` char(50) NOT NULL,
  `vend_address` char(50) DEFAULT NULL,
  `vend_city` char(50) DEFAULT NULL,
  `vend_state` char(5) DEFAULT NULL,
  `vend_zip` char(10) DEFAULT NULL,
  `vend_country` char(50) DEFAULT NULL,
  PRIMARY KEY (`vend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3、创建表时可指定引擎
create table 表名(…)engine=MyISAM,charset=utf8,auto_increment=10000;

mysql> create table test(id int primary key,name varchar(60),sex varchar(10))engine=myisam,charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test;
  CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

4、修改已有表的引擎(几乎不用)
alter table 表名 engine=InnoDB;

mysql> alter table test engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(60) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5,InnoDB引擎

① 支持事务：支持4个事务隔离级别，支持多版本读。
② 行级锁定（更新时一般是锁定当前行）：通过索引实现，全表扫描仍然会是表锁。
③读写阻塞与事务隔离级别相关。
④ 具有非常高效的缓存特性：能缓存索引，也能缓存数据。
⑤ 整个表和主键以Cluster方式存储，组成一颗平衡树。
⑥ 所有Secondary Index都会保存主键信息。
⑦ 支持分区，表空间，类似Oracle数据库。
⑧ 支持外键约束，5.5以前不支持全文索引，之后版本支持了。
⑨ 和MyISAM相比对硬件资源要求比较高。
适合的业务场景：

1. 需要事务支持的业务
2. 行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引完成。
3. 读写及更新都很频繁的场景
4. 数据一致性要求较高的业务
5. 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可用减少磁盘I/O。

6，MyISAM引擎

① 不支持事务（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功要么全部失败）。
② 表级锁定（更新时锁整个表）
③ 读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身不会阻塞另外的读。
④ 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少磁盘I/O，但这个缓存区只会缓存索引，而不会缓存数据。
⑤ 读取速度较快，占用资源相对少。
⑥ 不支持外键约束，但支持全文索引。
⑦ 5.5.5前为默认存储引擎。
适合的业务场景：

1. 不需要事务支持的业务
2. 读数据比较多的应用
3. 数据库修改较少的业务
4. 对数据一致性要求不是非常高的业务
5. 硬件资源比较差的机器可以用MyISAM

7，MEMORY引擎

基于哈希，存储在内存中，用于临时表
特点：
1、表记录存储在内存中，效率高
2、服务或主机重启，表记录清除

8，如何选择存储引擎总结

1、执行查(读)操作多的表用 MyISAM(使用InnoDB浪费资源)
2、执行写操作多的表用 InnoDB
3、临时表用 ： MEMORY