存储引擎
InnoDB存储引擎
InnoDB是MySQL的 默认事务型引擎,也是最重要、使用最广泛的存储引擎。它被设计用来 处理大量的短期事务,短期事务大部分情况是正常提交的,很少会被回滚。 InnoDB的性能和自动奔溃恢复特性,使得它在非事务存储的需求中也很流行。
InnoDB的数据存储在 表空间 中,表空间是由InnoDB管理的一个黑盒子,由一系列的数据文件组成。
InnoDB采用 MVCC(多版本并发控制),并且实现了四个标准的 隔离级别。其默认级别是 可重复读(REPEATABLE_READ),并且通过 间隙锁(next-key locking)策略防止幻读 的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中间隙进行锁定,以防止幻影行的插入。
InnoDB是基于 聚簇索引 建立的,InnoDB的索引结构和MySQL的其他存储引擎有很大的不同,聚簇索引对主键查询有很高的性能。不过它的二级索引(非主键索引)中必须包含主键列,所以如果 主键列很大的话,其他所有的索引都会很大。因此,若表上索引较多的话,主键应当尽可能的小。
InnoDB内部做了很多优化,能够自动在内存中创建 hash索引 以加速读操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区等。
MyISAM存储引擎
在MySQL5.1及之前的版本,MyISAM是默认的存储引擎。MyISAM提供了大量的特性,包括全文检索、压缩、空间函数(GIS)等,但 MyISAM不支持事务和行级锁,而且崩溃后无法安全恢复。
MyISAM会将表存储在两个文件中:**数据文件和索引文件。**MyISAM表可以包含动态或者静态(长度固定)行。
MyISAM压缩表
如果表在创建并导入数据后,不会再进行修改操作,那么这样的表或许适合采用 MyISAM压缩表。压缩表是不能进行修改的(除非先将表解除压缩,修改数据,然后再次压缩)。
压缩表可以极大地减少磁盘空间占用,因此也可以减少磁盘I/O,从而提升查询性能。压缩表也支持索引,但索引也是只读的。
压缩表中的记录是独立压缩的,所以读取单行的时候不需要去解压整个表(甚至也不解压行所在的整个页面)。
MyISAM性能
MyISAM引擎设计简单,数据以紧密格式存储,所以在某些场景下的性能很好。MyISAM有一些服务器级别的性能扩展限制,比如对索引键缓冲区的Mutex锁,MariaDB基于段(segement)的索引键缓冲区机制来避免该问题。但MyISAM最典型的性能问题还是表锁的问题,如果所有的查询都长期处于“Locked”状态,那么表锁就是罪魁祸首。
MySQL内建的其他存储引擎
- Archive引擎:只支持INSERT和SELECT操作,MySQL5.1之前也不支持索引。
- Blackhole引擎:没有实现任何的存储机制,会丢弃所有插入的数据,不做任何保存。
- CSV引擎:可以将普通的CSV文件(逗号分隔值的文件)作为MySQL的表来处理,也不支持索引。可以作为一种数据交换的机制。
- Federated引擎:是访问其他MySQL服务器的一个代理,会创建一个到远程MySQL服务器的客户端连接,并将查询传输到远程服务器执行,提取或发送需要的数据。
- Memory引擎:快速访问数据,且数据不会被修改,重启后丢失。比MyISAM快一个数量级。支持hash索引,Memory是表级锁,并发写入性能较低。
- Merge引擎:是MyISAM的一个变种。Merge由多个MyISAM表合并而来的虚拟表。该引擎已被放弃
- NDB集群引擎:作为SQL和NDB原生协议之间的接口,MySQL服务器、NDB集群存储引擎,以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合,被称为MySQL集群。
如何选择合适的引擎?
除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎。
例如,如果要用到 全文索引,建议使用 InnoDB+Sphinx 的组合,而不选择支持全文索引的 MyISAM