mysql - 查询以排除 ID 在另一个表中列出的行

Question

我很难弄清楚如何进行优化查询来执行以下操作，尽管听起来很简单。

假设我有一个名为 promo 的表(一列:ID)，另一个名为 promo_has_been_displayed_to_user 的表(两列:promo_id 和 user_id，promo_id 是引用 promo.ID 的外键)。我想要一个查询，该查询将返回 promo 中的所有行，这些行的 ID 字段未在 promo_has_been_displayed_to_user 中的任何行中提及，其中 promo_has_been_displayed_to_user.user_id 字段设置为 45。假设我在所有字段上都有索引。

(这个想法是，我有一个促销广告数据库和一个用户数据库，每次向用户展示广告时，我都会将广告展示给他们的信息存储在 promo_has_been_displayed_to_user 中。现在我想找到一个尚未向用户 45 展示的新广告。)

从理论上讲，执行此操作的最佳方法似乎如下:

1) 获取 promo_has_been_displayed_to_user 的子集(其中 user_id=45)，并在该子集中维护 user_id 字段的索引。 2) 对于 promo 中的每一行，获取 ID 并在步骤 1 中生成的子集中的索引 promo_id 字段上查找它。 3) 返回促销中您在第 2 步中未找到匹配项的所有行。

但是我如何构造一个反射(reflect)这一点的查询呢？

现在，我至少有两个查询将返回正确的答案(我已经用测试数据验证)；问题是我认为它们不会以最佳状态运行，原因如下:

1)

select * from promo
where ID not in (select promo_id from promo_has_been_displayed_to_user
                 where user_id=45)

此查询的问题在于，一旦您获得了“select promo_id from promo_has_been_displayed_to_user where user_id=45”返回的 ID 列表，我认为它只是一个列表(没有索引)，并且“不在”检查是通过一次仅检查该列表来实现的。如果 promo_has_been_displayed_to_user 的子集 user_id=45 结果很大，那么对于 promo 中的每一行，我们必须搜索一个没有索引的巨大列表。

2)

select * from promo p
where not exists (select * from promo_has_been_displayed_to_user
                  where promo_id = p.ID and user_id=45)

这一次，我们正在索引的 promo_id 字段上进行查找。但是，对于 promo 中的每一行，我都会查询整个 promo_has_been_displayed_to_user 表。如果只有 promo_has_been_displayed_to_user 的一小部分(其中 user_id=45)，这会造成浪费。

是否有一个查询可以结合两全其美的优点 - 我首先将 promo_has_been_displayed_to_user 缩减为 user_id=45 的子集，然后对于 promo 中的每一行，我对 promo_id 进行索引查找以查看是否有子集中的匹配行？

(这是 MySQL 5.0.95，尽管这听起来不是特定于数据库服务器的。)

Answer 1

您不能使用内部联接来执行此操作。您需要做的是反连接。通常，使用如下查询最容易完成此操作:

 SELECT * FROM A WHERE id NOT IN (SELECT id FROM B);

这是 SQL 中 antojoin 的基本语法。

但是，执行此操作的另一种方法是将 LEFT JOIN 转换为反连接，在某些数据库上，这性能更好:

SELECT A.* 
  FROM A
  LEFT JOIN B ON A.id = B.id
 WHERE A.id IS NOT NULL AND B.id IS NULL;

事实证明这是等效的，并且某些数据库可以更好地优化它。