MySQL求和查询需要很长时间才能完成，寻找瓶颈。

Question

我正在运行一个简单的MySQL查询，以找出用户玩游戏总共花费的时间：

SELECT userId, SUM(time) AS totalGameTime
                    FROM game_attempts
                    WHERE userId = 19599

EXPLAIN 显示如下内容：

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  game_attempts  ref  userId_gameId  userId_gameId  4  const  26880  

PROFILER显示，大部分时间花费在“发送数据”上：

Sending data    1.786524

为什么这样一个简单的查询需要花费如此长的时间才能完成？如何查找瓶颈所在？

更新。时间是INT(11)字段，没有涉及转换。

更新。可能的解决方法是引入（userId，time）索引，通过将部分数据移动到索引树中来解决问题。但它并不能解决加总30000个整数需要那么长时间的更大问题。

这个问题没有简单的答案。索引是正确的，没有耗时的转换。这只是关于DB引擎调优的问题——为什么定位这30000条记录和检索数据需要这么长时间呢？

重要的是说，表使用InnoDB引擎，包含约200万条记录。

- Denis Kulagin

---

当你说“巨大”的时间……？ - Strawberry

我认为对于这样一个简单的查询来说，2秒钟是非常长的时间。 - Denis Kulagin

它是否正在从远程服务器运行？ - Mark

不是的。我是通过在本地主机上使用php_my_admin运行它。我的意思是服务器和php_my_admin都位于同一台物理机器上。 - Denis Kulagin

@Strawberry - 这是一个糟糕的建议，MyISAM通常比InnoDB慢，认为它更快是一种误解，在这种特定情况下，基于InnoDB的引擎比MyISAM更慢几乎没有理由。 - N.B.

Answer 1

尝试像这样为userId创建索引，可以解决您的问题：

   ALTER TABLE game_attempts ADD INDEX (userId);

- echo_Me

---

不会的。MySQL已经使用userId_gameId复合索引来过滤userId了。 - Denis Kulagin

Answer 2

它表明您正在将大量行返回给客户端。请添加。

GROUP BY userId

如何确保只返回一行数据？

(保留HTML标记)

- Graham Griffiths

---

我现在意识到 MySQL 有特殊的行为，允许你选择不在 'group by' 子句中的列。服务器可以从该列的任何分组中选择任何值：http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html - Graham Griffiths

Answer 3

在任何其他的DBMS中，你的语句都会被视为无效的SQL，因为你查询的选择部分包含了一个聚合函数以及一个不属于GROUP BY子句的字段 - 实际上，你没有GROUP BY子句。

例如，Oracle会告诉你：

ORA-00937: not a single-group group function

在MSSQL中也会得到类似的结果。我猜想MySQL在这里所做的是比需要计算SUM更频繁。

以下查询将更符合SQL标准，并且速度更快：

SELECT userId, SUM(time) AS totalGameTime
  FROM game_attempts
 WHERE userId = 19599
GROUP BY userId;

- UweB

---

我希望分组会有所帮助，但事实并非如此。 - Denis Kulagin

显式使用GROUP BY版本的EXPLAIN是否显示它只返回一行？ - Graham Griffiths

我怀疑这不会“快得多”，但很高兴被证明是错误的。 - Strawberry

Answer 4

好的，我把这个作为答案记录下来，这样你就不会再犯这个错误了。

从MySQL 5.0.23开始，你可以设置

ONLY_FULL_GROUP_BY by SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY ';

您需要正确配置服务器

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause

来源 (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by)

- Raymond Nijland

Answer 5

在userId上创建一个索引。这将限制访问具有失败userId的记录。

- Joop Eggen

---

这绝对不是索引的问题。我的(userId, gameId)两列都有索引，并且 EXPLAIN 也确认了数据库引擎正在使用它。 - Denis Kulagin

嗯，这让它变得有趣了。尝试使用COUNT(userId)而不是SUM。时间上的“发送数据”有点名不副实，实际上是涉及读取。一个猜测，不太可能，是时间求和涉及来回转换。 - Joop Eggen

COUNT的效果非常好（少于5毫秒），但是EXPLAIN中额外的部分包含“使用索引”。我建议只使用索引树，不涉及实际数据。 - Denis Kulagin

而COUNT返回26880？ - Graham Griffiths

不需要转换，时间已经是一个整数变量——INT（11）！ - Denis Kulagin

Answer 6

你的“时间”列精度太高了吗？ 如果你进行求和操作呢？

SEC_TO_TIME(SUM(TIME_TO_SEC(time)))

用“代替”吗？

- Graham Griffiths

---

抱歉，我忘了说时间只是一个整数字段，没有涉及到转换。 - Denis Kulagin