子查询与连接

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 个带连接的索引行:

vs 1 indexed row with the join:

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

推荐答案

相关子查询"(即，其中 where 条件取决于从包含查询的行中获得的值的查询)将对每一行执行一次.不相关的子查询(其中 where 条件独立于包含查询的子查询)将在开始时执行一次.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.

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