Mysql存储引擎

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

本文目录：

1.MySQL体系结构

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

2.存储引擎介绍

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

3.MySQL 存储引擎特性

采用的是**插拔式**的插件方式；
存储引擎是指定在表之上的，即**一个库中的每一个表都可以指定选择存储引擎**；
不管表采用什么样的存储引擎，都会在数据区，产生对应的一个**.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文件存储。

特点：

不能定义索引，列定义必须为NOT NULL，不能设置自增列；（缺点：不适用于大表或者数据查询、排序等处理操作）

CSV数据的存储需要用,逗号隔开，可直接编辑 CSV文件进行数据的编排（缺点：数据安全性低）

可以对硬盘中保存的表文件数据进行直接编辑xxx.CSV文件，但是不能将该文件删除然后上传自己的同名.CSV文件(该文件中可能还有额外一些和表相关的数据)，我们只能对其中数据进行编辑操作。编辑之后，通过命令：flush table XXX表名的方式使其生效

应用场景：

数据的快速导入导出；

表格直接转换成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存储引擎，是以压缩协议进行数据的存储

特点：

只支持 insert 和 select 两种操作；（缺点：不适用于对数据的处理操作）

只允许自增ID列建立索引（缺点：还是不方便数据的处理）

支持行级锁

不支持事务

优点：数据占用磁盘少

应用场景：

日志系统；

大量的设备数据采集；

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’条件下高效，无法通过索引做区间查询。只能扫描全表。（范围查询、排序、组合索引效率不高）

特点：

数据都是存储在内存中，IO效率比其他引擎高很多；（优点：效率高）

服务重启后数据会丢失，内存数据表默认只有16M（缺点：保证不了持久性）

支持 Hash索引，B Tree索引，默认为 Hash索引

不支持大数据存储类型，如 blog，text 等

表级锁

应用场景：

等值查找热度较高数据；

查询结果在内存中进行计算，大多数都是采用这种存储引擎作为临时表存储需要计算的数据；

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

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

6.4 (重要)InnoDB存储引擎

6.4.1 InnoDB介绍

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

特点：

InnoDB 支持事务操作；（每一条SQL都默认封装成事务，自动提交，会影响速度）

InnoDB 支持外键；

InnoDB 是聚集索引（聚簇索引）；

InnoDB 不保存表的总条数；

InnoDB 5.7版本之前不支持全文检索；

InnoDB 支持表级锁、行级锁，默认为行级锁；

InnoDB 表必须有主键（如果我们没有明确去指定创建主键索引。它会帮我们隐藏的生成一个 6 byte 的 int 型的索引作为主键索引）；

InnoDB 文件存储方式为.frm文件存储表结构，ibd文件存储数据内容。

名词解释：

什么是聚集(簇)索引？

聚簇索引的特点是叶子节点包含了完整的记录行，而非聚簇索引的叶子节点只有所以字段和主键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 索引的另一种重要体现形式。

特点：

MyISAM 是非聚集索引；

MyISAM 有一个变量专门来保存整个表的行数，查询count很快(注意不能加任何 where 条件)

MyISAM 支持全文索引；

MyISAM 可以被压缩后进行查询操作，节省空间容量；

MyISAM 支持表级锁，不支持行级锁；

MyISAM 中主键不是必须的；

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介绍）

在这里插入图片描述

如何选择?

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

posted @ 2023-06-23 12:47 哩个啷个波阅读(26) 评论(0) 编辑收藏举报

刷新页面返回顶部

哩个啷个波