MySql的存储引擎介绍

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 下面主要介绍InnoDB、MyISAM和MEMEORY三种存储引擎。 InnoDB存储引擎 InnoDB遵循CNU通用公开许可(GPL)发行。InnoDB已经被一些重量级互联网公司所采用,如雅虎、Slashdot和Google,为用户操作非常大的数据库提供了一个强大的解决方案。

下面主要介绍InnoDB、MyISAM和MEMEORY三种存储引擎。

InnoDB存储引擎

InnoDB遵循CNU通用公开许可(GPL)发行。InnoDB已经被一些重量级互联网公司所采用,如雅虎、Slashdot和Google,为用户操作非常大的数据库提供了一个强大的解决方案。InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。MySQL从3.23.34a开始包含InnoDB存储引擎。InnoDB是MySQL上第一个提供外键约束的表引擎,而且InnoDB对事务处理的能力,也是MySQL其他存储引擎所无法比拟的。下面介绍InnoDB存储引擎的特点及缺点。

InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空,且值必须唯一。MySQL中规定自增列必须为主键。在插入值时,如果自动增长列不输入值,则插入的值为自动增长后的值;如果输入的值为0或空(NULL),则插入的值也为自动增长后的值;如果插入某个确定的值,且该值在前面没有出现过,则可以直接插入。

InnoDB存储引擎中支持外键(FOREIGN KEY)。外键所在的表为子表,外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时,子表也必须有相应的改变。InnoDB存储引擎中,创建的表的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path表空间中。

InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写效率稍差,占用的数据空间相对比较大。

InnoDB表是以下情况的理想引擎:

更新密集的表:InnoDB存储引擎特别适合处理多重并发的更新请求。

事务:InnoDB存储引擎是唯一支持事务的标准MySQL存储引擎,这是管理敏感数据(如金融信息和用户注册信息)的必需软件。

自动灾难恢复:与其他存储引擎不同,InnoDB表能够自动从灾难中恢复。虽然MyISAM表能在灾难后修复,但其过程要长得多。

Oracle的InnoDB存储引擎广泛应用于基于MySQL的Web、电子商务、金融系统、健康护理以及零售应用。因为InnoDB可提供高效的ACID独立性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)兼容事务处理能力,以及独特的高性能和具有可扩展性的构架要素。

另外,InnoDB设计用于事务处理应用,这些应用需要处理崩溃恢复、参照完整性、高级别的用户并发数,以及响应时间超时。在MySQL 5.5中,最显著的增强性能是将InnoDB作为默认的存储引擎。在MyISAM以及其他表类型依然可用的情况下,用户无需更改配置,就可构建基于InnoDB的应用程序。

MyISAM存储引擎

MyISAM存储引擎是MySQL中常见的存储引擎,曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的,它解决了ISAM的很多不足。MyISAM增加了很多有用的扩展。

MyISAM存储引擎的文件类型

MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同。扩展名包括frm、myd和myi

frm:存储表的结构;

myd:存储数据,是MYData的缩写;

myi:存储索引,是MYIndex的缩写。

MyISAM存储引擎的存储格式

基于MyISAM存储引擎的表支持3种不同的存储格式,包括静态型动态型压缩型

(1)静态型。如果所有表列的大小都是静态的(即不使用xBLOB、xTEXT或VARCHAR数据类型),MySQL就会自动使用静态MyISAM格式。使用这种类型的表性能非常高,因为在维护和访问以预定义格式存储的数据时需要很低的开销。但是,这项优点要以空间为代价,因为每列都需要分配给该列的最大空间,而无论该空间是否真正地使用。

(2)动态型。如果有表列(即使只有一列)定义为动态的(使用xBLOB、xTEXT或VARCHAR数据类型),MySQL就会自动使用动态格式。虽然MyISAM动态表占用的空间比静态表所占空间少,但空间的节省带来了性能的下降。如果某个字段的内容发生改变,则其位置很可能就需要移动,这会导致碎片的产生。随着数据集中的碎片增加,数据访问性能就会相应降低。这个问题有两种修复方法:尽可能使用静态数据类型。经常使用OPTIMIZE TABLE语句,它会整理表的碎片,恢复由于表更新和删除而导致的空间丢失。

(3)压缩型。有时会创建在整个应用程序生命周期中都只读的表。如果是这种情况,就可以使用myisampack工具将其转换为MyISAM压缩表来减少空间。在给定硬件配置下(例如,快速的处理器和低速的硬盘驱动器),性能的提升将相当显著。

MyISAM存储引擎的优缺点

MyISAM存储引擎的优点在于占用空间小,处理速度快。缺点是不支持事务的完整性和并发性。

MEMORY存储引擎

MEMORY存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表,而且所有数据也放在内存中。这些特性都与InnoDB存储引擎、MyISAM存储引擎不同。下面将对MEMORY存储引擎的文件存储形式索引类型存储周期和优缺点进行讲解。

MEMORY存储引擎的文件存储形式

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同,类型为frm。该文件中只存储表的结构,而其数据文件,都是存储在内存中。这样有利于对数据的快速处理,提高整个表的处理效率。值得注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了,可以释放这些内容,甚至可以删除不需要的表。

MEMORY存储引擎的索引类型

MEMORY存储引擎默认使用哈希(HASH)索引。其速度要比使用B型树(BTREE)索引快。如果希望使用B型树索引,可以在创建索引时选择使用。

