今天遇到一个很奇怪的问题
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
MySQL union的一种优化
原文：http://blog.itpub.net/29254281/viewspace-1681354/
