MySQL中MyISAM为什么比InnoDB查询快-安全小百科

大家都知道在MySQL中，MyISAM比InnoDB查询快，但很多人都不知道其中的原理。

今天我们就来聊聊其中的原理，另外也验证下是否MyISAM比InnoDB真的查询快。

在探索其中原理之前，我们先验证下查询速度。

验证

下面我们一起来验证两个问题：

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

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

验证前准备

订单表结构：

CREATE TABLE `trade` (

`id` INT(11) NULL DEFAULT NULL,

`trade_no` INT(11) NULL DEFAULT NULL,

UNIQUE INDEX `id` (`id`),

INDEX `trade_no` (`trade_no`)

)

COMMENT='订单'

COLLATE='utf8_unicode_ci'

ENGINE=InnoDB

;

分别创建不同引擎的表trade_myisam、trade_innodb，并插入10万条测试数据，如下：

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

验证思路

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

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

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

下面我们一起来验证下，文章开头的两个问题：

1、主键查询

C#实现主键查询Id在10000~20000区间，查询耗费时间平均值代码如下：

using (var conn = new MySqlConnection(connsql))

{

conn.Open();

var queryTime = 1000;

主键查询

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 + "毫秒");

主键查询

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();<br>}

运行结果：

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

2、非主键查询

C#实现费主键查询trade_no在10000~20000区间，查询耗费时间平均值代码如下：

using (var conn = new MySqlConnection(connsql))

{

conn.Open();

var queryTime = 1000;

非主键查询

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 + "毫秒");

非主键查询

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快。

对比结果

通过我们的验证来看，不管是主键还是非主键查询，MyISAM查询性能确实比较Innodb快。
查询速度不一样，肯定是MyISAM与InnoDB有什么区别，下面我们一起来看看它们的区别。

MyISAM与InnoDB区别

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

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

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

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

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

索引区别：

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

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

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

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

维护MVCC

InnoDB引擎还有需要实现MVCC，这里面也是损耗一定的性能的。

Innodb是通过每一行记录有2个额外的隐藏值来实现，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。

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

最后

通过我们的验证来看，不管是主键还是非主键查询，MyISAM查询性能确实比较Innodb快。
但是MyISAM不支持事务，在实现业务方面有很多麻烦的事情。

关于我们开发中，选择哪个数据引擎的问题。

平常在我们业务系统中，InnoDB数据引擎完全可以满足我们的需求，这两个引擎查询性能差别还是比较小的，完全没必要为了这点性能，给我们带来更多的工作量。
MyISAM引擎更多是用于存储数据仓库，只有查询少量插入的业务场景中，比如系统日志。

欢迎关注公众号【编程乐趣】，了解更多MySQL知识。

相关推荐: PHP线程安全（TS）与非线程安全（NTS）、Apache版本选择，及详解五种运行模式

在Windows下建立PHP开发环境，在考虑下载何种PHP版本时，遭遇一些让我困惑的情况，为了解决这些困惑，不出意料地牵扯出更多让我困惑的问题。为了将这些困惑一网打尽，我花了一下午加一晚上的时间查阅了大量资料，并做了一番实验后，终于把这些困惑全都搞得清清楚楚…

文章版权归作者所有，未经允许请勿转载。

THE END