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

推荐阅读:

相关文章