首页>国内 > 正文

天天观速讯丨为什么SQL语句命中索引比不命中索引要快?

2022-06-29 05:47:56来源:Tom弹架构

​有位粉丝面试高开的时候被问到,为什么SQL语句命中索引比不命中索引要快?虽然自己也知道答案,但被问到的瞬间,就不知道如何组织语言了。今天,我给大家深度分析一下。

1.索引的作用

想象一下,现在有一本包含几十万字的字典,有几百页厚,同时里面的字是无序排列的。如果在不使用目录的情况下,我们如何从字典中找出需要的字来呢?毫无疑问,我们只能一页一页地翻,显然,这是一项反人类的的工作。


(资料图)

我们必然想的是先看目录,然后,找到相关的字或者偏旁,然后,找到对应的页码再去查找想要找的文字,这样,效率就大大提高了。而事实上,目录就是一种索引,我们说的数据库索引思想和目录的思想一脉相承。

数据库索引最主要的作用就是帮助我们快速检索到想要的数据,从而不至于每次查询都做全局扫描。

假设不使用任何算法的情况下,我们要查询10万条记录中的某一条,在最坏的情况下需要遍历10万次。

但如果使用二分查找算法,则只需要进行log2 20000次,也就是14.287712次即可。这意味着我们只需对排序后的值进行14次搜索,就可以使用二分查找到想要的唯一值,常见的索引数据结构有B树和B+树。

下面我们,以MySQL的InnoDB引擎为例,分析一下索引的工作原理。

2.索引执行原理

我们知道MySQL的InnoDB引擎采用的是B+树数据结构,当我们去执行SELECT语句查询数据的时候,InnoDB需要从磁盘上去读取数据,而这个过程会涉及到磁盘 以及磁盘的随机IO ,我们来看这么一个图:

系统会把数据的逻辑地址传给磁盘,磁盘控制线路按照寻址逻辑把逻辑地址翻译成物理地址。也就是确定要读取的数据在哪个磁道、哪个扇区。为了读取这个扇区的数据,需要把磁头放在这个扇区上面,为了实现这样一个点,磁盘会不断地去旋转。把目标扇区旋转到磁头下面,使得磁头能够去找到对应的磁道。这里还会涉及到寻道的时间以及旋转时间的一个损耗。很明显磁盘IO这个过程的性能开销是非常大的,尤其是查询的数据量比较多的情况下。

所以InnotDB里面,干脆对存储在磁盘上的数据建立一个索引,然后把索引数据以及索引列对应的磁盘地址以B+树的方式进行存储。来看这么一个图:

当我们需要查找目标数据的时候,根据索引从B+树中去查找目标数据就行了。由于B+树的子树比较多,所以,只需要较少次数的磁盘IO就能够查找到目标数据。

至于B+树的数据结构,在这里就不分析了。大家可以去我的个人主页看往期视频有讲到。

3.索引的弊端

虽然,使用索引能减少磁盘IO次数,提高查询效率,但是,索引也不能建立太多。如果一个表中所有字段的索引很大,也会导致性能 l下降。想象一下,如果一个索引和一个表一样长,那么它将再次成为一个需要检查的开销。这就好比字典的目录非常详细,但是其长度已经和所有的文字一样长,这个时候目录本身的效率就大大下降了。

那索引有弊端吗?肯定是有的,索引可以提高查询读取性能,而它会将降低写入性能。当有索引时,如果更改一条记录,或者在数据库中插入一条新的记录,它将执行两个写入操作(一个操作是写入记录本身,另一个操作是将更新索引)。

因此,在定义索引时,必须牢记以下几点:

索引表中的每个字段将降低写入性能。建议使用表中的唯一值为字段编制索引。在关系数据库中充当外键的字段必须建立索引,因为它们有助于跨多个表进行复杂查询。索引还使用磁盘空间,因此在选择要索引的字段时要小心。

关键词: 的情况下 数据结构 逻辑地址 分析一下

相关新闻

Copyright 2015-2020   三好网  版权所有