ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 一次UnionAll的合并优化

一次UnionAll的合并优化

原创 MySQL 作者：壹頁書 时间：2015-12-23 11:46:05 0 删除 编辑
VV音乐朋友圈的功能

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

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

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

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

  1. SELECT a.* FROM (  
  2.                 (  
  3.                 SELECT 1 dynamicType,c.userID zpUserID,a.AVID,-1 ShareID,a.UserID,-1 ObjectID,'' Content,-1 TYPE,'' zpName,  
  4.                 a.Name,'' userName,a.frontCoverUrl,c.photo1 userPhoto,a.Tag,-1 zpPlayTimes,a.Description,-1 zpShareTimes,  
  5.                 a.FileType,-1 zpCommentTimes,a.Performer,-1 avStatus,a.Writer,c.nickName,a.Composer,'' photo1,a.Singer,  
  6.                 a.commentTimes,a.Editor,a.MIX,a.UploadDomain,a.FileDomain,a.FileURL,a.FileSize,a.FromIP,a.Status,a.SyncTelecom,  
  7.                 a.SyncUnicom,a.Lyric,a.SideshowBob,a.CreateTime,(a.PlayTimes + a.addPlayTimes) PlayTimes,a.PraiseTimes,  
  8.                 a.KSCSongID,b.kscLink,b.AccompanyLink,b.vocalID,b.SingerName,a.shareTimes,  
  9.                 c.authType, c.authState, c.authInfo, '' userAuthType, '' userAuthState, '' userAuthInfo,  
  10.                 a.exFileType,a.zpSource,a.flowerAmount, a.semiAVID, a.chorusNum,  
  11.                 a.semiUserID,f.Nickname semiNickName,f.photo1 semiUserPhoto,  
  12.                 f.authType semiAuthType, f.authState semiAuthState, f.authInfo semiAuthInfo,  
  13.                 IF(ISNULL(g.userid), 0, 1) isPraised  
  14.                 FROM space_av a  
  15.                 INNER JOIN (  
  16.                         select distinct ta.avid from space_av ta INNER JOIN (  
  17.                                 SELECT ToUserID userid FROM space_friend WHERE UserID = 7007007  
  18.                                  AND STATUS != 0  
  19.   
  20.                                 UNION ALL select 7007007  
  21.                         )tb on (ta.userid = tb.userid)  
  22.                         where ta.Status not in (1,4)  
  23.   
  24.                           and ta.zpSource = 0  
  25.                         ORDER BY ta.CreateTime DESC  
  26.                         LIMIT  30  
  27.                 )t1 ON(a.avid = t1.avid)  
  28.                 LEFT JOIN songod.sod_song_ksc b ON(a.kscSongID = b.SongID)  
  29.                 LEFT JOIN space_user c ON(a.UserID = c.UserID)  
  30.                 LEFT JOIN space_user f ON(a.semiUserID = f.UserID)  
  31.                 LEFT JOIN (SELECT userid, toUserID, objectID from space_praise_record where `Status` != 0 and type = 0 and userid =  
  32.                          7007007  
  33.                          ) g ON (a.UserID = g.toUserID and g.objectID = a.avid)  
  34.                 )  
  35.                 UNION  
  36.                 (  
  37.                 SELECT 2 dynamicType,c.userID zpUserID,-1 AVID,a.ShareID,a.UserID,a.ObjectID,a.Content,a.TYPE,b.name zpName,  
  38.                 '' NAME,c.Nickname userName,'' frontCoverUrl,c.photo1 userPhoto,'' Tag,(b.PlayTimes+b.addPlayTimes) zpPlayTimes,  
  39.                 b.Description,b.ShareTimes zpShareTimes,b.FileType,b.CommentTimes zpCommentTimes,'' Performer,b.Status avStatus,  
  40.                 '' Writer,d.nickname,''Composer,d.photo1,'' Singer,a.commentTimes,'' Editor,'' MIX,'' UploadDomain,'' FileDomain,  
  41.                 '' FileURL,'' FileSize,a.FromIP,a.Status,'' SyncTelecom,'' SyncUnicom,'' Lyric,'' SideshowBob,a.CreateTime,'' PlayTimes,  
  42.                 a.PraiseTimes,'' KSCSongID,'' kscLink,'' AccompanyLink,-1 vocalID,'' SingerName,'' shareTimes,  
  43.                 d.authType, d.authState, d.authInfo, c.authType userAuthType, c.authState userAuthState,  
  44.                 c.authInfo userAuthInfo, b.exFileType, b.zpSource, b.flowerAmount, b.semiAVID, b.chorusNum,  
  45.                 b.semiUserID,f.Nickname semiNickName,f.photo1 semiUserPhoto,  
  46.                 f.authType semiAuthType, f.authState semiAuthState, f.authInfo semiAuthInfo,  
  47.                 IF(ISNULL(g.userid), 0, 1) isPraised  
  48.                 FROM space_share a  
  49.                 INNER JOIN (select distinct ta.shareID from space_share ta inner join (  
  50.                                 SELECT ToUserID userid FROM space_friend WHERE UserID = 7007007  
  51.                                  AND STATUS != 0  
  52.   
  53.                                 UNION ALL select 7007007  
  54.                         )tc on(ta.userid = tc.userid)  
  55.                         LEFT JOIN space_av tb ON(ta.objectID = tb.avid)  
  56.            and tb.zpSource = 0  
  57.                         ORDER BY ta.CreateTime DESC  
  58.                         LIMIT 30  
  59.                 )t1 ON a.shareID = t1.shareID  
  60.                 LEFT JOIN space_av b ON(a.ObjectID = b.AVID)  
  61.                 LEFT JOIN space_user c ON(c.UserID = b.UserID)  
  62.                 LEFT JOIN space_user d ON(a.UserID = d.UserID)  
  63.                 LEFT JOIN space_user f ON(b.semiUserID = f.UserID)  
  64.                 LEFT JOIN (SELECT userid, toUserID, objectID from space_praise_record where `Status` != 0 and type = 1 and userid =  
  65.                          7007007  
  66.                          ) g ON (a.UserID = g.toUserID and g.objectid = a.shareid)  
  67.                 )  
  68.         ) a  
  69.         ORDER BY CreateTime DESC  
  70.    LIMIT 0,30; 


