20.索引及数据存储结构

索引概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

优点：

提高数据检索的效率，降低数据库的IO成本。
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
在实现数据的参考完整性方面，可以加速表和表之间的连接。即对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点：

创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

InnoDB中索引的推演

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;

这个新建的表中规定了c1列为主键，且使用 Compact 行格式来实际存储记录。简化表的行格式示意图：

record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、1表示目录项记录、2表示最小记录、3表示最大记录。
next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

存放在页中的示意图：

简单的索引设计方案

想快速的定位到需要查找的记录在哪些数据页可以为快速定位记录所在的数据页建立一个目录，此目录需要满足以下条件：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
给所有的页建立一个目录项。

效果示意：

每个目录项包含着该页的页号以及该页中用户记录的最小主键值，我们只需要将目录项在物理存储器上连续存储，就可以实现根据主键值快速查找某条记录的功能了。此目录便可称为索引。

目录项记录和普通的用户记录的不同点：

目录项记录的record_type值是1，而普通用户记录的record_type值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
了解：记录头信息里还有一个叫min_rec_mask的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

相同点：两者用的是一样的数据页，都会为主键值生成Page Directory（页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

当原先存储目录项记录的页30的容量填满，则不得不需要新的页来存放页31所对应的目录项。若目录项记录页的数目过多，我们很自然地会想到创建目录项记录页的目录页。

这种数据结构称为B+树。

B+树

一个B+树的节点可以分为很多层，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录，那么当B+树有4层时，最多能存放1千亿条记录。所以一般情况下，用到的B+树不会超过4层，则我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页）。

常见的索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

二级索引

也称辅助索引、非聚簇索引

回表：根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。

一个表中可以有多个二级索引，如果将完整的用户记录放在叶子节点会造成严重的数据冗余。

联合索引

本质上也是二级索引。

可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。

例如让B+树按照 c2和c3列的大小进行排序：先把各个记录和页按照c2列进行排序；在记录的c2列相同的情况下，采用c3列进行排序。

注意项：

根页面位置万年不动

之前为了方便理解，我们先提出存储用户的叶子节点，再提出存储目录项记录的内节点，但实际上B+树的形成过程如下：

当为某个表创建一个B+树索引（聚簇索引自动创建）时，都会为此索引创建一个根节点页面，此时根节点页面中既没有用户记录，也没有目录项记录。
插入的用户记录会先存储到此根节点。
当此根节点中的可用空间用完时，再插入记录会将根节点中的所有记录复制到一个新分配的页，记为a，然后对a页进行页分裂操作，得到另一个分页b，这时新插入的记录根据键值（就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

当InnoDB存储引擎需要用到该索引时，会从固定的位置读取根节点的地址，从而访问该索引。

内节点中目录项记录的唯一性

若二级索引中目录项的内容只是索引号+页号的搭配，那么当插入多个索引号相同的值时，会出现不清楚插入记录分配在哪个页数的情况。

那么需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上由三个部分构成：

索引列的值
主键值
页号

再插入记录时，可以先比较索引号，当索引号相同时再比较主键值，所以最后可以定位唯一的一条目录项记录，确定其插入的页数。

一个页面最少可以存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问的存储真实数据的目录。

MyISAM中的索引方案

B树索引适用存储引擎如表所示：

索引/存储引擎	MyISAM	InnoDB	Memory
B-Tree索引	支持	支持	支持

即使支持同一种类型的索引，但他们的实现原理是不同的。Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

两种引擎中索引的区别：

InnoDB包含1个聚簇索引，而MyISAM的索引方式都是“非聚簇”的，建立的索引相当于都是二级索引。
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

索引的代价

索引在空间和时间上都会有消耗：

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

MySQL数据结构选择的合理性

全表遍历自然是十分消耗性能的，不再赘述

Hash结构

哈希函数有可能将两个不同的关键字映射到相同的位置，叫做碰撞，在数据库中一般采用链接法来解决。

一般来说，全表遍历的时间复杂度为O(n)，Hash时间复杂度为O(1)。

索引 / 存储引擎	MyISAM	InnoDB	Memory
Hash索引	不支持	不支持	支持

虽然Hash索引查找的效率极高，但有些弊端：

Hash表只能匹配是否相等，不能实现范围查找
Hash索引指向的数据是无序的，无法起到排序优化的作用
Hash会将组合索引的字段合并计算，不支持联合索引的最左侧原则（即联合索引的部分索引无法使用）
一些极端情况下Hash会导致生成一条单链表，失去查询优势

