Mysql的两种存储引擎详细分析及区别(全)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 目录前言总结前言mysql有多种存储引擎(MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。)但是常用的主要是这两种,分别为myism和innodb总结区别MyISAMInnoDB构成上的区别: 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (

前言

mysql有多种存储引擎(MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。)

但是常用的主要是这两种,分别为myism和innodb

补充说明一下
这两种数据结构都是用的b+树
在这里插入图片描述
B+树的非叶子节点不存储数据,只有叶子节点才存储数据
而B树的非叶子和叶子节点都会存储数据,会导致非叶子节点存储的索引值会更少,树的高度相对会比B+树高,平均的I/O效率会比较低

所以使用B+树作为索引的数据结构,再加上B+树的叶子节点之间会有指针相连,也方便进行范围查找

1. MyISAM(非聚集索引)

  • 不支持事务,但是操作是有原子性的
  • 不支持外键,支持表锁,每次操作的时候都是锁住整张表

具体关于sql的锁机制
可看我之前的文章
Mysql中各类锁的机制图文详细解析(全)

  • update会有表锁,所以并发量小,因为会进行阻塞。所以如果执行大量的SELECT,MyISAM是更好的选择
  • 采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引不用保证唯一性
  • MyISAM表格可以被压缩,而且它们支持全文搜索
  • MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引
  • MyISAM缓存有表meta-data(行数等),因此在做COUNT(*)的数据查询的时候比较快,但是如果加了where条件,就会比较慢
  • 对于自增长的字段,在MyISAM表中可以和其他字段一起建立联合索引

主键索引
在这里插入图片描述

辅助索引
在这里插入图片描述
对于上述两幅索引图来说

MyISAM存储引擎在使用索引查询数据时,会先根据索引查找到数据地址,再根据地址查询到具体的数据。并且主键索引和辅助索引没有太多区别。

两个表都是差不多,查找的思路也都是根据索引找到数据地址,在通过数据地址找到数据

2. InnoDB(聚集索引)

与MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能。

  • 支持外键
  • 支持行锁,采用MVCC来支持高并发,有可能死锁
  • 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引
  • InnoDB用于事务处理,具有ACID事务支持等特性。如果在应用中执行大量insert和update操作,可选择。
  • 不支持全文搜索

主键索引
在这里插入图片描述

辅助索引
在这里插入图片描述
InnoDB中主键索引的叶子节点的数据区域存储的是数据记录,辅助索引存储的是主键值
主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;

因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问主键索引;

最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。

Innodb中的主键索引和实际数据时绑定在一起的,也就是说Innodb的一个表一定要有主键索引,如果一个表没有手动建立主键索引,Innodb会查看有没有唯一索引,如果有则选用唯一索引作为主键索引,如果连唯一索引也没有,则会默认建立一个隐藏的主键索引(用户不可见)。(用户没有指定的话会自己找生产一个隐藏列Row_id来充当默认主键)

另外,Innodb的主键索引要比MyISAM的主键索引查询效率要高(少一次磁盘IO),并且比辅助索引也要高很多。

所以,我们在使用Innodb作为存储引擎时,我们最好︰

  1. 手动建立主键索引
  2. 尽量利用主键索引查询

自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)

3. 总结

区别 MyISAM InnoDB
结构 每个MyISAM在磁盘上存储成三个文件(扩展名指出文件类型)。 .frm文件存储表定义,也就是存储结构的信息。.MYD文件存放数据文件。.MYI存放索引文件 表空间数据文件和它的日志文件。没有了myd与myi,只有.idb放了索引位置以及表的信息位置
事务 强调的是性能,不提供事务支持 提供事务支持事务,外部键等高级数据库功能
CRUD 查询很合适 增加或者更新合适。删除的时候,是一行一行删除
表的具体行数 select count(*) from table,可以很好的读取 不保存表的具体行数,要扫描一遍整个表来计算有多少行
AUTO_INCREMENT 可以和其他字段一起建立联合索引 必须包含只有该字段的索引
全文索引 支持 不支持
外键 不支持 支持
表锁 提供行锁(执行一个SQL语句时MySQL不能确定要扫描的范围,同样会锁全表, 例如update table set num=1 where name like "%aaa%)

MyISAM

  • 优点:查询数据相对较快,适合大量的select,可以全文索引。
  • 缺点:不支持事务,不支持外键,并发量较小,不适合大量update

InnoDB:

  • 优点:支持事务,支持外键,并发量较大,适合大量update
  • 缺点:查询数据相对较快,不适合大量的select

可能注意到我的标题有聚集索引和非聚集索引

具体这两部分的区分是

  1. 聚集索引(聚簇索引):表中的数据都会有一个主键,即使不创建主键,系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的,而B+树的键值就是主键,在B+树的叶子节点中,存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引,我们称之为聚集索引。`在B+树中,叶子节点存储整条记录的数据,这样的索引为聚集索引。`
  2. 非聚集索引(非聚簇索引):以主键以外的列值作为键值构建的B+树索引,我们称之为非聚集索引。叶子节点不存储表中的数据,而是存储该列对应的主键,想要查找数据我们还需要根据主键再去聚集索引中进行查找,这个再根据聚集索引查找数据的过程,我们称为回表
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
348
分享
相关文章
MySQL 和 Oracle 的区别?
本文对比了Oracle和MySQL数据库的多个方面。Oracle适用于大型数据库,支持高并发和大访问量,市场占有率为40%,安装占用空间较大,约3G;而MySQL适合中小型应用,是开源免费的,安装仅需152M。两者在主键生成、字符串处理、SQL语句、事务处理等方面存在差异。Oracle功能更为强大,尤其在企业级应用中表现突出,而MySQL则以简单易用见长。
103 7
MySQL 和 Oracle 的区别?
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
MYSQL支持的存储引擎有哪些, 有什么区别
MYSQL存储引擎有很多, 常用的就二种 : MyISAM和InnerDB , 者两种存储引擎的区别 ; ● MyISAM支持256TB的数据存储 , InnerDB只支持64TB的数据存储 ● MyISAM 不支持事务 , InnerDB支持事务 ● MyISAM 不支持外键 , InnerDB支持外键
简单聊聊MySQL的三大日志(Redo Log、Binlog和Undo Log)各有什么区别
在MySQL数据库管理中,理解Redo Log(重做日志)、Binlog(二进制日志)和Undo Log(回滚日志)至关重要。Redo Log确保数据持久性和崩溃恢复;Binlog用于主从复制和数据恢复,记录逻辑操作;Undo Log支持事务的原子性和隔离性,实现回滚与MVCC。三者协同工作,保障事务ACID特性。文章还详细解析了日志写入流程及可能的异常情况,帮助深入理解数据库日志机制。
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
131 7
MySQL事务日志-Undo Log工作原理分析
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
60 11
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制,包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件,能显著提升数据库读写性能。默认大小为128MB,可根据服务器配置调整(如32GB内存可设为2GB)。它通过free链表管理空闲缓存页,flush链表记录脏页,并用LRU链表区分冷热数据以优化淘汰策略。此外,还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法,以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词:MySQL内存数据更新机制。
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
102 82
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等