01-MySql B+树 页格式 行格式 页 区 段 表空间

MySql B+Tree

B+Tree 所有的数据都存放在叶子节点中, 每个叶子节点的最小单位是 ‘页’ 每个页默认也是16KB 的空间, 每个页里面存放N条列数据, 每条数据 都保存了下一条数据的地址 也就形成了一个单向列表, 每个页之间保存是一个双向列表保存了上一页下一页地址.

建表语句
CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
上图我们可以看出, 我们指定了一个主键为c1 当我们插入一条数据的时候 会按照c1从小到大 从左到右的顺序进行插入 每一条记录 都有一个record_type 字段 0=数据节点, 1= 非叶子节点, 在每一个节点 开始也结束的时候 有一个最小记录和最大记录, 也就对应这个节点的 链表头和尾部 头部用 record_type=2 表示 , 尾部record_type=3表示, 这样我们在插入数据的时候就已经构建好了一颗B+Tree 在查询的时候效率自然会高很多.

聚簇索引

聚簇索引:
也就是我们构建B+Tree的时候的 主键构建的索引, 它的特点是 叶子节点存储原数据, 也就是依赖上图 c1 构建的索引.

非聚簇索引(二级索引)

非聚簇索引(二级索引)
非聚簇索引如我们针对c3 这个字段再构建一个索引, 它的索引结构和上面一样也是B+Tree索引, 他也会关联到数据 , 但是我们想一下 一个数据有必要存储很多份吗 ? 所以非聚簇索引 存储的不是数据而是 主键地址;

上图主键索引就是我们主键构成的, 而二级索引叶子节点存储的是我们的主键;

回表查询的概念

如果我们针对二级索引c3 构建了一个二级索引, 查询数据的时候查询到叶子节点的时候查询到的不是真正的数据 而是主键索引, 需要拿着主键索引再去 聚簇索引查询真实的数据;
select * from index_demo where c2=‘x’ // 需要回表查询应为不知道c3的值是多少
select c1,c2 from index_demo where c2=‘x’ // 不需要回表查询 应为二级索引 已经包含 c1 , c2 的值了;

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：先把各个记录和页按照c2列进行排序。在记录的c2列相同的情况下，采用c3列进行排序注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：建立 联合索引 只会建立如上图一样的1棵B+树。

索引支持力度

MyISAM 和 InnoDB对比

InnoDB

1. InooDB Mysql5.7 中 .frm 文件存储数据库定义文件, .ibd 文件存储 索引文件和真实数据
2. 正因为以上的特点所以InnoDB可以有非聚簇索引

MyISAM

1. .frm 文件存储数据库定义文件, .MYI 存储索引文件, .MYD文件存储数据文件
2. 基于以上 MyISAM的特点所以我们可以理解为MyISAM没有聚簇索引, 因为是分不同文件存储的, 所以它的索引里面存储的是 真实数据的地址偏移量

索引的利弊

毫无疑问索引能够提高我们的查询效率, 但是也会降低我们修改的速度和性能 如 我们一个表有20个字段, 都建立了索引 插入一条数据的时候就要更新这20个索引, 修改删除更是如此, 空间上索引也会占用我们的硬盘 内存;
还需要额外的 记录位移, 页面分裂, 页面回收等来维护索引;

InnoDB 存储结构

数据页内部结构

位置	参数	描述
头部信息	FIL_PAGE_OFFSET	页号唯一定位一个页的编号
头部信息	FIL_PAGE_TYPE	页的类型有很多
头部信息	FIL_PAGE_PREV,FIL_PAGE_NEXT	上一页地址, 下一页地址
头部信息	FIL_PAGE_SPACE_OR_CHKSUM	校验和 如一个大数据的MD5
尾部信息		这部分数据和头部信息中校验和表示校验文件
body	Free Space (空闲空间)	表示这个页还剩余多少空间
body	User Records	用户记录数据, 单向链表
body	Infimum + Supremum（最小最大记录）	用户记录的链表头和尾部
body	Page Directory（页目录）	给用户数据做一个页目录数组 方便快速查找

InnoDB行格式

格式	参数	描述
COMPACT 行格式	NULL值列表	一行数据 二进制位的值为1时，代表该列的值为NULL。 0时，代表该列的值不为NULL
COMPACT 行格式	记录头信息（5字节）	delete_mask,0=没有被删除,1=被删除掉了; record_type, 0=普通记录, 1=非叶子节点,2=最小记录,3=最大记录;heap_no行编号,0隐藏最小几率,1隐藏最大几率, 其他用户记录一次递增;n_owned分组最大记录;next_record =下一行记录地址
COMPACT 行格式	记录的真实数据	会有三个隐藏列 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR

行格式除了COMPACT 还有 Dynamic和Compressed行格式 mysql 默认为 Dynamic 主要是针对行溢出 的策略基本和COMPACT 格式一样;

数据页, 数据区, 数据段

数据页

mysql 页 是 内存和磁盘交互的最小单位, 一个页= 16KB, 磁盘保证了一个页是顺序存储的; 在磁盘读取中 这个页是连续的存储, 所以顺序IO比随机IO要快很多; 当我们更新一条数据的时候也就更新了一个页的数据;

数据区

一个区 = 64个页 = 64*16 = 1024(1m) 空间 在存储的时候 , 区的概念也是为了解决 随机IO读取慢的问题, 所以引入了区的概念 , 在我们查找排序的时候 我们希望相邻的数据不要距离太远, 所以引入了区的概念;

数据段

我们再查询一条数据的时候基本 后面排序好的几条数据也是要的, 所以引入了段的概念, 有 叶子节点段 和非叶子节点段, 在查询排序好的数据的时候 查询某个区下面的数据都是非叶子节点的 提高的IO的顺序和性能;
段不是物理空间的连续空间, 它是由 连续的区和 碎片 页组成的

表空间

mysql 真实存储数据文件和索引的文件, 是数据库逻辑最高层