主页 / dba / 问题 / 3492
Accepted
Bruno
Asked: 2011-06-27 07:33:27 +0800 CST

使用 EAV 结构视图优化查询

  • 772

应用程序正在写入遵循 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))
postgresql performance

3 个回答

  1. Best Answer

    这是 EAV 设计的（众多）缺点之一。

    您无法真正改进 JOIN：由于必要的复杂性，基于成本的优化器无法得出完美的计划。它发现“足够好”

    建议：

    • 不要使用视图：使用聚合类型查询（例如，如果我同时匹配身高和体重，则 COUNT(*) = 2）
    • 使用触发器来维护一个真实的（或稀疏的）表并查询

    第一个选项扩展性更好，因为主 EAV 事实表上的一些索引可以很好地覆盖所有查询。

    • 7

  2. 你没有提到 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'));

    这样，至少查询规划器将有更多的材料可以使用。

    • 2

  3. 我会考虑尝试PostgreSQL 的 hstore 模块。

    • 1

相关问题