MySQL主流的存储引擎及区别

问题一：主流引擎和对应的文件形式

• MyISAM
  （1）.frm文件：存储表的定义数据
  （2）.myd文件：存放表具体记录的数据
  （3）.myi文件：存储索引
• InnoDB
  （1）使用共享表空间存储：所有的表和索引存放在同一个表空间，数据格式为ibdata文件。
  （2）使用多表空间存储：表结构放在.frm文件，数据和索引放在.ibd文件中。
• Memory
  使用.frm
• Archive
  （1） .frm用于存储表定义
  （2）.arz用于存储数据
• CSV
  使用.csv

问题二：在不同引擎里创建索引，会产生什么样的文件？

• MyISAM
  产生.myi文件，用于存储索引
• InnoDB
  （1）共享表空间：索引储存在ibdata文件中。
  （2）多表空间存储：索引放在.ibd文件中。
• Memory
  使用.frm结构索引。
• Archive
  不确定，猜测.frm用于存储表索引。
• CSV
  CSV不支持索引。

MyISAM

储存方式

每个MyISAM在磁盘上存储成三个文件。
（1）.frm文件：存储表的定义数据
（2）.myd文件：存放表具体记录的数据
（3）.myi文件：存储索引

支持数据类型

（1）静态固定长度表
这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。
（2）动态可变长表
优点是节省空间，但是一旦出错恢复起来比较麻烦。
（3）压缩表
支持数据压缩。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

特点

• 不支持事务，但存储速度快。
• 支持全文索引、B树索引和数据压缩。
• 支持大文件（达到63位文件长度）。
• 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。

适用场景

• 在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
• 大文件
• 主要用于插入新纪录和读出记录的表

InnoDB（默认的数据库存储引擎）

储存方式

（1）使用共享表空间存储：所有的表和索引存放在同一个表空间，数据格式为ibdata文件。
（2）使用多表空间存储：表结构放在.frm文件，数据和索引放在.ibd文件中。分区表的话，每个分区对应单独的.ibd文件。使用分区表的好处在于提升查询效率。
我没有在自己的系统中找到frm文件

特点/应用场景

• 支持事务，但损失了效率
• 可以支持在线热备份
• 支持外键约束（只有它支持外键）
• 使用的锁粒度为行级锁，可以支持更高的并发
• 可以通过自动增长列(auto_increment)
• 经常更新的表，适合处理多重并发的更新请求

适用场景

• 使用事务（数据完整性）
• 处理巨大数据量
• 外键（只有它支持外键）
• 并发控制
• 频繁更新、删除

Memory

储存方式

将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是.frm格式。

支持的数据类型

支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型

特点

• 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈。
• 由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失。（不能主动恢复）
• 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低。
• 默认使用Hash索引。
• 如果一个内部表很大，会转化为磁盘表。
• 查询速度快

适用场景

• 用于查询的临时表

Archive

储存方式

（1） .frm用于存储表定义
（2）.arz用于存储数据

特点

• 支持insert、replace和select操作，但是不支持update和delete
• 最好不经常对表进行查询
• 支持索引，但只允许在自增ID列上加索引
• 对于自增数据，不能插入不大于当前最大值的数据

适用场景

• 数据归档
• 适用于日志表

CSV

储存方式

• .csv文件

特点

• 不支持索引，没有主键列
• 不允许字段为空(null)

适用场景

• 交换数据的时候