ITPub博客

MySQL in UnionAll结果集的优化

原创 作者:壹頁書 时间:2015-12-31 10:21:41 5400 0 删除 编辑
问题还是前面的问题
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

请登录后发表评论 登录
全部评论 0

注册时间:2013-10-19

  • 博文量
    612
  • 访问量
    5788590