首页 > 数据库 >Mysql存储引擎

Mysql存储引擎

时间：2023-06-23 12:55:37浏览次数：65  
标签：存储 Mysql 索引 引擎 InnoDB MyISAM MySQL

原文链接:https://blog.csdn.net/lzb348110175/article/details/106555504

本文目录：

1.MySQL体系结构

​ **开篇先来一张MySQL体系图，让你来了解一下存储引擎所在的位置。**如果你对 MySQL 体系结构还不是很熟悉，你可以查看图片中的备注信息。你也可以跳转到：了解MySQL体系结构 来具体了解专业名词的介绍。
在这里插入图片描述

2.存储引擎介绍

​ 关系型数据库，就是用来存储各种数据信息的。根据不同业务场景，**比如说：有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表需要查询非常快**。在我们实际的业务开发中，可能需要用到各种各样的表，不同的表也意味着存储不同类型的数据，数据的处理上也就会存在着差异。**针对 MySQL 来说，它提供了很多类型的存储引擎来供我们选择，我们可以根据业务需求来选择不同的存储引擎，最大程度的发挥 MySQL 的强大之处。**

3.MySQL 存储引擎特性

  1. 采用的是**插拔式**的插件方式；
  2. 存储引擎是指定在表之上的，即**一个库中的每一个表都可以指定选择存储引擎**；
  3. 不管表采用什么样的存储引擎，都会在数据区，产生对应的一个**.frm**文件（表结构定义描述文件，下文有介绍。好像是在MySQL 8.0中，.frm 文件已经不存在了，此处以MySQL5.7介绍）

4.MySQL 有哪些存储引擎

​ 我们可以使用命令：**show engines;**来查看 MySQL 支持哪些存储引擎，如下图所示。我们可以看到 **MySQL 默认使用的存储引擎是 InnoDB**(下图 MySQL 使用的版本为5.7.28）。

​ **注意：在MySQL 5.5之前默认的存储引擎是 MyISAM，5.5版本及以后默认存储引擎修改为 InnoDB，具体5.5.x 哪个小版本号不清楚，讨论这也没什么意义)。**

​ 在工作中，经常用到的还是**InnoDB**和**MyISAM**这两种，其他存储引擎在指定一些场景下会更加的高效。接下来就从几个方面来对**InnoDB**、**MyISAM**、**CSV**、**Archive**、**Memory** 这五种存储引擎做一个基本的了解。
在这里插入图片描述

5.了解 MySQL 数据存储方式

​ 在开篇已经了解到，**MySQL使用的是硬盘来进行数据存储的**。那么它最终存储在哪个地方了呢？这个可以在我们搭建 MySQL 数据库时指定，我们也可以通过命令：**show variables like ‘datadir’**来查看具体存储为止。(此处命令是两个单引号，CSDN MarkDown编辑器的问题)
在这里插入图片描述
​ 进入/usr/local/mysql/data 目录，再进入到 db_store 目录下，我们便能够很真实的看到数据库中的数据在硬盘中存储。如下图所示。在进入文件夹，便是具体的数据库表信息了。在这里插入图片描述
在这里插入图片描述

6.MySQL存储引擎介绍

Tips：以下所有内容，基于 MySQL 5.7.28 版本介绍（CSV、Archive、Memory、InnoDB、MyISAM。其它引擎就不过多介绍）

6.1 CSV存储引擎

6.1.1 CSV介绍

CSV存储引擎，数据是以CSV文件存储。

特点：

  1. 不能定义索引，列定义必须为NOT NULL，不能设置自增列；（缺点：不适用于大表或者数据查询、排序等处理操作）
  2. CSV数据的存储需要用,逗号隔开，可直接编辑 CSV文件进行数据的编排（缺点：数据安全性低）
  3. 可以对硬盘中保存的表文件数据进行直接编辑xxx.CSV文件，但是不能将该文件删除然后上传自己的同名.CSV文件(该文件中可能还有额外一些和表相关的数据)，我们只能对其中数据进行编辑操作。编辑之后，通过命令：flush table XXX表名的方式使其生效

