一、引子
前些日在修改bug的时候涉及到了一个问题,就是当我在数据库中删除一个常量的时候,显示删除错误。后究其原因,发现是级联删除出现了问题,由于我当初建表的时候,选择的存储引擎是默认的INNODB,所以每当我删除的时候,因为存在外键,导致无法删除。随后,找到了解决措施,就是将我建表时的存储引擎从INNODNB改为MyISAM,后来删除问题就解决了。因此,在我寻求解决措施的时候,同时去了解了Mysql不同存储引擎的区别。
二、有关Mysql存储引擎的概述
和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。
Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。
MySQL5.0支持的存储引擎包含 : InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。
三、不同Mysql存储引擎的存储特性
因为篇幅有限,下面介绍的为几种常见存储引擎的特性
MyISAM
MyISAM存储引擎是MySQL中常见的存储引擎,曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM增加了很多有用的扩展。
MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同,或站名包括frm、MYD和MYI。其中,frm为扩展名的文件存储表的结构;MYD为扩展名的文件存储数据,是MYData的缩写;MYI为扩展名的文件存储索引,是MYIndex的缩写。
基于MyISAM存储引擎的表支持3种存储格式,包括静态型、动态型和压缩型。其中,静态型为MyISAM存储引擎的默认存储格式,其字段是固定长度的;动态型包含变长字段,记录的长度不是固定的;压缩型需要使用myiampack工具创建,占用的磁盘空间较小。
MyISAM存储引擎的优势在于占用空间小,处理速度快;缺点是不支持事务的完整性和并发性。
MEMORY
MEMORY存储引擎是MySQL中一类特殊存储引擎。其使用存储在内存中的内容来创建表,而且所有数据也放在内存中。这些特性都与InnoDB存储引擎、MyISAM存储引擎不同。
每个基于MEMORY存储引擎的表实际对应一个磁盘文件,该文件的文件名与表名相同,类型为frm类型,该文件中只存储表的结构,而其数据文件都是存储在内存中的。这样有利于数据的快速处理,提供整个表的处理效率。值得注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了,可以释放这些内存,甚至可以删除不需要的表。
MEMORY存储引擎默认使用哈希(HASH)索引。其速度要比使用B型树(BTREE)索引快。如果读者希望使用B型树索引,可以在创建索引时选择使用。
MEMORY表的大小是受到限制的。表的大小主要取决于两个参数,分别是max_rows和max_heap_table_size。其中,max_rows可以在创建表时指定;max_heap_table_size的大小默认为16MB,可以按需要进行扩大。因此,其存在于内存中的特性,这类表的处理速度非常快。但是,其数据易丢失,生命周期短。基于这个缺陷,选择MEMORY存储引擎时需要特别小心。
INNODB
InnoDB是MySQL数据库的一种存储引擎。InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。MySQL从3.23.34a开始就包含InnoDB存储引擎。InnoDB是MySQL第一个提供外键约束的表引擎,而且InnoDB对事务处理的能力也是MySQL对其他存储引擎所无法与之比拟的。
MySQL 5.6版本之后,除系统数据库之外,默认的存储引擎由MyISAM改为InnoDB,MySQL 8.0版本在原先的基础上将系统数据库的存储引擎也改为了InnoDB。
InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空,且值必须唯一。MySQL中规定自增列必须为主键。在插入值时,如果自动增长列不输入值,那么插入的值为自动增长后的值;如果输入的值为0或空(NULL),那么插入的值也为自动增长后的值;如果插入某个确定的值,且该值在前面没有出现过,那么可以直接插入。
InnoDB存储引擎中支持外键(FOREIGN KEY)。外键所在的表为子表,外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时,子表也必须有相应的改变。
InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制;缺点是其读写效率稍差,占用的数据空间相对比较大。
四、如何选择合适的存储引擎
1、MyISAM :由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定,读写相互阻塞,读取速度快,节约资源,所以如果应用是以查询操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
2、InnoDB : 是MySQL的默认存储引擎, 由于InnoDB支持事务、支持外键、行级锁定 ,支持所有辅助索引(5.5.5后不支持全文检索),高缓存,所以用于对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,读写频繁的操作,那么InnoDB存储引擎是比较合适的选择,比如BBS、计费系统、充值转账等
3、MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据环境下,可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速得到访问结果。