在JOIN中重写慢的SQL(子)查询 [英] Rewriting a slow SQL (sub) query in JOIN
本文介绍了在JOIN中重写慢的SQL(子)查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
因此,我有大量的慢速SQL查询,并将其缩小为慢速子查询,因此我想将其重写为JOIN.但是我被卡住了...(由于MAX
和GROUP BY
)
So I've got massive slow SQL query and I've narrowed it down to a slow sub-query, so I want to rewrite it to a JOIN. But I'm stuck... (due to the MAX
and GROUP BY
)
SELECT *
FROM local.advice AS aa
LEFT JOIN webdb.account AS oa ON oa.shortname = aa.shortname
WHERE aa.aa_id = ANY (SELECT MAX(dup.aa_id)
FROM local.advice AS dup
GROUP BY dup.shortname)
AND oa.cat LIKE '111'
ORDER BY aa.ram, aa.cpu DESC
LIMIT 0, 30
推荐答案
这是您查询的另一版本,其中子查询通过连接子句进行转换
Here is a different version of your query where the subquery is converted with a join clause
select * from local.advice aa
JOIN webdb.account oa ON oa.shortname = aa.shortname
join(
select max(aa_id) as aa_id,shortname from local.advice
group by shortname
)x on x.aa_id = aa.aa_id
where
oa.cat = '111'
order by aa.ram, aa.cpu DESC
limit 0,30
如果还没有添加索引,您可能还需要应用索引
Also you may need to apply indexes if they are not added already
alter table local.advice add index shortname_idx(shortname);
alter table webdb.account add index cat_shortname_idx(cat,shortname);
alter table local.advice add index ram_idx(ram);
alter table local.advice add index cpu_idx(cpu);
我假设aa_id
是主键,所以没有添加索引
I am assuming aa_id
is a primary key so did not add the index
在应用索引之前,请确保对表进行备份
Make sure to take a backup of the tables before applying the indexes
这篇关于在JOIN中重写慢的SQL(子)查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文