溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

Session重疊問題學(xué)習(xí)(四)--再優(yōu)化

發(fā)布時(shí)間：2020-08-11 10:31:08 來源：ITPUB博客 閱讀：133 作者：壹頁(yè)書 欄目：MySQL數(shù)據(jù)庫(kù)
接前文:
需求描述和第一版解決方案(執(zhí)行時(shí)間90秒)
http://blog.itpub.net/29254281/viewspace-2150229/

優(yōu)化和修改bug的版本(執(zhí)行時(shí)間25秒)
http://blog.itpub.net/29254281/viewspace-2150259/


我覺得在集合思維處理方式中,前文已經(jīng)達(dá)到最優(yōu)了.

如果放棄完全的集合處理思維,實(shí)際上還可以更加的優(yōu)化.

前文的幾個(gè)問題.
1.引入了過多的表結(jié)構(gòu).
2.寫表本身也花費(fèi)了時(shí)間.
3.前文按天批處理,粒度還是細(xì)了.應(yīng)該一把批量全出最快.
4.前文計(jì)算最小間隔范圍的部分,因?yàn)閼?yīng)用集合化思維,不好理解性能還差.

前文計(jì)算最小間隔范圍的部分如下
  1. select roomid,as DATETIME) starttime,as DATETIME) endtime from (    
  2.      select @d as starttime,@d:=d,v3.roomid,v3.d endtime from (    
  3.                 select distinct roomid,     
  4.                 when nums.id=1 then v1s       
  5.                 when nums.id=2 then v1e       
  6.                 when nums.id=3 then v2s       
  7.                 when nums.id=4 then v2e       
  8.                 end d   from (    
  9.                     select   v1.roomid, v1.s v1s,v1.e v1e,v2.s v2s,v2.e v2e    
  10.                     from t1 v1    
  11.                     inner join t1 v2 on ((v1.s between v2.s and v2.e or v1.e between v2.s and v2.e )  and v1.roomid=v2.roomid)     
  12.                     where v2.roomid in(select distinct roomid from t1 where date(s)=pTime)    
  13.                     and v2.s>=pTime and v2.s<(pTime+interval '1' and (v2.roomid,v2.userid,v2.s,v2.e)!= (v1.roomid,v1.userid,v1.s,v1.e)     
  14.                 ) a,nums where nums.id<=4    
  15.                 order by roomid,d    
  16.     ) v3,(select @d:='') vars    
  17. ) v4 where starttime!='' 

該部分使用集合處理方式,不好理解性能還差.

這塊可以通過游標(biāo)寫臨時(shí)表輕易解決。
本質(zhì)上最小范圍就是
每天每個(gè)房間每個(gè)記錄的開始時(shí)間和結(jié)束時(shí)間都扣出來作為一行 排序。

然后找到每個(gè)時(shí)間最近的下一個(gè)時(shí)間,作為最小時(shí)間范圍.

如果使用游標(biāo),遍歷一遍即可.

  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_start timestamp;  
  8.     declare v_end timestamp;  
  9.     declare cur_test CURSOR for select roomid,s,e from t1  ;  
  10.   
  11.     DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET done = 1;      
  12.       
  13.     drop table if exists t1;  
  14.     drop table if exists tmp_time_point;  
  15.     CREATE temporary TABLE `t1` (  
  16.       `roomid` int(11) NOT NULL DEFAULT '0',  
  17.       `userid` bigint(20) NOT NULL DEFAULT '0',  
  18.       `s` timestamp NOT NULL DEFAULT ON UPDATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
  19.       primary KEY `roomid` (`roomid`,`s`,`e`,`userid`)  
  20.     ) ENGINE=InnoDB;  
  21.   
  22.     create temporary table tmp_time_point(  
  23.             roomid bigint,  
  24.             timepoint timestamp,  
  25.             primary key(roomid,timepoint)  
  26.     ) engine=memory;  
  27.   
  28.   
  29. insert into t1  
  30. select distinct    
  31. roomid,    
  32. userid,    
  33. 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    
  34. from (    
  35. SELECT DISTINCT s.roomid, s.userid, s.s, (      
  36.         SELECT MIN(e)      
  37.         FROM (SELECT DISTINCT roomid, userid, roomend AS e      
  38.             FROM u_room_log a      
  39.             WHERE NOT EXISTS (SELECT *      
  40.                 FROM u_room_log b      
  41.                 WHERE a.roomid = b.roomid      
  42.                     AND a.userid = b.userid      
  43.                     AND a.roomend >= b.roomstart      
  44.                     AND a.roomend < b.roomend)      
  45.             ) s2      
  46.         WHERE s2.e > s.s      
  47.             AND s.roomid = s2.roomid      
  48.             AND s.userid = s2.userid      
  49.         ) AS e      
  50. FROM (SELECT DISTINCT roomid, userid, roomstart AS s      
  51.     FROM u_room_log a      
  52.     WHERE NOT EXISTS (SELECT *      
  53.         FROM u_room_log b      
  54.         WHERE a.roomid = b.roomid      
  55.             AND a.userid = b.userid      
  56.             AND a.roomstart > b.roomstart      
  57.             AND a.roomstart <= b.roomend)      
  58.     ) s, (SELECT DISTINCT roomid, userid, roomend AS e      
  59.     FROM u_room_log a      
  60.     WHERE NOT EXISTS (SELECT *      
  61.         FROM u_room_log b      
  62.         WHERE a.roomid = b.roomid      
  63.             AND a.userid = b.userid      
  64.             AND a.roomend >= b.roomstart      
  65.             AND a.roomend < b.roomend)      
  66.     ) e      
  67. WHERE s.roomid = e.roomid      
  68.     AND s.userid = e.userid     
  69.     
  70. ) t1 ,    
  71. nums     
  72. where  nums.id<=datediff(e,s)+1    
  73. ;    
  74.   
  75.     open cur_test;      
  76.     repeat      
  77.         fetch cur_test into v_roomid, v_start,v_end;      
  78.         if done !=1 then    
  79.            insert ignore into tmp_time_point(roomid,timepoint) values(v_roomid,v_start);  
  80.            insert ignore into tmp_time_point(roomid,timepoint) values(v_roomid,v_end);  
  81.         end if;    
  82.     until done end repeat;      
  83.     close cur_test;   
  84.       
  85.     
  86.   
  87.    
  88. select roomid,date(s) dt,round(second,s,e))/60) ts,max(c) c from (       
  89.     select roomid,s,e ,distinct userid) c  from (  
  90.         select distinct v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e  
  91.         from (  
  92.             select distinct roomid,as DATETIME) starttime,as DATETIME) endtime from (  
  93.                 select   
  94.                 if(@roomid=roomid,@d,'')  as starttime,@d:=timepoint,@roomid:=roomid,p.roomid,p.timepoint endtime  
  95.                 from tmp_time_point p,(select @d:='',@roomid:=-1) vars  
  96.                 order by roomid,timepoint  
  97.             ) v4 where starttime!='' and date(starttime)=date(endtime)  
  98.         ) v5 inner join t1 v6 on(v5.starttime between v6.s and v6.e and v5.endtime between v6.s and v6.e and v5.roomid=v6.roomid)  
  99.     ) v6 group by roomid,s,e having distinct userid)>1     
  100. ) v7 group by roomid,date(s);  
  101.   
  102.   
  103. END  

都內(nèi)聚到一個(gè)過程之后,不需要?jiǎng)?chuàng)建額外的普通表,直接在過程中創(chuàng)建臨時(shí)表.實(shí)現(xiàn)高內(nèi)聚,低耦合.

call p 
過程返回的結(jié)果即為最終結(jié)果.

三次測(cè)試耗時(shí)均低于 10.3秒
向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手