MySQL存储引擎详述:InnoDB为何胜出?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。

MySQL作为当前最流行的开源关系型数据库之一,其强大的功能和良好的性能使其广泛应用于各种规模的应用系统中。其中,存储引擎的设计理念是MySQL数据库灵活高效的关键所在。


添加图片注释,不超过 140 字(可选)


一、什么是存储引擎


添加图片注释,不超过 140 字(可选)


存储引擎是MySQL架构的重要组成部分,负责MySQL中数据的存储和提供了视图,存储过程等高级服务。简单来说,存储引擎就是数据库底层的引擎,用于处理SQL命令和底层的数据操作。不同的存储引擎支持不同的功能,可以根据应用场景选择最适合的存储引擎。

二、MySQL常见存储引擎


添加图片注释,不超过 140 字(可选)


从MySQL 5.5版本开始,默认的存储引擎由MyISAM改为InnoDB。常见的存储引擎及其特点如下:

InnoDB

InnoDB是MySQL 5.5版本后的默认存储引擎,完全支持ACID事务特性和行级锁,实现了SQL标准的四种隔离级别,支持外键约束。InnoDB使用聚集索引存放数据,在主键查询上有很高的效率。是当前使用最广泛的存储引擎。

MyISAM

MyISAM是MySQL早期的默认存储引擎,占用资源较少,读取数据快。但是不支持事务和行级锁,无外键约束等特性,已经逐渐被InnoDB取代。

Memory

Memory存储引擎将表中的数据存储在RAM中,因此读写速度非常快。但是重启服务后,表中数据会丢失。使用场景一般是临时存储数据等。

CSV

CSV存储引擎将表的数据存储在文本文件中,其格式为逗号分隔值文件,可以方便地将数据导出为文件。相比其他存储引擎,它的性能和功能都较差。

Archive

Archive存储引擎只支持INSERT和SELECT操作,用于日志存档等只需读取的应用场景。

Federated

Federated存储引擎将表的数据存储在远程的MySQL服务器中,本地只是一个指向远程表的指针。

除此之外,MySQL还有NDB、Maria等其他存储引擎,但使用较少。从应用场景来看,绝大多数情况下都应该选择InnoDB存储引擎。

三、曾经InnoDB vs MyISAM


添加图片注释,不超过 140 字(可选)


事务支持

InnoDB 支持事务,是有锁机制的,能够确保数据的一致性和完整性。MyISAM 不支持事务,在执行查询时允许其他用户对表进行插入、更新和删除操作,可能会导致数据不一致。这是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一。

外键约束

InnoDB 支持外键约束,能保证数据的参照完整性。而 MyISAM 不支持外键,在进行数据操作时不能保证关联数据的完整性,可能会出现数据孤立或数据丢失。

索引类型

InnoDB 使用聚集索引,数据文件本身按主键的顺序进行存放,所以主键查询效率很高。MyISAM 使用非聚集索引,数据文件是独立于索引文件的,所以需要根据索引文件中的指针,再去数据文件中查找对应的数据。InnoDB 辅助索引需要两次查找,MyISAM 无论是主键索引还是辅助索引都只需要一次查找。

计数效率

InnoDB 存储引擎需要实时维护数据行数,执行 SELECT COUNT(*) 时需要进行全表扫描。而 MyISAM 存储引擎在设计上借助了一个变量来存储表的具体行数,因此可以快速返回表行数。 当然这里说的SELECT COUNT(*) 是没有增加WHERE条件,如果增加了WHERE条件MyISAM还是需要遍历的。

锁粒度

InnoDB 的最小锁粒度是行锁,支持行级锁定,可以有效地支持高并发操作。MyISAM 最小锁粒度是表锁,只支持全表锁定,在进行读写操作时需要获取表级别的锁,并发性能差。这也是MySQL将默认引擎从MyISAM变成InnoDB的重要原因之一。

p.s.另外在崩溃恢复能力、热备份、克隆等方面两者也有不少差别。

四、如今InnoDB vs MyISAM


