Session重疊問題學習(九)--該問題第七次優化.優化合並演算法

壹頁書發表於2018-01-24
Session優化演算法
接前文
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章