写点什么

阿里一面,给了几条 SQL,问需要执行几次树搜索操作?

作者:Java高工P7
  • 2021 年 11 月 12 日
  • 本文字数:3106 字

    阅读完需:约 10 分钟

  • 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录,可以帮你快速找到对应的记录。

  • 索引一般存储在磁盘的文件中,它是占用物理空间的。

  • 正所谓水能载舟,也能覆舟。适当的索引能提高查询效率,过多的索引会影响数据库表的插入和更新功能。

二、索引有哪些类型类型

数据结构维度

  • B+树索引:所有数据存储在叶子节点,复杂度为 O(logn),适合范围查询。

  • 哈希索引: 适合等值查询,检索效率高,一次到位。

  • 全文索引:MyISAM 和 InnoDB 中都支持使用全文索引,一般在文本类型 char,text,varchar 类型上创建。

  • R-Tree 索引: 用来对 GIS 数据类型创建 SPATIAL 索引

物理存储维度

  • 聚集索引:聚集索引就是以主键创建的索引,在叶子节点存储的是表中的数据。

  • 非聚集索引:非聚集索引就是以非主键创建的索引,在叶子节点存储的是主键和索引列。

逻辑维度

  • 主键索引:一种特殊的唯一索引,不允许有空值。

  • 普通索引:MySQL 中基本索引类型,允许空值和重复值。

  • 联合索引:多个字段创建的索引,使用时遵循最左前缀原则。

  • 唯一索引:索引列中的值必须是唯一的,但是允许为空值。

  • 空间索引:MySQL5.7 之后支持空间索引,在空间索引这方面遵循 OpenGIS 几何数据模型规则。

三、面试官考点之为什么选择 B+树作为索引结构

可以从这几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数等等。为什么不是哈希结构?为什么不是二叉树,为什么不是平衡二叉树,为什么不是 B 树,而偏偏是 B+树呢?


我们写业务 SQL 查询时,大多数情况下,都是范围查询的,如下 SQL


select * from employee where age between 18 and 28;

为什么不使用哈希结构?

我们知道哈希结构,类似 k-v 结构,也就是,key 和 value 是一对一关系。它用于等值查询还可以,但是范围查询它是无能为力的哦。

为什么不使用二叉树呢?

先回忆下二叉树相关知识啦~ 所谓二叉树,特点如下:


  • 每个结点最多两个子树,分别称为左子树和右子树。

  • 左子节点的值小于当前节点的值,当前节点值小于右子节点值

  • 顶端的节点称为根节点,没有子节点的节点值称为叶子节点。


我们脑海中,很容易就浮现出这种二叉树结构图:



但是呢,有些特殊二叉树,它可能这样的哦:



如果二叉树特殊化为一个链表,相当于全表扫描。那么还要索引干嘛呀?因此,一般二叉树不适合作为索引结构。

为什么不使用平衡二叉树呢?

平衡二叉树特点:它也是一颗二叉查找树,任何节点的两个子树高度最大差为 1。所以就不会出现特殊化一个链表的情况啦。



但是呢:


  • 平衡二叉树插入或者更新是,需要左旋右旋维持平衡,维护代价大

  • 如果数量多的话,树的高度会很高。因为数据是存在磁盘的,以它作为索引结构,每次从磁盘读取一个节点,操作 IO 的次数就多啦。

为什么不使用 B 树呢?

数据量大的话,平衡二叉树的高度会很高,会增加 IO 嘛。那为什么不选择同样数据量,高度更矮的 B 树呢?



B 树相对于平衡二叉树,就可以存储更多的数据,高度更低。但是最后为甚选择 B+树呢?因为 B+树是 B 树的升级版:


  • B+树非叶子节点上是不存储数据的,仅存储键值,而 B 树节点中不仅存储键值,也会存储数据。innodb 中页的默认大小是 16KB,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的 IO 次数有会再次减少,数据查询的效率也会更快。

  • B+树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链表连着的。那么 B+树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。

四、面试官考点之一次 B+树索引搜索过程

面试官:?假设有以下表结构,并且有这几条数据


