4.1 InnoDB存储引擎表类型

像Oracle 中的索引组织表（index organized table）。每张表都会有主键，如没有显式主键，则
（1）、表中的非空唯一索引作为主键
（2）、自动创建一个6字节大小的指针

4.2 InnoDB逻辑存储结构

所有数据都被逻辑的存放在一个空间中，我们称之为表空间（tablespace）。表空间由段（segment）、区（extent）、页（page）组成，页在一些文档中有时也成为块（block）

InnoDB逻辑存储结构

4.2.1 表空间

第三章的Innodb_file_per_table参数想必还记得吧？设置为on后每张表将会独立存储，而不是统一记录在共享表空间ibdata1中。每张表的表空间中存放的是数据、索引和插入缓存，其他的如撤销信息、系统事务信息、二次写缓冲还是存放在共享表空间，所以即使启用了innodb_file_per_table，共享表空间依然会不断增大。

4.2.2 段

常见段有数据段、索引段、回滚段。
InnoDB表是索引组织的，数据段即为B+树的叶节点，索引段即为非叶节点。
InnoDB对于段空间管理是自动的。

4.2.3 区

区是由64个连续的页组成的，每个页大小为16KB，即每个区大小为1MB。
但是当我们启用innodb_file_per_table后，创建的表默认大小是96KB，这是因为每个段开始有32个页的碎片页（fragment page），用来存放数据，当这些页用完后才是64个页的连续页申请。

4.2.4 页

页是InnoDB磁盘管理的最小单位。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）

4.2.5 行

InnoDB是面向行的（row-oriented，相对比是面向列的数据库），也就是说数据的存放按行进行存放。每个页最多允许存放16KB / 2 ~ 200行的记录。

4.3 InnoDB物理存储结构

InnoDB表物理构成

• 共享表空间（或者独立的表空间文件）
• 日志文件组（确切说，redo文件组）
• 表结构定义文件（.frm）

4.4 InnoDB行记录格式

5.1后有Compact（默认）和Redundant两种格式

4.4.1Compact行记录格式

设计目标为高效存放数据，行数据越多，性能越高。

Compact行记录格式

• 变长字段长度列表，按照列的顺序逆序放置。列长度小于255字节，用1字节表示，大于255字节，则用2个字节表示
• NULL标志位，一个字节，表示对应列为NULL
• 记录头信息，5个字节，含义见下表
• 下面即为数据列，NULL不占用存储空间
• 每行除了用户定义的列，还有两个隐藏列，事务ID列（6字节）和回滚指针列（7字节），若没有定义主键，每行还会有一个6字节的RowID列

记录头信息1

记录头信息2

4.4.2 Redundant行记录格式

Redundant行记录格式

• 字段长度偏移列表，按照列的顺序逆序放置。列长度小于255字节，用1字节表示，大于255字节，则用2个字节表示
• 记录头信息，固定占用6个字节，含义见下表。n_fields、1byte_offs_flag两个值值得注意

• 数据列，varchar的NULL值不占用存储空间，但是char值需要占用空间

  
  
  记录头信息

4.4.3 行溢出数据

将一条记录中的某些数据存储在真正的数据页面之外，即为溢出数据。
不单单BLOB、LOB这类大对象列类型，varchar也可以存放为行溢出数据。varchar理论可以放65535个字节，减去别的开销，实际为65532个字节。

行溢出

4.4.4 Compressed 与 Dynamic行记录格式

InnoDB Plugin 引入了新的文件格式，之前的Compact和Redundant称为Antelope，新的称为Barracuda。
新文件格式下分为两种新的行记录格式，Compressed 和 Dynamic两种，采用完全的行溢出方式。

Barracuda行溢出

Compressed 行记录格式的另一个功能就是存储在其中的行数据会以zlib的算法进行压缩。

4.4.5 char的行结构存储

通常理解，varchar存储变长长度的字符类型，char存储定长长度的字符类型。然而在多字节字符集的情况下，char和varchar的行存储基本是没有区别的。

4.5 InnoDB数据页结构

页是InnoDB管理数据库的最小磁盘单位，类型为B-tree node的页，存放的就是表中行的实际数据了。
InnoDB页分为以下七个部分：

• File Header
• Page Header
• Infimum + Supremum Records
• User Records
• Free Space
• Page Directory

• File Trailer

  
  
  InnoDB数据页结构

4.5.1 File Header

File Header

Page类型1

Page类型2

4.5.2 Page Header

Page Header

4.5.3 Infimum 和 Supremum 记录

infimum指比任何主键值都要小的值
Supremum指比任何可能大的值都要大的值，这两个值在页创建时建立，并且任何情况下不会删除。

Infimum 和 Supremum

4.5.4 User Records 与 Free Space

User Records 即实际存储行记录的内容。再次强调，InnoDB存储引擎表总是B+树索引组织的。
Free Space值得就是空闲空间，是链表数据结构，当记录被删除时，该空间就会被加入空闲链表中。

4.5.5 Page Directory

Page Directory 中存放了记录的相对位置，有些时候这些记录指针被称为槽slots，或者目录槽。InnoDB并不是每条记录都有一个槽，一个槽可能指向4-8个记录，记录都是按照键顺序存放的。所以slots是稀疏目录，二叉查找到的，只是个粗略的结果，还需要recorder header中的next_record来继续查找相关记录。
需要牢记，B+树索引本身并不能找到具体的一条记录，B+树索引找到的只是该记录所在的页。

4.5.6 File Trailer

