循环中的MySQL查询与使用SQL连接 [英] MySQL query in a loop vs using a SQL join

问题描述

我正在公司内部就此问题的循环查询进行辩论:

I'm having an inner debate at my company about looping queries in this matter:

$sql = "
  SELECT foreign_key
  FROM t1";

foreach(fetchAll($sql) as $row)
{
  $sub_sql = "
    SELECT *
    FROM t2
    WHERE t2.id = " . $row['foreign_key'];

  foreach(fetchAll($sub_sql) as $sub_row)
  {
    // ...
  }
}

而不是像这样使用sql连接:

Instead of using an sql join like this:

$sql = "
  SELECT t2.*
  FROM t2
  JOIN t1
  ON t1.foreign_key = t2.id";

foreach(fetchAll($sql) as $row)
{
  // ...
}

---

有关此的其他信息，数据库巨大，有数百万行.

---

Additional information about this, the database is huge, millions of rows.

我当然已经搜索了这个问题的答案，但是没有人能够很好地回答这个问题，并且获得了很高的赞成票，这使我确信一种方法比另一种方法更好.

I have of course searched an answer to this question, but nobody can answer this in a a good way and with a lot of up votes that makes me certain that one way is better then the other.

有人可以向我解释为什么其中一种方法比另一种更好吗?

Can somebody explain to me why one of thees methods is better then the other one?

推荐答案

join方法通常被认为是更好的方法，只是因为它减少了将查询来回发送到数据库的开销.

The join method is generally considered better, if only because it reduces the overhead of sending queries back and forth to the database.

如果在表上具有适当的索引，则这两种方法的基本性能将相似.也就是说，两种方法都将使用适当的索引来获取结果.

If you have appropriate indexes on the tables, then the underlying performance of the two methods will be similar. That is, both methods will use appropriate indexes to fetch the results.

从数据库角度来看，join方法要好得多.它将数据逻辑整合在一处，使代码更加透明.它还允许数据库进行优化，这些优化在应用程序代码中可能并不明显.

From a database perspective, the join method is far superior. It consolidates the data logic in one place, making the code more transparent. It also allows the database to make optimizations that might not be apparent in application code.

这篇关于循环中的MySQL查询与使用SQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！