前言：和大多数数据库不同，MySQL 中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

0x00. 概述

MySQL 数据库最重要的一个特性是支持插件式存储引擎，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL 默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制性。 MySQL 5.0 支持的存储引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎都是非事务安全表。

0x01. 简单的引擎相关命令

1. 查看引擎信息

可以通过show engines命令查询当前数据库支持的存储引擎，下面以MySQL 5.7（Ubuntu）进行试验。可以看到MySQL 5.7版本支持的存储引擎包括：MyISAM、CSV、MRG_MYISAM、BLACKHOLE、PERFORMANCE_SCHEMA、InnoDB、ARCHIVE、MEMORY、FEDERATED ，其中默认的存储引擎是InnoDB，其他几列给出了这些引擎的一些概要的特性说明。

第二种方法是采用show variables like '%storage_engine%'命令搜索与存储引擎相关的信息，如下面的右图所示。据《深入浅出MySQL》这本书说，使用show variables like 'have%'可以查看当前版本支持的引擎类型，看一下它的环境是MySQL 5.0，而在5.7版本中显然这些信息已被去除了（如下面左图所示），只能看到一些关于安全、压缩和缓存之类的信息。

在创建新表的时候，可以通过增加 ENGINE 关键字设置新建表的存储引擎，例如，在下面的例子中，表 user 因为没有指明而使用默认的 InnoDB 存储引擎，而 city 表则是指明为 MyISAM 存储引擎的。当然也可以通过alter table user engine=MyISAM修改表定义将 user 表的存储引擎改为 MyISAM，这样它就可以使用 MyISAM 引擎相关的存储特性了，实验证明可以，在此不贴图。

2. 修改引擎信息

默认情况下，创建新表不指定表的存储引擎，则新表是默认存储引擎的，如果需要修改默认的存储引擎，可以通过命令set default_storage_engine = engine_name设置

还可以修改 MySQL 的配置文件my.cnf，下面使用locate命令定位该文件，可以看到其中的default-storage-enine为InnoDB，将其修改后保存退出。

重启mysql服务器：使用mysqladmin -u root -p shutdown或者service mysqld restart。登录mysql数据库，使用show engines;命令默认引擎是否修改成功。

0x02. 各引擎特性

1. 概述

下表概括对比了 MySQL 5.7 几个重要引擎的特性信息：

说明

• 主从复制由服务器而而不是引擎实现。
• MyISAM 的表压缩仅能使用行压缩格式，而且被压缩完的表只能读。
• 所有的引擎的数据加密都是由服务器通过调用加密函数实现的，在MySQL 5.7 及其往后的版本中InnoDB支持对静态的表空间进行加密。
• MySQL Cluster NDB 7.3 及其之后的版本才支持外键。
• InnoDB 在 MySQL 5.6 及其之后的版本才支持全文索引。
• InnoDB 在 MySQL 5.7 及其之后的版本才支持空间索引。
• InnoDB 在内部使用哈希索引实现适配性地哈希索引特性。

2. InnoDB

2.1 简介

InnoDB 是 MySQL 5.5 开始的默认事务型引擎，也是最重要、使用最广泛的存储引擎，是事务型数据库的首选引擎，支持ACID事务，特别地它被设计用来处理大量的短期事务（short-lived transaction，大部分情况是正常提交的，很少会被回滚），支持行级锁。InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

2.2 历史

• 现在的 InnoDB 的前身是2008年 InnoDB（而不是MySQL）发布的 InnoDB Plugin
• Oracle 收购了 Sun 公司后发布的 MySQL 5.5中才彻底使用 InnoDB plugin 替代了旧版本的 InnoDB，它已经开始支持一些新特性， 诸如利用排序创建索引（building index by sorting）、删除或者增加索引时不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式，以及文件格式管理等。
• 除了Oracle，Google、 Yasufumi Kinoshita、Percona、Facebook等团队也为 InnoDB 贡献了代码，这使得在过去的几年间，InnoDB 在可测量性、可扩展性、可配置化、性能、各种新特性和对Windows的支持等方面的提升速度大大加快。

2.3 概览

