Mysql的数据存储

对于mysql而言，数据是存储在文件系统中的，不同的存储存储引擎会有不同的文件格式和组织形式

1、InnoDB数据存储

InnoDB存储格式由大到小：表空间 → 段 → 区 → 页 → 行

对于innodb而言，数据是存储在表空间（文件空间file space）内的，表空间是一个抽象的概念，他对应着硬盘上的一个或多个文件，如下图：

如何用mysql存储数据 mysql 存储数据_java

表空间存储数据的单位是【页】，我们可以这样类比，一个表空间就是个大大的本子，本子里是一页页的数据（innodb是以页为单位进行数据存储的），常用页面类型有很多，不同类型的页面可以存放【不同类型的数据】，这里不展开讲解，暂时统称为【数据页】、他的通用部分如下，每一页大概占用16k的空间：

如何用mysql存储数据 mysql 存储数据_表空间_02

  • file header：记录页面的一些通用信息，比如当前页的校验和、页号、上页号、下页号、所属表空间等。
  • file trailer：主要的工作是检验页是否完整。
  • 表空间中的每一个页，都有一个页号（File_PAGE_OFFSET），我们可以通过这个页号在表空间快速定位到指定的页面。这个页号由4个字节组成，也就是32位，所有最多能存放2的32次方页，如果按照一页16k计算，一个表空间最大支持【64TB】的数据。整体的排列中页是连续的，但是页有上下指针，不连续的页也能组成链表。

表空间的示意图如下：

如何用mysql存储数据 mysql 存储数据_mysql_03

表空间可以分为系统表空间、独立表空间等：

（1）系统表空间（The System Tablespace）

  • 系统表空间包含了很多【公共数据】，比如InnoDB的数据字典，回滚信息、系统事物信息、二次写缓冲等，老版本的mysql表中的数据也会存储在系统表空间。
  • 系统表空间是一个共享的表空间因为它是被多个表共享的。
  • 该空间的数据文件通过参数【innodb_data_file_path】控制，默认值是ibdata1:12M:autoextend（文件名为ibdata1、12MB、自动扩展）。
  • 当然系统表空间也可以通过配置，修改文件的名称和个数。文件如下图：

如何用mysql存储数据 mysql 存储数据_如何用mysql存储数据_04

相关变量的设置：

-- 如果1代表开启，0代表关闭
show variables like'innodb_file_per_table'
-- 设置对应的变量
set global innodb_file_per_table=0;
-- 查看系统表空间的配置
show variables like "innodb_data_file_path";
-- 配置文件的配置
innodb_data_file_path=data1:512M;data2:512M:autoextend

（2）独立表空间（File-Per-Table Tablespaces）

  • 独立表空间是默认开启的，在5.6.6以后，Innodb不在默认将各个表的数据存储在【系统表空间】当中，而是会为每一个表建立一个独立表空间，innodb存储引擎的独立表空间为【.ibd】文件。
  • 如果启用了【innodb_file_per_table】参数，需要注意的是每张表的表空间内存放的只是【数据】、【索引】和【插入缓冲Bitmap页】，其他数据如：回滚信息、系统事物信息、二次写缓冲（Double write buffer）等还是放在原来的系统表空间内。
  • 同时说明了一个问题：即使启用了【innodb_file_per_table】参数，系统表空间还是会不断的增加其大小的。

如何用mysql存储数据 mysql 存储数据_如何用mysql存储数据_05

（3）其他类型的表空间

除了以上两种表空间，，innodb还提供了很多其他类型的表空间，比如通用表空间，undolog表空间、临时表空间等，这里不在赘述。

**tips：**MyIsam数据存储

MyIsam没有表空间的概念，他会在目录中产生2个文件【.MYD】（数据文件）、【 .MYI】（索引文件）三个文件。

如何用mysql存储数据 mysql 存储数据_java_06

**注意：**在5.7以前【数据文件】和【表信息文件】是分开的，相互独立的。会多一个【.frm】文件，8.0之后进行了合并。

