第四章表（学习笔记）

　　1. InnoDB逻辑存储结构

　　表是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

　　所有数据被逻辑的存放在一个空间中，称为表空间（tablespace），表空间又由段（segment）、区（extent）、页（page）等组成。

　　1.1 表空间

　　默认情况下，InnoDB存储引擎有一个共享表空间ibdata1，即所有数据存放在这个表空间内。如果用户启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。单独的表空间内存放的是数据、索引和插入缓冲Bitmap页，其他的数据，如回滚（undo）信息、插入缓冲索引页、系统事务信息、二次写缓冲（double write buffer）等还是存放在原来的共享表空间内。

　　1.2 段

　　数据段为B+树的叶子节点。索引段为B+树的非索引节点。回滚段在第七章涉及。

　　1.3 区

　　区是由连续页组成的空间，每个区的大小为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区。默认情况下，InnoDB存储引擎页的大小为16KB，即一个区一共有64个连续的页。InnoDB 1.2.x新增了参数innodb_pages_size，通过该参数可以将页设置为4K, 8K。

　　在每个段开始时，先用32个页大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这是因为，对于一些小表，或者undo这类段，可以在开始时申请较少的空间，节省磁盘容量的开销。

　　1.4 页

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

数据页（B-Tree Node）
undo页（undo log page）
系统页（System Page）
事务数据页（Transaction System Page）
插入缓冲位图页（Insert Buffer Bitmap）
插入缓冲空闲列表页（Insert Buffer Free List）
未压缩的二进制大对象页（Uncompressed BLOB Page）
压缩的二进制大对象页（Compressed BLOB Page）

　　1.5 行

　　InnoDB存储引擎中，数据是按行进行存放的。每个页最多允许存放16KB / 2 -200 行记录。

　　2. InnoDB行记录格式

　　2.1 Compact和Redundant 行记录格式参考博客：https://www.cnblogs.com/wilburxu/p/9435818.html

　　2.2 行溢出数据

　　MySQL官方手册中定义的长度65535，单位为字节，指的是一行中所有VARCHAR列的长度总和，如果超出，则无法创建。

　　有这样一个问题，InnoDB存储引擎的页为16KB，即16384字节，怎么存放65532字节呢？

　　行数据只保存了前768字节的前缀数据，之后是偏移量，指向行溢出页，即BLOB page。

　　2.3 Compressed 和 Dynamic 行记录格式　　

　　InnoDB 1. 0.x 版本开始引入了新的文件格式（file format，用户可以理解为新的页格式），以前支持的 Compact 和 Redundant 格式称为 Antelope 文件格式，新的文件格式称为 Barracuda 文件格式。Barracuda 文件格式下拥有两种新的行记录格式：Compressed 和 Dynamic。新的两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式，如图所示，在数据页中只存放 20 个字节的指针，实际的数据都存放在 Off Page 中，而之前的 Compact 和 Redundant 两种格式会存放 768 个前缀字节。　　

　　Compressed 行记录格式的另一个功能就是，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

　　3. InnoDB数据页结构

　　该部分参考了博客： https://www.huaweicloud.com/articles/81e3e782d140e68cfea255e90be83889.html

　　下图是InnoDB数据页结构示意图，以及各部分的简单说明。

　　每当我们插入一条记录，都会从Free Space部分申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

　　下面两张图显示的是一条记录被删除的过程。　　

　　delete_mask 这个属性标记着当前记录是否被删除。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已。所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

　　min_rec_mask B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。

　　n_owned 在页目录分组时使用，每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

　　heap_no 这个属性表示当前记录在本页中的位置，从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。heap_no值为0和1的记录，称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。　　

　　record_type 这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。

　　next_record 它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）

　　Page Directory（页目录）　　

　　下图是页目录的示意图。如果根据主键值查找页中某条记录呢？　　

通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
通过记录的next_record属性遍历该槽所在的组中的各个记录。

　　4. Named File Formats机制

　　InnoDB存储通过Named File Formats机制解决不同版本下页结构兼容性问题。

　　5. 约束

　　推荐阅读博客： https://blog.csdn.net/w_linux/article/details/79655073

　　5.1 数据完整性

　　约束用来保证数据库中数据的完整性。完整性有以下三种形式：

实体完整性保证表中有一个主键用户可以通过定义primary key 或 unique key约束来保证实体的完整性
域完整性保证每列的值满足特定的条件可以通过以下途径保证：

　　1）选择合适的数据类型确保一个数据值满足特定条件

　　2）外键约束

　　3）编写触发器

　　4） DEFAULT 约束

参照完整性保证两张表之间的关系通过定义外键或编写触发器以强制执行

　　InnoDB存储引擎提供以下几种约束：

primary key
unique key
foreign key
default
not null

　　5.2 约束的创建和查找

　　约束的创建可以有以下两种方式：

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

　　5.3 约束和索引的区别

　　的确，当用户创建了唯一索引就创建了唯一约束。但是，约束是一个逻辑概念，是为了保证数据完整性，索引是一个数据结构，是为了提高查询效率。

