MySQL in UnionAll結果集的優化

問題還是前面的問題
http://blog.itpub.net/29254281/viewspace-1878089/
http://blog.itpub.net/29254281/viewspace-1961333/

MySQL 5.6.14

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

原來的SQL:

select
1 dynamicType,
- 1 shareID,
AVID,
createTime,
userID,
commentTimes,
fromIP,
praiseTimes
from
space_av
where
userid in (select
toUserID
from
space_friend
where
userID = 901164037 union all select 901164037)
order by createTime DESC
limit 30;

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

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

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

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

如果in子查詢中沒有union all則執行計劃如下:

explain
select
1 dynamicType,
- 1 shareID,
AVID,
createTime,
userID,
commentTimes,
fromIP,
praiseTimes
from
space_av
where
userid in (select
toUserID
from
space_friend
where
userID = 901164037)
order by createTime DESC
limit 30;

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

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

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

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

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

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

SELECT
1 AS dynamicType,
- 1 AS shareID,
AVID,
createTime,
userID,
commentTimes,
fromIP,
praiseTimes
FROM
space_av
WHERE
userid IN
(
SELECT
toUserID
FROM
(
SELECT
toUserID
FROM
space_friend
WHERE
userID = 901164037
UNION ALL
SELECT 901164037
) tmp
)
ORDER BY createTime DESC
LIMIT 30

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

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

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

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

MySQL in UnionAll結果集的優化

相關文章