mysql - "Diamond"加入sql

Question

我有四个带有列的表格:

TABLE1: id
TABLE2: id, t4_ref, t1_ref
TABLE3: id, t4_ref, t1_ref
TABLE4: id, type

我想要做的是从第一个表获取ID，但前提是存在对该ID的TABLE2或TABLE3引用(在t1_ref列)并且TABLE4中的“类型”等于某物。

我不知道标题是否足够好，但这对我来说就像制造钻石 - 首先将两张表与一张表连接起来，然后将一张表与这两张表连接起来。

我不知道如何正确解决这个问题，我尝试过类似的方法:

SELECT COUNT(*) FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2 ON t1.id = t2.t1_ref
LEFT JOIN TABLE3 AS t3 ON t1.id = t3.t1_ref
LEFT JOIN TABLE4 AS t4 ON t2.t4_ref = t4.id OR t3.t4_ref = t4.id
WHERE t4.type = 'something';

还有:

SELECT COUNT(*) FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2 ON t1.id = t2.t1_ref
LEFT JOIN TABLE3 AS t3 ON t1.id = t3.t1_ref
LEFT JOIN TABLE4 AS t4 ON t2.t4_ref = t4.id
LEFT JOIN TABLE4 AS t4 ON t3.t4_ref = t4.id
WHERE t4.type = 'something';

查询运行，但速度太慢，我看不到结果，我想知道它是否真的一定那么慢，或者我做错了什么。请注意，每个表中有几十万条记录。有什么想法可以解决这个“钻石”连接问题吗？

Answer 1

我建议使用两个 exists 子句来执行此操作:

select t1.*
from table1 t1
where exists (select 1
              from table3 t3 join
                   table4 t4
                   on t3.t1_ref = t1.id and
                      t3.t4_ref = t4.id and t4.type = 'something'
             ) or
      exists (select 1
              from table2 t2 join
                   table4 t4
                   on t2.t1_ref = t1.id and
                      t2.t4_ref = t4.id and t4.type = 'something'
             );

为了提高效率，您需要以下索引:table2(t1_ref, t4_ref)、table3(t1_ref, t4_ref)、table4(id, type).