blocks|key|5610872|text|您可以先将子查询的结果选择到一个临时表中，然后在主查询中连接到它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5610873|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You could select the subquery's result into a temporary table first and then join to it in the main query.

blocks|key|3499004|text|最好的方法是将table1的WHERE条件放入FROM子句的子查询中。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3499005|SELECT+`table_1`.*+
FROM+(
++++++SELECT+*+FROM+`table_1`+WHERE+`table_1`.`id`+IN+(...)
+++++)
++INNER+JOIN+`table_2`+[...]
++INNER+JOIN+`table_3`+[...]
WHERE+[more+conditions]|code-block|syntax|javascript|3499006|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The best way is to put the WHERE conditions for table1 into a subquery in the FROM clause. 
EG:

<pre><code>SELECT `table_1`.* 
FROM (
 SELECT * FROM `table_1` WHERE `table_1`.`id` IN (...)
 )
 INNER JOIN `table_2` [...]
 INNER JOIN `table_3` [...]
WHERE [more conditions]
</code></pre>

blocks|key|3499013|text|不幸的是，不，你不能。子查询实际上为外部查询中的每一行运行一次。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3499014|实际上，我建议您将此连接转换为另一个连接，使用table_1.id作为到另一个表的键。|offset|length|style|CODE|3499015|entityMap^0|0|N|A|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Unfortunately, no, you cannot. Subqueries are actually run once for each row in the outer query.

I'd actually suggest that you convert this to another join, using <code>table_1.id</code> as your key to the other table.

blocks|key|3499031|text|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]|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3499032|如果内部表被正确索引，这里的子查询在严格的word意义上根本就不是“执行”的。|unstyled|3499033|由于子查询是IN表达式的一部分，因此将条件推入子查询并将其转换为EXISTS。|offset|length|style|CODE|3499034|实际上，此子查询在每个步骤中都会被计算：|3499035|EXISTS
(
SELECT++NULL
FROM++++[...]
WHERE+++id+=+table1.id
)|3499036|你可以在EXPLAIN+EXTENDED提供的详细描述中看到它。|3499037|这就是为什么它被称为DEPENDENT+SUBQUERY：每次求值的结果取决于table1.id的值。子查询本身是不相关的，它是相关的优化版本。|3499038|MySQL总是在更简单的过滤器之后对EXISTS子句求值(因为它们更容易求值，而且子查询有可能根本不被求值)。|3499039|如果希望一次性计算子查询，请将查询重写为：|3499040|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]|3499041|这将强制子查询在连接中领先，如果子查询与table_1相比较小，则效率较高；如果与table_1相比，子查询较大，则效率较低。|3499042|如果子查询中使用了[...].id上的索引，则将使用INDEX+FOR+GROUP-BY执行子查询。|3499043|entityMap^0|0|0|6|2|W|6|0|0|0|4|G|0|A|I|13|9|0|0|5|I|6|0|0|0|K|7|15|7|0|9|8|Q|I|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|17|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|18|8|@$I|19|J|1A|K|L]|$I|1B|J|1C|K|L]]|9|@]|A|$]]|$1|M|3|N|5|F|7|1D|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1E|8|@]|9|@]|A|$B|C]]|$1|Q|3|R|5|F|7|1F|8|@$I|1G|J|1H|K|L]]|9|@]|A|$]]|$1|S|3|T|5|F|7|1I|8|@$I|1J|J|1K|K|L]|$I|1L|J|1M|K|L]]|9|@]|A|$]]|$1|U|3|V|5|F|7|1N|8|@$I|1O|J|1P|K|L]|$I|1Q|J|1R|K|L]]|9|@]|A|$]]|$1|W|3|X|5|F|7|1S|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1T|8|@]|9|@]|A|$B|C]]|$1|10|3|11|5|F|7|1U|8|@$I|1V|J|1W|K|L]|$I|1X|J|1Y|K|L]]|9|@]|A|$]]|$1|12|3|13|5|F|7|1Z|8|@$I|20|J|21|K|L]|$I|22|J|23|K|L]]|9|@]|A|$]]|$1|14|3|-4|5|F|7|24|8|@]|9|@]|A|$]]]|15|$]]

<pre><code>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]
</code></pre>

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 <code>IN</code> expression, the condition is pushed into the subquery and it's transformed into an <code>EXISTS</code>.

In fact, this subquery is evaluated on each step:

<pre><code>EXISTS
(
SELECT NULL
FROM [...]
WHERE id = table1.id
)
</code></pre>

You can actually see it in the detailed description provided by <code>EXPLAIN EXTENDED</code>.

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

<code>MySQL</code> always evaluates the <code>EXISTS</code> 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:

<pre><code>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]
</code></pre>

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

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

blocks|key|2369072|text|这是mysql中的一个已知错误：http://bugs.mysql.com/bug.php?id=25926|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2369073|一种有用的解决方法是将子查询下推到另一个select+*+from+(subquery)+as+dt类型子查询中。|style|CODE|2369074|entityMap|0|LINK|mutability|MUTABLE|url|http://bugs.mysql.com/bug.php?id=25926^0|G|12|0|0|K|U|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@$A|U|B|V|F|G]]|9|@]|C|$]]|$1|H|3|-4|5|6|7|W|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

this is a known bug in mysql: <a href="http://bugs.mysql.com/bug.php?id=25926" rel="noreferrer">http://bugs.mysql.com/bug.php?id=25926</a>

a useful workaround is to push down the subquery into another <code>select * from (subquery) as dt</code> type subquery.

I have query like this:

<pre><code>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]
</code></pre>

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?

Can i force mysql to perform subquery first?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有这样的查询：SELECT `table_1`.* from `table_1`  INNER JOIN `table_2` [...]  INNER JOIN `table_3` [...]WHERE `table_1`.`id` IN(  SELECT `id` FROM [...])AND [more cond...

问是否可以强制mysql先执行子查询？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以强制mysql先执行子查询？EN