MySQL union的一種優化
今天遇到一個很奇怪的問題
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
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle優化案例-union代替or(九)Oracle優化
- SQL優化案例-union代替or(九)SQL優化
- mysql優化(一)MySql優化
- Oracle優化案例-又見union代替or(二十)Oracle優化
- MySQL學習(五) UNION與UNION ALLMySql
- Mysql效能優化一MySql優化
- Mysql索引優化(一)MySql索引優化
- 【MySQL】NOT EXISTS優化的一個案例MySql優化
- MySQL SQL優化案例(一)MySql優化
- Mysql高階優化(一)MySql優化
- 專題《一》 mysql優化MySql優化
- Mysql多欄位大表的幾種優化方法MySql優化
- MySQL, Incorrect usage of UNION and ORDER BYMySql
- MySQL 優化一(高階篇)MySql優化
- 一份平民化的MySQL效能優化指南MySql優化
- MySQL優化(1)——–常用的優化步驟MySql優化
- 有一種優化方法叫做Preload優化
- MySQL 海量資料的 5 種分頁方法和優化技巧MySql優化
- mysql優化MySql優化
- Mysql 優化MySql優化
- MySQL之SQL優化詳解(一)MySql優化
- MySql常用30種SQL查詢語句優化方法MySql優化
- MySQL 的查詢優化MySql優化
- MySQL架構的優化MySql架構優化
- mysql常用的優化操作MySql優化
- 關於mysql的優化MySql優化
- (mysql優化-3) 系統優化MySql優化
- MySQL 效能優化的 9 種姿勢,面試再也不怕了!MySql優化面試
- 兩種簡單分析和優化MySQL資料庫表的方法優化MySql資料庫
- MySQL(二) MySql常用優化MySql優化
- 介紹幾種提高mysql的效能和對於sql的優化的方法MySql優化
- 一種小程式弱網離線優化的思路優化
- 剖析Elasticsearch的IndexSorting:一種查詢效能優化利器ElasticsearchIndex優化
- mysql效能優化MySql優化
- MySQL表優化MySql優化
- MySQL——效能優化MySql優化
- mysql優化索引MySql優化索引
- MySQL索引優化MySql索引優化
- mysql order by 優化MySql優化