接前文:
需求描述和第一版解决方案(执行时间90秒)
http://blog.itpub.net/29254281/viewspace-2150229/

优化和修改bug的版本(执行时间25秒)
http://blog.itpub.net/29254281/viewspace-2150259/


我觉得在集合思维处理方式中,前文已经达到最优了.

如果放弃完全的集合处理思维,实际上还可以更加的优化.

前文的几个问题.
1.引入了过多的表结构.
2.写表本身也花费了时间.
3.前文按天批处理,粒度还是细了.应该一把批量全出最快.
4.前文计算最小间隔范围的部分,因为应用集合化思维,不好理解性能还差.

前文计算最小间隔范围的部分如下

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!='' 

该部分使用集合处理方式,不好理解性能还差.

这块可以通过游标写临时表轻易解决。
本质上最小范围就是
每天每个房间每个记录的开始时间和结束时间都扣出来作为一行 排序。

然后找到每个时间最近的下一个时间,作为最小时间范围.

如果使用游标,遍历一遍即可.

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  

都内聚到一个过程之后,不需要创建额外的普通表,直接在过程中创建临时表.实现高内聚,低耦合.

call p 
过程返回的结果即为最终结果.

三次测试耗时均低于 10.3秒




分享文章：Session重叠问题学习(四)--再优化
转载注明：http://azwzsj.com/article/iegeps.html