使用 EAV 結構視圖優化查詢
應用程序正在寫入遵循 EAV 結構的數據庫，類似於：
CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item INTEGER REFERENCES item(id), name TEXT, value INTEGER, PRIMARY KEY (item, name) ); INSERT INTO item VALUES (1, 'Item 1'); INSERT INTO item_attr VALUES (1, 'height', 20); INSERT INTO item_attr VALUES (1, 'width', 30); INSERT INTO item_attr VALUES (1, 'weight', 40); INSERT INTO item VALUES (2, 'Item 2'); INSERT INTO item_attr VALUES (2, 'height', 10); INSERT INTO item_attr VALUES (2, 'weight', 35);（我認為 EAV 有點爭議，但這個問題與 EAV 無關：這個遺留應用程序無論如何都無法更改。）
可以有多個屬性，但通常每個項目最多 200 個屬性（通常相似）。在這 200 個屬性中，大約有 25 個屬性比其他屬性更常見，並且在查詢中使用得更頻繁。
為了更容易根據這 25 個屬性中的一些屬性編寫新查詢（需求往往會發生變化，我需要靈活一些），我編寫了一個視圖來連接這 25 個屬性的屬性表。按照上面的範例，它看起來像這樣：
CREATE VIEW exp_item AS SELECT i.id AS id, i.description AS description, ia_height.value AS height, ia_width.value AS width, ia_weight.value AS weight, ia_depth.value AS depth FROM item i LEFT JOIN item_attr ia_height ON i.id=ia_height.item AND ia_height.name='height' LEFT JOIN item_attr ia_width ON i.id=ia_width.item AND ia_width.name='width' LEFT JOIN item_attr ia_weight ON i.id=ia_weight.item AND ia_weight.name='weight' LEFT JOIN item_attr ia_depth ON i.id=ia_depth.item AND ia_depth.name='depth';一份典型的報告只會使用這 25 個屬性中的幾個，例如：
SELECT id, description, height, width FROM exp_item;其中一些查詢沒有我希望的那麼快。使用
EXPLAIN時，我注意到未使用列上的連接仍然存在，當僅使用 3 或 4 個屬性時，在大約 25 個連接上，這會導致性能不必要的下降。當然，
LEFT JOIN在視圖中執行所有 s 是正常的，但我想知道是否有辦法保留這個視圖（或類似的東西：我主要對使用視圖來簡化我引用屬性的方式感興趣，或多或少好像它們是列）並避免（自動）對特定查詢的未使用屬性使用連接。到目前為止，我發現的唯一解決方法是為每個查詢定義一個特定視圖，該視圖僅基於所使用的屬性進行連接。（這確實提高了速度，正如預期的那樣，但每次都需要更多的視圖程式，因此靈活性有點低。）
有一個更好的方法嗎？（從編寫查詢的角度來看，是否有更好的方法可以“假裝” EAV 結構是一個結構良好的表，而不必進行這些不必要的左連接？）
我正在使用 PostgreSQL 8.4。中有大約 10K 行
item和大約 500K 行item_attr。我不期望超過 80K 行item和 4M 行item_attr，我相信現代系統可以毫無問題地處理。（也歡迎對其他 RDBMS/版本發表評論。）編輯：只是為了擴展這個例子中索引的使用。
PRIMARY KEY (item, name)隱式創建索引(item, name)，如CREATE TABLE文件中所述。考慮到item和name都與 中的等式約束一起使用，根據多列索引JOIN的文件，該索引似乎合適。下面的範例顯示該索引似乎按預期使用，沒有任何明確的附加索引：
EXPLAIN SELECT id, description, height, width FROM exp_item WHERE width < 100; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=28.50..203.28 rows=10 width=20) -> Nested Loop Left Join (cost=28.50..196.73 rows=10 width=16) -> Nested Loop Left Join (cost=28.50..190.18 rows=10 width=16) -> Hash Join (cost=28.50..183.64 rows=10 width=16) Hash Cond: (ia_width.item = i.id) -> Seq Scan on item_attr ia_width (cost=0.00..155.00 rows=10 width=8) Filter: ((value < 100) AND (name = 'width'::text)) -> Hash (cost=16.00..16.00 rows=1000 width=12) -> Seq Scan on item i (cost=0.00..16.00 rows=1000 width=12) -> Index Scan using item_attr_pkey on item_attr ia_depth (cost=0.00..0.64 rows=1 width=4) Index Cond: ((i.id = ia_depth.item) AND (ia_depth.name = 'depth'::text)) -> Index Scan using item_attr_pkey on item_attr ia_weight (cost=0.00..0.64 rows=1 width=4) Index Cond: ((i.id = ia_weight.item) AND (ia_weight.name = 'weight'::text)) -> Index Scan using item_attr_pkey on item_attr ia_height (cost=0.00..0.64 rows=1 width=8) Index Cond: ((i.id = ia_height.item) AND (ia_height.name = 'height'::text))
這是 EAV 設計的（眾多）缺點之一。
您無法真正改進 JOIN：由於必要的複雜性，基於成本的優化器無法得出完美的計劃。它發現“足夠好”
建議：
- 不要使用視圖：使用聚合類型查詢（例如，如果我同時匹配身高和體重，則 COUNT(*) = 2）
- 使用觸發器來維護一個真實的（或稀疏的）表並查詢
第一個選項擴展性更好，因為主 EAV 事實表上的一些索引可以很好地覆蓋所有查詢。
你沒有提到 eav 表上的索引，所以我假設你沒有任何索引。
添加一些部分的可能是有意義的。根據您正在執行的查詢類型，其中一個或兩個可能有用：
create index item_attr_weight_item_idx on item_attr(item) where (name = 'weight'); create index item_attr_weight_value_idx on item_attr(value) where (name = 'weight');或者，由於您的行數很少，因此大的胖索引
(name, value)或(name, item)可能會起作用。後者也可以部分化，例如：create index item_attr_freq_item_idx on item_attr(name, item) where (name in ('weight', 'height', 'width'));這樣，至少查詢規劃器將有更多的材料可以使用。