一次UnionAll的合并优化

最新推荐文章于 2020-11-12 09:13:59 发布

coolmsn8786

最新推荐文章于 2020-11-12 09:13:59 发布

阅读量133

点赞数

文章标签： php

VV音乐朋友圈的功能

1.找自己唱的歌,找自己好友唱的歌.
2.找自己分享的歌,找自己好友分享的歌.

然后根据时间排序,返回30个记录.

space_av 用户唱歌记录表
space_share 用户分享歌曲表.其中ObjectID和space_av的avid是关联关系.

原SQL.
用户好友比较少的时候,运行正常.但是用户好友很多的情况,比如超过1000个好友.SQL运行速度下降.
生产系统发现,一个用户好友达到了1200个左右,这个SQL运行时间超过5秒,这是不能接受的。

SELECT a.* FROM (
(
SELECT 1 dynamicType,c.userID zpUserID,a.AVID,-1 ShareID,a.UserID,-1 ObjectID,'' Content,-1 TYPE,'' zpName,
a.Name,'' userName,a.frontCoverUrl,c.photo1 userPhoto,a.Tag,-1 zpPlayTimes,a.Description,-1 zpShareTimes,
a.FileType,-1 zpCommentTimes,a.Performer,-1 avStatus,a.Writer,c.nickName,a.Composer,'' photo1,a.Singer,
a.commentTimes,a.Editor,a.MIX,a.UploadDomain,a.FileDomain,a.FileURL,a.FileSize,a.FromIP,a.Status,a.SyncTelecom,
a.SyncUnicom,a.Lyric,a.SideshowBob,a.CreateTime,(a.PlayTimes + a.addPlayTimes) PlayTimes,a.PraiseTimes,
a.KSCSongID,b.kscLink,b.AccompanyLink,b.vocalID,b.SingerName,a.shareTimes,
c.authType, c.authState, c.authInfo, '' userAuthType, '' userAuthState, '' userAuthInfo,
a.exFileType,a.zpSource,a.flowerAmount, a.semiAVID, a.chorusNum,
a.semiUserID,f.Nickname semiNickName,f.photo1 semiUserPhoto,
f.authType semiAuthType, f.authState semiAuthState, f.authInfo semiAuthInfo,
IF(ISNULL(g.userid), 0, 1) isPraised
FROM space_av a
INNER JOIN (
select distinct ta.avid from space_av ta INNER JOIN (
SELECT ToUserID userid FROM space_friend WHERE UserID = 7007007
AND STATUS != 0
UNION ALL select 7007007
)tb on (ta.userid = tb.userid)
where ta.Status not in (1,4)
and ta.zpSource = 0
ORDER BY ta.CreateTime DESC
LIMIT 30
)t1 ON(a.avid = t1.avid)
LEFT JOIN songod.sod_song_ksc b ON(a.kscSongID = b.SongID)
LEFT JOIN space_user c ON(a.UserID = c.UserID)
LEFT JOIN space_user f ON(a.semiUserID = f.UserID)
LEFT JOIN (SELECT userid, toUserID, objectID from space_praise_record where `Status` != 0 and type = 0 and userid =
7007007
) g ON (a.UserID = g.toUserID and g.objectID = a.avid)
)
UNION
(
SELECT 2 dynamicType,c.userID zpUserID,-1 AVID,a.ShareID,a.UserID,a.ObjectID,a.Content,a.TYPE,b.name zpName,
'' NAME,c.Nickname userName,'' frontCoverUrl,c.photo1 userPhoto,'' Tag,(b.PlayTimes+b.addPlayTimes) zpPlayTimes,
b.Description,b.ShareTimes zpShareTimes,b.FileType,b.CommentTimes zpCommentTimes,'' Performer,b.Status avStatus,
'' Writer,d.nickname,''Composer,d.photo1,'' Singer,a.commentTimes,'' Editor,'' MIX,'' UploadDomain,'' FileDomain,
'' FileURL,'' FileSize,a.FromIP,a.Status,'' SyncTelecom,'' SyncUnicom,'' Lyric,'' SideshowBob,a.CreateTime,'' PlayTimes,
a.PraiseTimes,'' KSCSongID,'' kscLink,'' AccompanyLink,-1 vocalID,'' SingerName,'' shareTimes,
d.authType, d.authState, d.authInfo, c.authType userAuthType, c.authState userAuthState,
c.authInfo userAuthInfo, b.exFileType, b.zpSource, b.flowerAmount, b.semiAVID, b.chorusNum,
b.semiUserID,f.Nickname semiNickName,f.photo1 semiUserPhoto,
f.authType semiAuthType, f.authState semiAuthState, f.authInfo semiAuthInfo,
IF(ISNULL(g.userid), 0, 1) isPraised
FROM space_share a
INNER JOIN (select distinct ta.shareID from space_share ta inner join (
SELECT ToUserID userid FROM space_friend WHERE UserID = 7007007
AND STATUS != 0
UNION ALL select 7007007
)tc on(ta.userid = tc.userid)
LEFT JOIN space_av tb ON(ta.objectID = tb.avid)
and tb.zpSource = 0
ORDER BY ta.CreateTime DESC
LIMIT 30
)t1 ON a.shareID = t1.shareID
LEFT JOIN space_av b ON(a.ObjectID = b.AVID)
LEFT JOIN space_user c ON(c.UserID = b.UserID)
LEFT JOIN space_user d ON(a.UserID = d.UserID)
LEFT JOIN space_user f ON(b.semiUserID = f.UserID)
LEFT JOIN (SELECT userid, toUserID, objectID from space_praise_record where `Status` != 0 and type = 1 and userid =
7007007
) g ON (a.UserID = g.toUserID and g.objectid = a.shareid)
)
) a
ORDER BY CreateTime DESC
LIMIT 0,30;