　　5.4 对错误数据的约束（NOT NULL）

　　在某些默认设置下，MySQL数据库允许非法的或不正确的数据的插入或更新，通过设置参数sql_mode = 'STRICT_TRANS_TABLES' 对输入的值进行约束。

　　5.5 ENUM 和 SET 约束

　　MySQL数据库不支持传统的CHECK约束，但是通过ENUM和SET可以解决部分这样的约束需求。如下代码所示：

　　但是ENUM只能对离散数值进行约束，对于传统CHECK约束支持的连续值的范围约束或更复杂的约束，需要通过触发器来实现对于值域的约束。

　　5.6 触发器与约束

　　触发器的作用是在执行INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。MySQL目前支持FOR EACH ROW的触发方式。通过触发器，用户可以实现MySQL数据库本身并不支持的一些特性，如对于传统CHECK约束的支持、物化视图、高级复制和审计等特性。下面代码展示触发器对约束的支持。

　　首先创建了一张usercah_err_log来记录错误数值更新的日志。然后创建触发器，判断新、旧值之间的差值，大于原值的数据会被判断为非法的输入，将cash设置为原来的值，并将非法数据更新到usercash_err_log。

　　5.7 外键约束

　　MyISAM存储引擎本身并不支持外键，对于外键的定义只起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。

　　一般来说，被引用的表称为父表，引用的表称为子表。可定义的子表操作有：

CASCADE 当父表发生DELETE或UPDATE操作时，对相应的子表数据也进行UPDATE 或DELETE操作
SET NULL 当父表发生DELETE或UPDATE操作时，对相应的子表数据更新为NULL
NO ACTION 当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生
RESTRICT 当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生

　　可以通过设置 foreign_key_checks = 0 在数据导入过程中忽视外键检查。

　　6. 视图

　　6.1 视图的作用

　　在MySQL数据库中，视图（view）是一个命名的虚表，由一个SQL查询来定义，可以当做表使用。与持久表不同的是，视图中的数据没有实际的物理存储。

　　视图的创建

CREATE [OR REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]  
AS select_statement  
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

　　视图的优点：

简单：对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来取数据或更新数据
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

　　对视图进行DML操作更新视图时，更新的是其背后的基表，但是包含下列内容之一，视图不能进行DML操作：

select子句中包含distinct
select子句中包含组函数
select语句中包含group by子句
select语句中包含order by子句
select语句中包含union 、union all等集合运算符
where子句中包含相关子查询
from子句中包含多个表
如果视图中有计算列，则不能更新
如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

　　6.2 物化视图

　　Oracle数据库支持物化视图——根据基表实际存在的实表，即物化视图的数据存储在非易失的存储设备上。物化视图可以用于预先计算保存多表的链接（JOIN）或聚集（GROUP BY）等耗时较多的SQL操作结果。这样，在执行复杂查询时，就可以避免进行这些耗时的操作，从而快速得到结果。物化视图有两种刷新方式：

ON DEMAND 在用户需要时进行刷新
ON COMMIT　物化视图在对基表的DML操作提交的同时进行刷新

　　MySQL本身并不支持物化视图，但是可以通过一些方法来部分实现物化视图的功能。

　　实现 ON DEMAND 功能：

　　有如下订单表，记录了用户采购电脑设备的信息　　

CREATE TABLE Orders
(
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
product_name  VARCHAR(30) NOT NULL,
price DECIMAL(8, 2) NOT NULL,
amount  SMALLINT   NOT NULL,
PRIMARY KEY  (order_id)
)ENGINE = InnoDB


INSERT INTO Orders VALUES
(NULL, 'CPU', 135.5, 1),
(NULL, 'Memory', 48.2, 3),
(NULL, 'CPU', 125.6, 3),
(NULL, 'CPU', 105.3, 4);

　　接着创建一张物化视图的基表，用来统计每件物品的信息