InnoDB支持自适应Hash索引：当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引（存在于InnoDB架构中的缓存中）。

方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深时，通过自适应 Hash 索引可以明显提高数据的检索效率。

可以通过 innodb_adaptive_hash_index 变量来查看是否开启了自适应 Hash：

show variables like '%adaptive_hash_index';

二叉搜索树

磁盘的IO次数和索引树的高度是相关的，为了提高查询效率，就需要减少磁盘IO次数，则需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。

AVL树

即平衡二叉树，在一些情况下，二叉搜索树有可能全部为右子节点成为一条单链表，此时可以推出平衡二叉树的概念。

B-Tree

B-树就是B树，只有B树和B+树，不要读B减数

平衡二叉树还不能满足我们的需求，若是“平衡M叉树”则可以存储更多的数据。

M阶的B树有以下特征：

根节点的儿子数的范围是 (2,M]。
每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为 [ceil(M/2), M]。
叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i] <Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …,P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]指向关键字大于 Key[k-1] 的子树。
所有叶子节点位于同一层。

B 树相比于平衡二叉树来说磁盘 I/O 操作要少，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，IO次数足够少，就可以提高查询性能。

B+Tree

B+ 树和 B 树的差异：

有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数+1。
非叶子节点的关键字也会同时存在在子节点中，并且是子节点中值最大（或最小）的关键字。
非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， 非叶子节点既保存索引，也保存数据记录。
所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

为了减少IO，索引树会一次性加载吗？

1、数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G。

2、当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO？

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值，因为是估算，为了方便计算，这里的K取值为10^3。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。（这里假定一个数据页也存储10^3条行记录数据了）

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的节点，而只是叶子结点中关键字的索引。所有任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

R-Tree

R-Tree在MySQL很少使用，仅支持 geometry数据类型，支持该类型的存储引擎只有myisam、bdb、 innodb、ndb、archive几种。

R树很好的解决了高维空间搜索问题。它把B树的思想很好的扩展到了多维空间，采用了B树分割空间的思想，并在添加、删除操作时采用合并、分解结点的方法，保证树的平衡性。因此，R树就是一棵用来存储高维数据的平衡树。相对于B-Tree，R-Tree的优势在于范围查找。

InnoDB数据存储结构

数据库的存储结构：页

索引结构给我们提供了高效的索引方式，不过索引信息以及数据记录都保存在文件上，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL服务器上的存储引擎负责对表中数据的读取和写入工作。不同存储引擎中存放的格式一般不同的，甚至有的存储引擎比如Memory都不用磁盘来存储数据。

磁盘与内存交互基本单位：页

页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应的分组中的记录即可快速找到指定的记录。

页的上层结构

在数据库中，还存在着区、段和表空间的概念：

区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。InnoDB中的页大小默认是16KB，那么一个区的大小是64*16KB=1MB。

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

页的内部结构

文件头部

File Header（文件头部）（38字节）：描述各种页的通用信息。（比如页的编号、其上一页、下一页等）

名称	占用空间大小	描述
`FIL_PAGE_SPACE_OR_CHKSUM`	`4`字节	页的校验和（checksum值），头尾部都有
`FIL_PAGE_OFFSET`	`4`字节	页号，可以唯一定位一个页
`FIL_PAGE_PREV`	`4`字节	上一个页的页号
`FIL_PAGE_NEXT`	`4`字节	下一个页的页号
FIL_PAGE_LSN	`8`字节	页面被最后修改时对应的日志序列位置
`FIL_PAGE_TYPE`	`2`字节	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	`8`字节	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
`FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID`	`4`字节	页属于哪个表空间

FIL_PAGE_TYPE 内部分配：

类型名称	十六进制	描述
FIL_PAGE_TYPE_ALLOCATED	0x0000	最新分配，还没有使用
`FIL_PAGE_UNDO_LOG`	0x0002	Undo日志页
FIL_PAGE_INODE	0x0003	段信息节点
FIL_PAGE_IBUF_FREE_LIST	0x0004	Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP	0x0005	Insert Buffer位图
`FIL_PAGE_TYPE_SYS`	0x0006	系统页
FIL_PAGE_TYPE_TRX_SYS	0x0007	事务系统数据
FIL_PAGE_TYPE_FSP_HDR	0x0008	表空间头部信息
FIL_PAGE_TYPE_XDES	0x0009	扩展描述页
FIL_PAGE_TYPE_BLOB	0x000A	溢出页
`FIL_PAGE_INDEX`	0x45BF	索引页，也就是我们所说的`数据页`

