Mysql 索引简析

不秃头的王小闹
158 阅读9分钟

Mysql是后台开发同学常用的db选择，具有速度快，容易上手，当然更主要的是开源，后续会慢慢出几篇文章来做一下简析，当然后续所有的文章都是基于 InnoDB 来进行讲解

1 数据行格式

所谓的行格式就是一条mysql数据的格式。Mysql提供的四种行格式

  • Compact
  • Redundant
  • Dynamic
  • Compressed

那么如何制定一个表的行格式呢，我们有2中方式

  • CREATE TABLE table_test (a1, b1, c1) ROW_FORMAT=Compact
  • ALTER TABLE table_test ROW_FORMAT=Compact

下面我们简单说一下每种行格式的结构

1.1 行格式详解

画个草图简单表示下行格式的结构

image.png

  • field 1~n 就是我们熟知的列信息
  • 剩余的统称为记录头信息
    • 变长字段长度列表
    • null 值列表
    • 记录头信息

示例表

idnameagescoreadress
1jack18100上海市闵行区xx路
2dan1998河北省
3green1796河南省郑州市

其中

  • name 为varchar(10)，允许为null
  • age为int，允许为null
  • address为varchar(20),是变长字段允许为null
格式特点小贴士
变长字段长度列表mysql可以支持非固定长度的数据类型，诸如 text blob 等, mysql通过 变长字段长度列表 来标记这些非固定长度字段的所占字节长度，数按照列的顺序逆序存放，存放的数据主要列的长度变长字段长度列表存储的长度一般是用6进制表示 示意图1
null 值列表统一管理值为null的列如果都是非null则 null值列表 不存在，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，见 示意图2
记录头信息里面有些详细的内容见表

变长字段长度示意图 示意图1 image.png

null值列表示意图 示意图2

image.png id=1的数据 age 和 address都是null，但是 name 不是，所以对应的二进制数字为 006,转为16进制还是006 示意图3 image.png

记录头信息，我们只说一些后面可能会用到的

名称说明补充
delete_mask用于标记数据是否被删除mysql中删除的数据不会真正的删除，而是用一个字段进行标记（有点类似于我们实际开发中数据的软删除）。删除后仍然存在于磁盘内，所有被删除的行记录会组成一个 被删除链表，也叫垃圾链表，新记录插入的时候可能会覆盖垃圾链表内的磁盘空间
n_owned表明该数据行拥有几行数据这个主要是后面讲到数据页内的数据检索用到
next_record当前记录的真实数据到下一条记录的真实数据的地址偏移量用于数据页内检索，按照主键大小进行排序
min_rec_maskB+树的每层非叶子节点中的最小记录都会添加该标记
heap_no记录在数据页内的位置最小记录和最大记录的 heap_no 值分别是0和1
record_type0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录

2 数据页结构

前面简单介绍了小mysql的行格式，下面我们简单了解下数据页（这里说的数据页特指存放记录的页）。 Innodb的数据页大概分为以下几个部分

名称功能备注
File Header文件头部，存放页通用信息
Page Header页头部数据页专有信息
Infimum + Supremum最小记录和最大记录标记该页的最小记录和最大记录(主键)，是实现索引的关键
User Records实际存储的记录信息页生成的时候并没有User Records，插入数据后才真正的生成；插入数据的时候会向Free Space 申请空间当 Free Space 使用完，标记着页空间使用完
Free Space空余空间同上
Page Director页内目录页中的某些记录的相对位置
File Trailer文件尾部校验页是否完整

image.png

image.png

2.1 next_record

  1. 所有用户记录以 next_record 组成了一个单链表，
  2. 被删除的行的 next_record 是0
  3. 最大记录Supremum 的 next_record 为0

2.2 Page Director

上门我们讲到数据页将所有有效的用户记录串联成一个单链表，链表头是Infimum ， 链表尾是Supremum 。那如果我们想定位数据页中某一行或者几行数据的时候该如何做呢？遍历链表？确实是一个办法，但是性能堪忧。为此Innodb引入了一个令人拍案叫绝的方案，即Page Director，即数据页目录。具体做法

  1. 将所有的正常记录划分为几个组，每组最大记录的n_owned 代表本组有几条数据
  2. 将每组最后一条数据的地址偏移量整合到一起，放在 Page Director 中。称之为槽solt
  3. Innodb规定，最小记录Infimum 所在的分组只能有一条，即它本身，最大记录Supremum所在的分组可以有1-8条，其他分组行数在 4-8条。Infimum 和Supremum 始终都在2个不同的分组里
  4. 数据页在刚刚完成初始化的时候只有2个分组，即 Infimum 和Supremum，在后续不断有数据插入的过程中的时候，都会从Page Director 中找到一个中找到主键值比本记录的主键值大并且差值最小的槽。然后该槽对应的n_owned +1，直到该组达到8。当达到8的时候，如果又有新数据过来，就会把该组分割成 2个组（4和5），同时增加一个槽来记录新的分组中最大记录的偏移量

