周五晚上10点了.
这一周连续
优化Sess
ion合并和拆分问题.每天都比前一天提升性能一倍以上.
终于在今天,用独创的小花狸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
和之前的算法比较,结果一致。基本上都在1.6秒左右.
0