应用场景：

  1. 数据的快速导入导出；
  2. 表格直接转换成CSV；

6.1.2 使用CSV存储引擎建表语句

CREATE TABLE `users_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(255) NOT NULL
) ENGINE=csv
123456

6.1.3 查看数据所在硬盘位置

通过上文5.了解 MySQL 数据存储方式，进入到存储数据所在的硬盘下，便可以看到 users_csv 相关的表（.frm 为表结构文件；.CSV为数据文件，.CSM不了解是干嘛的）
在这里插入图片描述

6.2 Archive存储引擎

6.2.1 Archive介绍

Archive存储引擎，是以压缩协议进行数据的存储

特点：

  1. 只支持 insert 和 select 两种操作；（缺点：不适用于对数据的处理操作）
  2. 只允许自增ID列建立索引（缺点：还是不方便数据的处理）
  3. 支持行级锁
  4. 不支持事务
  5. 优点：数据占用磁盘少

应用场景：

  1. 日志系统；
  2. 大量的设备数据采集；

6.2.2 使用Archive存储引擎建表语句

CREATE TABLE `users_archive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=Archive
1234567

6.2.3 Archive对比InnoDB，使用函数批量存储1w条数据

//批量添加函数:
CREATE DEFINER=`root`@`%` PROCEDURE `users_archive_batchAdd`()
begin
    declare i int;
    set i=1;
    while i<10000 do
				INSERT INTO users_archive(id,name,age,address) VALUES(i,concat('James',i),i,concat('BeiJing',i));
        set i=i+1;
     end while;
end
12345678910

6.2.4 查看数据所在硬盘位置

通过上文5.了解 MySQL 数据存储方式，进入到存储数据所在的硬盘下，便可以看到 users_archive 相关的表（.frm 为表结构文件；.ARZ为数据文件）
在这里插入图片描述

6.2.5 使用命令对比数据容量占用情况

命令：du -s -h *
在这里插入图片描述

6.3 Memory存储引擎

6.3.1 Memory介绍

Memory存储引擎（在以往的版本中，又叫 Heap 存储引擎）。数据存储在内存中，在实际的应用场景中用到的很少，但是它与优化器查询有很大的关系，对临时表很有用。（不了解优化器查询？？请点击链接：不会MySQL调优？来来瞅瞅SQL的执行计划吧了解）

Memory中基于Hash索引，底层是 Hash 表，对于精确查询非常高效。即：在’='、'in’条件下高效，无法通过索引做区间查询。只能扫描全表。（范围查询、排序、组合索引效率不高）

特点：

  1. 数据都是存储在内存中，IO效率比其他引擎高很多；（优点：效率高）
  2. 服务重启后数据会丢失，内存数据表默认只有16M（缺点：保证不了持久性）
  3. 支持 Hash索引，B Tree索引，默认为 Hash索引
  4. 不支持大数据存储类型，如 blog，text 等
  5. 表级锁

应用场景：

  1. 等值查找热度较高数据；
  2. 查询结果在内存中进行计算，大多数都是采用这种存储引擎作为临时表存储需要计算的数据；

工作中，使用内存管理这块，我们根本不需要考虑 Memory 这种存储引擎， Redis、Memcache 等它们不香吗？？Memory存储引擎更多的使用在 MySQL 优化器查询阶段。下面简单介绍一下：

在优化器查询阶段，会以生成一个 template 临时表的方式查询。它的内存数据表默认只有 16M，并不支持大数据类型的存储字段。在查询阶段，如果满足 Memory 存储引擎的所有条件，就会在优化器查询阶段使用 Memory 存储引擎。如果不满足条件，则会使用 MyISAM 引擎。

6.4 (重要)InnoDB存储引擎

6.4.1 InnoDB介绍

在 MySQL 5.5 及以后版本后，MySQL 选择使用 InnoDB为默认存储引擎。在创建数据库表时，不指定存储引擎时，使用的就是 InnoDB。如需使用其他存储引擎，可以手动来指定。

