MySQL union的一種優化

壹頁書發表於2015-06-01
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!=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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章