问题还是前面的问题
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博客

MySQL in UnionAll结果集的优化

最新文章