本文主要是针对《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;
扫一扫
530
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
