MySQL数据存储格式深度解析

MySQL数据存储格式深度解析

引用

CSDN

1.

https://blog.csdn.net/weixin_44817884/article/details/138714931

MySQL作为一款广泛使用的开源关系型数据库管理系统，其数据存储格式对于数据库的性能和效率有着至关重要的影响。本文将深入探讨MySQL中InnoDB和MyISAM两种存储引擎的数据存储方式，以及InnoDB存储引擎中ibd文件的存储格式，帮助读者更好地理解MySQL的数据存储机制。

一、整体存储逻辑

1.1 MySQL数据存放位置

不同的存储引擎对MySQL数据的存储方式有所不同。以InnoDB和MyISAM存储引擎为例：

InnoDB存储引擎

• .frm文件：存放表结构的定义信息。
• .ibd文件或.ibdata文件：存放InnoDB数据和索引。
• 独享表空间存储方式：每个表一个.ibd文件。
• 共享表空间存储方式：所有表共同使用一个.ibdata文件。
• 独享和共享由innodb_file_per_table字段控制，从5.6版本后，默认为1，即使用独享表的方式。

MyISAM存储引擎

• .frm文件：存放表结构的定义信息。
• MYD(MYData)文件：存放MyISAM表的数据。
• MYI(MYIndex)文件：存放MyISAM表的索引相关信息。

二、详细存储格式分析

考虑到工作中使用InnoDB存储引擎居多，后面讨论以InnoDB存储引擎为主。ibd文件又称表空间文件，从逻辑和物理上进行了层次性的划分。主要分为：

• 段（Segment）
• 区（Extent）
• 页（Page）
• 行（Row）

行（Row）

表示一条记录，是存储的最小单元。

页（Page）

考虑到每次读取一个条记录，需要进行一次IO，效率非常低，因此InnoDB是按照页为单位进行读取的。默认页的大小为16KB，为了提高读取效率，页中的行记录是连续存储，可以方便顺序IO。

区（Extent）

InnoDB存储引擎采用的是B+树来存储的。B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量随机I/O，随机 I/O 是非常慢的。因此，最好的解决办法就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

解决方案：在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

段（Segment）

段是由多个区组成的，两个区之间是逻辑上相邻的，物理上不一定相邻。段分为：

• 数据段：存放B+树叶子节点的区集合；
• 索引段：存放B+树的非叶子节点的区集合；
• 回滚段：存放的是回滚数据的区集合。后续用于MVCC版本控制。

2.1 存储格式分类

系统是不断演进的，因此MySQL的存储格式也在不断优化。主要有以下几种存储格式：

• COMPACT行格式：是其它几种格式的基础。处理行溢出时，一部分数据存储在当前页中，多余的部分存储在其它页中，然后记录其它页的内存地址
• Redundant行格式：已经不再使用。
• Dynamic行格式：目前MySQL5默认使用的方式。基本上和Compact一样，只不过在处理行溢出时，Danamic行格式，直接将数据存储在其他页面，然后指向该页面的内存地址
• Compressed行格式：采用压缩算法对页面进行压缩。

2.2 存储格式详解

下面分析的行记录存储格式，是MySQL的COMPACT格式，Dynamic和Compressed都是一样的。

2.2.1 额外信息

变长字段长度列表

变长字符有哪些：varchar,test,blob
是否必需：不一定。若表中没有变长字段,则不会有
【变长字段】占用内存大小：例如varchar(M), 在utf-8编码情况下，每个字符占用N个字节，则占用内存大小为：(M*N)个字节。具体情况要看编码格式,以及编码格式下的每个字符占用情况。
存储【变长字符长度】占用的内存大小为：

1. 若M*N <=255,则占用1字节;
2. 若M*N > 255,则需要区分字符串实际占用的大小L
   2.1 若L <= 127, 则使用1个字节来表示;
   2.2 若L > 127, 则使用2个字节来表示.
   【注意】：
3. 不会超过2个字节，因为一条记录的最多占用内存65535个字节
4. 若一行记录中，有多个变长字段，那每个变长字段的长度，按照逆序存放
5. 若没有变长字段，则不会有这个变长字段长度列表存在

NULL值列表

MySQL会将字段为null的，进行压缩处理，即若多个字段都为null，为了节省内存空间，会将每个允许存储null的列，对应一个二进制位，按照【逆序】存放。其中1表示该列的值为null，0表示不为null。例如：有三个字段可以为null，a,b,c，则存储形式为：
c b a
00000 0 0 0
因mysql要求，存储null值列表必须为整数个字节，因此，不足8的倍数的情况，前面自动补0，同时每8位表示一个字节
注意：

1. 因已经表示了是否存储为null，则在真实数据部分，若字段为null，则不会存储

记录头信息

主要用于描述记录的头信息，由固定的5个字节组成。

2.2.2 真实数据

隐藏字段

MySQL会为每条记录，添加一下隐藏列。例如:

• db row_id: 不是必须的。占用6个字节，行ID,唯一标识一条记录。详见下面的主键生成策略：
• db transction_id: 必须的。占用6个字节，事务ID
• db roll pointer: 必须的。占用7个字节，回滚指针
  【主键生成策略】：

1. 优先使用用户自定义主键
2. 若没有，则选取一个唯一非空字段作为主键
3. 若唯一非空字段也没有，则默认添加一个row_id作为主键

真实数据

就是业务方自己存储的字段值信息。