我可以强制mysql首先执行子查询吗?

本文介绍了我可以强制mysql首先执行子查询吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的查询:

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.