21-4-29_innodb内幕

2022-01-27 01:22:39

4.3.3 行溢出数据

innodb将一条记录中的某些数据存储在真正数据页面之外，如BLOB、LOB这些大对象列数据，VARACHAR也有可能被存放为行溢出数据

varchar(N)中的N指字符的长度，而varchar类型最大支持65535字节，且是所有varchar列的长度总和

一般情况，innodb的数据都在B-tree node中，发生行溢出时，数据存放在页类型为uncompress BLOB页中，数据页只保存了前768字节的前缀数据。之后是偏移量，指向行溢出页

为了保证B+树的含义，不使成为链表，则保证一个页中放下两条数据，

Text 或 Blob 的类型也跟 varchar 一样

4.3.4 Compressed 和 Dynamic

行溢出方式：

? 数据页只存放20字节指针，实际数据存放 Off Page中

Compact 和 Redundant都会存放前缀的768个前缀字节

Compressed会使用zlib算法进行压缩

4.3.5 Char的行结构存储

mysql>  create table j (a char(2) )character set gbk;
Query OK, 0 rows affected (1.74 sec)

mysql> insert into j select ‘ab‘;
Query OK, 1 row affected (0.33 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> set names gbk;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into j select ‘我们‘;
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into j select ‘a‘;
Query OK, 1 row affected (0.18 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select a,char_length(a),length(a) from j\G
*************************** 1. row ***************************
             a: ab
char_length(a): 2
     length(a): 2
*************************** 2. row ***************************
             a: 我们
char_length(a): 2
     length(a): 4
*************************** 3. row ***************************
             a: a
char_length(a): 1
     length(a): 1
3 rows in set (0.18 sec)

mysql> select a,HEX(a) from j\G
*************************** 1. row ***************************
     a: ab
HEX(a): 6162
*************************** 2. row ***************************
     a: 我们
HEX(a): CED2C3C7
*************************** 3. row ***************************
     a: a
HEX(a): 61
3 rows in set (0.00 sec)

对于多字节字符编码的CHAR数据的存储，innodb会将其视为变长字符类型，和varchar实际存储基本没区别

4.4 InnoDB 数据页结构

数据页组成

File Header文件头 38字节
Page Header页头 56字节
Infimum 和 Supremum Records 虚拟的最小最大记录
User Records 行记录
Page Directory页目录
File Trailer文件结尾信息 8字节

4.4.1 File Header

记录头信息：

页类型

14部分，56字节

4.4.3 Infimum 和 Supremum Record

虚拟行记录，限定边界，永远最大和最小

在页被创建时建立，不会被删除

4.4.4 UserRecord 和Free Space

UserRecord即实际存储行记录的内容

FreeSpace空闲空间，链表数据结构，记录被删除后会被加入空闲链表

4.4.5 PageDirectory

记录指针Slots槽或者DirectorySlots目录槽：存放记录的相对位置

是一个稀疏目录，即可能包含多个记录

最小伪记录的n_owned值为1，最大伪记录的n_owned值为[1, 8]

其他用户记录的n_owned取值范围为[4, 8]

当记录插入删除时需要对槽进行重新计算

slots记录按照索引值大小逆序存放（记录分组的第一个记录的值），适合使用二分查找

二分查找得到一个粗略记录，再通过记录中的recorder header的next_record查找

4.4.6 File Trailer

验证页是否完整写入磁盘，只有一个FIL_PAGE_END_LSN，8字节，4字节是checksum，4字节和File Header的FIL_PAGE_LSN相同

将这两个值与File Header的Fil_page_space_or_checksum和Fil_page_lsn比较，通过innodb的checksum比较

innodb每次读取页都会先检验页的完整，会有一定的开销

innodb_checksum_algorithm控制检验算法，默认crc32

4.5 Named File Formats 机制

通过此机制来解决不同版本下页结构兼容性的问题

1.0.x版本之前的文件格式定义为 Antelope，将当前版本支持的定义为Barracuda，新版本总是包含之前的版本页格式

4.6 约束

4.6.1 数据完整性

数据完整性

实体完整性保证每个表都有一个主键

使用primary key或unique key来保证实体完整性，甚至可以编写触发器
域完整性保证每列值满足特定条件
- 使用合适的数据类型
- 外键约束
- 触发器
- default约束
参照完整性保证两张表之间的关系
- 外键
- 触发器
InnoDB提供
- Primary Key
- Unique Key
- Foreign Key
- DEfault
- NOT NULL

4.6.2 约束的创建和查找

创建约束：

表建立时定义
利用Alter table

Unique Key：create unique index，默认约束名是列名，也可以命名

主键默认约束名为PRIMARY

Foreign Key约束有特殊的命名

create table u(id int,name varchar(20),id_card char(18),primary key(id),unique key(name));
Query OK, 0 rows affected (0.71 sec)

mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema = ‘learn_mysql‘ and table_name = ‘u‘\G
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: name
constraint_type: UNIQUE
2 rows in set (0.10 sec)

mysql> alter table u add unique key uk_id_card (id_card);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema = ‘learn_mysql‘ and table_name = ‘u‘\G
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: name
constraint_type: UNIQUE
*************************** 3. row ***************************
constraint_name: uk_id_card
constraint_type: UNIQUE
3 rows in set (0.00 sec)

mysql> create table p(id int,u_id int,primary key(id),foreign key(u_id) references p(id));
Query OK, 0 rows affected (0.64 sec)

mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema = ‘learn_mysql‘ and table_name = ‘u‘\G
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: name
constraint_type: UNIQUE
*************************** 3. row ***************************
constraint_name: uk_id_card
constraint_type: UNIQUE
3 rows in set (0.00 sec)

mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema = ‘learn_mysql‘ and table_name = ‘p‘\G
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: p_ibfk_1
constraint_type: FOREIGN KEY
2 rows in set (0.00 sec)

mysql> select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema=‘learn_mysql‘\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: learn_mysql
          CONSTRAINT_NAME: p_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: learn_mysql
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: NO ACTION
               TABLE_NAME: p
    REFERENCED_TABLE_NAME: p
1 row in set (0.00 sec)

4.6.3 约束和索引的区别

创建了唯一索引就创建了唯一约束

约束是一个逻辑概念，来保证数据的完整性

索引是一个数据结构，有逻辑概念，还代表着物理存储方式

4.6.4 对错误数据的约束

在某些设置下，mysql允许非法的或者不正确的数据的插入和更新，又或者在数据库内部将其转化为合法的值，如NULL变成0

当参数sql_mode可以进行更严格的设置

4.6.6 触发器与约束

作用：在执行insert、Delete、update之前或之后调用sql命令或者存储过程

创建

一个表最后6个触发器，三个操作的before和after各一个，只支持for each row

4.6.7 外键约束

保证参照完整性，可以在建表时建立也可以在通过alter

被引用的表称为父表，引用的表称为子表

定义时的ON DELETE 和 ON UPDATE 表示对父表操作时子表将会进行的操作：

CASCADE：删 --> 删，改 --> 改
SET NULL：子表相应的数据被设为NULL值
NO ACTION：抛出错误，不允许
RESTRICT：同 no action，如果定义外键时没有检查则默认RESTRICT

建立外键的列（子表）会被自动加上一个二级索引

foreign_key_checks控制表示在导入数据时都会进行外键检查

mysql> show variables like ‘foreign_key_checks‘;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

4.7 视图

视图view是一个命名的续表，由一个sql查询来定义，没有实际物理引擎

4.7.1 作用

作为安全层的作用，按照视图定义来取数据和更新数据

可以通过视图来更新基本表，称这样的视图为可更新视图

WITH CHECK OPTION针对可更新视图，更新的值是否需要检查

没有此参数插入不合构建视图的查询语句不会报错，会往原始表插入，但查询视图不会显示

有这参数后会直接报错

mysql> create table t(id int);
Query OK, 0 rows affected (0.34 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.15 sec)

mysql> insert into t values(2);
Query OK, 1 row affected (0.17 sec)

mysql> insert into t values(3);
Query OK, 1 row affected (0.14 sec)

mysql> create view v_t as select * from t where id < 2;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into v_t values (4);
Query OK, 1 row affected (0.18 sec)

mysql> select * from v_t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> alter  view v_t as select * from t where id < 2 with check option;
Query OK, 0 rows affected (0.23 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from v_t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into v_t values (4);
ERROR 1369 (HY000): CHECK OPTION failed ‘learn_mysql.v_t‘

show tables;查看表视图也会显示出来

查询当前数据库基本表

select * from information_schema.TABLES where table_type = ‘BASE TABLE‘ and table_schema = database()\G

查看视图的元数据

mysql> select * from information_schema.views where table_schema = database()\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: learn_mysql
          TABLE_NAME: v_t
     VIEW_DEFINITION: select `learn_mysql`.`t`.`id` AS `id` from `learn_mysql`.`t` where (`learn_mysql`.`t`.`id` < 2)
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 row in set (0.33 sec)

4.7.3 物化视图

mysql本身不支持，通过自建表和触发器实现部分物化视图功能

4.8 分区表

4.8.1 概述

mysql支持水平分区（同一行），将表或索引分成更小、更可以管理的部分

局部分区索引：一个分区即存放数据也存放索引

全局分区索引：数据在各个分区，索引在同一个对象

show plugins;查看是否有partition

分区类型：

range：给定连续区间
list：同range，是离散
hash：自定义表达式返回值
key：mysql提供的哈希函数

当表中存在主键或唯一索引，分区列必须是唯一索引的一个组成部分

没有时可以随意指定

21-4-29_innodb内幕

码农公寓

21-4-29_innodb内幕

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 Record

4.4.4 UserRecord 和Free Space

4.4.5 PageDirectory

4.4.6 File Trailer

4.5 Named File Formats 机制

4.6 约束

4.6.1 数据完整性

4.6.2 约束的创建和查找

4.6.3 约束和索引的区别

4.6.4 对错误数据的约束

4.6.6 触发器与约束

4.6.7 外键约束

4.7 视图

4.7.1 作用

4.7.3 物化视图

4.8 分区表

4.8.1 概述

码农公寓

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 Record

4.4.4 UserRecord 和Free Space

4.4.5 PageDirectory

4.4.6 File Trailer

4.5 Named File Formats 机制

4.6 约束

4.6.1 数据完整性

4.6.2 约束的创建和查找

4.6.3 约束和索引的区别

4.6.4 对错误数据的约束

4.6.6 触发器与约束

4.6.7 外键约束

4.7 视图

4.7.1 作用

4.7.3 物化视图

4.8 分区表

4.8.1 概述

相关文章