mysql延迟关联

1、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.Typ

2、e 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 =123and 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 allSELEC

3、T * 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字节则直接使用

4、磁盘临时表,那么可以采用延迟关联的方式先查找符合条件的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

5、Kind, zav.Labels Labels from zp_audio_video zavinner 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 =123and State = 1 and Performer!=诗卓 AND Type = 0 ORDER BY PlayTimes desc limit 100)tb1 ORDER BY RAND() limit 24)tb2)tb5 order by

6、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 secMySQL延迟关联性能优化方法这篇文章主要介绍了MySQL延迟关联性能优化方法,本文讲解了延迟关联的背景、延迟关联的分析、延迟关联的解决等内容,需要的朋友可以参考下：【背景】 某业务数据库load 报警异常，cpu usr 达到30-40 ，居高不下。使用工具查看数据库正在执行的sql ，排在前面的大部分是：代码如下:SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, marke

7、t_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =0 AND end_time =2014-05-29 ORDER BY id asc LIMIT 149420 ,20;表的数据量大致有36w左右，该sql是一个非常典型的排序+分页查询：order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行，然后再去取 M行。对于此类大数据量的排序操作，取前面少数几行数据会很快，但是越靠后，sql的性能就会越差，因为N越大，MySQL 需要扫描不需要的数据然后在丢掉，这样耗费大量的时间。【分析】针对limit 优化有很多种方式，1 前端加缓存，减少落到库的查询操作2 优化SQL3 使用书签方式 ，记录上次查询最新/大的id值，向后追溯 M行记录。4 使用Sphinx 搜索优化。对于第二种方式 我们推荐使用延迟关联的方法来优化排序操作，何谓延迟关联 ：通过使用覆盖索引查询返回需要的主键-/*关键点在这里（通过from子句中子查询语句找到，建立关联关系的表的主键*/,再根据主键关联原表获得需要的数据。通过这样的方式达到延迟2字的意思。【解决】根据延迟关联的思路，修改SQL 如下：优化前复制代码 代码如下:rootxxx 12:33:48explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =0 AND end_time =2014-05-29 O

《mysql延迟关联》由会员简****9分享，可在线阅读，更多相关《mysql延迟关联》请在金锄头文库上搜索。