Session重疊問題學習(六)--極致優化

壹頁書發表於2018-01-21
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/

週五晚上終於把這個演算法初步實現了.
連續加班忙碌了一個星期,終於有點曙光了.
從這個問題的緣起,到目前應該已經優化了快100倍了
但是週末的時候,想想還是不對.
小花狸Session合併演算法（對，以後這個演算法就叫這個名稱了）實現的合併速度應該是非常快的.代價僅僅是掃描一遍記錄.
這1.6秒到底用在哪裡了？

後來經過反覆除錯.發現還有兩塊可以優化改進的地方.
改進後的過程如下:

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

第一處改進
    原來同一房間同一使用者重疊時間合併,然後再拆分跨天資料，用的是一條SQL
    現在改進如下
  1. create temporary table tmp_s(  
  2.         roomid bigint,  
  3.         userid bigint,  
  4.         s timestamp,  
  5.         e timestamp,  
  6.         i int  
  7.     ) engine=memory;  
  8.       
  9. SET @A=0;      
  10. SET @B=0;      
  11.   
  12. insert into tmp_s  
  13.     SELECT x.roomid,x.userid,s,e,datediff(e,s)+1 i   
  14.     FROM     
  15.     (    
  16.         (    
  17.             SELECT @B:=@B+1 AS id,roomid,userid,s      
  18.             FROM (      
  19.                 SELECT DISTINCT roomid, userid, roomstart AS s          
  20.                 FROM u_room_log a          
  21.                 WHERE NOT EXISTS (SELECT *          
  22.                     FROM u_room_log b          
  23.                     WHERE a.roomid = b.roomid          
  24.                         AND a.userid = b.userid          
  25.                         AND a.roomstart > b.roomstart          
  26.                         AND a.roomstart <= b.roomend)    
  27.             ) AS p    
  28.         ) AS x,      
  29.         (    
  30.             SELECT @A:=@A+1 AS id,roomid,userid,e      
  31.             FROM     
  32.             (      
  33.                 SELECT DISTINCT roomid, userid, roomend AS e          
  34.                 FROM u_room_log a          
  35.                 WHERE NOT EXISTS (SELECT *          
  36.                     FROM u_room_log b          
  37.                     WHERE a.roomid = b.roomid          
  38.                         AND a.userid = b.userid          
  39.                         AND a.roomend >= b.roomstart          
  40.                         AND a.roomend < b.roomend)      
  41.             ) AS o    
  42.         ) AS y      
  43.     )     
  44.     WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid   ;     
  45.   
  46. select max(i) into @c from tmp_s;  
  47.       
  48. insert ignore into t1(roomid,userid,s,e)    
  49. select         
  50. roomid,  userid,        
  51. if(date(s)!=date(e) and id>1,date(s+interval id-1 day),s) s,        
  52. if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e        
  53. from tmp_s t1 STRAIGHT_JOIN      
  54. nums on(nums.id<=t1.i)  
  55. where nums.id<=@c  
  56.      
  57. ;        

先把同一房間同一使用者的重疊部分合並,然後暫存臨時表
記錄最大的間隔時間,然後再拆分資料

拆分資料的時候 使用STRAIGHT_JOIN 強制連線順序.
這樣避免因為數字輔助表過大,而導致效能陡然變差.


第二處改進
    原來使用distinct的查詢, 都改為在臨時表上增加主鍵.
    然後使用insert ignore into 代替 insert into 
    這樣大概優化了300毫秒

經過反覆優化之後，執行時間大致穩定在1250毫秒 至 1300 毫秒

各個部分耗時分析如下
填充tmp_s，合併同一房間同一使用者的重疊部分，耗時655毫秒
填充t1，拆分跨天的使用者資料，耗時62毫秒
填充t2，使用者時間段首尾相交或者首尾全部重合的資料拆分，耗時140毫秒
填充tmp_min_range,計算最小間隔範圍，耗時156毫秒
小花狸Session合併演算法，耗時219毫秒
結果統計展示，耗時47毫秒

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

相關文章