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

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

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

查詢游戲歷史成績(jī)最高分前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í)行時(shí)間僅為0.22秒，比原來(lái)的25秒提高了10000倍

查詢當(dāng)天游戲最好成績(jī)

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' )

特別消耗時(shí)間

另外，像：

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

這樣的語(yǔ)句會(huì)導(dǎo)致索引無(wú)效，因?yàn)閷?duì)每個(gè)dataline的值都需要用函數(shù)計(jì)算一遍，需要調(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

對(duì)于每個(gè)球員，找出球員號(hào)碼，名字以及他所引起的罰款的號(hào)碼，但只是針對(duì)那些至少有兩次罰款的球員。

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

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子句中的子查詢決定了每個(gè)球員的號(hào)碼，名字和罰款的編號(hào)。接下來(lái)，這個(gè)號(hào)碼變成了中間結(jié)果中的一列。然后指定了一個(gè)條件（NUMBER>=2）;最后，獲取SELECT子句中的列。

總結(jié)

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

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

您可能感興趣的文章: