InnoDB存储引擎 (第4章　表)

表是关于特定实体的数据集合。

4.1　索引组织表

在 InnoDB 中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。在 InnoDB 中，每张表都有一个主键，如果在创建表时没有显式地定义主键，则 InnoDB 会按如下方式选择或创建主键：

首先判断表中是否有非空的唯一索引（Unique NOT NULL），有则列为主键；
如不符合上述条件，InnoDB 会自动创建一个6字节大小的指针。

当表中有多个非空唯一索引时，InnoDB 会选择创建表时第一个定义的非空唯一索引为主键。主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

查询表的主键值:

select _rowid from t_msg ;

4.2 　InnoDB逻辑存储结构

所有数据都被逻辑地存放在一个空间中，我们称之为表空间。表空间又由段(segment)、区(extent)、页(page)组成，页有时也称为块。

表->段->区->页->行 ;

4.2.1　表空间

表空间可看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下InnoDB有一个共享表空间ibdata1，即所有数据都放在这个表空间内，如果启用参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。
在启用参数后，需要注意的是：每张表的表空间内存放的只是数据、索引和插入缓冲，其他类的数据，如撤销信息、系统事务信息、二次写缓冲等还是放在原来的共享表空间内。

这也说明了另外一个问题,即使启用了参数,原来的共享表空间还是会不断增大;

4.2.2　段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。前面介绍过InnoDB存储引擎表是由索引组织的，因此数据即索引，索引即数据。那么数据段即为B+树的页节点（图4-1中的leaf node segment），索引段即为B+树的非索引节点（none-leaf node segment）。
注意并不是每个对象都有段。因此更准确的说，表空间是由分散的页和段组成。

4.2.3　区

区是由64个连续的页组成的，每个页大小为16KB，即每个区大小为1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。

参数可以设置页的大小:

show variables like '%innodb_page_size%' ;

无论页大小怎么变化,区总是为1MB;

碎片页(fragment page),在每个段开始时，先有32个页大小的碎片页来存放数据，当这些页使用完之后才是64个连续页的申请。开始时申请较少的空间,节省磁盘开销;

4.2.4　页

页是InnoDB磁盘管理的最小单位。
常见页类型有：

　　　　数据页 (b-tree node)、

　　　　Undo页 (undo log page)、

　　　　系统页(System Page)、

　　　　事务数据页(Transaction system page)、

　　　　插入缓冲位图页(insert buffer bitmap)、

　　　　插入缓冲空闲列表页(insert buffer free list)、

　　　　未压缩的二进制大对象页(Uncompressed BLOB Page)、

　　　　压缩的二进制大对象页(copressed BLOB Page)

4.2.5　行

InnoDB存储引擎是面向行的，也就是说数据的存放按行进行存放。每个页最多存放7992行记录。

16 KB / 2 - 200 = 7992 行。

4.3　 InnoDB行记录格式

show table status like '%demo%' ;

可以查看行格式: row_format

4.3.1　Compact行记录格式

（MySQL5.0开始被引入的）设计目标是能高效存放数据。简单来说，如果一个页中存放的行数据越多，其性能就越高。

首部是一个非null变长字段长度列表,并且是按照列的顺序逆序放置, 长度为:

若列的长度<255字节,用1字节表示

若>255个字节,用2字节表示;

变长字段的长度最大不可以超过2字节,因为varchar类型的最大长度限制为65535,

第二部分是null标志位 占用一个字节,该位指示了该行数据中是否有NULL值,有用1表示,

record header: 固定占用5个字节(40位),每个位含义如下:

最后部分 是实际存储每个列的数据;

注意: NULL不该占该部分任何空间,即NULL除了占有NULL标志位,实际存储不占有任何空间;

另外: 每行数据还有两个隐藏列,事务ID列和回滚指针列,分别占6和7个字节;

若Innodb表没有定义主键,每行还会增加一个6安子杰的rowid列 ;

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

4.3.2　Redundant行记录格式

