跳至主要內容

MySQL 存储结构

pptg大约 5 分钟

1. MySQL数据的文件结构

MySQL的数据库文件存放在/var/lib/mysql中，每创建一个database就会在该目录中创建一个目录，并在该目录下保存对应的表结构和表数据。其中：

  • .opt：存储当前数据库的默认字符集和字符校验规则
  • .frm：存储表结构定义信息
  • .ibd：InnoDB的索引文件，存储表数据，称之为独占表空间文件

2. 表空间文件的结构

表空间由段（segment）、区（extent）、页（page）、行（row）组成，其中表空间包含多个段，段包含多个区，区包含多个页，页包含多个行

  1. 行（row）

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构

  1. 页（page）

数据以行为单位进行存储，但以页为单位进行读取，提高IO效率。每个页默认为16KB，页的种类有很多，包括数据页、undo 日志页、溢出页等。记录是存在数据页中的。

  1. 区（extent）

MySQL的数据页位于B+树的叶子节点，相邻节点之间存在双向链表。遍历时，如果节点物理位置相距过远会导致效率较低。因此，当表中数据量大的时候，为索引分配空间将以区为单位取代页为单位。每个区的默认大小为1MB，存储连续的64个页，加快了顺序IO的速度。

  1. 段（segment）

段中除了存放数据的数据段之外，还包括存储非叶子节点的索引段和进行回滚的回滚段等。

3. InnoDB行格式

行格式（row_format）一条记录的存储结构，InnoDB提供了4中行格式：

  • Redundant：5.0以前的格式，并不紧凑
  • Compact：5.1-5.7的格式，更紧凑
  • Dynamic和Compressed：基于Compact进行改进，5.7之后默认使用Dynamic

3.1 Compact行格式

一条Compact行格式的记录包括额外信息和真实数据两部分

Compact行格式
Compact行格式

3.1.1 额外信息

1. 变长字段长度列表

通过该列表，存储VARCHAR、TEXT、BLOB等变长字段，这样读取的时候才知道对应的长度

CREATE TABLE `user`(
  `id` int(11)  NOT NULL,
  `name` varchar(50),
  `account` varchar(50),
  `age` int(11),
  primary key(`id`) USING BTREE
) CHARACTER SET = ascii ROW_FORMAT = COMPACT

# id, name, account, age 
# -----------------------
# 1,  pptg, 0000001, 11  
# 2,  ptg , 0000002, NULL
# 3,  pp  , NULL   , NULL

对于上面的表和记录，ASCII编码的每个字符会占用1字节的空间，此时的变长字段长度列表如下：

变长字段长度列表
变长字段长度列表

此时该列表为了优化，将数据位数和数据放在一个CPU Cache中，是逆序记录的，也就是先记录account再记录name。对于第三条的NULL，不在该列表中记录。

注意

当行中不包括变长字段时，行格式中不会出现这个列表

2. NULL值列表

对于允许存在NULL的列，每一列按照二进制进行存放，1代表NULL，0代表不NULL，同样也是按照逆序进行记录，此外，当记录数不足8的倍数的时候，也会向上取整并在高位补0

比如上面的第个记录# 3, pp , NULL , NULL被记录为：

# 前五位是补0
# 后三位逆序记录 age = NULL、account = NULL、name != NULL
00000110

注意

当行中全是NOT NULL时，行格式中不会出现这个列表，所以最好都设置成NOT NULL

3. 记录头信息

记录头记录了其他的信息，其中比较重要的有：

  • delete_mask：标注数据是否删除，当delete数据时，并不会真正的删除记录，而是将delete_mask设置为1
  • next_record：下一个记录的位置，指向的是下一个记录头和真实数据之间的位置，这样往左读就是记录头，往右读就是数据
  • record_type：当前记录的类型，0是普通记录，1是B+树非叶子节点记录，2是最小记录，3是最大记录

3.1.2 数据

记录的真实数据中除了记录了字段，还有三个隐藏字段：

  • row_id：隐式id，当建表时没有指明id，这里会生产隐式id，非必需，占用6字节
  • trx_id：事务id，表示数据由哪个事务生成，必需，占用6字节
  • roll_pointer：记录上一个版本的指针，必需，占用7个字节

MySQL规定除了TEXT、BLOB，其他所有的数据字段+变长字段长度列表+NULL值列表加在一起不能超过65535个字节，其中varchar(n) 代表的是存储n个字符 ，具体的字节数根据编码不同也不同。因此在ASCII的情况下，varchar(n)最多能存65535 - 2 - 1个字符

4. 行溢出

MySQL中一个页的大小是16KB，即16384字节，但规定了最多能存储65535字节当出现了超出页大小的部分，会触发行溢出，将多余的数据存到溢出页中。此时真实数据会保存一部分在原页中，并用20字节存储溢出页的地址，将其余的数据存储在溢出页中

Compressed和Dynamic对于行溢出的处理有所不同，它们只在原页中存储指针，将全部的数据都存储在溢出页中