如何优化这个 MySQL 查询

Question

这个查询在数据库较小时运作良好，但现在有数百万行数据时，我意识到应该早些优化它。它正在查看超过600,000行数据并使用了“Using where; Using temporary; Using filesort”(导致执行时间为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

- James Simpson

---

运行 EXPLAIN 命令，它会告诉你哪些地方需要优化。 - Michael Todd

你可以做的一件事是将结果缓存到文件或其他地方，并每隔X分钟重新缓存。 - Zack

Answer 1

看起来你需要在 username, battle_type, outcome 或者 username, outcome, battle_type 上创建索引。

- Gilbert Le Blanc

---

不同顺序的索引有什么区别？ - Kerry Jones

@Kerry：通常情况下，您首先指定最具有粒度的列，然后是较少粒度的列。 - Gilbert Le Blanc

@newtover：没错，你说得对。我猜下一个要尝试的索引应该是outcome、battle_type和username。 - Gilbert Le Blanc

@Gilbert Le Blanc：你又错过了=），请看下面的回答。 - newtover

@Kerry：粒度是衡量SQL表列独特性的指标。id列具有很高的粒度，每个元素都是唯一的。粒度极低的列将是性别列，它的唯一值是“M”，“F”和null。您希望为具有高粒度的列创建索引。 - Gilbert Le Blanc

Answer 2

让我们看看你正在做什么：

1. 查找战斗类型为0且结果小于2的行
2. 按用户名分组排序
3. 计算聚合并将行折叠到不同的用户名中
4. 按动态计算字段排序

在步骤3和4中，你没有影响。当前形式的步骤2无法从任何索引中受益，因为outcome < 2是范围条件，但是在（battle_type，outcome，username）上建立索引看起来非常诱人。

假设outcome枚举为0,1,2,3...，你可以将范围条件更改为相等比较，并从（battle_type，outcome，username）索引中受益：

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不是枚举类型，那么在(battle_type, outcome)上建立索引就可以了。现在只建立(battle_type)的索引已经过度了，因为battle_type是复合索引中的前缀。

- newtover

---

这对一些帮助，但仍然需要查看超过500,000行，因此我将制作一个表来缓存结果并每10分钟更新。 - James Simpson

Answer 3

首先要确保你有良好的索引（正如其他人所提到的）。

然而，看起来你正在为网页创建排行榜。我的第一个问题是 - 你真的需要实时执行这个查询吗？你是否可以在数据库中创建一张表（或者在用户表中添加胜利和失败列），将这个查询的结果存储在其中，并定期刷新它？

- Eric Petroelje