首頁 > 學院 > 綜合知識 > 正文

Session重疊問題學習 -最優化

2022-07-31 18:43:53
字體:
來源:轉載
供稿:網友

       這一周連續優化Session合并和拆分問題.每天都比前一天提升性能一倍以上.
       終于在今天,用獨創的小花貍Session合并算法達到了最優級別.
 
       令人振奮的1.5秒到2秒級別.
 
       時間已經很晚了,思路也有些不清晰了.先把代碼貼出來.下周再仔細解釋一下這個奇妙算法。
 
DELIMITER $$  
  
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()  
BEGIN    
    declare done int default 0;        
    declare v_roomid bigint;    
    declare v_time timestamp(6);    
    declare v_cur_type smallint;  
  
    declare v_before_roomid bigint default -1;  
    declare v_before_type smallint default -1;  
    declare v_before_time timestamp(6) ;  
  
    declare v_num bigint default 0;  
  
  
    declare cur_test CURSOR for select roomid,type,timepoint from tmp_time_point order by roomid,timepoint,type ;  
    DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET done = 1;        
  
        
    drop table if exists t1;    
    drop table if exists t2;  
    drop table if exists tmp_time_point;    
    drop table if exists tmp_result;  
    drop table if exists tmp_min_range;  
  
    CREATE temporary TABLE `t1` (    
      `roomid` int(11) NOT NULL DEFAULT '0',    
      `userid` bigint(20) NOT NULL DEFAULT '0',    
      `s` timestamp(6),    
      `e` timestamp(6),    
      primary KEY `roomid` (`roomid`,`s`,`e`,`userid`)    
    ) ENGINE=memory;    
  
   CREATE temporary TABLE `t2` (    
      `roomid` int(11) NOT NULL DEFAULT '0',    
      `s` timestamp(6),    
      `e` timestamp(6),    
      primary KEY `roomid` (`roomid`,`s`,`e`)    
    ) ENGINE=memory;    
  
    CREATE temporary TABLE `tmp_min_range` (    
      `roomid` int(11) NOT NULL DEFAULT '0',    
      `s` timestamp(6),    
      `e` timestamp(6),    
      primary KEY `roomid` (`roomid`,`s`,`e`),  
      key(roomid,e)  
    ) ENGINE=memory;    
  
    create temporary table tmp_time_point(    
            roomid bigint,    
            timepoint timestamp(6),    
            type smallint,  
            key(roomid,timepoint)    
    ) engine=memory;    
      
    create temporary table tmp_result(    
            roomid bigint,    
            timepoint timestamp(6),  
            c int  
    ) engine=memory;    
    
SET @A=0;    
SET @B=0;    
insert into t1(roomid,userid,s,e)  
select distinct      
roomid,  userid,      
if(date(s)!=date(e) and id>1,date(s+interval id-1 date(s+interval id-1 date(e) ,e,date_format(s+interval id-1 '%Y-%m-%d 23:59:59')) e      
from (      
    SELECT x.roomid,x.userid,s,e    
    FROM   
    (  
        (  
            SELECT @B:=@B+1 AS id,roomid,userid,s    
            FROM (    
                SELECT DISTINCT roomid, userid, roomstart AS s        
                FROM u_room_log a        
                WHERE NOT EXISTS (SELECT *        
                    FROM u_room_log b        
                    WHERE a.roomid = b.roomid        
                        AND a.userid = b.userid        
                        AND a.roomstart > b.roomstart        
                        AND a.roomstart <= b.roomend)  
            ) AS p  
        ) AS x,    
        (  
            SELECT @A:=@A+1 AS id,roomid,userid,e    
            FROM   
            (    
                SELECT DISTINCT roomid, userid, roomend AS e        
                FROM u_room_log a        
                WHERE NOT EXISTS (SELECT *        
                    FROM u_room_log b        
                    WHERE a.roomid = b.roomid        
                        AND a.userid = b.userid        
                        AND a.roomend >= b.roomstart        
                        AND a.roomend < b.roomend)    
            ) AS o  
        ) AS y    
    )   
    WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid      
) t1 ,      
nums       
where  nums.id<=datediff(e,s)+1      
;      
  
insert into t2 (roomid,s,e)  
select roomid,  
s+interval startnum/1000000 second s,  
e-interval endnum/1000000 second e  
 from (  
    select   
    roomid,  
    s,e,  
    startnum,  
    when @eflag=eflag then @rn:=@rn+1 when @eflag:=eflag then @rn else @rn end endnum  
    from (  
        select * from (  
            select when @sflag=sflag then @rn:=@rn+1 when @sflag:=sflag then @rn else @rn end startnum,roomid,s,e,sflag,eflag from  
            (  
                select * from   
                (  
                    select t1.*,concat('[',roomid,'],',s) sflag,concat('[',roomid,'],',e) eflag from t1 order by roomid ,sflag  
                )a,(select @sflag:='',@rn:=0,@eflag:='') vars  
            ) b    
        ) bb order by roomid,eflag  
    ) c  
) d ;  
   
    insert into tmp_time_point(roomid,timepoint,type) select roomid,s,1 from t2;  
    insert into tmp_time_point(roomid,timepoint,type) select roomid,e,0 from t2;  
     
    insert into tmp_min_range(roomid,s,e)  
                select distinct roomid,starttime  starttime, endtime  endtime from (    
                    select     
                    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    
                    from tmp_time_point p,(select @d:='',@roomid:=-1) vars    
                    order by roomid,timepoint    
                ) v4 where starttime!='' and date(starttime)=date(endtime);  
  
    open cur_test;        
    repeat        
        fetch cur_test into v_roomid,v_cur_type,v_time;        
        if done !=1 then      
            -- 第一行或者每個房間的第一行  
            if v_before_roomid=-1 or v_roomid!=v_before_roomid  then  
                set v_before_roomid:=v_roomid;  
                set v_before_type:=1;  
                set v_before_time:='0000-00-00 00:00:00';  
                set v_num:=0;  
            end if;  
              
              
            if v_before_type=1  then  
           
                set v_num:=v_num+1;  
        
                insert into tmp_result(roomid,timepoint,c) values(v_roomid,v_time,v_num);  
            end if;  
              
            if v_before_type=0 then  
                 
                set v_num:=v_num-1;  
  
                insert into tmp_result(roomid,timepoint,c) values(v_roomid,v_time,v_num);  
            end if;  
  
            set v_before_roomid:=v_roomid;  
            set v_before_type:=v_cur_type;  
            set v_before_time:=v_time;  
        end if;      
    until done end repeat;        
    close cur_test;     
    
    select roomid,date(s) dt,round(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 (       
        select a.roomid,a.s,a.e,r.c,r.timepoint from tmp_result r   
        inner join   
        tmp_min_range a on( r.timepoint=a.e and r.roomid=a.roomid)  
        where     c>2  
    ) a group by roomid,date(s);    
  
END  
 
和之前的算法比較,結果一致?;旧隙荚?.6秒左右.

(編輯:錯新網)

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
金玫玫床戏