在 redundant 中，VARCHAR 类型的NULL值不占空间，但是 CHAR 类型 NULL 值要占空间。

首部是一个字段长度偏移列表,同样是按照列的顺序逆序放置;

若列的长度<255字节,用1字节表示

若>255个字节,用2字节表示;

第二部分记录头信息(record header) ,不同于Compact行记录格式, Redundant的占6个字节(48位),每个位的含义4-2图示;

n_fields值代表一行中列的数量占用10位,(mysql中一行列数量最多1023);

另一个注意的是1byte_offs_flags,该值定义了偏移列表占用1字节还是2字节;

最后部分 就是实际存储的每个列数据 ;

4.3.3　行溢出数据

实测，InnoDB 存储引擎能存放 VARCHAR 类型的最大长度为65532字节，而非65535. 这是因为还有别的开销;

一般情况下，InnoDB 存储引擎的数据是存放在页类型为 B-tree node 中。但是当发生行溢出时，数据存放在页类型为 UNcompress BLOB 页中。

此外需要注意的是mysql官方手册定义的65535长度是指所有varchar列长度总和,如果列的长度总和超过这个长度,依然无法创建表;

4.3.4　Compressed和Dynamic行记录格式

InnoDB 1.0.x 版本引入了新的文件格式（新的页格式）。以前的 Compact 和 Redundant 格式称为 Antelope 文件格式，新的文件格式称为 Barracuda 文件格式。包括，Compressed 和 Dynamic 两种行记录格式。

新的两种记录格式对于存放在bolb中的数据采用了完全的行溢出的方式,如图: 在数据页中值存放20个字节的指针,实际数据都是放在off page中,而之前的compact和redundant两种格式只会存放768个前缀字节;

Compressed ，存储在其中的行数据以 zlib 算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 等大长度类型的数据能够进行非常有效的存储。

4.3.5　CHAR的行结构存储

可以认为在多字节字符集的情况下， CHAR 和 VARCHAR 的实际行存储基本是没有区别的。

4.4　 InnoDB数据页结构

页是 InnoDB 存储引擎管理数据库的最小单位。页类型为 B-tree node 的页存放的即是表中行的实际数据。

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

4.4.1　File Header

记录页的一些头信息，共占用38字节;

4.4.2　Page Header

记录数据页的状态信息，由14个部分组成，占用56字节。

4.4.3　Infimum和Supremum Records

InnoDB的每个数据页中有两个虚拟的行记录，用于限定记录的边界。这两个值在页创建时被建立，在任何情况下都不会被删除。

Infimum是该页中比任何主键都要小的值,Supremum 指的是比可能大的值还要大的值;

4.4.4　User Records和Free Space

User Records实际存储行记录的内容,innodb存储引擎表总是B+树索引组织的;

Free Space 指的是空闲空间,同样也是个链表数据结构,在一条数据被删除后,该空间会被加入到空间链表中;

4.4.5　Page Directory

页目录中存放了记录的相对位置,(不是偏移量),有时候这些记录指针称为Slots(槽)或目录槽;

B+树索引本身并不能找到具体的一条记录,能找到只是该记录所在的页,数据库把页载入内存,然后通过页目录再进行二叉查找, 只不过二叉查找的时间复杂度很低,同时内存中查找很快,所以通常忽略这部分查找的时间;

4.4.6　File Trailer

目的:检测页是否已经完整写入磁盘;

占用8字节。

只有一个FIL_PAGE_END_LSN部分,占用8字节,

前4字节代表该页的checksum值,

最后4个字节和File header的FIL_PAGE_LSN相同,

将这两个值与File Header 的FIL_PAGE_SPACE_OR_CHEKSUM和FIL_PAGE_LSN值进行比较一致; 以保证页的完整性;

默认情况.Innodb每次从磁盘读取一个页就会检测该页的完整性,即页是否发生corrupt;

该检测会有一定的开销,

4.4.7　InnoDB数据页结构示例分析

略...

4.5　 Named File Formats机制

新的格式会包含旧的格式;

innodb_file_format 参数在mysql8中移除了;

