大家都知道在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;

    //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();<br>}
 
运行结果：
 
 
通过上面的图片，可以看到结果： MyISAM查询性能确实比较Innodb快。
 
2、非主键查询
 
C#实现费主键查询trade_no在10000~20000区间，查询耗费时间平均值代码如下：
 
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快。
 
对比结果
 
通过我们的验证来看，不管是主键还是非主键查询，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引擎更多是用于存储数据仓库，只有查询少量插入的业务场景中，比如系统日志。
 
 
 
 
 
 
 
 

说一下个人理解：
 MyISAM只加载了索引数据进内存,加载的数据量少,所以在相同时间内加载进内存的的索引数据也就越多,使CPU在相同时间内查询出更多的索引数据,当然这些必须要在查询的数据量大的情况下才能提现出来,查询的数据量少的时候则是Innodb更快.毕竟Innodb是聚集索引不用再去调用一次IO查询另一个文件了。
 
选择性问题上（实际开发都是几百上千万的数据）
 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB

1. MyISAM和InnoDB的区别
 
①MyISAM查询效率高，InnoDB查询效率低；
 
②MyISAM不支持事务和外键，而InnoDB支持；
 
③MyISAM是非聚簇索引(索引的叶子节点存储的是数据的物理位置)，InnoDB是聚簇索引(索引的叶子节点存储的就是数据)；
 
④InnoDB支持行级锁。
 
⑤MyISAM对应着3个物理文件：.frm表结构文件，.myi表索引文件，.myd表数据文件。而InnoDB对应2个物理文件：.frm表结构文件，.ibd表数据和索引文件
 
 
2. 为什么MyISAM查询效率高？
 
①InnoDB寻址要映射到块，然后到行；MyISAM直接是文件的Offset。
 
②因为InnoDB支持事务，涉及多版本并发控制(MVCC)，而且支持行级锁，所以会损失一定的性能。
 
③InnoDB的非聚集索引（叶子节点存储的是主键值，然后通过主键进行回表操作得到主键索引的聚集索引得到最终的数据）存在回表操作。
 
 
3. 数据库索引
 
3.1 什么是数据库索引：为了方便数据库快速查询、更新的手段，使用B+树实现。
 
3.2 B树和B+树的区别：B树是每个节点有多个子节点，不像二叉树，最多只有两个子节点；而且B树的所有叶子节点在同一层，而2-3树是最简单的B树，由二节点和三节点组成，二节点和三节点的要么没有子节点，要么有两个或三个子节点。然后B+树在B树的基础上进行了优化，B+树非叶子节点是叶子节点的索引，只有叶子节点通过链表存储数据。
 

 
区别：
 
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
 
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
 
3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构)，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
 
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
 
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
 
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快(注意不能加有任何WHERE条件)；
 
那么为什么InnoDB没有了这个变量呢？
 
因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
 
如果索引并没有完全处于InnoDB维护的缓冲区(Buffer Pool)中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
 
5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
 
6. MyISAM表格可以被压缩后进行查询操作
 
7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
 
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
 
8、InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键)，而Myisam可以没有
 
9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
 
Innodb：frm是表定义文件，ibd是数据文件
 
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
 
如何选择：
 
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
 
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
 
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
 
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。
 
InnoDB为什么推荐使用自增ID作为主键？
 
答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。
 
innodb引擎的4大特性
 
插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
 
以上就是MySQL MyISAM与InnoDB 的区别的详细内容，更多关于MySQL MyISAM与InnoDB 的资料请关注脚本之家其它相关文章！