InnoDB 存储引擎之表存储

本文主要是针对《Mysql技术内幕：InnoDB 存储引擎》一书中第四章关于表相关概念的概括和总结，主要包括组织索引表，InnoDB 逻辑存储结构，InnoDB 行记录格式，InnoDB 数据页结构，表相关的约束问题，视图，表分区。

一、索引组织表

索引组织表是指按照主键的顺序组织存放数据的表，InnoDB 存储引擎创建的表都是索引组织表。InnoDB 主键定义规则如下：

• 如果定义表时，显式指定了 Primary Key，Primary Key 便是主键。
• 如果没有使用 Primary Key 指定主键，判断表中是否有非空的唯一索引，如果有则该列即为主键，如果表中有多个非空的唯一索引，会选择第一个非空唯一索引为主键，这里是索引的定义顺序，并非列的定义顺序。
• 如果不存在非空的唯一索引，InnoDB 会主动创建一个 6 个字节大小的指针为主键。
• _rowid 在单个列为主键的情况下可以查看主键对应的值，多个列则无能为力。

二、InnoDB 逻辑存储结构

表空间

InnoDB 所有数据都存放在一个空间中，称之为表空间，表空间由段，区，页组成。

innodb_file_per_table 参数：

• InnoDB 默认情况下所有表的数据都存放在共享表空间 ibdata1 中，该参数决定了是否每为张表内的数据单独设置一个表空间。
• 如果开启该功能，单独表空间中只存放数据、索引、插入缓存 Bitmap 页，其它数据包括回滚信息，插入缓存索引页，系统事务信息等还是存放在原来的共享表空间中。

段

表空间由各个段组成，主要分为索引段，数据段以及回滚段。其中数据段存放在 B+ 树的叶子节点，索引段存放在 B+ 树的非叶子节点。

区

区由连续的页组成：

• 每个区的大小是固定的：1MB
• 默认页的大小是 16KB，所以一个区中有 64 个连续页。
• innodb_page_size 参数，在 1.2.x 版本以后可以设置对页的大小进行设置。
• 当创建一个表时，并不是直接使用连续的 64 个页存放数据，而是先用每个段开始时的 32 个碎片页来存放数据，等使用完这些碎片页才开始申请 64 个连续页，这样做的主要目的对于一些小表，刚开始可以申请较少的空间，节省磁盘容量的开销。

页

InnoDB 存储引擎中，常见的页的类型有：

• 数据和索引页
• undo 页
• 系统页
• 事务数据页
• 插入缓存 Bitmap 页
• 插入缓存空闲列表页
• 未压缩的二进制大对象页
• 压缩的二进制大对象页

行

InnoDB 存储引擎中，数据是按照行进行存放的，最多可以存放 7992 条行记录

三、InnoDB 行记录格式

InnoDB 中行记录格式通过在建表语句中关键字 ROW_FORMAT=xxx 来指定。

create Table mytest (
    t1 varchar(10),
    t2 varchar(10)
) engine=INNODB ROW_FORMAT=COMPACT

在 InnoDB 1.0.x 版本之前，提供了 Compact 和 Redundant 两种格式来存放行记录数据。

在 InnoDB 1.0.x 版本之后，引入了新的两种行记录格式：Compressed 和 Dynamic。

Compact

存放顺序：

• 变长字段列表：按照列的顺序逆序存放每个非 NULL 变长字段所占的长度
• NULL 标志位：存放哪些字段是NULL值
• 记录头信息：固定5个字节，记录一些关于行是否被删除，下一行记录的相对位置等信息
• 列1数据，列2数据…：存放每一列的数据

不管是 CHAR 类型还是 VARCHAR 类型，NULL 在 Compact 存储格式下都不占用存储空间。

Redundant

存放顺序：

• 字段长度偏移列表：逆序存放每个字段的偏移量
• 记录头信息：固定6个字节，记录一些关于行是否被删除，下一行记录的相对位置等信息
• 列1数据，列2数据…：存放每一列的数据

对于 VARCHAR 类型的 NULL 值同样不占用存储空间，但是 CHAR 类型的 NULL 值会占用存储空间。

