MySQL之表

淩亂°似流年 2021-08-29 13:26 391阅读 0赞

### 表 ###

*  0. 概述
 *  1. 索引组织表
 *  2. InnoDB 逻辑存储结构
 *   *  2.1 表空间
     *  2.2 段
     *  2.3 区
     *  2.4 页
     *  2.5 行
 *  3. InnoDB 行记录格式
 *   *  3.1 Compact 行记录格式
     *  3.2 Redundant 行记录格式
     *  3.3 行溢出数据
     *  3.4 Compressed 和 Dynamic 行记录格式
     *  3.5 CHAR 的行结构存储
 *  4. InnoDB 数据页结构
 *   *  4.1 File Header
     *  4.2 Page Header
     *  4.3 Infimum 和 Supremum Record
     *  4.4 User Record 和 Free Space
     *  4.5 Page Direction
     *  4.6 File Trailer
 *  5. Named File Formats 机制
 *  6. 约束
 *   *  6.1 数据完整性
     *  6.2 约束的创建和查找
     *  6.3 约束和索引的区别
     *  6.4 对错误数据的约束
     *  6.5 ENUM 和 SET 约束
     *  6.6 触发器与约束
     *  6.7 外键约束
 *  7. 视图
 *   *  7.1 视图的作用
 *  8. 分区表
 *   *  8.1 分区概述
     *  8.2 分区类型
     *   *  8.2.1 RANGE 分区
         *  8.2.2 LIST 分区
         *  8.2.3 HASH 分区
         *  8.2.4 KEY 分区
         *  8.2.5 COLUMN 分区
     *  8.3 子分区
     *  8.4 分区中的 NULL 值
     *  8.5 分区和性能

# 0. 概述 #

本篇博客对其中结构性的内容并没有进行深究，同时认为深究的意义不是很大，如果各位有兴趣，可以参阅《MySQL技术内幕》。

# 1. 索引组织表 #

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

如果在创建表时没有显示定义主键，则`InnoDB` 按照如下方式选择或创建主键。

1.  如果存在非空且唯一性索引，则将该列作为主键。
2.  如果不存在，则 `MySQL` 会自动创建一个 6 字节大小的指针作为主键。

# 2. InnoDB 逻辑存储结构 #

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70]

## 2.1 表空间 ##

默认情况下，`InnoDB`有一个共享表空间`ibdata1`，所有数据都存放在这个表空间内。

## 2.2 段 ##

表空间由各个段组成，常见的段有数据段、索引段、回滚段等。

由于`InnoDB` 表是索引组织的，因此数据即索引，索引即数据。数据段即为 `B+`树的叶子节点，索引段即为`B+` 树的非叶子节点。

## 2.3 区 ##

区是由连续页组成的空间，在任何情况下每个区的大小都为 1 MB。

默认情况下，`InnoDB` 页的大小为 16 `KB`，即一个区中一共有 64 个连续的页。

## 2.4 页 ##

页是`InnoDB`磁盘管理的最小单元，`1.2 X`版本后，可以将其设置为`4k`、`8k`、`16k`。

## 2.5 行 ##

`InnoDB`中数据是按行存放的，每页最多存放 7992 行记录。

# 3. InnoDB 行记录格式 #

`InnoDB` 提供了 `Compact` 和 `Redundant` 两种格式来存放行记录数据。

在 `MySQL 5.1` 版本中，默认设置为 `Compact` 行格式。

## 3.1 Compact 行记录格式 ##

`Compact`格式，其设计目标是高效地存储数据。即一个页中存放的行数据越多，其性能就越高。

![在这里插入图片描述][20200530223806612.png]

`Compact` 行记录格式的首部是一个非 NULL 变长字段长度列表，并且其是按照列的顺序逆序放置的。用`1`字节或者`2`字节表示。

`NULL` 标志位，该位指示了该行数据是否有 `NULL` 值，有则用 `1` 表示。用 `1` 字节表示。

## 3.2 Redundant 行记录格式 ##

不同于 `Compact` 行记录格式，`Redundant` 行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。

![在这里插入图片描述][20200530224132891.png]

## 3.3 行溢出数据 ##

`InnoDB`可以将一条记录中的某些数据存储在真正的数据页面之外，这部分数据称为行溢出数据。

## 3.4 Compressed 和 Dynamic 行记录格式 ##

`InnoDB 1.0.x` 引入新的文件格式 `Barracuda` ，该文件格式拥有两种新的行记录格式：`Compressed` 和 `Dynamic`。

新的两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式，如图所示，**在数据页中只存放20个字节的指针，实际的数据都存放在 Off Page 中**，而之前的 Compact 和Redundant 两种格式会存放 768 个前缀字节。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 1]

`Compressed` 的另一个功能就是，存储在其中的行数据会以 zlib 算法进行压缩。

## 3.5 CHAR 的行结构存储 ##

从 `MySQL 4.1` 开始，`CHAR(N)` 中的 `N` 指的是字符的长度，而不是之前版本的字节长度。

