mysql 优化sql

一.更新数据，原sql：

	set @beginTime='2019-11-26 00:00:00';
  set @endTime='2019-12-25 23:59:59';


#注册时间>30天(1-3单)-sql    370724

update users_jianfu set activity_id=10 where user_id in (
  
  select a.user_id from(
	select u.user_id  from  baojia_bike.users_jianfu as u ,(select user_id from  user_orders_last30days_temp where user_order_num>=1 and  user_order_num<=3 ) as b 
  where    u.user_id=b.user_id and u.register_time<@beginTime
	)as a 
)

优化：

 

    #优化
        set @beginTime='2019-11-26 00:00:00';
  set @endTime='2019-12-25 23:59:59';
update users_jianfu a, user_orders_last30days_temp as b set a.activity_id=10
where a.user_id=b.user_id and a.register_time<@beginTime  and b.user_order_num>=1 and  b.user_order_num<=3 

二. 查询

    set @beginTime='2019-11-25 00:00:00';
  set @endTime='2019-12-24 23:59:59';
select a.user_id from (select user_id from  user_orders_last30days_temp where user_order_num>=1 and  user_order_num<=3) as a left join (select user_id from baojia_bike.users_jianfu where register_time<@beginTime) as b  on a.user_id=b.user_id
 

优化后：

    #
        set @beginTime='2019-11-25 00:00:00';
  set @endTime='2019-12-24 23:59:59';
select a.user_id from  user_orders_last30days_temp as a left join  baojia_bike.users_jianfu  b on a.user_id=b.user_id where register_time<@beginTime and a.user_order_num>=1 and  a.user_order_num<=3

 

通过以上的例子看到，结论：mysql的语句能外层一次关联查询出来，不要嵌套子查询。