[toc]

表

组织索引表

InnoDB中表都是根据主键顺序组织存放的，叫做索引组织表，InnoDB中每张表中都要有一个主键，如果没有显式的创建主键，会自行选择或者创建主键

判断表中是否存在为空唯一的索引，有就让该列成为主键（多个索引时已第一个符合条件的索引为主键）
如果没有则自动创建一个6字节大小的指针（==不知道这个自动创建的指针的并发分配是怎么样的==）
==这里提到了两个键同一起作为主键可以查看一下==

InnoDB逻辑存储结构

解释一下上面这个图，Tablespace就不说了，Segment（段）就索引的叶子节点，Extent（区）一个段由多个区组成，一个区由多个Page组成，page中存储着Row行数据，下面展开说一下

Tablespace

这个东西之前说过了，可以是放到共享的，也可以是每个表独立的，注意每个表独立的时候，其实独立存储的数据是表内行数据、索引和插入缓冲Bitmap页，其他类的数据（回滚undo、插入缓冲索引页、系统事务信息、二次写double write buffer等）还是放到共享空间中的，所以有的情况下共享空间还是会不断的增长

段 segment

常见的段有数据段、索引段、回滚段等

数据段就是叶子节点
索引段就是非叶子节点
回滚节点后面会讲

区

由连续的页组成的空间，区的大小是固定的1M，页一开始的大小是16k，后来可以手动设置为2k、4k、8k，于是一个页中就有对应的512、256、128、64个页

一个段是由开始的32个碎片页和之后的区组成的，如果新建一个表，这个表占用的空间是96k而不是1M，为了避免一些小表和undo段浪费空间，一开始的时候段中只有6个碎片页，随着数据的增多，最多分配32个碎片页也就是512k，之后就会分配连续的页来进行数据存储了

页

page也可以叫block块，是InnoDB磁盘管理的最小单位，常见的页类：

数据页
undo页
系统页
事务数据页
插入缓冲位图页
插入缓冲空闲列表页
未压缩的二进制大对象页
压缩的二进制对象页

行

数据按行进行存储，每个页最多能存放16k/2 - 200行的记录，即7992行数据

4.3 InnoDB行记录格式

目前有两种记录格式compact和redundant，redundant是对老版本的兼容，所以可以认为就是使用compact格式

compact行记录格式

变长字段长度列表（==这里可能有varchar指定长度的误区==）
- 如果列的长度小于255字节，用1字节表示
- 大于255字节，用2字节表示
- 这里存储的是一行数据中所有varchar类型长度总和，最大限制为65535，==之前的想法是两个字节也就是2的16次方为38528，无符号时最多可以表示65536，是错的，不知道这里是怎么存储的==
null标志位，标记该行中是否有null值，有就为1
记录投信息，固定占用5字节（40位），记录内容大概是：行是否被删除、改记录拥有的记录数、索引堆中该记录的排序记录、记录类型（普通、B+树节点指针等）、下一条记录的相对位置
列数据就是用户定义的数据，注意Null不占用该部分的任何空间（实际是占有之前的标志位），另外还有两个隐藏列：事务ID列和回滚指针列分别是6、7字节，如果没有定义主键的话还会有一个6字节的rowid

redundant行记录格式

老版本的记录方式了，不进行太详细的解释

同样的字段长度偏移列表
记录头中有一个字段叫n_fields占用10位，这就就是为什么表最多能有1023个列
列数据

行溢出数据

行溢出数据就是指某些没有直接存放在页里面的数据，存放的是真实数据存放的指针，通常认为text和BLOB等大对象会这样存放，但实际上varchar也可能溢出，BLOB也可能不溢出

关于varchar：

首先我们认为varchar能存放65535，实际上真正去创建表字段的时候最大允许设置65532；
varcahr(n)这里的n只的是字符数，而上面的65532是指字节，也就是说如果设置格式为GBK那就最多只能存32767，设置格式UTF8最多只能存21845

关于溢出存储策略：

