主页 / dba / 问题 / 208266
Accepted
GoatInTheMachine
Asked: 2018-05-31 07:42:33 +0800 CST

高效的 SQL 查询返回满足子项约束的父项

  • 772

我有一个类似于Entity表的模式，与 具有一对多关系Attribute，与 具有一对多关系Value。Attribute有一列Name，并且Value有一列Value：

Entity
------
Id
Name


Attribute
---------
Id
EntityId
Name


Value
-----
Id
AttributeId
Value

我需要能够构建一个查询，该查询将仅返回Entity具有特定属性/值子项组合的行。目前我正在编写类似这样的查询：

select
    *
    from Entity e   
    where exists(
        select * from Attribute a 
            join Value v on v.AttributeId = a.id 
            where a.EntityId = e.id and a.Name = 'color' and v.Value = 'red')
    and exists(
        select * from Attribute a 
            join Value v on v.AttributeId = a.id 
            where a.EntityId = e.id and a.Name = 'size' and v.Value = 'small')

这可行，但每个额外的属性过滤器都会添加另一个exists子句，我对性能感到紧张。有没有一种更有效的方法来编写这种类型的查询，或者如果我使用正确的索引等，它是否可能具有完美的性能？

sql-server performance

2 个回答

  1. 你需要这样的东西：

    SELECT e.Id, count(1) AS cnt
  FROM Entity    AS e
  JOIN Attribute AS a ON a.EntityID = e.Id
  JOIN Value     AS v ON v.AttributeId = a.Id
 WHERE (a.Name = 'color' AND v.Value = 'red')
    OR (a.Name = 'size'  AND v.Value = 'small')
--  OR ( ... ) -- extended here
 GROUP BY e.Id
HAVING cnt = 2 -- exact number of desired attributes
;
    • 1
  2. Best Answer

    很难预测您的原始查询是否会出现性能问题。我们不知道您的表有多大、您有哪些索引、您可能添加了多少过滤器、查询需要多快完成，等等。您可能需要考虑以最自然的方式编写查询并衡量性能。如果性能可以接受，请按原样保留代码。

    如果确实需要提升性能，可以考虑类似下面的重写：

    select *
from Entity e 
WHERE e.id IN (
    SELECT a.id
    FROM Attribute a 
    join [Value] v on v.AttributeId = a.id 
    WHERE a.Name IN ('color', 'size') AND v.Value IN ('red', 'small') -- redundant filter to improve performance
    GROUP BY a.id
    HAVING MAX(CASE WHEN a.Name = 'color' and v.Value = 'red' THEN 1 ELSE 0 END) = 1
    AND MAX(CASE WHEN a.Name = 'size' and v.Value = 'small' THEN 1 ELSE 0 END) = 1
);

    无论您向子句中添加了多少过滤器，这都会让您对表attribute进行一次扫描。一个缺点是您可能会从过滤器中得到一个糟糕的基数估计，但如果过滤器的目标是从表中过滤掉大部分行，那么这可能不是问题。valueHAVINGHAVINGEntity

    • 1

相关问题