CREATE TABLE Orders_MV(
product_name   VARCHAR(30)       NOT NULL,
price_sum      DECIMAL(8,2)      NOT NULL,
amount_sum     INT               NOT NULL,
price_avg      FLOAT             NOT NULL,
orders_cnt     INT               NOT NULL,
UNIQUE   INDEX  (product_name);


INSERT  INTO Orders_MV
SELECT  product_name, SUM(price), SUM(amount), AVG(price), 
             COUNT(*) 
            FROM Orders
            GROUP BY product_name;

　　通过以上方式，用户就拥有了一个统计信息的物化视图。如果要实现ON DEMAND功能，只需要把表清空，重新导入数据即可。

　　如果要实现ON COMMIT 的物化视图，需要借助触发器来达到目的。需要对表Orders建立一个触发器：

DELIMITER  $$


CREATE  TRIGGER  tgr_Orders_insert
AFTER  INSERT ON Orders
FOR EACH ROW
BEGIN
    SET @old_price_sum = 0;
    SET @old_amount_sum = 0;
    SET @old_price_avg = 0;
    SET @old_orders_cnt = 0;

    SELECT IFNULL(price_num, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(order_cnt, 0)
    FROM Orders_MV
    WHERE product_name = NEW.product_name
    INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
    
    SET @new_price_sum = @old_price_sum + NEW.price;
    SET @new_amount_sum = @old_amount_sum + NEW.amount;
    SET @new_orders_cnt = @old_orders_cnt +1;
    SET @new_price_avg = @new_price_sum / @new_orders_cnt;

    REPLACE INTO Orders_MV
    VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);    
    END
    $$

    DELIMITER;

　　由于MySQL数据库本身并不支持物化视图，因此对于物化视图支持的查询重写功能就显得无能为力了，用户只能在应用程序端做一些控制　　

　　7. 分区表

　　7.1 分区概述

　　MyISAM, InnoaDB, NDB等存储引擎都支持分区功能。分区的过程是将一个表或索引分解为多个更小，更可管理的部分。就访问数据库的应用而言，逻辑上讲，只有一个表或一个索引，但是物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。MySQL数据库支持以下几种类型的分区：

RANGE分区：行数据基于一个给定连续区间的列值被放入分区
LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值
HASH分区：根据用户自定义的表达式的返回值来进行分区
KEY分区：根据MySQL数据库提供的哈希函数来进行分区

　　不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，下面创建分区的SQL语句会产生错误。

　　7.2.1 RANGE 分区

　　下面的SQL语句根据id列来创建分区表，id小于10时，数据插入P0，大于等于10，小于20时，插入P1分区。

　　查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由各个分区ibd文件组成，如下面的p0.ibd, p1.ibd.

　　RANGE分区主要用于日期列的分区，例如对于销售类的表，可以根据年份来分区存放销售记录，这种创建方式便于对sales表的管理：

可以通过alter table sales drop partition p2008 直接将2008年数据所在分区删除
加快某些查询操作

EXPLAIN PARTITIONS
SELECT * FROM sales
WHERE date>='2008-01-01' AND date<='2008-12-31'

　　SQL优化器只会去搜索P2008这个分区，而不是搜索所有的分区——称为Partition Pruning（分区修剪），查询速度会大幅提升。

　　优化器只能对YEAR(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP()这类函数进行优化选择。　　

　　7.2.2 LIST分区

　　list分区中的值是离散的，示例代码如下：

　　用INSERT插入多行数据的过程中遇到分区未定义的值时，MyISAM会将之前满足要求的行数据都插入，不满足要求的行数据即之后的不会插入，InnoDB将其视为一个事务，因此没有任何数据会被插入。

　　7.2.3 COLUMNS分区

　　前面介绍的RANGE, LIST, HASH和KEY分区中，数据必须是整型，如果不是整型，需要通过YEAR(), TO_DAYS(), MONTH()等函数化为整型。COLUMNS分区可以直接使用非整型的数据进行分区，分区数据类型直接比较而得。示例代码如下：

　　7.2.4 子分区

　　子分区是在分区的基础上在进行分区。MySQL数据库允许在RANGE和LIST的分区上再进行HASH 或 KEY的子分区。

　　7.2.5 分区中的NULL 值

　　对于RANGE分区，如果向分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区。

　　对于LIST分区，需要显式地指出那个分区中放入NULL值。

　　HASH 和 KEY 分区中，任何分区函数都会将含有NULL值地记录返回为0.

　　7.3 分区和性能

　　数据库的应用分为两类：一类是OLTP（在线事务处理），如blog，电子商务，网络游戏等；另一类是OLAP（在线分析处理），如数据仓库，数据集市。在一个实际的应用环境中，可能既有OLTP应用，也有OLAP应用。如网络游戏中，玩家操作的游戏数据库应用就是OLTP的，但是游戏厂商需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏，预测玩家的行为等，而这是OLAP的应用。

　　对于OLAP应用，分区可以提高查询的性能。因为，OLAP应用大多数查询需要频繁扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户需要查询表获取一年的数据。如果按时间戳进行分区，只需要扫描相应的分区即可。

　　对于OLTP应用，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录。对于一张大表，一般的B+树需要2~3次的磁盘IO。

　　如果对主键进行查询分区是有意义的。

　　如果用其他索引进行查询，查询开销则大的多，对于KEY的查询需要扫描所有的10个分区，每个分区的查询开销为2次IO，一共需要20次IO。

posted @ 2021-08-05 18:40 慕仙白阅读(222) 评论(0) 收藏举报

刷新页面返回顶部

慕仙白

第四章 表 （学习笔记）

1. InnoDB逻辑存储结构

2. InnoDB行记录格式

3. InnoDB数据页结构

4. Named File Formats机制

5. 约束

6. 视图

7. 分区表

公告

第四章表（学习笔记）

　　1. InnoDB逻辑存储结构

　　2. InnoDB行记录格式

　　3. InnoDB数据页结构

　　4. Named File Formats机制

　　5. 约束

　　6. 视图

　　7. 分区表