image.png

那在页内是如何进行数据检索的呢？（PS 应该有同学碰到过这个面试题）。 我们先定下个前提： 目前我们说的检索是根据主键查找，如果根据普通字段查找，只能依次遍历(那个说根据索引的同学先坐下，等我们讲到索引再谈) 下图是在页内进行检索的方式

image.png

2.3 File Header

名称功能
FIL_PAGE_SPACE_OR_CHKSUM页的校验和（checksum值）
FIL_PAGE_PREV上页的页号
FIL_PAGE_NEXT下页的页号
FIL_PAGE_LSN页面被最后修改时对应的日志序列位置
FIL_PAGE_TYPE该页的类型
FIL_PAGE_FILE_FLUSH_LSN仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID页属于哪个表空间

数据页通过 File Header的FIL_PAGE_PREV 和 FIL_PAGE_NEXT组成了双向链表

3 索引

3.1 数据页的联系

上面我们主要讲了在数据页内通过主键 id 如何快速定位数据的方式。其实现实中我们的数据往往分布于不同的页内，这种情况 Innodb 如何定位的呢。 下面这张图简单示意了一下多数据页的分布情况

image.png

  • record_type 表示该记录的类型 2表示最小记录即Infimum 3表示最大记录 即Supremum， 0表示普通记录
  • 数据页之间有严格的大小关系，即下一页最小id一定比上一页的最大id还要大。比如我们要插入 id=20 的数据，一定会插入再数据页1中，如果数据页1已满，我们会将id=200的放到数据页11中（页分裂）
  • 图中我们只标记了数据的主键，其他字段暂时未标记出来
  • 数据页之通过pre next类似的关系维持了一个双向链表

3.2 建立数据页的目录

3.2.1 数据页图解

如果把数据页内 槽管理记录的方案移植到管理数据页上。也就是给数据页做一个目录，是不是就可以实现多数据页的快速检索？答案是肯定的，Innodb给所有的数据页实现了一个目录， 目录项包含具体数据页的信息，里面只有2个字段主键ID（对应数据页中最小的主键ID）和页号，也可称之为目录记录 那么这些目录记录如何串联呢？个人想到了2中方案

  • 数组。实现一个数组，将 目录记录看成一个元素，然后依次放入数组
  • 链表。实现一个单链表，每个目录记录都有自己下个记录。 Innodb 最终选择了链表，原因如下
  1. 数组需要连续的空间，当数据库内数据几何增长的时候，Innodb需要随之扩展数组的空间。这个很难实现
  2. 如果在中间删除或者新加一个记录。后续元素都要移动。不是什么好主意 既然是链表，数据页内的元素也是一个链表啊。于是Innodb又采用了数据页的形式来维护目录记录，如下图

image.png 细心的胖友发现了目录页15中记录的 record_type=1.没错 目录记录的record_type就是1。目的就是要与数据页内记录区分开。

3.2.2 如果再插入一条呢

已上图为例，如果所有数据页已满的情况下，再插入一条数据 450，Innodb是如何操作的

  1. 既然现有的 目录页1，目录页11，目录页50 都已经满，Innodb为新的数据开辟一张新的数据页 目录页34。
  2. 基于上门我们说过的页分裂，新的数据450 比目录页50 中的最后一条要小，所以450 插入到 目录页50， 数据500插入到新的 目录页34

image.png

3.2.3 如何管理目录记录页

上面的图我们画了2个目录记录页，如果后续有更多的出来，Innodb该如何管理呢？答案还是链表，就是再向上抽取一层

image.png 上图的三层结构就是我们经常说的Innodb索引

3.2.4 索引分类

  1. 聚簇索引。 以主键ID作为查找依据建立的索引，上面的图就是聚簇索引
  2. 普通索引。 以普通字段 a 为查找依据建立的索引。建立的索引格式和聚簇索引一样，只不过以普通字段a作为基准。
    • 页内的记录是按照a列的大小顺序排成一个单向链表
    • 数据页也是按照 a列的大小形成双向链表
    • 目录记录页 也是按照 a列的大小形成双向链表
    • 叶子节点存储的是 a列 + 主键。如要要查找所有数据，还要根据主键 查一遍主键索引
  3. 联合索引。联合索引就是以列a2 和a3 代替a。其他都一样

本文为阅读 《MySQL 是怎样运行的：从根儿上理解 MySQL》读书笔记，有兴趣的朋友可以去看看，侵权删

avatar
文章
阅读
粉丝