3.3　什么是存储引擎

MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。在具体开发时，为了提高MySQL数据库管理系统的使用效率和灵活性，可以根据实际需要来选择存储引擎。存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。

3.3.1　MySQL支持的存储引擎

用户在选择存储引擎之前，首先需要确定数据库管理系统支持哪些存储引擎。在MySQL数据库管理系统中，通过SHOW ENGINES来查看支持的存储引擎，语法如下：

     SHOW ENGINES;

在MySQL中执行SHOW ENGINES的结果如图3-16所示。

图3-16　查询数据库存储引擎

也可以通过以下语句来查询：

     SHOW ENGINES \G;

查询结果如图3-17所示。

图3-17　查询数据库存储引擎

查询结果显示，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语句如下：

     SHOW VARIABLES LIKE 'have%';

查询结果如图3-18所示。

在创建表时，若没有指定存储引擎，表的存储引擎将为默认的存储引擎。如果需要操作默认存储引擎，首先需要查看默认存储引擎，读者可以使用下面的SQL语句来查询默认存储引擎：

     SHOW VARIABLES LIKE 'storage_engine';

执行结果如图3-19所示。

图3-18　查询存储引擎

图3-19　查询默认存储引擎

在图3-18显示的结果中，Variable_name参数表示存储引擎的名字；Value参数表示MySQL数据库管理系统是否支持存储引擎，其中YES表示支持，NO表示不支持，DISABLED表示支持但还未开启。

如果想修改MySQL的默认存储引擎，可以通过修改MySQL数据库管理系统的my.cnf或者my.ini文件的配置来实现，如图3-20所示，首先要关闭MySQL服务。打开my.ini进行编辑，配置默认搜索引擎，如图3-21所示。

图3-20　my.ini配置文件

图3-21　配置默认存储引擎

修改好默认存储引擎后，保存文件，再重新开启MySQL服务。或者用以下SQL语句来修改默认存储引擎：

     SET DEFAULT_STORAGE_ENGINE=MyISAM;
     SHOW VARIABLES LIKE '%storage_engine%';

修改完毕之后，再用SHOW语句查询，结果如图3-22和图3-23所示。

图3-22　设置默认存储引擎

图3-23　查看默认存储引擎

接下来简单介绍几种常见的存储引擎。

3.3.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存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写效率稍差，占用的数据空间相对比较大。

3.3.3　MyISAM存储引擎

MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM增加了很多有用的扩展。

MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同，扩展名包括frm、MYD和MYI。其中，frm为扩展名的文件存储表的结构；MYD为扩展名的文件存储数据，其是MYData的缩写；MYI为扩展名的文件存储索引，其是MYIndex的缩写。

基于MyISAM存储引擎的表支持3种存储格式，包括静态型、动态型和压缩型。其中，静态型为MyISAM存储引擎的默认存储格式，其字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要使用myisampack工具创建，占用的磁盘空间较小。

MyISAM存储引擎的优点在于占用空间小，处理速度快；缺点是不支持事务的完整性和并发性。

3.3.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存储引擎时需要特别小心。

3.3.5　选择存储引擎

在具体使用MySQL数据库管理系统时，选择一个合适的存储引擎是一个非常复杂的问题。因为每种存储引擎都有自己的特性、优势和应用场合，所以不能随便选择存储引擎。为了能够正确地选择存储引擎，必须掌握各种存储引擎的特性。

下面从存储引擎的事务安全、存储限制、空间使用、内存使用、插入数据的速度和对外键的支持等角度来比较InnoDB、MyISAM和MEMORY，如表3-1所示。

表3-1 存储类型对比

表3-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存储引擎。