基于属性的数据库设计：如何优化海量商品的搜索耗时？

针对你当前的EAV（实体-属性-值）模型在数据量增长后的多属性搜索性能问题，结合你的思路（单属性过滤+集合交集），以下是具体优化方案：

一、完善索引设计（核心基础） #

你已经注释的类型专属过滤索引是正确的方向，需要启用并优化：

1. 启用分类型过滤索引 #

-- 文本属性索引：按属性ID+文本值排序，包含商品ID避免回表
CREATE INDEX IX_PAV_Text
ON ProductAttributeValues (AttributeID, TextValue)
INCLUDE (ProductID)
WHERE TextValue IS NOT NULL;

-- 数值属性索引：支持范围查询
CREATE INDEX IX_PAV_Number
ON ProductAttributeValues (AttributeID, NumberValue)
INCLUDE (ProductID)
WHERE NumberValue IS NOT NULL;

-- 日期属性索引：支持范围查询
CREATE INDEX IX_PAV_Date
ON ProductAttributeValues (AttributeID, DateValue)
INCLUDE (ProductID)
WHERE DateValue IS NOT NULL;

-- 布尔属性索引
CREATE INDEX IX_PAV_Bool
ON ProductAttributeValues (AttributeID, BooleanValue)
INCLUDE (ProductID)
WHERE BooleanValue IS NOT NULL;

• 用INCLUDE替代将ProductID放入索引键，减少索引体积的同时避免回表查询。
• 过滤条件（WHERE XXX IS NOT NULL）进一步缩小索引范围，提升扫描效率。

二、高效实现多属性交集查询 #

1. 用INTERSECT直接实现集合交集 #

适合2-3个属性的组合查询，SQL Server会自动利用索引优化执行计划：

-- 示例：搜索颜色为红色且价格>100的商品ID
SELECT ProductID FROM ProductAttributeValues
WHERE AttributeID = (SELECT ID FROM ProductAttributes WHERE AttributeName = '颜色')
  AND TextValue = '红色'
INTERSECT
SELECT ProductID FROM ProductAttributeValues
WHERE AttributeID = (SELECT ID FROM ProductAttributes WHERE AttributeName = '价格')
  AND NumberValue > 100;

2. 分组计数法（适合多属性组合） #

当查询条件超过3个时，分组计数的方式更稳定，通过统计商品满足的条件数量筛选目标：

-- 示例：搜索颜色=红色、价格>100、上市日期>2023-01-01的商品
SELECT pav.ProductID
FROM ProductAttributeValues pav
JOIN (
    VALUES
        ((SELECT ID FROM ProductAttributes WHERE AttributeName = '颜色'), '红色', NULL, NULL, NULL),
        ((SELECT ID FROM ProductAttributes WHERE AttributeName = '价格'), NULL, 100, NULL, NULL),
        ((SELECT ID FROM ProductAttributes WHERE AttributeName = '上市日期'), NULL, NULL, '2023-01-01', NULL)
) AS conditions(AttrID, TextVal, NumMin, DateMin, BoolVal)
ON pav.AttributeID = conditions.AttrID
AND (
    (conditions.TextVal IS NOT NULL AND pav.TextValue = conditions.TextVal)
    OR (conditions.NumMin IS NOT NULL AND pav.NumberValue > conditions.NumMin)
    OR (conditions.DateMin IS NOT NULL AND pav.DateValue > conditions.DateMin)
)
GROUP BY pav.ProductID
HAVING COUNT(*) = 3; -- 条件的总数量

三、分块与排序优化（跳过无关子集） #

1. 有序分页查询 #

利用ProductID的自增有序性，结合分页语句快速跳过无需返回的子集：

-- 示例：分页返回符合条件的商品详情，每次取20条
SELECT p.*
FROM (
    SELECT ProductID FROM ProductAttributeValues
    WHERE AttributeID = @ColorAttrID AND TextValue = '红色'
    INTERSECT
    SELECT ProductID FROM ProductAttributeValues
    WHERE AttributeID = @PriceAttrID AND NumberValue > 100
) AS filtered
JOIN Products p ON filtered.ProductID = p.ID
ORDER BY p.ID -- 利用主键有序性快速定位
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

• 有序排序后，数据库可直接定位到分页起始位置，无需扫描全量结果。

2. 预计算物化视图（针对高频查询） #

对于用户常用的属性组合（比如颜色+价格+尺寸），创建物化视图预存储结果：

CREATE MATERIALIZED VIEW MV_CommonProductFilters
WITH (SCHEMABINDING)
AS
SELECT 
    p.ID AS ProductID,
    p.ProductName,
    p.Price AS ProductPrice,
    pav_color.TextValue AS Color,
    pav_size.TextValue AS Size
FROM dbo.Products p
JOIN dbo.ProductAttributeValues pav_color ON p.ID = pav_color.ProductID
    AND pav_color.AttributeID = (SELECT ID FROM dbo.ProductAttributes WHERE AttributeName = '颜色')
JOIN dbo.ProductAttributeValues pav_size ON p.ID = pav_size.ProductID
    AND pav_size.AttributeID = (SELECT ID FROM dbo.ProductAttributes WHERE AttributeName = '尺寸');

-- 给物化视图创建聚簇索引，提升查询速度
CREATE UNIQUE CLUSTERED INDEX IX_MV_CommonProductFilters_ProductID
ON MV_CommonProductFilters(ProductID);

• 物化视图会定期同步原表数据，查询时直接读取预计算结果，跳过多次EAV关联，性能提升明显。

四、额外优化建议 #

• 参数化查询：避免动态SQL拼接，使用参数传递属性ID和值，让SQL Server缓存执行计划。
• 高频属性冗余：将布尔/高频查询属性（比如是否库存）直接添加到Products表，减少EAV表的关联次数。
• 索引维护：定期执行ALTER INDEX ALL ON ProductAttributeValues REORGANIZE，清理索引碎片，保证索引效率。

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