使用STRAIGHT_JOIN优化一则

还是之前的那个事情.
http://blog.itpub.net/29254281/viewspace-1878089/

这个东西我还是想简单了.
原来的那个SQL,对于正常添加好友的用户,速度是很快的.
但是对于大量添加好友的用户,则会非常慢.
但是我们没有限制用户添加好友的最大数量.这是问题的关键.
有的用户竟然添加了上万的好友.

对于大量添加好友的用户,而无论是原来的,还是新改的SQL,都是如下部分最耗性能.

1.             select     
2.                 ta.shareID, ta.createtime, 2    
3.             from    
4.                 space_share ta    
5.             inner join     
6.             (    
7.                     SELECT     
8.                         ToUserID userid    
9.                     FROM    
10.                         space_friend    
11.                     WHERE    
12.                     UserID = 900438523 AND STATUS != 0 UNION ALL select 900438523    
13.             ) v2 on (v2.userid=ta.userid)    
14.             ORDER BY ta.CreateTime DESC    
15.             LIMIT 30   

这个用户添加了2.8w个好友.该SQL执行时间0.515s.
而正常的用户是一瞬间执行完成.
执行计划:


可以看到,MySQL 先执行v2,然后连接ta,然后做排序,返回30个数据.
MySQL认为v2的结果集比ta的结果集小.
这个SQL慢,主要是因为返回的结果太多,并且最后需要对大量数据排序.

如果先执行ta,就可以用索引消除排序.
这就需要用STRAIGHT_JOIN 强制连接的顺序.

1.          select     
2.              ta.shareID, ta.createtime, 2    
3.          from    
4.              space_share ta    
5.          STRAIGHT_JOIN   
6.          (    
7.                  SELECT     
8.                      ToUserID userid    
9.                  FROM    
10.                      space_friend    
11.                  WHERE    
12.                  UserID = 900438523 AND STATUS != 0 UNION ALL select 900438523    
13.          ) v2 on (v2.userid=ta.userid)    
14.          ORDER BY ta.CreateTime DESC    
15.          LIMIT 30   

执行计划:


执行时间:0.015s

不过这个场景,更适合semi join 

1.         select     
2.                ta.shareID, ta.createtime, 2    
3.         from    
4.                space_share ta    
5.         where ta.userid in (  
6.                    SELECT     
7.                        ToUserID userid    
8.                    FROM    
9.                        space_friend    
10.                    WHERE    
11.                    UserID = 900438523 AND STATUS != 0 UNION ALL select 900438523    
12.         )  
13.         ORDER BY ta.CreateTime DESC    
14.         LIMIT 30    

瞬间完成.

另外,相对于原来的SQL,我发现原来distinct完全没有必要.
因为每个用户的每次分享,都是不同的shareId,根本没有重复的可能性.

最终设想的改写方案
每天计算一次用户的好友数量,放在redis里.
如果好友数量小于1000,则用 普通的Inner Join内连接
如果好友数量大于1000,则用 STRAIGHT_JOIN 或者 in
当然,这个阈值的设定,需要实际的测试.

需要注意的是,如果用户的好友比较少,返回的数据很小,用 STRAIGHT_JOIN或者 in,反而拖累性能.

参考:
http://huoding.com/2013/06/04/261
http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/

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

转载于:http://blog.itpub.net/29254281/viewspace-1961333/