mysql union 慢_MySQL union的一种优化

今天遇到一个很奇怪的问题

SELECT zav.ZpID ZpID,

zav.Name Name,

zav.PlayTimes PlayTimes,

zav.UserID UserID,

zav.PerformerID PerformerId,

zav.Performer Performer,

zav.WriterID WriterId,

zav.Writer Writer,

zav.ComposerID ComposerId,

zav.Composer Composer,

zav.Compiler Compiler,

zav.Recorder Recorder,

zav.Harmony Harmony,

zav.Mixer Mixer,

zav.Publisher Publisher,

zav.SongWords SongWords,

zav.FileUrl FileUrl,

zav.frontCoverUrl frontCoverUrl,

zav.Software Software,

zav.Type Type,

zav.Kind Kind,

zav.Labels Labels

from

(

SELECT *  from zp_audio_video where CategoryOneID=7  and CategoryTwoID =123 and Performer='诗卓'  and State = 1   AND   Type = 0 and zpid!=326647

union all

select * from (

select * from (

select * from (

select * from (

SELECT *  from zp_audio_video where CategoryOneID=7 and zpid!=326647 and CategoryTwoID =123

and State = 1 and Performer!='诗卓'  AND   Type = 0 ORDER BY PlayTimes desc limit 100

)tb1 ORDER BY RAND() limit 24

)tb2

)tb5 order by tb5.PlayTimes desc

)tb6

union all

SELECT *  from zp_audio_video where CategoryOneID=7  and CategoryTwoID !=123   and State = 1   AND   Type = 0 and zpid!=326647 order by PlayTimes desc limit 100

)zav  limit 0,24;

内层三个union all的查询

每个查询单独执行都非常快,时间0.01 sec左右,

但是这个整体的SQL居然需要0.7s..

查阅资料发现,有如下的情况,MySQL会直接使用磁盘临时表

1.表中包含了BLOB和TEXT字段(MEMORY引擎不支持这两种字段)

2.group by和distinct子句中的有超过512字节的字段

3.UNION以及UNION ALL语句中，如果SELECT子句中包含了超过512(对于binary string是512字节，对于character是512个字符)的字段。

当然,使用磁盘临时表,肯定比内存临时表要慢很多.

不巧的是,SongWords的定义是varchar(4096)

找到问题就好解决了,既然union all中超过512字节则直接使用磁盘临时表,那么可以采用延迟关联的方式

先查找符合条件的ID,再使用内连接关联

SELECT zav.ZpID ZpID,

zav.Name Name,

zav.PlayTimes PlayTimes,

zav.UserID UserID,

zav.PerformerID PerformerId,

zav.Performer Performer,

zav.WriterID WriterId,

zav.Writer Writer,

zav.ComposerID ComposerId,

zav.Composer Composer,

zav.Compiler Compiler,

zav.Recorder Recorder,

zav.Harmony Harmony,

zav.Mixer Mixer,

zav.Publisher Publisher,

zav.SongWords SongWords,

zav.FileUrl FileUrl,

zav.frontCoverUrl frontCoverUrl,

zav.Software Software,

zav.Type Type,

zav.Kind Kind,

zav.Labels Labels

from zp_audio_video zav

inner join

(

select zpid from (

(SELECT zpid from zp_audio_video where CategoryOneID=7  and CategoryTwoID =123 and Performer='诗卓'  and State = 1   AND   Type = 0 and zpid!=326647)

union all

(

select zpid from (

select * from (

select * from (

SELECT zpid,PlayTimes  from zp_audio_video where CategoryOneID=7 and zpid!=326647 and CategoryTwoID =123

and State = 1 and Performer!='诗卓'  AND   Type = 0 ORDER BY PlayTimes desc limit 100

)tb1 ORDER BY RAND() limit 24

)tb2

)tb5 order by tb5.PlayTimes desc

)

union all

(SELECT zpid from zp_audio_video where CategoryOneID=7  and CategoryTwoID !=123   and State = 1   AND   Type = 0 and zpid!=326647 order by PlayTimes desc limit 100)

) t1

) t2 on(t2.zpid=zav.zpid)  limit 0,24;

使用延迟关联之后,查询时间降为0.01 sec

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