本章是mysql技术内幕这本书的读书笔记。可以点击超链接查看所有读书笔记。

1. 章节介绍

本章主要讨论InnoDB存储引擎表的逻辑存储以及实现。重点分析表的物理存储特征，即数据在表中是如何组织和存放的。表示关于特定实体的数据集合，也是关系型数据模型的核心。

2. 索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的。没显示定义主键，InnoDB存储引擎也会按照如下方式选择或者创建主键：

  1. 用非空唯一索引的列作为主键
  2. 自动创建一个6字节大小的指针

查看表的单列主键值可以使用以下语句(关键是使用_rowid) 

select _rowid from xxx

3. InnoDB逻辑存储结构

所有数据都被逻辑地存放在表空间。表空间由段、区、页组成。大致如下

3.1 表空间

默认InnoDB有默认的共享表空间ibdata1。如果启用了innodb_file_per_table才会一张表一个表空间。回滚信息、插入缓冲索引页、索引和插入缓冲bitmap页、系统事务信息、二次写缓冲等还是放在原来的共享表空间中。

3.2 段

常见的段分为：

  1. 数据段：B+树上的叶子节点
  2. 索引段：B+树上的非索引节点
  3. 回滚段：较为特殊 后面章节说

InnoDB会自动管理段，类似Oracle的自动段空间管理（ASSM）

3.3 区

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

3.4 页

3.4.1 压缩页

InnoDB 1.0.x之后版本引入了压缩页。每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K，因此每个区对应的数量也就为512,256,128。

3.4.2 默认页大小

InnoDB 1.2.x中通过设置innodb_page_size可以将默认的页设置为4K和8K。这里改变页大小并不进行压缩，请区别于压缩页

3.4.3 碎片页与存储优化

每个段在开始时，先用32个页大小的碎片页来存放数据，用完之后才是64个连续页的申请。这样做的目的是，对于一些小表，或者是UNDO这类的段可以在开始时申请较少的空间，节省磁盘容量的开销。

3.4.4 常见的页类型

  1. 数据页（B-tree Node）
  2. undo页（undo log page）
  3. 系统页（system page）
  4. 事务数据页（transaction system page）
  5. 插入缓冲位图页（insert buffer bitmap）
  6. 插入缓冲空闲列表页（inset buffer free list）
  7. 未压缩的二进制大对象页（uncompressed blob page）
  8. 压缩的二进制大对象页（compressed blob page）

3.5 行

每个页存放若干行。innodb是面向行来存储的存储引擎。每个页最多允许存放7992行记录。

4. InnoDB行记录格式

mysql中若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么这个表其实也叫动态表，即该表的 row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。反之，这张表叫静态表，该表 row_format为fixed,即每条记录占用字节一样。优点读取快，缺点浪费部分空间

所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。

row_format还有其他一些值：

  1. DEFAULT
  2. FIXED
  3. DYNAMIC
  4. COMPRESSED
  5. REDUNDANT
  6. COMPACT

修改行格式

ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改过程导致：

fixed--->dynamic: 这会导致CHAR变成VARCHAR

dynamic--->fixed: 这会导致VARCHAR变成CHAR
 InnoDB主要提供了Compact和Redundant两种格式来存放行记录数据。

使用 

#查看行格式
show table status like 'tableName';

4.1 Compact和Redundant行记录格式

该格式目标是为了高效存储数据：

行记录格式：常见两种行记录格式Compact和Redundant，mysql5.1版本后，主要是Compact行记录格式。对于Compact，不管是char型还是varchar型，null型都是不占用存储空间的；对于Redudant,varchar的null不占用空间，char的null型是占用存储空间的。

4.2 行溢出数据

innodb存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。BLOB、LOB这类大对象列类型的存储“可能”（取决于是否超过一个大小阈值）会把数据存放在数据页面之外。虽然varchar说是支持65535字节，但是实际上大量数据最后还是自动被处理保存在BLOB页当中，而不是数据页中了。这种就称为行溢出数据

无论是BLOB和是varchar，当数据量小的时候会先存在数据页，当数据超过某个阈值（BLOB大概700度字节，VARCHAR大概8000多字节），则数据存放到BLOB页当中。

