MySQL表

索引组织表

在InnoDB存储引擎中,表是根据主键顺序组织存放的,这种存储方式的表称为索引组织表,每张表都有一个主键,如果创建时没有显式定义主键,InnoDB存储引擎会按照如下方式进行创建主键:

  • 判断表中是否有非空的唯一索引,如果有,则该列为主键。
  • 如果不符合上列条件,InnoDB存储引擎会自动创建一个6字节大小的指针。

当表中有多个非空唯一索引,InnoDB会选择第一个定义的非空唯一索引作为主键。

InnoDB逻辑存储结构

所有的数据被逻辑存放在表空间,表空间又由段,区,页(块)组成。存储结构如图所示:
InnoDB逻辑存储结构

表空间

表空间是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。如果开启了innodb_file_per_table,每张表的数据可以单独放到一个表空间中。但是每张表的表空间存放的只是数据、索引和插入缓冲Bitmap页。其他类的数据,例如回滚信息,插入缓冲索引页,系统事务信息,二次写缓冲等还是存放在原来的共享表空间中。

表空间由各个段构成,常见的段有:数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的,因此数据即索引,索引即数据,数据段即为B+树的叶子节点,索引段即为B+树的非索引节点。在InnoDB存储引擎中,对段的管理都是由引擎自身完成,DBA不能也没有必要对其进行控制。

区是由连续页组成的空间,在任何情况下每个区的大小都为1MB,为了保证区的连续性,InnoDB一次从磁盘申请4~5个区,在默认情况下,页的大小为16KB,即一个区中共有64个连续页。

页是InnoDB磁盘管理的最小单位,默认每个页大小为16KB,可以通过参数innodb_page_size将页的大小设置为4K,8K、16K。

常见的页类型有:

  • 数据页
  • undo页
  • 系统页
  • 事务数据页
  • 插入缓冲位图页
  • 插入缓冲空闲列表页
  • 未压缩的二进制大对象页
  • 压缩的二进制大对象页

InnoDB存储引擎是面向行(row-oriented)的,每个页最多存放16K/2~200行的记录(7992行)。

InnoDB行记录格式

Compact行记录格式

该格式在MySQL5.0中引入,其设计目的是高效地存储数据。简单说,一个页存放的行数据越多,其性能就越高。它的存储方式如图所示:
Compact行记录格式

Compact行记录格式的首部是一个非NULL变长字段长度列表,并且是逆序放置的,其长度:

  • 若列的长度小于255字节,用1字节表示
  • 若大于255字节,用2字节表示

NULL标志位表示该行数据是否有NULL值,有则用1表示,占用1字节。头信息固定5字节(40位),每位的含义如下:
Compact记录头信息

最后的部分就是实际存储每个列的数据,NULL不占用任何空间,除了之前的标志位,每行数据除了用户定义的列之外,还有两个隐藏列,事务ID列(6字节)和回滚指针列(7字节)。如果没有定义主键,没行还会增加一个rowid列(6字节)。

行溢出数据

InnoDB存储引擎会将一些数据存储在数据页面之外。BLOB可以不存放在溢出页面,VARCHAR类型有可能被存放位行溢出数据。

InnoDB数据页结构

InnoDB数据页由以下七个部分构成,如图所示:
数据页结构

其中File Header、Page Header、File Trailer的大小是固定的,这些空间是用来标记该页的一些信息,如Checksum,数据页所在的B+树索引的层数。

File Header

该部分用来记录一些头信息,共由八个部分组成,占用38字节。
File Header组成部分

该部分用来记录数据页的状态信息,由14个部分组成,占用56字节,如图所示:
Page Header组成部分

Infimun和Supremum Records

Infimun用来记录是比该页中任何主键值都要小的值,Supremum Records指比任何值都大的值。这两个值在页创建时被建立。

User Record和Free Space

User Record是实际存储行记录的内容。Free Space指的是空闲空间,是一个链表数据结构,在一条记录被删除后,该空间会被加入到空闲链表中。

Page Directory

该部分存放了记录的相对位置,有时这些记录指针称为Slots或Directory Slots,一个槽可能包含多个记录。B+树索引本身并不能找到具体的一条记录,只能找到该记录所在的页,数据库把页载入内存,通过Page Directory进行二叉查找。

File Trailer

该部分是为了检测页是否已经完整地写入磁盘,File Trailer只有一个FIL_PAGE_END_LSN部分,占用8字节,前四个字节代表该页地checksum值,最后四个字节和File Header中的FIL_PAGE_LSN相同。将这两个值与File Header中的FIL_PAGE_SPACE_OR_CHKSUMFIL_PAGE_LSN的值进行比较,看是否一致,以此来保证页的完整性。

约束

约束机制用来保证数据库中数据的完整性。
完整性有以下三种形式:

  1. 实体完整性保证表中有一个主键。
  2. 域完整性保证数据每列的值满足特定的条件,域完整性通过以下几种方式来保证:
    • 选择合适的数据类型确保一个数据值满足特定条i教案
    • 外键约束
    • 编写触发器
  3. 参照完整性保证两张表之间的关系。

对于InnoDB存储引擎,提供了以下几种约束:

  • Primary Key
  • Unique Key
  • Foreign Key
  • Default
  • NOT NULL

约束和索引的区别:约束是一个逻辑上的概念,用来保证数据的完整性,而索引是一个数据结构,既有逻辑上的概念,在数据库上还代表着物理存储的方式。

分区表

分区功能并不是在存储引擎层完成的,不是只有InnoDB存储引擎支持分区,MySQL5.1添加了对分区的支持,分区的国恒就是将一个表或索引分解为多个更小的、更可管理的部分。从逻辑上讲,只有一个表或一个索引,但是物理上这个表或索引可能由数十个物理分区组成,每个分区都是独立的对象。

MySQL支持的分区类型为水平分(将同一表中不同行的记录分配到不同的物理文件中),不支持垂直分(将同一表中不同列的记录分配到不同的物理文件中)。MySQL的分区是局部分区索引,一个分区既存放了数据又存放了索引,而全局分区是指,数据存放在各个分区中,数据的索引放在一个对象中。

MySQL支持以下几种类型的分区:

  • RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区。
  • LIST分区:面向离散值的分区。
  • HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数。
  • KEY分区:根据MySQL数据库提供的Hash函数进行分区。

无论哪种分区方式,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。

子分区

子分区是在分区的基础上再进行分区,也成为复合分区,MySQL允许在RANGE和LIST分区上再进行HASH和KEY的子分区。
子分区需要注意以下几个问题:

  • 每个子分区的数量必须相同。
  • 要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区,就必须定义所有的子分区。
  • 每个SUBPARTITION子句必须包括子分区的一个名字。
  • 子分区的名字必须是唯一的。

参考

《MySQL技术内幕:InnoDB存储引擎(第二版)》

文章作者: L1nker4
文章链接: https://l1n.wang/2020/04/mysql-table/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 L1nker4's Blog