mysql - 使用许多内部连接构建 SQL 查询的最佳方式？

Question

我有一个SQL查询需要执行多个内连接，如下:

SELECT DISTINCT adv.Email, adv.Credit, c.credit_id AS creditId, c.creditName AS creditName, a.Ad_id AS adId, a.adName
FROM placementlist pl
INNER JOIN
    (SELECT Ad_id, List_id FROM placements) AS p
ON pl.List_id = p.List_id
INNER JOIN
    (SELECT Ad_id, Name AS adName, credit_id FROM ad) AS a
ON ...
(few more inner joins)

我的问题如下:如何优化此查询？我的印象是，即使我当前查询数据库的方式创建了小的临时表(内部 SELECT 语句)，对未更改的表执行内部连接仍然是有利的，因为它们可能有大约 10,000 - 100,000 个条目(不是数百万)。然而，有人告诉我这不是解决问题的最佳方法，但我没有机会询问推荐的方法是什么。

这里最好的方法是什么？

Answer 1

使用派生表如

INNER JOIN (SELECT Ad_id, List_id FROM placements) AS p

不推荐。让dbms自己找出它需要的值

INNER JOIN placements AS p

而不是(再次)通过强制它在表上创建一个仅包含两个值的 View 来告诉它。 (并且使用 FROM tablename 更具可读性。)

对于 SQL，您主要是说您想看到什么，而不是如何实现。 (好吧，当然这只是一个经验法则。)所以如果除了 Ad_id 和 List_id 之外没有其他列用于表放置，dbms 将找到处理这个问题的最佳方法。不要试图让它以您的方式使用。

IN 子句也是如此，顺便说一下，您经常会看到 WHERE col IN (SELECT DISTINCT colx FROM ...) 而不是简单的 WHERE col IN (SELECT colx 来自 ...)。这完全相同，但是使用 DISTINCT 告诉 dbms“在查找 col 之前使子查询的行不同”。但是你为什么要强制它这样做呢？为什么不让它只使用 dbms 认为最合适的方法？

回到派生表:当它们真正做某事时使用它们，尤其是聚合，或者当它们使您的查询更具可读性时。

此外，

SELECT DISTINCT adv.Email, adv.Credit, ...

看起来也不太好。是的，有时您需要 SELECT DISTINCT，但通常不需要。大多数情况下，这只是一个迹象，表明您没有仔细考虑您的查询。

例如:您想要选择购买产品 X 的客户。在 SQL 中，您会说:客户购买 X 的情况。或者:客户在 X 购买者的集合中。

 select * from clients c where exists
   (select * from purchases p where p.clientid = c.clientid and product = 'X');

或者

 select * from clients where clientid in
   (select clientid from purchases where product = 'X');

您不会说:给我所有客户和 X 次购买的组合，然后将其归结为每个客户一次。

 select distinct c.* 
 from clients c
 join purchases p on p.clientid = c.clientid and product = 'X';

是的，连接所有需要的表非常容易，然后列出要选择的列，然后将 DISTINCT 放在前面。但这会使查询有点模糊，因为您不会像编写任务那样编写查询。当涉及到聚合时，它会使事情变得困难。以下查询是错误的，因为您将赚取的钱乘以花钱记录的数量，反之亦然。

select
  sum(money_spent.value),
  sum(money_earned.value)
from user
join money_spent on money_spent.userid = user.userid
join money_earned on money_earned.userid = user.userid;

以下可能看起来正确，但仍然不正确(它仅在值恰好是唯一的情况下有效):

select
  sum(distinct money_spent.value),
  sum(distinct money_earned.value)
from user
join money_spent on money_spent.userid = user.userid
join money_earned on money_earned.userid = user.userid;

同样:您不会说:“我想将每次购买与每次收入结合起来，然后……”。你会说:“我想要每个用户花费的金额和赚取的金额”。因此，您处理的不是单笔购买或收入，而是它们的总和。如

select
  sum(select value from money_spent where money_spent.userid = user.userid),
  sum(select value from money_earned where money_earned.userid = user.userid)
from user;

或者:

select
  spent.total,
  earned.total
from user
join (select userid, sum(value) as total from money_spent group by userid) spent 
  on spent.userid = user.userid
join (select userid, sum(value) as total from money_earned group by userid) earned 
  on earned.userid = user.userid;

所以你看，这就是派生表发挥作用的地方。