mysql - 优化 MYSQL 上数百万行的求和/分组查询

Question

我有一个 MySQL 表，其中包含近 4,000,000 行，其中包含超过 100,000 名员工的收入交易。

其中有三列相关，它们是:

• 员工 ID [VARCHAR 和 INDEX](不是唯一的，因为一名员工可以获得不止一份收入)；
• 收入类型 [还有 VARCHAR 和 INDEX]
• 收入值(value) [十进制； 10,2]

我想做的事情对我来说似乎很简单。我想对每个员工分组的所有收入事件进行汇总，并按一种类型进行过滤。为此，我使用了以下代码:

SELECT 
    SUM(`value`) AS `SumofValue`,
    `type`,
    `EmployeeID`
FROM
    `Revenue`
GROUP BY `EmployeeID`
HAVING `type` = 'X'

结果应该是这样的:

  SUM        TYPE     EMPLOYEE ID
 R$ 250,00  X   250000008377
 R$ 5.000,00    X   250000004321
 R$ 3.200,00    X   250000005432
 R$ 1.600,00    X   250000008765
....

但是，这需要很长时间。我决定使用 LIMIT 命令将结果限制为 1.000 行，并且它正在工作，但如果我想对整个表执行此操作，根据我的预测，大约需要 1 小时。对于我来说看起来不太需要的事情来说，这似乎是太多的时间(但我假设我可能是错的)。不仅如此，这只是我打算在将来运行的更复杂查询的第一步，在该查询中，除了员工 ID 之外，我还将按雇主 ID 进行分组(一个人可以从多个人那里获得收入)雇主)。

有什么办法可以优化吗？我的代码有什么问题吗？有什么 secret 途径可以提高这个操作的速度吗？我是否也应该对收入值列建立索引？如果这是 MySQL 的限制，是否有任何选项可以更好地处理这个问题？我真的很感激任何帮助。

提前致谢

披露:这是一个开放的政府数据库。所有这些数据均依法向公众开放。

Answer 1

首先，使用 WHERE 来表达查询，而不是 HAVING -- 在进行聚合之前进行过滤:

SELECT SUM(`value`) AS `SumofValue`,
       MAX(type) as type,
       EmployeeID
FROM Revenue r
WHERE `type` = 'X'
GROUP BY EmployeeID;

接下来，尝试使用此索引:(type, EmployeeId, value)。至少，这是查询的覆盖索引。 MySQL(取决于版本)可能足够聪明，也可以将其用于聚合。