2.3 MySQL存储引擎
简单地说，存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。由于在关系型数据库中数据是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。但是，MySQL数据库提供了多种存储引擎，例如MyISAM、InnoDB和MEMORY等。甚至允许开发人员根据自己的需求编写自己的存储引擎。
下面将学习如何查看MySQL支持的存储引擎、每种存储引擎的作用，以及选择存储引擎的方法。
2.3.1 MySQL存储引擎简介
在MySQL最开始发行的时候是ISAM存储引擎，而且实际上在最初的时候，MySQL甚至是没有存储引擎这个概念的。MySQL在架构上面也没有像现在这样的SQL Layer和Storage Engine Layer这两个结构清晰的层次结构，当时不管是代码本身还是系统架构，对于开发者来说都很痛苦的一件事情。到后来，MySQL意识到需要更改架构，将前端的业务逻辑和后端数据存储以清晰的层次结构拆分开的同时，对ISAM做了功能上面的扩展和代码的重构，并改名为MyISAM存储引擎。在之后的MySQL版本中默认存储引擎都是MyISAM。
MySQL在5.1（不包括）之前的版本中，存储引擎是需要在MySQL安装的时候就必须和MySQL一起被编译并同时被安装的。也就是说，5.1之前的版本中，虽然存储引擎层和SQL层的耦合已经非常少了，基本上完全是通过接口来实现交互，但是这两层之间仍然是没办法分离的，即使在安装的时候也是一样。
但是从MySQL 5.1开始，MySQL AB对其结构体系做了较大的改造，并引入了一个新的概念：插件式存储引擎体系结构。MySQL AB在架构改造的时候，让存储引擎层和SQL层各自更为独立，耦合更小，甚至可以做到在线加载新的存储引擎，也就是完全可以将一个新的存储引擎加载到一个正在运行的MySQL中，而不影响MySQL的正常运行。插件式存储引擎的架构，为存储引擎的加载和移出更为灵活方便，也使自行开发存储引擎更为方便简单。在这一点上面，目前还没有哪个数据库管理系统能够做到。
MySQL的插件式存储引擎主要有MyISAM、InnoDB、NDB Cluster、Maria、Falcon、Memory、Archive、Merge和Federated等，其中最著名而且使用最为广泛的是MyISAM和InnoDB两种存储引擎。MyISAM是MySQL最早的ISAM存储引擎的升级版本，也是MySQL默认的存储引擎。而InnoDB实际上并不是MySQL公司的，而是第三方软件公司Innobase（在2005年被Oracle公司所收购）所开发，其最大的特点是提供了事务控制等特性，所以使用也非常广泛。
其他的存储引擎使用场景要稍微少一些，都是应用于某些特定的场景，如NDBCluster虽然也支持事务，但是主要是用于分布式环境。Maria是MySQL最新开发（还没有发布最终的GA版本）的对MyISAM的升级版存储引擎。Falcon是MySQL公司自行研发的用于替代当前InnoDB存储引擎的一款带有事务等高级特性的数据库存储引擎，目前正在研发阶段。Memory存储引擎所有数据和索引均存储于内存中，所以主要是用于一些临时表，或者对性能要求极高的表。Archive是一个数据经过高比例压缩存放的存储引擎，主要用于存放过期而且很少访问的历史信息，不支持索引。Merge和Federated 在严格意义上来说，并不能算作一个存储引擎。因为Merge存储引擎主要用于将几个基表合并到一起，对外作为一个表来提供服务，基表可以基于其他的几个存储引擎。而Federated实际上所做的事情，有点儿类似于Oracle的dblink，主要用于远程存取其他MySQL服务器上面的数据。
2.3.2 查看MySQL存储引擎
查看MySQL支持存储引擎的方法非常简单，语法如下：
SHOW ENGINES;
【范例1】
在命令行下登录到MySQL并执行SHOW ENGIENS命令查看所有存储引擎，输出结果如下。
mysql> show engines\G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it
disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
提示
MySQL 每条命令默认以分号“;”作为结束，这里使用“\G”可以让结果显示得更加美观。
从上述输出结果中可以知道，InnoDB是默认的数据库存储引擎。除此之外，当前版本的MySQL数据库还支持FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE和PERFORMANCE_SCHEMA存储引擎。查询结果的每条记录都包含相同的列，下面是列的说明。
（1）Engine：数据库存储引擎名称。
（2）Support：表示MySQL是否支持该类引擎，YES表示支持，NO表示不支持。
（3）Comment：表示对该引擎的注释信息。
（4）Transactions：表示是否支持事务处理，YES表示支持，NO表示不支持。
（5）XA：表示是否分布式交易处理的XA规范，YES表示支持，NO表示不支持。
（6）Savepoints；表示是否支持保存点，以便事务回滚到保存点，YES表示支持，NO表示不支持。
【范例2】
SHOW ENGINES命令会返回所有的存储引擎，如果只希望查看当前MySQL的默认存储引擎，可以使用如下命令。
SHOW VARIABLES LIKE 'storage_engine';
输出的结果如下，可以看出默认存储引擎为InnoDB。
mysql> SHOW VARIABLES LIKE 'storage_engine';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| storage_engine | InnoDB |
+------------------------+-------------+
2.3.3 MyISAM存储引擎
2.3.1节介绍了MyISAM存储引擎是由早期的ISAM存储引擎升级而来，提供了高速存储和检索，以及全文搜索能力。同时也是MySQL的默认存储引擎，但是它并不支持事务。
MyISAM引擎的表存储为三个文件，其文件名称与表名相同，但是后缀名分别以“.myd”、“.myi”和“.frm”结尾。其中，“.myd”为后缀名的文件存储数据；“.myi”为后缀名的文件存储索引，它是MYIndex的缩写；“.frm”为后缀名的文件存储表的结构。
MyISAM支持三种不同的存储格式，即静态型、动态型和压缩型。其中，前两种格式根据正使用的列的类型来自动选择，而第三个是已压缩格式，只能使用myisampack工具进行创建。
1．静态固定长度
这是一种默认的存储格式，其字段都是非变长的字段，即字段都是固定的。在存储时，列的宽度不足时，会自动使用空格补足。但是，在访问时并不会得到这些空格。使用这种存储格式的优点是：存储非常迅速，容易缓存，并且在出现故障时容易恢复。但是，这种格式也存在着缺点，例如占用的空间通常比动态表多。
2．动态可变长度
在这种存储格式中，其字段是可变长的，即记录的长度是不固定的。使用这种存储格式时，虽然占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期改善性能，并且出现故障的时候恢复相对比较困难。
3．压缩表
压缩表占用磁盘空间小，每个记录是被单独压缩的，所以只有非常小的访问开支。
2.3.4 InnoDB存储引擎
InnoDB是当前MySQL数据库版本的默认存储引擎，它与MyISAM相比，其最大特色就是支持ACID兼容的事务功能。目前，InnoDB采用双轨制授权，一个是GPL授权，另一个则是专有软件授权。
InnoDB存储引擎给MySQL数据库提供了具有事务、回滚和崩溃修复能力、多版本并发控制的事务安全型表。InnoDB存储引擎也提供了行级锁，提供与Oracle类似的不加锁读取。它的缺点在于：读写效率稍差，占用的数据空间相对较大。
InnoDB是MySQL数据库中第一个提供外键约束的表引擎，而且它对于事务的处理能力，也是其他存储引擎所无法比拟的。如下通过三个方面介绍了InnoDB存储引擎的特点。
（1）InnoDB存储引擎中存储表和索引有两种方式：使用共享表空间存储和使用多表空间存储。
①共享表间空间存储。
表结构存储在后缀名是“.frm”的文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
②多表空间存储。
表结构存储在后缀名是“.frm”的文件中，但是每个表的数据和索引单独保存在“.ibd”文件中。如果为分区表，则每个分区表对应单独的“.ibd”文件，文件名是表名+分区名。使用多表空间存储需要设置参数innodb_file_per_table，并且重启服务才能生效，只对新建表有效。
（2）InnoDB存储引擎支持外键，外键所在的表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。如果删除、修改父表中的某条信息时，子表也必须有相应的改变。
（3）InnoDB存储引擎支持自动增长列AUTO_INCREMENT，自动增长列的值不能为空，而且值必须是唯一的。另外，在MySQL中规定自增列必须为主键，在插入值时，自动增长列分为以下三种情况。
①如果自动增长列不输入值，则插入的值为自动增长后的值。
②如果输入的值为0或空（NULL），则插入的值也为自动增长后的值。
③如果插入某个确定的值，且该值在前面的数据中没有出现过，则可以直接插入。
2.3.5 MEMORY存储引擎
MEMORY存储引擎会将表中的数据存放在内存，如果数据库重启或发生崩溃，表中的数据都将消失。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。
如果数据库重启或发生崩溃，MEMORY存储引擎表中的数据都将会消失。因此，它非常适合用于存储临时数据的临时表，以及数据仓库中的表。默认情况下，MEMORY存储引擎使用的是哈希（HASH）索引，而不是BTREE索引。
提示
哈希索引的速度要比BTREE索引快，如果读者希望使用BTREE索引，那么可以在创建索引时选择使用。
虽然MEMORY存储引擎速度非常快，但是在使用上有一定的限制。例如，该存储引擎只支持表锁、并发性能较差，而且不支持TEXT和BLOB列类型。最重要的是，存储变长字段（VARCHAR）时是按照固定长度字段（CHAR）的方式进行的，这样会导致内存的浪费。
另外，还有一点是不能忽视的，MySQL数据库使用MEMORY存储引擎作为临时表来存放查询的中间结果集。如果中间结果集大于MEMORY存储引擎表的容量设置，或中间结果含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘。
2.3.6 其他存储引擎
除了上面介绍的三种存储引擎外，下面对MySQL数据库支持的其他存储引擎进行简单的说明。
1．PERFORMANCE_SCHEMA存储引擎
PERFORMANCE_SCHEMA是MySQL在5.5版本中新增的一个存储引擎，主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表的。
PERFORMANCE_SCHEMA存储引擎提供了以下三个功能。
（1）提供进程等待的详细信息，包括锁、互斥变量和文件信息等。
（2）保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断。
（3）对于新增和删除监控事件点都非常容易，并且可以随意改变MySQL服务器的监控周期。
例如，下面的代码检查数据库是否启动performance_schema功能，如果返回的结果为ON则表示开启，如果为OFF则表示功能处于关闭状态，代码如下。
mysql> SHOW VARIABLES LIKE 'performance_schema';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| performance_schema | ON |
+------------------------------+---------+
2．BLACKHOLE存储引擎
BLACKHOLE存储引擎还被称为“黑洞引擎”，它能够接收数据，但是会丢弃数据而非存储它。使用该存储引擎写入的任何数据都会消失，不进行实际存储。它虽然不能存储数据，但是MySQL还是会正常地记录下日志，而且这些日志还会被正常地同步到Slave上，可以在Slave上对数据进行后续处理。
BLACKHOLE存储引擎一般用于以下三种场合。
（1）验证转储文件语法的正确性。
（2）来自二进制日志记录的开销测量，通过比较开启或者禁用二进制日志的BLACKHOLE功能。
（3）被用来查找与存储引擎自身不相关的性能瓶颈。
3．CSV存储引擎
逻辑上由逗号分隔数据的存储引整，它会在数据库子目录里为每个数据表创建一个后缀名是“.csv”的文件。这是一种普通的文本文件，每个数据行占用一个文本行，而且CSV存储引擎不支持索引。
4．MRG_MYISAM存储引擎
MRG_MYISAM存储引擎也叫MERGE存储引擎，它允许集合将被处理同样（指所有表同样的列和索引信息）的MyISAM表作为一个单独的表。
5．ARCHIVE存储引擎
ARCHIVE存储引擎被用来以非常小的覆盖区存储大量无索引数据。这种存储引擎拥有高效的插入速度，但是对查询的支持相对较差。
2.3.7 如何选择存储引擎
实际工作中选择一个合适的存储引擎是一个很复杂的问题，每种存储引擎都有各自的优势。因此，不能笼统地说哪个存储引擎更好，只有说合适不合适。下面分别介绍了InnoDB、MyISAM和MEMORY这三种存储引擎的特性对比，根据其不同的特性，给出了一些选择的建议。
1．InnoDB存储引擎
它主要用于事务处理应用程序，支持外键，同时还支持崩溃修复能力和并发控制。如果对事务的完整性要求比较高，要求实现并发通知，那么选择InnoDB存储引擎比较有优势。如果需要频繁地进行更新和删除操作，也可以选择该存储引擎，因为该存储引擎可以实现事务的提交和回滚。
2．MyISAM存储引擎
这种存储引擎提供了高速的存储与检索和全文搜索能力。该存储引擎插入数据快，但是空间和内存的使用效率较低。如果表主要适用于插入新记录和读出记录，那么选择MyISAM存储引擎可以实现处理的高效率。
3．MEMORY存储引擎
这种存储引擎提供“内存中”表，该存储引擎的所有数据都存储在内存中，数据的处理速度很快但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择这种存储引擎。
注意
MEMORY存储引擎对表的大小有要求，不建议太大的表。因此，这类数据库只使用于相对较小的数据库表。