改写如下:
  1. select   
  2. init.dynamicType,cav.userID zpUserID,  
  3. if(init.dynamicType=1,b.avid,-1) AVID,  
  4. if(init.dynamicType=1,-1,a.ShareID) ShareID,  
  5. if(init.dynamicType=1,b.userid,a.userid) UserID,  
  6. if(init.dynamicType=1,-1,a.objectID) ObjectID,  
  7. if(init.dynamicType=1,'',a.Content) Content,  
  8. if(init.dynamicType=1,-1,a.TYPE) TYPE,  
  9. if(init.dynamicType=1,'',bshared.name) zpName,  
  10. if(init.dynamicType=1,b.Name,'') Name,  
  11. if(init.dynamicType=1,'',cshared.Nickname) userName,  
  12. if(init.dynamicType=1,b.frontCoverUrl,'') frontCoverUrl,  
  13. if(init.dynamicType=1,cav.photo1,cshared.photo1) userPhoto,  
  14. if(init.dynamicType=1,b.Tag,'') Tag,  
  15. if(init.dynamicType=1,-1,(bshared.PlayTimes+bshared.addPlayTimes)) zpPlayTimes,  
  16. if(init.dynamicType=1,b.Description,bshared.Description) Description,  
  17. if(init.dynamicType=1,-1,bshared.ShareTimes) zpShareTimes,  
  18. if(init.dynamicType=1,b.FileType,bshared.FileType) FileType,  
  19. if(init.dynamicType=1,-1,bshared.CommentTimes) zpCommentTimes,  
  20. if(init.dynamicType=1,b.Performer,'') Performer,  
  21. if(init.dynamicType=1,-1,bshared.Status) avStatus,  
  22. if(init.dynamicType=1,b.Writer,'') Writer,  
  23. if(init.dynamicType=1,cav.nickName,dshared.nickName) nickName,  
  24. if(init.dynamicType=1,b.Composer,'') Composer,  
  25. if(init.dynamicType=1,'',dshared.photo1) photo1,  
  26. if(init.dynamicType=1,b.Singer,'') Singer,  
  27. if(init.dynamicType=1,b.commentTimes,bshared.commentTimes) commentTimes,  
  28. if(init.dynamicType=1,b.Editor,'') Editor,  
  29. if(init.dynamicType=1,b.MIX,'') MIX,  
  30. if(init.dynamicType=1,b.UploadDomain,'') UploadDomain,  
  31. if(init.dynamicType=1,b.FileDomain,'') FileDomain,  
  32. if(init.dynamicType=1,b.FileURL,'') FileURL,  
  33. if(init.dynamicType=1,b.FileSize,'') FileSize,  
  34. if(init.dynamicType=1,b.FromIP,a.FromIP) FromIP,  
  35. if(init.dynamicType=1,b.Status,a.Status) Status,  
  36. if(init.dynamicType=1,b.SyncTelecom,'') SyncTelecom,  
  37. if(init.dynamicType=1,b.KSCSongID,'') KSCSongID,  
  38. if(init.dynamicType=1,ksc.kscLink,'') kscLink,  
  39. if(init.dynamicType=1,ksc.AccompanyLink,'') AccompanyLink,  
  40. if(init.dynamicType=1,ksc.vocalID,-1) vocalID,  
  41. if(init.dynamicType=1,ksc.SingerName,'') SingerName,  
  42. if(init.dynamicType=1,b.shareTimes,'') shareTimes,  
  43. if(init.dynamicType=1,cav.authType,dshared.authType) authType,  
  44. if(init.dynamicType=1,cav.authState,dshared.authState) authState,  
  45. if(init.dynamicType=1,cav.authInfo,dshared.authInfo) authInfo,  
  46. if(init.dynamicType=1,'',cshared.authType) userAuthType,  
  47. if(init.dynamicType=1,'',cshared.authState) userAuthState,  
  48. if(init.dynamicType=1,'',cshared.authInfo) userAuthInfo,  
  49. if(init.dynamicType=1,b.exFileType,bshared.exFileType) exFileType,  
  50. if(init.dynamicType=1,b.zpSource,bshared.zpSource) zpSource,  
  51. if(init.dynamicType=1,b.flowerAmount,bshared.flowerAmount) flowerAmount,  
  52. if(init.dynamicType=1,b.semiAVID,bshared.semiAVID) semiAVID,  
  53. if(init.dynamicType=1,b.chorusNum,bshared.chorusNum) chorusNum,  
  54. if(init.dynamicType=1,b.semiUserID,bshared.semiUserID) semiUserID,  
  55. if(init.dynamicType=1,fav.Nickname,fshared.Nickname) semiNickName,  
  56. if(init.dynamicType=1,fav.photo1,fshared.photo1) semiUserPhoto,  
  57. if(init.dynamicType=1,fav.authType,fshared.authType) semiAuthType,  
  58. if(init.dynamicType=1,fav.authState,fshared.authState) semiAuthState,  
  59. if(init.dynamicType=1,fav.authInfo,fshared.authInfo) semiAuthInfo,  
  60. IF(ISNULL(if(init.dynamicType=1,g.userid,gg.userid)), 0, 1) isPraised  
  61. from (  
  62.     select   
  63.         *  
  64.     from  
  65.     (  
  66.         select   
  67.             *  
  68.         from  
  69.         (  
  70.             select   
  71.                 distinct ta.avid objectID, ta.createtime, 1 dynamicType  
  72.             from  
  73.                 space_av ta  
  74.             inner join   
  75.             (  
  76.                     SELECT   
  77.                         ToUserID userid  
  78.                     FROM  
  79.                         space_friend  
  80.                     WHERE  
  81.                     UserID = 7007007 AND STATUS != 0 UNION ALL select 7007007  
  82.             )v1 on (v1.userid=ta.userid)    
  83.             where  
  84.                 ta.Status not in (1 , 4)  
  85.                 and ta.zpSource = 0  
  86.             ORDER BY ta.CreateTime DESC  
  87.             LIMIT 30  
  88.         ) a   
  89.         union all   
  90.         select   
  91.             *  
  92.         from  
  93.         (  
  94.             select   
  95.                 distinct ta.shareID, ta.createtime, 2  
  96.             from  
  97.                 space_share ta  
  98.             inner join   
  99.             (  
  100.                     SELECT   
  101.                         ToUserID userid  
  102.                     FROM  
  103.                         space_friend  
  104.                     WHERE  
  105.                     UserID = 7007007 AND STATUS != 0 UNION ALL select 7007007  
  106.             ) v2 on (v2.userid=ta.userid)  
  107.             ORDER BY ta.CreateTime DESC  
  108.             LIMIT 30  
  109.         ) b  
  110.     ) c  
  111.     order by createtime desc  
  112.     limit 30  
  113. ) init   
  114. LEFT JOIN space_share a on (init.ObjectID=a.shareID and init.dynamicType=2)  
  115. LEFT JOIN space_av b on(init.dynamicType=1 and b.avid=init.objectID)  
  116. LEFT JOIN space_av bshared on(init.dynamicType=2 and bshared.avid=a.ObjectID)  
  117. LEFT JOIN space_user cav ON(cav.UserID = b.UserID)  
  118. LEFT JOIN space_user fav ON(b.semiUserID = fav.UserID)  
  119. LEFT JOIN space_user cshared ON(cshared.UserID = bshared.UserID)  
  120. LEFT JOIN space_user dshared ON(a.UserID = dshared.UserID)  
  121. LEFT JOIN space_user fshared ON(bshared.semiUserID = fshared.UserID)  
  122. LEFT JOIN space_praise_record g ON  
  123. (g.toUserID=b.userid and g.objectid = init.ObjectID and g.Status != 0 and g.type = 1 and g.userid =7007007)  
  124. LEFT JOIN space_praise_record gg ON  
  125. (gg.toUserID=a.userid and gg.objectid = init.ObjectID and gg.Status != 0 and gg.type = 1 and gg.userid =7007007)  
  126. LEFT JOIN songod.sod_song_ksc ksc on(ksc.songid=b.KSCSongID and init.dynamicType=1)   

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


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

0
0
上一篇： 数据库层防止重复提交
下一篇： Redis crackit 漏洞尝试
请登录后发表评论 登录
全部评论
壹頁書

注册时间：2013-10-19

  • 博文量
    620
  • 访问量
    6339947

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593