插件式存储引擎是MySQL数据库最重要的特性之一,用户可以根据应用的需要选择如何存储和索引数据,是否使用事务等。MySQL默认支持多种存储引擎,以适用于不同领域的数据库应用需要,用户可以通过选择使用不同的存储引擎提高应用的效率。目前MySQL支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB CIuster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表。
特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 有 | 64TB | 有 | 没有 | 有 |
事务安全 | 支持 | ||||
锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | 支持 | |||
全文索引 | 支持 | ||||
集群索引 | 支持 | ||||
数据缓存 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 低 | 高 | 低 | 低 | |
内存使用 | 低 | 高 | 中等 | 低 | 高 |
批量插入的速度 | 高 | 低 | 高 | 高 | 高 |
支持外键 | 支持 |
1、MyISAM
MyISAM是MySQL默认的存储引擎。MyISAM不支持事务,也不支持外键,其优势是访问的速度快,对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来建表。
每个MyISAM在磁盘上存储成3个文件,其文件名都和标明相同,但是扩展名分别是:
- .frm(存储表定义)
- .MYD (MYData,存储数据)
- MYI (MYIndex,存储索引)
数据文件和索引文件可以放置在不同的目录,平均分布IO,获得更快的速度。
MyISAM的表还支持3种不同的存储格式,分别是:
- 静态表
- 动态表
- 压缩表
静态表是默认的存储格式。静态表中的字段都是非变长字段,这样每个记录都是固定长度的,这种存储方式的有点是存储非常迅速,容易缓存,出现故障容易恢复,缺点是占用的空间通常比动态表多。静态表的数据在存储时会按照列的宽度定义补足空格,但是在应用访问的时候并不会得到这些空格,这些空格在返回给应用之前已经去掉。
2、InnoDB
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全,但是对比MyISAM存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。
1、自动增长列
InnoDB表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值。对于InnoDB表,自动增长列必须是索引,如果是组合索引,也必须是组合索引的第一列。
2、外键约束
MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求附表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。
3、存储方式
InnoDB存储表和索引有两种方式:
- 使用共享表空间存储,这种方式创建的表的表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件。
- 使用多表空间存储,这种方式创建的表的表结构仍然保存在.frm文件中,但是每个表的数据和索引单独保存在.ibd中,如果是个分区表,则每个分区对应单独的.ibd文件,文件名是表名+分区名,可以在创建分区的时候指定每个分区的数据文件的位置,以此来将表的IO均匀分布在多个磁盘上。
3、MEMORY
MEMORY存储引擎使用存在于内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常快,因为它的数据时放在内存中的,并且默认使用hash索引,但是一旦服务关闭,表中的数据就会丢失。
在给MEMORY表创建索引的时候,可以指定使用hash索引还是btree索引。
4、MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身没有数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。
5、如何选择合适的存储引擎
- MyISAM:默认的MySQL插件式存储引擎。如果应用是以读操作和插入操作为主,只是很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
- InnoDB:用户事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询意外,还包括很多的更新、删除操作,那么选择这个存储引擎是非常合适的。
- MEMORY:将多有数据存储在RAM中,在需要快速定位记录和其他类似数据的环境下,可以提供极快的访问,它的缺陷是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据时可以恢复的。MEMORY表常用于更新不太频繁的小表,用以快速得到访问结果。
- MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率,这对于数据仓储等VLDB环境十分适合。