1. 索引组织表

​ 在InnoDB存储引擎中,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表.

​ 在InnoDB存储引擎中,每张表都有个主键,如果在创建表的时候没有显式的定义出主键会按照下面的方式选择或者创建一个主键字段:

• 如果有非空的唯一索引,选择这个字段作为主键字段(如果有多个,按照定义索引的顺序选择第一个字段)
• 如果不符合第一条,自动创建一个6字节大小的指针.

2. InnoDB逻辑存储结构

​ 从InnoDB存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间.表空间又由段(segment),区(extent),页(page,默认为16K)组成.

(1). 表空间

​ 表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中.如果用户启用了参数innodb_file_per_table,则每张表都可以有一个独立的表空间.

​ 但要注意的是,每张表的独立表空间只存放数据,索引和插入缓冲Bitmap页,其他类的数据(undo回滚信息,插入缓冲索引页,系统事务信息,二次写缓冲等等)还是存放在共享表空间的.那么意味着,就算开启了innodb_file_per_table参数,共享表空间还是会不断的增大.

​ 在共享表空间中的空间如果失效(如被覆盖掉的undo信息),并不会立即回收这些内存,而是标记为可用空间,供下次使用.

(2). 段

​ 表空间是又一个一个段组成的,常见的段有数据段,索引段,回滚段等等.

​ InnoDB存储引擎是索引组织的,因此数据即索引,索引即数据.那么数据段即为B+树的叶子节点,索引段即为B+数的非叶子节点.回滚段较为特殊,暂不介绍.

​ 才InnoDB存储引擎中,对段的管理都是由引擎自身所完成.

(3). 区

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

​ 但是这64个连续的页并不是在申请完区空间后就得到的.InnoDB存储引擎在每一个区开始使用时会使用32个页大小的碎片页来存放数据,这些页使用完时候才会一次性申请64个连续页.这样的目的是对于一些小表,或者undo这类的段,可以在开始时申请较少的空间,节省磁盘容量的开销.

(4). 页

​ 页是InnoDB磁盘管理的最小单位,默认每个页的大小为16KB.从InnoDB 1.2.x版本开始,可以通过innodb_page_size将页的大小设置为4K,8K,16K.

常见的页类型有:

• 数据页(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)

(5). 行

​ InnoDB存储引擎是面向列的 ,也就是说数据是按行进行存放的.每个页存放的行记录也是有硬性定义的,最多允许存放7992行数据(16KB/2-200).

3. InnoDB行记录格式

​ InnoDB存储引擎和大多数数据库一样,记录是以行的形式存储的.在InnoDB 1.0.x版本之前,InnoDB存储引擎提供了Compact和Redundant两中格式来存放行记录数据.在MySQL 5.1版本中,默认设置为Compact版本.

(1). Compact行记录格式

​ Compact行记录实在MySQL 5.0中引入的,其设计目的是高效的存储数据.一个页中存放的行数据越多,其性能就越高.

​ Compact行记录格式由变长字段长度列表,NULL标志位,记录头信息,列数据1,列数据2.....组成.

​ 其中变长字段长度列表是按照列的逆序排列的,有若干个1或者2字节的二进制数组成(1字节代表225的长度,2字节则为65535).变长字段长度列表之后的第二个部分是NULL标志为,改为只是了该行数据是否有NULL值,以二进制的形式展示.接下来是记录头信息,固定站5字节,含义见下表:

​ 最后的部分就是存储每个列的数据,NULL不占该部分的任何空间.

​ 每行数据除了用户定义的列外,还有两个隐藏列,事务ID列(6字节)和回滚指针列(7字节).如果InnoDB表没有定义主键,每行还会增加一个rowid列.

(2). Redundant行记录格式

​ Redundant是MySQL 5.0之前InnoDB的行记录存储方式,不同于Compact方式,首部是一个字段长度偏移列表,同样是逆序,然后是记录头信息,紧跟着列数据1,列数据2......

(3). 行溢出数据

​ InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页之外,一般为BLOB,LOB这种大对象列类型会这样存储.但是需要注意的是,BLOB也可以不将数据放在溢出页面,VARCHAR也可能会存在溢出页面之中.

​ VARCHAR类型最大支持65535字节,所以对于不同的编码格式,存储的数据长度是不一致的.此外这个长度是对于一行中的所有列VARCHAR总长度和,如果这个总长度超出了65535字节,就发生了溢出,会将数据存放在Uncompress BLOB页中.

​ InnoDB存储引擎表是索引组织的,即B+Tree的结构,这样每个页中至少要存放两条数据才有意义,如果一行的大小不允许同一页中在存放下一行数据,那么这一行数据就会被存放入溢出页中.

