MySQL磁盘结构与分区概念

文件

MySQL是一个数据库管理系统，不仅存储了数据，还有其他管理系统相关的文件，比如配置参数文件，日志文件，存储内容的文件等

1 参数文件

参数文件就是配置文件，结构是参数名为键，参数值为值的键值对，当MySQL实例启动时，数据库会先去读一个配置参数文件

参数类型又分为

• 动态（dynamic）参数：可以修改，但是会分为作用范围
  • global：全局生效
  • session：当前会话生效
• 静态（static）参数：不能修改，修改时会报错

2 日志文件

日志文件大致分为四个：错误日志、二进制日志(bin log)、慢查询日志、查询日志、回滚日志、重做日志等，但是回滚和重做日志属于innodb存储引擎的日志

错误日志

• 该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。用户可以通过命令SHOW VARIABLES LIKE’log_error’来定位该文件。
• 可以看到错误文件的路径和文件名，在默认情况下错误文件的文件名为服务器的主机名。
• 当出现MySQL数据库不能正常启动时，第一个必须查找的文件应该就是错误日志文件。

二进制文件

• 记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改
  • 只要是update，或者delete，insert等类型的语句不管是否真实操作了数据都会记录
  • 还包括了执行数据库更改操作的时间等其他额外信息
• 二级制文件作用：恢复(数据的恢复需要二进制日志)、复制(主从同步)、审计(审计SQL是否有注入的攻击)
• 二进制文件默认下没有启动，启动后会带来性能的的损失，但并不大

慢查询日志

• 慢查询日志（slow log）可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。

具体操作慢查询日志以及实战可以看这篇
MySQL的索引与SQL优化

查询日志

• 记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log

回滚和重做日志(undo and redo)

• 属于InnoDB的一种日志文件

3 InnoDB的存储引擎文件

表空间文件

• InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计， 数据和索引被组织在其中
  • 表空间由一个/多个数据文件组成，这些数据文件可以是 .ibd或系统表空间文件（如 ibdata1）。
• 默认表空间就是系统表空间，其中包含了数据字典等关键信息。

重做日志文件

• 记录了对于InnoDB存储引擎的事务日志，所在主机掉电导致实例失败，InnoDB会使用重做日志恢复到掉电前，以此保证数据完整
• 每个InnoDB引擎至少会有一个重做日志组，每组至少两个重做日志文件，重做日志的写入是对日志组中的文件进行循环写入，如下

其他文件

• 套接字文件
  • 为了在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，提供的一个文件。
  • 可由参数socket控制。一般在/tmp目录下，名为mysql.sock
• 表结构定义文件
  • 因为MySQL插件式存储引擎的体系结构的关系，但不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件
  • 这个文件记录了该表的表结构定义。
  • frm还用来存放视图的定义
    • 如用户创建了一个v_a视图，那么对应地会产生一个v_a.frm文件，用来记录视图的定义
  • 该文件是文本文件，可以直接使用cat命令进行查看

磁盘结构

索引组织表

• InnoDB存储引擎中，根据主键顺序组织存放的表称为索引组织表
• InnoDB中每张表都有一个主键，不管是显示的还是隐式的
  • 如果没有显示的主键，InnoDB会主动帮我们创建
    • 非空的唯一索引的字段（Unique NOT NULL）作为主键
      • 如果一个表有多个唯一索引，选择的是创建时间最早的那个索引
      • 主键的选择根据的是定义索引的顺序，而不是建表时列的顺序
    • 若不符合上述，InnoDB会自动创建一个6字节大小的指针——_rowid
• _rowid可以用于查询单一主键的表的行id，并不能用于复合主键中，如primary key(a,b)，将a和b作为复合主键那么就不能使用_rowid

磁盘逻辑存储结构

innodb存储数据的文件为表空间文件，但是表空间并不是存储数据的最小单位，页是数据存储的最小单位，之间还有其他大小结构

• 表空间又由段（segment）、区（extent）、页（page）组成。页也称为块（block）

如下图结构所示

段

• 数据段：存储数据的段，为B+树中的叶子节点
• 索引段：存储索引的段，为B+树种的索引节点
• 回滚段：后续介绍

区

• 由页组成，任何情况下都为1MB，能存放64个连续的页。当存储压缩页时，一个区能存放的页也随之增加。
  • 页的大小可以变化，但是区终究只是1MB