改写如下:

select
init.dynamicType,cav.userID zpUserID,
if(init.dynamicType=1,b.avid,-1) AVID,
if(init.dynamicType=1,-1,a.ShareID) ShareID,
if(init.dynamicType=1,b.userid,a.userid) UserID,
if(init.dynamicType=1,-1,a.objectID) ObjectID,
if(init.dynamicType=1,'',a.Content) Content,
if(init.dynamicType=1,-1,a.TYPE) TYPE,
if(init.dynamicType=1,'',bshared.name) zpName,
if(init.dynamicType=1,b.Name,'') Name,
if(init.dynamicType=1,'',cshared.Nickname) userName,
if(init.dynamicType=1,b.frontCoverUrl,'') frontCoverUrl,
if(init.dynamicType=1,cav.photo1,cshared.photo1) userPhoto,
if(init.dynamicType=1,b.Tag,'') Tag,
if(init.dynamicType=1,-1,(bshared.PlayTimes+bshared.addPlayTimes)) zpPlayTimes,
if(init.dynamicType=1,b.Description,bshared.Description) Description,
if(init.dynamicType=1,-1,bshared.ShareTimes) zpShareTimes,
if(init.dynamicType=1,b.FileType,bshared.FileType) FileType,
if(init.dynamicType=1,-1,bshared.CommentTimes) zpCommentTimes,
if(init.dynamicType=1,b.Performer,'') Performer,
if(init.dynamicType=1,-1,bshared.Status) avStatus,
if(init.dynamicType=1,b.Writer,'') Writer,
if(init.dynamicType=1,cav.nickName,dshared.nickName) nickName,
if(init.dynamicType=1,b.Composer,'') Composer,
if(init.dynamicType=1,'',dshared.photo1) photo1,
if(init.dynamicType=1,b.Singer,'') Singer,
if(init.dynamicType=1,b.commentTimes,bshared.commentTimes) commentTimes,
if(init.dynamicType=1,b.Editor,'') Editor,
if(init.dynamicType=1,b.MIX,'') MIX,
if(init.dynamicType=1,b.UploadDomain,'') UploadDomain,
if(init.dynamicType=1,b.FileDomain,'') FileDomain,
if(init.dynamicType=1,b.FileURL,'') FileURL,
if(init.dynamicType=1,b.FileSize,'') FileSize,
if(init.dynamicType=1,b.FromIP,a.FromIP) FromIP,
if(init.dynamicType=1,b.Status,a.Status) Status,
if(init.dynamicType=1,b.SyncTelecom,'') SyncTelecom,
if(init.dynamicType=1,b.KSCSongID,'') KSCSongID,
if(init.dynamicType=1,ksc.kscLink,'') kscLink,
if(init.dynamicType=1,ksc.AccompanyLink,'') AccompanyLink,
if(init.dynamicType=1,ksc.vocalID,-1) vocalID,
if(init.dynamicType=1,ksc.SingerName,'') SingerName,
if(init.dynamicType=1,b.shareTimes,'') shareTimes,
if(init.dynamicType=1,cav.authType,dshared.authType) authType,
if(init.dynamicType=1,cav.authState,dshared.authState) authState,
if(init.dynamicType=1,cav.authInfo,dshared.authInfo) authInfo,
if(init.dynamicType=1,'',cshared.authType) userAuthType,
if(init.dynamicType=1,'',cshared.authState) userAuthState,
if(init.dynamicType=1,'',cshared.authInfo) userAuthInfo,
if(init.dynamicType=1,b.exFileType,bshared.exFileType) exFileType,
if(init.dynamicType=1,b.zpSource,bshared.zpSource) zpSource,
if(init.dynamicType=1,b.flowerAmount,bshared.flowerAmount) flowerAmount,
if(init.dynamicType=1,b.semiAVID,bshared.semiAVID) semiAVID,
if(init.dynamicType=1,b.chorusNum,bshared.chorusNum) chorusNum,
if(init.dynamicType=1,b.semiUserID,bshared.semiUserID) semiUserID,
if(init.dynamicType=1,fav.Nickname,fshared.Nickname) semiNickName,
if(init.dynamicType=1,fav.photo1,fshared.photo1) semiUserPhoto,
if(init.dynamicType=1,fav.authType,fshared.authType) semiAuthType,
if(init.dynamicType=1,fav.authState,fshared.authState) semiAuthState,
if(init.dynamicType=1,fav.authInfo,fshared.authInfo) semiAuthInfo,
IF(ISNULL(if(init.dynamicType=1,g.userid,gg.userid)), 0, 1) isPraised
from (
select
*
from
(
select
*
from
(
select
distinct ta.avid objectID, ta.createtime, 1 dynamicType
from
space_av ta
inner join
(
SELECT
ToUserID userid
FROM
space_friend
WHERE
UserID = 7007007 AND STATUS != 0 UNION ALL select 7007007
)v1 on (v1.userid=ta.userid)
where
ta.Status not in (1 , 4)
and ta.zpSource = 0
ORDER BY ta.CreateTime DESC
LIMIT 30
) a
union all
select
*
from
(
select
distinct ta.shareID, ta.createtime, 2
from
space_share ta
inner join
(
SELECT
ToUserID userid
FROM
space_friend
WHERE
UserID = 7007007 AND STATUS != 0 UNION ALL select 7007007
) v2 on (v2.userid=ta.userid)
ORDER BY ta.CreateTime DESC
LIMIT 30
) b
) c
order by createtime desc
limit 30
) init
LEFT JOIN space_share a on (init.ObjectID=a.shareID and init.dynamicType=2)
LEFT JOIN space_av b on(init.dynamicType=1 and b.avid=init.objectID)
LEFT JOIN space_av bshared on(init.dynamicType=2 and bshared.avid=a.ObjectID)
LEFT JOIN space_user cav ON(cav.UserID = b.UserID)
LEFT JOIN space_user fav ON(b.semiUserID = fav.UserID)
LEFT JOIN space_user cshared ON(cshared.UserID = bshared.UserID)
LEFT JOIN space_user dshared ON(a.UserID = dshared.UserID)
LEFT JOIN space_user fshared ON(bshared.semiUserID = fshared.UserID)
LEFT JOIN space_praise_record g ON
(g.toUserID=b.userid and g.objectid = init.ObjectID and g.Status != 0 and g.type = 1 and g.userid =7007007)
LEFT JOIN space_praise_record gg ON
(gg.toUserID=a.userid and gg.objectid = init.ObjectID and gg.Status != 0 and gg.type = 1 and gg.userid =7007007)
LEFT JOIN songod.sod_song_ksc ksc on(ksc.songid=b.KSCSongID and init.dynamicType=1)

这个SQL在好友非常多的情况,执行时间在0.23s左右,基本符合优化的预期.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1878089/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1878089/

coolmsn8786

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一次UnionAll的合并优化

VV音乐朋友圈的功能1.找自己唱的歌,找自己好友唱的歌.2.找自己分享的歌,找自己好友分享的歌.然后根据时间排序,返回30个记录.space_av 用户唱歌记录表space_share 用户分享歌...
复制链接

扫一扫