准则是避免一个页只存一条数据，一个页最少要存储两条数据，由于页的大小为16k即16384字节，所以最大不发生溢出存储的varchar长度为8098（还会留出一部分空间存储页的结构信息，后面会讲，这里16384-8098*2 = 188字节）
对于之前提到的text和BLOB也是一样的如果数据小于8k的话就不会进行溢出存储
溢出存储时的结构：只存储前768个字节的前缀，剩下的存储到BLOB页中

Compressed和Dynamic行记录格式

InnoDB 1.0.x之后引入了新的文件格式，之前我们讲到的compact和redundant格式称为Antelope，新的文件格式称为Barracuda（Compressed和Dynamic）

新的格式会将溢出的数据（BLOB页中的数据）进行完全的溢出存储，数据页中只存储20字节的指针，结构如下

另外Compressed格式的另一个功能是对行数据进行zlib算法压缩，所以对BLOB、Text、varchar之类的大长度类型存储效率比较好

Char类型

之前认为char是定长的，实际从MySQL 4.1版本之后char(n)这里的n也变成了字符长度，同样的问题出现了，如果字段的字符集是多字节（比如GBK、UTF8），这个时候InnoDB存储的时候是采用和varchar一样的机制（会在变长字段长度中记录它的长度）

页的结构

这个数据官方没有出说明文档，这是看源码分析出来的，版本变更不一定准确，作为结构探索的产考

数据页由7个部分组成：

File Header 文件头 38 byte
Page Header 页头 56 byte
Infimun和supremum records
User Records 用户记录，就是行记录
Free Space 空闲空间
Page Directory 页目录
File Trailer 文件结尾信息 8 byte

file header

这里的东西太细不一个个过，主要的作用

维护页的完整性，通过对比刷新版本号来检查是否存储写入磁盘过程中是否发生过异常
定位页，双向链表中前后页的指针
标记表空间

用户数据在页中的记录位置和行数
维护可用空间指针
维护已删除的数据空间指针
维护最后插入记录指针
维护页在索引树中的位置，0x00代表叶节点，即第0层
记录当前页属于哪个索引

Infimum和Supremum Record

本质就是索引节点的标记数据，对应了最小和最大索引值

User Record 和 Free Space

就是用户的记录（链表）和空闲可用的空间（用链表维护，被删除的空间也会加入到链表中）

Page Directory

将页中的数据指针（每个指针称为槽）进行存放，这里是一个稀疏目录，一个槽中可能有多个指针，查询将页加载到内存之后，在内存中进行二叉查找，这个过程很快所以一般都忽略掉了

Named File Formats机制

InnoDB会不断的对页的存储格式进行更新，新的文件格式总数包含之前的文件格式，InnoDB已经通过a-z动物名的方式预留了很多的格式名称，以后会挨个进行实现

查看方式show VARIABLES like 'innodb_file_format'

约束 constraint

完整性约束

实现的几种方式：

通过数据类型来约束数据值的范围和满足特定条件
外键约束
编写触发器
default约束强制数据域完整性

InnoDB提供的几种约束：主键、唯一键、外键、默认值、非空

约束的创建和查找

这个就不过多的进行介绍了，创建表和修改表结构的时候都可以设置上面提到的约束，查看语句这里不一一列举

约束和索引的区别

约束是一个逻辑概念用来保证数据完整性，索引是一个数据结构代表数据物理存储的方式

==唯一性约束的实现是怎么样的==

对错误数据的约束

MySQL运行非法或错误的数据插入或更新，会自动转换为一个合法值

ENUM 和 set约束

比如性别这种，只支持male或者female可以用enum来实现，别的输入会报错

触发器与约束

MySQL5.1之后提供了比较完善的触发器机制，最多可以为一个表创建6个触发器：insert、update、delete的before和after；通过触发器可以定制化约束

外键约束

这个不常使用，这里也没有更细致的介绍

视图

虚拟表，没有实际的物理存储

视图的作用

作为一个抽象装置，也能起到安全层的作用（我们是用ES代替了视图），可以直接对视图进行更新，本质是更新了对应的基表的数据

物化视图

InnoDB不支持

分区表

分区概述

