VV音乐朋友圈的功能
1.找自己唱的歌,找自己好友唱的歌.
2.找自己分享的歌,找自己好友分享的歌.
然后根据时间排序,返回30个记录.
space_av 用户唱歌记录表
space_share 用户分享歌曲表.其中ObjectID和space_av的avid是关联关系.
原SQL.
用户好友比较少的时候,运行正常.但是用户好友很多的情况,比如超过1000个好友.SQL运行速度下降.
生产系统发现,一个用户好友达到了1200个左右,这个SQL运行时间超过5秒,这是不能接受的。
改写如下:
这个SQL在好友非常多的情况,执行时间在0.23s左右,基本符合优化的预期.
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/