在 mysql 查询中的 SELECT 中使用 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8585592/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using SELECT within SELECT in mysql query
提问by Googlebot
It is common to use SELECTwithin SELECTto reduce the number of queries; but as I examined this leads to slow query (which is obviously harmful for mysql performance). I had a simple query as
通常使用SELECTinsideSELECT来减少查询次数；但是当我检查这会导致查询缓慢（这显然对 mysql 性能有害）。我有一个简单的查询
SELECT something
FROM posts
WHERE id IN (
SELECT tag_map.id
FROM tag_map
INNER JOIN tags
ON tags.tag_id=tag_map.tag_id
WHERE tag IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6')
)
This leads to slow queries of "query time 3-4s; lock time about 0.000090s; with about 200 rows examined".
这导致“查询时间 3-4 秒；锁定时间大约 0.000090 秒；检查了大约 200 行”的缓慢查询。
If I split the SELECTqueries, each of them will be quite fast; but this will increase the number of queries which is not good at high concurrency.
如果我拆分SELECT查询，每个查询都会非常快；但这会增加不擅长高并发的查询数量。
Is it the usual situation, or something is wrong with my coding?
这是通常的情况，还是我的编码有问题？
回答by Interrobang
In MySQL, doing a subquery like this is a "correlated query". This means that the results of the outer SELECTdepend on the result of the inner SELECT. The outcome is that your inner query is executed once per row, which is very slow.
在 MySQL 中，执行这样的子查询是“相关查询”。这意味着外部SELECT的结果取决于内部的结果SELECT。结果是您的内部查询每行执行一次，这非常慢。
You should refactor this query; whether you join twice or use two queries is mostly irrelevant. Joining twice would give you:
你应该重构这个查询；无论是加入两次还是使用两次查询都无关紧要。加入两次会给你：
SELECT something
FROM posts
INNER JOIN tag_map ON tag_map.id = posts.id
INNER JOIN tags ON tags.tag_id = tag_map.tag_id
WHERE tags.tag IN ('tag1', ...)
For more information, see the MySQL manual on converting subqueries to JOINs.
有关更多信息，请参阅有关将子查询转换为 JOIN的 MySQL 手册。
Tip: EXPLAIN SELECTwill show you how the optimizer plans on handling your query. If you see DEPENDENT SUBQUERYyou should refactor, these are mega-slow.
提示：EXPLAIN SELECT将向您展示优化器如何计划处理您的查询。如果你看到DEPENDENT SUBQUERY你应该重构，这些都是超级慢的。
回答by jclozano
You could improve it by using the following:
您可以使用以下方法改进它：
SELECT something
FROM posts
INNER JOIN tag_map ON tag_map.id = posts.id
INNER JOIN tags
ON tags.tag_id=tag_map.tag_id
WHERE <tablename>.tag IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6')
Just make sure you only select what you need and do not use *; also state in which table you have the tag column so you can substitute <tablename>
只要确保你只选择你需要的东西，不要使用 *; 还要说明您在哪个表中有标签列，以便您可以替换<tablename>
回答by Somnath Muluk
Join does filtering of results. First join will keep results having 1st ON condition satisfied and then 2nd condition gives final result on 2nd ON condition.
加入过滤结果。第一次加入将保持满足第一个 ON 条件的结果，然后第二个条件在第二个 ON 条件下给出最终结果。
SELECT something
FROM posts
INNER JOIN tag_map ON tag_map.id = posts.id
INNER JOIN tags ON tags.tag_id = tag_map.tag_id AND tags.tag IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6');
You can see these discussions on stack overflow :
你可以看到这些关于堆栈溢出的讨论：
Join helps to decrease time complexity and increases stability of server.
加入有助于降低时间复杂度并提高服务器的稳定性。
Information for converting sub queries to joins:
将子查询转换为连接的信息：