4.6　约束

4.6.1　数据完整性

几乎所有的关系型数据库都提供了约束（constraint）机制，该机制确保了数据库中数据的完整性。

数据完整性有以下三种形式：

实体完整性保证表中有一个主键。可通过 Primary Key 和 Unique Key 约束来保证，也可编写一个触发器来保证数据完整性。
域完整性保证数据每列的值满足特定的条件。可通过以下几种途径来保证：
1. 选择合适的数据类型确保一个数据值满足特定条件；
2. 外键约束；
3. 编写触发器；
4. 考虑用 DEFAULT 约束作为强制域完整性的一个方面。
参照完整性保证两张表之间的关系。定义外键以强制参照完整性。

对 InnoDB 本身而言，提供一下几种约束：

primary key
unique key
foreign key
not null
default

4.6.2　约束的创建和查找

创建的两种方式：

表建立时就进行约束定义
利用 ALTER TABLE 命令来进行创建约束

对于主键约束而言，其默认约束名为 PRIMARY；对于 Unique Key 约束，默认约束名和列名一样，也可以人为指定。

通过 information_schema 结构下的 TABLE_CONSTRAINTS 来查看当前 MySQL 库下所有的约束信息。

对于外键约束的命名，可通过查看表 REFERENCE_CONSTRAINTS 来详细了解。

4.6.3　约束和索引的区别

确实，当用户创建了一个唯一索引就创建了一个唯一的约束。

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

4.6.4　对错误数据的约束

数据库本身没有对数据的正确性进行约束。如果用户想通过约束对于数据库非法数据的插入或更新，即数据库提供报错而不是警告，那就必须设置参数 sql_mode，用来严格审核输入的参数。

SET sql_mode = 'STRICT_TRANS_TABLES';

4.6.5　ENUM和SET约束

搭配 sql_mode 可以规定某一个域为 ENUM 指定的范围值。

SET：

多选字符串数据类型，适合存储“多个值”。

create table set_table
(
    id    int auto_increment primary key,
    hobby set ('music','movie','swimming','footbal')
);

insert into set_table(id, hobby)
values (1, 'music,movie,footbal');

结果:

4.6.6　触发器与约束

最多可以为一个表创建6个触发器;分别为insert,update,delete的before和after各定义一个;

mysql只支持for each row的触发方式,即按每行记录进行触发;

4.6.7　外键约束

外键用来保证参照完整性。

MyISAM 中的外键只是起到一个注释的作用，InnoDB 完整支持外键约束。

用户可以在执行 CREATE TABLE 时就添加外键，也可以在表创建后通过 ALTER TABLE 命令来添加。

一般来说，称被引用的表为父表，引用的表称为子表。外键定义时的 ON DELETE 和 ON UPDATE 表示对父表进行 DELETE 和 UPDATE 操作时，对子表所做的操作。

可定义的子表操作有：

CASCADE：当父表发生 DELETE 和 UPDATE 操作时，对应的子表中的数据也进行 DELETE 和 UPDATE 操作；
SET NULL：当父表发生 DELETE 和 UPDATE 操作时，相应的子表的数据被更新为 NULL，前提是子表中的列允许为 NULL。
NO ACTION：当父表发生 DELETE 和 UPDATE 操作时，抛出错误，不允许这类操作发生。
RESTRICT：当父表发生 DELETE 和 UPDATE 操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定 ON DELETE 或 ON UPDATE ，RESTRICT 就是默认的外键设置。
指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

　　外键约束对于参照完整性约束能起到很好的作用，但是对于数据的导入操作，外键约束的检查会浪费大量时间，因为 MySQL 的外键是即时检查的，所以对导入的每一行都会进行外键检查，用户可以在导入过程中忽视外键的检查：

　　SET foreign_key_checks = 0;

　　LOAD DATA...

　　Set foreign_key_checks = 1;

4.7　视图

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

4.7.1　视图的作用

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

　create view v_t as select * from t where id<10;

4.7.2　物化视图

