你管这破玩意叫 B+ 树?(一)

发布于 2022-5-26 18:38
浏览
0收藏

 

索引可以说是每个工程师的必备技能点,明白索引的原理对于写出高质量的 SQL 至关重要,今天我们就从 0 到 1 来理解下索引的原理,相信大家看完不光对索引还会对 MySQL 中 InnoDB 存储引擎的最小存储单位「页」会有更深刻的认识


从实际需求出发


假设有如下用户表:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` int(11) DEFAULT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
  `height` int(11) DEFAULT NULL COMMENT '身高',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

 

可以看到存储引擎使用的是 InnoDB,我们先来看针对此表而言工作中比较常用的 SQL 语句都有哪此,毕竟技术是要为业务需求服务的,

1. select * from user where id = xxx
2. select * from user order by id asc/desc
3. select * from user where age = xxx
4. select age from user where age = xxx
5. select age from user order by age asc/desc

 

既然要查询那我们首先插入一些数据吧,毕竟没有数据何来查询

insert into user ('name', 'age', 'height') values ('张三', 20, 170);
insert into user ('name', 'age', 'height') values ('李四', 21, 171);
insert into user ('name', 'age', 'height') values ('王五', 22, 172);
insert into user ('name', 'age', 'height') values ('赵六', 23, 173);
insert into user ('name', 'age', 'height') values ('钱七', 24, 174);

 

插入后表中的数据如下:

 你管这破玩意叫 B+ 树?(一)-开源基础软件社区
不知你有没发现我们在插入的时候并没有指定 id 值,但 InnoDB 为每条记录默认添加了一个 id 值,而且这个 id 值是递增的,每插入一条记录,id 递增 1,id 为什么要递增呢,主要是为了查询方便,每条记录按 id 由小到大的顺序用链表连接起来,这样每次查找 id = xxx 的值就从 id = 1 开始依次往后查找即可

 你管这破玩意叫 B+ 树?(一)-开源基础软件社区
现在假设我们要执行以下 SQL 语句,MySQL 会怎么查询呢

select * from user where id = 3


如前所述,首先从 id 最小的记录也就是 id = 1 读起,每次读一条记录,将其 id 值与要查询的值比较,连续读三次记录于是找到了记录 3,注意这个读的操作,是首先需要把存储在磁盘的记录读取到内存然后再比较 id 的,从磁盘读到内存算一次 IO,也就是说此过程中产生了三次 IO,如果只是几条记录还好,但如果要比较的条数多的话对性能是非常严重的挑战,如果我要查询为 id = 100 的记录那岂不是要产生 100 次 IO?既然瓶颈在 IO,那该怎么改进呢,很简单,我们现在的设计一次 IO 只能读一条记录,那改为一次 IO 能读取 100 条甚至更多不就只产生一次 IO 了吗,这背后的思想就是程序局部性原理:当用到了某项数据时,很可能会用到与之相邻的数据,所以干脆把相依的数据一起加载进去(你从 id = 1 开始读,那很可能用到 id = 1 紧随其后的元素,于是干脆把 id = 1 ~ id = 100 的记录都加载进去)

 

当然一次 IO 的读取记录也并不是多多益善,总不能为了一条查询记录而把很多无关的数据都加载到内存吧,那会造成资源的极大浪费,于是我们采用了一个比较折中的方案,我们规定一次 IO 读取 16 K 的数据,假设为 100 条数据好了,这样如果我们要查询 id = 100 的记录,只产生了一次 IO 读(id=1~id=100 的记录),比起原来的 100 次 IO 提升了 100 倍的性能

 你管这破玩意叫 B+ 树?(一)-开源基础软件社区
我们把这 16KB 的记录组合称为一个

 

页目录


一次 IO 会读取一个页,然后再在内存里查找页里的记录,在内存里查找确实比磁盘快多了,但我们仍不满意,因为如果要查找 id = 100 的记录,要先从  id = 1 的记录比较起,然后是id=2,…,id=100,需要比较 100 次,能否更快一点?

 

可以参照二分查找,先查找 id = (1+100)/2  = 50,由于 50 < 100,接着在  50~100 的记录中查,然后再在 75~100 中查,这样经过 7 次就可找到 id = 100 次的记录,比起原来的 100 次比较又提升了不少性能。但现在问题来了,第一次要找到 id =  50 的记录又得从 id = 1 开始遍历 50 次才能找到,能否一下就定位到 id=50 的记录呢,如果不能,哪怕第一次从 id = 30 或 40 开始查找也行啊

 

有什么数据结构能满足这种需求呢,还记得跳表不,每隔 n 个元素抽出一个组成一级索引,每隔 2*n 个元素组成二级索引。。。

 你管这破玩意叫 B+ 树?(一)-开源基础软件社区
如图示,以建立一级索引为例,我们在查找的时候先在一级索引查找,在一级索引里定位到了再到链表里查找,比如我们要找 7 这个数字,如果不用跳表直接在链表里查,需要比较 7 次,而如果用了跳表我们先在一级索引查找,发现只要比较 3 次,减少了四次,所以我们可以利用跳表的思想来减少查询次数,具体操作如下,每 4 个元素为一组组成一个槽(slot),槽只记录本组元素最大的那条记录以及记录本组有几条记录

 你管这破玩意叫 B+ 树?(一)-开源基础软件社区
现在假设我们想要定位 id = 9 的那条记录,该怎么做呢,很简单:首先定位记录在哪个槽,然后遍历此槽中的元素

  1. 定位在哪个槽,首先取最小槽和最大槽对应的 id(分别为 4, 12),先通过二分查找取它们的中间值为 (4+12)/2 = 8,8 小于 9,且槽 2 的最大 id 为 12,所以可知 id = 9 的记录在槽 2 里
  2. 遍历槽 2 中的元素,现在问题来了,我们知道每条记录都构成了一个单链表,而每个槽指向的是此分组中的最大 id 值,该怎么从此槽的第一个元素开始遍历呢,很简单,从槽 1 开始遍历不就行了,因为它指向元素的下一个元素即为槽 2 的起始元素,遍历后发现槽 2 的 第一个元素即为我们找到的 id 为 9 的元素

 

可以看到通过这种方式在页内很快把我们的元素定位出来了,MySQL 规定每个槽中的元素在 1~8 条,所以只要定位了在哪个槽,剩下的比较就不是什么问题了,当然一个页装的记录终究是有限的,如果页满了,就要要开辟另外的页来装记录了,页与页之间通过链表连接起来,但注意看下图,为啥要用双向链表连接起来呢,别忘了最开头我们列出的 「order by id asc 」和「order by id desc 」这两个查询条件,也就是说记录需要同时支持正序与逆序查找,这就是为什么要使用双向链表的原因

你管这破玩意叫 B+ 树?(一)-开源基础软件社区

文章转自公众号:码海

分类
标签
已于2022-5-26 18:38:47修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