服务器之家

服务器之家 > 正文

MYSQL子查询和嵌套查询优化实例解析

时间:2020-08-16 17:19     来源/作者:dodomail

查询游戏历史成绩最高分前100

Sql代码

?
1
2
3
4
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代码

?
1
2
3
4
5
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代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

像查询里:

?
1
2
3
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' )

特别消耗时间

另外,像:

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

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

Sql代码

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

//更改后
Sql代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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代码

?
1
2
3
4
5
6
7
8
9
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子句中的子查询决定了每个球员的号码,名字和罚款的编号。接下来,这个号码变成了中间结果中的一列。然后指定了一个条件(NUMBER>=2);最后,获取SELECT子句中的列。

总结

以上就是本文关于MYSQL子查询和嵌套查询优化实例解析的全部内容,希望对大家有所帮助。如有不足之处请留言,小编会及时更正。

感谢朋友们对服务器之家网站的支持!

原文链接:http://dodomail.iteye.com/blog/250199

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
返回顶部