如何根据多列有效地选择NON-NULL、NON-EMPTY行

Question

下面的查询有效，但我想知道是否有优化它的方法？按照目前的情况，它将对每个OR语句进行多次表扫描？

我在查询中采取防御措施的原因是，如果有人在#{电话}或#{电子邮件}中传递NULL或空字符串，它将在这个过程中删除大量数据。因此，必须只为每个OR语句选择强匹配的值。

SELECT ID 
FROM...
WHERE (phone != '' AND phone IS NOT NULL AND phone = #{phone})
OR (phone2 != '' AND phone2 IS NOT NULL AND phone2 = #{phone})
OR (phone3 != '' AND phone3 IS NOT NULL AND phone3 = #{phone})
OR (email != '' AND email IS NOT NULL AND email = #{email})

Answer 1

我认为应该在应用层而不是数据库中清除参数phone。

然而，如果它像你提到的那样关键，有时采取一些安全措施是很方便的，比如你正在使用的措施。如果是这种情况，那么查询看起来不错。

从性能的角度来看，查询可以从索引中受益：

create index ix1 on t (phone);
create index ix2 on t (phone2);
create index ix3 on t (phone3);
create index ix4 on t (email);

现在，根据优化和数据直方图，引擎可能会选择避开索引。如果是这样的话，您可以通过使用UNION而不是OR(旧技巧(来重新表述查询，从而促进上述索引的使用。例如：

SELECT ID 
WHERE (phone != '' AND phone IS NOT NULL AND phone = #{phone})
FROM...
UNION
SELECT ID 
WHERE (phone2 != '' AND phone2 IS NOT NULL AND phone2 = #{phone})
FROM...
UNION
SELECT ID 
WHERE (phone3 != '' AND phone3 IS NOT NULL AND phone3 = #{phone})
FROM...
UNION
SELECT ID 
WHERE (email != '' AND email IS NOT NULL AND email = #{email})

当谓词中没有ORs时，引擎使用索引要容易得多。然而，这种技巧稍后在执行UNIONs时会付出代价。如果所选行的数量较低，则这种成本应该是边际的。