MySQL各存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
使用以下命令可以查看MySQL支持的引擎：

mysql> show engines;

一、InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计 用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流 行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。 □D＞如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去深入学习的对象， 收益肯定比将时间平均花在每个存储引擎的学习上要高得多。

InnoDB的历史

InnoDB有着复杂的发布历史，了解一下这段历史对于理解InnoDB很有帮助。2008年， 发布了所谓的InnoDB plugin,适用于MySQL 5.1版本，但这是Oracle创建的下一代 InnoDB引擎，其拥有者是InnoDB而不是MySQL。这基于很多原因，这些原因如果要一一道来，恐怕得喝掉好几桶啤酒。MySQL默认还是选择了集成旧的InnoDB引擎。当 然用户可以自行选择使用新的性能更好、扩展性更佳的InnoDB plugin来覆盖旧的版本。 直到最后，在Oracle收购了 Sun公司后发布的MySQL 5.5中才彻底使用InnoDB plugin 替代了旧版本的InnoDB (是的，这也意味着InnoDB plugin已经是原生编译了，而不是编译成一个插件，但名字已经约定俗成很难更改)。

这个现代的InnoDB版本，也就是MySQL 5.1中所谓的InnoDB plugin,支持一些新特性， 诸如利用排序创建索引(building index by sorting)、删除或者增加索引时不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式，以及文件格式管 理等。很多用户在MySQL 5.1中没有使用InnoDB plugin,或许是因为他们没有注意到有这个区别。所以如果你使用的是MySQL 5.1, 一定要使用InnoDB plugin,真的比旧版本的InnoDB要好很多。

InnoDB是一个很重要的存储引擎，很多个人和公司都对其贡献代码，而不仅仅是 Oracle公司的开发团队。一些重要的贡献者包括Google、Yasufumi Kinoshita、Percona,、Facebook等，他们的一些改进被直接移植到官方版本，也有一些由InnoDB团队重新实现。 在过去的几年间，InnoDB的改进速度大大加快，主要的改进集中在可测量性、可扩展性、 可配置化、性能、各种新特性和对Windows的支持等方面。MySQL 5.6实验室预览版 和里程碑版也包含了一系列重要的InnoDB新特性。

为改善InnoDB的性能，Oracle投入了大量的资源，并做了很多卓有成效的工作(外部贡献者对此也提供了很大的帮助)。在本书的第二版中，我们注意到在超过四核CPU的系统中InnoDB表现不佳，而现在已经可以很好地扩展至24核的系统，甚至在某些场景， 32核或者更多核的系统中也表现良好。很多改进将在即将发布的MySQL 5.6中引入， 当然也还有机会做更进一步的改善。

InnoDB概览

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

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

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

InnoDB内部做了很多优化，包括从磁盘读取数据时釆用的可预测性预读，能够自动在 内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够 加速插入操作的插入缓冲区(insert buffer)等。本书后面将更详细地讨论这些内容。

InnoDB的行为是非常复杂的，不容易理解。如果使用了 InnoDB引擎，笔者强烈建议阅 读官方手册中的"InnoDB事务模型和锁” 一节。如果应用程序基于InnoDB构建，则事 先了解一下InnoDB的MVCC架构带来的一些微妙和细节之处是非常有必要的。存储引 擎要为所有用户甚至包括修改数据的用户维持一致性的视图，是非常复杂的工作。

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

MylSAM存储引擎

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

存储

MylSAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。MylSAM表可以包含动态或者静态（长度固定）行。MySQL会根据表的定义来 决定采用何种行格式。MylSAM表可以存储的行记录数，一般受限于可用的磁盘空间， 或者操作系统中单个文件的最大尺寸。

在MySQL 5.0中，MylSAM表如果是变长行，则默认配置只能处理256TB的数据，因 .为指向数据记录的指针长度是6个字节。而在更早的版本中，指针长度默认是4字节，所以只能处理4GB的数据。而所有的MySQL版本都支持8字节的指针。要改变 MylSAM表指针的长度（调高或者调低），可以通过修改表的MAX_R0WS和AVG_R0W_ LENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致 重建整个表和表的所有索引，这可能需要很长的时间才能完成。

MylSAM特性

作为MySQL最早的存储引擎之一，MylSAM有一些已经开发出来很多年的特性，可以 满足用户的实际需求。

加锁与并发

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

修复

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

索引特性

对于MylSAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建

索引。MylSAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的 査询。

延迟更新索引键(Delayed Key Write)

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

MylSAM压缩表

如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合釆用- MylSAM压缩表。

可以使用myisampack对MylSAM表进行压缩(也叫打包pack)o压缩表是不能进行修 改的(除非先将表解除压缩，修改数据，然后再次压缩)。压缩表可以极大地减少磁盘 空间占用，因此也可以减少磁盘I/O,从而提升査询性能。压缩表也支持索引，但索引 也是只读的。

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

MylSAM性能

MylSAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。 MylSAM有一些服务器级别的性能扩展限制，比如对索引键缓冲区(key cache)的 Mutex锁，MariaDB基于段(segment)的索引键缓冲区机制来避免该问题。但MylSAM 最典型的性能问题还是表锁的问题，如果你发现所有的査询都长期处于“Locked”状态， 那么毫无疑问表锁就是罪魁祸首。

MySQL内建的其他存储引擎

MySQL还有一些有特殊用途的存储引擎。在新版本中，有些可能因为一些原因已经不 再支持;另外还有些会继续支持，但是需要明确地启用后才能使用。

Archive 引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。

Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MylSAM表的磁 盘I/O更少。但是每次SELECT査询都需要执行全表扫描。所以Archive表适合日志和 数据釆集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的 INSERT操作的场合下也可以使用。

Archive引擎支持行级锁和专用的缓冲区，所以可以实现高并发的插入。在一个査询开 始直到返回表中存在的所有行数之前，Archive引擎会阻止其他的SELECT执行，以实现 一致性读。另外，也实现了批量插入在完成之前对读操作是不可见的。这种机制模仿了 事务和MVCC的一些特性，但Archive引擎不是一个事务型的引擎，而是一个针对高速 插入和压缩做了优化的简单引擎。

Blackhole 引擎

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

CSV引擎

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

Federated 引擎

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

Memory引擎

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

Memroy表在很多场景可以发挥好的作用：

• 用于査找(lookup)或者映射(mapping)表，例如将邮编和州名映射的表。

• 用于缓存周期性聚合数据(periodically aggregated data)的结果。

• 用于保存数据分析中产生的中间数据。

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

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

人们经常混淆Memory表和临时表。临时表是指使用CREATE TEMPORARY TABLE语句 创建的表，它可以使用任何存储引擎，因此和Memory表不是一回事。临时表只在 单个连接中可见，当连接断开时，临时表也将不复存在。

Merge引擎

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

NDB集群引擎

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

第三方存储引擎

OLTP类引擎等不多介绍了。