在SQL Server中为经常使用的查询创建适当的索引

在我的应用程序中，我有两个相当频繁使用的查询。 这些查询的Where子句如下：

WHERE FieldA = @P1 AND (FieldB = @P2 OR FieldC = @P2)

和

WHERE FieldA = @P1 AND FieldB = @P2

P1和P2是在UI中输入或来自外部数据源的参数。

FieldA是一个int ，非常不唯一，意思是：表中只有两个，三个，四个不同的值，并且有20000行

FieldB是一个varchar(20) ，并且“几乎”是唯一的，只有很少的行，FieldB可能具有相同的值

FieldC是一个varchar(15) ，也非常明显，但不如FieldB

FieldA和FieldB一起是唯一的（但不构成我的主键，它是一个带有聚集索引的简单自动递增标识列）

我想知道现在定义一个索引以加速这两个查询的最佳方式是什么。 我应该如何定义一个索引...

FieldB (or better FieldC here?)
FieldC (or better FieldB here?)
FieldA

...或更好的两个指标：

FieldB
FieldA

和

FieldC
FieldA

或者还有其他更好的选择吗？ 什么是最好的方式，为什么？

提前感谢您的建议！

编辑：

就像其他读者的信息：这是另一个已被删除的答案。 其实答案似乎对我非常有用。 建议创建两个索引（根据我上面的第二个选项），并通过使用两个select语句的UNION来重新WHERE FieldA = @P1 AND FieldB = @P2第一个查询（其中一个使用WHERE FieldA = @P1 AND FieldB = @P2 ，另一个使用WHERE FieldA = @P1 AND FieldC = @P2 ）而不是OR从两个指数中受益（OR运算符不会这样）。

EDIT2：

有OR或者索引的陈述没有被使用，并且UNION更可取似乎是错误的 - 至少根据我自己的测试（见下面我自己的回答）。

---

扩展Remus'（编辑：现在删除）答案...

如果@ p2是varchar（15），那么您无法可靠地与FieldB进行比较，它是varchar（20）

如果@ p2是varchar（20），那么FieldC将被转换为varchar（20）并且不使用索引（或者至多扫描它）

如果@ p1只有2,3,4个值，那么为什么不使用tinyint并减少表/索引大小？

在解决此数据类型优先级问题之前，我不打扰索引：这是OR子句问题之上的问题。

最后，一列是唯一的或不唯一的：中间没有。 统计在这里有选择性的帮助，但它是无关紧要的。

由于FieldA的选择性FieldC, FieldA我会将Remus的答案中的索引改为FieldB, FieldA （和unique）和FieldC, FieldA

编辑，评论之后：你不能比较使用@ p2和使用常量字符串。

---

在使用更大的数据库进行一些测试后（在SQL Server 2008中），我添加了自己的答案：

首先，我决定了第二个选项，意思是我创建了两个索引：

CREATE UNIQUE NONCLUSTERED INDEX [IX_B] ON [dbo].[MyTable] 
(
    [FieldB] ASC,
    [FieldA] ASC
)
CREATE NONCLUSTERED INDEX [IX_C] ON [dbo].[MyTable] 
(
    [FieldC] ASC,
    [FieldA] ASC
)

我已经测试了两个查询：

declare @p1 int = 1;
declare @p2 varchar(20) = '12345678';

select * from MyTable
where FieldA=@p1 and (FieldB=@p2 or FieldC=@p2);

执行此查询时，我得到以下查询计划（ ID是表的主键， PK_MyTable是主键上的聚集索引）：

|--Nested Loops(Inner Join, OUTER REFERENCES:([MyDb].[dbo].[MyTable].[ID]))
   |--Stream Aggregate(GROUP BY:([MyDb].[dbo].[MyTable].[ID]) DEFINE:([MyDb].[dbo].[MyTable].[FieldA]=ANY([MyDb].[dbo].[MyTable].[FieldA])))
   |  |--Merge Join(Concatenation)
   |     |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_B]), SEEK:([MyDb].[dbo].[MyTable].[FieldB]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
   |     |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_C]), SEEK:([MyDb].[dbo].[MyTable].[FieldC]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[PK_MyTable]), SEEK:([MyDb].[dbo].[MyTable].[ID]=[MyDb].[dbo].[MyTable].[ID]) LOOKUP ORDERED FORWARD)

因此，似乎两个索引都被使用（“索引搜索”）。

查询时间：00：00：00.2220127

我测试的第二个查询是使用JOIN来避免OR操作符（请参阅我的问题中的“编辑”）：

declare @p1 int = 1;
declare @p2 varchar(20) = '12345678';

select * from MyTable where FieldA=@p1 and FieldB=@p2
union
select * from MyTable where FieldA=@p1 and FieldC=@p2;

该查询具有以下查询计划：

|--Merge Join(Union)
   |--Nested Loops(Inner Join, OUTER REFERENCES:([MyDb].[dbo].[MyTable].[ID]))
   |  |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_B]), SEEK:([MyDb].[dbo].[MyTable].[FieldB]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
   |  |--Clustered Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[PK_MyTable]), SEEK:([MyDb].[dbo].[MyTable].[ID]=[MyDb].[dbo].[MyTable].[ID]) LOOKUP ORDERED FORWARD)
   |--Nested Loops(Inner Join, OUTER REFERENCES:([MyDb].[dbo].[MyTable].[ID]))
      |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_C]), SEEK:([MyDb].[dbo].[MyTable].[FieldC]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
      |--Clustered Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[PK_MyTable]), SEEK:([MyDb].[dbo].[MyTable].[ID]=[MyDb].[dbo].[MyTable].[ID]) LOOKUP ORDERED FORWARD)

再次使用两个索引（“索引查找”）。

查询时间：00：00：00.3710212

注意：对于这两个查询，我声明@ p2的长度并不重要：使用varchar（8）或varchar（20）或varchar（30）会给出相同的结果和查询计划。

遵循这些结果，我将继续使用OR运算符而不是UNION，因为这两个查询都使用索引，但第一个查询更快。

链接地址: http://www.djcxy.com/p/1167.html

上一篇: Creating an appropriate index for a frequently used query in SQL Server

下一篇: Why does this gstreamer pipeline stall?