数据库索引:如何提高查询效率
今天我们来讲一下数据库索引,关于什么情况下索引会生效以及如何建立正确的索引,相信这对广大开发者来说都非常实用。
什么时候索引会生效
我们只需要在查询语句的 WHERE
子句中使用了与索引前缀匹配的等式条件,或者查询中使用了索引的第一个属性的不等式条件时,索引就会生效。
例如,假设我们有一个如下的索引:
create index i on T(a, b, c)
那么以下两个查询语句会使用索引:
select *
from T t
where t.a=1 and t.c = 2;
select *
from T t
t t.a > 1;
但以下两个查询语句却不会使用索引:
select *
from T t
where t.b=1 and t.c=2;
select *
from T t
where t.b > 1
如何建立正确的索引
接下来我们来讨论一下如何建立正确的索引。下面以一张包含四个表的数据库为例,我们来看一下两个查询语句:
假设:
Shops
来自不同国家Purchase
存储了多年的记录
查询语句一:
select count(*)
from Shop sh, Purchase pu
where pu.date>='2020-1-1' and
sh.country = 'Belgium' and
pu.psid = sh.sid;
查询语句二:
select count(*)
from Shop sh, Purchase pu
where pu.date>='2020-1-1' and
sh.country = 'China' and
pu.psid = sh.sid;
根据查询语句的不同,我们需要采用不同的索引策略,具体如下:
- 查询语句一
在这种情况下,Shops
来自不同国家,而 Purchase
存储了多年的记录。因此,我们可以推断出,Purchase
表中的数据量会很大,并且数据会比较分散。在这种情况下,我们需要一个更精确的索引来提高查询性能。因此,最佳索引是:
Shop(sid)
Purchase(psid, date)
这样,我们可以通过 psid
和 date
快速定位到 Purchase
表中的相关记录,并通过 sid 在 Shop
表中找到相关的商店记录。
- 查询语句二
在这种情况下,Shops
只来自“Belgium” 和 “China”,而 Purchase
仅存储了近两年的记录。这意味着 Purchase
表中的数据量较小,且数据的分布较为集中。在这种情况下,我们可以使用较为简单的索引:
Shop(sid)
Purchase(date)
由于数据集中,我们只需要根据 date
在 Purchase
表中快速定位到相关记录,然后通过 sid
在 Shop
表中找到相关的商店记录。这里,我们不需要在 Purchase
表中使用额外的psid
索引,因为 date
索引已经足够高效。
那么为什么不在第二个查询语句中使用 Purchase(date, psid)
呢?假设使用 Purchase(date, psid)
,那么首先查询到得是符合 date 条件的记录,接着查询符合 psid
的记录。符合psid
的记录会很分散,因为有太多的 Shop
。而如果使用 Purchase(psid, date)
,首先查询到的是符合 psid
的记录,而符合 date 的记录也是连续存在的。
date | psid | psid | date | |
---|---|---|---|---|
2020-01-02 | 1 | 1 | 2020-01-02 | |
2020-01-02 | 2 | 1 | 2020-01-03 | |
2020-01-02 | 3 | 2 | 2020-01-02 | |
2020-01-02 | 4 | 2 | 2020-01-03 | |
2020-01-02 | 5 | 3 | 2020-01-02 | |
2020-01-02 | 6 | 3 | 2020-01-03 | |
2020-01-02 | 7 | 4 | 2020-01-02 | |
2020-01-02 | 8 | 4 | 2020-01-03 | |
2020-01-02 | 9 | 5 | 2020-01-02 | |
2020-01-03 | 1 | 5 | 2020-01-03 | |
2020-01-03 | 2 | 6 | 2020-01-02 | |
2020-01-03 | 3 | 6 | 2020-01-03 | |
2020-01-03 | 4 | 7 | 2020-01-02 | |
2020-01-03 | 5 | 7 | 2020-01-03 | |
2020-01-03 | 6 | 8 | 2020-01-02 | |
2020-01-03 | 7 | 9 | 2020-01-02 |
END
最后,我们要提醒一下,索引的效率是和数据的特征有关的。我们需要根据具体的业务需求和数据特征来选择最合适的索引,才能达到最优的查询效率。
下一篇文章,我们将使用 SQLite
数据库来具体说明索引是如何起作用的,敬请期待!