Mysql 常用存储引擎简介

  Mysql默认支持多种存储引擎，以适用于不同的应用需求，用户可以选择不同的存储引擎来提高应用的效率。

默认情况下，创建新表的时候不指定具体的存储引擎，则使用默认的存储引擎。在创建新表时，可以通过增加ENGINE关键字设置新表的存储引擎，也可以使用ALTER TABLE语句将一个已经存在的表的存储引擎进行修改。具体实现如下：

创建一个名为test的表，编码为utf8，存储引擎选择MyISAM;

CREATE TABLE test(

  id bigint(20) NOT NULL AUTO_INCREMENT,

 PRIMARY KEY(id)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

修改test表的存储引擎，将其改为INNODB;

ALTER TABLE test ENGINE = INNODB;

下边图介绍一下常用的几种存储引擎以及它们之间的区别：

一 .MyISAM

MyISAM不支持事务、也不支持外键，但是访问速度快，对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表。

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，扩展名分别为：

.frm(存储表定义),

.MYD(MYData存储数据),

.MYI(MYIndex，存储索引)

数据文件和索引文件可以防止在不同的目录下，平局分布IO，获得更快的速度。在创建表的时候可以通过DATA DIRECTORY和INDEX DIRECTORY语句指定，就是说不同表的索引文件和数据文件可以放置到不同的文件路径下。注意：文件路径需要是绝对路径，并且具有访问的权限。

MyISAM的表支持3种不同的存储格式：

静态（固定长度）表；

动态表；

压缩表。

其中，默认存储格式是静态表，静态表中的字段都是非边长字段，这样每个记录都是固定长度的，这种存储方式存储非常迅速，容易缓存，出现故障容易恢复，但是占用的空间却比动态表要多。静态表在存储数据时，会按照列的宽度填充空格，在应用访问时去除这些空格，所以如果应用保存的内容后边本来就带有空格的时候，会使得访问时丢失末尾空格。

动态表中包含边长字段，记录是不固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表有myisampack工具创建，占据非常小的磁盘空间。因为每个记录都是被单独压缩的，所以只有非常小的访问开支。

二 . InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保存数据和索引。InnoDB支持了自动增长列，以及外键约束。

对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但对于MyISAM表，自动增长列可以是组合索引的其他列。

mysql支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。注意：当某个表被其他表创建了外键参照，那么该表的对应的索引或者主键禁止被删除；在导入多个表的数据时，如果需要忽略表之前的朱外键关系，可以暂时关闭外键的检查，可以通过SET FOREIGN_KEY_CHECKS =0暂时关闭外键，需要开启时执行SET FOREIGN_KEY_CHECKS =1即可。

InnoDB存储表和索引有以下两种方式。

1.使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

2.使用多表空间存储，这种方式创建的表的表结构仍然是保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以再创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

三. MEMOEY

MEMOEY 存储引擎使用存在内存中的内容来创建表。每个MEMOEY 表只实际对应一个磁盘文件，格式为.frm。MEMOEY 类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用hash索引，但是一旦服务关闭，表中的数据就会丢失掉。注意：对MEMOEY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

四. MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM表进行的。

如何选择合适的存储引擎？

在选择存储引擎时，应根据应用的特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。下面介绍常用存储引擎的适用环境：

MyISAM:如果应用以读操作和插入操作为主，只是很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么选择这个引擎是非常合适的。

InnoDB:用于事务处理应用程序，支持外键。如果对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作处理插入和查询以外，还包括很多对的更新。删除操作，那么InnoDB是比较合适的。

MEMOEY：MEMORY表通常用于更新不太频繁的小表，用于快速得到访问结果。

MERGE：MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。对于诸如数据仓库等VLDB环境十分合适。