Compressed 和 Dynamic

• 对于存放在 BLOB 中的数据，完全采用行溢出数据存放
• Compressed 存放的行数据会采用 zlib 算法进行压缩

行溢出数据

• InnoDB 会将一些大对象数据存放在数据页之外的 BLOB 页中
• 要不要将数据放在 BLOB 页中，取决于当前页中是否可以存放下至少两行数据
• VARCHAR 类型的数据也有可能被存放在 BLOB 页中，而 BLOB 类型和 TEXT 类型的数据也有可能不被存放在 BLOB 页中
• 对于 Compact 和 Redundant 格式存放的数据，采用的是部分行溢出存储，前 768 字节还是会存放在数据页中的
• 对于 Compressed 和 Dynamic 格式存放的数据，采用的完全行溢出存储，只用 20 个字节存放指针，其余所有数据都放在行溢出数据中

CHAR 类型的存储

• 对于单字节的字符编码，CHAR 类型是固定长度的字符串
• 对于多字节的字符编码，CHAR 类型不再代表固定长度的字符串了，innoDB 将其看作变长字符存放，例如对于 UTF-8 下的 CHAR(10) 类型，最小可以存放10字节的字符，最大可以存放30字节的字符

四、InnoDB 数据页结构

InnoDB 数据页由以下七个部分组成：

• File Header 文件头
• Page Header 页头
• Infimun 和 Supremum Records
• User Records 行记录
• Free Space 空闲空间
• Page Directory 页目录
• File Trailer 文件结尾信息

File Header

File Header 用来记录页的一些头信息，共占用 38 个字节，主要存放关于当前页的一些信息，包括下面一些信息：

• 当前页在表空间的偏移量
• 上一页和下一页的位置，方便查找下一页和上一页数据
• 页的类型，包括索引页，数据页，系统页，插入缓存位图等
• …

Page Header

Page Header 用来记录数据页的状态信息，主要包括下面一些信息：

• 指向可重用空间的首指针
• 已删除的记录数
• 页中记录的数量
• 当前页在索引树中的位置
• 索引ID，表示当前页属于那些索引
• B+ 树所在的段信息，只有 Root 节点才会定义
• …

Infimun 和 Supremum Records

Infimun 和 Supremum Records 是两个虚拟的行记录，用来存放记录的边界， Infimun 记录比所有主键值都要小的记录，Supremum Records 记录比所有主键值都要大的值，这两个值在页创建时被创建，任何情况下都不会删除。

User Records 和 Free Space

User Records 存放行记录数据，Free Space 指的是空闲空间，当一条记录被删除时就加入到空闲链表结构里

Page Directory

Page Directory 存放了记录的相对位置，称为槽，并不是每行记录一个槽，是一个稀疏目录，一个槽里面可能有多个记录，按照主键值顺序索引存放。

Page Directory 主要用于在页中根据索引查询某条记录时，通过二分查找法查到一个粗略的位置，最后在通过行记录中的 next_record 来继续查找相关记录。

所以整个数据查询的逻辑如下：

- 通过 B+ 树找到该记录所在的页，数据库把该页载入内存
- 然后再根据  Page Directory 的二分查找法找到一个粗略的记录位置
- 最后再根据行记录中的 next_record 来继续查找相关记录

File Trailer

File Trailer 为了检查页是否被完整的写入磁盘或者是否完整从磁盘读取，可以通过以下两个参数对其进行控制：

• innodb_checksums: 是否开启页面完整性检查
• innodb_checksum_algorithm：设置页面完整性检查算法，默认是 crc32

五、约束

数据的完整性包括下面三个部分：

• 实体完整性：主键约束，Primary Key 和 Unique Key
• 域完整性：保证每列的值满足一些条件，通过默认值，NOT NULL 等来约束
• 参照完整性：保证两张表之间的关系，通过外键和触发器来约束

触发器