文件尾部

File Trailer（文件尾部）（8字节）

前4个字节代表页的校验和：和File Header中的校验和相对应的。

为了检测一个页是否完整（同步时可能发生只同步一半就宕机的情况），这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。

后4个字节代表页面被最后修改时对应的日志序列位置（LSN）：
这个部分也是为了校验页的完整性的，如果首部和尾部的LSN值校验不成功的话，就说明同步过程出现了问题。

空闲空间

每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，存储的记录会按照指定的行格式存储到User Records部分。

用户记录

User Records中的记录按照指定的行格式一条一条摆在User Records部分，相互之间形成单链表。

最小最大记录

对于一条完整的记录来说，比较记录的大小就是比较主键的大小。InnoDB规定的最小记录与最大记录这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的。

这两条记录不属于用户记录，所以没有在User Records部分存放

页目录

在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此在页结构中专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。

将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。
第 1 组，也就是最小记录所在的分组只有 1 个记录；
最后一组，就是最大记录所在的分组，会有 1-8 条记录；
其余的组记录数量在 4-8 条之间。
这样做的好处是，除了第 1 组（最小记录所在组）以外，其余组的记录数会尽量平分。
在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

记录的分组规则：对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

步骤：

初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量

页面头部

Page Header部分占用固定的56个字节，专门存储各种状态信息。

名称	占用空间大小	描述
PAGE_N_DIR_SLOTS	2字节	在页目录中的槽数量
PAGE_HEAP_TOP	2字节	还未使用的空间最小地址，也就是说从该地址之后就是`Free Space`
PAGE_N_HEAP	2字节	本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE	2字节	第一个已经标记为删除的记录的记录地址（各个已删除的记录通过`next_record`也会组成一个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE	2字节	已删除记录占用的字节数
PAGE_LAST_INSERT	2字节	最后插入记录的位置
PAGE_DIRECTION	2字节	记录插入的方向
PAGE_N_DIRECTION	2字节	一个方向连续插入的记录数量
PAGE_N_RECS	2字节	该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
PAGE_MAX_TRX_ID	8字节	修改当前页的最大事务ID，该值仅在二级索引中定义
PAGE_LEVEL	2字节	当前页在B+树中所处的层级
PAGE_INDEX_ID	8字节	索引ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF	10字节	B+树叶子段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP	10字节	B+树非叶子段的头部信息，仅在B+树的Root页定义

InnoDB行格式（或记录格式）

指定行格式的语法

在创建或修改表的语句时可以指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

COMPACT行格式

变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

存储的变长长度和字段顺序时反过来的，例如两个varchar的顺序为a(10),b(15)，那么存储的长度顺序为15,10。

NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。若表中没有允许存储 NULL 的列，则NULL值列表也不存在。

为什么要定义NULL值列表？

数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，会很浪费空间。所以直接就在行数据得头部开辟出一块空间专门用来记录：

二进制位值为1时，代表该列的值为NULL；0则不为NULL
记录顺序为反，即应从右往左读

例如字段a,b,c，a为主键，存储一条数据为a=1,b=null,c=2。则NULL值列表中存储：01；0表示c不为null，1表示b为null。因为主键默认唯一且非空，所以存储数据时会自动跳过主键。

记录头信息（5字节）

名称	大小（单位：bit）	描述
`预留位1`	1	没有使用
`预留位2`	1	没有使用
`delete_mask`	1	标记该记录是否被删除，为1表示已被删除
`mini_rec_mask`	1	B+树的每层非叶子节点中的最小记录都会添加该标记，值为1。
`n_owned`	4	表示当前记录拥有的记录数
`heap_no`	13	表示当前记录在记录堆的位置信息
`record_type`	3	表示当前记录的类型，`0`表示普通记录，`1`表示B+树非叶子节点记录，`2`表示最小记录，`3`表示最大记录
`next_record`	16	表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量

为什么还要存储被删除的记录？

如果直接移除这些记录那么其它在磁盘上的记录需要重新排列，导致性能消耗。所有被打上删除标记的记录会组成一个垃圾链表，这个链表中的记录占用的空间称为可重用空间，新纪录的插入可能会将之覆盖。

