两个MySQL表的最优连接

Question

我有一个表(T1)，其中包含大约500000条非重复记录：

ID1    Relation  ID2
4      Rel4      13
5      Rel5       4
13     Rel13     16
16     Rel16     5

我有属性表T1_Prop：

ID    Entity    
4     Ent4     
5     Ent5
13    Ent13   
16    Ent16  

我想以一种高效的方式连接这两个表(基于id : 4)，如下所示：

 Entity   Relation   Entity
 Ent4      Rel4      Ent13  
 Ent5      Rel5      EntEnt4

我设计了这个select语句，包括JOIN语句，它工作得很好。然而，我不确定这是否是最好的方法：

select 
  a.entity, 
  r.relation, 
  b.entity 
from T1 as r 
INNER JOIN T1_Prop as a ON a.ID=r.ID1 AND (r.ID1=4 OR r.ID2=4) 
INNER JOIN T1_Prop as b ON b.ID=r.ID2;

Answer 1

这是SQL的一个很好的用法。它就是为这种查询而构建的。

在T1上，您需要两个覆盖索引来加快速度。它们是：

(ID1, ID2, relation)

和

(ID2, ID1, relation)

这两个索引用于处理OR子句。这是我看到的唯一一个潜在的性能问题，这只是因为OR操作有时会欺骗查询规划器进行过多的表扫描。

尝试重构您的查询，使您选择的ID值更可见。

select   a.entity, r.relation, b.entity 
  from T1 as r 
 INNER JOIN T1_Prop as a ON a.ID=r.ID1  
 INNER JOIN T1_Prop as b ON b.ID=r.ID2
 WHERE (r.ID1=4 OR r.ID2=4) 

然后，如果您在性能方面遇到问题，那么在创建覆盖索引之后，再次重构它以

select   a.entity, r.relation, b.entity 
  from T1 as r 
 INNER JOIN T1_Prop as a ON a.ID=r.ID1  
 INNER JOIN T1_Prop as b ON b.ID=r.ID2
 WHERE r.ID1=4 
UNION
select   a.entity, r.relation, b.entity 
  from T1 as r 
 INNER JOIN T1_Prop as a ON a.ID=r.ID1  
 INNER JOIN T1_Prop as b ON b.ID=r.ID2
 WHERE r.ID2=4 

Answer 2

除了第一个ON子句之外，您的查询看起来还不错。条件(r.ID1=4 OR r.ID2=4)不是用于将T1_Prop中的哪些记录联接到T1记录的规则。相反，它是一个条件，T1记录了要考虑的条件，因此它属于WHERE子句。

select 
  a.entity AS entity1, 
  r.relation, 
  b.entity AS entity2
FROM t1 AS r 
INNER JOIN t1_prop AS a ON a.id = r.id1
INNER JOIN t1_prop AS b ON b.id = r.id2
WHERE r.id1 = 4 OR r.id2 = 4;

这不会改变执行计划；DBMS仍将执行此操作。但它更具可读性，因为它显示了实际意图:获取其中一个ID为4的关系，并将实体连接到这些关系。

显示此意图的另一个选项是：

select 
  a.entity AS entity1, 
  r.relation, 
  b.entity AS entity2
FROM (SELECT * FROM t1 WHERE r.id1 = 4 OR r.id2 = 4) AS r 
INNER JOIN t1_prop AS a ON a.id = r.id1
INNER JOIN t1_prop AS b ON b.id = r.id2;

有些人认为FROM中的子查询可读性较差，但其他人并非如此。当查询变得更复杂时，如果您甚至处理来自不同表的聚合，这通常是构建干净查询的方法。

上述两个查询实际上都不比另一个更好或更差。