mysql延迟关联.docxVIP

?viewspace-1681354/MySQL union的一种优化?2015-06-01 23:18:24分类：?MySQL今天遇到一个很奇怪的问题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!=326647union allselect * 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)tb6union allSELECT * ?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.7 s..查阅资料发现,有如下的情况,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