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

效率起飞