MySQL引擎:InnoDB VS MyISAM

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL引擎:InnoDB VS MyISAM

MySQL引擎:InnoDB VS MyISAM

文章目录

InnoDB特点 VS MyISAM特点

MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。

在MySQL中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。MySQL 8.0支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。

其中,最常用的引擎是InnoDB和MyISAM。

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。MySQL 5.5.5之后,InnoDB作为默认存储引擎:

InnoDB 主要特性如下:

  1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎

InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表与其他MySQL表的类型混合起来,甚至在同一个查询中也可以混合。

  1. InnoDB是为处理巨大数据量的最大性能设计

它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

  1. InnoDB存储引擎完全与MySQL服务器整合,为在主内存中缓存数据和索引而维持它自己的缓冲池

InnoDB将它的表和索引存在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。

  1. InnoDB支持外键完整性约束(FOREIGN KEY)

存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6B的ROWID,并以此作为主键。

  1. InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录,使用InnoDB时,MySQL将在数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

MyISAM存储引擎:

MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一MyISAM拥有较高的插入、查询速度,但不支持事务

MyISAM的主要特性如下:

  1. 在支持大文件(达63位文件长度)的文件系统和操作系统上被支持
  2. 当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块以及若下一个块被删除则扩展到下一块来自动完成。
  3. 每个MyISAM表最大的索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16个。
  4. 最大的键长度是1000B,这也可以通过编译来改变。对于键长度超过250B的情况,一个超过1024B的键将被用上。
  5. BLOB和TEXT列可以被索引。
  6. NULL值被允许在索引的列中,这个值占每个键的0~1个字节。
  7. 所有数字键值以高字节优先被存储,以允许一个更高的索引压缩。
  8. 每个表一个AUTO_INCREMENT列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列,这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。
  9. 可以把数据文件和索引文件放在不同目录。
  10. 每个字符列可以有不同的字符集。
  11. 有VARCHAR的表可以固定或动态记录长度。
  12. VARCHAR和CHAR列可以多达64KB。

InnoDB索引 VS MyISAM索引

InnoDB索引:

InnoDB索引分为聚簇索引与非聚簇索引(二级索引、辅助索引)

PS:非聚簇索引也叫二级索引、辅助索引,不用多想,以后看到这些名字也别着急了。

  • 聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的
  • 非聚簇索引(二级索引、辅助索引):除主键外的其它字段建立的索引称为非聚簇索引(二级索引、辅助索引)。被索引的字段值作为索引数据,叶子节点还包含了主键值

其他功能支持:

  • 支持事务
  • 通过 undo log 支持事务回滚、当前读(多版本查询)
  • 通过 redo log 实现持久性
  • 通过两阶段提交实现一致性
  • 通过当前读、锁实现隔离性
  • 支持行锁、间隙锁
  • 支持外键

聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的

  • 主键即 7369、7499、7521 等

非聚簇索引(二级索引、辅助索引):除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据,叶子节点还包含了主键值

  • 上图中 800、950、1100 这些是工资字段的值,根据它们建立了二级索引

  • 上图中,如果执行查询 select empno, ename, sal from emp where sal = 800,这时候可以利用二级索引定位到 800 这个工资,同时还能知道主键值 7369
  • 但 select 字句中还出现了 ename 字段,在二级索引中不存在,因此需要根据主键值 7369 查询聚簇索引来获取 ename 的信息,这个过程俗称回表

MyISAM索引:

MyISAM索引只有一种:

  • 被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的

其他功能支持:

  • 不支持事务,没有 undo log 和 redo log
  • 仅支持表锁
  • 不支持外键
  • 会保存表的总行数

被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
|
6天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
6天前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
6天前
|
存储 缓存 关系型数据库
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
|
7天前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
166 100
MySQL底层概述—2.InnoDB磁盘结构
|
7天前
|
缓存 算法 关系型数据库
MySQL底层概述—1.InnoDB内存结构
本文介绍了InnoDB引擎的关键组件和机制,包括引擎架构、Buffer Pool、Page管理机制、Change Buffer、Log Buffer及Adaptive Hash Index。
156 97
MySQL底层概述—1.InnoDB内存结构
|
12天前
|
存储 SQL 缓存
MySQL原理简介—2.InnoDB架构原理和执行流程
本文介绍了MySQL中更新语句的执行流程及其背后的机制,主要包括: 1. **更新语句的执行流程**:从SQL解析到执行器调用InnoDB存储引擎接口。 2. **Buffer Pool缓冲池**:缓存磁盘数据,减少磁盘I/O。 3. **Undo日志**:记录更新前的数据,支持事务回滚。 4. **Redo日志**:确保事务持久性,防止宕机导致的数据丢失。 5. **Binlog日志**:记录逻辑操作,用于数据恢复和主从复制。 6. **事务提交机制**:包括redo日志和binlog日志的刷盘策略,确保数据一致性。 7. **后台IO线程**:将内存中的脏数据异步刷入磁盘。
|
2月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
2月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
82 7
|
3月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
203 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件