sql - PostgreSQL 如何处理 1 + n 查询？

Question

我正在针对 Sakila 数据库进行测试，请参阅 http://www.postgresqltutorial.com/postgresql-sample-database/ .该数据库包含三个关系:

• 电影:电影编号、片名
• Actor :actor_id, first_name
• film_actor:film_id, actor_id

我想列出所有电影，对于每部电影，我想列出在该特定电影中演出的所有 Actor 。我以以下查询结束:

select   film_id, title, array
         (
           select     first_name
           from       actor
           inner join film_actor
           on         actor.actor_id = film_actor.actor_id
           where      film_actor.film_id = film.film_id
         ) as actors
from     film
order by title;

从概念上讲，这是一个1 + n 查询:

one query: get films
n queries: for each film f
             f.actors = array(get actors playing in f)

我一直明白 1 + n 查询应该不惜一切代价避免，因为这不能很好地扩展。

所以这让我想知道:PostgreSQL 是如何在内部实现的？假设我们有 1000 部电影，它是否在内部执行 1000 个 select actor.first_name from actor inner join ... 查询？或者 PostgreSQL 在这方面更聪明，它会像下面这样吗？

1. one query:  get films
2. one query:  get actors related to these films while keeping reference to film_id
3. internally: for each film f
                 f.actors = array(subset of (2) according to film_id)

这执行 1 + 1 次查询。

Answer 1

您在嵌套循环中思考。这是您在使用关系数据库时应该克服的问题(除非您使用的是 MySQL)。

你描述为“1 + n”的是一个嵌套循环:你扫描一个表，对于找到的每一行，你扫描另一个表。

您的 SQL 查询的编写方式，PostgreSQL 别无选择，只能执行嵌套循环。

只要外表(在您的示例中为 film)只有几行，这就很好。一旦外表变大，性能就会迅速下降。

除了嵌套循环，PostgreSQL 还有另外两种连接策略:

• 哈希连接:扫描内部关系并创建哈希结构，其中哈希键是连接键。然后扫描外部关系，并为找到的每一行探测散列。

  将其视为一种哈希连接，但在内部，您拥有高效的内存数据结构。

• 合并连接:两个表都按连接键排序，并通过同时扫描结果进行合并。

建议您在编写查询时不要使用“相关子查询”，以便 PostgreSQL 可以选择最佳的连接策略:

SELECT film_id, f.title, array_agg(a.first_name)
FROM film f
   LEFT JOIN film_actor fa USING (film_id)
   LEFT JOIN actor a USING (actor_id)
GROUP BY f.title
ORDER BY f.title;

使用左外连接是为了即使电影没有 Actor 也能得到结果。