Mysql查询优化

Mysql  /  houtizong 发布于 2年前   157

   项目需要查询游戏用户的流失用户，对于流失用户的定义为：当天登陆，之后三天未登录的用户视为流失用户，目前有数据表 login_info 分别存储了用户id，登陆时间（int 11）。
   最简单的实现方式：SELECT DISTINCT uid from login_info WHERE login_time between $time1['begin'] AND $time1['end']
and uid not in (select DISTINCT uid from login_info where login_time between $time2['begin'] and $time2['end'])  其中的$time1  存储为一天的时间起止 $time2存储为流失的时间范围（这里是三天），但是实际当中查询速度相当慢。
    群里找了个高手，给出了另外一种解决办法，最终sql如下：

select uid from (select uid,MAX(login_time) as max,MIN(login_time) as min FROM login_info where login_time between 1361721600 and 1362067199 GROUP BY uid ) as A
where max between 1361721600 AND 1361807999

查询时间大大缩短，不得不佩服高手啊！！！具体就不做解释了