MySQL技术内幕读书笔记三、MySQL表结构

最新推荐文章于 2021-06-29 17:27:45 发布

小宇哥x

最新推荐文章于 2021-06-29 17:27:45 发布

阅读量191

点赞数

分类专栏： MySQL技术内幕读书笔记文章标签：索引组织表表空间数据页B-tree Node 分区表 InnoDB数据页结构

本文链接：https://blog.csdn.net/qq_42372017/article/details/117405811

版权

MySQL技术内幕读书笔记专栏收录该内容

6 篇文章 1 订阅

订阅专栏

文章目录

一、表结构

一、表结构

1.1　索引组织表

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

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

当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

1.2　InnoDB逻辑存储结构

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

1.2.1　表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。

如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

1.2.2　段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（Leaf node segment），索引段即为B+树的非叶子节点（Non-leaf node segment）。

1.2.3　区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4～5个区。在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页。

1.2.4　页

页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。

在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.2.5　行

InnoDB存储引擎是面向行的（row-oriented），也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200行的记录，即7992行记录。

1.3　InnoDB行记录格式

InnoDB存储引擎的记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。在InnoDB 1.0.x版本之前，InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据，这也是目前使用最多的一种格式。

在MySQL 5.1版本中，默认设置为Compact行格式。用户可以通过命令SHOW TABLE STATUS LIKE’table_name’来查看当前表使用的行格式，其中row_format属性表示当前所使用的行记录结构类型。如：

mysql＞SHOW TABLE STATUS like'mytest%'\G;
***************************1.row***************************
Name:mytest
Engine:InnoDB
Version:10
Row_format:Compact
Rows:6
Avg_row_length:2730
Data_length:16384
Max_data_length:0
Index_length:0
Data_free:0

1.3.1　Compact行记录格式

Compact行记录是在MySQL 5.0中引入的，其设计目标是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高。

Compact行记录格式的首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：

若列的长度小于255字节，用1字节表示；
若大于255个字节，用2字节表示。

变长字段的长度最大不可以超过2字节，这是因在MySQL数据库中VARCHAR类型的最大长度限制为65535。变长字段之后的第二个部分是NULL标志位，该位指示了该行数据中是否有NULL值，有则用1表示。该部分所占的字节应该为1字节。接下来的部分是记录头信息（record header），固定占用5字节（40位）。

最后的部分就是实际存储每个列的数据。需要特别注意的是，NULL不占该部分任何空间，即NULL除了占有NULL标志位，实际存储不占有任何空间。另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。若InnoDB表没有定义主键，每行还会增加一个6字节的rowid列。

1.3.2　行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。

InnoDB存储引擎的页为16KB，即16384字节，怎么能存放65532字节呢？因此，在一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。

mysql＞CREATE TABLE t(
-＞a VARCHAR(65532)
-＞)ENGINE=InnoDB CHARSET=latin1;
Query OK,0 rows affected(0.15 sec)
mysql＞INSERT INTO t
-＞SELECT REPEAT('a',65532);
Query OK,1 row affected(0.08 sec)
Records:1 Duplicates:0 Warnings:0

首先创建了一个列a长度为65532的VARCHAR类型表t，然后插入了列a长度为65532的记录，接着通过工具py_innodb_page_info看表空间文件，可以看到的页类型有：

[root@nineyou0-43 mytest]#py_innodb_page_info.py-v t.ibd
page offset 00000000,page type＜File Space Header＞
page offset 00000001,page type＜Insert Buffer Bitmap＞
page offset 00000002,page type＜File Segment inode＞
page offset 00000003,page type＜B-tree Node＞,page level＜0000＞
page offset 00000004,page type＜Uncompressed BLOB Page＞
page offset 00000005,page type＜Uncompressed BLOB Page＞
page offset 00000006,page type＜Uncompressed BLOB Page＞
page offset 00000007,page type＜Uncompressed BLOB Page＞
Total number of page:8:
Insert Buffer Bitmap:1
Uncompressed BLOB Page:4
File Space Header:1
B-tree Node:1
File Segment inode:1

