一、查看与设置
旧版本的 MySQL 可使用以下命令查询默认存储引擎：
show variables like 'table_type';
新版本的 MySQL 使用以下命令查询：
show variables like '%storage_engine%';
可以通过以下命令查询系统中支持的所有引擎及默认的引擎：
show engines \G
定义表结构时，如果不指定存储引擎则按照默认引擎设置，也可以显式指定
二、各种存储引擎的特性
| 特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 有 | 64TB | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 表锁 |
| B 树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | ||||
| 全文索引 | 支持 | ||||
| 集群索引 | 支持 | ||||
| 数据缓存 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 低 | 高 | N/A | 低 | 低 |
| 内存使用 | 低 | 高 | 中等 | 低 | 高 |
| 批量插入的速度 | 高 | 低 | 高 | 高 | 高 |
| 支持外键 | 支持 |
1、MyISAM
MySQL 5.5.5 之前的默认存储引擎，不支持事务和外键，但访问数据块的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用 MyISAM。
（1）文件组成
使用 MyISAM 的表在磁盘上存储为三个文件，文件名都与表名相同，扩展名分别是：
- .frm：存储表定义；
- .MYD：MYData，存储数据；
- .MYI：MYIndex，存储索引。
（2）表损坏与修复
MyISAM 不具备 crash-safe 能力，所以在以下场景中有可能导致表损坏：
- 服务器突然断电导致数据文件损坏；强制关机，没有先关闭 mysql 服务；mysqld 进程在写表时被杀掉；
- 服务器宕机；
- 磁盘损坏；
- mysql 本身的bug
损坏后的表不能被访问，可以通过以下命令检查表的健康：
check table tablename;
如果一个表被损坏了，可以通过以下命令修复：
repair table tablename;
为了减少表损坏的概率，减少文件碎片，在进行大量的更新删除操作之后，可以使用以下命令来优化表：
optimize table tablename;
（3）存储格式
MyISAM 的表支持三种不同的存储格式，分别为：
- 静态（固定长度）表；
- 动态表；
- 压缩表。
静态表是默认的存储格式。表中字段都是非变长字段，每条记录占用的空间大小是固定。
【优点】存储迅速，容易缓存，出现故障容易恢复。
【缺点】占用空间比动态表多。
【实例演示】
如下面的静态表中，每个字段的长度都是固定的，因此创建出来的表，每条记录占用的存储空间都是 53 个字节。
动态表中包含了变长字段，实际用到多少空间就分配多少，每条记录占用的空间大小是动态的。
【优点】占用的空间相对较少。
【缺点】频繁地更新和删除记录会产生碎片，需要定期执行 optimize table 或 myisamchk-r 命令来改善性能，并且在出现故障时恢复比较困难。
压缩表由 myisampack 工具创建，占据非常小的磁盘空间，适用于基本都是查询的场景。
2、InnoDB
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，且支持外键，锁的粒度是行锁，能够获得更高的并发度，但跟 MyISAM 相比需要占用更多的磁盘空间，为了保证事务安全和 crash-safe 能力，插入数据时需要做更多的操作，因此写处理效率低一些。
（1）自增列
InnoDB 的自增列可以手动插入，但如果是空或者 0 则按照自增值来，若手工插入了合法值，则下一个自增值为该值 +1。
InnoDB 自动增长列必须是索引，如果是组合索引，自增列也必须是索引中的第一列。
（2）外键约束
InnoDB 是 MySQL 中唯一支持外键的索引。假设 A 是主表，主键为 a，B 是子表，主键为 b，则 B 中必须有一列 a 作为外键，对应 A 的主键。
在声明外键时还可以施加约束：
-
restrict和no action相同，只指限制在子表有关联记录的情况下父表不能更新； -
cascade表示父表更新和删除时，更新和删除子表对应的记录； -
set null表示父表在更新和删除时，子表对应的字段被 set null。
插入数据时，因为外键的存在和约束，会多做一些检查，也需要更多的存储空间，所以大批量导入数据时，会降低数据导入速度，因此可以通过暂时关闭外键约束来加快处理速度，命令为：
// 查看开关
select @@foreign_key_check;
// 禁用
set foreign_key_check=0;
// 导入完成后，重启约束
set foreign_key_check=1;
（3）存储方式
InnoDB 存储表和索引有两种方式：
共享表空间存储：表结构保存在 .frm 文件中，数据和索引保存在
innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。使用多表空间存储：表结构保存在 .frm 文件中，每个表的数据和索引单独保存在 .ibd 文件中。如果是分区表，则对每个分区对应单独的 .ibd 文件，文件名是 "表名+分区名"，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀地分布在多个磁盘上。
根据参数 innodb_file_per_table 参数的设置，决定使用共享表空间存储还是多表空间存储，默认是打开的，即默认使用的是多表空间存储。如果修改了该参数，则需要重启 MySQL 才能生效，并且原来的表的存储方式不变，新建的表才会按照新的设置进行表空间存储。
使用多表空间特性的表，可以方便地进行单表备份和恢复。
3、MEMORY
（1）内存表的特性
MEMORY 引擎的数据都在内存中，因此访问速度非常快，每个内存表都只有一个磁盘文件 .frm，但是一旦服务关闭，表中的数据就会丢失掉。
（2）内存表的自动初始化
如果想在服务启动时，自动加载一些数据到内存表中，则可以使用 --init-file 选项，或者在配置文件中添加 init-file 配置项，步骤如下：
a. 编写一个文件，将要在服务启动时自动执行语句放入其中
b. 在服务初始化配置中加上 init-file 选项
c. 重启服务器
（3）索引
MEMORY 是 MySQL 唯一一个支持哈希索引的引擎，默认也是哈希索引，当然也可以显式指定使用 HASH 索引还是 BTREE 索引。
（4）大小限制
定义内存表时，可通过 max_rows 子句指定表的最大行数，若不指定，则默认最大数据量限制为 16M，由 max_heap_table_size 系统变量设定。
（5）使用场景
用于内容变化不频繁地代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。更新操作时要考虑到服务重启后丢失的问题，做好数据备份。
4、MERGE
MERGE 引擎是对一组 MyISAM 表的操作，这些表必须满足以下条件：
- 使用 MyISAM 引擎
- 表结构完全相同
（1）定义
MERGE 表不存储数据，对表执行增删查改实际上是对内部的 MyISAM 表进行的操作，定义 MERGE 表的语法如下：
create table merge_tablename (
...
)engine=merge union=(myisam_tab1, myisam_tab2, ..., myisam_tabn) insert_method={first|last};
其中 union 定义了内部都有哪些 MyISAM 表，insert_method 则指定了插入数据时要插入到哪个表中取。
查询数据时，可以把 MERGE 表当成类似视图一样使用：
插入数据时，按照表定义决定插入到内部的第一个还是最后一个 MyISAM 表
（2）文件组成
- .frm：存储表的定义。
- .MRG：存储组合表的组合信息。
三、存储引擎的组合
-
MyISAM：MySQL 5.5.5 之前默认的存储引擎
【适用场景】
① 读操作和插入操作为主，只有很少的删除和更新；
② 对事务完整性、并发性要求不高【缺点】
① 不支持事务
② 宕机、服务器断电、磁盘损坏等情况下数据可能被损坏需要修复
-
InnoDB：MySQL 5.5.5 之后默认的存储引擎
【使用场景】
① 需要事务或外键支持；
② 数据操作除了查询和插入外，还包含很多更新和删除；
③ 希望在不可预期如宕机、服务器断电、磁盘损坏等各种情况下依然鞥保证 crash-safe 能力；
④ 对并发要求比较高。
-
MEMORY
【使用场景】
① 将所有数据保存在内容中，方便快速定位记录；
② 更新不频繁的小表。【缺点】
对表大小有限制，不能存储大量数据。
-
MERGE
【使用场景】
将多个 MyISAM 表以逻辑方式组合在一起，作为一个对象使用，可以突破对单个表的大小限制，并通过不同的表分布在多个磁盘上，可以有效地概述 MERGE 表的访问效率，对于数据仓库的场景十分合适。
