MySQL - 存储引擎MyISAM和Innodb

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: MySQL - 存储引擎MyISAM和Innodb

1. 概述

首先,我们先看一下MySQL的体系架构,对MySQL整个架构有一个直观的感受:

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

存储引擎在MySQL的体系架构中位于第三层,负责MySQL中数据的存储与提取,是与底层系统文件打交道的子系统。MySQL存储引擎是插件式的,执行器通过调用存储引擎的API接口操作数据,接口屏蔽了不同存储引擎之间的差异,可以根据不同的需求去选择不同的存储引擎。

存储引擎是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制,这种机制就叫作存储引擎。

存储引擎是针对表的而不是库,对于同一个库不同的表可以使用不同的存储引擎。常见的存储引擎有 MyISAM,InnoDB,Memory。

使用show engines命令,就可以查看当前数据库支持的引擎信息。

在5.5版本之前默认采用MyISAM存储引擎,从5.5开始采用InnoDB存储引擎。

  • InnoDB:支持事务,具有提交,回滚和崩溃恢复能力,事务安全;
  • MyISAM:不支持事务和外键,访问速度快;
  • Memory:利用内存创建表,访问速度非常快,因为数据在内存,而且默认使用Hash索引,但是一旦关闭,数据就会丢失;
  • Archive:归档类型引擎,仅能支持insert和select语句;
  • Csv:以CSV文件进行数据存储,由于文件限制,所有列必须强制指定not null,另外CSV引擎也不支持索引和分区,适合做数据交换的中间表;
  • BlackHole: 黑洞,只进不出,进来消失,所有插入数据都不会保存;
  • Federated:可以访问远端MySQL数据库中的表。一个本地表,不保存数据,访问远程表内容;
  • MRG_MyISAM:一组MyISAM表的组合,这些MyISAM表必须结构相同,Merge表本身没有数据,对Merge操作可以对一组MyISAM表进行操作。

2. MyISAM和InnoDB对比

InnoDB和MyISAM是使用MySQL最常用的两种存储引擎,我们重点来看下两者的区别:

  • 事务和外键

InnoDB支持事务和外键,具有安全性和完整性,适合大量insert或update操作

MyISAM不支持事务和外键,它提供高速存储和检索,适合大量的select查询操作

  • 锁机制

InnoDB支持行级锁,锁定指定记录。基于索引来加锁实现。

MyISAM支持表级锁,锁定整张表。

  • 索引结构

InnoDB使用聚集索引(聚簇索引),索引和记录在一起存储,既缓存索引,也缓存记录。

MyISAM使用非聚集索引(非聚簇索引),索引和记录分开。

  • 并发处理能力

InnoDB读写阻塞可以与隔离级别有关,可以采用多版本并发控制(MVCC)来支持高并发。

MyISAM使用表锁,会导致写操作并发率低,读之间并不阻塞,读写阻塞。

  • 存储文件

InnoDB表对应两个文件,一个.frm表结构文件,一个.ibd数据文件。InnoDB表最大支持64TB;

MyISAM表对应三个文件,一个.frm表结构文件,一个MYD表数据文件,一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

  • 适用场景

InnoDB

  1. 需要事务支持(具有较好的事务特性)
  2. 行级锁定对高并发有很好的适应能力
  3. 数据更新较为频繁的场景
  4. 数据一致性要求较高
  5. 硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,减少磁盘IO

MyISAM

  1. 不需要事务支持(不支持)
  2. 并发相对较低(锁定机制问题)
  3. 数据修改相对较少,以读为主
  4. 数据一致性要求不高

两种引擎该如何选择?

是否需要事务?有,InnoDB

是否存在并发修改?有,InnoDB

是否追求快速查询,且数据修改少?是,MyISAM

在绝大多数情况下,推荐使用InnoDB

附上各个引擎的对比图:

3. InnoDB

InnoDB是MySQL5.5.x开始默认的事务型引擎,也是使用最广泛的存储引擎。被设计用来处理大量短期事务的。

InnoDB所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),表的大小只受限于操作系统文件的大小。表的结构定义存在.frm后缀文件中,数据和索引集中存放在.idb后缀文件中。因为表数据和索引是在同一个文件,InnoDB的索引是聚簇索引。

