PAYATHON 2026

如何优化基于属性的数据库设计中的搜索性能？

技术老齐

先启用按“属性 ID + 属性值”排列的复合索引，再由 SQL Server 对各个筛选结果求交集。数值范围查询可以直接通过这种索引定位区间，无须自己维护有序 chunks。

你注释掉的索引已经接近所需方案。当前搜索慢的原因，还要结合实际查询、属性值表的行数和执行计划来判断。仅凭表结构，不能直接归因于 JOIN。

1. 现有主键与搜索需要的访问顺序不同

ProductAttributeValues 的主键是：

PRIMARY KEY (ProductID, AttributeID)

它适合查询“某个产品有哪些属性”，或查找“某个产品的某个属性”。

按属性搜索时，条件通常是：

WHERE AttributeID = @AttributeID
  AND NumberValue >= @MinValue
  AND NumberValue <= @MaxValue

这类查询需要先定位属性，再查找该属性下的数值区间，因此应补充以 (AttributeID, NumberValue) 开头的索引。

10,000 个产品也不代表属性表只有 10,000 行。每个产品如果保存多个属性，搜索涉及的行数就会更多。评估数据规模时，应重点看 ProductAttributeValues 的行数。

2. 先建立数值属性的筛选索引

数值属性可以使用这个索引：

CREATE INDEX IX_PAV_Number
ON ProductAttributeValues (AttributeID, NumberValue)
INCLUDE (ProductID)
WHERE NumberValue IS NOT NULL;

其中：

  • AttributeID 用于定位指定属性。
  • NumberValue 用于查找该属性下符合范围的值。
  • ProductID 让查询直接从索引取得匹配产品的 ID，减少回表读取。
  • WHERE NumberValue IS NOT NULL 让索引只包含数值属性行，缩小索引体积。

你原先的定义也能支持这类查询：

CREATE INDEX IX_PAV_Number
ON ProductAttributeValues (AttributeID, NumberValue, ProductID)
WHERE NumberValue IS NOT NULL;

两种定义选一种即可，不必同时建立。这里将 ProductID 放进 INCLUDE，是因为它主要用于返回候选产品，不用于进一步缩小数值范围。

日期属性可以采用相同结构：

CREATE INDEX IX_PAV_Date
ON ProductAttributeValues (AttributeID, DateValue)
INCLUDE (ProductID)
WHERE DateValue IS NOT NULL;

文本和布尔属性的索引按实际查询需求添加即可。每个索引都会占用存储空间，并增加写入时的维护成本。对于 NVARCHAR(500) 文本键，还应核对目标 SQL Server 版本的索引键长度限制。

3. 在数据库中对多个属性的匹配结果求交集

“分别筛选，再取交集”的思路可行。直接使用 INTERSECT，就不必把大量产品 ID 传到应用程序中处理。

以下查询要求产品同时满足两个数值属性条件，属性 ID 和范围均为示例：

DECLARE @AttributeA INT = 10;
DECLARE @MinA DECIMAL(18,2) = 100.00;
DECLARE @MaxA DECIMAL(18,2) = 200.00;

DECLARE @AttributeB INT = 20;
DECLARE @MinB DECIMAL(18,2) = 5.00;
DECLARE @MaxB DECIMAL(18,2) = 10.00;

WITH MatchingProducts AS (
    SELECT ProductID
    FROM ProductAttributeValues
    WHERE AttributeID = @AttributeA
      AND NumberValue >= @MinA
      AND NumberValue <= @MaxA

    INTERSECT

    SELECT ProductID
    FROM ProductAttributeValues
    WHERE AttributeID = @AttributeB
      AND NumberValue >= @MinB
      AND NumberValue <= @MaxB
)
SELECT
    p.ID,
    p.ProductName,
    p.Price
FROM Products AS p
INNER JOIN MatchingProducts AS m
    ON m.ProductID = p.ID
WHERE p.IsActive = 1;

每个分支都具备使用数值索引的条件，求交集时只处理 ProductID。这里的范围包含上下界。

INTERSECT 可能需要排序或哈希操作，不一定比自连接或 EXISTS 快。CTE 也不保证将中间结果物化保存。

同样的条件也可以写成多个 EXISTS：

SELECT
    p.ID,
    p.ProductName,
    p.Price
