本文主要是针对《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;

推荐阅读：

相关文章