MySQL in UnionAll結果集的優化

壹頁書發表於2015-12-31
MySql優化
問題還是前面的問題
http://blog.itpub.net/29254281/viewspace-1878089/
http://blog.itpub.net/29254281/viewspace-1961333/

MySQL 5.6.14

需求:
找使用者和使用者朋友唱的歌,按照時間排序,返回最近的30個.

原來的SQL:
  1. select   
  2.     1 dynamicType,  
  3.     - 1 shareID,  
  4.     AVID,  
  5.     createTime,  
  6.     userID,  
  7.     commentTimes,  
  8.     fromIP,  
  9.     praiseTimes  
  10. from  
  11.     space_av  
  12. where  
  13.     userid in (select   
  14.             toUserID  
  15.         from  
  16.             space_friend  
  17.         where  
  18.             userID = 901164037 union all select 901164037)  
  19. order by createTime DESC  
  20. limit 30;  

執行時間:0.28s
執行計劃:


現象:
如果使用者加的好友非常多,比如一口氣加了幾萬好友,則這個SQL非常快.
如果使用者只是加了十幾個好友,則這個SQL很慢.

上面的使用者只是加了幾個好友,所以執行的很慢.

為什麼走了索引全掃描呢?
space_av是使用者的歌曲作品表,資料量非常的大.

如果in子查詢中沒有union all則執行計劃如下:
  1. explain  
  2. select     
  3.     1 dynamicType,    
  4.     - 1 shareID,    
  5.     AVID,    
  6.     createTime,    
  7.     userID,    
  8.     commentTimes,    
  9.     fromIP,    
  10.     praiseTimes    
  11. from    
  12.     space_av    
  13. where    
  14.     userid in (select     
  15.             toUserID    
  16.         from    
  17.             space_friend    
  18.         where    
  19.             userID = 901164037)    
  20. order by createTime DESC    
  21. limit 30;    

如果in子查詢返回的結果比較少,執行計劃如下:

因為結果集比較小,所以即使有排序,問題也不大.


如果in子查詢返回的結果很多,則執行計劃如下:

走索引全掃描,但是使用索引消除排序,所以速度也很快.


問題來了,一開始的那個SQL  in (select xx union all select xx)
in 子查詢返回的結果集很小,但是優化器選擇了走索引全掃描.
可能是因為MySQL 認為子查詢中有union all 結果集一定很大,所以走了索引全掃描。
但是,如果子查詢中union all返回的結果很小,則效能直線下降.

一種變通的優化方法:騙優化器說,這不是一個包含union all的子查詢.

  1. SELECT   
  2.     1 AS dynamicType,  
  3.     - 1 AS shareID,  
  4.     AVID,  
  5.     createTime,  
  6.     userID,  
  7.     commentTimes,  
  8.     fromIP,  
  9.     praiseTimes  
  10. FROM  
  11.     space_av  
  12. WHERE  
  13.     userid IN   
  14.     (  
  15.         SELECT   
  16.             toUserID  
  17.         FROM  
  18.         (  
  19.             SELECT   
  20.                 toUserID  
  21.             FROM  
  22.                 space_friend  
  23.             WHERE  
  24.                 userID = 901164037   
  25.                 UNION ALL   
  26.             SELECT 901164037  
  27.         ) tmp  
  28.     )  
  29. ORDER BY createTime DESC  
  30. LIMIT 30  

那麼在子查詢返回結果集小的時候,執行計劃如下:


在子查詢返回結果集很大的時候,執行計劃如下:


恩,根據返回結果,都走了正確的執行計劃.
僅僅是因為在 union all 外面又套了一層,讓優化器感知不到子查詢中包含了union all

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1968995/，如需轉載，請註明出處，否則將追究法律責任。

相關文章