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

问题描述

我有这样的查询:

SELECT `table_1`.* from `table_1`内连接`table_2` [...]内连接`table_3` [...]WHERE `table_1`.`id` IN(从 [...] 中选择`id`)AND [更多条件]

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

有可能吗?

解决方案

SELECT `table_1`.*FROM `table_1`内部联接`table_2` [...]内部联接`table_3` [...]WHERE `table_1`.`id` IN(选择`id`从    [...])AND [更多条件]

如果内部表被正确索引，那么这里的子查询在严格意义上根本没有被执行".

由于子查询是 IN 表达式的一部分，因此将条件推送到子查询中并转换为 EXISTS.

事实上，这个子查询在每一步都被评估:

存在(选择空从    [...]WHERE id = table1.id)

其实可以在EXPLAIN EXTENDED提供的详细说明中看到.

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

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

如果您希望一次计算所有子查询，请将查询重写为:

SELECT table_1.*从    (选择不同的 id从    [...]) q加入表_1ON table_1.id = q.id加入表_2在      [...]加入表_3在      [...]WHERE [更多条件]

这会强制子查询在连接中领先，如果子查询比 table_1 小，效率更高，如果子查询比 table_1 大，效率低代码>.

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

I have query like this:

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]

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

Is is possible?

解决方案

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.

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
)

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

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 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]

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.

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

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