2、组织结构

  • 区（extent）：：每一个表空间保存了大量的页，为了更好的管理这些页面，Innodb提出了【区】的概念，对于16k的页，连续64个页就是一个区，大概1M的空间，每一个表空间都是由若干个连续的区组成的，每256个区被划分为一组。
  • 段（Segment）：分为索引段，数据段，回滚段等后边会将，段是为了区分不同的数据类型，相同的段存的数据类型是一致的。一个段包含256个区(256M大小)。

inndb表空间结构如下图所示：

如何用mysql存储数据 mysql 存储数据_java_07

3、Row Format(行记录格式)

  • 一个表的【行记录格式】决定表中行的【物理存储模式】，决定了【DQL】和【DML】的操作性能。越多的行被匹配进独立的磁盘页，sql的性能会更好一些，需要的缓存及io操作就越少。
  • 一条完整的信息记录分为：【记录的额外信息】和【记录的真实数据】两大部分，就如同一箱苹果里的苹果分为包装和苹果一样。
  • 我们可以通过命令SHOW TABLE STATUS LIKE 'table_name'来查看当前表使用的行格式，其中row_format就代表了当前使用的行记录结构类型。

指定行格式的语法如下：

-- 创建数据表时，显示指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 创建数据表时，修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

-- 具体如下：
CREATE TABLE `ydl_user`  (
  `user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID'，
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号'，
  ....
  PRIMARY KEY (`user_id`) USING BTREE
) ROW_FORMAT = DYNAMIC;

2.1、COMPACT行记录格式

【compact行记录】是在MySQL 5.0时被引入的，其设计目标是能高效存放数据。Compact行记录以如下方式进行存储：

如何用mysql存储数据 mysql 存储数据_mysql_08

  1. 【变长字段长度列表】：第一个部分是一个非NULL【变长字段长度列表】。这个其实很好理解，我们的数据类型除了定长的char、int还有不定长的如varchar、text等，变长列的真实长度就保存在这个部分，他是按照列的顺序【逆序放置】的。当列的长度小于255字节，如（varchar(50)），用1字节表示；若大于255个字节（varchar(600)），用2个字节表示，这其实也就说明了为什么varchar的最大长度是65536。
  2. 【NULL标志位】：第二个部分是【NULL标志位】，他指示了当前行数据中哪些为null值，用一个bitmap表示。举一个例子：假如该标志位为06(二进制：00000110)则表示第二三列（可以为空的列）的数据为NULL。需要注意的是，NULL值标志位仅仅针对可以为【NULL的字段】，如果某个字段被定义为not null，那么这个字段就不会进入NULL值标志位的BitMap中，这样可以节省很多空间，NULL值标志位也是逆序排列，占用空间按照字节数高位补零，如有九个字段可以为空（00000001 01010101）。
  3. 【记录头信息】：第三部分为记录头信息（record header），固定占用5个字节（40位），每位的含义见下表：

如何用mysql存储数据 mysql 存储数据_表空间_09

  • 【列的数据信息】：第四部分就是实际存储的每个列的数据了，需要特别注意的是，NULL不占该部分任何数据，即NULL除了占有NULL标志位，实际存储不占有任何空间。Innodb存储变长列（VARCHAR， VARBINARY， BLOB， TEXT）的前768字节，剩下的部分存储在溢出页中。固定长度列，超过768字节的视为变长列。内部存储前768字节，20字节指针存储列的溢出页的地址，所以长度为768+20字节。
  • 【事务ID列】：6个字节
  • 【回滚指针列】：7个字节
  • 【RowID列】：若InnoDB表没有定义Primary Key，每行还会增加一个【6字节的RowID列】

如何用mysql存储数据 mysql 存储数据_如何用mysql存储数据_10

例子：我们不妨举一个例子，看看硬盘的存储格式，以下表为准：

create table test (
　　t1 varchar(10)，
　　t2 varchar(10)，
　　t3 char(10)，
　　t4 varchar(10)
) engine=innodb row_format=compact;

insert into row_test values('a'，'bb'，'bb'，'ccc'); 
#改行的长度计算：
-- 【变长字段长度列表】：有3个varchar即可变长度：3列的数据分别长度为2 2 3，用3个字节表示3个varchar的长度----3
-- 【空标志位】：其中4个字段可以为空，使用一个bitmap表示即可，1个字节----1
-- 【记录头信息】：固定长度5个字节----5
-- 【列数据信息】：1+2+2+3=8字节---8
-- 【事务ID】：6个字节----6
-- 【回滚指针】：7个字节----7
-- 【RowID列】：该表没有主键，会生成一个rowid，长度位6字节
-- 【总长度】：3+1+5+8+6+7+6=36个字节长度

节选对应的【真实的表空间】中的的二进制结构表示：

03 02 01 00 00 00 10 00 
2c 00 00 00 00 2b 68 00  00 00 00 06 05 80 00 00 
00 32 01 10 61 62 62 62  62 20 20 20 20 20 20 20 
20 63 63 63 03 02 01 00  00 00 18 00 2b 00 00 00  
00 02 01 00 00 00 00 0f  62 c9 00 00 01 b2 01 10  
64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  
03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00 
00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66

第一行整理如下，需要注意，我们有三个变长列varchar：

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第一行没有NULL值
00 00 10 00 2c // 记录头信息，固定5字节长度
00 00 00 00 2b 68 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 06 05 // 事务ID，固定6个字节
80 00 00 00 32 01 10 // 回滚指针，固定7个字节
61 // 61是16进制，转行为2进制为0110 0001对应10进制为64+32+1=96对应ascii码为'a'
62 62 // 数据'bb'
62 62 20 20 20 20 20 20 20 20 // t3数据'bb'  Ox20十进制是32对应ascii码是空字符
63 63 63 // 数据'ccc'

2.2、REDUNDANT

【Redundant】是MySQL 5.0版本之前InnoDB的行记录存储方式。Redundant行记录以如下方式存储：

如何用mysql存储数据 mysql 存储数据_如何用mysql存储数据_11

从上图可以看到，Redundant行格式如下：

  1. 第一个部分保存了【字段长度偏移列表】，这个部分保存了该行数据所有列，包括隐藏列的长度偏移量。举一个例子说明一下偏移，假如第一个字段长度为x，第二个字段长度为y，那么列表中第一个字段就是x，第二个字段就是x+y。这个偏移列表是按照列的顺序【逆序排列】。
  2. 第二个部分为记录头信息【record header】，Redundant行格式固定占用6个字节（48位），每位的含义如下图：
    有几个标志位我们可以注意一下，n_fields值代表一行中列的数量，占用10位，这也很好地解释了为什么MySQL【一个行支持最多的列为1023】。

如何用mysql存储数据 mysql 存储数据_java_12

3.第三个部分就是实际存储的每个列的数据了。

null值的存储，在【字段长度列表】的每个字段长度最高位标记 1 表示这个字段为 NULL。

  • 对于一字节存储，通过【最高位标记字段】判断是否为 NULL，如果为 NULL，则最高位为1，否则为0。剩下的 7 位用来存储数据，所以最多是127。
  • 对于两字节存储，通过【最高位标记字段】判断是否为 NULL，第二位标记这条记录是否在同一页，如果在则为0，如果不在则为1，这其实就涉及到了后面要说的溢出页。剩下的 14 位表示长度，所以最多是16383。
  • 在这种类型的行格式中，无论字段是否为 NULL，或者长度是多少，char(M) 都会占用 M * 字节编码最大长度那么多字节。为 NULL 的话，填充的是 0x00，不为 NULL，长度不够的情况下，末尾补充 0x20。 对于varchar来说，NULL 还是不占用空间的。

小结：

compact格式比redundant存储空间大约减少20%。如果受限于cache命中和磁盘速度，compact格式会快一些，若受限于CPU速度，compact格式会慢一些。

2.3、DYNAMIC

InnoDB Plugin引入了两种新的文件格式（file format，可以理解为新的页格式），对于以前支持的Compact和Redundant格式将其称为Antelope文件格式，新的文件格式称为Barracuda。Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种。

新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。mysql8.0默认此格式。

如何用mysql存储数据 mysql 存储数据_表空间_13

2.4、COMPRESSED

种新的行记录格式Compressed和Dynamic两种。

新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。mysql8.0默认此格式。

[外链图片转存中…(img-yj8x7jNh-1678451700006)]

2.4、COMPRESSED

基于dynamic格式，支持表和索引数据压缩。compressed行格式采用dynamic相同的页外存储细节，同时，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能进行非常有效的存储


博客地址：https://bluebeastmight.github.io/