特点：

  1. InnoDB 支持事务操作；（每一条SQL都默认封装成事务，自动提交，会影响速度）
  2. InnoDB 支持外键；
  3. InnoDB 是聚集索引（聚簇索引）；
  4. InnoDB 不保存表的总条数；
  5. InnoDB 5.7版本之前不支持全文检索；
  6. InnoDB 支持表级锁、行级锁，默认为行级锁；
  7. InnoDB 表必须有主键（如果我们没有明确去指定创建主键索引。它会帮我们隐藏的生成一个 6 byte 的 int 型的索引作为主键索引）；
  8. InnoDB 文件存储方式为.frm文件存储表结构，ibd文件存储数据内容。

名词解释：

  1. 什么是聚集(簇)索引？
     
      聚簇索引的特点是叶子节点包含了完整的记录行，而非聚簇索引的叶子节点只有所以字段和主键ID。

    MySQL 索引采用 B+Tree（不熟悉？来跳转链接了解：MySQL 索引底层为什么选择B+Tree）。InnoDB 和 MyISAM 作为 MySQL 中 B+Tree 索引的两种重要体现形式。

    InnoDB 推荐以主键作为索引来组织数据进行存储，它认为主键是一个非常重要的属性。InnoDB 表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键来构造一个B+树，叶子节点中存放的就是整张表的数据。

    一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁 update 这一点。

    （估计你还看不懂，哈哈。那就看看这里你就懂了---->MySQL一张表，比如有id(主键)，name，age等字段。我们可以建很多个索引，MySQL除了主键索引外，都是非聚簇索引。即只有我们创建的主键id索引，我们可以叫他id索引，它别名又叫做聚簇索引，（因为只有id索引，叶子节点包含了完整的记录行）理解成一个别名的即可。如果我们再创建一个name索引，它就叫做非聚簇索引，或者辅助索引。）【也可参考：MyISAM 和 InnoDB 的区别 文中介绍的聚簇索引】

    我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据；

InnoDB 支持的一些特性，我们在此无法验证，你只需要记住就好了。现在我们有一个**db_store库**，库中我们创建一个 employee 表，我们来看一下它在硬盘中的存储情况。

6.4.2 使用InnoDB存储引擎建表语句

