在mysql中select,在SELECT中使用SELECT在mysql查询中

最新推荐文章于 2024-09-19 17:37:23 发布

转载最新推荐文章于 2024-09-19 17:37:23 发布 · 984 阅读

文章标签：

#在mysql中select

本文探讨了在MySQL中使用嵌套SELECT导致查询变慢的问题，分析了原因——外层查询依赖内层查询，提出通过重构查询或分拆查询来提高性能。讲解了如何避免'DEPENDENT SUBQUERY'带来的巨慢查询，以及如何利用EXPLAIN SELECT进行优化。

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

)

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

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?

解决方案

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

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

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.