mysql语句优化首先去掉子查询

最新推荐文章于 2024-01-17 17:52:12 发布

我叫你把音乐关掉

最新推荐文章于 2024-01-17 17:52:12 发布

阅读量241

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_35510729/article/details/118306882

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

子查询的查询效率非常低
子查询:查询时间14秒

select u.uid,u.phone,u.name, u.createtime,
(select count(*) from tc_shop s where s.uid=u.uid and s.status=1) as shopnums,
coalesce((select sum(h.amount) from tc_hongmi_record h where  h.in_out=1 and (h.type=1 or h.type=4) and h.b_phone = u.phone),0) as amount 
from tc_user u 
where u.invitecode='.123456.' 
and (u.name like "%1'%" or u.phone like "%1%") 
order by u.createtime desc limit 0,20

左链接不到一秒

select u.uid,u.phone,u.name, u.createtime,shopnums,
coalesce(suma,0) as amount 
from tc_user u 
left join  (select count(id) as shopnums,uid from tc_shop where status=1)s on s.uid=u.uid  
left join  ((select sum(amount) as suma,b_phone from tc_hongmi_record where  in_out=1 and (type=1 or type=4)))h on h.b_phone = u.phone 
where u.invitecode=123456 and (u.name like "%1%" or u.phone like "%1%") 
order by u.createtime desc limit 0,20