MySQL作为全世界广受欢迎的数据库,被用于很多中小型的项目中,但是你对 MySQL 数据库的存储引擎了解多少呢?
我们将逻辑表中的数据存储到数据库中,数据库又将我们表中的数据存储到物理设备中(如磁盘,内存)。数据在物理设备上如何存储?如何创建索引?如何进行增删该查操作?对数据在物理设备上的存储和操作方式(或者说是类型、服务)就称为数据库引擎。
比如,在赛车比赛中,如果赛车手根据不同的比赛地形选择合适的赛车引擎,就能够让赛车跑得更快。MySQL 数据库支持插拔式的存储引擎,可以很方便的更换不同的数据库引擎,相比较而言,赛车手更换引擎就没那么方便了。MySQL 数据库的逻辑架构图如下,可以看到,存储引擎在整个 MySQL 数据库系统中位于底层。
在 命令行中使用 show engines 查看 MySQL 数据库引擎,从下图中可以看出当前 MySQL 默认的数据库引擎就是 InnoDB。(我这里的 MySQL 为社区版 5.7 版本,请自行忽略背景中的小姐姐,手动滑稽~。)
下面我将一一介绍这些数据库引擎。
InnoDB 引擎:
自从 MySQL5.6 后,InnoDB 就是 MySQL 默认的存储引擎,在这之前是 MyISAM 引擎。
InnoDB支持事务,支持外键,支持自动的灾难恢复,使用行锁支持高并发的读取,支持热备份和奔溃后的快速恢复,支持全文检索(5.6.4版本才支持,所以一些面试题由于时间原因可能这里不严谨),有自己的读写缓存管理机制,即缓存索引也缓存数据,不支持压缩(未来可能支持)。
使用 InnoDB 引擎创建表,会生成 frm 文件和 idb 文件,frm 文件中存储表的定义,idb 文件中存储表的数据和索引。
一般来说,InnoDB 对于大多数情况都是很不错的选择,能满足绝大多数的需要,这也是它称为MySQL5.6 之后默认数据库引擎的原因。
MyISAM 引擎:
MyISAM不支持事务,不支持奔溃后快速恢复,使用表锁不适合高并发,追求性能,依赖操作系统管理读取和写入的缓存,只缓存索引不缓存真实数据,支持压缩。
使用 MyISAM 引擎创建表,会生成 frm 文件、MYD文件和MYI文件,frm 文件中存储表的定义,MYD 文件中存储表的数据,MYI文件中存储表的索引。
因为 MyISAM 引擎不支持事务处理,所以意味着有事务需求时,不能使用 MyISAM 存储引擎。MyISAM 追求的是性能,适合插入和读取操作。
MRG_MyISAM (MERGE)引擎:
MRG_MyISAM引擎是一组 MyISAM 表的组合,在MySQL5.7 之前叫做 MEGER。
使用 MEG_MyISAM 引擎创建表,会生成 frm 文件和 MRG 文件,frm 文件中存储表的定义,MEG 文件存储表的数据。
MEG_MyISAM引擎需要主表 MERGE 和子表具有完全一样的数据结构(属性、数据类型、索引)。对于存储策略是分成多表,如日志表,按照一年十二个月份划分,需要 12 张定义完全一样的数据表。当联合多个月份的表查询数据时,需要写多个表的连接,这时使用 MEG_MyISAM 引擎只需要像对待一张表一样查询,对数据的操作就变得简单了。
MEMORY 引擎:
MEMEORY速度快,数据直接存储在内存中。
使用 MEMORY 引擎创建表,只会生成 frm 文件,因为表的数据是暂时存放在内存中的。
MEMORY追求的是速度,为了得到更快的响应时间,采用速度相对磁盘快得多的内存做为存储介质,可以作为临时表使用。但由于数据是放在内存中的,表中的数据需要立即使用,否则当 MySQL守护进程奔溃时,表中的数据就会丢失。
CSV 引擎:
使用 CSV 引擎创建表,会生成 frm 文件,CSV 文件和CSM文件,frm 文件中存储表的定义,CSV 文件中存储表的数据,CSM 文件中存储表的元信息(如数据表现在的状态,数据表中的数据数量)。
生成的数据文件可直接使用 Office 中的 Excel 打开,但要注意文件损坏,必要时可使用命令 check table、repair table 检查和恢复表。
BLACKHOLE 引擎:
BLACKHOLE如同它的名字,插入任何数据都不会保存,表永远为空,写入任何数据都会消失,就像是黑洞一样,可以吃掉所有的数据。
使用 BLACKHOME 引擎创建表,只会生成 frm 文件,frm 文件中存储表的定义,因为写入任何数据都不会保存,所以也就没有存储表数据的文件。
BLACKHOLE不会保存数据,所以数据表可以作为演示使用,随意的增删改查,最终都不会在表中留下数据。
PERFORMANCE_SCHEMA 引擎:
该引擎用于收集数据库性能相关的参数,是 MySQL 的性能模式,主要关注性能数据。用户不能直接创建存储引擎为PERFORMANCE_SCHEMA 的表。
ARCHIVE 引擎:
ARICHIVE支持数据压缩,支持插入和查询操作两种功能。
使用 ARCHIVE 引擎创建表,会生成 frm 文件和 ARZ 文件,frm 文件中存储表的定义,ARZ 文件中存储表的压缩后的数据和索引。
FEDERATED 引擎:
支持代理查询,即创建一个远程的 MySQL客户端连接,将要查询的语句传输到远程服务器上执行,完成数据的存取。
从上图中可以看到,社区版的 MySQL 并不支持 FEDERATED 引擎。
除了上面这些数据库引擎,你还可以使用自己开发的数据库引擎,MySQL 提供了很好的扩展性。
根据自己的需要使用数据库引擎,才能发挥出数据库的性能和满足实际的需要。在选择 MySQL 数据库引擎之前,你要考虑下面几个问题。
·1、是否需要支持事务?
·2、是否需要热备份?
·3、是否需要奔溃恢复?
·4、是否需要外键支持?
·5、存储的限制?
·6、对索引和缓存的支持?
这里也提供一张各种引擎对照表供你查看,一般来说 InnoDB 是个万金油式的选择,InnoDB 和 MyISAM 引擎最常用。