
2.2 什么是存储引擎
MySQL中提到了存储引擎的概念。简而言之,存储引擎就是指表的类型。在具体开发时,为了提高MySQL数据库管理系统的使用效率和灵活性,可以根据实际需要来选择存储引擎。因为存储引擎指定了表的类型,即如何存储和索引数据、是否支持事务等,同时存储引擎也决定了表在计算机中的存储方式。
2.2.1 MySQL支持的存储引擎
用户在选择存储引擎之前,首先需要确定数据库管理系统支持哪些存储引擎。在MySQL数据库管理系统,通过SHOW ENGINES来查看支持的存储引擎,语法如下:
SHOW ENGINES;
在MySQL中执行SHOW ENGINES的结果如图2-7所示。

图2-7 查询数据库存储引擎
也可以通过以下语句来查询:
SHOW ENGINES \G;
查询结果如图2-8所示。

图2-8 查询数据库存储引擎
查询结果显示,MySQL 8支持9种存储引擎,分别为MEMORY、MRG_MYISAM、CSV、FEDERATED、PERFORMANCE_SCHEMA、MyISAM、InnoDB、BLACKHOLE和ARCHIVE。其中:
- Engine参数表示存储引擎名称。
- Support参数表示MySQL数据库管理系统是否支持该存储引擎:YES表示支持,NO表示不支持。
- DEFAULT表示系统默认支持的存储引擎。
- Comment参数表示对存储引擎的评论。
- Transactions参数表示存储引擎是否支持事务:YES表示支持,NO表示不支持。
- XA参数表示存储引擎所支持的分布式是否符合XA规范:YES表示支持,NO表示不支持。
- Savepoints参数表示存储引擎是否支持事务处理的保存点:YES表示支持,NO表示不支持。
在MySQL数据管理系统中,除了可以通过SQL语句SHOW ENGINES查看所支持的存储引擎外,还可以通过SQL语句SHOW VARIABLES来查看所支持的存储引擎,具体SQL语句如下,查询结果如图2-9所示。
SHOW VARIABLES LIKE 'have%';
在创建表时,若没有指定存储引擎,表的存储引擎将为默认的存储引擎。如果需要操作默认存储引擎,首先需要查看默认存储引擎。可以使用下面的SQL语句来查询默认存储引擎,执行结果如图2-10所示。
SHOW VARIABLES LIKE 'default_storage_engine';

图2-9 查询存储引擎

图2-10 查询默认存储引擎
在图2-10显示的结果中,Variable_name参数表示存储引擎的名字;Value参数表示MySQL数据库管理系统是否支持存储引擎,其中YES表示支持、NO表示不支持、DISABLE表示支持但还未开启。
如果想修改MySQL的默认存储引擎,可以通过修改MySQL数据库管理系统的my.cnf或者my.ini文件的配置来实现,如图2-11所示。首先关闭MySQL服务,然后打开my.ini进行编辑,配置默认存储引擎,如图2-12所示。

图2-11 my.ini配置文件

图2-12 配置默认存储引擎
修改好默认存储引擎后,保存文件,再重新开启MySQL服务。或者用以下SQL语句来修改默认存储引擎,修改完毕之后,再用SHOW语句去查询,结果如图2-13、图2-14所示。
SET DEFAULT_STORAGE_ENGINE=MyISAM; SHOW VARIABLES LIKE '%storage_engine%';

图2-13 设置默认存储引擎

图2-14 查看默认存储引擎
接下来简单介绍几种常见的存储引擎。
2.2.2 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存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制;缺点是其读写效率稍差,占用的数据空间相对比较大。
2.2.3 MyISAM存储引擎
MyISAM存储引擎是MySQL中常见的存储引擎,曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM增加了很多有用的扩展。
MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同,或站名包括frm、MYD和MYI。其中,frm为扩展名的文件存储表的结构;MYD为扩展名的文件存储数据,是MYData的缩写;MYI为扩展名的文件存储索引,是MYIndex的缩写。
基于MyISAM存储引擎的表支持3种存储格式,包括静态型、动态型和压缩型。其中,静态型为MyISAM存储引擎的默认存储格式,其字段是固定长度的;动态型包含变长字段,记录的长度不是固定的;压缩型需要使用myiampack工具创建,占用的磁盘空间较小。
MyISAM存储引擎的优势在于占用空间小,处理速度快;缺点是不支持事务的完整性和并发性。
2.2.4 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存储引擎时需要特别小心。
2.2.5 选择存储引擎
在具体使用MySQL数据库管理系统时,选择一个合适的存储引擎是非常复杂的问题。因为每种存储引擎都有自己的特性、优势和应用场合,所以不能随便选择存储引擎。为了能够正确地选择存储引擎,必须掌握各种存储引擎的特性。
下面从存储引擎的事务安全、存储限制、空间使用、内存使用、插入数据的速度和对外键的支持等角度来比较InnoDB、MyISAM和MEMORY,如表2-1所示。
表2-1 存储类型对比

表2-1给出了InnoDB、MyISAM、MEMORY这3种存储引擎特性的对比。下面根据其不同的特性,给出相应的建议。
(1)InnoDB存储引擎
InnoDB存储引擎支持事务处理,支持外键,同时支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高,要求实现并发控制,那么选择InnoDB存储引擎会有很大的优势。如果需要频繁地进行更新、删除操作的数据库,也可以选择InnoDB存储引擎。因为该类存储引擎可以实现事务的提交(Commit)和回滚(Rollback)。
(2)MyISAM存储引擎
MyISAM存储引擎的出入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择MyISAM存储引擎能实现处理的高效率。如果应用的完整性、并发性要求很低,也可以选择MyISAM存储引擎。
(3)MEMORY存储引擎
MEMORY存储引擎的所有数据都在内存中,数据的处理速度快,但安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,那么可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求,不能建立太大的表,所以使用于相对较小的数据库表中。
这些选择存储引擎的建议都是根据各种存储引擎的不同特点提出的,并不是绝对的,实际应用中还需要根据实际情况进行分析。
提示
在同一个数据库中,不同的表可以使用不同的存储引擎:如果一个表要求较高的事务处理,可以选择InnoDB;如果一个表会被频繁查询,可以选择MyISAM存储引擎;如果是一个用于查询的临时表,那么可以选择MEMORY存储引擎。