mysql存储引擎

来源《深入浅出mysql》

mysql5.5之前默认存储引擎是MyISAM，5.5之后改为InnoDB。

修改默认存储引擎，可以在参数文件中设置default-table-type。

修改表的存储引擎：

alter table 表名 engine = innodb;

1、MyISAM

MyISAM是mysql默认的存储引擎，不支持事务，也不支持外键，优势是访问速度快。对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎。

每个myisam在磁盘上存储成3个文件，文件名和表名相同，扩展名分别是：

.frm（存储表定义）

.MYD（MYData，存储数据）

.MYI（MYIndex，存储索引）

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

myisam类型的表可能会损坏，损坏后的表可能不能被访问。myisam类型的表提供修复的工具，可以用CHECK TABLE语句来检查myisam表的健康，并用REPAIR TABLE修复损坏的表。

myisam表支持3种存储格式，分别是：

静态（固定长度）表；

动态表；

压缩表；

静态表是默认的存储格式，表中字段都是固定长度。优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在存储时会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格。需要注意：如果需要保存的内容后面本来就带有空格，返回时会丢失尾部的空格，如果前面有空格，前面的空格还是会保留。

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

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

2、innodb

innodb提供了具有提交、回滚和崩溃恢复能力的事务安全。对比myisam，innodb写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

特点：

a、自动增长列

自动增长列可以手动插入，插入值是0或者空，会自动插入增长后的值。

alter table xxx AUTO_INCREMEMT = n；设置自动增长列的初始值。默认1。该默认值保留在内存中，如果数据库重新启动，这个默认值会丢失，需要重新设置。

可以使用 select LAST_INSERT_ID(); 查询当前线程最后插入记录使用的值。如果一次插入多条记录，返回的是第一条记录使用的自动增长值。

对于innodb，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。对于myisam，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列按照组合索引的前面几列进行排序后递增的。

b、外键约束

mysql支持外键的存储引擎只有innodb。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

在创建索引时，可以指定在删除、更新父表时，对子表进行相应的操作，包括restrict、cascade、set null、no action。restrict 和 no action 相同，是指限制在子表有关联记录的情况下父表不能更新；cascade表示父表在更新或者删除时，更新或者删除子表对应的记录。set null表示父表在更新或者删除时，子表对应字段被set null。

当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。

c、存储方式

innodb存储表和索引有两种方式：

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

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

3、memory

memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问速度非常快，因为他的数据是放在内存中的。并且默认使用hash索引，一旦服务关闭，表中的数据就会丢失。

给memory表创建索引的时候，可以指定 使用hash索引还是btree索引。

服务器需要足够的内存来维持所有在同一时间使用的memory表，当不再需要memory表的内容时，要释放被memory表使用的内存，执行delete from 或truncate table ，或者整个的删除表（drop table）。

每个memory表中可以放置的数据量的大小，受到max_heap_table_size系统变量的约束，这个变量初始值是16MB，可以根据需要加大。

memory存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效的对中间结果进行分析并得到最终统计结果。

4、merge

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

merge表的插入操作是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值：FIRST插入第一个表，LAST插入最后一个表，不定义或者NO，表示不能对merge表执行插入操作。

merge表的删除操作只是删除merge的定义，对内部表没有任何影响。

merge表在磁盘上保留2个文件：.frm存储表定义，.MRG文件包含组合表的信息。可以通过修改.MRG文件来修改merge表，但是修改后需要通过FLUSH TABLES刷新。

几种常用存储引擎的适用环境：

myisam：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整 性、并发性要求不是很高，选择myisam非常合适。

innodb：用于事务处理的应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询，还包括很多的更新，删除操作，选择innodb。

memory：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。通常用于更新不太频繁的小表，用以快速得到访问结果。

merge：用于将一系列等同的myisam表以逻辑方式组合在一起，并作为一个对象引用他们。优点在于可以突破对单个myisam表大小的限制，并且通过将不同的表分布在多个磁盘上，有效改善merge表的访问效率。