CREATE TABLE employee (id int(11) NOT NULL,name varchar(255) DEFAULT NULL,age int(11) DEFAULT NULL,date datetime DEFAULT NULL,sex int(1) DEFAULT NULL,PRIMARY KEY (id),KEY idx_age (age) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into employee values(100,'小伦',43,'2021-01-20','0');insert into employee values(200,'俊杰',48,'2021-01-21','0');insert into employee values(300,'紫琪',36,'2020-01-21','1');insert into employee values(400,'立红',32,'2020-01-21','0');insert into employee values(500,'易迅',37,'2020-01-21','1');insert into employee values(600,'小军',49,'2021-01-21','0');insert into employee values(700,'小燕',28,'2021-01-21','1');


面试官:?如果执行以下的查询 SQL,需要执行几次的树搜索操作?可以画下对应的索引结构图~


select * from Temployee where age=32;


解析:?其实这个,面试官就是考察候选人是否熟悉 B+树索引结构图。可以像酱紫回答~


  • 先画出idx_age索引的索引结构图,大概如下:



  • 再画出 id 主键索引,我们先画出聚族索引结构图,如下:



因此,这条 SQL 查询语句执行大概流程就是酱紫:


  • 搜索idx_age索引树,将磁盘块 1 加载到内存,由于 32<37,搜索左路分支,到磁盘寻址磁盘块 2。

  • 将磁盘块 2 加载到内存中,在内存继续遍历,找到 age=32 的记录,取得 id = 400.

  • 拿到 id=400 后,回到 id 主键索引树。

  • 搜索id主键索引树,将磁盘块 1 加载内存,在内存遍历,找到了 400,但是 B+树索引非叶子节点是不保存数据的。索引会继续搜索 400 的右分支,到磁盘寻址磁盘块 3.

  • 将磁盘块 3 加载内存,在内存遍历,找到 id=400 的记录,拿到 R4 这一行的数据,好的,大功告成。


因此,这个 SQL 查询,执行了几次树的搜索操作,是不是一步了然了呀。特别的,在idx_age二级索引树找到主键id后,回到 id 主键索引搜索的过程,就称为回表。


什么是回表?拿到主键再回到主键索引查询的过程,就叫做回表

五、面

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


试官考点之覆盖索引


面试官:?如果不用select *, 而是使用select id,age,以上的题目执行了几次树搜索操作呢?


解析:?这个问题,主要考察候选人的覆盖索引知识点。回到idx_age索引树,你可以发现查询选项 id 和 age 都在叶子节点上了。因此,可以直接提供查询结果啦,根本就不需要再回表了~



覆盖索引:在查询的数据列里面,不需要回表去查,直接从索引列就能取到想要的结果。换句话说,你 SQL 用到的索引列数据,覆盖了查询结果的列,就算上覆盖索引了。


所以,相对于上个问题,就是省去了回表的树搜索操作。

六、面试官考点之索引失效

面试官:?如果我现在给name字段加上普通索引,然后用个 like 模糊搜索,那会执行多少次查询呢?SQL 如下:


select * from employee where name like '%杰伦 %';


解析:?这里考察的知识点就是,like 是否会导致不走索引,看先该 SQL 的 explain 执行计划吧。其实 like 模糊搜索,会导致不走索引的,如下:



因此,这条 SQL 最后就全表扫描啦~日常开发中,这几种骚操作都可能会导致索引失效,如下:


  • 查询条件包含 or,可能导致索引失效

  • 如何字段类型是字符串,where 时一定用引号括起来,否则索引失效

  • like 通配符可能导致索引失效。

  • 联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。

  • 在索引列上使用 mysql 的内置函数,索引失效。

  • 对索引列运算(如,+、-、*、/),索引失效。

  • 索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效。

  • 索引字段上使用 is null, is not null,可能导致索引失效。

  • 左连接查询或者右连接查询查询关联的字段编码格式不一样,可能导致索引失效。

  • mysql 估计使用全表扫描要比使用索引快,则不使用索引。

七、面试官考点联合索引之最左前缀原则

面试官:?如果我现在给 name,age 字段加上联合索引索引,以下 SQL 执行多少次树搜索呢?先画下索引树?


select * from employee where name like '小 %' order by age desc;


解析:?这里考察联合索引的最左前缀原则以及 like 是否中索引的知识点。组合索引树示意图大概如下:

用户头像

Java高工P7

关注

还未添加个人签名 2021.11.08 加入

还未添加个人简介

评论

发布
暂无评论
阿里一面,给了几条SQL,问需要执行几次树搜索操作?