一次UnionAll的合併優化

壹頁書發表於2015-12-23
優化
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部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1878089/，如需轉載，請註明出處，否則將追究法律責任。

相關文章