三，InnoDB 表
1，InnoDB逻辑存储结构
表空间，段，区，页(块)，行
1）表空间
InnoDB存储引擎逻辑结构最高层，所有数据存放在表空间中，如果不启用innodb_file_per_table，则每张表内的数据放在共享表空间中，如果启用则单独放到一个表空间内。
表空间中存放数据，索引，插入缓冲。而undo log，系统事务信息，二次写缓存都是存放在共享表空间，也就是说即使启用innodb_file_per_table，共享表的大小还是会变大，即使是roll back也不会回收空间，但是innodb会根据undo log是否还有用来进行标记，为下一次插入undo日志所使用
2）段
段有数据段，索引段，回滚段等
在Innodb中数据即索引，索引即数据，所以数据为B+树的叶节点，而索引为B+树的非叶子节点
并不是每个表空间都有段，表空间由分散的页和段组成
3）区
区由连续64块页组成，一个页的大小为16KB，一个区大小为1MB，而创建表时，并不是直接创建64块页，而是先使用32块离散的页空间，使用完后在以64个连续的页申请
4）页
InnoDB磁盘管理的最小单位，常见页类型：
5）行
数据的存放是按行进行存放。
2，InnoDB物理存储结构
InnoDB表由共享表空间，日志文件组，表结构定义文件组成
当开启innodb_file_per_table时，每个表独立产生一个表空间文件，以ibd结尾
.frm是表结构定义文件，与存储引擎无关
3，InnoDB行记录格式
InnoDB行记录格式分为两个类型：Compact 和 Redundant
1）Compact
- 变长字段长度列表：
开头首部是一个非NULL变长字段长度列表，且是逆序放置。当列的长度小于255字节占用1字节，当列长度大于255字节占用2字节，不可超过两字节。
- NULL标志位：
这个部位记录了当前改行数据是否有NULL值，用1标识
- 记录头信息（5 byte)：
-
事务ID列（6 byte 递增）
-
回滚指针列（7 byte）
-
RowID列（6 byte，当没有定义primary key时产生）
实操：
进行以上命令操作后，我们用Ultra去打开mytest.ibd文件（mysql/data文件夹下）(前提是要开启innodb_file_per_table=ON生成独立文件)，可以得到如下结果：
下图是上述图片第一行与第三行分析：
我们由此可以得出以下几点：
- 长度列表只用来标识变长字节，且是逆序放置
- char类型如果长度未填满会用0x20来进行填充
- rowID最后四个字节，代表着当前记录与下一记录的偏移量，实际 nextLog = currentLog + offset，由此可以看出页内部是通过一种链表的结构来串联各个记录的
2）Redundant
- 字节长度偏移列表：
与compact不同，此处记录的是一个字段的长度偏移列表，逆序放置，大于255使用2字节，小于255使用1字节，这里的字段包括了（事务ID，回滚指针，RowId，和列数据）
- 记录头信息（6 byte)：
-
事务ID列（6 byte 递增）
-
回滚指针列（7 byte）
-
RowID列（6 byte，当没有定义primary key时产生）
实操：
和compact一样打开文件
可以发现与compact相比，有比较大的不同，下面是第一行的分析结果
-
首先RowID的长度为6，即0x06。第二列是事务ID列长度为6，即6+6=12=0x0c。第三列为回滚指针，12+7=19=0x13。接下来依次类推，redundant记录了所有列(除记录头)的所有偏移地址，这一点是与Compcat不同的
-
Null处理不同：我们来看第三行NULL的行分析
对于varchar的空值，不占用任何存储空间，但是对于char的空值是占用原来同样的存储空间的，这一点与compact的空值标记位相比有很大的不同
同时根据字符集的不同，char和varchar所占用的实际字节也不同，这些会在下文说到
3.1 行溢出数据
**行溢出数据：**即将一条记录中的某些数据存储在真正数据页面之外
在mysql中，过大的对象会被存储在真正数据页面之外，除了BLOB和LOB这些常见的对象外，varchar实际上也会被存储在页面外。
1）varchar行溢出数据分析
在Mysql中varchar最多可以存储65535个字节，但是实际上只能存储65532字节，因为varchar需要一些其他字节进行长度标识
可以通过以下两条命令来进行测试：
create table test ( a varchar(65535) )charset=latin1 engine=innodb //创建失败，在严格模式下
create table test ( a varchar(65532) )charset=latin1 engine=innodb //创建成功
注：在严格模式下第一条命令会创建失败，如果非严格模式该命令会成功但是实际上varchar会转化为text类型
如果在不同的字符集下，例如GBK，UTF-8，65532也会报错，因为不同的字符集对应字符大小也是不同的。而mysql所规定的varchar大小为(65535)，是指所有列的varchar总和不超过 65535
create table test ( a varchar(22000),b varchar(22000),c varchar(22000) )charset=latin1 engine=innodb
但是问题来了，一个页16kb数据16384个字节，怎么能存放的下65532的varchar呢，于是这就牵扯到行溢出数据。超出的varchar只会在原页保留前768个字节以及指向BLOB页的指针，剩下的字节全部存放在Uncompressed BLOB PAGE中
我们知道一个页至少有两条行数据(否则就变成链表了)，如果刚好插入的两条数据不超过16384字节就不会放入BLOB页中，如果当前页只能存放一条数据，则超出的下一条数据会存放至溢出页中。BLOB，Text也是同理。
3.2 Compressed与Dynamic
在InnoDB plugin中，有两个新的格式。他会将BLOB的数据全部放入溢出页（留20字节的指针），还会使用zlib的算法进行压缩，对于BLOB，TEXT，VARCHAR这类长度类型进行有效存储。
3.3 char的行结构存储
char虽说是定长的字符类型，但是当采用不同的字符集时，会和varchar一样变成不定长度，对于未满长度字符还是填充0x20
4，InnoDB数据页结构
1）File Header(38 byte)
页头部存的是一个数据页的概要信息，是一个页专有的，而文件头存的是各种页通用的信息，比如页的类型是什么、页的编号是多少、上一页的页号是多少、下一页的页号是多少等等（说明是个双向链表）。
2）Page Header(56 byte)
记录页内部的一些信息
3）Infimum和Supremum
虚拟记录行，用来划清边界，分别代表最小记录和最大记录，且在行记录中Infimum next_record指向第一条记录，而最后一条记录指向Supremum，其大小和行格式有关（compact为26byte）
4）User Record
每插入一条记录就会从Free Space申请空间并且将记录连向User Record链表中
5）Page Directory
当数据量十分庞大的时候，我们不可能通过遍历的方式在页内寻找数据。由于每条记录都是有序的于是我们便可以使用类似跳表的思想来思考Page Directory。在Page Dirctory中，会将多个记录分组(4~8个)并且会使用SLOT（槽）来指向分组中的最大顺序的record
分组规则
最小记录单独一组
最大记录8个一组
其他记录4~8个一组
分组之后查询顺序：
- 通过B+树索引找到相应的页
- 页中使用二分法找到相应的槽，（要根据n_owned去查找）
- 找到槽中最小的record遍历下去
6）File Tailer（8 byte）
进行文件校验，前四个字节代表checksum和File Header的FILE_PAGE_SPACE_OR_CHKSUM进行比较，后四个字节和FILE_PAGE_LEN相同
5，Named File Formats
随着版本更替，类似于之前Dynamic，Compressed的新格式诞生，其对应着新的不同页格式，为了兼容新旧格式便采用Name File Formats机制来解决不同版本下页结构兼容性问题
6，约束
关系数据库和文件系统不同的一点就是，关系数据库能保证数据完整性而文件系统需要程序端来控制。关系数据库通过约束来保证数据完整性。
数据完整性的三种形式
- 实体完整性：保证只有个主键（primary key，unique key，触发器）
- 域完整性：保证数据的值满足特定的条件(合适的数据类型，外键约束，编写触发器，DEFAULT约束)
- 参照完整性：保证两张表之间的关系（触发器，外键）
InnoDB四种约束
- Unique key
- Foreign key
- Default
- NOT NULL
7，视图
视图是一个根据对物理表查询而虚拟化的表，没有物理表现形式。应用程序可以根据视图定义获取数据或者更新数据。起到一个安全层的作用。
1，虚拟视图
create table t (id int);//创建表
create view v_t as select * from t where id < 10; //创建物理表中id<10的视图
WITH CHECK OPTION:在更新时，会根据视图的定义来判断更新是否有效。
2，物化视图
在Oracle中支持物化视图，即这个视图时实际存在的，用于预先计算并保存表连接或聚集耗时较多的操作结果。
创建视图的方式：
- BUILD IMMEDIATE:创建物化视图时就生成数据
- ,BUILD DEFERRED:创建物化视图时不生成数据按需生成数据。
刷新模式：
- ON DEMAND：用户需要时刷新
- ON COMMIT：用户提交时便刷新
刷新方法：
- **FAST：**增量刷新
- **COMPLETE：**全量刷新
- **FORCE：**判断是否可以快速刷新，可以则FAST，不可以则COMPLETE
- **NEVER：**不刷新
MYSQL是不具备物化表的，如果想要构建物化表，可以配合触发器和两张表来实现
8，分区
分区功能不是存储引擎层完成的，所以InnoDB，MyISAM，NDB都是支持分区功能的，分区即将一个表或者索引物分成更小的部分，方便管理和优化查询。MYSQL数据库支持水平分区并不支持垂直分区
**水平分区：**一表中不同行记录放入到不同文件中
**垂直分区：**一表中不同列的数据放入到不同文件中
被分区的文件变为表名#p#分区名.ibd
查看表的分区情况
select table_name,partition_name,table_rows from information_schema.PARTITIONS where
table_schema=database() and table_name='$tableName'\G;
分区类型
- Range
Range分区是将表按照给定的连续区间的列值放入分区
create table range_t(id int)engine=innodb
partition by range(id)(
partition p0 values less than (10),
partition p1 values less than (20)
);
以上操作会建立两个表，<10的放入p0，<20的会放入p20，超出20的会报错无法存放，可以自行插入数据并且使用上述查看情况代码查看。
当你查询10<id<20的数据列时，只会查询p1表，而不会去查询p0表，大大提高了查询效率
explain partitions select * from range_t where id>=10\G;
但如果是查询5<id<20的时候便会查询两个分区，效率会降低
Range中只能使用YEAR()，TO_DAYS()，TO_SECONDS进行优化选择。
- List
与Range分区类似，不过是面向离散的区间
create table list_t(id int)engine=innodb
partition by list(id)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8)
);
如果插入的值不在定义中，则会抛出异常
- Hash
根据用户自定义的函数进行分区
Hash分区
create table hash_t(date datetime)engine=innodb
partition by hash(YEAR(date))
partitions 4;
按年份%4进行分区
Linear Hash分区
create table linear_t(date datetime)engine=innodb
partition by linear hash(YEAR(date))
partitions 4;
取大于分区数量4的下一个2的幂值V，V=POWER(2,CEILING(LOG(2,NUM)))=4;来进行分区
Linear分区对增删改查操作更快，但是不如hash分区均匀
- Key
根据MYSQL提供的函数来进行分区
create table key_t(date datetime)engine=innodb
partition by linear key(YEAR(date))
partitions 4;
根据MYSQL的powers-of-two算法得到的分区
COLUMNS分区
前面四种分区条件必须是整数类型，而COLUMS分区可以使用非整数类型数据
只需在每一个分区条件后加上一个COLUMNS字段即可，如下代码展示
create table key_t(date datetime)engine=innodb
partition by linear key columns(YEAR(date))
partitions 4;
子分区
子分区即在分区的基础上在进行分区，MYSQL运行在LIST，RANGE分区上再进行HASH，KEY分区
create table son_t (a INT,date datetime)engine=innodb
partition by range(YEAR(date))
subpartition by hash (MONTH(date))
subpartitions 12(
partition p2021 values less than (2022),
partition p2022 values less than (2023),
partition p0 values less than MAXVALUE
);
规则：
- 任何一个分区上面定义了子分区，那所有分区必须定义子分区，如下代码错误
每一个分区的子分区数目必须相同
每一个分区的子分区名字必须唯一
分区中NULL值
Oracle和Mysql对待NULL值的分区是不一样的，Oracle会专门建立一个NULL值分区来存放NULL值。
MYSQL处理
**RANGE：**把NULL视为比所有非NULL值都小的数，所以是放在分区最左边。()会把NULL的值记录
**LIST：**必须为NULL创建对应的NULL值
**KEY，HASH：**将NULL作为0值返回
分区与性能
实际上使用分区并不一定会使性能优化，相反可能还会导致查询变慢。
当你对索引行进行搜索时，会大大提高速度，但是对非索引行搜索时，需要遍历整个分区去寻找
我们知道1000w行的数据对应B+树也不超过三层，原先无分区状态，最多进行三次磁盘IO，而当你对其进行分区(分10个区，一个区100w，2层)，即非索引行要进行2*10=20次磁盘IO可见速度之慢
- 什么时候该用分区？
需要对批量数据每次进行数据分析，且不会涉及其他非索引行查询的可以使用分区
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
