ITPub博客

首页 > 数据库 > MySQL > Session重叠问题学习(九)--该问题第七次优化.优化合并算法

Session重叠问题学习(九)--该问题第七次优化.优化合并算法

原创 MySQL 作者:壹頁書 时间:2018-01-24 12:45:59 0 删除 编辑
接前文
Session重叠问题学习(二),这是问题和需求的描述,执行时间90秒
http://blog.itpub.net/29254281/viewspace-2150229/

Session重叠问题学习(三)--优化,一次优化后,执行时间25秒
http://blog.itpub.net/29254281/viewspace-2150259/

Session重叠问题学习(四)--再优化,二次优化后,执行时间10秒
http://blog.itpub.net/29254281/viewspace-2150297/

Session重叠问题学习(五)--最优化,三次优化后,执行时间1.6秒
http://blog.itpub.net/29254281/viewspace-2150339/

Session重叠问题学习(六)--极致优化,四次优化后,执行时间1250-1300毫秒
http://blog.itpub.net/29254281/viewspace-2150364/

Session重叠问题学习(七)--小花狸合并算法和最后一次优化.第五次优化,980毫秒
http://blog.itpub.net/29254281/viewspace-2150403/

Session重叠问题学习(八)--该问题第六次优化和Oracle版本.第六次优化,880毫秒
http://blog.itpub.net/29254281/viewspace-2150464/

虽然经过反复优化,还是需要880毫秒.
而Oracle仅仅需要200毫秒左右.
这主要是因为MySQL没有提供开窗函数.在合并同一房间同一用户的重合时间段时,需要大量扫描和计算.

这块可以修改成游标方式.减少扫描和计算.

