• 博客访问: 5737283
  • 博文数量: 605
  • 用 户 组: 普通用户
  • 注册时间: 2013-10-19 21:50
个人简介

高山仰止http://my.csdn.net/wzy0623王工的博客 参加炼数成金培训输入 Dataguru培训优惠码 DR50,报名立减50%固定学费。 http://edu.dataguru.cn/

文章分类

全部博文(605)

文章存档

2018年(10)

2017年(67)

2016年(104)

2015年(144)

2014年(220)

2013年(60)

分类: MySQL

2018-01-17 18:17:40

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

前文中的算法想了一天半,终于在昨天晚上得出了正确的结果.
在我的环境中,耗时90s ,还有进一步优化的空间.

首选是生成 t1 和 t2的方式.
之前使用create table 方式 导致类型不对,
因为是临时作用的表,所以可以预先创建表结构
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`)
)  ;

前文中的第一步可以封装为一个过程
  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 day),s) s,    
  11. if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e    
  12. from (    
  13. SELECT DISTINCT s.roomid, s.userid, s.s, (      
  14.         SELECT MIN(e)      
  15.         FROM (SELECT DISTINCT roomid, userid, roomend AS e      
  16.             FROM u_room_log a      
  17.             WHERE NOT EXISTS (SELECT *      
  18.                 FROM u_room_log b      
  19.                 WHERE a.roomid = b.roomid      
  20.                     AND a.userid = b.userid      
  21.                     AND a.roomend >= b.roomstart      
  22.                     AND a.roomend < b.roomend)      
  23.             ) s2      
  24.         WHERE s2.e > s.s      
  25.             AND s.roomid = s2.roomid      
  26.             AND s.userid = s2.userid      
  27.         ) AS e      
  28. FROM (SELECT DISTINCT roomid, userid, roomstart AS s      
  29.     FROM u_room_log a      
  30.     WHERE NOT EXISTS (SELECT *      
  31.         FROM u_room_log b      
  32.         WHERE a.roomid = b.roomid      
  33.             AND a.userid = b.userid      
  34.             AND a.roomstart > b.roomstart      
  35.             AND a.roomstart <= b.roomend)      
  36.     ) s, (SELECT DISTINCT roomid, userid, roomend AS e      
  37.     FROM u_room_log a      
  38.     WHERE NOT EXISTS (SELECT *      
  39.         FROM u_room_log b      
  40.         WHERE a.roomid = b.roomid      
  41.             AND a.userid = b.userid      
  42.             AND a.roomend >= b.roomstart      
  43.             AND a.roomend < b.roomend)      
  44.     ) e      
  45. WHERE s.roomid = e.roomid      
  46.     AND s.userid = e.userid     
  47.     
  48. ) t1 ,    
  49. nums     
  50. where  nums.id<=datediff(e,s)+1    
  51.     
  52. ;    
  53.   
  54. END  

函数修改如下
  1. DELIMITER $$  
  2.   
  3. CREATE DEFINER=`root`@`localhost` FUNCTION `f`(pTime timestampRETURNS 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,CAST(starttime as DATETIME) starttime,CAST(endtime as DATETIME) endtime from (  
  10.          select @d as starttime,@d:=d,v3.roomid,v3.d endtime from (  
  11.                     select distinct roomid,   
  12.                     case      
  13.                     when nums.id=1 then v1s     
  14.                     when nums.id=2 then v1e     
  15.                     when nums.id=3 then v2s     
  16.                     when nums.id=4 then v2e     
  17.                     end d   from (  
  18.                         select   v1.roomid, v1.s v1s,v1.e v1e,v2.s v2s,v2.e v2e  
  19.                         from t1 v1  
  20.                         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)   
  21.                         where v2.roomid in(select distinct roomid from t1 where date(s)=pTime)  
  22.                         and v2.s>=pTime and v2.s<(pTime+interval '1' dayand (v2.roomid,v2.userid,v2.s,v2.e)!= (v1.roomid,v1.userid,v1.s,v1.e)   
  23.                     ) a,nums where nums.id<=4  
  24.                     order by roomid,d  
  25.         ) v3,(select @d:='') vars  
  26.     ) v4 where starttime!=''    
  27. ) 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)  
  28. ;  
  29.   
  30. select row_count() into pResult;  
  31. RETURN   pResult;  
  32. END  

原来是针对每天每个房间处理,经过优化对某天的所有房间进行处理,批量的形式更快

另外在中间过程增加了类型转换,可以更好的利用索引
 select roomid,CAST(starttime as DATETIME) starttime,CAST(endtime as DATETIME) endtime 


另外第7行 原来没有 distinct 可能导致bug
select distinct v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e 

调用时执行:
truncate table t1;
truncate table t2;
call p;
select f(s) from (
select distinct date(s) s from t1
) t

两步的执行时间:


今天优化了一天,从90s优化到25s以内,已经达到了预期。
我觉得在单线程环境,基本上已经达到最优.
如果还想优化到极致,第二步的函数执行,可以通过JAVA程序多线程一起跑,只要服务器CPU核数多,优化效果应该还是很明显的。

阅读(2484) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册