通过工具可以观察到表空间中有一个数据页节点B-tree Node，另外有4个未压缩的二进制大对象页Uncompressed BLOB Page，在这些页中才真正存放了65532字节的数据。

1.3.3　CHAR的行结构存储

通常理解VARCHAR是存储变长长度的字符类型，CHAR是存储固定长度的字符类型。了解行结构的内部的存储，并可以发现每行的变长字段长度的列表都没有存储CHAR类型的长度。

InnoDB存储引擎内部对CHAR类型在多字节字符集类型的存储。CHAR类型被明确视为了变长字符类型，对于未能占满长度的字符还是填充0x20。InnoDB存储引擎内部对字符的存储和我们用HEX函数看到的也是一致的。因此可以认为在多字节字符集的情况下，CHAR和VARCHAR的实际行存储基本是没有区别的。

1.4　InnoDB数据页结构

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

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

File Header（文件头）
Page Header（页头）
Infimun和Supremum Records
User Records（用户记录，即行记录）
Free Space（空闲空间）
Page Directory（页目录）
File Trailer（文件结尾信息）

其中File Header、Page Header、File Trailer的大小是固定的，分别为38、56、8字节，这些空间用来标记该页的一些信息，如Checksum，数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的。

1.5　约束

1.5.1　数据完整性

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束（constraint）机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说，数据完整性有以下三种形式：

域完整性保证数据每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：

选择合适的数据类型确保一个数据值满足特定条件。
外键（Foreign Key）约束。
编写触发器。
还可以考虑用DEFAULT约束作为强制域完整性的一个方面。

对于InnoDB存储引擎本身而言，提供了以下几种约束：

Primary Key
Unique Key
Foreign Key
Default
NOT NULL

1.5.2　约束和索引的区别

看到Primary Key和Unique Key的约束，有人不禁会问：这不就是通常创建索引的方法吗？那约束和索引有什么区别呢？

的确，当用户创建了一个唯一索引就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的，约束是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

1.6　分区表

1.6.1　分区概述

分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持。

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

当前MySQL数据库支持以下几种类型的分区：

RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。MySQL 5.5开始支持RANGE COLUMNS的分区。
LIST分区：和RANGE分区类型，只是LIST分区面向的是离散的值。MySQL 5.5开始支持LIST COLUMNS的分区。
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
KEY分区：根据MySQL数据库提供的哈希函数来进行分区。

1.RANGE分区

RANGE分区是最常用的一种分区类型。下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时，数据插入p0分区。当id大于等于10小于20时，数据插入p1分区。

CREATE TABLE t(
id INT
)ENGINE=INNDB
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN(10),
PARTITION p1 VALUES LESS THAN(20));

2.LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。

mysql＞CREATE TABLE t(
-＞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)
-＞);
Query OK,0 rows affected(0.26 sec)

3.HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

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

4.KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。对于NDB Cluster引擎，MySQL数据库使用MD5函数来分区；对于其他存储引擎，MySQL数据库使用其内部的哈希函数，这些函数基于与PASSWORD()一样的运算法则。如：

mysql＞CREATE TABLE t_key(
-＞a INT,
-＞b DATETIME)ENGINE=InnoDB
-＞PARTITION BY KEY(b)
-＞PARTITIONS 4;
Query OK,0 rows affected(0.43 sec)

5.COLUMNS分区

RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须是整型（interger），如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()，TO_DAYS()，MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGE COLUMNS分区可以对多个列的值进行分区。

COLUMNS分区支持以下的数据类型：

所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。
日期类型，如DATE和DATETIME。其余的日期类型不予支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不予支持。

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,'mmmm'),
PARTITION p2 VALUES LESS THAN(15,30,'sss'),
PARTITION p3 VALUES LESS THAN(MAXVALUE,MAXVALUE,MAXVALUE)
);