快捷導(dǎo)航

MYSQL子查詢和嵌套查詢優(yōu)化實例解析

更新時間：2017年10月13日 09:14:21 作者：dodomail

本文通過實例向大家介紹了MYSQL子查詢和嵌套查詢優(yōu)化的相關(guān)內(nèi)容，附代碼示例，具有一定參考價值。希望對大家使用MySQL有所幫助。

查詢游戲歷史成績最高分前100

Sql代碼

SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)  
FROM cdb_playsgame ps1  
where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3'  
GROUP BY ps.uid order by ps.credits desc LIMIT 100;

Sql代碼

SELECT ps.*  
FROM cdb_playsgame ps,(select ps1.uid, ps1.gametag, MAX(credits) as credits 
FROM cdb_playsgame ps1 group by uid,gametag) t 
WHERE ps.credits=t.credits AND ps.uid=t.uid AND ps.gametag=t.gametag AND ps.gametag='yeti3'  
GROUP BY ps.uid order by ps.credits desc LIMIT 100;

執(zhí)行時間僅為0.22秒，比原來的25秒提高了10000倍

查詢當天游戲最好成績

Sql代碼

 SELECT ps. * , mf. * , m.username 
FROM cdb_playsgame ps 
LEFT JOIN cdb_memberfields mf ON mf.uid = ps.uid 
LEFT JOIN cdb_members m ON m.uid = ps.uid 
WHERE ps.gametag = 'chuansj' 
AND FROM_UNIXTIME( ps.dateline, '%Y%m%d' ) = '20081008' 
AND ps.credits = ( 
SELECT MAX( ps1.credits ) 
FROM cdb_playsgame ps1 
WHERE ps.uid = ps1.uid 
AND ps1.gametag = 'chuansj' 
AND FROM_UNIXTIME( ps1.dateline, '%Y%m%d' ) = '20081008' ) 
GROUP BY ps.uid 
ORDER BY credits DESC 
LIMIT 0 , 50

像查詢里：

AND ps.credits=(SELECT MAX(ps1.credits)  
 FROM {$tablepre}playsgame ps1 where ps.uid=ps1.uid AND ps1.gametag = '$game'  
 AND FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime' )

特別消耗時間

另外，像：

FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime'

這樣的語句會導(dǎo)致索引無效，因為對每個dataline的值都需要用函數(shù)計算一遍，需要調(diào)整為：

Sql代碼

AND ps1.dateline >= UNIX_TIMESTAMP('$todaytime')

//更改后
Sql代碼

 SELECT ps. * , mf. * , m.username 
FROM cdb_playsgame ps, cdb_memberfields mf, cdb_members m, ( 
 
SELECT ps1.uid, MAX( ps1.credits ) AS credits 
FROM cdb_playsgame ps1 
WHERE ps1.gametag = 'chuansj' 
AND ps1.dateline >= UNIX_TIMESTAMP( '20081008' ) 
GROUP BY ps1.uid 
) AS t 
WHERE mf.uid = ps.uid 
AND m.uid = ps.uid 
AND ps.gametag = 'chuansj' 
AND ps.credits = t.credits 
AND ps.uid = t.uid 
GROUP BY ps.uid 
ORDER BY credits DESC 
LIMIT 0 , 50

對于每個球員，找出球員號碼，名字以及他所引起的罰款的號碼，但只是針對那些至少有兩次罰款的球員。

更緊湊的查詢，在FROM子句中放置一個子查詢。

Sql代碼

SELECT PLAYERNO,NAME,NUMBER 
FROM (SELECT PLAYERNO,NAME, 
       (SELECT COUNT(*) 
       FROM PENALTIES 
       WHERE PENALTIES.PLAYERNO = 
          PLAYERS.PLAYERNO) 
       AS NUMBER 
    FROM PLYERS) AS PN 
WHERE NUMBER>=2

FROM子句中的子查詢決定了每個球員的號碼，名字和罰款的編號。接下來，這個號碼變成了中間結(jié)果中的一列。然后指定了一個條件（NUMBER>=2）;最后，獲取SELECT子句中的列。

總結(jié)

以上就是本文關(guān)于MYSQL子查詢和嵌套查詢優(yōu)化實例解析的全部內(nèi)容，希望對大家有所幫助。感興趣的朋友可以參閱：mysql in語句子查詢效率慢的優(yōu)化技巧示例、淺談mysql的子查詢聯(lián)合與in的效率等，如有不足之處請留言，小編會及時更正。

感謝朋友們對腳本之家網(wǎng)站的支持！

您可能感興趣的文章: