sql - 使用索引加速连接

Question

我知道使用索引可以帮助加速两个或多个表的连接。以下示例使用共享的 Department_id 列连接两个表 emps 和 depts:

select last_name, department_name 
from emps join depts 
using(department_id);

我的问题是:对两个表之一上的 Department_id 列进行索引会加快此查询的速度，还是必须在两个表的两个 Department_id 列上创建索引才能看到性能的提高？

Answer 1

这两个表自然会在 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)