<em id="hanht"></em>

    <dd id="hanht"></dd>

    <em id="hanht"><acronym id="hanht"></acronym></em>
    
    <button id="hanht"></button>
    <rp id="hanht"><object id="hanht"><blockquote id="hanht"></blockquote></object></rp><em id="hanht"></em>

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

    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秒左右.

    (編輯:錯新網)

    發表評論 共有條評論
    用戶名: 密碼:
    驗證碼: 匿名發表
    一级特黄大片欧美久久久久_一本一道久久综合狠狠老_JLZZ日本人年轻护士_欧美男男作爱VIDEOS可播放
      <em id="hanht"></em>

      <dd id="hanht"></dd>

      <em id="hanht"><acronym id="hanht"></acronym></em>
      
      <button id="hanht"></button>
      <rp id="hanht"><object id="hanht"><blockquote id="hanht"></blockquote></object></rp><em id="hanht"></em>