在SELECT中使用SELECT在mysql查询中 [英] Using SELECT within SELECT in mysql query

问题描述

通常在 SELECT 中使用 SELECT 可减少查询数量;但是我检查这导致慢查询（这显然有害于mysql性能）。我有一个简单的查询作为

It is common to use SELECT within SELECT to 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

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')
  )

查询时间3-4s;锁定时间约0.000090s;检查约200行。

This leads to slow queries of "query time 3-4s; lock time about 0.000090s; with about 200 rows examined".

如果我拆分 SELECT 查询，每个都会很快;但是这将增加在高并发性上不好的查询数量。

If I split the SELECT queries, each of them will be quite fast; but this will increase the number of queries which is not good at high concurrency.

这是常见的情况，还是我的编码有问题？

Is it the usual situation, or something is wrong with my coding?

推荐答案

在MySQL中，像这样的子查询是一个关联查询。这意味着外部 SELECT 的结果取决于内部 SELECT 的结果。结果是你的内部查询每行执行一次，这是非常慢的。

In MySQL, doing a subquery like this is a "correlated query". This means that the results of the outer SELECT depend on the result of the inner SELECT. The outcome is that your inner query is executed once per row, which is very slow.

你应该重构这个查询;无论是连接两次还是使用两个查询都是不相关的。加入两次会给你：

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', ...)

有关详细信息，请参阅将子查询转换为JOIN 。

For more information, see the MySQL manual on converting subqueries to JOINs.

提示： EXPLAIN SELECT 优化器计划处理您的查询。如果你看到 DEPENDENT SUBQUERY ，你应该重构，这些是非常慢的。

Tip: EXPLAIN SELECT will show you how the optimizer plans on handling your query. If you see DEPENDENT SUBQUERY you should refactor, these are mega-slow.

这篇关于在SELECT中使用SELECT在mysql查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！