MySQL之存储引擎InnoDB和MyISAM的区别及底层详解(转)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之存储引擎InnoDB和MyISAM的区别及底层详解(转),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6061字,纯文字阅读大概需要9分钟。
内容图文
![MySQL之存储引擎InnoDB和MyISAM的区别及底层详解(转)](/upload/InfoBanner/zyjiaocheng/510/04780b4e3ad94f6a9276a4b2d2ccb379.jpg)
题外话:中华文化博大进深,从学Java到数据库,无一不体现出同一组件鱼和熊掌不可兼得的要义。自然,编程中安全和效率也很难同时做到完美,这一次InnoDB和MyISAM又让我大开眼界。
好了,Talk is cheap,show you the code:
测试环境:Mysql 5.7.20-log,IDEA 2018
首先创建两张表: testinnodb,testmyisam,sql如下:
1.同时大批量插入数据(百万级,million),小编采用了存储过程,代码及测试结果如下:
下面代码在IDEA上运行即可:
同时插入100W条数据,MyISAM耗时38s左右,而InnoDB却耗时76分钟4s左右,很明显可以看出MyISAM在处理速度上完胜InnoDB,但是如果实际项目中使用,由于涉及到数据安全(或者事物安全)问题,大多数公司还是选择了InnoDB, 较少公司使用MyISAM(得力于其在业务层的严格控制)。但MyISAM依然可以被我们使用在日志数据分析,实验等环境中。
2.再看其在删改查方面的对比
改查耗时
删除耗时
其实对比下来,差距并没有插入数据那样夸张,对于大多数要求事物安全的公司来说还是可以接受的。
PS: 你可以使用mysql插件profile来显示最近执行命令的持续时长,用法如下:
mysql默认是关闭profiles的,你需要开启它,
查看是否开启命令: show variables like ‘%pro%‘;
小编已经把他开启了,所以显示为ON,默认为OFF。
开启命令:set profiling=1;
关闭命令:set profiling=0;
查询最近使用命令时长:show profiles;
根据Query ID查询单个命令详情: show profile for query 1;
根据Query ID查询单个命令memory,source,cpu等详情: show profile cpu for query 1;或者 show profile all for query 1;
测试Over,接下来总结一下:
1.InnoDB支持事物,外键等高级的数据库功能,MyISAM不支持。需要注意的是,InnDB行级锁也不是绝对的,例如mysql执行一个未定范围的sql时,也还是会锁表,例如sql中like的使用
2.效率,明显MyISAM在插入数据的表现是InnoDB所远远不及的,在删改查,随着InnoDB的优化,差距渐渐变小
3.行数查询,InnoDB不保存行数,也就是select的时候,要扫描全表,MyISAM只需读取保存的行数即可,这也是MyISAM查询速度快的一个因素。
4.索引,InnoDB会自动创建Auto_Increment类型字段的索引,一般习惯应用于主键,即主键索引(只包含该字段),而MyISAM可以和其他字段创建联合索引。
除此之外,MyISAM还支持全文索引(FULLTEXT_INDEX),压缩索引,InnoDB不支持。
备注:MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引,而Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB存储引擎被完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。
5.服务器数据备份。InnoDB必须导出SQL来备份,LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
备注:而且MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB是拷贝数据文件、备份 binlog,或者用 mysqldump,支持灾难恢复(仅需几分钟),MyISAM不支持,遇到数据崩溃,基本上很难恢复,所以要经常进行数据备份。
6.锁的支持。**MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
使用场景建议:
1)可靠性高或者要求事务处理,则使用InnoDB。这个是必须的。
2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。
对比之下,MyISAM的使用场景:
1)做很多count的计算的。如一些日志,调查的业务表。
2)插入修改不频繁,查询非常频繁的。
MySQL能够允许你在表这一层应用数据库引擎,所以你可以只对需要事务处理的表格来进行性能优化,而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言,灵活性才是关键。
引擎原理分析
MyISAM索引结构: MyISAM索引用的B+ tree来储存数据,MyISAM索引的指针指向的是键值的地址,地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集索引
主索引如下:
辅助索引如下:
因此,过程为: MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,根据data域的值去读取相应数据记录。
InnoDB引擎的索引结构:
也是B+Treee索引结构。Innodb的索引文件本身就是数据文件,即B+Tree的数据域存储的就是实际的数据,这种索引就是聚集索引。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。【Java高架构师、分布式架构、高可扩展、高性能、高并发、性能优化、Spring boot、Redis、ActiveMQ、Nginx、Mycat、Netty、Jvm大型分布式项目实战学习架构师视频免费获取架构群;855355016】
InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。
建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。
主索引如下:
辅助索引如下:
上图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
而且,与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。
因此,过程为:将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用”where id = 13”这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
两种索引数据查找过程如下:
小礼物走一走,来简书关注我
作者:Java高级技术
链接:https://www.jianshu.com/p/92ba79cbc6a3
来源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。
MySQL之存储引擎InnoDB和MyISAM的区别及底层详解(转)
标签:几分钟 生成 解决方法 关闭 data- file set mem 而且
本文系统来源:https://www.cnblogs.com/dayhand/p/11080155.html
内容总结
以上是互联网集市为您收集整理的MySQL之存储引擎InnoDB和MyISAM的区别及底层详解(转)全部内容,希望文章能够帮你解决MySQL之存储引擎InnoDB和MyISAM的区别及底层详解(转)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。