联接与子查询

我是一个老式的 MySQL 用户,并且始终喜欢JOIN不是子查询。但是如今,每个人都使用子查询,而我讨厌它。我不知道为什么

我缺乏理论知识来自行判断是否存在差异。子查询是否与JOIN一样好,因此JOIN担心吗?

答案

子查询是解决以下形式的问题的逻辑上正确的方法:“从 A 获取事实,以从 B 获取事实为条件”。在这种情况下,将 B 留在子查询中比执行联接更具逻辑意义。从实际意义上讲,它也是更安全的,因为您不必因与 B 的多次比赛而从 A 获得重复的事实时保持谨慎。

但是,实际上,答案通常取决于性能。一些优化器在进行联接与子查询时会吸柠檬,而另一些则以另一种方式吸柠檬,这是特定于优化器,特定于 DBMS 版本和特定于查询的。

从历史上看,显式联接通常会获胜,因此联接的既定常识是更好的,但优化器一直都在改进,因此我更喜欢先以逻辑上一致的方式编写查询,然后在性能约束允许的情况下进行重组。

在大多数情况下, JOIN的速度比子查询要快,而且子查询要快得多。

JOIN小号 RDBMS 可以创建一个执行计划,是更好地为您的查询,可以预测哪些数据应该被加载到被处理和保存时间,不同子查询在那里将运行所有的查询和加载所有的数据做处理。

子查询的优点在于,它们比JOIN更具可读性:这就是为什么大多数新 SQL 用户都喜欢它们的原因;这是简单的方法;但是就性能而言,JOINS 在大多数情况下都更好,即使它们也不难阅读。

取自 MySQL 手册13.2.10.11 将子查询重写为 Joins ):

LEFT [OUTER] JOIN 可以比同等子查询更快,因为服务器可能可以更好地对其进行优化 - 这不仅限于 MySQL Server。

因此子查询的速度可能比LEFT [OUTER] JOIN慢,但在我看来,它们的优势是可读性更高。

使用 EXPLAIN 查看数据库如何对数据执行查询。这个答案有一个很大的 “取决于” ...

当 PostgreSQL 认为一个子查询比另一个查询快时,可以将它重写为一个联接或一个子查询的联接。这完全取决于数据,索引,相关性,数据量,查询等。

在 2010 年,我会加入这个问题的作者,并会强烈投票赞成JOIN 。但是,有了更多的经验(尤其是在 MySQL 中),我可以说:是的,子查询会更好。我在这里阅读了多个答案。有人说,子查询速度更快,但是缺乏很好的解释。我希望我可以提供这个(非常)较晚的答案:

首先,让我说最重要的: 子查询有不同形式

第二个重要声明: 大小很重要

如果使用子查询,则应了解 DB-Server 如何执行子查询。特别是如果子查询被评估一次或每一行!另一方面,现代的数据库服务器可以进行很多优化。在某些情况下,子查询有助于优化查询,但是较新版本的 DB-Server 可能会使优化过时。

选择字段中的子查询

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

请注意,对于foo每个结果行都会执行一个子查询。如果可能的话,请避免这样做,这可能会大大降低对庞大数据集的查询速度。但是,如果子查询没有对foo引用,则 DB 服务器可以将其优化为静态内容,并且只能进行一次评估。

Where 语句中的子查询

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

如果幸运的话,数据库会在内部将其优化为JOIN 。否则,您的查询在庞大的数据集上将变得非常非常慢,因为它将对foo每一行执行子查询,而不仅仅是 select-type 中的结果。

联接语句中的子查询

SELECT moo, bar 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(bar), me FROM wilco GROUP BY me
    ) ON moo = me

这很有趣。我们将JOIN与子查询结合在一起。这就是子查询的真正优势。想象一下,数百万行数据集中wilco ,但只有少数不同的me 。现在,我们不再需要连接一个巨大的表,而是可以使用一个较小的临时表来连接。根据数据库的大小,这可能导致查询更快。使用CREATE TEMPORARY TABLE ...INSERT INTO ... SELECT ...可以达到相同的效果,这可以在非常复杂的查询中提供更好的可读性(但可以将数据集锁定在可重复的读取隔离级别)。

嵌套子查询

SELECT moo, bar
  FROM (
    SELECT moo, CONCAT(roger, wilco) AS bar
      FROM foo
      GROUP BY moo
      HAVING bar LIKE 'SpaceQ%'
  ) AS temp_foo
  ORDER BY bar

您可以将子查询嵌套在多个级别中。如果必须对结果进行分组或排序,这可以帮助处理庞大的数据集。通常,DB-Server 为此创建一个临时表,但是有时您不需要对整个表进行排序,而只需要对结果集进行排序。根据表的大小,这可能会提供更好的性能。

结论

子查询不能代替JOIN并且您不应该像这样使用它们(尽管可以)。以我的拙见,正确使用子查询是作为CREATE TEMPORARY TABLE ...的快速替代。一个好的子查询会以某种方式减少数据集,而您无法在JOINON语句中完成。如果子查询具有关键字GROUP BYDISTINCT ,并且最好不在选择字段或 where 语句中,那么它可能会大大提高性能。

首先,要比较两者,您应该将带有子查询的查询区分开来:

  1. 一类子查询,该子查询始终具有用联接编写的相应等效查询
  2. 不能使用连接重写的一类子查询

对于第一类查询,一个好的 RDBMS 会将联接和子查询视为等效项,并将产生相同的查询计划。

这些天连 mysql 都可以做到。

尽管如此,有时还是不能,但这并不意味着联接将永远获胜 - 我有在 mysql 中使用子查询的情况改善了性能。 (例如,如果某些因素阻止 mysql 计划程序正确估计成本,并且如果计划程序看不到 join-variant 和 subquery-variant 相同,则子查询可以通过强制某个路径来胜过联接)。

