mysql - 帮我算出一个 MySQL 查询

Question

这些是我的表:

Class- id- nameOrder- id- name- class_id (FK)Family- id- order_id (FK)- nameGenus- id- family_id (FK)- nameSpecies- id- genus_id (FK)- name

我正在尝试进行查询以获取其下没有任何物种的类名、目名和姓氏列表。您可以看到该表具有某种形式的层次结构，从 Order 一直到 Species。每个表都有外键 (FK)，它与层次结构中位于其上方的直接表相关。

努力在工作中做到这一点，但我做得不太好。任何帮助将不胜感激!

Answer 1

元答案(对前两个答案的评论):

使用 IN 往往会退化为非常类似于 IN 中所有项的 OR(析取)。表现不佳。

进行左连接并查找 null 是一种改进，但它是一种蒙昧主义。如果我们能说出我们的意思，让我们用最接近自然语言的方式说出来:

select f.name
from   family f left join genus g on f.id = g.family_id
       WHERE NOT EXISTS (select * from species c where c.id = g.id);

我们想要不存在的地方，所以如果我们能说“不存在的地方”就更好了。而且，子查询中的 select * 并不意味着它真的带回了整行，因此将 select * 替换为 select 并不是“优化” 1，至少在任何现代 RDBMS 上都不是。

此外，如果一个科有很多属(在生物学中，大多数科都有)，当我们只关心科时，我们将为每个(科、属)获取一行。因此，让我们为每个家庭排一行:

select DISTINCT f.name
from   family f left join genus g on f.id = g.family_id
       WHERE NOT EXISTS (select * from species c where c.id = g.id);

这仍然不是最优的。为什么？好吧，它满足了 OP 的要求，因为它找到了“空”属，但找不到没有属的科，即“空”科。我们也可以让它这样做吗？

select f.name
from   family f 
       WHERE NOT EXISTS (
       select * from genus g 
       join species c on c.id = g.id 
       where g.id = f.id);

我们甚至可以摆脱 distinct，因为我们不会将家庭加入任何事物。这是一种优化。

来自 OP 的评论:

That was a very lucid explanation. However, I'm curious as to why using IN or disjunctions is bad for performance. Can you elaborate on that or point me to a resource where I can learn more about the relative performance cost of different DB operations?

这样想。假设 SQL 中没有 IN 运算符。你会如何伪造 IN？

通过一系列 OR:

where foo in (1, 2, 3)

相当于

where ( foo = 1 ) or ( foo = 2 ) or (foo = 3 ) 

好吧，你说，但这仍然不能告诉我为什么它不好。这很糟糕，因为通常没有合适的方法来使用键或索引来查找它。因此，您得到的是 a) 表扫描，其中对于每个析取(或 IN 列表的谓词或元素)，该行都会被测试，直到测试为真或列表已用尽。或者 b) 您对这些析取中的每一个进行表扫描。第二种情况 (b) 实际上可能更好，这就是为什么您有时会看到带有 OR 的 select 变成了 OR union 的每个分支的一个 select:

 select * from table where x = 1 or x = 3 ;

 select * from table where x = 1 
 union select * from table where x = 3 ;

这并不是说您永远不能使用 OR 或 IN 列表。在某些情况下，查询优化器足够聪明，可以将 IN 列表转换为连接——而您得到的其他答案恰恰是最有可能发生这种情况的情况。

但是如果我们可以显式地将我们的查询变成一个连接，那么我们就不必怀疑查询优化器是否聪明。通常，连接是数据库最擅长的事情。