Session重疊問題學習(五)--最優化

壹頁書發表於2018-01-19
Session優化
週五晚上10點了.
這一週連續優化Session合併和拆分問題.每天都比前一天提升效能一倍以上.
終於在今天,用獨創的小花狸Session合併演算法達到了最優級別.

令人振奮的1.5秒到2秒級別.

時間已經很晚了,思路也有些不清晰了.先把程式碼貼出來.下週再仔細解釋一下這個奇妙演算法。

  1. DELIMITER $$  
  2.   
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()  
  4. BEGIN    
  5.     declare done int default 0;        
  6.     declare v_roomid bigint;    
  7.     declare v_time timestamp(6);    
  8.     declare v_cur_type smallint;  
  9.   
  10.     declare v_before_roomid bigint default -1;  
  11.     declare v_before_type smallint default -1;  
  12.     declare v_before_time timestamp(6) ;  
  13.   
  14.     declare v_num bigint default 0;  
  15.   
  16.   
  17.     declare cur_test CURSOR for select roomid,type,timepoint from tmp_time_point order by roomid,timepoint,type ;  
  18.     DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET done = 1;        
  19.   
  20.         
  21.     drop table if exists t1;    
  22.     drop table if exists t2;  
  23.     drop table if exists tmp_time_point;    
  24.     drop table if exists tmp_result;  
  25.     drop table if exists tmp_min_range;  
  26.   
  27.     CREATE temporary TABLE `t1` (    
  28.       `roomid` int(11) NOT NULL DEFAULT '0',    
  29.       `userid` bigint(20) NOT NULL DEFAULT '0',    
  30.       `s` timestamp(6),    
  31.       `e` timestamp(6),    
  32.       primary KEY `roomid` (`roomid`,`s`,`e`,`userid`)    
  33.     ) ENGINE=memory;    
  34.   
  35.    CREATE temporary TABLE `t2` (    
  36.       `roomid` int(11) NOT NULL DEFAULT '0',    
  37.       `s` timestamp(6),    
  38.       `e` timestamp(6),    
  39.       primary KEY `roomid` (`roomid`,`s`,`e`)    
  40.     ) ENGINE=memory;    
  41.   
  42.     CREATE temporary TABLE `tmp_min_range` (    
  43.       `roomid` int(11) NOT NULL DEFAULT '0',    
  44.       `s` timestamp(6),    
  45.       `e` timestamp(6),    
  46.       primary KEY `roomid` (`roomid`,`s`,`e`),  
  47.       key(roomid,e)  
  48.     ) ENGINE=memory;    
  49.   
  50.     create temporary table tmp_time_point(    
  51.             roomid bigint,    
  52.             timepoint timestamp(6),    
  53.             type smallint,  
  54.             key(roomid,timepoint)    
  55.     ) engine=memory;    
  56.       
  57.     create temporary table tmp_result(    
  58.             roomid bigint,    
  59.             timepoint timestamp(6),  
  60.             c int  
  61.     ) engine=memory;    
  62.     
  63. SET @A=0;    
  64. SET @B=0;    
  65. insert into t1(roomid,userid,s,e)  
  66. select distinct      
  67. roomid,  userid,      
  68. if(date(s)!=date(e) and id>1,date(s+interval id-1 day),s) s,      
  69. if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e      
  70. from (      
  71.     SELECT x.roomid,x.userid,s,e    
  72.     FROM   
  73.     (  
  74.         (  
  75.             SELECT @B:=@B+1 AS id,roomid,userid,s    
  76.             FROM (    
  77.                 SELECT DISTINCT roomid, userid, roomstart AS s        
  78.                 FROM u_room_log a        
  79.                 WHERE NOT EXISTS (SELECT *        
  80.                     FROM u_room_log b        
  81.                     WHERE a.roomid = b.roomid        
  82.                         AND a.userid = b.userid        
  83.                         AND a.roomstart > b.roomstart        
  84.                         AND a.roomstart <= b.roomend)  
  85.             ) AS p  
  86.         ) AS x,    
  87.         (  
  88.             SELECT @A:=@A+1 AS id,roomid,userid,e    
  89.             FROM   
  90.             (    
  91.                 SELECT DISTINCT roomid, userid, roomend AS e        
  92.                 FROM u_room_log a        
  93.                 WHERE NOT EXISTS (SELECT *        
  94.                     FROM u_room_log b        
  95.                     WHERE a.roomid = b.roomid        
  96.                         AND a.userid = b.userid        
  97.                         AND a.roomend >= b.roomstart        
  98.                         AND a.roomend < b.roomend)    
  99.             ) AS o  
  100.         ) AS y    
  101.     )   
  102.     WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid      
  103. ) t1 ,      
  104. nums       
  105. where  nums.id<=datediff(e,s)+1      
  106. ;      
  107.   
  108. insert into t2 (roomid,s,e)  
  109. select roomid,  
  110. s+interval startnum/1000000 second s,  
  111. e-interval endnum/1000000 second e  
  112.  from (  
  113.     select   
  114.     roomid,  
  115.     s,e,  
  116.     startnum,  
  117.     case when @eflag=eflag then @rn:=@rn+1 when @eflag:=eflag then @rn else @rn end endnum  
  118.     from (  
  119.         select * from (  
  120.             select case when @sflag=sflag then @rn:=@rn+1 when @sflag:=sflag then @rn else @rn end startnum,roomid,s,e,sflag,eflag from  
  121.             (  
  122.                 select * from   
  123.                 (  
  124.                     select t1.*,concat('[',roomid,'],',s) sflag,concat('[',roomid,'],',e) eflag from t1 order by roomid ,sflag  
  125.                 )a,(select @sflag:='',@rn:=0,@eflag:='') vars  
  126.             ) b    
  127.         ) bb order by roomid,eflag  
  128.     ) c  
  129. ) d ;  
  130.    
  131.     insert into tmp_time_point(roomid,timepoint,type) select roomid,s,1 from t2;  
  132.     insert into tmp_time_point(roomid,timepoint,type) select roomid,e,0 from t2;  
  133.      
  134.     insert into tmp_min_range(roomid,s,e)  
  135.                 select distinct roomid,starttime  starttime, endtime  endtime from (    
  136.                     select     
  137.                     if(@roomid=roomid,@d,'')  as starttime,@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') endtime    
  138.                     from tmp_time_point p,(select @d:='',@roomid:=-1) vars    
  139.                     order by roomid,timepoint    
  140.                 ) v4 where starttime!='' and date(starttime)=date(endtime);  
  141.   
  142.     open cur_test;        
  143.     repeat        
  144.         fetch cur_test into v_roomid,v_cur_type,v_time;        
  145.         if done !=1 then      
  146.             -- 第一行或者每個房間的第一行  
  147.             if v_before_roomid=-1 or v_roomid!=v_before_roomid  then  
  148.                 set v_before_roomid:=v_roomid;  
  149.                 set v_before_type:=1;  
  150.                 set v_before_time:='0000-00-00 00:00:00';  
  151.                 set v_num:=0;  
  152.             end if;  
  153.               
  154.               
  155.             if v_before_type=1  then  
  156.            
  157.                 set v_num:=v_num+1;  
  158.         
  159.                 insert into tmp_result(roomid,timepoint,c) values(v_roomid,v_time,v_num);  
  160.             end if;  
  161.               
  162.             if v_before_type=0 then  
  163.                  
  164.                 set v_num:=v_num-1;  
  165.   
  166.                 insert into tmp_result(roomid,timepoint,c) values(v_roomid,v_time,v_num);  
  167.             end if;  
  168.   
  169.             set v_before_roomid:=v_roomid;  
  170.             set v_before_type:=v_cur_type;  
  171.             set v_before_time:=v_time;  
  172.         end if;      
  173.     until done end repeat;        
  174.     close cur_test;     
  175.     
  176.     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(c)-1 c from (       
  177.         select a.roomid,a.s,a.e,r.c,r.timepoint from tmp_result r   
  178.         inner join   
  179.         tmp_min_range a on( r.timepoint=a.e and r.roomid=a.roomid)  
  180.         where     c>2  
  181.     ) a group by roomid,date(s);    
  182.   
  183. END  

和之前的演算法比較,結果一致。基本上都在1.6秒左右.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2150339/，如需轉載，請註明出處，否則將追究法律責任。

相關文章