使用STRAIGHT_JOIN優化一則

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

這個東西我還是想簡單了.
原來的那個SQL,對於正常新增好友的使用者,速度是很快的.
但是對於大量新增好友的使用者,則會非常慢.
但是我們沒有限制使用者新增好友的最大數量.這是問題的關鍵.
有的使用者竟然新增了上萬的好友.

對於大量新增好友的使用者,而無論是原來的,還是新改的SQL,都是如下部分最耗效能.

select
ta.shareID, ta.createtime, 2
from
space_share ta
inner join
(
SELECT
ToUserID userid
FROM
space_friend
WHERE
UserID = 900438523 AND STATUS != 0 UNION ALL select 900438523
) v2 on (v2.userid=ta.userid)
ORDER BY ta.CreateTime DESC
LIMIT 30

這個使用者新增了2.8w個好友.該SQL執行時間0.515s.
而正常的使用者是一瞬間執行完成.
執行計劃:

可以看到,MySQL 先執行v2,然後連線ta,然後做排序,返回30個資料.
MySQL認為v2的結果集比ta的結果集小.
這個SQL慢,主要是因為返回的結果太多,並且最後需要對大量資料排序.

如果先執行ta,就可以用索引消除排序.
這就需要用STRAIGHT_JOIN 強制連線的順序.

select
ta.shareID, ta.createtime, 2
from
space_share ta
STRAIGHT_JOIN
(
SELECT
ToUserID userid
FROM
space_friend
WHERE
UserID = 900438523 AND STATUS != 0 UNION ALL select 900438523
) v2 on (v2.userid=ta.userid)
ORDER BY ta.CreateTime DESC
LIMIT 30

執行計劃:

執行時間:0.015s

不過這個場景,更適合semi join

select
ta.shareID, ta.createtime, 2
from
space_share ta
where ta.userid in (
SELECT
ToUserID userid
FROM
space_friend
WHERE
UserID = 900438523 AND STATUS != 0 UNION ALL select 900438523
)
ORDER BY ta.CreateTime DESC
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/，如需轉載，請註明出處，否則將追究法律責任。

使用STRAIGHT_JOIN優化一則

相關文章