FROM Products AS p
WHERE p.IsActive = 1
  AND EXISTS (
      SELECT 1
      FROM ProductAttributeValues AS a
      WHERE a.ProductID = p.ID
        AND a.AttributeID = @AttributeA
        AND a.NumberValue >= @MinA
        AND a.NumberValue <= @MaxA
  )
  AND EXISTS (
      SELECT 1
      FROM ProductAttributeValues AS b
      WHERE b.ProductID = p.ID
        AND b.AttributeID = @AttributeB
        AND b.NumberValue >= @MinB
        AND b.NumberValue <= @MaxB
  );

这段查询沿用上一段的参数。优化器会结合产品数量、范围大小和统计信息选择访问方式，SQL 中条件的书写顺序不保证就是执行顺序。

多个属性条件需要分别匹配对应的行。一个属性值行只属于一个属性，因此同一行不可能同时满足 AttributeID = 10 AND AttributeID = 20。

4. 让范围条件能直接用于索引查找

数值已经存储在 DECIMAL(18,2) 列中，直接比较即可：

WHERE AttributeID = @AttributeID
  AND NumberValue >= @MinValue
  AND NumberValue <= @MaxValue

参数也应使用匹配的类型。避免先把列转换成字符串再比较：

-- 不适合数值范围搜索
WHERE CAST(NumberValue AS NVARCHAR(50)) >= @MinText

这种写法会改变比较语义，也会妨碍查询通过数值索引定位范围。

日期筛选通常适合使用半开区间：

WHERE AttributeID = @DateAttributeID
  AND DateValue >= @StartDate
  AND DateValue < @EndDate

例如，查询某一天时，将结束边界设为次日零点，可以覆盖当天带时间部分的记录。

如果搜索条件数量可变，应只生成实际需要的筛选分支，并通过参数化方式执行。大量使用下面这类可选条件，会让同一条查询处理差异很大的搜索情况，增加优化难度：

WHERE (@MinValue IS NULL OR NumberValue >= @MinValue)

5. 用执行计划和统计输出检查效果

在 SSMS 中启用“包括实际执行计划”，并在测试查询前后开启、关闭统计输出：

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 在这里执行完整搜索查询

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

比较添加索引前后的效果，以及 INTERSECT、EXISTS 两种写法时，重点查看：

  • 属性表的逻辑读取次数是否下降。
  • 数值索引的 Seek Predicates 是否包含属性 ID 和范围边界。
  • 估算行数与实际行数是否相差很大。
  • 求交集或关联时是否处理了大量中间行，是否出现溢出到 tempdb 的警告。
  • 耗时是否主要来自排序、返回大量结果或等待锁。

判断效果不能只看有没有 Index Seek。当范围覆盖某个属性的大部分记录时，扫描可能比大量查找更划算。范围越宽，通常需要读取的匹配数据也越多。

测试应覆盖窄范围、宽范围和不同属性组合。统计信息如果明显失真，再检查统计信息维护情况。如果同一参数化查询在不同范围下的表现差异很大，可以进一步排查参数敏感问题，并验证 OPTION (RECOMPILE) 是否有帮助，同时计入每次重新编译的成本。

6. 索引如何支持有序范围读取

普通行存储索引使用 B-tree 家族的结构，SQL Server 的相关索引采用 B+ tree。对于 (AttributeID, NumberValue) 索引，数据库可以定位某个属性下的范围起点，再沿叶级读取匹配区间，跳过不相关的键值范围。

这与你设想的“按顺序排列，并跳过部分数据块”相近。自行分桶需要额外维护边界、处理跨桶查询和属性更新，还要为不同属性分别组织数据。就目前的问题而言，先利用数据库已有的索引更直接。

表分区的帮助取决于查询条件能否排除分区，不能自动加速所有属性查询。多个属性的任意范围搜索，很难共用一套有效的分区边界。

7. 什么时候再考虑调整表结构

当前设计属于 EAV（Entity-Attribute-Value）的变体，适合属性经常变化的场景。不过，多个属性同时筛选需要跨行匹配，筛选条件越多、候选集越大，成本也会越高。

如果主要搜索长期集中在少数固定属性上，可以考虑将这些属性放到产品列或专用搜索表中，再按常见查询建立复合索引。这会增加同步和维护工作，应先完成索引与查询优化，再根据实际负载决定是否调整。

写入时的数据校验也需要补上。现有“恰好一个值非空”约束，既未保证值列与 ProductAttributes.AttributeType 一致，也未保证该属性适用于产品所属类别。类型存错的记录可能在对应的范围查询中被遗漏，需要在写入流程或数据库校验逻辑中处理。

备注：内容仅供参考。