针对参数化查询的Sql Server索引

Question

我希望创建最佳的SQL查询索引。用户可以在界面上选择选项，然后构建查询。以下是表格和查询：

CREATE TABLE [dbo].[MyTable](
    [Id] [nvarchar](32) NOT NULL,
    [SomeKey] [int] NOT NULL,
    [Col1] [nvarchar](max) NULL,
    [Col2] [nvarchar](max) NULL,
    [NumCol1] [int] NOT NULL,
    [NumCol2] [int] NOT NULL,
    [BitCol1] [bit] NOT NULL,
    [BitCol2] [bit] NOT NULL,
    [Created] [datetime] NOT NULL
CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

查询语句在此处（SP）：

ALTER PROCEDURE [dbo].[MyTable] 
@SomeKey INT,@Col1 BIT,@Col2 BIT,@BitCol1 BIT,@BitCol2 BIT,
@NumCol1 INT,@NumCol2 INT,@offset INT,@take INT
AS
DECLARE @sql NVARCHAR(MAX), @paramlist  nvarchar(4000) 
SET @sql = 'SELECT [Id],[SomeKey],[Col1],[Col2],[NumCol1],[NumCol2],[BitCol1],[BitCol2], FROM MyTable WHERE SomeKey = @SomeKey'
IF @NumCol1 IS NOT NULL SELECT @sql = @sql + ' AND NumCol1 = @NumCol1'
IF @NumCol2 IS NOT NULL SELECT @sql = @sql + ' AND (NumCol2 > @NumCol2)'
IF @Col1 IS NOT NULL SELECT @sql = @sql + ' AND (Col1 = '''' OR Col1 IS NULL)'
IF @Col2 IS NOT NULL SELECT @sql = @sql + ' AND (Col2 = '''' OR Col2 IS NULL)'
IF @BitCol1 IS NOT NULL SELECT @sql = @sql + ' AND BitCol1 = 1'
IF @BitCol2 IS NOT NULL SELECT @sql = @sql + ' AND BitCol2 = 1'
SELECT @sql = @sql + ' ORDER BY Created OFFSET @offset ROWS FETCH NEXT @take ROWS ONLY'
SELECT @paramlist = '@SomeKey INT, @Col1 INT, ....'
EXEC sp_executesql @sql, @paramlist, @SomeKey, @Col1, @Col2, ...

希望你能理解。我正在构建数据库中的查询字符串，并执行它。如果我发送参数 Col1 = true，这意味着我想要从数据库中获取所有 Col1 为空的数据。从应用程序中，我确保准备好了所有参数。其余部分应该很明显，但是请在评论中随时提出任何问题！

如何为此表/查询创建索引，以避免索引扫描或类似的“慢”搜索？而且，我只是一个Web开发人员，所以这（高级）索引对我来说是新事物！数据库是SQL Server 2012。

更新：使用场景
表格将具有从100k到最多10m-20m行。行不会被更新。插入将批量完成500 (sqlbulkcopy C＃类)，每5-10分钟或更长时间进行一次，当应用程序运行时才会执行（它并非总是运行）。每一两周会删除一些行（基于SomeKey列）。
应用程序用户不会很多（同时可能只有几个用户，最多10个），因此不要指望很多查询/秒（每5-10秒1个查询？），但我希望它快速（以毫秒为单位测量，而不是秒数）。

- Hrvoje Hudo

Answer 1

索引是一种权衡。它必须在表中的数据更改时进行更新，因此对于有大量INSERT和UPDATE的表格要小心。

在SQL Server Management Studio中，您可以查看查询的执行过程。在查询菜单下有相关选项。您将能够看到在查询执行期间花费最多时间的内容。

另一个选择是通过菜单“工具>数据库引擎调整顾问”访问调整顾问。它会建议创建索引。

无论您在搜索中使用哪些变量，例如WHERE，都是成为索引的好候选对象。

索引不是会使您死亡的东西。您可以使用它来找到最佳解决方案，并且可以在生产过程中进行更改而不会出现主要问题。不幸的是，您是否需要索引的决定不仅基于表设计，还基于该表中的数据量、数据多样性以及对该表执行的操作的性质。

更新：

可能会帮助您的内容：

MSDN查询性能

MSDN查询调整

参考您的执行计划，我建议您阅读关于理解嵌套循环连接的部分。

索引扫描通常是从给定列检索大量数据的结果。

- rumburak

---

这是我实际的查询计划 http://imgur.com/FQcITX7 ，我不喜欢这个索引扫描。但目前我还在猜测，希望能得到某种专家答案。 - Hrvoje Hudo

Answer 2

最佳解决方案实际上取决于我们处理的资源 - 表中有多少行，可用的内存/空间有多少，动态参数使用频率如何以及表的典型使用模式是什么（读取比写入更多吗？）。

我首先会在SomeKey上添加索引，因为它总是被引用，然后尝试添加Created，因为排序总是被使用。您还可以通过使bit列过滤静态（它们始终是一种或另一种方式 - 您知道所有可能的值），然后将其添加到SomeKey和Created之间的索引中来改进逻辑。

最后，您可以尝试将INT列作为键的包含列添加到索引中，以查看是否可以通过减少磁盘读取来抵消添加的索引大小。

这只是一个开始，因为一切都取决于您的典型工作负载。请相应地进行测试。

- bartover