本次优化之后,MySQL版本耗时420毫秒.

  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()  
  2. BEGIN            
  3.     declare done int default 0;        
  4.     declare v_roomid bigint;    
  5.     declare v_userid bigint;  
  6.     declare v_start timestamp;    
  7.     declare v_end timestamp;  
  8.   
  9.     declare v_prev_roomid bigint default -1;  
  10.     declare v_prev_userid bigint default -1;  
  11.     declare v_max_end timestamp;  
  12.   
  13.     declare cur_test CURSOR for select roomid,userid,roomstart,roomend from u_room_log order by roomid,userid,roomstart,roomend  ;    
  14.     
  15.     DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET done = 1;   
  16.       
  17.     drop table if exists t;  
  18.     drop table if exists t1;            
  19.     drop table if exists tmp_time_point;            
  20.     drop table if exists tmp_s;  
  21.       
  22.     create temporary table t(      
  23.         roomid bigint,      
  24.         userid bigint,      
  25.         s timestamp,      
  26.         e timestamp,      
  27.         broken int      
  28.     ) engine=memory;    
  29.   
  30.     CREATE temporary TABLE `t1` (            
  31.       `roomid` int(11) NOT NULL DEFAULT '0',            
  32.       `userid` bigint(20) NOT NULL DEFAULT '0',            
  33.       `s` timestamp,            
  34.       `e` timestamp,        
  35.        primary key(roomid,userid,s,e)        
  36.     ) ENGINE=memory;            
  37.           
  38.     
  39.     create temporary table tmp_time_point(            
  40.             roomid bigint,            
  41.             timepoint timestamp,            
  42.             type smallint,          
  43.             key(roomid,timepoint)            
  44.     ) engine=memory;            
  45.             
  46.     create temporary table tmp_s(        
  47.         roomid bigint,        
  48.         userid bigint,        
  49.         s timestamp,        
  50.         e timestamp,        
  51.         i int        
  52.     ) engine=memory;        
  53.       
  54.     open cur_test;        
  55.     repeat        
  56.         fetch cur_test into v_roomid,v_userid,v_start,v_end;        
  57.         if done !=1 then      
  58.             if(v_roomid=v_prev_roomid and v_userid=v_prev_userid) then   
  59.                 if(v_start<=v_max_end) then  
  60.                     insert into t values(v_roomid,v_userid,v_start,v_end,0);  
  61.                 else   
  62.                     insert into t values(v_roomid,v_userid,v_start,v_end,1);  
  63.                 end if;  
  64.                 if(v_end>=v_max_end) then  
  65.                     set v_max_end:=v_end;  
  66.                 end if;  
  67.                 set v_prev_roomid:=v_roomid;  
  68.                 set v_userid:=v_userid;  
  69.             else  
  70.                 set v_max_end:=v_end;  
  71.                 set v_prev_roomid:=v_roomid;  
  72.                 set v_prev_userid:=v_userid;  
  73.                 insert into t values(v_roomid,v_userid,v_start,v_end,1);  
  74.   
  75.             end if;  
  76.         end if;      
  77.     until done end repeat;        
  78.     close cur_test;     
  79.   
  80. insert into tmp_s  
  81. select roomid,userid,min(s) s,max(e) e,datediff(max(e),min(s))+1 i   from (  
  82.     select roomid,userid,s,e,case when @flag=flag then @rn:=@rn+broken when @flag:=flag then @rn:=broken end ran from (  
  83.         select roomid,userid,s,e,broken,concat(roomid,',',userid) flag from t,(select @flag:='',@rn:=0) vars  
  84.     ) a order by roomid,userid,s,e  
  85. ) b   
  86. group by roomid,userid,ran;       
  87.    
  88. select max(i) into @c from tmp_s;        
  89.             
  90. insert ignore into t1(roomid,userid,s,e)          
  91. select               
  92. roomid,  userid,              
  93. if(date(s)!=date(e) and id>1,date(s+interval id-1 day),s) s,              
  94. if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e              
  95. from tmp_s t1 STRAIGHT_JOIN            
  96. nums on(nums.id<=t1.i)        
  97. where nums.id<=@c        
  98.            
  99. ;              
  100.           
  101.     -- 开始点+1,结束点-1      
  102.     insert into tmp_time_point(roomid,timepoint,type) select roomid,s,1 from t1;          
  103.     insert into tmp_time_point(roomid,timepoint,type) select roomid,e,-1 from t1;       
  104.       
  105.   
  106.         select  roomid,date(s) dt,round(sum(timestampdiff(second,date_format(s,'%Y-%m-%d %H:%i:%s'),date_format(e,'%Y-%m-%d %H:%i:%s')))/60) ts,max(rn) c from (            
  107.                 select             
  108.                 if(@roomid=roomid,@d,'')  as s,@d:=str_to_date(timepoint,'%Y-%m-%d %H:%i:%s.%f'),@roomid:=roomid,p.roomid,str_to_date(timepoint,'%Y-%m-%d %H:%i:%s.%f') e  ,rn          
  109.                 from     
  110.                 (    
  111.                     select round(case when @roomid=roomid then @rn:=@rn+prevType when @roomid:=roomid then @rn:=prevType end) rn,b.prevType,roomid,timepoint,type  from (    
  112.                         select if(@roomid=roomid,@type,0) prevType ,case when @roomid=roomid then @type:=type when @roomid:=roomid then @type:=1 end,a.roomid,timepoint,type from (    
  113.                             select * from (    select roomid,timepoint,sum(type) type from tmp_time_point group by  roomid,timepoint) tmp_time_point,(select @roomid:=-1,@rn:=0,@type:=0) vars order by roomid ,timepoint    
  114.                         ) a    
  115.                     ) b order by roomid ,timepoint     
  116.                 )p,(select @d:='',@roomid:=-1) vars            
  117.                 order by roomid,timepoint            
  118.         ) v4 where s!='' and date(s)=date(e) and rn>=2        
  119.         group by roomid,date(s);           
  120.           
  121. END  







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2150486/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-10-19

  • 博文量
    621
  • 访问量
    6090202