InnoDB 是 MySQL 默认的事务型引擎,也是最重要、最广泛的存储引擎。它的设计是用来处理大量短期事务,短期事务大部分是正常提交的,很少回滚。InnoDB 的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中,也很流行。除了非常特别的原因需要使用其他引擎,InnoDB 也是非常好值得花时间研究的对象。
InnoDB 的数据存储在表空间中,表空间是由 InnoDB 管理的黑盒文件系统,由一系列系统文件组成。InnoDB 可以将每个表的数据和索引存放在单独的文件中。InnoDB 也可以使用裸设备作为表空间存储介质。
InnoDB 通过间隙锁(next-key locking)防止幻读的出现。InnoDB 是基于聚簇索引建立,与其他存储引擎有很大的区别,聚簇索引对主键查询有很高的性能,不过它的二级索引(secondary index,非主键索引)必须包含主键列。所以如果主键列很大的话,索引会很大。
在 5.1 之前,MyISAM 是默认的引擎,MyISAM 有大量的特性,包括全文索引、压缩、空间函数。但是 MyISAM 不支持事务和行级锁,而且在崩溃后无法安全恢复。即使后续版本中 MyISAM 支持了事务,但是很多人的概念中依然是不支持事务的引擎。
MyISAM 并不是无所事处。对于一些只读数据,或者表空间较小,可以忍受恢复操作,可以使用 MyISAM。MyISAM 会将表存储在两个文件中:数据文件、索引文件。分别是 .MYD、.MYI 扩展名。MyISAM 表可以包含动态或者静态行。MySQL 会根据表定义选择那种行格式。MyISAM 表的行记录数,取决于磁盘空间和操作系统中的单个文件最大尺寸。
在 MySQL 中,默认配置只能存储 256TB 的数据。因为指向数据记录的指针长度是 6 字节。需要修改可以修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项。两个相乘是最大的大小。会导致重建索引。
MyISAM 是对整个表加锁,而不是行锁,读取的时候对表加共享锁,写入的时候加排他锁。但是在表有读取查询的同时,也可以往表内写入记录。
对于 MyISAM,即使是 Blob,Text 等等长字段,也可以基于前 500 字符创建索引,MyISAM 支持全文索引,这是一个基于分词创建的索引,也可以支持复杂的查询。
MyISAM 可以选择延迟更新索引键,在创建表的时候指定 delay_key_write 选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘,而是写到缓存区,只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能,但是在主机崩溃时会造成索引损坏,需要执行修复操作。
MyISAM 另一个特性是支持压缩表。如果数据在写入后不会修改,那么这个表适合 MyISAM 压缩表。可以使用 myisampack 对 MyISAM 表进行打包,压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用,因此可以减少磁盘 IO,提升性能,压缩表也支持索引,但是索引也是只读的。
整体来说 MyISAM 并没有那么不堪,但是由于没有行锁机制,所以在海量写入的时候,会导致所有查询处于 Locked 状态。
Archive 引擎支持是 Insert,Select 操作,现在支持索引,Archive 引擎会缓存所有的写,并利用 zlib 对写入行进行压缩,所以比 MyISAM 表的磁盘 IO 更少。但是在每次 Select 查询都需要执行全表扫描。所以在 Archive 适合日志和数据采集应用。这类应用在分析时往往需要全表扫描忙活着更快的 Insert 操作场景中也可以使用。
Archive 引擎支持行级锁和专用的缓存区,所以可以实现高并发写入,在查询开始到返回表存在的所有行数之前,Archive 会阻止其他 Select 执行,用来实现一致性读。另外也实现了批量写入结束前批量写入数据对读操作不可见,这种机制模仿了事务和 MVCC 的特性,但是 Archive 不是一个事务型引擎,而是针对高写入压缩做了优化的简单引擎。
Blackhole 没有实现任何存储机制,它会舍弃所有写入数据,不做任何保存,但是服务器会记录 Blackhole 表的日志,用于复制数据到备库,或者只是简单的记录到日志,这种特殊的存储引擎可以在一些特俗的复制架构和日志审核时发挥作用。但是不推荐。
Federated 引擎是访问其他 MySQL 服务器的一个代理,它会创建一个到远程 MySQL 服务器的客户端连接,并将查询传输到远程服务器执行,然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如 MicrosoftSQLServer 和 Oracle 的类似特性竞争的,可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用 Memory 表(以前也叫做 HEAP 表)是非常有用的。Memory 表至少比 MyISAM 表要快一个数量级,因为所有的数据都保存在内存中,不需要进行磁盘 I/O。Memory 表的结构在重启以后还会保留,但数据会丢失。
Merge 引擎是 MyISAM 引擎的一个变种。Merge 表是由多个 MyISAM 表合并而来的虚拟表。如果将 MySQL 用于日志或者数据仓库类应用,该引擎可以发挥作用。但是引入分区功能后,该引擎已经被放弃。
NDB 集群存储引擎,作为 SQL 和 NDB 原生协议之间的接口。MySQL 服务器、NDB 集群存储引擎,以及分布式的、share-nothing 的、容灾的、高可用的 NDB 数据库的组合,被称为 MySQL 集群(MySQLCluster)。