代码之家 › 专栏 › 技术社区 › Ronyis

使用多个筛选器对一对多关系进行SQL查询

one-to-many join postgresql sql

Ronyis · 技术社区 · 7 年前

我有一些使用SQL的经验,但是我仍然不知道如何才能有效地执行下面的查询。

Box 和 Item . id 属性,它是主键(以及其他一些键),以及 项目 box_id , type , name . 每个表有数十亿条记录,每个盒子平均有10个项目。查询的分页大小应为10。我使用单列索引对所有 项目 属性。以下查询(第一页)需要很长的时间(超过一分钟):

SELECT Box.id FROM Box WHERE (EXISTS (SELECT 1 FROM Item WHERE Item.box_id = Box.id AND Item.type = 'my_type')) AND (EXISTS (SELECT 1 FROM Item WHERE Item.box_id = Box.id AND Item.name = 'my_name')) LIMIT 10

3 回复 | 直到 7 年前

The Impaler 7 年前

你想要所有的盒子,而不仅仅是10个。
按名字比较时有个拼写错误。应该是: Item.name = 'my_name'
你说“我已经为所有项目属性建立了索引”,我假设你为项目的所有列都建立了单列索引 Item 桌子。
列 id of Box是主键,因此它已经有了索引。

现在,我认为您使用的索引对于这个查询不是最佳的,因为它们只单独包含列。如果您还没有这些索引,请尝试创建以下索引:

create index ix1 on Item (box_id, type);

create index ix2 on Item (box_id, name);

是的,两个都是。再试一次查询,看看需要多长时间。

如果仍然很慢,请使用以下方式发布解释计划:

EXPLAIN ANALYZE
SELECT Box.id 
  FROM Box 
  WHERE 
(EXISTS (SELECT 1 FROM Item WHERE Item.box_id = Box.id AND Item.type = 'my_type')) 
  AND
(EXISTS (SELECT 1 FROM Item WHERE Item.box_id = Box.id AND Item.name = 'my_name'))

Brian 7 年前

INTERSECT

  SELECT Box_id FROM Item
  WHERE Item.type = 'my_type'
  INTERSECT
  SELECT Box_id FROM Item 
  WHERE Item.name = 'my_name'

注意:INTERSECT返回不同的值,因此不需要外部查询来获取满足条件的不同Box\u id值的列表。此查询确实返回孤立项(box表中没有box\u id的项),因此如果是这种情况,则可能需要外部查询。

Ilya Konyukhov 7 年前

像这样的?

SELECT DISTINCT ON (Box.id) Box.*
FROM Box
  JOIN Item I1 ON I1.box_id = Box.id AND I1.type = 'my_type'
  JOIN Item I2 ON I2.box_id = Box.id AND I2.name = 'my_name'
ORDER BY Box.id;

JOIN