ImioDB 的数据存储在表空间(tablespace)中，表空间是由 InnoDB 管理的一个黑盒子，由一系列的数据文件组成。在MySQL 4.1 以后的版本中，InnoDB 可以将每个表的数据和索引存放在单独的文件中。ImioDB 也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要的选择。

InnoDB 采用 MVCC 来支持髙并发，并且实现了四个标准的隔离级别。其默认级別是REPEATABLE READ (可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得 InnoDB 不仅仅锁定査询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB 表是基于聚簇索引建立的。InnoDB 的索引结构和 MySQL 的其他存储引擎有很大的不同，聚簇索引对主键査询有很髙的性能。不过它的二级索引（secondary index, 非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。InnoDB 的存储格式是平台独立的，也就是说可以将数据和索引文件从 Intel 平台复制到 PowerPC 或者 Sun SPARC 平台。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建哈希索引以加速读操作的自适应哈希索引(adaptive hash index)，以及能够加速插入操作的插入缓冲区(insert buffer)等。

作为事务型的存储引擎，InnoDB 通过一些机制和工具支持真正的热备份，Oracle 提供的 MySQL Enterprise Backup、Percona 提供的开源的 XtraBackup 都可以做到这一点。MySQL 的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

3. MyISAM

在 MySQL 5.1 及之前的版本，MylSAM 是默认的存储引擎。MylSAM 提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但 MylSAM 不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。正是由于 MylSAM 引擎的缘故，即使 MySQL 支持事务已经很长时间了，在很多人的概念中 MySQL 还是非事务型的数据库。尽管 MylSAM 引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复(repair)操作.，则依然可以继续使用 MylSAM（但不要默认使用 MylSAM，而是应当默认使用 InnoDB）

3.1 存储

每个 MyISAM 表在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是

• .frm：存储表格式定义
• .MYD（MYData）：数据文件
• .MYI （MYIndex）：索引文件

怎么验证呢？首先搜索一下 MySQL 的表数据文件目录，可以看到是在/var/lib/mysql目录里，通过提权列出其中的文件，发现在刚才实验的test_engine的数据库中存在6个文件，其中因为city表是在创建的时候指定使用 MyISAM 引擎的，其表的存储也的确是分成了city.frm、city.MYD和city.MYI。

MylSAM 表可以包含动态或者静态（长度固定）行。MySQL 会根据表的定义来决定采用何种行格式。MylSAM 表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。在 MySQL 5.0 中，MylSAM 表如果是变长行，则默认配置只能处理256TB的数据，因为指向数据记录的指针长度是6个字节。而在更早的版本中，指针长度默认是4字节，所以只能处理4GB的数据。而所有的 MySQL 版本都支持8字节的指针。要改变 MylSAM 表指针的长度（调高或者调低），可以通过修改表的MAX_R0WS和AVG_R0W_LENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引，这可能需要很长的时间才能完成。

3.2 加锁与并发

MylSAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁， 写入时则对表加排他锁。但是在表有读取査询的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

3.3 修复

对于MylSAM表，MySQL可以手工或者自动执行检査和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检査表的错误，如果有错误可以通过执行REPAIR TABLE mytable进行修复。另外，如果MySQL服务器已经关闭，也可以通过 mysqlcheck 和 myisamchk 命令行工具进行检查和修复。

3.4 索引特性

对于 MylSAM 表，即使是 BLOB 和 TEXT 等长字段，也可以基于其前500个字符创建索引。MylSAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的査询。

延迟更新索引键(Delayed Key Write) ：创建 MylSAM 表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区(in-memory key buffer)，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。

3.5 压缩表

如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用 MylSAM 压缩表。可以使用对 MylSAM 表进行压缩（也叫打包pack)。压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O,从而提升査询性能。压缩表也支持索引，但索引也是只读的。

以现在的硬件能力，对大多数应用场景，读取压缩表数据时的解压带来的开销影响并不大，而减少I/O带来的好处则要大得多。压缩时表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表（甚至也不解压行所在的整个页面）。

可以使用 myisampack 命令行工具对 MylSAM 表进行压缩操作。