​ 对于TEXT和BLOB类型的数据,与VARCHAR一样.

​ 大多数情况下,BLOB类型的数据都很大,都会溢出的,数据数据都是保存在BLOB页中的,数据页只保存前768字节.

(4). Compressed和Dynamic行记录格式

​ InnoDB 1.0.x版本开始引入了新的文件格式,成为Barracuda文件格式,有两种行记录格式:Compressed和Dynamic.这两种新的记录格式对于存放BLOB中的数据采用了完全的行溢出方式.

​ 除此之外,Compressed行记录格式的另一个功能是存储在其中的行数据会以zlib的算法进行压缩.

(5). CHAR的行结构存储

​ 从MySQL 4.1版本开始,CHR(N)中的N指的是字符的长度,而不是存储数据所占字节长度.也就是说不同的字符集下,CHAR类型存储的不是定长数据.

4. InnoDB数据页结构

​ 页是InnoDB存储引擎管理数据库的磁盘最小单位,InnoDB数据页由一下7部分组成:

• File Header(文件头)
• Page Header(页头)
• Infimun和Supermum Records
• User Records(行记录)
• Free Space(空闲空间)
• Page Directory(页目录)
• File Trailer(文件结尾信息)

(1). File Header

​ File Header用来记录页的一些头信息,由以下8部分组成共38字节.

File Header组成部分:

InnoDB存储引擎中页的类型:

(2). Page Header

​ 该部分用来记录数据页的状态信息.由14部分组成,共56字节.

(3). Infimum和Supremum Record

​ 每个数据页中有两个虚拟的行记录,用来限定记录的边界.Infimum用来记录比页中任何主键值都要小的值,Supermum指比任何可能大的值还要大的值.这两个值在页创立时建立,任何情况下不会被删除.

(4). User Record和Free Space

​ User Record指数据存储行记录的内容.

​ Free Space值空闲空间,也是个链表数据结构.一条记录被删除后会被加入到空闲链表中.

(5). Page Directory

​ 页目录,其中存放了记录的==相对==位置,有时候这些记录指针被称为Slots(槽).在InnoDB中并不是每个记录拥有一个槽,InnoDB存储引擎的槽是一个稀疏目录,一个槽中可能包含多个记录.

(6). File Trailer

​ 为了检测页是否已经完整的写入磁盘,设置了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进行比较,依次来确保页的完整性.

​ 默认情况下,每次从磁盘读取一个页就会检查依次该页的完整性,就是通过检查File Trailer部分进行检测.

5. Named File Formats机制

​ 目的是解决不同版本下页结构兼容性问题.

​ InnoDB存储引擎不同版本之间新的文件格式总是包含于之前版本的页格式.

6. 约束

(1). 数据完整性

​ 关系型数据库和文件系统的一个不同点就是关系数据库本身能保证数据的完整性,不需要应用程序的空值,而文件系统一般需要在程序端进行控制.

数据完整性有一下三种形式:

1. 实体完整性保证表中有一个主键
2. 域完整性保证每列数据的值满足特定的条件,实现:
   1. 选择合适的数据类型
   2. 外键约束
   3. 触发器
   4. default约束(默认值)
3. 参照完整性保证两张表之间的关系(外键,触发器)

(2). 约束的创建和查找

约束的创建可以使用一下两种方式:

1. 建立表时进行约束的定义
2. 利用alter table命令创建约束

对于Unique Key(唯一索引)还能通过create unique index命令创建.

create table 库名.表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
);

// 常见约束
primary key (PK)      #标识该字段为该表的主键，可以唯一的标识记录，主键就是不为空且唯一当然其还有加速查询的作用
foreign key (FK)      #标识该字段为该表的外键，用来建立表与表的关联关系
not null              #标识该字段不能为空
unique key (UK)       #标识该字段的值是唯一的
auto_increment        #标识该字段的值自动增长（整数类型，而且为主键）
default               #为该字段设置默认值

unsigned              #将整型设置为无符号即正数
zerofill              #不够使用0进行填充

(3). 约束和索引的区别

​ 当用户创建了一个唯一索引就创建了一个唯一的约束.

​ 约束是一个逻辑的概念,用来保护数据的完整性,而索引是一个数据结构,既有逻辑上的概念,在数据库中还代表着物理存储的方式.

(4). 对错误数据的约束

​ MySQL数据库允许非法的或者不正确的数据插入或更新,又或者可以在数据库内部将其转化为一个合法的值,数据库本身没有对数据的正确性进行约束.

(5). ENUM和SET约束