对于Compact和Redundant行记录格式来说，行溢出的阈值为768个前缀字节。而对于dynamic和compressed来说则是20个字节的字节指针。

4.3 dynamic和compressed

之前的compact和redundant格式一起称为Antelope文件格式。现在的新行记录格式compressed和dynamic称为Barracuda文件格式。

这两种新的记录格式，采用完全的行溢出方式。如下图所示：

当行格式为DYNAMIC或COMPRESSED时，TEXT/BLOB之类的长列（long column，也有可能是其他较长的列，不一定只有TEXT/BLOB类型，看具体情况）会完全存储在一个独立的data page里，聚集索引页中只使用20字节的指针指向新的page，这就是所谓的off-page，类似ORACLE的行迁移，磁盘空间浪费较严重，且I/O性能也较差。因此，强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型；

关于行记录格式可以参考下MySQL优化案例系列 — 优化InnoDB表BLOB列的存储效率。这个人关于MYSQL的博客能还是很不错的！

4.4 char的行结构存储

char和varchar的实际行存储基本是没有区别的。

5. innodb数据页存储

页是innodb存储引擎管理数据库的最小磁盘单位。

其基本数据页结构如下图：

5.1 file header

file header用来“记录页的头部信息”，总共占用38字节，主要由以下8个部分组成。
页类型

5.2 page header

这才是真正的页头，记录数据页的状态信息。注意区别file header

5.3 infimum和supremum record

在innodb存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。这两个值在页创建时被建立，并且在任何情况下不会被删除。

5.4 user record和free space

user record即实际存储行记录的内容。在innodb当中都是以B+树索引组织的。

free space是一个链表结构，用来指名空间空间。

5.4 page directory

页目录存放了记录的相对位置，不是偏移量。这些记录指针称为槽（Slots）或目录槽（directory slots）。一个槽中可以包含多个记录。在innodb存储引擎中page directory是稀疏目录，二叉查找的结果只是一个粗略的结果。存储引擎必须通过recorder header中的next record来继续查找相关记录。

PS：
 需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过page directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找速度很快，因此通常忽略这部分查找所用的时间。

5.5 file trailer

file trailer 只有FIL_PAGE_SPACE_OR_CHKSUM一个部分。该部分8字节，前4字节是校验和，后4字节是和File header中fil_page_LSN对应。通过比较file trailer和file header中的这两对值，来检验页的完整性。

innodb_checksums_algorithm可以用来指定校验算法，建议使用strict_crc32

6. named file formats机制

该机制用于解决不同版本下页结构兼容性的问题。实际上就是新文件格式支持老文件格式。如下图所示: antelope就是之前说的 Compact和Redundant；barracuda就是dynamic和compressed。每个新格式的命名都按照动物来命名，首字母按照字母排序。下一个动物是Cheetah、Dragon等。

7. 约束和视图

约束是为了保证数据完整性。常见的有唯一性、主键、外键约束等。添加约束可以在建表或者使用命令来操作。这部分比较简单，这里不再赘述。

7.1 虚表和物化视图

虚表即实际上是不存在的表（每次重新查询计算，获得虚表）。而物化视图则是实际存在的实表。可见如果查询代价小，可以用虚表。如果存在JOIN和GROUP BY等耗时多的，就要使用物化视图。

物化视图有几种刷新模式，指定当基表发生DML操作后，物化视图何时采用哪种方式和基本表进行同步。

刷新的模式有：

  1. ON DEMAND: 在用户需要的时候进行刷新
  2. ON COMMIT： 物化视图在对基表的DML操作提交的同时进行刷新

刷新的方法有：

  1. FAST： 增量刷新
  2. COMPLETE：对整个物化视图进行完全的刷新
  3. FORCE：数据库在刷新时会去判断是否可以进行快速刷新，如果可以采用FAST，否则使用COMPLETE
  4. NEVER：物化视图不进行任何刷新

8. 分区表

8.1 分区概述

水平分区：指将同一个表中的不同行的记录分配到不同物理文件中。
 垂直分区：指将同一个表中的不同列的记录分配到不同的物理文件中。

MYSQL不支持垂直分区。这个我已经在mysql官方文档中进行验证，即使是较新的MYSQL5.7也是不支持垂直分区的。

