资源描述:
《mysql延迟关联》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、MySQLunion的一种优化 2015-06-0123:18:24分类: MySQL今天遇到一个很奇怪的问题SELECTzav.ZpIDZpID, zav.NameName, zav.PlayTimesPlayTimes, zav.UserIDUserID, zav.PerformerIDPerformerId, zav.PerformerPerformer, zav.WriterIDWriterId, zav.WriterWriter, zav.Compose
2、rIDComposerId, zav.ComposerComposer, zav.CompilerCompiler, zav.RecorderRecorder, zav.HarmonyHarmony, zav.MixerMixer, zav.PublisherPublisher, zav.SongWordsSongWords, zav.FileUrlFileUrl, zav.frontCoverUrlfrontCoverUrl, zav
3、.SoftwareSoftware, zav.TypeType, zav.KindKind, zav.LabelsLabels from (SELECT* fromzp_audio_videowhereCategoryOneID=7 andCategoryTwoID=123andPerformer='诗卓' andState=1 AND Type=0andzpid!=326647unionallselect*from( select*from( select*from( select*fro
4、m( SELECT* fromzp_audio_videowhereCategoryOneID=7andzpid!=326647andCategoryTwoID=123 andState=1andPerformer!='诗卓' AND Type=0ORDERBYPlayTimesdesclimit100 )tb1ORDERBYRAND()limit24 )tb2 )tb5orderbytb5.PlayTimesdesc)tb6unionallSELECT* fromzp_
5、audio_videowhereCategoryOneID=7 andCategoryTwoID!=123 andState=1 AND Type=0andzpid!=326647orderbyPlayTimesdesclimit100)zav limit0,24;内层三个unionall的查询每个查询单独执行都非常快,时间0.01sec左右,但是这个整体的SQL居然需要0.7s..查阅资料发现,有如下的情况,MySQL会直接使用磁盘临时表1.表中包含了BLOB和TEXT字段(MEMORY引擎不支持这两种字段)2.groupby和distinct子句中的有超过512字节的字
6、段3.UNION以及UNIONALL语句中,如果SELECT子句中包含了超过512(对于binarystring是512字节,对于character是512个字符)的字段。当然,使用磁盘临时表,肯定比内存临时表要慢很多.不巧的是,SongWords的定义是varchar(4096)找到问题就好解决了,既然unionall中超过512字节则直接使用磁盘临时表,那么可以采用延迟关联的方式先查找符合条件的ID,再使用内连接关联SELECTzav.ZpIDZpID, zav.NameName, zav.PlayTimesPlayTimes,
7、 zav.UserIDUserID, zav.PerformerIDPerformerId, zav.PerformerPerformer, zav.WriterIDWriterId, zav.WriterWriter, zav.ComposerIDComposerId, zav.ComposerComposer, zav.CompilerCompiler, zav.RecorderRecorder,