如何通过条件连接优化查询？

Question

我需要优化一个查询，其中一个连接取决于条件。

我有两个表格

Table1有两列，我们称之为A和B列，可以与table2的C列相关联，

如果B列为空，我必须将t1.A与t2.C匹配。 如果B列不为空，我必须将t1.B与t2.C匹配

最后，我需要知道t1上哪些条目没有在t2中找到匹配...

更详细地说，t1是客户表，A和B都是客户代码。 A代码永远不会与B代码相同，在存在B的情况下，B具有优先权（B是新的客户代码，但旧客户没有它。如果B为空，则使用A作为代码）（所有这些因为B列是新的，而旧客户对B列具有空值）。

t2是购买表。t2.C是客户代码，但在这种情况下是单列，它为旧客户存储A代码，为新客户存储B代码。

我唯一想要的就是知道哪些客户还没有任何购买记录，用尽可能高效的查询方式。

我想出了几个查询，但它们非常慢，我想是因为处理条件的方式：

第一次尝试：

select * 
from t1
left join t2 on (t1.A = t2.C or t1.B = t2.C)
where t2.D is null;

请注意，我可以使用OR条件，因为我知道t1.A永远不会与任何t1.B相同，所以在t2.C中，它只能与A或B匹配，但永远不会同时匹配（假设该条件得到保证）。查询非常缓慢，在我的SQL客户端中超时了。

第二次尝试

select * 
from t1
left join t2 on (if(t1.B is null, t1.A = t2.C, t1.B = t2.C))
where t2.D is null;

在这种情况下，比较条件取决于t1.B，如果为null，则与A进行比较，如果不是，则与B进行比较。 同样，查询非常缓慢。

我想我可以只使用两个连接，并对每个连接使用每个条件（A或B），但我不知道如何实现它，特别是因为我只需要获取那些t2上没有匹配项的t1客户端。 （也就是说，在t1上没有t2购买记录的客户端）

有什么更有效的查询建议吗？

谢谢

- DiegoDD

Answer 1

如果 t1.A 或者 t1.B 上没有索引，我建议您使用 IFNULL 函数：

select * 
from t1
left join t2 on ifnull(t1.B, t1.A) = t2.C
where t2.D is null;

然而，如果任何一列被索引，我认为你最好使用 UNION ALL 来获得最佳性能：

select * 
from t1
left join t2 on t1.A = t2.C
where t2.D is null
and t1.B is null
union all
select * 
from t1
left join t2 on t1.B = t2.C
where t2.D is null
and t1.B is not null;

编译期间优化器无法确定在连接时使用t1.A还是t1.B，因此无法选择索引并选择了表扫描。但如果将其分成两个查询，则知道在连接时使用哪个列，并且可以使用相应的索引。请参见SQL Fiddle示例。

- GarethD

---

谢谢！第一个版本（使用ifnull）工作得很好，我看到的结果与我第二次尝试非常相似，在第二次尝试中，我使用了 on (if(t1.B is null, t1.A = t2.C, t1.B = t2.C)) 而不是你的 on ifnull(t1.B, t1.A) = t2.C。是什么使它们如此不同，以至于你的版本比我的高效得多？我觉得这真的很有趣。事实上，你的ifnull版本比union all更快（0.2秒对比0.6秒），即使我为所有涉及的列（A、B和C）都有索引。 - DiegoDD

IFNULL 比你的 if 语句工作得更好的原因是因为列 t2.C 不包含在其中，它是静态的，所以 MySQL 知道它可以使用这个列上的索引，而在你的 if 语句中，它无法确定 t2.C 在真和假表达式中都被使用，因此不使用索引。 - GarethD

哦，现在我明白了！所以问题不是简单地使用ifnull(x,y)而不是if(x is null,y,x)，而是因为比较的列在if之外。因此，使用(if(t1.B is null, t1.A, t1.B) = t2.C)与使用ifnull()完全相同。谢谢！ - DiegoDD

接受了这个答案，但Gordon Linoff的答案也是正确的，事实上如果有索引，它运行得更快。 - DiegoDD

UNION ALL 这个技巧对我非常有用！ - Vítor Martins

Answer 2

哎呀，进行条件连接往往会导致查询性能非常差。在这种情况下，您正在测试两个值并且可能希望查看是否不存在任何一个值。尝试将其拆分为两个连接：

select * 
from t1 left join
     t2
     on t1.A = t2.C left join
     t2 t2a
     on t1.B = t2a.C
where t2.D is null and t2a.D is null;

这将允许查询使用 A、B 和 C 上的索引。

- Gordon Linoff