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 
)

查询逻辑为：固定过滤ItemId，同时动态组合任意数量的OptionValueIdN列做等值筛选，最终返回PriceNodeId，示例查询：

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

当前已创建的索引：

• 基于Id的非聚集主键索引
• 基于PriceNodeId的聚集索引
• 非聚集索引PriceNodeLookupIndex_All：以ItemId为键列，包含所有OptionValueId1~20列

当前执行计划显示使用PriceNodeLookupIndex_All索引，但该索引仅能先按ItemId过滤，再在包含列中扫描匹配OptionValueId条件，无法针对任意组合的OptionValueId做高效索引查找。

优化建议 #

1. 重构为EAV（实体-属性-值）表结构 #

这是解决任意列组合筛选最彻底的方案，将原表的多列OptionValueId拆分为行存储：

CREATE TABLE [dbo].[PriceNodeLookupEAV]
(
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    [PriceNodeId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    [OptionTypeId] INT NOT NULL, -- 对应原表的OptionValueId1~20，例如用1代表OptionValueId1
    [OptionValueId] INT NOT NULL
)
-- 创建覆盖所有查询场景的复合索引
CREATE NONCLUSTERED INDEX IX_PriceNodeLookupEAV_Item_Type_Value
ON [dbo].[PriceNodeLookupEAV] (ItemId, OptionTypeId, OptionValueId)
INCLUDE (PriceNodeId)

对应的查询改写为多条件EXISTS关联，例如原示例查询可改为：

SELECT DISTINCT main.PriceNodeId
FROM PriceNodeLookupEAV main
WHERE main.ItemId = 2345
AND EXISTS (SELECT 1 FROM PriceNodeLookupEAV 
            WHERE PriceNodeId = main.PriceNodeId 
              AND OptionTypeId = 5 
              AND OptionValueId = 63423)
AND EXISTS (SELECT 1 FROM PriceNodeLookupEAV 
            WHERE PriceNodeId = main.PriceNodeId 
              AND OptionTypeId = 11 
              AND OptionValueId = 97543)
AND EXISTS (SELECT 1 FROM PriceNodeLookupEAV 
            WHERE PriceNodeId = main.PriceNodeId 
              AND OptionTypeId = 13 
              AND OptionValueId = 39452)

该方案的优势是：无论组合多少个OptionValueId，都能通过索引快速定位，避免扫描大量数据。

2. 创建列存储索引 #

列存储索引对多列任意组合的等值筛选（点查询）性能优异，同时具备高压缩率，适合此类ad-hoc查询场景：

CREATE CLUSTERED COLUMNSTORE INDEX CCI_PriceNodeLookupIndex
ON [dbo].[PriceNodeLookupIndex]
WITH (DROP_EXISTING = OFF)

若不想替换现有聚集索引，可创建非聚集列存储索引：

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_PriceNodeLookupIndex
ON [dbo].[PriceNodeLookupIndex] (ItemId, PriceNodeId, 
    OptionValueId1, OptionValueId2, ..., OptionValueId20)

列存储索引会自动优化查询计划，针对任意OptionValueId组合的筛选，都能高效扫描目标列并返回结果。

3. 优化现有索引（临时过渡方案） #

若无法修改表结构，可调整现有索引，将高频使用的OptionValueId列加入键列，例如：

CREATE NONCLUSTERED INDEX IX_PriceNodeLookup_Item_HighFreqOptions
ON [dbo].[PriceNodeLookupIndex] (ItemId, OptionValueId5, OptionValueId11, OptionValueId13)
INCLUDE (PriceNodeId, OptionValueId1, OptionValueId2, ...) -- 包含剩余OptionValueId列

但此方案仅能覆盖特定组合的查询，无法解决所有任意组合的场景，仅适合作为临时优化手段。

4. 使用内存优化表 #

若系统并发高、数据量适中，可将表改为内存优化表，并创建哈希索引：

CREATE TABLE [dbo].[PriceNodeLookupIndex_Memory]
(
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    [PriceNodeId] [int] NOT NULL,
    [ItemId] [int] NOT NULL,
    [OptionValueId1] [int] NULL,
    [OptionValueId2] [int] NULL,
    -- ... 其余OptionValueId列
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

-- 创建针对ItemId和OptionValueId的组合哈希索引
CREATE NONCLUSTERED HASH INDEX IX_Memory_Item_Option5
ON [dbo].[PriceNodeLookupIndex_Memory] (ItemId, OptionValueId5)
WITH (BUCKET_COUNT = 1000000)
-- 可根据高频组合创建多个哈希索引

内存优化表的哈希索引能提供毫秒级的点查询性能，适合高并发场景。

当前状态评估 #

现有方案并非最优：PriceNodeLookupIndex_All索引只能先过滤ItemId，再扫描包含列匹配OptionValueId条件，当ItemId对应的数据量较大时，扫描成本会显著上升，无法满足任意组合查询的高效需求。

内容的提问来源于stack exchange，提问作者earlxtr