heap_no值怎么分配的？

MySQL会自动给每个页加两个记录，称为伪记录或虚拟记录。一个代表最小记录，一个代表最大记录。它们的heap_no值分别为0和1，则我们添加的数据从2开始。

next_record值指向的不是按照插入顺序的下一条记录，而是按照主键值从小到大的排列的下一条记录。

记录的真实数据

记录的真实数据除了我们自定义的列的数据之外，还有三个隐藏列：

列名	是否必须	占用空间	描述
row_id（DB_ROW_ID）	否	6字节	行ID，唯一标识一条记录
transaction_id（DB_TRX_ID）	是	6字节	事务ID
roll_pointer（DB_ROLL_PTR）	是	7字节	回滚指针

一个表没有手动定义主键，则会选取一个Unique键作为主键，若Unique键还没有，就会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

CREATE TABLE mytest(
    col1 VARCHAR(10),
    col2 VARCHAR(10),
    col3 CHAR(10),
    col4 VARCHAR(10)
)ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;

INSERT INTO mytest
VALUES('a','bb','bb','ccc'),('d','ee','ee','fff'),('d',NULL,NULL,'fff');

mytest.ibd文件存储内容：
-------------------------------------------------------------------------
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00|supremum........|
0000c080 2c 00 00 00 2b 68 00 00 00 00 00 06 05 80 00 00|,...+h..........|
0000c090 00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20|.2..abbbb|
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00|ccc........+...|
0000c0b0 2b 68 01 00 00 00 00 06 06 80 00 00 00 32 01 10|+h...........2..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66|deeeefff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 2b 68 02 00 00|..........+h...|
0000c0e0 00 00 06 07 80 00 00 00 32 01 10 64 66 66 66 00|........2..dfff.|
-------------------------------------------------------------------------
解析：
03 02 01                     /*变长字段长度列表，逆序*/
00                           /*NULL标志位，第一行没有NULL值*/
00 00 10 00 2c               /*Record Header，固定5字节长度*/
00 00 00 2b 68 00            /*RowID InnoDB自动创建，6字节*/
00 00 00 00 06 05    	     /*TransactionID*/
80 00 00 00 32 01 10   	     /*Roll Pointer*/
61                           /*列1数据'a'*/
62 62                        /*列2数据'bb'*/
62 62 20 20 20 20 20 20 20 20/*列3数据'bb'*/
63 63 63                     /*列4数据'ccc'*/

第三行数据有NULL值，其NULL值06转换为二进制为00000110，表示第二三列的值为NULL。

Dynamic和Compressed行格式

mysql5.0之后的默认行格式为Compact ， 5.7之后的默认行格式为dynamic

行溢出

一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出。

#变长字段的长度占用2个字节，NULL值标识需要占用1个字节
#若有not null属性，则可以存储65533个字节。
CREATE  TABLE  varchar_size_demo(
    c  VARCHAR(65532)
)  CHARSET=ascii  ROW_FORMAT=Compact;

Dynamic、Compressed行格式和Compact行格式挺像，只不过在处理行溢出数据时有分歧：

Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。
Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

Redundant行格式不常用，不在此做详细介绍

区、段和碎片区

区的作用：

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的使用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。

引入区的概念，一个区就是物理位置上连续的64个页。因为InnoDB中的页的大小默认是16KB，所以一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页的单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机I/O，功大于过！

区的分类：

大体上可以分为4种类型：

空闲的区(FREE)：现在还没有用到这个区中的任何页面。
有剩余空间的碎片区(FREE_FRAG)：表示碎片区中还有可用的页面。
没有剩余空间的碎片区(FULL_FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面。
附属于某个段的区(FSEG)：每一索引都可以分为叶子节点段和非叶子节点段

处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区都是独立的，直属于表空间。而处于FSEG状态的区是附属于某个段的。

段的作用：

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。

段其实不对应表空间中的某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

碎片区的作用：

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M（64*16KB=1024KB）存储空间，但对于只存在几条记录的小表也分配2M的存储空间过于浪费。为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片（fragment）区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页面用于段A，有些页面用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以为某个段分配存储空间的策略为：

在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

posted @ 2023-10-19 14:18 LemonPuer 阅读(729) 评论(0) 收藏举报

刷新页面返回顶部

柠檬普洱