接前文
http://blog.itpub.net/29254281/viewspace-2150229/

前文中的算法想了一天半,終于在昨天晚上得出了正確的結(jié)果.
在我的環(huán)境中，耗時(shí)90s ，還有進(jìn)一步優(yōu)化的空間.

首選是生成 t1 和 t2的方式.
之前使用create table 方式 導(dǎo)致類型不對(duì),
因?yàn)槭桥R時(shí)作用的表,所以可以預(yù)先創(chuàng)建表結(jié)構(gòu)
CREATE TABLE `t1` (
  `roomid` int(11) NOT NULL DEFAULT '0',
  `userid` bigint(20) NOT NULL DEFAULT '0',
  `s` timestamp ,
  `e` timestamp,
  primary  KEY (`roomid`,`userid`,`s`,`e`),
  KEY (`roomid`,`s`,`e`)
)  ;

CREATE TABLE `t2` (
  `roomid` int(11) NOT NULL DEFAULT '0',
  `userid` bigint(20) NOT NULL DEFAULT '0',
  `s` timestamp ,
  `e` timestamp,
  primary  KEY (`roomid`,`userid`,`s`,`e`),
  KEY (`roomid`,`s`,`e`)
)  ;

前文中的第一步可以封裝為一個(gè)過程

1. DELIMITER $$  
2.   
3. CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()  
4. BEGIN  
5.   
6. insert into t1  
7. select distinct    
8. roomid,    
9. userid,    
10. 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    
11. from (    
12. SELECT DISTINCT s.roomid, s.userid, s.s, (      
13.         SELECT MIN(e)      
14.         FROM (SELECT DISTINCT roomid, userid, roomend AS e      
15.             FROM u_room_log a      
16.             WHERE NOT EXISTS (SELECT *      
17.                 FROM u_room_log b      
18.                 WHERE a.roomid = b.roomid      
19.                     AND a.userid = b.userid      
20.                     AND a.roomend >= b.roomstart      
21.                     AND a.roomend < b.roomend)      
22.             ) s2      
23.         WHERE s2.e > s.s      
24.             AND s.roomid = s2.roomid      
25.             AND s.userid = s2.userid      
26.         ) AS e      
27. FROM (SELECT DISTINCT roomid, userid, roomstart AS s      
28.     FROM u_room_log a      
29.     WHERE NOT EXISTS (SELECT *      
30.         FROM u_room_log b      
31.         WHERE a.roomid = b.roomid      
32.             AND a.userid = b.userid      
33.             AND a.roomstart > b.roomstart      
34.             AND a.roomstart <= b.roomend)      
35.     ) s, (SELECT DISTINCT roomid, userid, roomend AS e      
36.     FROM u_room_log a      
37.     WHERE NOT EXISTS (SELECT *      
38.         FROM u_room_log b      
39.         WHERE a.roomid = b.roomid      
40.             AND a.userid = b.userid      
41.             AND a.roomend >= b.roomstart      
42.             AND a.roomend < b.roomend)      
43.     ) e      
44. WHERE s.roomid = e.roomid      
45.     AND s.userid = e.userid     
46.     
47. ) t1 ,    
48. nums     
49. where  nums.id<=datediff(e,s)+1    
50.     
51. ;    
52.   
53. END  

函數(shù)修改如下

1. DELIMITER $$  
2.   
3. CREATE DEFINER=`root`@`localhost` FUNCTION `f`(pTime timestamp) RETURNS int(11)  
4. BEGIN  
5. declare pResult bigint;  
6. insert into t2  
7. select distinct v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e  
8. from (  
9.     select roomid,as DATETIME) starttime,as DATETIME) endtime from (  
10.          select @d as starttime,@d:=d,v3.roomid,v3.d endtime from (  
11.                     select distinct roomid,   
12.                     when nums.id=1 then v1s     
13.                     when nums.id=2 then v1e     
14.                     when nums.id=3 then v2s     
15.                     when nums.id=4 then v2e     
16.                     end d   from (  
17.                         select   v1.roomid, v1.s v1s,v1.e v1e,v2.s v2s,v2.e v2e  
18.                         from t1 v1  
19.                         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)   
20.                         where v2.roomid in(select distinct roomid from t1 where date(s)=pTime)  
21.                         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)   
22.                     ) a,nums where nums.id<=4  
23.                     order by roomid,d  
24.         ) v3,(select @d:='') vars  
25.     ) v4 where starttime!=''    
26. ) 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)  
27. ;  
28.   
29. select row_count() into pResult;  
30. RETURN   pResult;  
31. END  

原來(lái)是針對(duì)每天每個(gè)房間處理,經(jīng)過優(yōu)化對(duì)某天的所有房間進(jìn)行處理,批量的形式更快

另外在中間過程增加了類型轉(zhuǎn)換,可以更好的利用索引
 select roomid,CAST(starttime as DATETIME) starttime,CAST(endtime as DATETIME) endtime 


另外第7行 原來(lái)沒有 distinct 可能導(dǎo)致bug
select distinct v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e 

調(diào)用時(shí)執(zhí)行:
truncate table t1;
truncate table t2;
call p;
select f(s) from (
select distinct date(s) s from t1
) t

兩步的執(zhí)行時(shí)間：


今天優(yōu)化了一天，從90s優(yōu)化到25s以內(nèi),已經(jīng)達(dá)到了預(yù)期。
我覺得在單線程環(huán)境,基本上已經(jīng)達(dá)到最優(yōu).
如果還想優(yōu)化到極致,第二步的函數(shù)執(zhí)行,可以通過JAVA程序多線程一起跑,只要服務(wù)器CPU核數(shù)多,優(yōu)化效果應(yīng)該還是很明顯的。

文章題目：Session重疊問題學(xué)習(xí)(三)--優(yōu)化
路徑分享：http://m.2m8n56k.cn/article36/ijjcsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號(hào)、微信小程序、用戶體驗(yàn)、面包屑導(dǎo)航、定制開發(fā)、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：[email protected]。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)