js 递归查询所有的叶子结点_浅谈mysql的查询过程

傷城~ 2023-01-03 14:11 51阅读 0赞

## 步骤 ##

查询过程上看，大致步骤如下：

1.  查看缓存中是否存在id
2.  如果有则从内存中访问，否则要访问磁盘
3.  将索引数据存入内存，利用索引来访问数据
4.  对于数据也会检查数据是否存在于内存
5.  如果没有则访问磁盘获取数据，读入内存
6.  返回结果

## 遍历索引(B+树) ##

### 主键索引**(聚集索引)** ###

从上至下遍历一次B+树，直到找到具体的主键，拿到叶子结点存储的数据。

### **辅助索引(二级索引)** ###

需要遍历两次B+树，第一次遍历是找到对应的主键，第二次遍历是根据主键找到具体的数据。

比如查询二级索引的sql，先通过遍历二级索引的B+树来找到对应的主键，然后回表即通过主键遍历聚集索引B+树，拿到具体的数据。(mysql里面每次新建索引都会生成新的B+树，这也是索引文件会随着索引字段不断增加的原因)

### **回表** ###

通过辅助索引拿到主键id之后，要再去遍历聚集索引的B+树，这个过程就叫做回表。回表的操作更多的是随机io，随机io在性能上还是比较低下的。

比如user表中有三个字段，a,b,c，给a和b建立联合索引idex\_a\_b(a,b)sql：select \* from user where a=1 and b=2;

1.  用二级索引index\_a\_b来查询，速度会很快。(顺序IO)
2.  拿到主键id之后，这个主键id并不是顺序排列的，还要用主键去查询聚簇索引(随机io)
3.  当随机io很多，也就是拿到的主键id很多的时候，回表的代价是巨大的。

所以最好是选用覆盖索引或者让where 之后的条件筛选更多的数据。

## io ##

B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。

如果不存储数据，那么就会存储更多的键值，相应的树的阶数(节点的子节点树)就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

另外，B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

因为 B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。

那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

以下sql为例

select * from user where id>=18 and id <40

![5a86e0e2f806d51535ced729d124586b.gif][]

### 主键索引**(聚集索引)** ###

![122e428002b043990be41d0670515514.png][]

![5a86e0e2f806d51535ced729d124586b.gif][]

一般根节点都是常驻内存的，也就是说页 1 已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。

从内存中读取到页 1，要查找这个 id>=18 and id <40 或者范围值，我们首先需要找到 id=18 的键值。

从页 1 中我们可以找到键值 18，此时我们需要根据指针 p2，定位到页3。

要从页 3 中查找数据，我们就需要拿着 p2 指针去磁盘中进行读取页 3。

从磁盘中读取页 3 后将页 3 放入内存中，然后进行查找，我们可以找到键值 18，然后再拿到页 3 中的指针 p1，定位到页 8。

同样的页 8 页不在内存中，我们需要再去磁盘中将页 8 读取到内存中。

将页 8 读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值 18。

此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值 18 对应的数据。

因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

我们可以一直找到键值为 22 的数据，然后页 8 中就没有数据了，此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

因为页 9 不在内存中，就又会加载页 9 到内存中，并通过和页 8 中一样的方式进行数据的查找，直到将页 12 加载到内存中，发现 41 大于 40，此时不满足条件。那么查找到此终止。

### **辅助索引(二级索引)** ###

查找的流程跟聚集索引一样，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。

辅助索引获取主键的时间复杂度是 lognN(假设第二层级是n个节点)，再通过lognN获取主键对应的数据列。

[5a86e0e2f806d51535ced729d124586b.gif]: /images/20221119/d92094b94d694bd3b73bc98d4fee3150.png
[122e428002b043990be41d0670515514.png]: /images/20221119/1b06a297e3a844fb9e4e8dd9103ab8c7.png

发表评论取消回复

表情：

评论列表（有 0 条评论，51人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL 子结点查询所有父结点（查询结果包含自身结点）

MySQL 子结点查询所有父结点（查询结果包含自身结点） SQL代码 SELECT t2.id, t2.`menuname` FROM

傷城~/ 2023年10月05日 23:20/ 0 赞/ 60 阅读

相关 mysql递归查询所有下级

mysql递归查询所有下级的sql 解决方法：假如tb\_manage\_user表中uid是主键，creator\_id是存储父级的uid，uid为10的所有子级

柔光的暖阳◎/ 2023年06月25日 06:05/ 0 赞/ 42 阅读

相关 js 递归查询所有的叶子结点_浅谈mysql的查询过程

步骤查询过程上看，大致步骤如下： 1. 查看缓存中是否存在id 2. 如果有则从内存中访问，否则要访问磁盘 3. 将索引数据存入内存，利用索引来访问数据 4.

傷城~/ 2023年01月03日 14:11/ 0 赞/ 52 阅读

相关 mysql 递归查询

SELECT id AS ID,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM ( SELECT

淡淡的烟草味﹌/ 2022年12月17日 08:58/ 0 赞/ 196 阅读

相关 mysql存储过程递归查询

根据父级点查询子节点 create function queryChildrensId(orgid char(255)) returns varcha

客官°小女子只卖身不卖艺/ 2022年11月21日 04:18/ 0 赞/ 221 阅读

相关 MySQL 递归查询

初始化脚本 CREATE TABLE t_depart ( id int, pid int, name VARCHAR(100) );

淩亂°似流年/ 2022年11月15日 12:58/ 0 赞/ 50 阅读

相关 MySQL递归查询

mysql的逆袭:如何做递归层次查询最近在做一个从oracle [数据库][Link 1]到my [sql数据库][Link 1]的移植，遇到一个这样的问题

﹏ヽ暗。殇╰゛Y/ 2022年08月20日 12:26/ 0 赞/ 248 阅读

相关求二叉树叶子结点的递归实现

/求二叉树叶子结点的递归实现/ include<stdio.h> typedef struct btnode { char da

布满荆棘的人生/ 2022年06月15日 04:52/ 0 赞/ 232 阅读

相关 mysql 递归查询

比较靠谱已验证OK [https://www.cnblogs.com/xiaoxi/p/5942805.html][https_www.cnblogs.com_xiaoxi

喜欢ヅ旅行/ 2021年12月16日 16:55/ 0 赞/ 384 阅读

相关 Mysql递归查询

在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但是，在MySQL的目前版本

柔情只为你懂/ 2021年05月12日 11:50/ 0 赞/ 661 阅读