对于 `UTF8` 下 `CHAR(10)` 类型的列，其最小可以存储 `10` 字节的字符，而最大可以存储`30`字节的字符。

因此，对于多字节字符编码的 CHAR 数据类型的存储，InnoDB 存储引擎在内部将其视为变长字符类型，**因此可以认为多字节字符集的情况下，CHAR和VARCHAR的实际行存储基本是没有区别的，对于未能占满长度的字符还是填充 0x20**。

# 4. InnoDB 数据页结构 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 2]

## 4.1 File Header ##

`File Header`用来记录页的一些头信息。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 3]  
       `InnoDB`存储引擎中页的类型：

![在这里插入图片描述][20200530225637899.png_x-L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70]

## 4.2 Page Header ##

`Page Header`用来记录数据页的状态信息。

![在这里插入图片描述][20200530225950343.png_xhbmdfemhhb18_size_16_color_FFFFFF_t_70]

## 4.3 Infimum 和 Supremum Record ##

在 `InnoDB` 中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。`Infimum` 记录是比该页中任何主键值都要小的值，`Supremum` 比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下都不会被删除。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 4]

## 4.4 User Record 和 Free Space ##

`User Record` 实际存储行记录的内容。

`Free Space` 指的是空闲空间，是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲列表中。

## 4.5 Page Direction ##

`Page Directory`（页目录）中存放了记录的相对位置。

**B+树索引本身并不能查找到具体的的一条记录，能找到的只是该记录所在的页。数据库把页载入到内存，然后通过 `Page Directory` 再进行二叉查找**。

## 4.6 File Trailer ##

通过页中的 `File Trailer` 部分，可以检测页是否已经完整地写入磁盘（如写入过程中磁盘损坏、机器关闭等）。

在默认配置下，`InnoDB` 存储引擎每次从磁盘读取一个页就会检测该页的完整性。

# 5. Named File Formats 机制 #

`Name File Formats`机制用来解决不同版本下页结构兼容性的机制。

# 6. 约束 #

## 6.1 数据完整性 ##

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性。

约束的作用便是用来保证数据完整性。

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

*  实体完整性保证表中有一个主键
 *  域完整性保证数据每列的值满足特定的条件

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

外键（`Foreign Key`）约束；

编写触发器；

还可以考虑用 `DEFAULT` 约束作为强制域完整性的一个方面。

*  参照完整性保证两证表之间的关系

`InnoDB` 提供以下几种约束：

*  Primary Key
 *  Unique Key
 *  Foreign Key
 *  DEFAULT
 *  NOT NULL

## 6.2 约束的创建和查找 ##

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

1.  表建立时进行约束定义
2.  利用 `ALTER TABLE`命令进行创建约束

## 6.3 约束和索引的区别 ##

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

## 6.4 对错误数据的约束 ##

`MySQL`并没有对非法数据的插入或更新进行约束，如果需要插入非法数据时，选择报错，必须设置参数`sql_mode`，来严格审核输入的参数。

## 6.5 ENUM 和 SET 约束 ##

`MySQL`不想`SQLServer`那样，拥有`CHECK`约束,，只能提供离散的约束方式`ENUM`和`SET`来解决一部分需求。

CREATE TABLE a(
    	id INT,
        sex SET ('male','fenake');
    );

`ENUM` 与 `SET` 的区别：

`SET` 约束可以从里面取多个值，而`ENUM`只能取1个。

## 6.6 触发器与约束 ##

触发器的作用是在执行`INSERT`、`DELETE`和`UPDATE`命令之前或之后自动调用`SQL`命令或存储过程。

## 6.7 外键约束 ##

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

对父表的外键进行`DELETE`和`UPDATE`操作时，子表有如下四种操作：

*  CASCADE
 *  SET NULL
 *  NO ACTION
 *  RESTRICT

`CASCADE` 表示当父表发生 `DELETE` 或 `UPDATE` 操作时，对相应的子表中的数据也进行 `DELETE` 或 `UPDATE` 操作。

`SET NULL` 表示当父表发生 DELETE 或 UPDATE 操作时，相应的子表中的数据被更新为 `NULL` 值，但是子表中相应的列必须允许设置为 `NULL` 值。

`NO ACTION` 表示当父表发生 `DELETE` 或 `UPDATE` 操作时，抛出错误，不允许这类操作发生。

`RESTRICT` 表示当父表发生 `DELETE` 或 `UPDATE` 操作时，抛出错误，不允许这类操作发生。

`RESTRICT`是在修改或者删除之前去检查从表中是否有对应的数据，如果有，拒绝操作，而`NO ACTION`是在修改或者删除完以后去检查从表中是否有对应的数据，如果有，拒绝操作，但是在MySQL中，外键约束都会立即检查，所以两者等价。

RESTRICT 是默认的外键设置。

# 7. 视图 #

视图是一个命名的虚表，与持久表不同的是，视图中的数据没有实际的物理存储。

## 7.1 视图的作用 ##

在一定程度上起到一个安全层的作用：程序本身不需要关心基表（base table）的结构，只需要按照视图定义来读取数据或更新数据。

