Informix DB 上索引（索引？）的使用

Question

我的 Informix 数据库中有一个包含超过 500 万条记录的表。

这不是实际的表格，但会显示我遇到的问题。

表：销售额
列：sale_id、sale_confirmed、vendor_id、purchase_id
索引： idx1(sale_id)、idx2(sale_confirmed)、idx3(vendor_id)、idx4(purchaser_id)

如果我执行这样的查询：

select *  
from sales  
where sale_confirmed IS NULL  
or sale_confirmed = '' 

那么查询将在大约 4 或 5 秒内完成。

如果我执行这样的查询：

select *  
from sales  
where vendor_id = 12345 
or purchaser_id = 12345

那么查询将在大约 4 或 5 秒内完成。

但是，如果我运行此查询（前 2 个查询的组合）：

select *  
from sales  
where (sale_confirmed IS NULL  
        or sale_confirmed = '' )
and (vendor_id = 12345 
       or purchaser_id = 12345)

则该查询运行了 15 分钟，然后我取消了它。

数据库似乎不够智能，无法串联使用不同的索引，即它似乎无法使用 idx2 查找 X 行并在该 X 行中使用 idx3 和 idx4 - 是这样吗，我本以为这样做会足够聪明吗？

有没有办法在处理 WHERE 子句的第二部分时强制数据库使用 idx3 和 idx4？

除了创建新索引之外还有其他解决方案吗？

谢谢。

Answer 1

尝试使用 UNION，其中每个部分只需选择两个索引：

select *  
from sales  
where (sale_confirmed IS NULL  
        or sale_confirmed = '' )
and vendor_id = 12345


UNION

select *  
from sales  
where (sale_confirmed IS NULL  
        or sale_confirmed = '' )
and purchaser_id = 12345

如果 Informix 支持内联视图，则根据供应商/购买者获取行集，然后从该集中排除未经确认的销售。

select inlineview.*  from
(
select *  from sales  
where vendor_id = 12345 or purchaser_id = 12345
) as inlineview
where (sale_confirmed IS NULL or sale_confirmed = '')

最后，我认为您可能希望删除 sale_confirmed 上的低基数索引。

PS 我的数据库中通常不会有允许空字符串、NULL 和其他值的列。如果你有的话，我会将该字段限制为 BIT 类型，即 1 和 0，默认值为 0。

Answer 2

“sale_confirmed”上的索引不太可能有用，因为“sale_confirmed”的基数似乎很低（NULL，是，否？）。更好的模式设计会在“sale_confirmed”上强制执行 NOT NULL，并且 CHECK 约束会强制执行“Y”或“N”，默认情况下可能会给您“N”，除非您另外指定。这将避免必须对“sale_confirmed”进行 OR 运算，这很混乱。

Tim 建议的 UNION 技术可能是一个不错的解决方法。