​ MySQL数据库不支持传统的CHECK约束,但是通过ENUM(枚举类型,单选)和SET(集合类型,多选,求和)类型可以解决部分这样的约束要求.但对于连续值的万为约束或更复杂的约束,需要使用触发器来实现.

(6). 触发器与约束

​ 触发器的作用是在执行insert,delete或者update命令之前或者之后自动调用SQL命令或存储过程.

​ 创建触发器的命令如下,只有Super权限的MySQL用户才可以执行这条命令:

create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
    执行语句
end;

​ 最多可以为一张表建立6个触发器,即inser,delete和update三种操作各一个before和after触发器.

​ MySQL只支持for each row的触发器,即按每行记录进行出发.

(7). 外键约束

​ 外键用来保证参照完整性,InnoDB存储引擎完整支持外键约束.

​ 一般来说,被引用的表被称为父表,引用表称为子表.外键定义时的on delete和on update表示在对父表进行delete和update操作时,对子表所进行的操作.

foreign key 
子表名(子表字段) references 父表名(父表字段)
[on delete restrict|cascade|set null|no action]
[on update restrict|cascade|set null|no action]

• cascade:对子表进行同步
• set null:设置子表为null(子表中的字段不能NOT NULL)
• no action:不允许父表的此操作,抛出异常
• restrict:同上

​ MySQL中最后的两条是一样的.

​ InnoDB存储引擎会在外键建立的同时自动为外键列加上一个索引,可以很好的避免死锁问题.

7. 视图

​ MySQL数据库中,视图是一个命名的虚表,由一个SQL查询来定义,可以当做表使用,但没有物理存储.

(1). 视图的作用

create view 视图名 [视图列名]
as 查询语句
[with [cascaded|local] check option]

​ 视图的主要用途之一就是被用作一个抽象装置,程序本身不需要关心基表的结构,只需要按照视图定义来取数据或者更新数据.同时起到一个安全层的作用.

​ 用户对某些视图的更新操作,其本质就是通过的视图的定义来更新基本表.视图定义中的with check option就是针对于可更新视图的更新检查.加上该选项,MySQL数据库会对更新视图插入的数据进行检查,对于不满足==视图定义条件(也就是查询语句中的条件查询)==的插入,会抛出一个异常.不允许数据更新.

(2). 物化视图

​ 物化视图是指该视图不是基于表的虚表,而是根据基表实际存在的实体表.可用于预先计算并保存多表的链接,聚集等耗时的SQL操作结果.

物化视图的刷新模式:

• on demand:需要时刷新
• on commit:实时刷新

刷新的方法:

• fast:局部刷新
• complete:全局刷新
• force:通过判断选择前两种
• never:不进行刷新

​ MySQL数据库不支持物化视图,不过可以使用其他方式实现.on demand的物化视图可以通过定时吧数据转入另一张表来完成,其中不需要定义语法上的视图.如果要实现on commit的物化视图,需要使用触发器.

8. 分区表

(1). 概述

​ 分区功能并不是在存储引擎层完成的,常见的存储引擎都支持,但不是所有.

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

​ MySQL支持的分区类型为水平分区(将不同行的数据分到不同的物理文件中),而不支持垂直分区(同之前,不同列).此外,MySQL分区是局部分区索引(一个分区中既存放了数据,有存放了索引),另一种是全局分区(数据存放在各个分区中,所有数据的索引放在一个对象中).

​ 分区主要用于数据库高可用性的管理.

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

• RANGE分区:行数据在一个连续的给定区间的数据放入一个分区.
• LIST分区:面向离散的值
• HASH分区:根据自定义的表达式的返回值进行分区
• KEY分区:根据MySQL数据库提供的哈希函数来进行分区

​ 不论建立何种类型的分区,如果表中存在主键或者唯一索引,那么分区别必须是唯一索引的一个组成部分.

​ 唯一索引可以是允许NULL值的,并且分区列只要是唯一索引的一个部分就可以了(多个唯一索引之一).

(2). 分区类型

1). RANGE分区

​ RANGE分区,是最常用的一种分区类型.

create table t(
    value int
)engine=InnoDB
partition by RANGE(value)(
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p3 values less than maxvalue
);

​ 这里范围是[10,20)插入p1,右边为开区间.

​ 当插入一个不在分区中定义的值是,MySQL数据库会抛出异常,我们可以通过添加一个maxvalue值作为无限大来解决这个问题.

​ RANGE主要用于日期列的分区.如下所示:

create table t_date(
    value datetime
)engine=InnoDB
partition by RANGE(year(value))(
    partition p2008 values less than (2009),
    partition p2009 values less than (2010),
    partition pfuture values less than maxvalue
);

