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

子查询的查询效率非常低
子查询:查询时间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

效率起飞

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值