InnoDB采用MVCC支持高并发,并且实现了四种标准的隔离级别(读未提交,读已提交,可重复读,可串行化),其默认级别是REPEATABLE-READ(可重复读),并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁不仅仅锁定查询涉及的行,还会对索引中的间隙行进行锁定,以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的,聚簇索引对主键的查询有很高的性能。但是InnoDB的非主键索引中必须包含主键列,所以如果主键列很大的话,非主键索引也会很大。如果一张表的索引较多,主键应该尽可能的小。关于索引,后面会详细讲解。

InnoDB的内部优化,包括磁盘预读(从磁盘读取数据时采用可预测性读取),自适应哈希(自动在内存中创建hash索引以加速读操作)以及能够加速插入操作的插入缓冲区。

4. MyISAM

在MySQL5.1及之前的版本,MyISAM是默认的存储引擎。提供了大量的特性,包括全文索引,压缩,空间函数等,但是不支持事务和行级锁,而且有一个严重的问题是奔溃后无法安全恢复。

MyISAM的数据表存储在磁盘上是3个文件,表结构定义存在.frm后缀文件中,表数据存储在.MYD后缀文件中,表索引存储在.MYI后缀文件中。表数据和表索引在不同的文件中,所以MyISAM索引是非聚簇索引。而且MyISAM可以存储表数据的总行数。

MyISAM表支持数据压缩,对于表创建后并导入数据以后,不需要修改操作,可以采用MyISAM压缩表。压缩命令:myisampack,压缩表可以极大的减少磁盘空间占用,因此也可以减少磁盘I/O,提高查询性能。而且压缩表中的数据是单行压缩,所以单行读取是不需要解压整个表。

5. Memory

Memory存储引擎的数据是存放在内存中的,所以如果服务器重启会导致数据丢失,但是表结构还是存在的表结构是以 .frm 后缀的文件中。

Memory默认hash索引,因此查询非常快。Memory表是表级锁,因此并发写入的性能较低。不支持BLOB或TEXT类型的列,并且每行的长度都是固定的,所以即使指定了varchar列实际存储也会转换成char,会导致内存浪费。

如果MySQL查询过程中需要使用临时表来保存中间结果,内部使用的临时表就是Memory表,如果中间结果太大超出Memory表的限制或者含有BLOB或TEXT字段,那么临时表会转换成MyISAM表。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
20天前
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 , Inno DB 引搞 是 MySQL 中 高 性 能 , 高 可靠 的 存 储选项 , 宽泛 应用于要求强 复杂交易处理场景 。
60 15
|
6月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
149 1
|
5月前
|
存储 SQL 关系型数据库
MySQL存储引擎简介
在选择相应的存储引擎时,需要充分考虑实际业务场景、性能需求和数据一致性要求,从而为数据管理提供最佳支持。
306 17
|
10月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1668 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
6月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
104 0
|
7月前
|
存储 算法 关系型数据库
InnoDB与MyISAM实现索引方式的区别?
首先两者都是用的是B+树索引,但二者的实现方式不同。 对于主键索引,InnoDB中叶子节点保存了完整的数据记录,而MyISAM中索引文件与数据文件是分离的,叶子节点上的索引文件仅保存了数据记录的地址. 对于辅助索引,InnoDB中辅助索引会对主键进行存储,查找时,先通过辅助索引的B+树在叶子节点获取对应的主键,然后使用主键在主索引B+树上检索操作,最终得到行数据;MyISAM中要求主索引是唯一的,而辅助索引可以是重复的,主索引与辅助索引没有任何区别,因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址
|
6月前
|
安全 关系型数据库 MySQL
MySQL8使用物理文件恢复MyISAM表测试
MySQL8使用物理文件恢复MyISAM表测试
88 0
|
7月前
|
存储 关系型数据库 MySQL
MYSQL支持的存储引擎有哪些, 有什么区别
MYSQL存储引擎有很多, 常用的就二种 : MyISAM和InnerDB , 者两种存储引擎的区别 ; ● MyISAM支持256TB的数据存储 , InnerDB只支持64TB的数据存储 ● MyISAM 不支持事务 , InnerDB支持事务 ● MyISAM 不支持外键 , InnerDB支持外键
|
11月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
340 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
10月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
224 7

推荐镜像

更多