复合索引、单一索引和重复列索引

Question

我有以下问题。DataStaging是一个有1000万行的大表。

Product is一个较小的表有1000行。

我们需要使用ref来查找product_id，而Product表上有两个ref (ref1,ref2)，因此必须连接该表两次。

    UPDATE  dbo.DataStaging
    SET     ProductId = COALESCE(Ref1_Pid, Ref2_Pid, 0) 
    FROM    dbo.DataStaging s
            LEFT JOIN ( SELECT  id [Ref1_Pid] ,
                                Ref1
                        FROM    dbo.Product
                        WHERE   isActive = 1
                      ) p1 ON s.[Ref] = p1.Ref1
            LEFT JOIN ( SELECT  id [Ref2_Pid] ,
                                Ref2
                        FROM    dbo.Product
                        WHERE   IsActive = 1
                      ) p2 ON s.[Ref] = p1.Ref2
           
    WHERE   s.TypeId = 1
            AND s.StatusId = 2

这是产品表PK_Product上的主键，我可以自由添加Non_Clustered索引。

(1)三个索引: NC_index on (IsActive)，NC_Index on (Ref1)，NC_Index on (Ref2)

(2)两个综合指数: NC_Index on (IsActive，Ref1)，NC_Index on (IsActive，Ref2)

(3)一个综合指数: NC_Index on (IsActive，Ref1，Ref2)

对于(1)，它使用主键PK_Product扫描表，但不使用NC索引。

对于(2)，它对每个索引使用NC_index扫描。

对于(3)，它对相同的索引使用NC_index扫描，但行大小是(2)的两倍

结果，性能(2) > (3) > (1)

我的问题是，

为什么(1)不扫描NC索引？

如果创建像(2)或(3)这样的索引有什么缺点？

假设上面的查询是Product中最繁重的进程，但是有数百个stored procs使用带有不同where条件的product表和select语句。即使上述查询的性能是(2) > (3)，(2)仍然比(3)更好吗？

(暂时忽略dataStaging上的索引)

Answer 1

(1)需要对IsActive上的索引和Ref1/Ref2上的索引进行索引联接，因为它认为这不是最优的。

我会在(2)上做一个变化--两个过滤后的索引包含：

create index IX_Product_Ref1 on Product (Ref1) include(id) where (IsActive = 1)
create index IX_Product_Ref2 on Product (Ref2) include(id) where (IsActive = 1)

(3)只有同时查询IsActive、Ref1和Ref2才是个好主意。

另外，你能不能不要这样写你的查询？

UPDATE  dbo.DataStaging
    SET     ProductId = isnull(p.id, p2.id) 
FROM    dbo.DataStaging s
LEFT JOIN dbo.Product p ON s.[Ref] = p.Ref1 and p.IsActive = 1
LEFT JOIN dbo.Product p2 ON s.[Ref] = p2.Ref2 and p2.IsActive = 1
WHERE s.TypeId = 1
AND s.StatusId = 2