• 触发器用于在执行 INSERT，DELETE，UPDATE 之前或者之后自动调用的 sql 语句或者存储过程
• 创建触发器命令是 CREATE TRIGGER，只有具备 Super 权限的 MYSQL 数据库用户才能执行该命令
• 一个表最多创建 6 个触发器，分别是 before insert/before delete/before update/after insert/after delete/after update
• 触发器可以用来实现对数据完整性约束做检查，物化视图，高级复制，审计等功能

外键约束

• InnoDB 在创建外键时，自动会给该列加上索引
• 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以
• 外键通过 FOREIGN KEY … REFERENCES 来定义：

CREATE TABLE `dage` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1；

CREATE TABLE `xiaodi` (
  `id` int(11) NOT NULL auto_increment,
  `dage_id` int(11) default NULL,
  `name` varchar(32) default '',
  PRIMARY KEY  (`id`),
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1；

• 定义外键时可以指定 ON DELETE 和 ON UPDATE，表示对外键进行 DELETE 和 UPDATE时，子表的行为：

- SET NULL：表示外键发生变化时，子表相应字段被设置为 NULL
- NO ACTION：表示外键发生变化时，抛出错误，不允许该类操作
- SET DEFAULT：表示外键发生变化时，设置成相应的默认值
- RESTRICT：表示父表发生变化时，抛出错误，不允许该类操作，和 NO ACTION 类似

• 创建外键约束时，每次插入和更新会花费大量时间去检查相关依赖，对于性能有一定的消耗，通过 set foreign_key_checks = 0 或者 = 1 来关闭或者开启外键约束检查

对错误数据的约束

默认情况下，如果向 mysql 中插入不符合约束规范的数据，mysql 会给予警告提示，并且将不符合规范的数据自动转换为符合符合规范的数据，并不会报错。

可以通过参数 sql_mode = “STRICT_TRANS_TABLES” 强制对输入值进行合法性检查，如果不满足条件则报错。

六、视图

• 视图没有实际的物理存储
• 视图可以被更新数据，本质上是对基表进行更新的
• 视图的优点是简化查询，数据安全（只查询部分数据），数据独立
• 创建视图时如果添加 WITH CHECK OPTION 选项，则会对插入视图的数据作检查，不满足视图定义的数据会报错
• SHOW TABLES 命令查看到所有基表及视图，如果只想看到视图或者基表，可以查看 information_schema 下的 Table 和 Views 表查看
• mysql 不支持物化视图，不过可以通过触发器的方式来模拟物化视图，每次更新时，把表中的数据清空，重新导入一遍数据
• 创建视图通过 CREATE VIEW AS … 语句实现：

create view v_match
    as 
    select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
    from 
    PLAYERS a,MATCHES b,TEAMS c
    where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;

七、分区表

区分特性：

• 分区功能并不是在存储引擎层完成的，但并不是所有存储引擎都支持分区功能。
• MYSQL 数据库不支持垂直分区，支持水平分区，也就是按照行进行分区。
• MYSQL 数据库的分区是局部分区索引，即一个分区中既存放了数据又存放了索引。
• have_partion 参数用来设置数据库是否开启分区功能
• 不管创建何种分区，如果表中存在唯一主键或者索引时，分区列必须是唯一索引的一个组成部分
• 通过 explain partition 可以查看当前 SQL 的分区命中情况
• 可以通过查看 information_schema 下的 PARTITIONS 表来查看每个分区的具体信息
• 插入值应该严格遵守分区的定义，如果插入的值不在分区定义范围内，MYSQL 数据库会抛出一个异常
• RANGE，LIST，HASH，KEY 分区的结果必须是整型，如果不是整型，需要根据函数计算转换为整型

分区优点：

• 和单个磁盘或文件系统分区相比，可以存储更多的数据。
• where 子句中包含分区条件时，可以只扫描必要的分区，提高查询效率。
• 涉及聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需汇总得到结果。
• 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

分区分类：

RANGE 分区

• 行数据基于属于一个给定的连续区间的列值被放入分区
• 对于某些具有连续意义的列进行分区可能会提高查询效率，一般用于日期列的分区
• 如果是根据日期函数对某列的计算结果进行分区，分区优化器只能对 YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP() 这类函数进行查询优化
• 特殊值 MAXVALUE 表示正无穷值

create table t(
id int
) ENGINE=INNODB
PARTITION BY RANGE (id)(
    PARTITION p0 values less then (10),
    PARTITION p1 values less then (20),
    PARTITION p1 values less then MAXVALUE  - MAXVALUE 表示正无穷
)

LIST 分区

• 和 RANGE 分区类似， LIST 分区是面向离散的值，Range 分区用 values less then 定义分区，而 List 分区用 values in 定义分区
• 在用 INSERT 插入多行数据的过程中遇到分区未定义的情况，MyISAM 引擎会将之前的数据都插入，之后的数据不会插入，而 InnoDB 引擎会将其视为一个事务，不插入任何数据

create table t(
id int
) ENGINE=INNODB
PARTITION BY RANGE (id)(
    PARTITION p0 values in  (10, 20),
    PARTITION p1 values in (30, 40, 50)
)

HASH 分区

• 根据用户自定义的表达式的返回值进行分区，返回值是正整数
• 通过 PARTITION BY HASH(expr) 来指定分区，expr 必须返回一个整数的表达式
• 通过 PARTITIONS 字段指定分区的个数，系统会根据 HASH(expr) 表达式返回的结果使用取余方法将数据拆分为多个分区

create table t(
id int,
b DATETIME
) ENGINE=INNODB
PARTITION BY HASH (YEAR(b))
PARTITIONS 4;    - 表示设置4个分区

KEY 分区

• 和 HASH 分区类似，只是根据 mysql 存储引擎自己提供的哈希函数来进行分区

create table t(
id int,
b DATETIME
) ENGINE=INNODB
PARTITION BY KEY (b)
PARTITIONS 4;    - 表示设置4个分区

COLUMNS 分区

• 和其它四种分区相比，COLUMNS 分区可以直接使用非整数类型的数据进行分区，根据数据类型直接比较，不需要转换为整型
• COLUMNS 分区分为 RANGE COLUMNS 和 LIST COLUMNS 两种
• 对于 RANGE COLUMNS 可以使用多个列进行分区

create table rcx (
    a int,
    b int,
    c char(3),
    d int
) engine=innoDB
partition by range columns(a,d,c)(
    partition p0 values less than (5, 10, 'ggg'),
    partition p1 values less than (10, 20, 'mmm'),
    partition p3 values less than (maxvalue, maxvalue, maxvalue),
)

子分区：

• 在已经分区的基础上再进行分区，也称为复合分区
• 可以在 LIST 和 RANGE 的基础上再进行 HASH 和 KEY 分区
• 每个子分区的数量必须相同，子分区的名字必须唯一
• 要在任何一个分区表上明确定义子分区，就必须在所有分区上都明确定义子分区
• 创建子分区的两种方式：

--  不定义每个子分区
CREATE TABLE tb_sub (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

-- 定义每个子分区
CREATE TABLE tb_sub_ev (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

分区中 NULL 值处理：

• mysql 不禁止在分区键值上使用 NULL
• range 分区对于 NULL 值的处理是将 NULL 值视为小于任何一个非 NULL 值
• 对于 list 分区如果插入 NULL 值会报错
• hash 和 key 分区对于 NULL 值的处理将含有 NULL 值的记录都返回为 0

分区与性能：

并不是所有启动了分区的表，数据库查询就会变快。一般对于 OLTP(在线事务处理) 的应用不建议使用分区，建议对于 OLAP(在线分析处理)使用分区。一般 OLTP 表数据量不是特别大，而且大部分查询会根据索引查询，采用分区反而会增加 IO 的读写次数使性能下降。

表和分区交换数据：

通过 ALTER TABLE … EXCHANGE PARTITION 语法可以将某个分区的数据和另外一个非分区表中的数据进行交换，必须满足以下条件：

• 表结构必须相同
• 非分区表中的数据必须在交换分区定义内
• 被交换的表中不能含有外键或者其他对该表的外键引用
• 使用该语句时不会触发交换表和被交换表上的触发器
• AUTO_INCREMENT 列会自动被重置
• 用户需要有 DROP 的权限

alter table e exchange partition p0 with table e2;