一文带你看懂 MySQL 存储引擎

本文目录：

- 1.MySQL体系结构
- 2.存储引擎介绍
- 3.MySQL 存储引擎特性
- 4.MySQL 有哪些存储引擎
- 5.了解 MySQL 数据存储方式
- 6.MySQL存储引擎介绍
- - 6.1 CSV存储引擎
  - - 6.1.1 CSV介绍
    - 6.1.2 使用CSV存储引擎建表语句
    - 6.1.3 查看数据所在硬盘位置
  - 6.2 Archive存储引擎
  - - 6.2.1 Archive介绍
    - 6.2.2 使用Archive存储引擎建表语句
    - 6.2.3 Archive对比InnoDB，使用函数批量存储1w条数据
    - 6.2.4 查看数据所在硬盘位置
    - 6.2.5 使用命令对比数据容量占用情况
  - 6.3 Memory存储引擎
  - - 6.3.1 Memory介绍
  - 6.4 (重要)InnoDB存储引擎
  - - 6.4.1 InnoDB介绍
    - 6.4.2 使用InnoDB存储引擎建表语句
    - 6.4.3 了解 InnoDB 硬盘存储方式
    - 6.4.4 .frm 和 .ibd 格式文件介绍
  - 6.5 (重要)MyISAM存储引擎
  - - 6.5.1 MyISAM介绍
    - 6.5.2 使用MyISAM存储引擎建表语句
    - 6.5.3 了解 MyIsam 硬盘存储方式
    - 6.5.4 .frm | .MYD | .MYI 格式文件介绍
- 7. (重点)MyISAM 和 InnoDB 的区别（介绍特详细哦）

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

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

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

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

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

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