File Trailer 是为了保证页完整的写入磁盘。
只有一个FIL_PAGE_END_LSN部分，8个字节，前4代表该页的checksum值，后4与File Header中的FIL_PAGE_LSN相同，通过比较是否一致保证页的完整性

4.6 Named File Formats

InnoDB通过Named File Formats机制来解决不同版本下页结构兼容性问题

InnoDB 文件格式

参数Innodb_file_format_check 用来检测当前InnoDB存储引擎文件格式的支持度，该值默认为ON

4.7 约束

4.7.1 数据完整性

关系型数据库系统和文件系统的不同点是，关系型数据库本身能够保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。几乎所有的关系型数据库都提供了约束（constraint）机制，约束提供了一条强大而简易的途径来保证数据库中的数据完整性，数据完整性有三种形式：

• 实体完整性，保证表中有一个主键
• 域完整性，保证数据的值满足特定的条件
• 参照完整性，保证两张表之间的关系

对于InnoDB而言，提供了4种约束：

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

4.7.2 约束的创建和查找

可以创建表时候直接写出来，也可以表建立后用sql语句创建

4.7.3 约束和索引的区别

• 约束更是一个逻辑的概念，用来保证数据完整性。
• 索引是一个数据结构，有逻辑上的概念，在数据库中更是一个物理存储的方式。

4.7.4 对于错误数据的约束

mysql允许非法或错误数据的插入，会默认将其转化为一个合法的值，然后报出warning，如需要让其插入失败，报error，则需要设定sql_mode，用来严格审核输入的参数。

4.7.5 ENUM和SET约束

set sql_mode = 'STRICT_TRANS_TABLES';

再配合枚举或集合，可以实现check约束，但也只限于这种对于离散数据的约束，对连续值的范围约束和更复杂的约束还是无能为力。

触发器与约束

• 触发器的作用是在INSERT、DELETE、UPDATE前后自动调用SQL命令或者存储过程

• 触发器创建命令是CREATE TRIGGER，只有Super权限的MySQL用户才可以执行

  
  
  创建触发器
  
  

  *最多可以为一个表建立6个触发器，insert、update、delete前后各一个。

• 当前只支持FOR EACH ROW 的触发方式

应用：
可以在修改值之前判断是否符合业务逻辑，如取款业务取负数款项等。

4.7.7 外键

外键创建实例

外键删除时的子表操作

• oracle中可以延迟检查外键约束，mysql都是即时检查
• oracle中不要忘了给外键加索引，防止死锁；InnoDB会自动加索引

• 对于数据导入，可能因为外键而花费很多时间，可以这样避免：

  
  
  避免因外键而浪费检查时间

4.8 视图

视图（View）是一个命名的虚表，它有一个查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有物理表现形式。

4.8.1 视图的作用

主要用途之一是被用作一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来获取或者更新数据，因此，视图同时在一定程度上起到一个安全层的作用。

alter view v_t as select * from t where id < 10 with check option;

with check option 表示更新视图时对插入数据进行检查

4.8.2 物化视图

用于预先计算并保存表连接或聚集等耗时较多的操作结果，这样，在执行复杂查询时，就可以避免进行这些耗时的操作，从而快速得到结果。
MySQL本身不支持物化视图，但是可以通过触发器插入一个新的表实现。

4.9 分区表

4.9.1 分区概述

MySQL支持水平分区，不支持垂直分区。此外，MySQL的分区是局部分区索引，一个分区中既有数据也有索引。
通过下面命令来查看是否启用了分区

show variables like '%partition%';

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

• RANGE分区：行数据基于属于一个给定连续区间的列值放入分区。
• LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。
• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
• KEY分区：根据MySQL数据库提供的哈希函数来进行分区。
  不论哪种类型的分区，当表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

4.9.2 RANGE分区

RANGE分区示例1

如果此时插入30

RANGE分区示例2

RANGE分区主要用于日期列的分区，比如可以方便按年份，日期查看数据

下面是两则使用注意：

注意事项1

注意事项2 - 1

注意事项2 - 2

4.9.3 LIST分区

LIST分区示例

当然，如果插入的数据不在定义中时，一样会报错。
但是，在插入多组数据，其中有错误数据时，MyISAM引擎会将出错数据之前的数据插入；
InnoDB引擎则会将整句插入语句无效，当做事务来处理。

4.9.4 HASH分区

hash分区的目的是将数据均匀分布到各个分区。

HASH分区示例

数据库还支持一种称为LINEAR HASH的分区

• 优点，增、删、合并、拆分将变得更加快捷，有利于处理含有更大量数据的表
• 缺点，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡

4.9.5 KEY分区

与HASH分区类似，只是这次不适用用户提供的函数，而是用数据库内部的哈希函数。

KEY分区示例

4.9.6 COLUMNS分区

相当于RANGE分区和LIST分区的进化版本，直接使用某一列分区，而不需要将此列转换为整形，如用YEAR()函数处理日期列。

COLUMNS分区

4.9.7 子分区

在分区基础上再分区，也成为复合分区。
MySQL支持在RANGE或LIST分区上再进行HASH或KEY分区

子分区示例

4.9.8 分区中的NULL值

• RANGE中使用将会将NULL放入最左边分区
• LIST分区，必须显示指出放入哪个分区
• HASH和KEY分区将会NULL返回0

4.9.9 分区和性能

分区并不适用OLTP应用
请对本节进行细致阅读

欢迎大家关注我的公众号

半亩房顶