​ 要删除响应时间的记录只需要删除分区即可(删除分区约束):

alter table 表名 drop partition 分区名;

​ 其次,在进行查询语句时,SQL优化器会对查询范围进行裁剪,只搜索部分分区,所以可以大幅度优化查询速度.这被称为Partition Pruning(分区修减).对于RANGE分区的查询,优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择.

注意:
    where value>='2008-1-1' and value<='2008-12-31';
和
    where value>='2008-1-1' and value<'2009-1-1';
的优化是不同的,后者会搜索两个分区p2008和p2009
所以应当根据分区对SQL进行优化.

2). LIST分区

​ 分区列的值是离散的.

create table t(
    value int
)engine=InnoDB
partition by RANGE(value)(
    partition p0 values in (1,3,5,7,9),
    partition p1 values in (0,2,4,6,8)
);

​ 如果插入值不在分区的定义中,MySQL数据库同样会抛出异常.

​ 在用insert插入多个行数据的过程中遇到分区未定义的值时,InnoDB存储引擎会撤销所有插入的操作,其他存储引擎有不同的操作.

3). HASH分区

​ HASH分区的目的是将数据均匀的分布到预先定义的各个分区中,保证个分区的数据数量大致是一样的.在RANGE和LIST分区中,必须明确制定一个给定的列支或者列值集合应该保存在那个分区中,但在HASH分区中,MySQL自动的完成这些操作.

​ 需要使用partitions制定分区的数量.否则默认为1.

create table t(
    value int
)engine=InnoDB
partition by HASH(value)
partitions 4;

还支持一种LINEAR HASH的分区(线性HASH).哈希函数的步骤如下:

1. 给分区数量向上取整为2的幂值,记为v
2. N = F( num ) & ( v-1 )
3. 如果N>=分区数量,则在进行如下操作:
   1. v = CEIL( v/2 ) 除2,取整
   2. N = N & ( v-1 ) 二次进行哈希

​ LINEAR HASH分区的优点在于,增加,删除,合并和拆分分区将变得更加快捷.缺点在于各个分区间的数据分布可能不如HASH分区均衡.

4). KEY分区

​ KEY分区使用MySQL数据库提供的函数进行分区.

create table t(
    value int
)engine=InnoDB
partition by KEY(value)
partitions 4;

5). COLUMNS分区

​ 前面介绍的分区都有局限性,分区的依据必须为整数,否则需要通过函数来转换.

​ MySQL 5.5版本开始支持COLUMNS分区,可视为RANGE分区和LIST分区的进化,可以直接使用非整型的数据(日期,字符,不支持float)进行分区.

​ 对LIST分区的加强主要指对字符串进行分类.

​ 在RANGE或者LIST后加上COLUMNS即可去掉转化函数.

create table t_date(
    value datetime
)engine=InnoDB
partition by RANGE COLUMNS (value)(
    partition p2008 values less than (2009),
    partition p2009 values less than (2010),
    partition pfuture values less than maxvalue
);

(3). 子分区

​ 子分区是在分区的基础上在进行分区,MySQL数据库允许在RANGE和LIST分区上在进行一次HASH或KEY分区.语法如下:

create table t(
    value int
)engine=InnoDB
partition by RANGE(value)
subpartition by HASH(value)(
    partition p0 values less than (10)(
        subpartition s0,
        subpartition s0
    ),
    partition p1 values less than (20)(
        subpartition s2,
        subpartition s3
    ),
    partition p3 values less than maxvalue(
        subpartition s4,
        subpartition s5
    )
);

注意事项:

• 每个分区的子分区数量必须一致
• 只要一个分区定义了子分区,那么所有分区都要有子分区
• 每个子分区必须有名字,且唯一

(4). 分区中的NULL值

​ MySQL数据库的分区视NULL为无穷小的值.例如RANGE分区会将NULL值插入最左侧最小的分区.但在LIST分区中必须明确指出那个分区可以放置NULL值.HASH和KEY分区中,NULL的哈希结果总是为0.

(5). 分区和性能

​ 数据库应用一般分为两类:OLTP(在线事务处理,一般指高并发情况下的数据库设计)和OLAP(在线分析处理,指多样化查询的数据库设计).对于OLAP的应用,分区可以很好的提高效率,但对于OLTP的应用,分区很可能导致增加IO操作,而非提高效率.

(6). 在表和分区之间交换数据

​ MySQL 5.6开始支持分区或者子分区中的数据与另一个非分区表中的数据进行交换.

alter table 分区表名 exchange partition 分区表中的分区名 with table 非分区表