将一个表或者索引分解为多个更小、更可管理的部分；逻辑上只有一个表物理上由多个物理分区组成，每个分区都是独立的对象，可以独自处理也可以作为更大对象的一部分来处理

分区分为水平（按记录行）和垂直分区（按列），MySQL只支持水平分区

同时分为局部分区和全局分区，局部就是把数据和索引都进行独立的存放，全局值数据进行分区存放但是索引在一个对象中进行管理，MySQL只支持局部分区

当一个数据插入时，如果分区未定义（不再已定义的分区中），InnoDB会抛出异常，且如果批量插入中有一个这样的异常数据，所有数据都不会被插入（看成一个事务）

分区字段必须是表结构中唯一性索引的一部分（所有包含唯一性约束的索引的一部分即可，比如联合唯一索引的一个字段），如果表中没有唯一性约束，那就可以用任意字段进行分区

key分区的时候，需要将分区数量设置为质数（3、7、11、13、17），不然会出现数据不能均匀分布的情况，==且不知道为什么只有将分区key作为主键组成部分才分区成功了==

分区类型

MySQL支持以下几种分区：

range：将给定列连续的按区间进行分区
list：对比range分区面向的是离散的值
hash：根据用户自定义的表达式返回值来进行分区
key：根据MySQL数据库提供的哈希函数来进行分区

range

最常见的一种分区方式，比如通过id进行分区的时候，会将数据按指定的区间划分在磁盘上由独立的ibd文件进行存储（正常就是一个idb文件）

partition by range(id)(
    partition p0 values less than (10),
    partition p1 values less than (20)
)

优化场景：

通过id分区，可以提高指定id查询的速度
通过时间进行分区，比如销售表，这个时候如果要进行年度数据的统计速度会得到提升，当然查询的时候也要根据分区的逻辑进行SQL编写

list

和range很接近，只是分区值是离散的

partition by range(id)(
    partition p0 values in (1,3,5,7,9),
    partition p1 values in (0,2,4,6,8)
)

hash

目的是将数据均匀的分不到预先定义的各个分区中,两种hash分区方式，linear在增加、删除、合并和拆分上更快捷，缺点是数据分布不是特别均衡

<!--hash分区-->
partition by hash (year(b)) partitions 4;
<!--linear hash分区-->
partition by linear hash (year(b)) partitions 4;

key

和hash差不多，区别是hash可以指定函数进行分区，key使用MySQL提供的函数进行分区，同样有linear关键字

1	partition by key (b) partitions 4;

columns

这个东西是MySQL5.5之后开始支持的，上面说的4中分区方式都有一个前提就是分区字段类型要是整型，如果不是需要通过函数转换为整型，而columns可以看做是range和list的进化，可以直接使用非整型数据进行分区

子分区

在分区基础上再进行分区，也称为符合分区；MySQL允许在range和list的分区上再进行hash或key的子分区

<!--这样会创建3*2 共计6个分区-->
partition by range(year(b))
subpartition by hash (to_days()b)
subpartitions 2(
    partition p0 values less than (1990),
    partition p1 values less than (2000),
    partition p2 values less than maxvalue
)

子分区还可以显式的对名字进行指定，这个时候必须每个子分区都列出来，并且名字不能重复
子分区可以用于特别大的表，在多个磁盘建分配分配数据和索引

分区中的null值

MySQL允许对null做分区，策略上MySQL会把null看成一个比所有非null都小的值，于是不同的分区策略下null的处理也不同

range中，会把null放到最左边的分区
list分区下要使用null的话必须显式的指定哪个分区中放入null
hash和key中函数会把含有null值的记录返回为0

分区的性能

比如我们认为1000w数据的索引树高度为3，100w数据高度为2，通过主键hash分为10个分区，这个时候通过主键进行查询可以节省一次IO，但是如果是对别的索引进行查询的时候，会对10个子索引进行查询，也就是20次IO，速度反而变慢了，所以设计的时候需要结合场景

在表和分区间交换数据

创建两个一样的表，一个定义分区，一个不定义分区，这个时候可以通过alter table ... exchange partition将数据进行交换，没有想出应用场景，这里也没有介绍

表