MEMORY存储引擎的存储周期

MEMORY存储引擎通常很少用到。因为MEMORY表的所有数据是存储在内存上的,如果内存出现异常就会影响到数据的完整性。如果重启机器或者关机,表中的所有数据将消失。因此,基于MEMORY存储引擎的表生命周期很短,一般都是一次性的。

MEMORY存储引擎的优缺点

MEMORY表的大小是受到限制的。表的大小主要取决于两个参数,分别是max_rowsmax_heap_table_size。其中,max_rows可以在创建表时指定;max_heap_table_size的大小默认为16MB,可以按需要进行扩大。因为其存在于内存中的特性,所以这类表的处理速度非常快。但是,其数据易丢失,生命周期短。

创建MySQL MEMORY存储引擎的出发点是速度。为得到最快的响应时间,采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提高性能,但要记住,当mysqld守护进程崩溃时,所有的MEMORY数据都会丢失。

MEMORY表不支持VARCHAR、BLOB和TEXT数据类型,因为这种表类型按固定长度的记录格式存储。此外,如果使用4.1.0之前的MySQL版本,则不支持自动增加列(通过AUTO_INCREMENT属性)。要记住MEMORY表只用于特殊的范围,不会用于长期存储数据。基于这个缺陷,选择MEMORY存储引擎时要特别小心。

当数据有以下情况时,可以考虑使用MEMORY表:

暂时:目标数据只是临时需要,在其生命周期中必须立即可用。

相对无关:存储在MEMORY表中的数据如果突然丢失,不会对应用服务产生实质的负面影响,而且不会对数据完整性有长期影响。

如果使用MySQL4.1以及之前版本,MEMORY的搜索比MyISAM表的搜索效果要低,因为MEMORY表只支持散列索引,这需要使用整个键进行搜索。但是,4.1之后的版本同时支持散列索引和B树索引。B树索引优于散列索引的是,可以使用部分查询和通配查询,也可以使用<、>和>=等操作符方便数据挖掘。

如何选择存储引擎

每种存储引擎都有各自的优势,不能笼统地说谁比谁更好,只有适合不适合。下面根据其不同的特性,给出选择存储引擎的建议。

InnoDB存储引擎:用于事务处理应用程序,具有众多特性,包括ACID事务支持、支持外键。同时支持崩溃修复能力和并发控制。如果对事务的完整性要求比较高,要求实现并发控制,那选择InnoDB存储引擎有很大的优势。需要频繁地进行更新、删除操作的数据库,也可以选择InnoDB存储引擎。因为,该类存储引擎可以实现事务的提交(Commit)和回滚(Rollback)。

MyISAM存储引擎:管理非事务表,它提供高速存储和检索,以及全文搜索能力。MyISAM存储引擎插入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择MyISAM存储引擎能实现处理的高效率。如果应用的完整性、并发性要求很低,也可以选择MyISAM存储引擎。

MEMORY存储引擎:MEMORY存储引擎提供“内存中”表,MEMORY存储引擎的所有数据都在内存中,数据的处理速度快,但安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求,不能建太大的表。所以,这类数据库只使用相对较小的数据库表。

 

 

 

 

 

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
2月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
80 7
|
4月前
|
存储 SQL 关系型数据库
MySQL存储引擎
本文介绍了数据库优化的多个方面,包括选择合适的存储引擎、字段定义原则、避免使用外键和触发器、大文件存储策略、表拆分及字段冗余处理等。强调了从业务层面进行优化的重要性,如通过活动设计减少外部接口调用,以及在高并发场景下的流量控制与预处理措施。文章还提供了具体的SQL优化技巧和表结构优化建议,旨在提高数据库性能和可维护性。
134 1
MySQL存储引擎
|
3月前
|
存储 缓存 关系型数据库
【赵渝强老师】MySQL的MyISAM存储引擎
在MySQL5.1版本之前,默认存储引擎为MyISAM。MyISAM管理非事务表,提供高速存储和检索,支持全文搜索。其特点包括不支持事务、表级锁定、读写互阻、仅缓存索引等。适用于读多、写少且对一致性要求不高的场景。示例代码展示了MyISAM存储引擎的基本操作。
|
3月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
3月前
|
存储 关系型数据库 MySQL
【赵渝强老师】MySQL的Memory存储引擎
MySQL 的存储引擎层负责数据的存储和提取,支持多种存储引擎,如 InnoDB、MyISAM 和 Memory。InnoDB 是最常用的存储引擎,从 MySQL 5.5.5 版本起成为默认引擎。Memory 存储引擎的数据仅存在于内存中,重启后数据会丢失。示例中创建了使用 Memory 引擎的 test3 表,并展示了数据在重启后消失的过程。
|
4月前
|
存储 SQL 缓存
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
|
5月前
|
存储 缓存 关系型数据库
MySQL高级篇——存储引擎和索引
MyISAM:不支持外键和事务,表锁不适合高并发,只缓存索引,内存要求低,查询快MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高表名.frm 存储表结构;表名.MYD 存储数据 (MYData);
MySQL高级篇——存储引擎和索引
|
6月前
|
存储 关系型数据库 MySQL
MySQL 中的事务存储引擎深入解析
【8月更文挑战第31天】
86 0
|
8月前
|
存储 关系型数据库 MySQL