JOIN查询是否比多个查询快?(运行主查询,然后根据主查询的结果运行许多其他select)

我这么问是因为加入它们会使我的应用程序的设计复杂化

如果它们快了,有人能大致估计出快了多少吗?如果是1.5倍,我不在乎,但如果是10倍,我就在乎。


当前回答

这太模糊了,不能给你一个与你的具体情况相关的答案。这取决于很多因素。Jeff Atwood(这个网站的创始人)确实写过这个。不过,在大多数情况下,如果你有正确的索引,并且正确地执行join,那么执行一次旅行通常比执行几次旅行要快。

其他回答

是的,使用join的查询会更快。虽然不知道要查询的表之间的关系、数据集的大小或主键的位置,但几乎不可能说速度有多快。

为什么不把这两种情况都测试一下,然后你就会确定……

根据我的经验,我发现运行几个查询通常更快,特别是在检索大型数据集时。

当与来自另一个应用程序(如PHP)的数据库交互时,到服务器的一次访问要比多次访问多。

还有其他方法可以限制访问服务器的次数,同时仍然运行多个查询,这些方法通常不仅更快,而且使应用程序更易于阅读—例如mysqli_multi_query。

当涉及到SQL时,我不是新手,我认为对于开发人员,尤其是新手来说,有一种趋势是花费大量时间试图编写非常聪明的连接,因为它们看起来很聪明,而实际上有一些聪明的方法来提取数据,看起来很简单。

最后一段是我的个人观点,但我希望这能有所帮助。我同意其他人的观点,尽管他们认为你应该进行基准测试。两种方法都不是灵丹妙药。

是否应该使用连接首先是关于连接是否有意义。只有在这种情况下,性能才会被考虑,因为几乎所有其他情况都会导致明显更差的性能。

性能差异很大程度上取决于所查询信息的相关性。联接是有用的,当数据相关且索引正确时,联接速度很快,但它们通常会导致一些冗余,有时结果比需要的多。如果您的数据集不是直接相关的,那么将它们放在一个查询中将导致所谓的笛卡尔积(基本上是所有可能的行组合),这几乎不是您想要的结果。

这通常是由多对一对多关系引起的。例如,HoldOffHunger的回答提到了一个关于帖子、标签和评论的查询。评论与一篇文章相关,标签也是如此。但是标签与注释无关。

+------------+     +---------+     +---------+
|  comment   |     |   post  |     |  tag    |
|------------|*   1|---------|1   *|---------|
| post_id    |-----| post_id |-----| post_id |
| comment_id |     | ...     |     | tag_id  |
| user_id    |     |         |     | ...     |
| ...        |     |         |     | ...     |
+------------+     +---------+     +---------+

在这种情况下,最好至少有两个独立的查询。如果您试图连接标记和注释,因为两者之间没有直接的关系,您最终会得到标记和注释的所有可能组合。许多*许多=许多许多。除此之外,由于帖子和标签是不相关的,您可以并行执行这两个查询,从而获得潜在的收益。

让我们考虑一个不同的场景:您希望将评论附加到一篇文章,以及评论者的联系信息。

 +----------+     +------------+     +---------+
 |   user   |     |  comment   |     |   post  |
 |----------|1   *|------------|*   1|---------|
 | user_id  |-----| post_id    |-----| post_id |
 | username |     | user_id    |     | ...     |
 | ...      |     | ...        |     +---------+
 +----------+     +------------+

This is where you should consider a join. Aside from being a much more natural query, most database systems (including MySQL) have lots of smart people put lots of hard work into optimizing queries just like it. For separate queries, since each query depends on the results of the previous one, the queries can't be done in parallel, and the total time becomes not just the actual execute time of the queries, but also the time spent fetching results, sifting through them for IDs for the next query, linking rows together, etc.

这里有100个有用的查询链接,这些都是在Oracle数据库中测试的,但记住SQL是标准的,Oracle, MS SQL Server, MySQL和其他数据库之间的区别是SQL方言:

http://javaforlearn.com/100-sql-queries-learn/

真正的问题是:这些记录是一对一的关系还是一对多的关系?

TLDR回答:

如果是一对一,则使用JOIN语句。

如果一对多,则在服务器端代码优化中使用一个(或多个)SELECT语句。

为什么以及如何使用SELECT进行优化

基于一对多关系对大型记录组进行SELECT'ing(使用多个查询而不是连接)可产生最佳效率,因为JOIN'ing存在指数级内存泄漏问题。获取所有数据,然后使用服务器端脚本语言将其分类:

SELECT * FROM Address WHERE Personid IN(1,2,3);

结果:

Address.id : 1            // First person and their address
Address.Personid : 1
Address.City : "Boston"

Address.id : 2            // First person's second address
Address.Personid : 1
Address.City : "New York"

Address.id : 3            // Second person's address
Address.Personid : 2
Address.City : "Barcelona"

在这里,我将在一个select语句中获取所有记录。这比JOIN要好,JOIN每次只获取一小组这样的记录,作为另一个查询的子组件。然后我用服务器端代码解析它,看起来像……

<?php
    foreach($addresses as $address) {
         $persons[$address['Personid']]->Address[] = $address;
    }
?>

何时不使用JOIN进行优化

与多个SELECT语句相比,基于与单个记录的一对一关系连接一大组记录可以产生最佳效率,这些SELECT语句一个接一个地只获得下一个记录类型。

但是JOIN在获取一对多关系的记录时效率很低。

示例:数据库Blogs有3个感兴趣的表,Blogpost、Tag和Comment。

SELECT * from BlogPost
LEFT JOIN Tag ON Tag.BlogPostid = BlogPost.id
LEFT JOIN Comment ON Comment.BlogPostid = BlogPost.id;

如果有1篇博文,2个标签,2条评论,你会得到这样的结果:

Row1: tag1, comment1,
Row2: tag1, comment2,
Row3: tag2, comment1,
Row4: tag2, comment2,

注意每个记录是如何复制的。2个注释和2个标签等于4行。如果我们有4个注释和4个标签呢?不是8行,而是16行:

Row1: tag1, comment1,
Row2: tag1, comment2,
Row3: tag1, comment3,
Row4: tag1, comment4,
Row5: tag2, comment1,
Row6: tag2, comment2,
Row7: tag2, comment3,
Row8: tag2, comment4,
Row9: tag3, comment1,
Row10: tag3, comment2,
Row11: tag3, comment3,
Row12: tag3, comment4,
Row13: tag4, comment1,
Row14: tag4, comment2,
Row15: tag4, comment3,
Row16: tag4, comment4,

添加更多的表、更多的记录等,问题将迅速膨胀到数百行,其中大部分都是冗余数据。

这些复制品多少钱?内存(在SQL服务器和试图删除重复项的代码中)和网络资源(在SQL服务器和代码服务器之间)。

来源:https://dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html;https://dev.mysql.com/doc/workbench/en/wb-relationship-tools.html