为具有子查询的查询优化SQL“Where”子句

Question

假设我有以下虚构的数据结构：

```html

让我们假设我有以下虚构的数据结构：

```

create table "country"
(
  country_id integer,  
  country_name varchar(50),
  continent varchar(50),
  constraint country_pkey primary key (country_id)
);

create table "person"
(
  person_id integer,
  person_name varchar(100),
  country_id integer,
  constraint person_pkey primary key (person_id)
);

create table "event"
(
  event_id integer,
  event_desc varchar(100),
  country_id integer,
  constraint event_pkey primary key (event_id)
);

我想查询每个国家的人员和事件行数。我决定使用子查询。

select c.country_name, sum(sub1.person_count) as person_count, sum(sub2.event_count) as event_count
from
  "country" c
  left join (select country_id, count(*) as person_count from "person" group by country_id) sub1
    on (c.country_id=sub1.country_id)
  left join (select country_id, count(*) as event_count from "event" group by country_id) sub2
    on (c.country_id=sub2.country_id)
group by c.country_name

我知道您可以使用SELECT语句在字段列表中实现此操作，但使用子查询的优点是我更灵活地修改SQL以使其更加概括并使用其他字段。比如说，如果我将查询改为按大陆显示，只需将字段“c.country_name”替换为“c.continent”即可轻松完成。

我的问题涉及筛选。如果我们添加一个WHERE子句：

select c.country_name, 
  sum(sub1.person_count) as person_count, 
  sum(sub2.event_count) as event_count
from
  "country" c
  left join (select country_id, count(*) as person_count from "person" group by country_id) sub1
    on (c.country_id=sub1.country_id)
  left join (select country_id, count(*) as event_count from "event" group by country_id) sub2
    on (c.country_id=sub2.country_id)
where c.country_name='UNITED STATES'
group by c.country_name

子查询似乎仍然对所有国家执行计数。假设人员和事件表很大，并且我已经在所有表上为country_id创建了索引。速度非常慢。数据库不应该只为过滤的国家执行子查询吗？我必须重新创建每个子查询的国家过滤器吗（这非常繁琐，代码不容易修改）？顺便说一下，我同时使用PostgreSQL 8.3和9.0，但我想其他数据库也会发生同样的情况。

- clj

---

执行计划是什么样子的？ - Kuberchaun

这只是一个假设的情景。但是，在运行非常类似的查询的生产数据库上，它对子查询中的表进行了顺序扫描。它从未使用索引。而且这些表很大。 - clj

Answer 1

数据库难道不应该只执行被筛选的国家的子查询吗？

不是这样的。像您的查询一样，第一步是从FROM子句中的所有表构造器中似乎构建一个工作表。然后才会评估WHERE子句。

想象一下，如果sub1和sub2都是基本表而不是子选择，您将如何执行此操作。它们都有两列，并且每个country_id都有一行。如果您想要JOIN所有行，可以编写以下代码。

from
  "country" c
  left join sub1 on (c.country_id=sub1.country_id)
  left join sub2 on (c.country_id=sub2.country_id)

但是如果你想要在单个行上进行JOIN操作，你需要编写类似于以下内容的代码。

from
  "country" c
  left join (select * from sub1 where country_id = ?)
    on (c.country_id=sub1.country_id)
  left join (select * from sub2 where country_id = ?)
    on (c.country_id=sub2.country_id)

Joe Celko曾参与开发早期的SQL标准，并经常在Usenet上写关于SQL计算顺序的问题。

- Mike Sherrill 'Cat Recall'

---

但是，当前的数据库系统不会在这种情况下进行优化吗？您建议我如何修改查询，以使其仍然具有灵活性/可重用性/易于修改，并且仍然能够快速执行？我认为尝试为每个子查询重新创建过滤器并不实际。 - clj

当然。所有的DBMS都会在它们可以优化的地方进行优化。但是您明确地告诉DBMS，您希望sub1成为一个包含两列（country_id、person_count）的表，并且您希望每个country_id有一行。sub2的推理相同。 - Mike Sherrill 'Cat Recall'

很遗憾我们没有一个好的解决方案。这使得SQL代码更难以维护/适应不断变化的过滤器，同时仍然保持快速执行。 - clj

在概念层面上，您的原始查询将被命名为“计算一个国家的人员事件数量”。它的名称和列名是数据库公共接口的一部分。WHERE子句实现了“一个国家”的部分。您可以编写一个PostgreSQL函数，以国家名称作为参数，并将该参数替换为所有必要的WHERE子句。任何更复杂的过滤（例如按人口、事件、人员性别或地址）在命名为“计算一个国家的人员事件数量”的dbms对象中都没有意义。 - Mike Sherrill 'Cat Recall'

查询的优点在于，我可以轻松地更改字段列表和分组列表，使其按大陆或特定国家进行分组，而无需更改from子句。这使得SQL代码更易于适应/维护。 - clj

由于from子句集中在国家表上，因此可以查询与国家相关的任何内容。例如，非常容易按大陆计算人员和事件，或使用国家表上的任何其他字段，甚至可以添加与国家相关的其他表的连接。当然，它有其信息获取的限制，但重点是从代码角度来看，它更加灵活/易于维护，因为from子句可以固定，并且只有选择和分组子句会发生变化，而不必将where子句过滤器级联/重写到每个子查询中。 - clj

Answer 2

你能使用country_id而不是country_name来过滤/分组行吗？我猜你没有按名称建立索引。 子查询不使用任何索引，因为你需要扫描整个表。如果要减少扫描次数，应该过滤数据。

- ravnur

---

我在 country_name 上建立了一个索引。但是，子查询仍然扫描整个表而不仅仅是特定的国家过滤器。 - clj

当然会这样。因为你没有过滤数据。索引仅在优化器认为您检索少量行时使用（使用索引进行快速扫描）。当您扫描整个表时，通过整个表运行以获取所有记录更快。所以...优化器很好，您应该阅读有关索引策略的文档 :) - ravnur

我有“where c.country_name ='UNITED STATES'”过滤器。那么，为什么子查询仍然要扫描整个表？ - clj

由于优化器的原因，它会按照从下到上的顺序分析查询。因此，在您的情况下，它将首先运行两个子查询并获取两个子集。然后它将连接country表并过滤数据。请对此查询进行解释，您将看到它的效果。 - ravnur

有没有什么办法可以避免将过滤器放在子查询中？比如说，如果我想将这个查询变成一个视图，那么如果你筛选特定的国家，使用这个视图会非常慢。如果你筛选特定的国家，那么要使这个视图运行快速是不可能的，因为这意味着你需要修改内部子查询以包含你的过滤器。 - clj

您可以单独运行子查询：1）获取person_count 2）获取event_count。在我看来，您正在试图重新发明自行车。 - ravnur