MySQL中InnoDB和MyISAM的存储引擎的差异

作为DBA，我们应该深刻的认识存储引擎。今天介绍两种最常见的存储引擎和它们的区别：InnoDB和MyISAM。

InnoDB存储引擎

InnoDB存储引擎支持事务，其设计目标主要就是面向OLTP（On Line Transaction Processing 在线事务处理）的应用。特点为行锁设计、支持外键，并支持非锁定读。从5.5.8版本开始，InnoDB成为了MySQL的默认存储引擎。

InnoDB存储引擎采用聚集索引（clustered）的方式来存储数据，因此每个表都是按照主键的顺序进行存放，如果没有指定主键，InnoDB会为每行自动生成一个6字节的ROWID作为主键。

MyISAM存储引擎

MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向OLAP（On Line Analytical Processing 联机分析处理）应用，适用于数据仓库等查询频繁的场景。在5.5.8版本之前，MyISAM是MySQL的默认存储引擎。该引擎代表着对海量数据进行查询和分析的需求。它强调性能，因此在查询的执行速度比InnoDB更快。

InnoDB和MyISAM的区别

事务

为了数据库操作的原子性，我们需要事务。保证一组操作要么都成功，要么都失败，比如转账的功能。我们通常将多条SQL语句放在begin和commit之间，组成一个事务。

InnoDB支持，MyISAM不支持。

主键

由于InnoDB的聚集索引，其如果没有指定主键，就会自动生成主键。
MyISAM支持没有主键的表存在。

外键

为了解决复杂逻辑的依赖，我们需要外键。比如高考成绩的录入，必须归属于某位同学，我们就需要高考成绩数据库里有准考证号的外键。

InnoDB支持，MyISAM不支持。

索引

为了优化查询的速度，进行排序和匹配查找，我们需要索引。比如所有人的姓名从a-z首字母进行顺序存储，当我们查找zhangsan或者第44位的时候就可以很快的定位到我们想要的位置进行查找。

InnoDB是聚集索引，数据和主键的聚集索引绑定在一起，通过主键索引效率很高。如果通过其他列的辅助索引来进行查找，需要先查找到聚集索引，再查询到所有数据，需要两次查询。

MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据的指针。

从InnoDB 1.2.x版本，MySQL5.6版本后，两者都支持全文索引。

auto_increment自增

对于自增数的字段，InnoDB要求该列必须是索引，同时必须是索引的第一个列，否则会报错：

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

把(b,a)顺序替换为(a,b)即可。

而MyISAM可以将该字段与其他字段随意顺序组成成联合索引。

表行数

很常见的需求是看表中有多少条数据，此时我们需要select count(*) from table_name。

InnoDB不保存表行数，需要进行全表扫描。MyISAM用一个变量保存，直接读取该值，更快。当时当带有where查询的时候，两者一样。

存储

数据库的文件都是需要在磁盘中进行存储，当应用需要时再读取到内存中。一般包含数据文件、索引文件。

InnoDB分为：

• .frm表结构文件
• .ibdata1共享表空间
• .ibd表独占空间
• .redo日志文件

MyISAM分为三个文件：

• .frm存储表定义
• .MYD存储表数据
• .MYI存储表索引

执行速度

如果你的操作是大量的查询操作，如SELECT，使用MyISAM性能会更好。
如果大部分是删除和更改的操作，使用InnoDB。

InnoDB和MyISAM的索引都是B+树索引，通过索引可以查询到数据的主键，不熟悉B+树的可以查看MySQL InnoDB索引原理和算法。两者的性能区别主要在于查询到数据主键后两者的处理方式却不同。

InnoDB会缓存索引和数据文件，一般以16KB为一个最小单元（数据页大小）和磁盘进行交互，InnoDB在查询到索引数据后实际得到的是主键的ID，它需要在内存中的数据页中查找该行的全部数据，但如果该数据不是加载过的热数据，还需要进行数据页的查找和替换，这其中可能牵涉到多次I/O操作和内存中数据查找，导致耗时较高。

而MyISAM存储引擎只缓存索引文件，不缓存数据文件，其数据文件的缓存直接使用操作系统的缓存，这点非常独特。此时相同的空间能够加载更多的索引，因此当缓存空间有限时，MyISAM的索引数据页替换次数会更少。根据前面我们知道MyISAM的文件分为MYI和MYD，当我们通过MYI查找到主键ID时，其实得到是MYD数据文件的offset偏移量，查找数据比InnoDB寻址映射要快的多。

但由于MyISAM是表锁，而InnoDB支持行锁，因此在牵涉到大量写操作时，InnoDB的并发性能比MyISAM好很多。同时InnoDB还通过MVVC多版本控制来提高并发读写性能。

delete删除数据

调用delete from table时，MyISAM会直接重建表，InnoDB会一行一行的删除，但是可以用truncate table代替。参考: mysql清空表数据的两种方式和区别。

锁

MyISAM仅支持表锁，每次操作锁定整张表。
InnoDB支持行锁，每次操作锁住最小数量的行数据。

表锁相比于行锁消耗的资源更少，且不会出现死锁，但同时并发性能差。行锁消耗更多的资源，速度较慢，且可能发生死锁，但是因为锁定的粒度小、数据少，并发性能好。如果InnoDB的一条语句无法确定要扫描的范围，也会锁定整张表。

当行锁发生死锁的时候，会计算每个事务影响的行数，然后回滚行数较少的事务。

数据恢复

MyISAM崩溃后无法快速的安全恢复。InnoDB有一套完善的恢复机制。

数据缓存

MyISAM仅缓存索引数据，通过索引查询数据。InnoDB不仅缓存索引数据，同时缓存数据信息，将数据按页读取到缓存池，按LRU（Latest Rare Use 最近最少使用）算法来进行更新。

如何选择存储引擎

创建表的语句都是相同的，只有最后的type来指定存储引擎。

MyISAM

1、大量查询总count

2、查询频繁，插入不频繁

3、没有事务操作

InnoDB

1、需要高可用性，或者需要事务

2、表更新频繁

推荐学习：MySQL教程

以上就是MySQL中InnoDB和MyISAM的存储引擎的差异的详细内容，更多请关注Gxlcms其它相关文章！