24

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

 4 years ago
source link: https://mp.weixin.qq.com/s/EP08i39J6Hu5xMV3fdcmCg
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

前言

在之前 《大师，我悟了：为什么 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 文件夹中，也可以自己修改。

EbEbEz6.png!mobile

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

2IB3UnI.png!mobile

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

NJBfau.png!mobile

底层查找过程 ：

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

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

“

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

”

QNRFZbb.gif!mobile

InnoDB

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

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

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

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

IF7JZr7.png!mobile

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

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

6NnYRv.png!mobile

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

底层查找过程 ：

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

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

N3Ifym6.gif!mobile

聚集（聚簇）索引

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

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

很显然， 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 的中间某一位置插入元素，当这个节点位置放满了的时候，节点就要进行分裂操作（效率低）再去维护，有可能树还要进行平衡，又是一个耗性能的操作。

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

二级索引

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

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

bQZr6v7.png!mobile

底层查找过程 ：

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

7RRV3qq.gif!mobile

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

参考资料

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

最后

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

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

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

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

推荐阅读

点个在看 你最好看


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK