使用SELECT查询检查具有单个值的多个列

Question

我有一桌水果。现在在UI中，我为搜索条件提供了一个单独的字段。使用这个单一的搜索条件，我想在水果表的多个列中进行搜索。

考虑水果表包含列ID、Desc、Price、Quant、Stock。在这里，Price、Quant是整数，Stock是varchar。

我尝试了以下返回结果的查询，但我担心性能问题。

假设用户在UI中提供的字段中输入2并单击搜索，则查询将如下所示

select ID, Desc, Price, Quant, Stock 
from Fruits 
where Price = '2' 
or Quant = '2' 
or stock = '2' 

这是搜索同一个表的多列的正确方法吗？也会对性能有影响吗？

Answer 1

首先，您需要确保这些类型是兼容的。这些值很可能是数字，所以去掉引号：

select ID, Desc, Price, Quant, Stock 
from Fruits f
where Price = 2 or Quant = 2 or stock = 2; 

这可以更简单地写成：

select ID, Desc, Price, Quant, Stock 
from Fruits f
where 2 in (Price, Quant, Stock); 

但这不会对性能有所帮助。

在大多数数据库中，查询将需要全表扫描--尽管有些数据库支持一种称为跳过扫描的特殊类型的索引扫描，这可能会有所帮助。

我认为解决这个问题的唯一方法是在每一列上有一个单独的索引：

create index idx_fruits_price on fruits(price);
create index idx_fruits_quant on fruits(quant, price);
create index idx_fruits_stock on fruits(stock, quant, price);

(您将了解为什么额外的列很有帮助。)

然后使用union all

select ID, Desc, Price, Quant, Stock 
from Fruits f
where Price = 2
union all
select ID, Desc, Price, Quant, Stock 
from Fruits f
where quant = 2 and price <> 2
union all
select ID, Desc, Price, Quant, Stock 
from Fruits f
where stock = 2 and price <> 2 and stock <> 2;

每个子查询都可以使用其中一个索引。由于不等式的存在，结果是排他的--假设列值不是null。如果允许使用null，则可以调整逻辑以处理此问题。