CREATE TABLE `employee` (
  `employeeID` int(11) NOT NULL,
  `userName` varchar(16) COLLATE utf8_bin DEFAULT NULL,
  `phoneNum` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  `lastUpdate` datetime DEFAULT NULL,
  PRIMARY KEY (`employeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  -- 我们可以看出使用的 ENGINE=INNODB
123456789

6.4.3 了解 InnoDB 硬盘存储方式

通过上文5.了解 MySQL 数据存储方式，进入/usr/local/mysql/data 目录，再进入到 db_store 目录下，我们便能够很真实的看到数据库中的数据在硬盘中存储，它其实就是以**两个文件（employee.frm、employee.ibd）**的方式体现。(此处可以看到有employee 和 store 两个，说明 db_store 库下有两个表)
在这里插入图片描述

6.4.4 .frm 和 .ibd 格式文件介绍

.frm文件：保存的是表结构定义描述文件
.ibd文件：保存的是employee表中的数据内容
在这里插入图片描述

6.5 (重要)MyISAM存储引擎

6.5.1 MyISAM介绍

MyISAM 作为 MySQL 中 B+Tree 索引的另一种重要体现形式。

特点：

  1. MyISAM 是非聚集索引；
  2. MyISAM 有一个变量专门来保存整个表的行数，查询count很快(注意不能加任何 where 条件)
  3. MyISAM 支持全文索引；
  4. MyISAM 可以被压缩后进行查询操作，节省空间容量；
  5. MyISAM 支持表级锁，不支持行级锁；
  6. MyISAM 中主键不是必须的；
  7. MyISAM 文件存储方式为.frm文件存储表结构，.MYD文件存储数据内容，.MYI文件存储索引文件。

现在我们在**db_store库**，库中我们创建一个 employee_myisam 表，来看一下它在硬盘中的存储情况。

6.5.2 使用MyISAM存储引擎建表语句

CREATE TABLE `employee_myisam` (
  `employeeID` int(11) NOT NULL,
  `userName` varchar(16) COLLATE utf8_bin DEFAULT NULL,
  `phoneNum` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  `lastUpdate` datetime DEFAULT NULL,
  PRIMARY KEY (`employeeID`)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_bin;  -- 我们可以看出使用的 ENGINE=MyISAM
123456789

6.5.3 了解 MyIsam 硬盘存储方式

通过上文5.了解 MySQL 数据存储方式，进入/usr/local/mysql/data 目录，再进入到 db_store 目录下，我们便能够很真实的看到数据库中的数据在硬盘中存储，它其实就是以**三个文件（xxx.frm、xxx.MYD、xxx.MYI）**的方式体现。
在这里插入图片描述

6.5.4 .frm | .MYD | .MYI 格式文件介绍

.frm文件：保存的是表结构定义描述文件
.MYD文件：保存的是数据内容
.MYI文件：保存的是索引内容在这里插入图片描述

7. (重点)MyISAM 和 InnoDB 的区别（介绍特详细哦）

MyISAM 和 InnoDB 作为 MySQL 两大常用的存储引擎，经常在面试中被提及到。本文就来介绍一下它们之间的区别。** 如果你想了解MySQL 存储引擎相关内容，请跳转链接：一文带你看懂 MySQL 存储引擎

区别：

1.InnoDB 支持事务，MyISAM 不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的InnoDB表转为MYISAM会失败； (外键现在用的也不多，因为它关联性太强，如果要删除一个表，会因为有外键的关联而导致删除失败。通常是通过 table a = table b on a.id = b.id 这种两表关联的方式来间接替代外键作用 )

3.InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的；MyISAM是非聚集索引，它也是使用B+Tree作为索引结构，但是索引和数据文件是分离的，索引保存的是数据文件的指针。
在这里插入图片描述
在这里插入图片描述
  4.InnoDB 必须要有主键，MyISAM可以没有主键；InnoDB 如果我们没有明确去指定创建主键索引。它会帮我们隐藏的生成一个 6 byte 的 int 型的索引作为主键索引。

5.InnoDB辅助索引和主键索引之间存在层级关系；MyISAM辅助索引和主键索引则是平级关系。即：InnoDB 如果添加其他辅助索引，辅助索引查询就需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也相应都会很大。（比如表中有id(主键),name,age 字段，我们创建一个主键索引，再来创建一个name索引，层级关系如下图所示）
在这里插入图片描述
在这里插入图片描述
  6. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

7. Innodb不支持全文索引，而MyISAM支持全文索引，在全文索引领域的查询效率上MyISAM速度更快高；（MySQL 5.7 版本以后，InnoDB也支持全文索引了）

8. InnoDB支持表级锁、行级锁，默认为行级锁；而 MyISAM 仅支持表级锁。InnoDB 的行锁是实现在索引上的，而不是锁在物理行上。如果访问未命中索引，也是无法使用行锁，将会退化为表锁

9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。【InnoDB 中，.frm文件：保存的是表结构定义描述文件；.ibd文件：保存的是employee表中的数据内容】；【MyISAM中，.frm文件：保存的是表结构定义描述文件，.MYD文件：保存的是数据内容，.MYI文件：保存的是索引内容】（好像是在MySQL 8.0中，.frm 文件已经不存在了，此处以MySQL5.7介绍）

在这里插入图片描述
在这里插入图片描述

如何选择?

  1. 判断是否需要支持事务，如果要请选择 InnoDB，如果不需要可以考虑MyISAM；
  2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读也有写，那还是使用InnoDB吧。
  3. 系统奔溃后，MyISAM恢复起来更困难；
  4. MySQL5.5 版本开始 InnoDB 已经成为 MysQL 的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

标签：存储,Mysql,索引,引擎,InnoDB,MyISAM,MySQL
From： https://www.cnblogs.com/javaxubo/p/17499014.html

相关文章

  • mysql的数据类型以及mysql中的int11是什么意思
    今天抽时间来讲一下mysql里的知识点，之前有不少人问过我，mysql中的int(11)，这个11到底是啥意思？是11位的意思吗？你是否也想过这个问题，是否也有这个疑问？ok，今天就展开来讲一下，用通俗易懂的大白话来给你彻底搞明白一、跟你扯点二进制的小东西要讲清楚这个问题，我先来给大家科普一点计算......
  • C:\Windows\Temp 目录是 Windows 操作系统中用于存储临时文件的默认目录。在这个目
    C:\Windows\Temp目录是Windows操作系统中用于存储临时文件的默认目录。在这个目录下，系统和应用程序可能会创建、存储和临时使用各种文件。临时文件通常是在程序运行期间产生的，用于存储临时数据、缓存数据或执行特定任务。它们可以包括但不限于以下类型的文件：安装程序临时文件......
  • C:\Windows\WinSxS 目录是 Windows 操作系统中一个重要的系统目录，它包含了组件存储
    C:\Windows\WinSxS目录是Windows操作系统中一个重要的系统目录，它包含了组件存储和组件映像服务（ComponentStoreandComponentBasedServicing）的相关文件。WinSxS目录主要用于存储操作系统及其组件的多个版本和配置文件。它的主要功能是支持系统的“部署”、“维护”和“修......
  • MySQL事务四大隔离级别分析
    什么是事务？事务，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务的四大特性原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部都执行，要么都不执行。一致性：指在事务开始之前和事务结束以后，数据不会被破坏，......
  • kubernetes使用ceph作为持久化存储
    1.资源规划官方：https://ceph.com/en官方文档：https://docs.ceph.com/en/latest/start/introceph是一种开源的分布式的存储系统包含以下几种存储类型：块存储（rbd），对象存储(RADOSFateway)，文件系统（cephfs）介绍：本篇文件主要是在ubuntu22.04本地去搭建一套ceph集群，后续使用storagec......
  • C:\Windows\Logs 是 Windows 操作系统中用于存储日志文件的默认目录。在这个目录下，
    C:\Windows\Logs是Windows操作系统中用于存储日志文件的默认目录。在这个目录下，你可以找到各种与系统运行、应用程序和服务相关的日志文件。一些常见的日志文件包括：Application.log：记录应用程序的活动和错误信息。System.log：记录系统级别的事件和错误信息。Security.log：记......
  • 搜索引擎如何优化
    1、了解搜索引擎如何抓取网页和如何索引网页。2、Meta标签优化。3、如何选取关键词并在网页中放置关键词。4、了解主要的搜索引擎。5、主要的互联网目录6、按点击付费的搜索引擎。7、搜索引擎登录。8、链接交换和链接广泛度（LinkPopularity）。9、标签的合理使用:标签的语义......
  • 服务器和存储入门
    收藏：服务器和存储入门Cloud研习社 Cloud研习社 2023-06-2107:31 发表于山东收录于合集#服务器12个#云计算251个#计算机223个第1章、什么是服务器服务器的逻辑架构仍然遵循冯·诺依曼架构，主要包含：处理器（CPU、GPU、DPU等）、存储器、I/O接口，以及SSD、BMC、PCIe插......
  • Mysql 事务
    1.事务基本特性ACID1.1原子性：     指的是一个事务中的操作要么全部成功，要么全部失败。1.2一致性     指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修......
  • mysql
    一登录mysql 1.windowsCMD登录输入mysql -uroot-p1234562.mysql命令界面登录：输入密码1234563. 命令行登录：在navicat中连接mysql输入密码123456二 初识mysql数据库简介1.为何需要数据库可以持久化2.数据库能够做什么存储大量数据，方便检索和访问（数据库中的数据量大）......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99