Session重疊問題學習(三)--優(yōu)化

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

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

首選是生成 t1 和 t2的方式.
之前使用create table 方式導致類型不對,
因為是臨時作用的表,所以可以預先創(chuàng)建表結構
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`)
) ;

前文中的第一步可以封裝為一個過程

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
BEGIN
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
;
END

函數(shù)修改如下

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `f`(pTime timestamp) RETURNS int(11)
BEGIN
declare pResult bigint;
insert into t2
select distinct v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e
from (
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!=''
) 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)
;
select row_count() into pResult;
RETURN pResult;
END

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

另外在中間過程增加了類型轉(zhuǎn)換,可以更好的利用索引
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

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

兩步的執(zhí)行時間：
Session重疊問題學習(三)--優(yōu)化

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

文章題目：Session重疊問題學習(三)--優(yōu)化
路徑分享：http://jinyejixie.com/article36/ijjcsg.html

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

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

猜你還喜歡下面的內(nèi)容

成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

Session重疊問題學習(三)--優(yōu)化