MySQL存储引擎MyISAM与InnoDB两者之前的区别-阿里云开发者社区

MySQL的存储引擎，MyISAM与InnoDB是我们最经常使用的存储引擎，我们一起来从性能、存储结构、索引、如何抉择的角度，一起来对比下两者之前的差别。

文中讲解的方法，都是MySQL本身支持的，只是涉及的代码，采用C#作为例子。

1、前期准备

1.1、创建表并插入测试数据

分别创建不同引擎的订单表：trade_myisam、trade_innodb，订单表包含id、订单编号两个字段。

创建InonoDB引擎的订单表SQL代码如下：

CREATE TABLE `trade_innodb` (
  `id` INT(11) NOT NULL,
  `trade_no` INT(11) NOT NULL,
  PRIMARY KEY `id` (`id`),
  INDEX `trade_no` (`trade_no`) USING BTREE
)
COMMENT='订单'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

创建MyISAM引擎的订单表SQL代码如下：

CREATE TABLE `trade_myisam` (
  `id` INT(11) NOT NULL,
  `trade_no` INT(11) NOT NULL,
  PRIMARY KEY `id` (`id`),
  INDEX `trade_no` (`trade_no`) USING BTREE
)
COMMENT='订单'
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
;

分别插入10万条测试数据，结果如下：

这里所有的字段，采用的都是Int类型，是为了方便我们，下面使用区间查询，来验证查询速度。

1.2、测试环境

文中测试环境电脑配置如下：

操作系统：Window 10 专业版

CPU：Inter(R) Core(TM) i7-8650U CPU @1.90GHZ 2.11 GHZ

内存：16G

MySQL版本：5.7.26

2、MyISAM与InnoDB性能对比

2.1 验证思路

MyISAM在MySQL 5.5 及之前的版本，MyISAM是默认引擎；InnoDB是专门为处理巨大数据量时的最大性能设计的，现在为MySQL默认引擎。

在MySQL官方文档说明，MyISAM比InnoDB查询快，但很多人都不知道其中的原理。

在探索其中原理之前，我们先验证下查询速度，另外也验证下MyISAM是否真的比InnoDB查询性能好。

在Mysql中，索引分为主键索引和非主键索引，这两种索引都能起到，加快查询速度的作用，但在查询速度上是有区别的，主键索引要快于非主键索引，所以我们从以下两个方面去验证：

1、通过主键查询，MyISAM是否比InnoDB查询快？

2、通过非主键字段查询，MyISAM是否比InnoDB查询快？

具体验证方法为：

1、分别通过id、trade_no查询10000~20000区间的记录，并获得查询所花费时间。

2、循环查询100次，并取得查询耗费时间平均值。

3、通过对比查询耗费时间平均值，来判断性能。

2.2、验证测试

2.2.1、主键查询性能测试

查询主键id在10000~20000区间，查询100次，并统计耗费时间的平均值，C#实现代码如下：