# 8. 分区表 #

## 8.1 分区概述 ##

分区指将一个表或索引分解为多个更小、更可管理的部分。

就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。

MySQL 数据库支持的分区类型为水平分区（指将同一表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。

分区可能会给某些 SQL 语句性能带来提高，但是分区主要用于数据库高可用性的管理。在`OLTP`应用中，对于分区的使用应该非常小心。

## 8.2 分区类型 ##

### 8.2.1 RANGE 分区 ###

行数据基于一个给定连续区间的列值被放入分区。

启用分区之后，表不再由一个 ibd 文件组成了，而是由建立分区时的各个分区 ibd 文件组成。

创建一个 `id` 列的区间分区表。当 `id` 小于 `10` 时，数据插入 `p0` 分区。当 `id` 大于等于 `10` 小于 `20` 时，数据插入 `p1` 分区。

create table t(
    	id INT
    )ENGINE = INNODB
    PARTITION BY RANGE (id(
    PARTITION P0 VALUES LESS THAN(10),
    PARTITION P1 VALUES LESS THAN(10));
    )

当插入一个不再分区中定义的值时，`MySQL` 会抛出一个异常。

`RANGE` 分区主要用于日期列的分区，例如对于销售类的表，可以根据年来分区存放销售记录。

### 8.2.2 LIST 分区 ###

和`RANGE`分区类型，只是`LIST`分区面向的是离散的值，而非连续的。

CREATE TABLE t(
    	a INT,
        b INT
    )ENGINE = INNODB
    PARTITION BY RANGE (b)(
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (0,2,4,6,8));

在用 `INSERT` 插入多个行数据的过程中遇到分区未定义的值时，`MyISAM` 和 `InnoDB` 的处理完全不同。`MyISAM` 会将之前的行数据都插入，但之后的数据不会被插入。而`InnoDB` 将其视为一个事务，因此没有任何数据插入。

### 8.2.3 HASH 分区 ###

根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。

`HASH` 分区的目的是将数据均匀地分布到预先定义地各个分区中，保证各分区地数据数量大致是一样的。

要使用 `HASH`分区来分割一个表，要在 `CHEATE TABLE`语句添加一个 `PARTITION BY HASH(expr)`子句，其中 “`expr`” 是返回一个整数的表达式。

CREATE table t_hash(
    	a INT,
        b DATETIME
    )ENGINE = InnoDB
    PARTITION BY HASH (YEAR(b))
    PARTITIONS 4;

### 8.2.4 KEY 分区 ###

根据`MySQL`数据库提供的哈希函数进行分区。

`KEY` 分区和 `HASH` 分区相似，不同在于 `HASH` 分区使用用户定义的函数进行分区，`KEY` 分区使用 `MySQL` 数据库提供的函数进行分区。

### 8.2.5 COLUMN 分区 ###

对于前四种分区，分区的条件是：整型（`integer`），如果不是整型，那应该需要通过函数将其转换为整性。

`COLUMNS` 分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转换为整性。

## 8.3 子分区 ##

子分区是在分区得基础上再进行分区，`MySQL`是在分区的基础上再进行分区，有时也称这种分区为符合分区。`MySQL`数据库允许再`RANGE`和`LIST`的分区上再进行`HASH`或`KEY`的子分区。

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

表 `ts` 先根据 `b` 列进行了 `RANGE` 分区，然后又进行了一次 `HASH` 分区，所以分区的数量应该为（3\*2=6）个

## 8.4 分区中的 NULL 值 ##

`MySQL` 允许对`NULL`值做分区。

`MySQL` 数据库得分区总是视 `NULL` 值小于任何得一个非 `NULL`值。

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

在 `LIST` 分区下要使用 `NULL` 值，则必须显示地指出那个分区中放入 `NULL` 值，否则会报错。

`HASH` 和 `KEY` 分区对于 `NULL` 的处理方式和 `RANGE` 分区、`LIST` 分区不一样。任何分区函数都会将含有 `NULL` 值得记录返回为 `0`。

## 8.5 分区和性能 ##

对于`OLAP`（在线分析处理）的应用，分区的确是可以很好地提高查询地性能，因为`OLAP`应用大多数查询需要频繁地扫描一张很大的表。

对于`OLTP`（在线事务处理）的应用，则未必，如果涉及不好的分区会带来严重的性能问题。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200530222306126.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18=,size_16,color_FFFFFF,t_70
[20200530223806612.png]: https://img-blog.csdnimg.cn/20200530223806612.png
[20200530224132891.png]: https://img-blog.csdnimg.cn/20200530224132891.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200530224605187.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200530225451625.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200530225616414.png?xcess=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18=,size_16,color_FFFFFF,t_70
[20200530225637899.png_x-L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200530225637899.png?x-L3dhbmdfemhhb18=,size_16,color_FFFFFF,t_70
[20200530225950343.png_xhbmdfemhhb18_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200530225950343.png?xhbmdfemhhb18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200530230109246.png?/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdfemhhb18=,size_16,color_FFFFFF,t_70