子查询vs连接 [英] Subqueries vs joins

问题描述

我重构了我们从另一家公司继承的一个缓慢的应用程序，使用内部连接，而不是一个子查询，如

I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like

where id in (select id from ... )

重构的查询运行速度大约100倍（〜50秒〜〜0.3）我预计会有所改善，但任何人都可以解释为什么这么大？ where子句中使用的列都被索引。 SQL执行查询的where子句每行一次还是什么？

The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected an improvement, but can anyone explain why it was so drastic? The columns used in the where clause were all indexed. Does SQL execute the query in the where clause once per row or something?

更新 - 解释结果：

区别在于where id in（）查询的第二部分 -

The difference is in the second part of the "where id in ()" query -

2   DEPENDENT SUBQUERY  submission_tags ref st_tag_id   st_tag_id   4   const   2966    Using where

vs 1带索引的连接行： p>

vs 1 indexed row with the join:

    SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   newsladder_production.st.submission_id  1   Using index

推荐答案

相关子查询（即，其中条件取决于从包含查询的行获取的值）将为每一行执行一次。不相关的子查询（其中条件独立于包含查询的一个）将在开始执行一次。 SQL引擎会自动进行这种区分。

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically.

但是，解释方案会给你一些脏的细节。

But, yeah, explain-plan will give you the dirty details.

这篇关于子查询vs连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！