随着分布式技术发展和数据规模增长，分表分库是很常见的方案。因此本节我们也要重点学习下。

MYSQL数据库的分区是局部分区索引。这意味着数据和索引必须同时进行分区，而不能只能数据分区，而对索引不分区。

MYSQL支持以下几种类型的分区：

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

PS：不论创建何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的组成部分。如果没有主键或者唯一索引，那么可以随便选择一个列为分区列。

8.2 RANGE分区

最常用的一种分区类型。

例子：当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 (20)
);

PS: 如果插入大于20的值会抛出错误，要表示无穷大的值可以使用 values less than maxvalue

查看数据文件，会发现如下所示：命名规则——表名#P#分区名

查看某张表的分区情况可以查询，information_schema数据库下的PARTITIONS表。
 PS： information_schema这个数据库十分重要，注意好好利用。

查询某张表的分区情况 

select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'
\G;

RANGE分区比较适合用于日期列的分区，例如对于销售类的表，可以根据年来分区存放销售记录。这样的好处是：

  1. 删除某年的数据很方便，直接删除分区即可。alter tables sales drop patition p2008
  2. 加快某年查询的速度。通过先输入explain partitions命令，可以查看该SQL查询了哪些分区。

注意：优化器只能对YEAR()、TO_DAYS()、TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。

8.3 LIST分区

LIST分区就是通过自己定义一系列具体的LIST值来完成分区。例子如下： 

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)
 )
);

8.4 HASH分区

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

例子： 

create table t_hash(
    a int,
    b datetime
)engine=innodb
partition by hash(year(b))
partiton 4;

此外MYSQL还支持一中LINEAR HASH方法来进行哈希分区。这个算法只要把上面的HASH关键字改成linear hash(year(b))即可。这个算法比之前的复杂很多，优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有大量数据的表。缺点在于，与使用HASH分区得到的数据分布相比，各个分区数据的分布可能不打均衡。

8.5 KEY分区

KEY分区和HASH分区比较类似。区别在于HASH分区使用用户定义的函数进行分区，KEY分区使用MYSQL数据库提供的函数进行分区。对于INNODB，MYSQL使用其内部的哈希函数，这些函数基于与PASSWORD()一样的运算法则。分区的编号通过2的幂算法得到。 

create table t_key(
    a int, 
    b datetime
    )engine=innodb
    
    partition by key(b)
    partitons 4;

8.6 COLUMNS分区（range columns和list columns）

前面的几种分区方法，都有条件：数据必须是整型，如果不是整型，那应该需要通过函数将其转化为整型，如YEAR(),TO_DAYS()等。

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

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

range columns和list columns例子：

最佳实践：建议用range columns和list columns代替range和list

8.7 子分区

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

例子：


文件存储情况：


显示指定子分区的名字：

建立子分区需要的注意点：

8.8 分区中NULL值

MYSQL数据库允许对NULL值做分区。NULL被人为小于任何非NULL值。

不同分区类型对NULL值处理

  1. RANGE：插入NULL，放入最左侧分区
  2. LIST：必须显式地指出哪个分区中放入NULL值
  3. HASH和KEY：任何分区函数都会将含有NULL值的记录返回为0.

8.9 分区和性能

分区是否提高性能要看情况。

  1. OLAP应用：OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户查询获取一年的数据，如果采用时间戳分区，就只需要扫描一个分区即可。
  2. OLTP应用：通常不可能会获取一张大表10%的数据，大部分情况都是通过索引返回几条数据即可。一般B+树索引对于一张大表，只需要进行2到3次IO。因此B+树可以很好地完成操作，不需要分区的帮助。并且设计不好的分区会带来严重性能问题。

例子：可见分区的时候，要对分区带来的两方面影响进行考量：

  1. 分区之后B+树平均降低的高度（平均I/O变化，avg-IO）
  2. 分区后带来的总共I/O次数变化（total-IO）

当avg-IO*分区数<total-IO，那么分区后才会带来改善I/O的优势。

当然我们这里不讨论分布式的情况。分布式的分区，其实是并发I/O，性能一般都会因为分区后更好。

8.10 在表和分区间交换数据

这个主要是依靠alter table tableName exchange partition语法。

使用该语法需要满足以下条件：

例子：

9.总结