当前位置:网站首页>【数据库】数据库建立索引的原因

【数据库】数据库建立索引的原因

2021-01-01 10:02:32 osc_8hhlaimy

一、 为什么加索引

  • 主要原因:可以利用二分查找大大加快数据的检索速度(B+树)

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

  • 可以加速表和表之间的连接

二、 如何加索引

1. 场景介绍

引入一个场景,以下面的表为例,这个表有5个字段,分别是id,name,time,subject和grade,我们要进行如下两种操作

  • 查询某一个人某一门科目在某一天考了多少分
  • 查询某一个人某一门科目在某个时间范围内分别考了多少分
字段名 字段含义
id 主键id
name 姓名
time 时间
subject 科目
grade 成绩

假设数据如下

id name time subject grade
1 Bob 3 English 95
2 Jack 3 English 98
3 Jack 3 Math 97
4 Bob 6 English 90
5 Bob 8 English 93
6 Bob 7 Math 100

假设我们现在需要查询Bob在第3天英语考了几分,由于存储结构乱序的,所以数据库内部只能一个个遍历然后比较name,subject和time是否满足条件,其复杂度为O(n),非常低效

select * from test_index 
where name = 'Bob' 
and time = 3 
and subject = 'English';

2. 加入索引

给数据表加索引其实就是给某个字段建立了一个排序表,相当于图书馆里给每本书进行编号,并按照顺序放在书架上,我们要想找到指定编号的图书时,只需要使用类似二分查找的方式,就可以快速找到对应的图书了。

二分查找时间复杂度为O(logn),当n为109时,大约只要执行30次对比,所以相比于O(n)的复杂度高效不少

加了索引之后,数据库会分配一个额外的空间,用来存储索引表,每次对数据库进行插入、删除和修改的时候也会修改索引表,所以增加索引之后,会增加数据库的大小和以及数据库修改的时间。因此如果数据库修改多而查询少,就要考虑增加索引的性价比。

举个例子,我们对上面的表中的name字段增加索引

ALTER TABLE `normandb`.`test_index` 
ADD INDEX `index`(`name`) USING BTREE

然后索引表就会变成如下般呈现

name id time subject grade
Bob 1 3 English 95
Bob 4 6 English 90
Bob 5 8 English 93
Bob 6 7 Math 100
Jack 2 3 English 98
Jack 3 3 Math 97

可以看到,索引表按照name进行了排序,这样的话,如果我们要按照name去进行查询的时候,能很快地在索引表里找到符合要求的name。

显而易见,之前我们的查询,需要给name,time和subject都加上索引,那是不是直接给这三个字段都加上索引就行了呢?

ALTER TABLE `normandb`.`test_index` 
ADD INDEX `index`(`name`) USING BTREE,
ADD INDEX `index2`(`time`) USING BTREE,
ADD INDEX `index3`(`subject`) USING BTREE

冷静分析,这其实是不可以的,如果为每一个字段各自建一个索引,那就会建立三个依照各自字段进行排序的索引表,当我们使用完name索引之后拿到的数据在time索引表中是乱序的,所以就无法二分查找了。

所以就要用到组合索引。

3. 组合索引

ALTER TABLE `normandb`.`test_index` 
ADD UNIQUE INDEX `index`(`name`, `time`, `subject`) USING BTREE;

用上面语句我们就可以建立一个基于name,time,subject三个字段的索引,数据库就会按照创建时的顺序,对各个字段进行排序建立索引表,如下所示

name time subject grade
Bob 3 English 95
Bob 6 English 90
Bob 7 Math 100
Bob 8 English 93
Jack 3 English 98
Jack 3 Math 97

这样的话,去查找我们需要的信息,只需要进行三次二分查找就行了,时间复杂度为O(k*logn)

PS : 可以注意到上面建立索引的时候用了UNIQUE INDEX,这个其实就是唯一索引,确定了唯一索引之后,数据表中这三个字段一模一样的数据只能出现一次,之后插入会执行失败,可以保证唯一性

4. 范围查询

如果我们想要知道Bob在第5天到第8天的英语成绩分别是多少,数据库语句应该是这样

select * from test_index 
where name = 'Bob' 
and time >= 5 and time <= 8
and subject = 'English';

这时候我们回去看看上面建立的组合索引,就会发现对time进行排序之后,筛选出的数据subject字段是乱序的,那么查询效率就又下降了不少了,因为subject字段相当于要在time字段相等的情况下才会保持有序。

所以增加索引的语句应该是这样

ALTER TABLE `normandb`.`test_index` 
ADD UNIQUE INDEX `index`(`name`, `subject`, `time`) USING BTREE;

三、 总结

  • 使用索引能够加快查询速度
  • 经常作为查询条件的字段可以设置索引
  • 设置索引会降低修改数据时的效率,不是越多越好
  • 在建立索引的时候:等值查询的字段,尽量放在范围查询的字段前面

版权声明
本文为[osc_8hhlaimy]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/4375296/blog/4872986