using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    //查询次数
    var queryTime = 1000;

    //InnoDB数据引擎：主键查询
    //开始时间
    var startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        var sql = "select * from trade_innodb where id>=10000 and id<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //完成时间
    var endTime = DateTime.Now;
    //耗时
    var spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("InnoDB主键查询1000次平均耗时：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

    //MyISAM数据引擎：主键查询
    //开始时间
    startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        var sql = "select * from trade_myisam where id>=10000 and id<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //完成时间
    endTime = DateTime.Now;
    //耗时
    spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("MyISAM主键查询1000次平均耗时：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

    conn.Close();
   }

运行结果：

通过上面的图片，可以看到结果：主键查询的方式，MyISAM查询性能比较Innodb快。

2.2.2、非主键查询

查询非主键trade_no在10000~20000区间，查询100次，并统计耗费时间的平均值，C#实现代码如下：

using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    //查询次数
    var queryTime = 1000;

    //InnoDB数据引擎：非主键查询
    //开始时间
    var startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        var sql = "select * from trade_innodb where trade_no>=10000 and trade_no<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //完成时间
    var endTime = DateTime.Now;
    //耗时
    var spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("InnoDB非主键查询1000次平均耗时：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

    //MyISAM数据引擎：非主键查询
    //开始时间
    startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        var sql = "select * from trade_myisam where trade_no>=10000 and trade_no<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //完成时间
    endTime = DateTime.Now;
    //耗时
    spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("MyISAM非主键查询1000次平均耗时：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

    conn.Close();
}

运行结果：

通过上面的图片，可以看到结果：非主键查询的方式，MyISAM查询性能比较Innodb快。

2.3 验证结论

通过我们的验证来看，不管是主键还是非主键查询，MyISAM查询性能确实比较Innodb快。

3、MyISAM与InnoDB区别

从上面的验证测试结果看，MyISAM与InnoDB查询速度是不一样，下面我们一起来看看它们的区别。

3.1、维护区别

通过上面表格对比，InnoDB在做SELECT的时候，要维护的东西比MYISAM引擎多很多，影响查询速度有：

1）数据块，InnoDB要缓存，MyISAM只缓存索引块，这中间还有换进换出的减少；

2）InnoDB寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比InnoDB要快；

3）InnoDB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

MVCC (Multi-Version Concurrency Control)多版本并发控制。

3.2、索引区别

MyISAM与InnoDB索引存储方式也是不一样的（关于索引的使用请查看《细说MySql索引原理》）。

InnoDB表是根据主键进行展开的 B+tree 的聚集索引。MyISAM则非聚集型索引，MyISAM存储会有两个文件，一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据。

聚集型索引并不是一种单独的索引类型，而是一种存储方式，InnoDB 聚集型索引实际上是在同一结构中保存了 B+tree 索引和数据行。当有聚簇索引时，它的索引实际放在叶子页中。

下面我们通过2张图，我们就能直观看到这两个引擎，索引结构的区别：

3.3、MVCC的维护

InnoDB引擎还有需要实现MVCC，这里面也是损耗一定的性能的。Innodb是通过每一行记录有2个额外的隐藏值来实现，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。

但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。

3.4、其他差别

1、InnoDB支持事务，MyISAM不支持，针对数据的增删改如果出错，采用InnoDB引擎，MySQL会自动帮我们维护，而MyISAM不会；

2、InnoDB支持外键，MyISAM不支持；

3、InnoDB不支持FullText类型的全文索引；

4、InnoDB是不保存表的行数，如果要返回表的总行数，InnoDB需要扫描一遍表来统计总数，但MyISAM不需要，只需返回已经存储好的行数；

5、删除整个表数据，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。而采用TRUNCATE 操作而不受影响；

6、MyISAM是表锁，所以对于一些高并发的操作，容易导致整个表卡主，性能非常慢；而InnoDB采用的是行锁，但如果更新操作，没有使用索引，依然会触发表锁。

4、MyISAM与InnoDB使用的选择

从上面的对比看，InnoDB支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM不支持。MyISAM的性能更优，占用的存储空间少。那关于这两种引擎我们如何选择呢。

先抛开性能的差别，两者之间最大的差别就是事务的区别，在实际开发中，如果我们选择MyISAM，我们就要在业务代码中自己去维护，这块业务实现是非常麻烦的，并且只要涉及插入、更新、删除的地方都要维护。

从上面性能对比看，这两个引擎查询性能差别还是比较小的，平常在我们业务系统中，InnoDB数据引擎完全可以满足我们的需求，完全没必要为了这点性能，给我们带来更多的工作量。

MyISAM引擎不需要去维护索引文件，所以在相同的数据量下，MyISAM引擎占用的空间是更小的，所以MyISAM特别适合用于存储数据仓库，特别是较多查询、少量更新的业务场景中，比如系统日志。

5、总结

通过我们的验证来看，不管是主键还是非主键查询，MyISAM查询性能确实比较InnoDB快，但两者差别不是很大，我们在开发中，默认采用InnoDB就可以，这样开发工作量比较少，可以提升我们的开发效率，针对一些有存储空间要求的，再来考虑采用MyISAM。

MySQL存储引擎MyISAM与InnoDB两者之前的区别