具有动态谓词的表的最佳索引

我想优化SQL Server中下表的查询速度

CREATE TABLE [dbo].[PriceNodeLookupIndex]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PriceNodeId] [int] NOT NULL,
    [ItemId] [int] NOT NULL,
    [OptionValueId1] [int] NULL,
    [OptionValueId2] [int] NULL,
    [OptionValueId3] [int] NULL,
    [OptionValueId4] [int] NULL,
    [OptionValueId5] [int] NULL,
    [OptionValueId6] [int] NULL,
    [OptionValueId7] [int] NULL,
    [OptionValueId8] [int] NULL,
    [OptionValueId9] [int] NULL,
    [OptionValueId10] [int] NULL,
    [OptionValueId11] [int] NULL,
    [OptionValueId12] [int] NULL,
    [OptionValueId14] [int] NULL,
    [OptionValueId15] [int] NULL,
    [OptionValueId13] [int] NULL,
    [OptionValueId16] [int] NULL,
    [OptionValueId17] [int] NULL,
    [OptionValueId18] [int] NULL,
    [OptionValueId19] [int] NULL,
    [OptionValueId20] [int] NULL,

    CONSTRAINT [PK_PriceNodeLookupIndex] 
        PRIMARY KEY NONCLUSTERED 
)

针对该表运行的查询如下所示：

SELECT PriceNodeId 
FROM PriceNodeLookupIndex 
WHERE ItemId = 2345
  AND OptionValueId5 = 63423
  AND OptionValueId11 = 97543
  AND OptionValueId13 = 39452

但是，那OptionValueId谓词列表是动态生成的，因此它可以是以下任意组合OptionValueId列。

因此下一个查询可能是

SELECT PriceNodeId 
FROM PriceNodeLookupIndex     
WHERE ItemId = 2345
  AND OptionValueId7 = 45340
  AND OptionValueId14 = 5693

查询中只会使用任何列之一。

目前的指数是

• 非聚集索引Id
• 聚集索引PriceNodeId

and:

CREATE NONCLUSTERED INDEX [PriceNodeLookupIndex_All] 
ON [dbo].[PriceNodeLookupIndex] ([ItemId] ASC)
INCLUDE ([OptionValueId1], [OptionValueId2], [OptionValueId3],
         [OptionValueId4], [OptionValueId5], [OptionValueId6],
         [OptionValueId7], [OptionValueId8], [OptionValueId9],
         [OptionValueId10], [OptionValueId11], [OptionValueId12],
         [OptionValueId13], [OptionValueId14], [OptionValueId15],
         [OptionValueId16], [OptionValueId17], [OptionValueId18],
         [OptionValueId19], [OptionValueId20])

当我运行执行计划时，它使用大索引，但也建议目标索引，该索引仅适用于确切的组合OptionValueId列（对我来说没用）。

整个应用程序的性能由这些查询的速度决定。

有没有人对我如何提高这些查询的速度有任何建议，或者这是否已经达到最好的程度？

因为您违反了数据库建模的第一范式（通过不正当的手段 - apocope），所以您无法优化此类查询。

First您应该通过将此伪数组 (OptionValueId1 ... OptionValueId20) 外部化到如下表中来重新规范化您的模型：

CREATE TABLE dbo.PriceNodeLookupIndex_values
(   Id int        NOT NULL REFERENCES dbo.PriceNodeLookupIndex ON DELETE CASCADE,
    Position      INT NOT NULL,
    OptionValue   INT NOT NULL,
    CONSTRAINT    PK_PriceNodeLookupIndex_values PRIMARY KEY(Id, Position));
CREATE INDEX X ON dbo.PriceNodeLookupIndex_values (OptionValue);

Second，删除原始表中的所有 OptionValueId1 ... OptionValueId20 。

Third使用数据透视运算符创建一个视图来模拟异常表。例如，您应该为此视图指定名称“dbo.PriceNodeLookupIndex2”。

Last，测试为 dbo.PriceNodeLookupIndex2 重写的查询的速度。

每当你不尊重数据建模概念（如正常形式）的精神时，性能都会很丑陋......

如果您希望用户可以插入或更新视图，请编写两个 INSTEAD OF 触发器来无缝地完成这项工作。

特别是在你的原始表中，许多列将具有 NULL 值（实际上是一个标记），几乎有 4 个字节没有值......结果将是一个肥胖的表，其中有很多空的东西，这也会降低性能。

顺便问一下，为什么 PriceNodeLookupIndex 使用非聚集主键？你喜欢搬起石头砸自己的脚吗？

我不推荐的另一种方法是为原始表使用聚集列存储索引......

作为 SQL 命令的补充（但用法语......稍后将用英语）：模型化：viol de la première forme normale par « apocope » http://mssqlserver.fr/modelisation-viol-de-la-premiere-forme-normale-par-apocope/

为了好玩，该视图与原始表完全相同：

CREATE VIEW dbo.V_PriceNodeLookupIndex
AS
SELECT Id, PriceNodeId, ItemId, 
       [1] AS OptionValueId1,
       [2] AS OptionValueId2,
       [3] AS OptionValueId3,
       [4] AS OptionValueId4,
       [5] AS OptionValueId5,
       [6] AS OptionValueId6,
       [7] AS OptionValueId7,
       [8] AS OptionValueId8,
       [9] AS OptionValueId9,
       [10] AS OptionValueId10,
       [11] AS OptionValueId11,
       [12] AS OptionValueId12,
       [13] AS OptionValueId13,
       [14] AS OptionValueId14,
       [15] AS OptionValueId15,
       [16] AS OptionValueId16,
       [17] AS OptionValueId17,
       [18] AS OptionValueId18,
       [19] AS OptionValueId19,
       [20] AS OptionValueId20
FROM   (SELECT PN.Id, PN.PriceNodeId, PN.ItemId, V.Position, V.OptionValue
        FROM   questions_76231541.PriceNodeLookupIndex AS PN
               LEFT OUTER JOIN questions_76231541.PriceNodeLookupIndex_values AS V
                  ON PN.Id = V.Id) AS SRC
PIVOT  (MAX(OptionValue)
        FOR Position IN ( [1],  [2],  [3],  [4],  [5],  [6],  [7],  [8],  [9], [10], 
                         [11], [12], [13], [14], [15], [16], [17], [18], [19], [20])
       ) AS PV;
GO