优化这个MySQL查询的最佳方法是什么？

Question

这是一个查询，用来统计每个玩家在游戏中的结果，并显示符合条件的玩家。

select   *, 
         (kills / deaths)  as killdeathratio, 
         (totgames - wins) as losses 
from     (select   gp.name              as name, 
                   gp.gameid            as gameid, 
                   gp.colour            as colour, 
                   Avg(dp.courierkills) as courierkills, 
                   Avg(dp.raxkills)     as raxkills, 
                   Avg(dp.towerkills)   as towerkills, 
                   Avg(dp.assists)      as assists, 
                   Avg(dp.creepdenies)  as creepdenies, 
                   Avg(dp.creepkills)   as creepkills, 
                   Avg(dp.neutralkills) as neutralkills, 
                   Avg(dp.deaths)       as deaths, 
                   Avg(dp.kills)        as kills, 
                   sc.score             as totalscore, 
                   Count(* )            as totgames, 
                   Sum(case 
                         when ((dg.winner = 1 and dp.newcolour < 6) or 
                               (dg.winner = 2 and dp.newcolour > 6)) 
                         then 1 
                         else 0 
                       end) as wins 
          from     gameplayers as gp, 
                   dotagames   as dg, 
                   games       as ga, 
                   dotaplayers as dp, 
                   scores      as sc 
          where    dg.winner <> 0 
                   and dp.gameid = gp.gameid 
                   and dg.gameid = dp.gameid 
                   and dp.gameid = ga.id 
                   and gp.gameid = dg.gameid 
                   and gp.colour = dp.colour 
                   and sc.name   = gp.name 
          group by gp.name 
          having   totgames >= 30
          ) as h 
order by totalscore desc

现在我不太确定最好的优化方法是什么，但你认为有什么办法可以让这个查询更快呢？

我使用的是一台Q6600 @ 2.4GHz的电脑，4GB内存，运行64位的Linux Ubuntu 9.04系统，这个查询可能需要6.7秒才能完成（我的数据库非常大）。

另外，我还想对结果进行分页处理，但在这个查询的基础上执行额外的条件会太慢……

我用django作为前端，所以任何涉及使用python或django的方法都非常欢迎。对MySQL和Apache2的优化建议也很不错。当然，我也愿意修改查询，以便让它运行得更快。

感谢你阅读我的问题；期待看到你的回答！

编辑：解释查询结果

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra 
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    783     Using filesort
2   DERIVED     sc  ALL     name,name_2     NULL    NULL    NULL    2099    Using temporary; Using filesort
2   DERIVED     gp  ref     gameid,colour,name  name    17  development.sc.name     2    
2   DERIVED     ga  eq_ref  PRIMARY,id,id_2     PRIMARY     4   development.gp.gameid   1   Using index
2   DERIVED     dg  ref     gameid,winner   gameid  4   development.ga.id   1   Using where
2   DERIVED     dp  ref     gameid_2,colour     gameid_2    4   development.ga.id   10  Using where

Answer 1

没有任何索引能帮你，因为你是在扫描整个表。随着你的数据库越来越大，查询的速度会越来越慢。

可以考虑在每场比赛后，记录这场比赛的数据，同时在玩家的记录中增加一些计数。这样你就不需要再用count()和sum()去计算，因为这些信息已经准备好了。

Answer 2

以下是一些关于数据库查询的建议：

• 大多数情况下，使用“select *”是不好的，最好只选择你需要的列。
• 可以把查询分成多个简单的查询，当需要时可以使用临时表。
• 用“sum(case”这种写法可以通过子查询来实现。
• MySQL在处理“或”表达式时性能很差，建议使用两个查询，然后把结果合并在一起。

Answer 3

首先，这段SQL写得很糟糕。最明显的错误是在每个AS子句前面换行。第二个明显的问题是使用了隐式连接，而不是明确使用INNER JOIN ... ON ...。

现在来回答实际的问题。

在不知道数据或环境的情况下，我首先会查看一些MySQL服务器的设置，比如sort_buffer和key_buffer。如果你没有更改过这些设置，建议你去了解一下。默认的设置非常保守，通常可以提高十倍以上，特别是在你这种大型服务器上。

了解这些之后，我会运行查询的一部分，看看速度如何，以及EXPLAIN的结果。索引的效果可能非常显著，但MySQL有个“手指和脚”的问题，它每个表只能使用一个索引。而且带过滤的JOIN可能需要两个索引。所以它就得进行行扫描来进行其他检查。不过，拆分查询并尝试不同的组合会让你看到问题出在哪里。

现在你可能会有一个“临界点”的概念：这是指当某些原始数据的大小稍微增加，比如需要提取的数据量，性能会大幅下降，因为某些内部结构变得太大。在这个时候，你可能需要增加临时表的大小。要注意，这种优化有点像黑魔法。:-)

不过，还有另一种方法：反规范化。在简单的实现中，定期运行的脚本会不时执行这个耗时的查询，并将数据放入一个结构更接近你想要展示的单独表中。这种方法有多种变体。可以在应用程序中实时更新，或者使用表触发器。在另一种极端情况下，你可以让应用程序偶尔运行这个耗时的查询，但将结果缓存一段时间。如果很多人经常调用这个查询，哪怕是2秒的缓存时间，在每秒运行15次的请求中也会有明显的改善。

你还可以通过运行六个查询，每个查询返回一部分数据，然后对数据进行后处理，来找到生成相同数据的方法。你也可以运行你原始查询的版本，返回更多数据（这可能会更快，因为过滤更少），然后进行后处理。我发现很多时候，五个简单的小查询比一个试图做所有事情的大查询要快得多，速度可以快一个数量级，有时甚至两个数量级。