MySql技术内幕 - 表
一、索引组织表
在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存放方式的表称为索引组织表，在InnoDB中每张表都有一个主键
如果在创建表的时候没有显示的定义主键，InnoDB会按照下面的方式选择或创建主键：
- 首先判断表中是否存在非空的唯一索引，如果有，则该列即为表的主键
- 如果不符合第一个条件，会自动创建一个6个字节大小的指针
当表中存在多个非空的唯一索引时，选择第一个满足条件的列，作为表的主键。
我们可以通过 select *,_rowid from tablename 来查看一个表的主键，但是这种方式只适用于单列为主键的情况，如果主键是多列的，就不再适用。
二、InnoDB逻辑存储结构
从InnoDB存储引擎的逻辑存储结构来看，所有的数据都被逻辑的存储在一个空间中，称之为表空间。
表空间又由段（Segment）, 区（extent）, 页（page）组成，页在很多文档中也被称为块（block）
- 段（Segment）
首先表空间是由很多段组成的，常见段有数据段，索引段，回滚段等。前面已经说过，InnoDB存储引擎表是索引组织的，因此数据即索引
索引即数据。那么在数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。
- 区（Extent）
区是由很多个页组成的，在任何情况下每个区的大小都为1MB，为了保证区中的页是连续的，InnoDB存储引擎一次从磁盘申请4～5个区，在默认情况下
InnoDB存储引擎页的大小为16K，即一个区中存在64个连续的页
- 页（Page）
InnoDB中的页有时也称为块，页是InnoDB磁盘管理的最小单位，在InnoDB存储引擎中，默认每个页的大小为16K
但是从InnoDB 1.2x版本开始，可以通过参数：innodb_page_size来设置每个页的大小
三、InnoDB行记录格式
InnoDB中的数据是按行进行存储的，这也就是说在每个页中保存着每一行记录，在InnoDB中有两种格式来存放行数据：Compact 和 Redundant
而Redundant是为了兼容之前的版本而保留的，在5.1版本开始默认的row_format是Compact
可以通过：show table status like 'tablename' 来查看表的保存格式
- Compact格式
Compact是在MySql 5.0版本的时候引入的，其设计的目的是高效的存储数据，简单的说就是在一个页中存放的行越多，其性能也就越好
这里就不详细记录细节了，需要的可以自行查阅相关书籍，这里就是列举一些我们平时常用的原理：
1.VARCHAR最大长度的限制是65535字节：这是因为在存储每一行的数据的时候，是先标记当前行中的变长字段长度的列表，然后约定是变长字段最多用两个字节来存储
这样也就是16 bit ，转换成十进制也就是65535 。但是当我们在MySql中创建65535大小的变长列时，一定会报错。这是因为还有别的开销，实测最大可创建65532。
而且字符集只能是latin1的。如果我们使用了UTF-8或者GBK，会提示我们超过最大限制。
所以这里我们应该理解为VARCHAR(N) 中的N指的是字符的长度，而我们上面说的65535是字节。
还有一个要注意的是MySql官方文档中有说明，65535指的是所有VARCHAR列的长度总和，如果总和超过了这个数值，还是会报错的。
- Redundant格式
这个格式是MySql 5.0版本之前InnoDB记录的存储方式
1.一行数据最多支持1023个列：因为在头部信息中存在一个n_fields值，是10 bit 的大小，所以就是最多支持1023个列。
上面提到了每行记录变长字段之和最长为65535个字节，但是大家有没有想到，InnoDB 存储引擎的页的大小为16K，也就是16384个字节，那怎么能存储65535个字节的呢？
所以这里就要说一种情况，就是行溢出
一般情况下InnoDB存储引擎会将数据存放在页类型为B-tree node中，但是如果一行记录不能完成存储在数据页中，发生行溢出时，数据存放在页类型为Uncompress BLOB 中
所以当我们一行记录的大小超过了一个页的限制时，那么溢出的部分是需要保存在其他地方的，非当前页。然后在当前页保存溢出部分的指针，指向实际保存的地方。
这里实际上在数据页中只会保留前多少字节，其他的部分都在其他地方存储。
所以说到这里大家有没有联想到，为什么我们使用索引查询数据的时候最快，为什么我们不提倡使用select * 这种方式？就是因为索引的数据一定都是在数据页里面的，因为InnoDB
是一个索引组织表，这样可以保证一次寻址直接返回，如果我们查询了不在数据页上的数据的时候，那就不得不进行再次寻址去寻找我们需要的字段来提供返回结果。
还有一个问题我们要考虑，在一个数据页中应该最少保留几行记录？
至少应该是两条把，如果再少的话就变成链表了，就失去了B+tree的意义了，所以如果页中只能存放一行记录，那么引擎会自动行数据保存在溢出页中，而不是存储在数据页中
在InnoDB 1.0x版本开始，引入了新的文件格式，或者我们可以理解为新的页格式。
之前的Compact 和 Redundant 格式统称为Antelope文件格式，新的文件格式称为Braarcuda。Barracuda文件格式拥有两种新的行记录格式：Compressed 和 Dynamic
新的两种文件格式对于存放在BLOB中的数据采用了完全的行溢出方式，在新的数据页中只存放20字节的指针，实际的数据都存放在Off Page中，而之前的Compact 和 Redundant
则会存放前768个前缀字节，这样做的好处就是在一个数据页中可以保存更多的行记录，整个B+tree的高度会降低，但是另外一个缺点就是每次查询结果可能需要两次寻址。
四、InnoDB数据页结构
每一个数据页都是由以下7个部分组成：
- File Header
- Page Header
- Infimun 和 Supermun Records
- User Records
- Free Records
- Page Directory
- File Trailer
Infimun记录是比该页中任何主键值都小的值，Supermun是比任何可能大的值还大的值，这两个值在页创建的时候被建立，并且在任何时候不会被删除
B+Tree索引其实本身并不能找到具体的一条记录，只能找到这条记录所在的页，然后数据库把页读取到内存中，然后再通过Page Directory再进行二叉查找，只不过这个时间非常短。
这里的File Trailer部分要特别介绍一下，在这个部分只有一个FIL_PAGE_END_LSN，占用了8个字节。前4个字节代表该页的checksum值，最后4字节和File Header中的FIL_PAGE_LSN相同
将这两个值与File Header中的FIL_PAGE_SPACE_OR_CHKSUM 和 FIL_PAGE_LSN 值进行比较看是否一致，通过这种方式保证数据页的完整性。
五、分区表
MySql 的分区功能不是在存储引擎层完成的，因此不是只有InnoDB存储引擎才支持分区
MySql 支持的分区类型为水平分区，并不支持垂直分区。此外MySql 的分区是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指，数据存放
在各个分区中，但是所有数据的索引存放在一个对象中，目前MySql 还不支持全局分区。
可以通过查看参数：have_partitioning 来查看当前数据库是否开启了分区功能
其实大多数人都有一个误区，就是只要使用了分区，就一定能给某些SQL语句带来性能上的提升，但是其实分区主要是用于数据库高可用性管理的。
当前MySql中支持一下几种类型的分区：
- Range分区：行数据基于属于一个给定的连续区间的列值被放入分区
- List分区：和Range类似，只是List分区是面向离散值的
- Hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能是负数
- Key分区：根据MySql 数据库提供的哈希函数来进行分区
不论创建何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。
唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列
如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列，因为这个时候表的主键是有系统自动生成的，不会出现重复的情况
- Range分区
定义了分区规则，就会严格遵守，如果我们插入了一个不在分区定义中存在的值，这时MySql 会抛异常，所以一般情况我们会在创建分区的最后添加一个maxvalue的分区
maxvalue可以理解为正无穷大 ：partition p2 values less than maxvalue
其实在日常使用中，Range分区别主要是用于日期列的分区，可以根据年份来进行存储数据
还有一个需要注意的是，我们在分区条件中，使用函数不要用常数组合的方式，比如YEAR(b) + 100 + MONTH(C) 这种形式，如果通过这种形式定义了分区
那么在查询的时候是会扫描所有分区的，因为MySql 对于Range分区的查询，优化器只能对YEAR() , TO_DAYS() , TO_SECONDS() , UNIX_TIMESTAMP() 这类函数进行优化处理
- List分区
List分区与Range非常相似，只不过这个是分散的值，非连续的
还有就是List分区在不同存储引擎下的表现也是不一样的，如果我们批量插入数据，其中存在一条记录不在任何分区中，那么对于InnoDB存储引擎来说，会将整个插入看作
一个事务来处理，就是一条记录都不会插入。
- Hash分区
Hash分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的，在Range和List分区中需要我们指定一个列值或者集合来进行分区
但是在Hash分区中，MySql是自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或者是表达式，以及指定被分区的表将要被分割成的数量
如果不指定需要分区的数量，那么默认值就是1个
这里还提供了一个加强版的Hash分区，LINEAR HASH，与Hash分区的功能一样，只是算法更加的复杂，优点在于增加，删除，合并和拆分分区更加的方便
这有利于处理数据量非常大的表，缺点在与可能数据分区的没有Hash的那么均衡
- Key分区
Key分区与Hash分区类似，不同指出在与Hash需要使用用户定义的函数进行分区，Key分区使用MySql数据库提供的函数进行分区
同样Key分区也支持LINEAR KEY的方式，效果与Hash的相同
- Columns分区
前面介绍了四种分区，但是分区的条件是必须是整型，如果不是整型，那因该需要通过函数将其转化成整型
在MySql 5.5版本开始支持Columns分区，可以看作是Range 和 List 分区的一种进化
Columns分区可以直接使用非整型数据进行分区，分区根据类型比较直接得出，不需要转化成整型
使用方式就是Range Columns 和 List Columns，只是关键字的不同。
子分区：
子分区就是在分区的基础上再进行分区，有时也称这种分区为复合分区，MySql允许在Range 和 List 的分区基础上再进行Hash 或者Key 的自分区
建立子分区有以下几个问题要注意：
- 每个子分区的数量必须相同
- 要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有子分区
- 每个SUBPARTITION子句必须包括子分区的一个名字
- 子分区的名字必须是唯一的
分区中的Null值：
MySql 数据库允许对Null值进行分区，但是处理方法与其他数据库有所不同，MySql 数据库的分区总是视Null 值小于任何一个非Null值
对于Range分区，如果向其中插入了Null值的记录，则会被放入到最左侧的分区中，同样如果我们删除最左侧的分区，也一同删除了Null值的记录
List 分区如果存在Null 值必须显示的指定哪个分区要放入，否则会报错
Hash 和 Key 分区对于Null值的处理方式和Range , List 的分区不一样。任何分区函数都会将含有Null值的记录返回0
分区的性能：
其实分区的使用是非常依赖场景的，不是说一张很大的表使用了分区之后，SQL的查询性能就一定会变快
因为一张表不管是100W 还是 1000W，其对应的B+tree其实相差不会很多，最多可能就差1层而已
那么如果我们使用了分区表，原本的查询只需要2～3次IO，然后直接寻址找到对应的数据页，但是因为我们进行了分区，在数据分散的情况下
我们需要扫描更多的分区来返回结果，那么每个分区都需要进行2～3次IO，那么加起来可能就会需要20～30次的IO，所以整体的查询性能可能不一定会更快
分区交换数据：
从MySql 5.6开始支持alter table ... exchange partition 语法。该语法允许分区或子分区中的数据与另一个非分区的表中的数据进行交换
如果非分区表中的数据为空，那么相当于将分区中国的数据转移到非分区表中，若分区表中的数据为空，则相当于将外部表中的数据导入到分区中
要使用分区数据交换，需要满足如下几个条件：
- 要交换的表需和分区表有着相同的表结构，但是表中不能含有分区
- 在非分区表中的数据必须在交换的分区定义内
- 被交换的表中不能含有外键，或者其他的表中含有对该表的外键饮用
- 用户除了需要alter insert 和 create 权限之外，还需要drop的权限
- 使用该语句时，不会触发交换表和被交换表中的触发器
- auto_increment 将会被重置
浙公网安备 33010602011771号