MySQL_数据页、b+树索引

InnoDB记录存储结构

除InnoDB以外的常用引擎,还有MyISAM

页是磁盘和内存中交互的基本单位,也是存储空间的基本单位,默认大小为16kb

COMPACT行格式:

记录的额外信息:变长字段长度列表、NULL值列表、记录信息;

1. 变长字段列表:(如varchar等)因存储多少字节的数据是不固定的,所以分为两部分:真正的数据、占用的字节数

按照列的顺序逆序存放 最大存储数M * 占用字节数W <= 255, 则使用1字节表示占用的字节数,其中若>255, 若实际占用的<=127则还用1字节,否则2字节.
InnoDB读取记录时候先看表结构,入欧工允许存储的最大字节数部大雨255可以认为只是用1字节来表示真是数据占用的字节数

2. NULL值列表:首先统计允许存储NULL的列有哪些,若没有则NULL值列表页不存在了

REDUNDANT行格式:

5.0之前的了

DYNAMIC和COMPRESSED行格式

类似于COMPACT行格式,不过处理溢出时的决策不同,不是只把溢出的数据放到原先的溢出页中了,而是直接将所有的数据存储在溢出页,然后直接指向地址,另外COMPRESSED回采用压缩算法对页面进行压缩.

InnoDB数据页结构

页的结构图:

一开始生成页的时候,没有User Records的部分,每当插入一条记录时,会从Free Space中申请一个记录大小的空间,并分配.

记录头:

其中
n_owned是带头大哥记录的是该组中所有的记录条数，小弟记录的都为0。
next_record代表从当前记录的真实数据到下一条记录的真实数据，正数代表在后面，附属代表在前面(按主键值排序)，而且想做读取就是记录头信息，向右读区是真实数据。
Supremum主键值最大的用户记录的下一条记录
Infimum记录的下一条记录是主键值最小的用户记录

变长字段长度列表、NULL值列表中的信息逆序存放的原因：
可以是使得记录中位置靠前的字段和他们对应的字段长度信息在内存中的距离更近，这可能会提高高速缓存的命中率。

Page Directory页目录

1、将所有的有效正常记录（包括Infimum和Supremum）划分为几个组
2、每个组的最后一条记录计为“带头大哥”
3、每个组最后一条数据的地址偏移量单独提取出来，按顺序存储到靠页尾部的地方，这个地方就是页目录。并且将之称为槽（slot），占用2字节

Infimum记录所在分组只能有1条记录
Supremum记录的所在的分组只能在1-8条
剩下的记录范围只能是4-8条

步骤：1.只有两个槽Infi，Supre 2.找到比插入大但差值最小的记录，槽对应的n_owned+1
3.记录数为8后，再插入时拆分成两个组，一个4一个5，新增槽，记录最大的记录的偏移量

采用二分法快速寻找槽

PageHeader页面头部

描述记录的状态信息

File Header文件头部

通用于各种类型的页，都会将其作为第一个组成部分，描述通用于各种页的信息。

其中：
FIL_PAGE_SPACE_OR_CHKSUM：相当于当前页面的校验和。
FIL_PAGE_OFFSET：页号，通过它定位一个页
FILE_PAGE_TYPE：页的类型

以下的开头前缀都是FIL_PAGE_
TYPE_ALLOCATED 最新分配没使用
UNDO_LOG undo日志页
INODE 存储段的信息
IBUF_FREE_LIST Change Buffer空闲列表
IBUF_BITMAP Change Buffer的一些属性
TYPE_SYS 系统数据
TYPE_TRX_SYS 事务系统数据
TYPE_FSP_HDR 表空间头部信息
TYPE_XDES 存储区的一些信息
TYPE_BLOB 溢出页
INDEX 索引页 即数据页。可以组成一个双向链表

File Trailer 文件尾部

File Trailer由8个字节组成，可以分成2个小部分

• 前4个字节代表校验和，与File Header中的校验和对应。每次页面在内存中修改时，刷新之前将校验和算出来，File Header会首先刷新到磁盘中，然后校验和也会被写到页的尾部，若刷新成功那么校验和是一致的，不同则错误
• 对应最后修改时的LSN的后4个字节（日志序列号）与Header中的FIL_PAGE_LSN后4哥字节相同，用于完整性。

B+树索引

InnoDB的索引方案

聚簇索引（ID排序）
二级索引（别的列排序）
联合索引（多个列排序）

1. B+树索引的根节点不会移动（页号不会改变）
2. 二级索引的内节点记录的内容实际上由3部分组成：索引列的值、主键值、页号
3. 一个页面至少容纳两条记录

MyISAM索引方案

将索引和数据分开存储
将记录按照插入顺序存放再一个文件中（数据文件），不划分页，通过行号快速访问。
将索引信息单独存放在另一个文件中，为表的主键单独创建一个索引，在索引的叶子节点中是主键值与行号的组合。
即MyISAM中建立的索引相当于全部都是二级索引。

MySQL中创建和删除索引的语句

ALTER TABLE 表名 ADD/DROP （INDEX｜KEY）索引名

B+树索引的使用

B+树索引在空间和时间上都有代价。可以用于减少需要扫描的记录数量，也可以用于排序和分组。

一般只为WHERE中的列、连接子句中的连接列，活着出现在ORDER BY 或 GROUP BY

注意事项：

• 只为用于搜索、排序或分组的列创建索引
• 不重复的数据占重比大时使用（过多可以重复的列，有太多的回表操作）
• 索引列的类型尽量小（IO消耗少）
• 只为索引前缀建立索引，减小占用的存储空间（可以只保留字符串的前10个字符）
• 使用覆盖索引进行查询，避免回表操作，（在查询列表中只保留索引列）
• 让索引列以列名出现在搜索条件中（key2 * 2 <4 变为 key2 < 4/2）
• 减少聚簇索引发生页面分裂，让主键拥有AUTO_INCREMENT属性（减少插入时的损耗）
• 定位并删除表中的冗余和重复索引（已经有联合索引，那么单独索引就可以看成冗余索引）