高效的 SQL 查詢返回滿足對孩子的約束的父母

我有一個類似於有一個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子句，我對性能感到緊張。是否有更有效的方法來編寫這種類型的查詢，或者如果我使用正確的索引等，它可能會完美執行？

很難預測您的原始查詢是否會出現性能問題。我們不知道你的表有多大，你有什麼索引，你可以添加多少過濾器，查詢需要多快完成，等等。您可能需要考慮以最自然的方式編寫查詢並測量性能。如果性能可以接受，請保持原樣。

如果確實需要提高性能，可以考慮進行類似於以下的重寫：

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
);

無論您在子句中添加了多少過濾器，這都會讓您對attributeand表進行一次掃描。一個缺點是您可能會從過濾器中獲得較差的基數估計，但如果過濾器的目標是過濾掉表中的大部分行，那麼這可能不是問題。value``HAVING``HAVING``Entity

引用自：https://dba.stackexchange.com/questions/208266