结论是,如果要确保哪个查询的性能更好,则应同时针对联接和子查询变量测试查询。

对于第二类 ,比较是没有意义的,因为无法使用联接重写那些查询,在这种情况下,子查询是完成所需任务的自然方法,因此您不应歧视它们。

SQL Server 的 MSDN 文档说

许多包含子查询的 Transact-SQL 语句也可以替换为联接。其他问题只能与子查询一起提出。在 Transact-SQL 中,包含子查询的语句与不包含子查询的语义等效版本之间通常没有性能差异。但是,在某些情况下必须检查是否存在,联接会产生更好的性能。否则,必须为外部查询的每个结果处理嵌套查询,以确保消除重复项。在这种情况下,联接方法将产生更好的结果。

所以如果你需要类似的东西

select * from t1 where exists select * from t2 where t2.parent=t1.id

尝试改用 join。在其他情况下,它没有区别。

我说:为子查询创建函数消除了混乱的问题,并允许您对子查询实施其他逻辑。因此,我建议尽可能为子查询创建函数。

代码混乱是一个大问题,数十年来,业界一直致力于避免代码混乱。

我认为所引用答案中未充分强调的是特定(使用)情况下可能出现的重复和有问题的结果的问题。

(尽管 Marcelo Cantos 确实提到了它)

我将引用斯坦福大学关于 SQL 的 Lagunita 课程中的示例。

学生桌

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  123 | Amy    |  3.9 |   1000 |
|  234 | Bob    |  3.6 |   1500 |
|  345 | Craig  |  3.5 |    500 |
|  456 | Doris  |  3.9 |   1000 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  987 | Helen  |  3.7 |    800 |
|  876 | Irene  |  3.9 |    400 |
|  765 | Jay    |  2.9 |   1500 |
|  654 | Amy    |  3.9 |   1000 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

申请表

(针对特定大学和专业的申请)

+------+----------+----------------+----------+
| sID  | cName    | major          | decision |
+------+----------+----------------+----------+
|  123 | Stanford | CS             | Y        |
|  123 | Stanford | EE             | N        |
|  123 | Berkeley | CS             | Y        |
|  123 | Cornell  | EE             | Y        |
|  234 | Berkeley | biology        | N        |
|  345 | MIT      | bioengineering | Y        |
|  345 | Cornell  | bioengineering | N        |
|  345 | Cornell  | CS             | Y        |
|  345 | Cornell  | EE             | N        |
|  678 | Stanford | history        | Y        |
|  987 | Stanford | CS             | Y        |
|  987 | Berkeley | CS             | Y        |
|  876 | Stanford | CS             | N        |
|  876 | MIT      | biology        | Y        |
|  876 | MIT      | marine biology | N        |
|  765 | Stanford | history        | Y        |
|  765 | Cornell  | history        | N        |
|  765 | Cornell  | psychology     | Y        |
|  543 | MIT      | CS             | N        |
+------+----------+----------------+----------+

让我们尝试查找已申请CS专业的学生的 GPA 成绩(不考虑大学)

使用子查询:

select GPA from Student where sID in (select sID from Apply where major = 'CS');

+------+
| GPA  |
+------+
|  3.9 |
|  3.5 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

该结果集的平均值为:

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');

+--------------------+
| avg(GPA)           |
+--------------------+
| 3.6800000000000006 |
+--------------------+

使用联接:

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+------+
| GPA  |
+------+
|  3.9 |
|  3.9 |
|  3.5 |
|  3.7 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

此结果集的平均值:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+-------------------+
| avg(GPA)          |
+-------------------+
| 3.714285714285714 |
+-------------------+

显然,第二次尝试在我们的用例中会产生误导性的结果,因为它会计算重复次数以计算平均值。这也是明显的是使用distinct与加盟 - 基于语句将不能消除问题,因为它会错误地保留三分之一的发生了的3.9分。鉴于我们实际上有两名(2)具有该分数的学生符合我们的查询条件,因此正确的情况是考虑3.9分数的两次(2)出现。

似乎在某些情况下,除任何性能问题外,子查询是最安全的方法。

在旧的 Mambo CMS 的大型数据库上运行:

SELECT id, alias
FROM
  mos_categories
WHERE
  id IN (
    SELECT
      DISTINCT catid
    FROM mos_content
  );

0 秒

SELECT
  DISTINCT mos_content.catid,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

〜3 秒

解释表明,他们检查的行数完全相同,但其中一行需要 3 秒,而另一行几乎是即时的。故事的道德启示?如果性能很重要(不是什么时候?),请尝试多种方式,看看哪种方式最快。

和...

SELECT
  DISTINCT mos_categories.id,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

0 秒

再次,相同的结果,检查了相同的行数。我的猜测是,找出 DISTINCT mos_content.catid 比发现 DISTINCT mos_categories.id 需要更长的时间。

子查询通常用于返回单行作为原子值,尽管它们可以用于通过 IN 关键字将值与多行进行比较。在 SQL 语句中几乎任何有意义的点都允许使用它们,包括目标列表,WHERE 子句等。一个简单的子查询可以用作搜索条件。例如,在一对表之间:

SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

请注意,对子查询的结果使用普通值运算符要求仅返回一个字段。如果您有兴趣检查一组其他值中是否存在单个值,请使用 IN:

SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');

这显然与 LEFT-JOIN 不同,在 LEFT-JOIN 中,您只想从表 A 和 B 中连接东西,即使联接条件在表 B 中找不到任何匹配记录,依此类推。

如果您只是担心速度,则必须检查数据库并编写一个好的查询,看看性能是否有显着差异。