3,6 性能

MylSAM 引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。MylSAM 有一些服务器级别的性能扩展限制，比如对索引键缓冲区（key cache）的 Mutex 锁，MariaDB 基于段（segment）的索引键缓冲区机制来避免该问题。但 MylSAM 最典型的性能问题还是表锁的问题，表级锁的存在加大了封锁的粒度，系统开销较小，但是也减低了并发度，使得可能经常出现的情况是所有的査询都长期处于Locked状态。

4 其他引擎

4.1 Archive

Archive 存储引擎只支持INSERT和SELECT操作，在 MySQL 5.1 之前也不支持索引。Archive 引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比 MylSAM 表的磁盘I/O更少。但是每次SELECT査询都需要执行全表扫描。所以 Archive 表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的 INSERT 操作的场合下也可以使用。Archive 引擎支持行级锁和专用的缓冲区，所以可以实现高并发的插入。在一个査询开始直到返回表中存在的所有行数之前，Archive 引擎会阻止其他的 SELECT 执行，以实现一致性读。另外，也实现了批量插入在完成之前对读操作是不可见的。这种机制模仿了事务和 MVCC 的一些特性，但 Archive 引擎不是一个事务型的引擎，而是一个针对髙速插入和压缩做了优化的的简单引擎。

4.2 Blackhole

Blackhole 引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。

4.3 CSV

CSV 引擎可以将普通的 CSV 文件（逗号分割值的文件）作为 MySQL 的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将 Excel 等电子表格软件中的数据存储为 CSV 文件，然后复制到 MySQL 数据目录下，就能在 MySQL 中打开使用。同样，如果将数据写入到一个 CSV 引擎表，其他的外部程序也能立即从表的数据文件中读取 CSV 格式的数据。因此 CSV 引擎可以作为一种数据交换的机制，非常有用。

4.4 Federated

Federated 引擎是访问其他 MySQL 服务器的一个代理，它会创建一个到远程 MySQL 服务器的客户端连接，并将査询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如 Microsoft SQL Server 和 Oracle 的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。MadaDB 使用了它的一个后续改进版本，叫做 FederatedX。

4.5 Memory

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表（以前也叫做 HEAP 表）是非常有用的。Memory 表至少比 MylSAM 表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。Memory 表的结构在重启以后还会保留，但数据会丢失。Memroy表在很多场景可以发挥好的作用：

• 用于查找或者映射表，例如将邮编和州名映射的表。
• 用于缓存周期性聚合数据(periodically aggregated data)的结果。
• 用于保存数据分析中产生的中间数据。

Memory 表支持哈希索引，因此査找操作非常快。虽然 Memory 表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy 表是表级锁，因此并发写入的性能较低。它不支持 BLOB 或 TEXT 类型的列，并且每行的长度是固定的，所以即使指定了 VARCHAR 列，实际存储时也会转换成 CHAR，这可能导致部分内存的浪费（其中一些限制在 Percona 版本已经解决）。

如果 MySQL 在执行査询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是 Memory 表。如果中间结果太大超出了 Memory 表的限制，或者含有 BLOB 或 TEXT 字段，则临时表会转换成 MylSAM 表。

4.6 Merge

Merge 引擎是 MylSAM 引擎的一个变种。Merge 表是由多个 MylSAM 表合并而来的虚拟表。如果将 MySQL 用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃。

4.7 NDB 集群引擎

2003年，当时的 MySQL AB 公司从索尼爱立信公司收购了 NDB 数据库，然后开发了 NDB 集群存储引擎，作为 SQL 和 NDB 原生协议之间的接口。MySQL 服务器、NDB 集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB 数据库的组合，被称为MySQL集群(MySQL Cluster)。

0x03. 参考

• MySQL 5.7 官方文档: MySQL 5.7 Reference Manual
• 《高性能MySQL》（第三版，宁海元等，电子工业出版社）
• 《深入浅出MySQL-数据库开发、优化与管理维护》（第二版，唐汉明等，人民邮电出版社）

0x04. 相关文章

• InnoDB 存储引擎
• MyISAM 存储引擎