MySql – 浅谈MyISAM与InnoDB的区别及选择

目录

1.MyISAM

1.1 MyISAM数据存储形式

1.2 锁的粒度

1.3 事务

2.InnoDB

2.1 InnoDB数据存储形式

2.2 锁的粒度

2.3 事务

2.4 数据的存储特点

3.MyISAM与InnoDB的区别

3.1 MyISAM优点

3.2 MyISAM缺点

3.3 InnoDB优点

3.4 InnoDB缺点

3.5 MyISAM与InnoDB的区别

3.6 表格对比

4.MyISAM与InnoDB适用场景

---

1.MyISAM

1.1 MyISAM数据存储形式

       MyISAM是MySQL关系数据库5.5版本之前的默认储存引擎（5.5之后改为InnoDB）。MyISAM采用的是索引与数据分离的形式（非聚簇索引），将数据保存在尾缀为.frm，.MYD，.MYI的三个文件中。

       在查询数据用到索引时，先到.MYI（索引树）文件中进行查找，取到数据所在.MYD（数据文件）的行位置，最终拿到数据。因为MyISAM存储引擎的索引文件和数据文件是独立分开的，所以称之为非聚簇索引。

1.2 锁的粒度

       MyISAM不支持行锁，所以读取时对表加上共享锁，在写入是对表加上排他锁。由于是对整张表加锁，相比InnoDB，在并发写入时效率很低。

1.3 事务

       MyISAM不支持事务。

2.InnoDB

InnoDB是默认的事务型存储引擎，也是最重要，使用最广泛的存储引擎。在没有特殊情况下，一般优先使用InnoDB存储引擎。

2.1 InnoDB数据存储形式

       使用InnoDB时，会将数据表分为.frm 和 .idb两个文件进行存储。

2.2 锁的粒度

       InnoDB支持行锁，采用MVCC(多版本并发控制)来支持高并发，InnoDB实现了四个隔离级别，默认级别是REPETABLE READ。

2.3 事务

       InnoDB是典型的事务型存储引擎

2.4 数据的存储特点

       InnoDB表是基于聚簇索引建立的，它的数据文件和索引文件是同一个文件。聚簇索引指向主键对数据的引用，非主键索引则指向对主键的引用。聚簇索引对主键的查询有很高的性能，即在查询过程中，找到了索引，便找到了数据文件，不过他的二级索引（非主键索引）必须包含主键列，索引其他的索引会很大。InnoDB中，没有主键索引，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的id来当作主键索引。

3.MyISAM与InnoDB的区别

3.1 MyISAM优点

1. 高性能的读取速度
2. 保存了表的行数，当使用count统计的时候不会扫描全表

3.2 MyISAM缺点

1. 缺点是为表级别锁，粒度大，发生锁冲动概率较高，容纳并发能力低。
2. 不支持事务。

3.3 InnoDB优点

1. 支持事务，支持ACID特性
2. 实现了SQL标准的四种隔离级别
3. 支持行级锁和外键约束
4. 可以利用事务日志进行数据恢复

3.4 InnoDB缺点

1. 因为没有保存表的行数，当使用COUNT统计时会扫描全表。

3.5 MyISAM与InnoDB的区别

1. InnoDB支持事务，MyISAM不支持事务。InnoDB对于每一条SQL都默认封装成事务，自动提交，这也是为什么比MyISAM查询慢的主要原因。
2. InnoDB支持外键，MyISAM不支持外键。对于一个包含外键存储引擎为InnoDB的表转换MyISAM会失败。
3. InnoDB为聚簇索引，MyISAM为非聚簇索引。
4. InnoDB不保存表的具体行数，count（*）需要扫描全表，MyISAM使用变量保存了整个表的行数，执行count（*）只需要读取该变量即可。
5. InnoDB不支持全文索引，MyISAM支持全文索引。

3.6 表格对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁	行锁
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还要缓存真实数据
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

4.MyISAM与InnoDB适用场景

MyISAM：

1. 做很多count的计算；
2. 插入不频繁，应用中需要执行大量的SELECT，查询非常频繁；
3. 不需要事务；
4. 系统崩溃后，数据恢复困难，是否可以接受

InnoDB：

1. 可靠性要求比较高，或者要求事务；
2. 应用中需要执行大量的INSERT或UPDATE操作，表更新和查询都相当的频繁；