Session重疊問題學習(四)--再優化

發布時間：2020-08-11 10:31:08 來源：ITPUB博客閱讀：134 作者：壹頁書欄目：MySQL數據庫

接前文:
需求描述和第一版解決方案(執行時間90秒)
http://blog.itpub.net/29254281/viewspace-2150229/

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

我覺得在集合思維處理方式中,前文已經達到最優了.

如果放棄完全的集合處理思維,實際上還可以更加的優化.

前文的幾個問題.
1.引入了過多的表結構.
2.寫表本身也花費了時間.
3.前文按天批處理,粒度還是細了.應該一把批量全出最快.
4.前文計算最小間隔范圍的部分,因為應用集合化思維,不好理解性能還差.

前文計算最小間隔范圍的部分如下

select roomid,as DATETIME) starttime,as DATETIME) endtime from (
select @d as starttime,@d:=d,v3.roomid,v3.d endtime from (
select distinct roomid,
when nums.id=1 then v1s
when nums.id=2 then v1e
when nums.id=3 then v2s
when nums.id=4 then v2e
end d from (
select v1.roomid, v1.s v1s,v1.e v1e,v2.s v2s,v2.e v2e
from t1 v1
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)
where v2.roomid in(select distinct roomid from t1 where date(s)=pTime)
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)
) a,nums where nums.id<=4
order by roomid,d
) v3,(select @d:='') vars
) v4 where starttime!=''

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

這塊可以通過游標寫臨時表輕易解決。
本質上最小范圍就是
每天每個房間每個記錄的開始時間和結束時間都扣出來作為一行排序。

然后找到每個時間最近的下一個時間,作為最小時間范圍.

如果使用游標,遍歷一遍即可.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
BEGIN
declare done int default 0;
declare v_roomid bigint;
declare v_start timestamp;
declare v_end timestamp;
declare cur_test CURSOR for select roomid,s,e from t1 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
drop table if exists t1;
drop table if exists tmp_time_point;
CREATE temporary TABLE `t1` (
`roomid` int(11) NOT NULL DEFAULT '0',
`userid` bigint(20) NOT NULL DEFAULT '0',
`s` timestamp NOT NULL DEFAULT ON UPDATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
primary KEY `roomid` (`roomid`,`s`,`e`,`userid`)
) ENGINE=InnoDB;
create temporary table tmp_time_point(
roomid bigint,
timepoint timestamp,
primary key(roomid,timepoint)
) engine=memory;
insert into t1
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 DISTINCT s.roomid, s.userid, s.s, (
SELECT MIN(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)
) s2
WHERE s2.e > s.s
AND s.roomid = s2.roomid
AND s.userid = s2.userid
) AS e
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)
) s, (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)
) e
WHERE s.roomid = e.roomid
AND s.userid = e.userid
) t1 ,
nums
where nums.id<=datediff(e,s)+1
;
open cur_test;
repeat
fetch cur_test into v_roomid, v_start,v_end;
if done !=1 then
insert ignore into tmp_time_point(roomid,timepoint) values(v_roomid,v_start);
insert ignore into tmp_time_point(roomid,timepoint) values(v_roomid,v_end);
end if;
until done end repeat;
close cur_test;
select roomid,date(s) dt,round(second,s,e))/60) ts,max(c) c from (
select roomid,s,e ,distinct userid) c from (
select distinct v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e
from (
select distinct roomid,as DATETIME) starttime,as DATETIME) endtime from (
select
if(@roomid=roomid,@d,'') as starttime,@d:=timepoint,@roomid:=roomid,p.roomid,p.timepoint endtime
from tmp_time_point p,(select @d:='',@roomid:=-1) vars
order by roomid,timepoint
) v4 where starttime!='' and date(starttime)=date(endtime)
) 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)
) v6 group by roomid,s,e having distinct userid)>1
) v7 group by roomid,date(s);
END

都內聚到一個過程之后,不需要創建額外的普通表,直接在過程中創建臨時表.實現高內聚,低耦合.

call p
過程返回的結果即為最終結果.

三次測試耗時均低于 10.3秒

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

Session重疊問題學習(四)--再優化

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

Session重疊問題學習(四)--再優化

猜你喜歡

最新資訊

相關推薦

相關標簽