MySQL组查询与多个求和不使用索引，使用文件排序滞后

Question

给定下列表格…

CREATE TABLE values_table (
  id int(11) NOT NULL auto_increment,
  account_id int(11) NOT NULL,
  user_id int(11) NOT NULL,
  model varchar(255) NOT NULL,
  ...
  value1 int(11) NOT NULL default '0',
  value2 int(11) NOT NULL default '0',
  value3 int(11) NOT NULL default '0',
  value4 int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  ....
) ENGINE=InnoDB AUTO_INCREMENT=2364641 DEFAULT CHARSET=utf8;

和下面的查询…

SELECT user_id, SUM(value1) AS value1, SUM(value2) AS value2, SUM(value3) AS value3, SUM(value4) as value4
from values_table
where account_id = 10 and model = 'ModelName'
group by user_id;

…什么字段应该添加到索引中，以确保执行不会以Using temporary; Using filesort;结束?

我试过改编http://mysqldba.blogspot.com/2008/06/how-to-pick-indexes-for-order-by-and.html和http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html上描述的细节，但没有运气。

我试过(account_id, model), (account_id, model, user_id), (account_id, model, user_id, value1, value2, value3, value4)。它们都不阻止使用临时表和文件排序。

Answer 1

我的印象是MySQL只能在所有列都在索引中时用索引优化group by。然后，只有这些查询的一个子集可以被优化。你的问题已经指向文档，但这里是最新的版本。

您可以在(account_id, model)上使用索引来减少数据量。然而，你可能仍然有很多匹配，然后MySQL对索引和group by变得挑剔。

有一种方法可以让MySQL使用索引进行聚合。如果只有一个计算列，那么可以尝试:

select u.user_id,
       (select sum(v.value1)
        from values_table v
        where v.account_id = 10 and v.model = 'ModelName' and
              v.user_id = u.user_id
       ) as sum1
from (select distinct user_id
      from values_table
      where v.account_id = 10 and v.model = 'ModelName'
     ) u
group by u.user_id;

对于from中的子查询，应该使用values_table(account_id, model, user_id)上的索引。它还应该为相关子查询使用和索引:values_table(user_id, account_id, model, value1)是理想的。但是，您必须对输出中的每一列重复这个构造(也许还有最优索引)。如果有十个聚合列，那么对于一个聚合列来说更快的方法可能会受到影响。

如果这不起作用，那么你有更少的选择:

• 决定你真的需要一个更严格的where子句来减少数据量。
• 使用触发器在用户级别维护预聚合数据。