我知道使用索引可以帮助加快两个或多个表的连接。以下示例使用共享department_id列联接两个表(emps 和 depts(:
select last_name, department_name
from emps join depts
using(department_id);
我的问题是:索引两个表之一上的department_id列会加快此查询速度,还是必须对两个表中的department_id列创建索引才能看到性能的提高?
这两个表自然已经有一个关于department_id
的索引,因为这应该是depts
主键和emps
外键。
但是,在您的查询中,不太可能使用索引。当 DBMS 最终要读取所有记录时,为什么要费心扫描索引树?例如,简单的顺序全表扫描,然后对哈希进行连接通常会快得多。
我们再看一个例子:
select e.last_name, d.department_name
from emps e
join depts d on d.department_id = e.department_id
where e.first_name = 'Laura';
在这里,我们只对少数员工感兴趣。这就是索引发挥作用的地方。我们想要一个关于emps(first_name)
的索引。然后我们将知道员工记录,department_id
,我们可以访问关联的dept
记录。
但是这样说,我们注意到我们使用索引来查找表记录来查找department_id
。从指数中获取正确department_id
不是更快吗?是的,会的。所以索引应该在emps(first_name, department_id)
.
主键depts
是department_id
,所以此列是索引的,我们可以很容易地找到带有部门名称的depts
记录。
但是我们可以再次问同样的问题:我们不能从索引中获取正确的名称吗?这导致我们覆盖包含查询中使用的所有列的索引。
所以,虽然
index idx_emps on emps(first_name, department_id)
index idx_depts on depts(department_id)
足够了,我们可以使用这些覆盖索引更快地获得查询:
index idx_emps on emps(first_name, department_id, last_name)
index idx_depts on depts(department_id, department_name)
您应该始终为 pk 和 fk 编制索引,以减少阻塞和争用,同时数据库强制实施一致性和完整性。
我还建议显式加入,而不是USING
。那里有太多的惯例和魔力:)