用于参数化查询的 SQL Server 索引

Question

我想为 sql 查询创建最佳索引。用户可以在 UI 上选择选项，从中构建查询。这是表和查询:

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，这意味着我想要来自 db 的所有空 Col1。从应用程序我确保我准备好所有参数。我希望休息是不言自明的(但请随时在评论中提出任何问题)!

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

更新:使用场景
表格最多有 100k 到 10m-20m 行。行不会更新。当应用程序运行时(它并不总是运行)，每 5-10 分钟或更长时间，将批量插入 500 个(sqlbulkcopy c# 类)。每周一次——删除一些行(基于 SomeKey col)。
应用程序不会有很多用户(可能同时很少，最多 10 个)，所以不要期望每秒有很多查询(每 5-10 秒 1 个查询？)，但我希望它很快(以毫秒为单位) ，不是秒)。

Answer 1

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

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

其他选项是调优顾问，可从菜单工具 > 数据库引擎调优顾问访问。它将建议创建索引。

无论您在 WHERE 之类的搜索中使用什么变量，都可以很好地编入索引。

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

更新:

什么可以帮助你:

MSDN Query Performance

MSDN Query Tuning

关于您的执行计划，我建议您阅读关于 Understanding Nested Loops Joins 的部分

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