数据库索引：如何提高查询效率

数据库索引：如何提高查询效率

今天我们来讲一下数据库索引，关于什么情况下索引会生效以及如何建立正确的索引，相信这对广大开发者来说都非常实用。

什么时候索引会生效

我们只需要在查询语句的 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

如何建立正确的索引

接下来我们来讨论一下如何建立正确的索引。下面以一张包含四个表的数据库为例，我们来看一下两个查询语句：

假设：

1. Shops 来自不同国家
2. 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;

根据查询语句的不同，我们需要采用不同的索引策略，具体如下：

1. 查询语句一

在这种情况下，Shops 来自不同国家，而 Purchase 存储了多年的记录。因此，我们可以推断出，Purchase 表中的数据量会很大，并且数据会比较分散。在这种情况下，我们需要一个更精确的索引来提高查询性能。因此，最佳索引是：

Shop(sid)
Purchase(psid, date)

这样，我们可以通过 psid 和 date 快速定位到 Purchase 表中的相关记录，并通过 sid 在 Shop 表中找到相关的商店记录。

2. 查询语句二

在这种情况下，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 数据库来具体说明索引是如何起作用的，敬请期待！