如何优化此MySQL查询

Question

这个查询在数据库小的时候工作得很好，但是现在数据库中有数百万行，我意识到我应该更早地优化它。它正在查看超过600,000行，并使用where；使用临时；使用文件排序(这导致执行时间为5-10秒)。它在字段‘battle_type’上使用索引。

SELECT username, SUM( outcome ) AS wins, COUNT( * ) - SUM( outcome ) AS losses
FROM tblBattleHistory
WHERE battle_type =  '0' && outcome <  '2'
GROUP BY username
ORDER BY wins DESC , losses ASC , username ASC 
LIMIT 0 , 50

Answer 1

第一件事是确保你有良好的索引(正如其他人所提到的)。

但是，看起来您正在为网页创建某种排行榜。我的第一个问题是--您真的需要实时执行这个查询吗？您是否可以使用此查询的结果在数据库中创建一个表(或在users表中添加一个wins和losses列)，并定期刷新它？

Answer 2

看起来你需要一个关于username, battle_type, outcome或username, outcome, battle_type的索引。

Answer 3

让我们看看你在做什么：

对于grouping

• Compute聚合，

1. 查找battle_type =0且结果<2的行
2. Order by username，并将行折叠到distinct usernames
3. Order by动态计算字段

在第3步和第4步中，您没有任何影响。当前形式的步骤2不能从任何索引中受益，因为outcome < 2是范围条件，索引on (battle_type，outcome，username)看起来非常诱人。

假设outcome是0,1,2,3...的枚举，您可以将范围条件更改为相等比较，并从索引(battle_type，结果，用户名)中受益：

SELECT username, SUM( outcome ) AS wins, COUNT( * ) - SUM( outcome ) AS losses
FROM tblBattleHistory
WHERE battle_type = 0 AND outcome IN (0, 1)
GROUP BY username
ORDER BY wins DESC , losses ASC , username ASC 
LIMIT 0 , 50

如果outcome不是枚举，那么index on (battle_type，outcome)也可以。由于battle_type是复合索引中的前缀，因此现在仅在(battle_type)上的索引是多余的。