添加图片注释,不超过 140 字(可选)


  • 从5.7开始,InnoDB更快
  • 在8.0中InnoDB性能不断提高
  • InnoDB 中整个事务层得到了极大的改进
  • 原本临时表MyISAM 更好,在8中磁盘内部临时表只支持InnoDB
  • 原本批量加载时MyISAM的性能优于InnoDB,禁用 InnoDB持久性后InnoDB更快
  • 原本COUNT(*)MyISAM 的速度更快,现在情况已不再如此。在多核环境下InnoDB更快
  • 从MySQL 5.6开始,InnoDB 支持全文搜索 (FTS),从那时起它的扩展能力比MyISAM好3倍
  • 云管理的MySQL服务均不包含MyISAM。Oracle云基础设施中的MySQL数据库服务也是如此。


添加图片注释,不超过 140 字(可选)


官方blog的大牛也在建议大家都使用InnoDB,InnoDB已经优于MyISAM


添加图片注释,不超过 140 字(可选)


比如官方这个测试报告,在并发数据的增加InnoDB吞吐量会趋于稳定,而MyISAM在高并发下还会有所下降。当然此时还有新建与修改的操作时MyISAM是表级锁就会更慢了。

五、应用场景

1)InnoDB 适用场景

事务支持应用

InnoDB 支持事务处理,符合 ACID 原则,能够保证数据的原子性、一致性、隔离性和持久性,适合需要事务支持的应用场景。

高并发读写环境

InnoDB 采用行级锁定,支持非锁定并发读,可以有效提高多用户并发读写操作的性能,适合高并发读写的应用环境。

数据完整性要求高

InnoDB 支持外键约束,能有效保证数据的参照完整性,防止出现数据孤立或丢失情况,适合对数据完整性要求较高的场合。

2)MyISAM 适用场景

读密集型应用

MyISAM 只有表级锁,读操作不会阻塞其他读,而只是阻塞写,因此适合读密集的操作环境。

事务非必需环境

MyISAM 不支持事务和外键,但可以节省系统开销,如果应用对事务完整性要求不高,可以选择 MyISAM。


六、选择建议

对于大多数应用场景,我们都建议使用InnoDB存储引擎,因为它支持事务、外键约束等高级特性,而且也能够获得较好的性能表现。只有在不需要事务支持,且对全文索引有较高需求的场景下,才考虑使用MyISAM存储引擎。正如上面说的在多核CPU的情况下COUNT(*)加条件 InnoDB优于MyISAM,如果硬盘条件一般的情况下或者别的情况下MyISAM在查询的速度上应该还是会比InnoDB快。

MySQL从5.5版本开始就将InnoDB设置为默认的存储引擎,这也从侧面体现了InnoDB在当前的重要地位。另外官方Blog文章也表达出在高并发场景下InnoDB的表现也会优于MyISAM。当然,根据具体的应用需求来选择合适的存储引擎,才是最佳的实践方式。



相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8天前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
174 100
MySQL底层概述—2.InnoDB磁盘结构
|
8天前
|
缓存 算法 关系型数据库
MySQL底层概述—1.InnoDB内存结构
本文介绍了InnoDB引擎的关键组件和机制,包括引擎架构、Buffer Pool、Page管理机制、Change Buffer、Log Buffer及Adaptive Hash Index。
162 97
MySQL底层概述—1.InnoDB内存结构
|
5天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
|
8天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
8天前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
8天前
|
存储 缓存 关系型数据库
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
|
13天前
|
存储 SQL 缓存
MySQL原理简介—2.InnoDB架构原理和执行流程
本文介绍了MySQL中更新语句的执行流程及其背后的机制,主要包括: 1. **更新语句的执行流程**:从SQL解析到执行器调用InnoDB存储引擎接口。 2. **Buffer Pool缓冲池**:缓存磁盘数据,减少磁盘I/O。 3. **Undo日志**:记录更新前的数据,支持事务回滚。 4. **Redo日志**:确保事务持久性,防止宕机导致的数据丢失。 5. **Binlog日志**:记录逻辑操作,用于数据恢复和主从复制。 6. **事务提交机制**:包括redo日志和binlog日志的刷盘策略,确保数据一致性。 7. **后台IO线程**:将内存中的脏数据异步刷入磁盘。
|
27天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
13天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
95 42
|
4天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
50 25