• 一个段最开始不会分配区，而是使用碎片页32个，等用完后才会分配区。目的是减少磁盘开销

页

• 是InnoDB磁盘管理的最小单位，默认大小为16kb
• 常见的页类型：数据页（B-tree Node）、undo页（undo Log Page）、系统页（System Page）、事务数据页（Transaction system Page）、插入缓冲位图页（Insert Buffer Bitmap）、插入缓冲空闲列表页（Insert Buffer Free List）、未压缩的二进制大对象页（Uncompressed BLOB Page）、压缩的二进制大对象页（compressed BLOB Page）

行

• 数据按照行进行存放，最多存放16KB/2->7992行，最少存放16KB/200->80行
• 行的结构比较特殊，因为存储了具体的行记录

• 其中变长字段长度列表，主要是一个记录该行中变长字段的大概长度的列表（List或者Array），如果变长字段的长度小于255个字符则用1字节，反之用2字节表示，并且长度不能大于2字节表示范围。
• NULL标志位主要是表示当前行是否有NULL数据，则用1表示，没有长度限制，动态扩展
• 记录头信息（record header），固定占用5字节（40位）
• 最后就是存储的该行数据以及隐藏列->事务ID和回滚指针列，如果没有定义主键还会有一个rowid列

行溢出数据

• 为了保证叶子节点(页)至少有两行记录，避免成为链表结构，所以防止BLOG，TEXT，varchar过长，将前缀768比特的数据存放在数据行中，后续使用偏移值指向BLOB的页的位置

数据页结构

InnoDB页由七部分构成

• File Header（文件头）固定为38字节
• Page Header（页头）固定为56字节
• Infimun和Supremum Records
• User Records（用户记录，即行记录）
• Free Space（空闲空间）
• Page Directory（页目录）
• File Trailer（文件结尾信息）固定为8字节

File Header、Page Header、File Trailer的大小是固定的，其中的一些属性是用来标记该页的一些信息，如Checksum，数据页所在B+树索引的层数等
User Records、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的

分区

数据进行分区，一方面可以提高数据的检索速度，还可以将数据分区减轻检索压力

• 分区类型有：水平分区（不同行放在不同的区），垂直分区（不同的列放在不同的区）
• 分区并不是引擎层的功能，不同的存储引擎也可以分区，但是也有一部分引擎不能分区
• 并且MySQL的分区是局部分区索引，一个分区中既存放了数据又存放了索引
  • 全局分区索引即是，数据分区，但是索引全部放在一个对象中

range分区

Range分区是可以指定根据插入的值进行分区

• 例如根据日期分区，那么就可以让2021年的数据放到第一个分区，2022放到第二个分区，当根据时间查询时，可以快速定位到分区进行查询。

list分区

根据某一列的值进行分区

• 如一个表有年龄字段，可以让LIST分区根据年龄分区，那么年龄为1的就会被分在一个分区，其余年龄也类似，所以当进行查询时，查询年龄为1的数据，就可以直接定位到一个分区，并且将值年龄为1的值全部取出

Hash分区

• 不是使用的虚拟一致性hash，而是普通的hash算法
  • 当hash碰撞较为严重时，会进行重新散列
• 和Hash索引一样，不支持范围查询、增减分区的代价较高、可以使用用户自定义的hash算法进行hash散列

Key分区

• KEY分区和HASH分区相似，不同之处在于KEY分区使用MySQL数据库提供的函数进行分区

COLUMNS分区

• 可视为RANGE分区和LIST分区的一种进化。
• COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。
• RANGECOLUMNS分区可以对多个列的值进行分区。
• 并不支持FLOAT和DECIMAL

子分区

• MySQL允许在分区的基础上在进行分区形成子分区
• 子分区的建立需要注意以下几个问题：
  • 每个子分区的数量必须相同。
  • 要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区
  • 每个SUBPARTITION子句必须包括子分区的一个名字。
  • 子分区的名字必须是唯一的。

分区中的NULL值

• MySQL允许对NULL值进行分区，如一个字段为int类型，那么对其进行Range分区后，如果该列的值有NULL，则将这个NULL值视作无限小的值，所以放在最左边的分区
• 在LIST分区下要使用NULL值，则必须显式地指出哪个分区中放入NULL值，否则会报错
• HASH和KEY分区对于NULL的处理方式和RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0
  • 也就是说，只要是NULL，则计算出的hash值就为0