- 1【目标检测】数据集准备全流程记录_official-ssdd-open
- 2大数据毕业设计hadoop+spark+hive知识图谱酒店推荐系统_hadoop国内外研究现状
- 3判断是否utf8编码的算法_wx小程序 判断文件是否为utf8
- 4Linux 使用 yum安装 ELK服务，yum 安装elasticsearch和Kibana(未写完)
- 524.云原生ArgoCD高级之钩子_argocd postsync
- 6FPGA与MATLAB联合仿真FIR滤波器_matlab fpga fir quartus
- 7全网国内外20个开源知识管理工具
- 8【世界经济论坛】明日职业走向：大语言模型/AIGC引领的工作变革
- 9flink kafka 消费以及生产并行度设置_flink消费kafka并行度
- 10py2neo 创建关系_py2neo，neo4j：如何在两个现有节点之间创建关系
在mysql中select,在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')
)
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.
