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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 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则以简单易用见长。
MySQL 和 Oracle 的区别?
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
简单聊聊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也叫回滚日志。
125 7
MySQL事务日志-Undo Log工作原理分析
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
58 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内存数据更新机制。
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
196 11
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
99 7
AI助理

你好,我是AI助理

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