联接查询与多个查询

这太含糊了,无法给你一个与你的具体案件相关的答案。这取决于很多事情。Jeff Atwood(这个网站的创始人)实际上 wrote about this . 不过,在大多数情况下,如果您有正确的索引,并且正确地进行了连接,那么一次旅行通常比多次旅行要快。

对于内部联接,单个查询是有意义的,因为您只得到匹配的行。 对于左联接,多个查询更好…看看我做的以下基准:

1. 带有5个联接的单个查询

   查询: 8.074508秒

   结果大小:2268000

2. 连续5个查询

   组合查询时间: 0.00262秒

   结果大小:165(6+50+7+12+90)

.

注意,我们在两种情况下得到相同的结果(6 x 50 x 7 x 12 x 90=2268000)

左联接使用的内存与冗余数据呈指数级增长。

如果只进行两个表的联接,那么内存限制可能没有那么糟糕,但通常是三个或更多个表,因此需要进行不同的查询。

附带说明,我的mysql服务器就在我的应用服务器旁边…所以连接时间可以忽略不计。如果您的连接时间在几秒钟内,那么可能有一个好处

弗兰克

事实上,我自己来找这个问题的答案,在阅读了给出的答案后,我只能同意比较数据库查询性能的最佳方法是获得真实世界的数字,因为有很多变量需要考虑,但是,我也认为,比较它们之间的数字几乎没有什么好处。LL病例。我的意思是,这些数字应该总是与一个可接受的数字进行比较,而绝对不能相互比较。

我可以理解,如果一种查询方式需要0.02秒,而另一种查询方式需要20秒,这是一个巨大的区别。但是,如果一种查询方式需要0.00000000002秒,而另一种查询方式需要0.0000002秒呢?在这两种情况下,一种方法比另一种方法快1000倍,但它是不是 真的? 在第二种情况下仍然“大呼小叫”?

正如我个人所见:如果它运行良好,就选择简单的解决方案。

快速测试从50000行表中选择一行并与100000行表中的一行连接。基本上看起来像:

$id = mt_rand(1, 50000);
$row = $db->fetchOne("SELECT * FROM table1 WHERE id = " . $id);
$row = $db->fetchOne("SELECT * FROM table2 WHERE other_id = " . $row['other_id']);

VS

$id = mt_rand(1, 50000);
$db->fetchOne("SELECT table1.*, table2.*
    FROM table1
    LEFT JOIN table1.other_id = table2.other_id
    WHERE table1.id = " . $id);

两个选择方法花了3.7秒读取50000次,而加入在我家慢计算机上花了2.0秒。内部联接和左联接没有区别。获取多行(例如,在集合中使用)会产生类似的结果。

构造单独的查询和联接,然后对它们进行计时——没有什么比实际数字更有用了。

然后更好——在每个查询的开头添加“explain”。这将告诉您MySQL使用多少子查询来回答您的数据请求,以及每个查询扫描多少行。

与开发人员复杂性相比,根据数据库的复杂性,执行许多选择调用可能更简单。

尝试对连接和多个选择运行一些数据库统计信息。查看在您的环境中,联接是否比选择快/慢。

同样,如果将其更改为Join意味着需要额外的一天/周/月的开发工作,那么我将坚持多个选择

干杯,

BLT

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

TLDR回答:

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

如果一对多,使用一(或多) SELECT 具有服务器端代码优化的语句。

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

选择 对基于一对多关系的大型记录组进行查询(使用多个查询而不是联接)可产生最佳效率,如 加入 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语句中获取所有记录。这比 加入 ,它将获取这些记录的一小部分,一次一个,作为另一个查询的子组件。然后我用服务器端代码分析它,它看起来像…

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

何时不使用联接进行优化

加入 基于一对一关系的一大组记录与单个记录相比,产生最佳效率。 选择 语句,一个接一个,它只是得到下一个记录类型。

但是 加入 获取一对多关系的记录时效率低下。

示例:数据库博客有3个感兴趣的表:blogpost、tag和comment。

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

如果有1个blogpost、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 Server中,以及尝试删除重复项的代码)和网络资源(在SQL Server和代码服务器之间)。

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

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

