sql-server - 为什么在我的情况下 SQL 选择了不正确的索引？

Question

我有一个带有两个索引的表；一个是多列聚集索引，在 3 列上:

(
   symbolid int16,
   bartime int32,
   typeid int8
) 

第二个是非聚集在

(
   bartime int16
)

我试图运行的选择语句是:

    SELECT symbolID, vTrdBuy
    FROM mvTrdHidUhd 
    WHERE typeID = 1 
    AND barDateTime = 44991 
    AND symbolid in (1010,1020,1030,1040,1050,1060) 

我使用 sql management studio 编辑器在 sql2008 上运行此查询并启用实际执行计划，我发现 sql 使用第二个索引和 propse 为三列(symbolid、bartime、typeid)创建一个新索引，但非聚集!!! (我认为它说非聚集索引，因为已经有聚集索引)

这个选择是错误的，我再次重新运行相同的查询并强制 SQL 使用集群索引(使用“带索引”)并且性能更好。

我在这里有两个问题，一个与此行为有关，第二个与查询本身有关

为什么 SQL 选择了错误的索引并提出了相同的索引

我应该在 "where" 中使用哪一个获得更好性能的条件

symbolid in (1010,1020,1030,1040,1050,1060)

(symbolid = 1010 or symbolid = 1020 ..etc)

(symbolid between (1010 and 1060))

测试后

我发现当我将 where 条件从使用 IN 更改为使用 >= 和 <= 时，bartime 列上的非聚集索引比 3 列上的聚集索引具有更好的性能。

所以我有两种情况，如果 WHERE 使用 IN 最好使用聚集索引，如果它包含 >= 和 <= 它使用第二个。

Answer 1

SELECT  symbolID, vTrdBuy
FROM    mvTrdHidUhd 
WHERE   typeID = 1 
        AND barDateTime = 44991 
        AND symbolid IN (1010,1020,1030,1040,1050,1060)

聚集索引的单个连续范围不涵盖这种情况。

这些行:

1010, 44991, 1
1010, 50000, 1
1020, 44991, 1

将按顺序出现在索引中，但您的查询将选择第一个和第三个，跳过第二个。
SQL Server可以使用 Clustered Index Seek如果谓词数量有限，例如您的 IN案件。在这种情况下，它使用多个范围:

SELECT  symbolID, vTrdBuy
FROM    mvTrdHidUhd 
WHERE   (typeID = 1 
        AND barDateTime = 44991 
        AND symbolid = 1010)
        OR
        (typeID = 1 
        AND barDateTime = 44991 
        AND symbolid = 1010)
        OR …

但如果是 BETWEEN范围在 symbolid它不能构造如此有限数量的谓词，这就是它恢复到效率较低的原因 Clustered Index Scan (它在 symbolid 上扫描并过滤掉错误的结果)。

在这种情况下，您的非聚集索引性能更好。

你可以像这样重写你的查询:

SELECT  symbolID, vTrdBuy
FROM    (
        SELECT  DISTINCT symbolid
        FROM    mvTrdHidUhd 
        WHERE   symbolid BETWEEN 1010 AND 1050
        ) s
JOIN    mvTrdHidUhd m
ON      m.symbolid = s.symbolid
        AND m.typeID = 1 
        AND m.barDateTime = 44991

，这将使用 Clustered Index Seek在你的 table 上，两者都建立一个 DISTINCT symbolid 的列表并加入这个名单。