MySQL支持多种存储引擎,其中最常用的有InnoDB、MyISAM。我们可以通过show engines来查看当前数据库所支持的存储引擎。
使用show engines可以看到默认的存储引擎是InnoDB。其实MySQL 5.5版本之前,使用MyISAM作为默认的存储引擎,5.5版本之后采用InnoDB作为默认存储引擎。
除了show engines查看存储引擎外,还可以show variables like '%engine%'命令查看MySQL当前默认的存储引擎。
InnoDB存储引擎官方文档:MySQL :: MySQL 8.0 Reference Manual :: 17 The InnoDB Storage Engine
首先,所有的存储引擎中只有 InnoDB 是事务性存储引擎,也就是说只有 InnoDB 支持事务。除了事务属性外,还有其他不一样的地方。
InnoDB支持事务,MyISAM不提供事务支持。InnoDB 实现了 SQL 标准,定义了四个隔离级别,具有commit和rollback事务的能力。InnoDB 默认使用的REPEATABLE-READ(可重复读)隔离级别是可以解决幻读问题发生的。
InnoDB支持行级锁和表级锁,默认为行级锁。MyISAM只支持表级锁。正是由于MyISAM只支持表级锁,因此他在并发时的写性能要远远低于InnoDB。
InnoDB大家都知道是支持外键的,但MyISAM不支持。外键可以增加数据的约束性,保证数据强一致性,但是在性能上会有一些损耗。
由于MyISAM不支持行级锁,因此肯定不支持MVCC,InnoDB采用MVCC来支持高并发,并实现了四个标准的隔离级别。默认级别是REPEATABLE-READ(可重复读),并且通过间隙锁策略防止幻读的出现。
借助于redo log,InnoDB支持数据安全恢复。即当数据库出现异常崩溃后,重新启动时会保证数据库恢复到崩溃前的状态。而MyISAM不支持。
众所周知,InnoDB使用的是B+Tree作为索引结构,类似的MyISAM也是使用的B+Tree。但是InnoDB,其数据文件本身就是索引文件。而MyISAM中,索引文件和数据文件是分离的,其表数据文件本身是按B+Tree组织的一个索引结构,树的叶子节点保存了完整的数据记录。
MyISAM支持全文索引,这是一种基于分词创建的索引,可以支持复杂的查询。
InnoDB是基于聚簇索引建立的,聚簇索引堆逐渐查询有很高的性能,不过他的二级索引(非主键索引)中必须包含主键列,所以如果主键很大的话,其他的所有索引都会很大。因此,如果表上的索引较多,主键应该尽可能小。
MyISAM由于引擎设计简单,数据以紧密格式进行粗出,所以在某些场景下性能很好。但是其典型的问题还是表锁的问题,由于不支持行级锁,所有的查询都会进行锁表(读表时加共享锁,写表时加排他锁)而长期处于Locked状态。在CPU多核心数的加持下,由于InnoDB支持行级锁,其并发读写能力呈线性增长。MyISAM不支持并发,所以他的处理能力在CPU多核心数下显得心有余力不足。
大多数情况下,InnoDB都是正确的选择,所以Oracle在MySQL 5.5版本时终于将InnoDB作为默认的存储引擎了。
在《高性能MySQL》一书上提到“除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎”。如要用到全文索引,建议优先考虑InnoDB加上Sphinx的组合,而不是直接考虑使用支持全文索引的MyISAM。
如果实在不得已需要使用不同的存储引擎,最好优先考虑以下几个因素:
最后,总结一下两者的差异: