我可以强制mysql首先执行子查询吗? [英] Can i force mysql to perform subquery first?

问题描述

我有这样的查询:

SELECT `table_1`.* from `table_1`
  INNER JOIN `table_2` [...]
  INNER JOIN `table_3` [...]
WHERE `table_1`.`id` IN(
  SELECT `id` FROM [...]
)
AND [more conditions]

当我使用EXPLAIN时，结尾处是"DEPENDENT SUBQUERY"，但我希望先执行此子查询，然后再执行其他条件.

When I use EXPLAIN, there is 'DEPENDENT SUBQUERY' at the end, but I want this subquery to be performed first, before other conditions.

有可能吗?

推荐答案

SELECT  `table_1`.*
FROM    `table_1`
INNER JOIN
        `table_2` [...]
INNER JOIN
        `table_3` [...]
WHERE   `table_1`.`id` IN
        (
        SELECT  `id`
        FROM    [...]
        )
        AND [more conditions]

如果内部表已正确索引，则严格意义上来说，这里的子查询根本不会执行".

If the inner table is properly indexed, the subquery here is not being "performed" at all in a strict sense of word.

由于子查询是IN表达式的一部分，因此条件被压入子查询中，并转换为EXISTS.

Since the subquery is a part of an IN expression, the condition is pushed into the subquery and it's transformed into an EXISTS.

实际上，此子查询是在每个步骤上评估的:

In fact, this subquery is evaluated on each step:

EXISTS
(
SELECT  NULL
FROM    [...]
WHERE   id = table1.id
)

您实际上可以在EXPLAIN EXTENDED提供的详细说明中看到它.

You can actually see it in the detailed description provided by EXPLAIN EXTENDED.

这就是为什么它被称为DEPENDENT SUBQUERY的原因:每次评估的结果取决于table1.id的值.这样的子查询是不相关的，它是相关的优化版本.

That's why it's called DEPENDENT SUBQUERY: the result of each evaluation depends on the value of table1.id. The subquery as such is not correlated, it's the optimized version that is correlated.

MySQL总是在更简单的过滤器之后评估EXISTS子句(因为它们更容易评估，并且有可能根本不会评估子查询).

MySQL always evaluates the EXISTS clause after the more simple filters (since they are much easier to evaluate and there is a probability that the subquery won't be evaluated at all).

如果您希望一次评估所有子查询，请按以下方式重写查询:

If you want the subquery to be evaluated all at once, rewrite the query as this:

SELECT  table_1.*
FROM    (
        SELECT  DISTINCT id
        FROM    [...]
        ) q
JOIN    table_1
ON      table_1.id = q.id
JOIN    table_2
ON      [...]
JOIN    table_3
ON      [...]
WHERE   [more conditions]

这将强制子查询进入联接，如果子查询比table_1小，则效率更高；如果子查询比table_1大，则效率更低.

This forces the subquery to be leading in the join, which is more efficient if the subquery is small compared to table_1, and less efficient if the subquery is large compared to table_1.

如果子查询中使用的[...].id上有索引，则将使用INDEX FOR GROUP-BY执行子查询.

If there is an index on [...].id used in the subquery, the subquery will be performed using an INDEX FOR GROUP-BY.

这篇关于我可以强制mysql首先执行子查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！