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 Standard Edition上运行，而无需一些附加组件或类似的搜索引擎solr。

我在泡菜中，所以如果有人有任何想法或解决方案，我将非常感激。谢谢！

Answer 1

我做了一个片段，显示了我将按照的方式工作。索引的正确选择对于加快查询速度很重要。始终检查执行计划以进行索引调整。

笔记：

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

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

如果文章属性表太大，则SQL Server可能必须做磁盘IO才能执行select语句，这肯定会使语句变慢。

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

• 在表上提供合适的索引以加快查询速度。为此，优良作法是通过检查实际执行计划来分析查询。

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

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

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

您将在要选择文章的会话中创建此临时表。然后插入搜索条件，触发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;