当从另一个应用程序(如php)与数据库进行交互时,有一个参数是通过多次访问服务器。

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

谈到SQL,我不是新手,我认为开发人员,特别是年轻人有一种倾向,他们花很多时间尝试编写非常聪明的连接,因为它们看起来很聪明,而实际上有一些聪明的方法可以提取看起来很简单的数据。

最后一段是个人意见,但我希望这有帮助。我同意其他人的看法,尽管他们说你应该做基准。这两种方法都不是灵丹妙药。

它的吞吐量会更快吗?可能。但它也可能一次锁定更多的数据库对象(取决于您的数据库和模式),从而减少并发性。在我的经验中,人们经常被“减少数据库往返”的论点误导,当事实上,在大多数数据库位于同一局域网上的OLTP系统上,真正的瓶颈很少是网络。

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

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

有几个因素意味着没有二进制答案。什么是性能最佳的问题取决于您的环境。顺便说一下,如果带有标识符的单次选择不是次秒,那么配置可能有问题。

真正需要问的问题是您希望如何访问数据。single选择支持后期绑定。例如,如果您只需要员工信息,可以从Employees表中选择。外键关系可用于以后根据需要检索相关资源。选择将已经有一个关键点指向,所以它们应该非常快,您只需要检索您需要的内容。必须始终考虑网络延迟。

联接将一次检索所有数据。如果您正在生成报告或填充网格,这可能正是您想要的。在这种情况下,编译的和优化的联接比单个选择要快。记住,临时联接可能没有那么快——您应该编译它们(到存储过程中)。速度的答案取决于执行计划,该计划详细说明了DBMS检索数据所采取的步骤。

是否应该使用联接是关于联接是否 有道理 .只有在这一点上,性能才是需要考虑的,因为几乎所有其他情况都会导致 更糟的 性能。

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

这通常是由多对一对多关系引起的。例如, HoldOffHunger's answer 提到了对文章、标签和注释的单个查询。评论和一篇文章有关,标签也是……但是标签和评论无关。

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

在这种情况下,至少两个独立的查询显然更好。如果你试图加入标签和评论,因为两者之间没有直接的关系,你最终会得到标签和评论的每一种可能的组合。 many * many == manymany . 除此之外,由于文章和标签是不相关的,您可以并行执行这两个查询,从而获得潜在的收益。

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

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

这是您应该考虑加入的地方。除了是一个更自然的查询之外,大多数数据库系统(包括MySQL)都有很多聪明的人,他们在优化查询方面投入了大量的努力。对于单独的查询,由于每个查询都依赖于前一个查询的结果,因此不能并行执行查询,因此总时间不仅成为查询的实际执行时间,而且成为获取结果、筛选结果以获取下一个查询的ID、将行链接在一起等所花费的时间。

这个问题由来已久,但缺少一些基准。我与它的两个竞争对手对决:

• N+ 1查询
• 2个查询,第二个查询使用 WHERE IN(...) 或等同

结果很清楚:在mysql上, JOIN 是 许多的 更快。n+1查询会显著降低应用程序的性能:

也就是说,除非您选择了许多指向极少数不同的外来记录的记录。以下是极端情况的基准:

这在典型的应用程序中是不太可能发生的,除非您加入了一对多关系,在这种情况下,外键在另一个表上,并且您要多次复制主表数据。

外卖:

• 对于一对一关系,始终使用 加入
• 对于*-对许多关系,第二个查询可能更快

见 my article on Medium 更多信息。

是的,使用联接的一个查询会更快。尽管不知道正在查询的表的关系、数据集的大小或主键在哪里,但几乎不可能知道要快多少。

为什么不把这两种情况都测试出来,你就肯定知道了…