带你扒一扒 MySQL 的数据在磁盘上到底长什么样子

前言

在之前 《大师，我悟了：为什么 MySQL 索引要用 B+tree ，而且还这么快？》 一文中我从索引的各种数据结构和大家聊到了 MySQL 底层索引的数据结构 B+tree 和工作原理。里面多处提到了找数据要从我们电脑的磁盘上找，今天就来说一说 MySQL 中的数据在磁盘上， 它到底是如何进行存储的 ？

存储引擎

百度百科是这样定义存储引擎的： MySQL 中的数据用各种不同的技术存储在文件（或者内存）中，这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎。

简单来说就是 不同的存储引擎，我们的数据存储的格式也会不一样。 就好比图片有不同的格式，比如： .jpg , .png , .gif 等等……

现在 MySQL 中 常用的 存储引擎有两种： MyISAM 和 InnoDB 。

MySQL 5.5之前 ， MyISAM 是默认的存储引擎。

MySQL 5.5开始 ， InnoDB 是默认的存储引擎。

主要区别

MyISAM InnoDB 事务 不支持:x: 支持 表/行锁 只有表锁 还引入了行锁 外键 不支持:x: 支持✔ 全文索引 支持✔ 版本5.6 开始支持 读写速度 更快 更慢

MyISAM 最致命的一点就是不支持事务，而 InnoDB 支持。所以现在 InnoDB 已经成为我们使用的标配、最主流的存储引擎了。

相关命令

查询当前数据库支持的存储引擎

show engines;

查询当前默认的存储引擎

show variables like '%storage_engine%';

查询表的相关信息

show table status like '表名';

MyISAM

每个 MyISAM 表都以3个文件存储在磁盘上。这些文件的名称以表名开头，以扩展名指示文件类型。

.frm 文件（frame）存储表 结构 ；

.MYD 文件（MY Data）存储表 数据 ；

.MYI 文件（MY Index）存储表 索引 。

MySQL 里的数据默认是存放在安装目录下的 data 文件夹中，也可以自己修改。

下面我创建了以 MyISAM 作为存储引擎的一张表 t_user_myisam。

.MYI 文件组织索引的方式就是 B+tree 。叶子节点的 value 处存放的就是 索引所在行的磁盘文件地址 。

底层查找过程 ：

首先会判断查找条件 where 中的字段是否是索引字段，如果是就会先拿着这字段去 .MYI 文件里通过 B+tree 快速定位，从根节点开始定位查找；

找到后再把这个索引关键字（就是我们的条件）存放的磁盘文件地址拿到 .MYD 文件里面找，从而定位到索引所在行的记录。

“

表逻辑上相邻的记录行数据在磁盘上并不一定是物理相邻的。

”

InnoDB

一张 InnoDB 表底层会对应 2个 文件在文件夹中进行数据存储。

.frm 文件（frame）存储表 结构 ；

.ibd 文件（InnoDB Data）存储表 索引+数据 。

下面我创建了以 InnoDB 作为存储引擎的一张表 t_user_innodb。

很显然， InnoDB 把索引和数据都放在一个文件里存着了。毫无疑问， InnoDB 表里面的数据也是用 B+tree 数据结构组织起来的。

下面我们来看看它具体是怎么存储的。

.ibd 存储数据的特点就是 B+tree 的叶子节点上 包括了我们要的索引和该索引所在行的其它列数据 。

底层查找过程 ：

首先会判断查找条件 where 中的字段是否是索引字段，如果是就会先拿着这字段去 .ibd 文件里通过 B+tree 快速定位，从根节点开始定位查找；

找到后直接把这个索引关键字及其记录所在行的其它列数据返回。

聚集（聚簇）索引

聚集索引 ：叶子节点包含了完整的数据记录。

简单来说就是索引和它所在行的其它列数据全部都在一起了。

很显然， MyISAM 没有聚集索引， InnoDB 有，而且 InnoDB 的主键索引就是天然的聚集索引。

有聚集索引当然就有 非聚集索引（稀疏索引） 。对于 MyISAM 来说，它的索引就是非聚集索引。因为它的 索引 和 数据 是 分开 两个文件存的：一个 .MYI 存索引，一个 .MYD 存数据。

为什么 DBA 都建议表中一定要有主键，而且推荐使用整型自增？

“

杠精请撤离：这里是推荐，没说一定。非要用 UUID 不拦着你:grin:

”

为什么要有主键？

因为 InnoDB 表里面的数据必须要有一个 B+tree 的索引结构来组织、维护我们的整张表的所有数据，从而形成 .idb 文件。

那和主键有什么关系？

如果 InnoDB 创建了一张没有主键的表，那这张表就有可能没有任何索引，则 MySQL 会选择所有具有唯一性并且不为 null 中的第一个字段的创建聚集索引。

如果没有唯一性索引的字段就会有一个隐式字段成为表的聚集索引：而这个隐式字段，就是 InnoDB 帮我们创建的一个长度为 6字节 的整数列 ROW_ID ，它随着新行的插入单调增加， InnoDB 就以该列对数据进行聚集。

使用这个 ROW_ID 列的表都共享一个相同的 全局 序列计数器（这是数据字典的一部分）。为了避免这个 ROW_ID 用完，所以建议表中一定要单独建立一个主键字段。

为什么推荐使用整型自增？

首先整型的 占用空间 会比字符串 小 ，而且在 查找 上 比大小 也会比字符串更 快 。字符串比大小的时候还要先转换成 ASCII 码再去比较。

如果 使用自增 的话，在 插入 方面的效率也会提高。

不使用自增，可能时不时会往 B+tree 的中间某一位置插入元素，当这个节点位置放满了的时候，节点就要进行分裂操作（效率低）再去维护，有可能树还要进行平衡，又是一个耗性能的操作。

都用自增就会永远都往后面插入元素，这样索引节点分裂的概率就会小很多。

二级索引

除聚集索引之外的所有索引都叫做二级索引，也称辅助索引。

它的叶子节点则不会存储其它所有列的数据，就 只存储主键值 。

底层查找过程 ：

每次要找数据的时候，会根据它找到对应叶子节点的主键值，再把它拿到聚集索引的 B+tree 中查找，从而拿到整条记录。

优点：保持一致性和节省空间。

参考资料

1. https://blog.jcole.us/innodb/

最后

今天和大家聊了 MySQL 数据在我们电脑中到底是如何进行存储的，从不同存储引擎展开说明，直到聚集索引和二级索引以及它们查找数据的过程。

后续将和大家谈谈工作中用到最多的 联合索引 ，和它的 最左前缀优化 又是怎么一回事。敬请期待！

如果本文对你有帮助的话不妨点个赞:+1:或者在看呦。

分享技术，稳住，我们能赢:muscle:！

大师，我悟了：为什么 MySQL 索引要用 B+tree ，而且还这么快？

【工作实录】我终于一次性刷新了所有服务的内存数据

HashMap 的 7 种遍历方式与性能分析！