代码之家 › 专栏 › 技术社区 › Slauma

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

indexing sql-server-2008 database sql-server sql

Slauma · 技术社区 · 14 年前

在我的应用程序中,我有两个非常常用的查询。这些查询的Where子句如下:

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

和

WHERE FieldA = @P1 AND FieldB = @P2

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

int 高度非唯一性意味着:一个表中只有两个,三个,四个不同的值,比如说20000行
varchar(20) 并且是“几乎”唯一的,只有极少数行FieldB可能具有相同的值
FieldC是一个 varchar(15) 也很明显,但没有FieldB那么明显
FieldA和FieldB一起是唯一的(但不构成我的主键,它是一个简单的带有聚集索引的自动递增标识列)

我现在想知道定义索引的最佳方法是什么,以加快这两个查询的速度。我应该用…来定义一个索引吗。。。

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

... 或更好的两个指数:

FieldB
FieldA

FieldC
FieldA

或者还有其他更好的选择吗?最好的方法是什么?为什么?

编辑:

UNION WHERE FieldA = @P1 AND FieldB = @P2 还有一个是 WHERE FieldA = @P1 AND FieldC = @P2 )而不是 OR 从这两个索引中获益(与OR操作符不同)。

不使用with或索引,最好使用UNION的说法似乎是错误的——至少根据我自己的测试(见下面我自己的答案)。

2 回复 | 直到 14 年前

gbn 14 年前

如果@p2是varchar(15),那么您无法与FieldB进行可靠的比较,它是varchar(20)
如果@p2是varchar(20),那么FieldC将被转换为varchar(20),并且不使用索引(或者最多扫描它)
如果@p1只有2、3、4个值,那么为什么不精简并减少表/索引的大小呢?

在您解决这个数据类型优先级问题之前,我不会为索引操心:这是在OR子句问题之上的。

我会把莱姆斯的答案倒过来 FieldB, FieldA (和独特的)和 FieldC, FieldA 因为菲尔达的选择性

在注释后编辑:不能将@p2的使用与常量字符串的使用进行比较。

Slauma 14 年前

首先,我决定了第二个选项,也就是说,我创建了两个索引:

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)

所以似乎两个索引都被使用(“索引查找”)。

我测试的第二个查询是使用联接来避免OR运算符(请参阅我问题中的“Edit”):

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)

再次使用两个索引(“索引查找”)。

注意:对于这两个查询,我声明@p2的长度无关紧要:使用varchar(8)或varchar(20)或varchar(30)可以得到相同的结果和查询计划。

在这些结果之后,我将继续使用OR操作符而不是UNION,因为两个查询都使用索引,但是第一个查询更快。