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;

执行时间仅为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'

这样的语句会导致索引无效，因为对每个dataline的值都需要用函数计算一遍，需要调整为：

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