mysql本身不支持物化视图;

4.8　分区表

4.8.1　分区概述

不是在存储引擎层完成的,Innodb , myisam ,ndb支持分区, CSV,MERGE,fedorated等不支持;

mysql支持水平分区,不支持垂直分区;

mysql分区是局部分区索引(分区存放索引+数据); 全局索分区是指所有数据的索引放在一个分区中;

查看当前数据库是否支持分区 : show variables like '%partition%' ; (mysql8中移除);

mysql8官方手册:

从MySQL服务器中删除了通用的分区处理程序。为了支持分区对于给定的表，用于该表的存储引擎现在必须提供它自己的(“本机”)分区处理程序。从MySQL中删除了——partition和——skip-partition选项和分区相关的条目不再显示在显示插件的输出中或INFORMATION_SCHEMA。插件表。目前有两个MySQL存储引擎提供了本地分区支持:InnoDB和NDB。其中,MySQL 8.0只支持InnoDB。使用任何在MySQL 8.0中创建分区表的尝试其他存储引擎出现故障。

例如 :

-- 创建表分区demo
create table t3
(
    col1 int  null,
    col2 date null,
    col3 int  null,
    col4 int  null,
    unique key (col1, col2, col3, col4)
)
    partition by HASH ( col3 )
        partitions 4
;

查看表分区信息 : (mysql8)

select * from information_schema.PARTITIONS
where table_name ='t3';

4.8.2　分区类型

RANGE分区 :行数据基于属于一个给定连续区间的列值被放入分区;
LIST分区: 和RANGE分区类似, 只是list分区是离散的值;
Hash分区: 根据用户自定义的表达式的返回值来进行分区,返回值不能为负数;
key分区: 根据mysql数据库提供的哈希函数来进行分区

RANGE分区

CREATE TABLE t_range
(
    id INT
) engine = INNODB
    PARTITION BY RANGE (ID) (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20)
        );

INSERT INTO test.t_range (id) VALUES (9); 
INSERT INTO test.t_range (id) VALUES (10) ;
INSERT INTO test.t_range (id) VALUES (15) ; 

-- 查询:
select * from information_schema.PARTITIONS
where table_name ='t_range';

结果是 : p0 1条数据 , p1 有2条数据;

超过分区定义的值会报错:

INSERT INTO test.t_range (id) VALUES (30) ;

LIST分区

List分区和Range分区类似, 只是分区类的值是离散的,而非连续的;

CREATE TABLE t_list
(
    a INT,
    b INT
) engine = INNODB
    PARTITION BY LIST (b)
        (
        PARTITION p0 VALUES IN (1,3,5,7,9),
        PARTITION p1 VALUES IN (0,2,4,6,8) );
-- 查看:
select TABLE_NAME,PARTITION_NAME,TABLE_ROWS
from information_schema.PARTITIONS
where table_name = 't_list';

同样,如果插入的值不在分区定义汇总,则数据库会抛出异常;

在INSERT多行数据时，如果有一行数据插入失败，MyISAM存储引擎会将之前的行数据都插入，但之后的数据不会被插入。

但是InnoDB会把全部行插入当做事务，有一条失败就会全部失败，前面插入的行数据会回滚。

HASH分区

hash分区的目的是将数据均匀地分布在预先定义的各个分区中,保证各分区的数据数量大致是一样的;

CREATE TABLE t_hash
(
    a INT,
    b datetime
) engine = INNODB
    PARTITION BY HASH (year(b))
        PARTITIONs 4 ;

PARTITIONs 非负整数表示将要被分割成分区的数量,默认1

插入 2010-04-01 , 2010 mod 4 =2 ,所以放在p2分区中 ;

insert into t_hash values (1,'2010-04-01') ;

select TABLE_NAME,PARTITION_NAME,TABLE_ROWS

from information_schema.PARTITIONS

where table_name = 't_hash';

LINEAR HASH

mysql支持另一种称为LIner Hash的分区,它使用更加复杂的算法来确定新插入行到分区表的位置;

