连接查询（JOINS）与while语句的区别

Question

在我来到工作的公司中，他们运行着一个PHP/MySQL关系型数据库。我一直以为如果我需要从不同的表中提取不同的信息，我只需要做一个简单的连接就可以把数据拉进来，比如...

SELECT table_1.id, table_2.id FROM table_1 LEFT JOIN table_2 ON table_1.sub_id = table_2.id

当我到达现在的工作岗位时，这就是他们所做的事情。

<?php $query = mysql_query("SELECT sub_id FROM table_1");
while($rs = mysql_fetch_assoc($query)) {
    $query_2 = mysql_fetch_assoc(mysql_query("SELECT * FROM table_2 WHERE id = '{$rs['sub_id']}'"));
    //blah blah blah more queries
?>

当我问为什么他们用第二种方法时，他们说它比连接查询更快。他们管理着一个拥有数百万条记录的数据库，这些记录存储在不同的表格中，其中一些表格有点宽（按行计算）。他们说，他们希望避免在执行糟糕的查询时使用连接查询，否则会锁定一个或多个表格。还有一件事要记住的是，这个数据库附带了一个庞大的报表生成器，客户可以使用它来构建自己的报表，如果他们构建了一个大的报表，可能会引起一些麻烦。

我很困惑，所以我想向广大程序员公众提出这个问题。做while语句（一次较大的查询以获取许多行，然后进行许多小的子查询）是否比做连接查询（一次拉取较大的查询以获取所有所需数据）更快？只要索引正确，是否重要？还有一件事需要考虑的是，当前DB处于InnoDB格式。

谢谢！

更新于8/28/14

因此，我认为我应该对这个问题进行更新，并分享一下更长时间内的解决方案。在这次讨论之后，我决定在工作中重新构建报表生成器。我没有明确的结果数字，但我想分享一下结果。

我认为我有点过度，因为我将整个报表（返回的数据相当动态）转化为了大量的连接查询。大多数连接查询都将一个值与一个主键进行连接，因此它们非常快。如果报表要拉取30列数据，并且它拉取了2000条记录，那么每个字段都会运行一个查询以获取数据（因为该数据可能在不同的字段上）。 30 x 2000 = 60000，即使在较快的查询时间下，每个查询所需的时间为0.0003秒，总查询时间也为18秒左右（这基本上就是我记得的）。现在，我将查询重建为基于许多主键的大型联接查询，在可能的情况下，相同的报表加载时间约为2-3秒，其中大部分时间用于下载HTML。每个返回的记录根据所需的数据进行0-4次额外的查询（如果可以在连接中获取数据，则75％的时间不需要任何数据）。因此，相同的2000条记录将返回额外的0-8000次查询（比60000好多了）。

我认为while语句在某些情况下很有用，但正如下面评论中所述，基准测试才是关键。在我的情况下，连接操作是更好的选择，但在我的网站的其他领域，while语句更有用。例如，我有一个报告，客户可以请求按多个类别提取数据，并仅返回这些类别的数据。我曾经使用category_id IN(...,...,..,..,等等等)来处理50-500个ID，但索引会因此而崩溃。所以我将ID分成10组，并运行相同的查询x / 10次，结果比以前快得多，因为索引喜欢处理10个ID，而不是500个，因此我看到了查询的显着改进。

- n0nag0n

---

进行基准测试。你的同事不会因为网上某个人说他们错了就改变他们的想法。想出一个合适的测试用例，分别尝试两种方法。但是如果你能展示一些硬数据给他们看，他们怎么能反驳呢？ - Frank Farmer

Answer 1

如果索引使用得当，则使用JOIN几乎总是更有效的，但需要注意的是最佳效率并不总是等于最佳性能。

然而，并没有一种通用的答案，你应该使用EXPLAIN分析查询以确保索引确实被使用，并且避免不必要的临时表使用等。在某些情况下，条件会导致查询无法使用索引。在这种情况下，按照你提到的方法将查询分成多个部分可能会更快。

如果我在现有项目中遇到这样的代码，我会质疑它：检查查询，考虑执行查询的不同方式，确保这些事情已经被考虑，为或反对这种做法建立一个科学的、基于事实的案例。确保原始开发人员已经尽了他们的职责，因为不使用JOIN表面上指向了较差的数据库或查询设计。最终，结果大声地说明一切，如果所有的优化和纠正仍然导致比使用查询片段提供的更慢的连接，则更快的解决方案将占上风。基准测试并根据基准测试的结果进行操作；在软件设计中，没有一种情况可以为了遵循关于你应该或不应该做什么的任意规则而交换较差的性能。最佳性能方法就是最佳方法。

- Chris Baker

Answer 2

如果索引放置得当，进行大查询会更好。

其背后的逻辑：

1个查询= 1个对DB服务器的调用，然后处理查询（优化器和所有内容），最终返回结果。 N个查询意味着N个对数据库的调用，包括N个对优化器的调用，以及在糟糕情况下的I / O。

MySQL在JOIN上有优化工作。如果您使用while，则无法使用这些优化。

如先前的回答所述，请使用EXPLAIN检查是否有任何未使用索引的内容，如果使用了JOIN。此外，您应该检查分配给InnoDB缓存的内存以及分配给MySQL解析给定查询的内存。也许正是由于这些参数，数据库在执行JOIN时变慢。

- Lumbendil

Answer 3

我会说答案是，这要看情况。通常来说，我会说连接是答案，而在循环中执行多个查询是不好的做法，但是这完全取决于正在进行的操作。

对于你来说是这种情况吗？如果没有详细的表结构和索引信息以及外键的使用等，我们无法确定。如果您想要检查，最好的方法是尝试并查看结果。获取他们的查询，解释它们，编写自己的查询，并对其进行解释，看哪个更有效率。

- Jonnix

Answer 4

我不确定关于大型数据库，但在我的项目中，我总是尽量减少查询次数。查询会使用硬盘访问和（如果不在同一主机上）网络访问，这些都很慢。如果第一个查询中有很多条目，你可能会在每个页面上运行成千上万次的查询，这将会很慢。

- Alex Turpin

Answer 5

基准测试以找出实际答案。

根据您提供的示例，如果使用等效数据，数据库连接的联接使用更多资源的可能性非常小，而设置新连接并执行完全相同的操作（毕竟：仍然以与联接相同的方式连接数据，即使是外部完成）：如果是这样，引擎可以简单地重写以使用该外部路线来提高性能。

当联接使用更多资源时（除了索引问题之外），它主要来自每行检索数据的缺点，这意味着父表的信息将在每行中重复，即使这是冗余的。

这可能会导致性能问题，如果：

1. 有许多子项对应一个父项，并且
2. 您从父项获取大量数据（许多列或大型字段）

根据我的经验，减少查询数量几乎总是有益于性能（我通过组合查询进行了优化，而不是拆分查询）。

当然，正确使用索引是很好的建议，但乍一看，我认为它不会解释这两种情况之间的差异，因为相同的索引（或缺乏索引）将适用于两种情况。

- Inca