• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

深入理解MySql中Innodb引擎和MyIsam引擎的区别

互联网 diligentman 3小时前 2次浏览

深入理解MySql中Innodb引擎和MyIsam引擎的区别(基于mysql 5.6以上的版本)

Innodb引擎和MyIsam引擎主要有一下的区别

1 最开始Mysql是没有Innodb引擎的,而MyIsam是Mysql数据的自带的引擎,但是由于MyIsam引擎没有事务的,而Innodb引擎是另一个公司以以插件的方式引入MySql的。

下面我们通过案例来证明MyIsam引擎没有事务,Innodb引擎具有事务。
我建了两个表,innodb_table的表是使用Innodb引擎的,myisam_table是使用MyIsam引擎的。
深入理解MySql中Innodb引擎和MyIsam引擎的区别
我先关闭了Mysql数据库中事务自动提交的设置
深入理解MySql中Innodb引擎和MyIsam引擎的区别1.1 先对MyIsam引擎进行测试,我开启了两个客户端,一个用来插入数据,一个用来查询

深入理解MySql中Innodb引擎和MyIsam引擎的区别1.2 在客户端1号中,我开启了事务,但是没有提交事务,然后使用客户端2号对表进行查询,然后发现能查出我刚刚插入的数据

深入理解MySql中Innodb引擎和MyIsam引擎的区别因此说明MyIsam是没有事务的。

1.3 接下来我们测试一下innodb引擎
我开启了事务,然后查询该表,数据为空,然后插入一条数据,但没有提交事务
深入理解MySql中Innodb引擎和MyIsam引擎的区别
然后使用客户端2号对innodb_table进行查询,发现数据为空
深入理解MySql中Innodb引擎和MyIsam引擎的区别
但是没有提交事务,客户端1号能查询出数据
深入理解MySql中Innodb引擎和MyIsam引擎的区别

接下来我们把客户端1号的事务进行提交,然后再对客户端2号进行查询
对刚刚插入操作的事务进行提交
深入理解MySql中Innodb引擎和MyIsam引擎的区别使用客户端2号对表进行查询,发现客户端1号提交数据后,客户端2号就能查询到插入的数据
深入理解MySql中Innodb引擎和MyIsam引擎的区别

结论:说明MyIsam引擎是没有事务的,Innodb引擎具有事务,并且该事务的隔离级别:可重复读

2 Innodb引擎锁定的最小粒度是行锁,而MyIsam锁的最小粒度是表锁

2.1 Innodb引擎锁的粒度测试

使用客户端号,开启事务,然后对innodb_table表中id=1的数据进行update操作,但是没有提交事务
深入理解MySql中Innodb引擎和MyIsam引擎的区别在客户端1号没有提交事情前,客户端2号也对innodb_table表中id=1的数据进行更新,发现执行语句一直处于等待的状态
深入理解MySql中Innodb引擎和MyIsam引擎的区别过几十秒后抛出一个等待超时的错误
深入理解MySql中Innodb引擎和MyIsam引擎的区别然后对客户端1号的更新操作的事务进行提交
深入理解MySql中Innodb引擎和MyIsam引擎的区别在对客户端2号进行id=1的数据进行更新,发现更新完成
深入理解MySql中Innodb引擎和MyIsam引擎的区别

结论:Innodb引擎的锁级别最小粒度为行。

2.2 MyIsam引擎锁粒度的测试

我们首先对myisam_table锁上一个写锁
锁表命令: lock table 表名 write/read;

深入理解MySql中Innodb引擎和MyIsam引擎的区别当客户端1号对表进行上锁后,客户端2号对该表进行查询,发现一直处于等待的状态,查询不出数据
深入理解MySql中Innodb引擎和MyIsam引擎的区别这是我们就使用客户端1号进行解锁的操作
解锁命令:unlcok 表名
深入理解MySql中Innodb引擎和MyIsam引擎的区别当解锁了之后客户端2号的查询就有结果了
深入理解MySql中Innodb引擎和MyIsam引擎的区别

结论:MyIsam引擎只有表锁其中包括读锁、和写锁,因此如果在选择引擎时,需要根据业务的实际情况再选择对应的引擎。

3 Innodb引擎和MyIsam引擎它们之间的存储数据文件的结构不同

3.1 Innodb引擎表的数据文件结构,就只有一个扩展名.ibd的文件

在该xx.ibd文件存储了表的结构、索引、以及数据

3.2 MyIsam引擎具有三个文件的结构,分别为MYD、MYI、FRM(sdi)

MyIsam三个文件分别表示:
MYD文件:存储了数据
MYI文件: 存储了索引
FRM(sdi)文件:存储了表的结构

深入理解MySql中Innodb引擎和MyIsam引擎的区别

4 InnoDB引擎的主键索引是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。

4.1 从下面的结构图中就能看出InnoD引擎中主键索引的叶子节点是包含了该索引的所有数据,而非主键索引中的叶子节点是主键,因此使用主键索引时是可以不用进行回表操作的,而使用非主键索引查询整行数据时需要最少一次回表,如果非主键索引中的主键的值正好是你要查询的值,那非主键索引查询也可以不用回表,直接放回叶子节点的值即可。

InnoDB的索引结构图如下:
深入理解MySql中Innodb引擎和MyIsam引擎的区别

4.2 MyIsam引擎的索引都不是聚簇索引,在查询时至少需要进行一次的回表查询,其执行的过程:先通过索引表查询到索引对应该行的物理地址,然后去读该物理地址的值

MyIsam索引结构图:
深入理解MySql中Innodb引擎和MyIsam引擎的区别
最后我们总结一下本文的一些结论:

1.Innodb索引具有事务;MyIsam引擎没有事务

2.Innodb引擎锁的最小粒度是行锁;MyIsam引擎锁的最小粒度为表

3.Innodb引擎的数据存储文件结构不同,Innodb只有XX.ibd文件;而MyIsam具有xx.MYI、XX.MYD、xx.FRM三个文件结构

4.Innodb引擎中主键索引是聚簇的,Myisam引擎是非聚簇的。

等下次有时间我们在介绍一些innodb为什么有事务功能,而Myisam没有,而主要原因是Innodb引擎比Myisam引擎多了一个redo log日志,使得innodb具有事务的功能。更详细的内容我将更新在新的文章中。


程序员灯塔
转载请注明原文链接:深入理解MySql中Innodb引擎和MyIsam引擎的区别
喜欢 (0)