社区编辑申请
注册/登录
为什么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

2022-05-27 21:56:55

2022-05-19 08:28:19

索引数据库

2019-07-09 14:42:17

SQLexplain索引

2022-03-03 09:05:17

索引MySQL数据查询

2016-12-12 13:07:57

数据库优化SQL

2022-04-14 08:21:35

2010-09-16 13:42:55

2020-08-10 11:20:59

索引MySQL数据库

2019-08-19 11:07:41

2021-12-29 07:01:53

2010-05-14 17:56:16

SQL优化索引

2010-07-07 10:31:43

SQL Server数

2022-02-11 19:06:29

2010-09-24 19:18:22

2020-03-05 16:55:56

索引数据库SQL

2020-04-26 09:48:11

MySQL数据库架构

2022-01-05 12:03:48

2019-12-04 07:39:07

2011-04-22 14:45:45

同话题下的热门内容

明明加了唯一索引,为什么还是产生重复数据?推荐几款好用的MySQL开源客户端,建议收藏字节二面,两个事务执行 SQL 语句的过程中,导致死锁不同数据库存储引擎技术的优劣势分析MySQL源码解析之执行计划

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号