SQL Server 按列对查询

Question

我正在研究亚马逊等产品过滤器(多面搜索)。我有一个具有如下属性(颜色、内存、屏幕)的表:

ArticleID  PropertyID  Value
---------  ----------  ------------
1          1           Black
1          2           8 GB
1          3           15"
2          1           White
2          2           8 GB
3          3           13"

我必须根据选择的属性来选择文章。您可以为一项属性选择多个值(例如 RAM:4 GB 和 8 GB)，也可以选择多个属性(例如 RAM 和屏幕大小)。

我需要这样的功能:

SELECT ArticleID
FROM ArticlesProperties
WHERE (PropertyID = 2 AND Value IN ('4 GB', '8 GB'))
  AND (PropertyID = 3 AND Value IN ('13"'))

我曾经通过创建动态查询然后执行该查询来做到这一点:

SELECT ArticleID
FROM ArticlesProperties
WHERE PropertyID = 2 AND Value IN ('4 GB', '8 GB')

INTERSECT

SELECT ArticleID
FROM ArticlesProperties
WHERE PropertyID = 3 AND Value IN ('13"')

但我认为这不是好方法，必须有更好的解决方案。表中有数百万个属性，因此需要进行优化。

解决方案应在 SQL Server 2014 标准版上运行，无需某些附加组件或搜索引擎，例如 solr 等。

我陷入了困境，所以如果有人有一些想法或解决方案，我将非常感激。谢谢!

Answer 1

我制作了一个片段，显示了我的工作思路。良好的索引选择对于加快查询速度非常重要。始终检查执行计划以调整索引。

注释:

• 该脚本使用临时表，但本质上它们与常规表没有什么不同。除了 #select_properties 之外，如果您打算使用脚本中概述的工作方式，临时表应该成为常规表。

• 使用属性选择值的 ID 存储文章属性，而不是实际的选择值。当这些表由 SQL Server 缓存时，这可以节省磁盘空间和内存。 SQL Server 将尽可能多地将表缓存在内存中，以便更快地为 select 语句提供服务。

  如果文章属性表太大，SQL Server 可能需要进行磁盘 IO 来执行 select 语句，这肯定会减慢语句的速度。

  额外的好处是，对于查找，您正在查找 ID(整数)而不是文本(VARCHAR)。查找整数比查找字符串快得多。

• 在表上提供合适的索引以加快查询速度。为此，通过检查 Actual Execution Plan 来分析查询是一个很好的做法。 。

  我在下面的代码片段中包含了几个这样的索引。根据文章属性表的行数和统计信息，SQL Server会选择最佳的索引来加速查询。

  如果 SQL Server 认为查询缺少 SQL 语句的正确索引，则实际执行计划将指示您缺少索引。当查询变慢时，最好通过检查 SQL Server Management Studio 中的实际执行计划来分析这些查询。

• 该代码段使用临时表来指定您要查找的属性:#select_properties。通过插入属性 ID 和属性选择值 ID 来提供该表中的条件。最终选择查询选择至少一个属性选择值适用于每个属性的文章。

  您将在要选择文章的 session 中创建此临时表。然后插入搜索条件，触发 select 语句，最后删除临时表。

<小时/>

CREATE TABLE #articles(
    article_id INT NOT NULL,
    article_desc VARCHAR(128) NOT NULL,
    CONSTRAINT PK_articles PRIMARY KEY CLUSTERED(article_id)
);

CREATE TABLE #properties(
    property_id INT NOT NULL, -- color, size, capacity
    property_desc VARCHAR(128) NOT NULL,
    CONSTRAINT PK_properties PRIMARY KEY CLUSTERED(property_id)
);

CREATE TABLE #property_values(
    property_id INT NOT NULL,
    property_choice_id INT NOT NULL, -- eg color -> black, white, red
    property_choice_val VARCHAR(128) NOT NULL,
    CONSTRAINT PK_property_values PRIMARY KEY CLUSTERED(property_id,property_choice_id),
    CONSTRAINT FK_values_to_properties FOREIGN KEY (property_id) REFERENCES #properties(property_id)
);

CREATE TABLE #article_properties(
    article_id INT NOT NULL,
    property_id INT NOT NULL,
    property_choice_id INT NOT NULL
    CONSTRAINT PK_article_properties PRIMARY KEY CLUSTERED(article_id,property_id,property_choice_id),
    CONSTRAINT FK_ap_to_articles FOREIGN KEY (article_id) REFERENCES #articles(article_id),
    CONSTRAINT FK_ap_to_property_values FOREIGN KEY (property_id,property_choice_id) REFERENCES #property_values(property_id,property_choice_id)

);
CREATE NONCLUSTERED INDEX IX_article_properties ON #article_properties(property_id,property_choice_id) INCLUDE(article_id);

INSERT INTO #properties(property_id,property_desc)VALUES
    (1,'color'),(2,'capacity'),(3,'size');

INSERT INTO #property_values(property_id,property_choice_id,property_choice_val)VALUES
    (1,1,'black'),(1,2,'white'),(1,3,'red'),
    (2,1,'4 Gb') ,(2,2,'8 Gb') ,(2,3,'16 Gb'),
    (3,1,'13"')  ,(3,2,'15"')  ,(3,3,'17"');

INSERT INTO #articles(article_id,article_desc)VALUES
    (1,'First article'),(2,'Second article'),(3,'Third article');

-- the table you have in your question, slightly modified
INSERT INTO #article_properties(article_id,property_id,property_choice_id)VALUES 
    (1,1,1),(1,2,2),(1,3,2), -- article 1: color=black, capacity=8gb, size=15"
    (2,1,2),(2,2,2),(2,3,1), -- article 2: color=white, capacity=8Gb, size=13"
    (3,1,3),        (3,3,3); -- article 3: color=red, size=17"

-- The table with the criteria you are selecting on
CREATE TABLE #select_properties(
    property_id INT NOT NULL,
    property_choice_id INT NOT NULL,
    CONSTRAINT PK_select_properties PRIMARY KEY CLUSTERED(property_id,property_choice_id)
);
INSERT INTO #select_properties(property_id,property_choice_id)VALUES
    (2,1),(2,2),(3,1); -- looking for '4Gb' or '8Gb', and size 13"

;WITH aid AS (  
    SELECT ap.article_id
    FROM #select_properties AS sp
         INNER JOIN #article_properties AS ap ON
            ap.property_id=sp.property_id AND
            ap.property_choice_id=sp.property_choice_id
    GROUP BY ap.article_id
    HAVING COUNT(DISTINCT ap.property_id)=(SELECT COUNT(DISTINCT property_id) FROM #select_properties)
    -- criteria met when article has a number of properties matching, equal to the distinct number of properties in the selection set
)
SELECT a.article_id,a.article_desc
FROM aid 
     INNER JOIN #articles AS a ON 
         a.article_id=aid.article_id
ORDER BY a.article_id;
-- result is the 'Second article' with id 2

DROP TABLE #select_properties;
DROP TABLE #article_properties;
DROP TABLE #property_values;
DROP TABLE #properties;
DROP TABLE #articles;