优点: 增加.删除.合并和拆分分区将变得更加快捷,有利于处理大量数据的表;

缺点:与HASH分区得到的数据分布相比,各个分区间的数据可能不大均衡;

KEY分区

与HASH分区相似,不同之处在于HASH分区通过用户定义的函数进行分区,KEY分区使用mysql数据库提供的函数进行分区;

CREATE TABLE t_key
(
    a INT,
    b datetime
) engine = INNODB
    PARTITION BY KEY (b)
        PARTITIONs 4
;

COLUMNS分区

可以对多个列的值进行分区;

支持以下数据类型:

所有整数类型:INT,TINYINT,BIGINT; 而FLOAT和DECIMAL则不予支持;

日期类型,DATE和DATETIME, 其余日期类型不予支持;

字符串类型: 如CHAR,VARCHAR,BINARY 和VARBINARY; 而BLOB和TEXT类型不予支持;

这里举例对单列分区:

CREATE TABLE t_columns_range
(
    a INT,
    b datetime
) engine = INNODB
    PARTITION BY RANGE COLUMNS (b) (
        PARTITION p0 VALUES LESS THAN ('2009-01-01'),
        PARTITION p1 VALUES LESS THAN ('2010-01-01')
        )
;

当然也可以用LIST来分区;

4.8.3　子分区

也就是在分区的基础上在进行分区,有时候也称复合分区;

mysql允许在RANGE和LIST的分区上再进行HASH或KEY的子分区;

CREATE TABLE ts
(
    a INT,
    b DATE
) engine = INNODB
    PARTITION BY RANGE (YEAR(b))
        SUBPARTITION BY HASH (TO_DAYS(b))
        SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        );

4.8.4　分区中的NULL值

Mysql允许对null值做分区,但处理的方法与其他数据库可能完全不同;

mysql分区总是视null值小于任何一个非null的值;

RANGE中使用null值,则会将该值放入最左边的分区;

LIST分区下要使用NULL值,则必须显式地之处哪个分区中放入NULL值, 否则会报错;

HASH和KEY分区对于NULL处理方式和RANGE分区,LIST分区不一样,任何分区函数都会讲NULL值记录返回为0;

4.8.5　分区和性能

分区并不是一定能带来性能的提高。要合理分区。有时候有主键和索引就可以很快查询到所需数据，减少查询的数据量才是更快的。因为索引的搜索是B+树。

LOTP应用中,分区要谨慎设计;

4.8.6　在表和分区间交换数据

ALTER TABLE ...EXCHANGE PARTITION ;

该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换;

必须满足一定的条件 ;

4.9　小结

省略...

posted @ 2020-10-12 09:09 将军上座阅读(188) 评论(0) 编辑收藏举报

刷新页面返回顶部

将军上座

菩提本无树,明镜亦非台

InnoDB存储引擎 (第4章 表)

4.1 索引组织表

4.2 InnoDB逻辑存储结构

4.2.1 表空间

4.2.2 段

4.2.3 区

4.2.4 页

4.2.5 行

4.3 InnoDB行记录格式

4.3.1 Compact行记录格式

4.3.2 Redundant行记录格式

4.3.3 行溢出数据

4.3.4 Compressed和Dynamic行记录格式

4.3.5 CHAR的行结构存储

4.4 InnoDB数据页结构

4.4.1 File Header

4.4.2 Page Header

4.4.3 Infimum和Supremum Records

4.4.4 User Records和Free Space

4.4.5 Page Directory

4.4.6 File Trailer

4.4.7 InnoDB数据页结构示例分析

4.5 Named File Formats机制

4.6 约束

4.6.1 数据完整性

4.6.2 约束的创建和查找

4.6.3 约束和索引的区别

4.6.4 对错误数据的约束

4.6.5 ENUM和SET约束

4.6.6 触发器与约束

4.6.7 外键约束

4.7 视图

4.7.1 视图的作用

4.7.2 物化视图

4.8 分区表

4.8.1 分区概述

4.8.2 分区类型