为什么SQL语句命中索引比不命中索引要快?

数据库 MySQL
想象一下,现在有一本包含几十万字的字典,有几百页厚,同时里面的字是无序排列的。如果在不使用目录的情况下,我们如何从字典中找出需要的字来呢?

​有位粉丝面试高开的时候被问到,为什么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下降。想象一下,如果一个索引和一个表一样长,那么它将再次成为一个需要检查的开销。这就好比字典的目录非常详细,但是其长度已经和所有的文字一样长,这个时候目录本身的效率就大大下降了。

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

图片

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

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

责任编辑:武晓燕 来源: Tom弹架构
相关推荐

2021-06-28 07:13:35

SQL语句索引

2022-09-28 07:31:59

索引数据库查询

2014-12-05 10:10:44

DockerRocketCoreOS

2010-10-19 16:06:26

SQL Server索

2020-01-22 16:36:52

MYSQL开源数据库

2021-12-13 01:40:29

ElasticSear倒排索引

2023-09-22 10:05:32

2019-09-09 10:36:21

C语言编程语言程序员

2021-02-03 08:52:52

Mysql索引数据库

2020-11-27 06:58:24

索引

2020-03-05 16:55:56

索引数据库SQL

2019-11-14 16:23:07

MySQL索引数据库

2019-07-29 09:42:56

索引死锁MySQL

2021-12-21 06:09:05

Python切片索引

2020-10-29 09:19:11

索引查询存储

2010-07-19 16:36:13

SQL Server视

2010-07-14 15:04:53

SQL Sever索引

2010-06-11 17:13:34

MySQL表索引

2020-08-10 11:20:59

索引MySQL数据库

2022-03-28 08:24:52

MySQL聚簇索引非聚簇索引
点赞
收藏

51CTO技术栈公众号