在mysql上用数百万行优化求和/分组查询

rekjcdws 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(870)

我有一个mysql表，有近4000.000行，其中包含超过100000名员工的收入事务。
其中有三列相关内容，分别是：
员工id[varchar and index]（由于一个员工获得多个收入，因此不唯一）；
收入类型[也包括varchar和index]
收入价值[小数点；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命令将结果限制在1000行以内，这是可行的，但是如果我想对整个表执行此操作，根据我的预测，大约需要1个小时。这似乎是太多的时间去做一些我看起来不太需要的事情（但我想我可能错了）。不仅如此，这只是我打算在将来运行的更复杂查询的第一步，在这个查询中，除了雇员id（一个人可以从多个雇主那里获得收入）之外，我还将按雇主id进行分组。
有没有办法优化这个？我的代码有什么问题吗？有没有什么秘密途径可以提高这个操作的速度？我是否也应该为收入的价值列编制索引？如果这是mysql的限制，有没有更好的方法来处理这个问题？我真的很感激任何帮助。
提前谢谢
披露：这是一个开放的政府数据库。所有这些数据都是合法向公众开放的。

sql mysql

来源：https://stackoverflow.com/questions/52049326/optimizing-summing-grouping-query-with-millions-of-rows-on-mysql

2条答案

按热度按时间

fbcarpbf1#

首先，使用 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（取决于版本）可能足够聪明，可以将其用于聚合。

赞(0）回复(0）举报 2021-06-21

idv4meu82#

根据您定义的模式，为什么要将varchar数据类型用于雇员id和类型。您可以为具有1-->x，2-->y的类型创建引用表…因此基本上整数引用将用于事务表中的类型。
只需创建一个虚拟表，并执行同样的查询，这需要一个小时。甚至你也会看到执行计划的重大变化。

CREATE TABLE test_transaction
(
Employee_ID BIGINT,
Type SMALLINT,
Income DECIMAL(10,2)
)

在employee\u id和type列上创建单独的索引。

赞(0）回复(0）举报 2021-06-20

我来回答

在mysql上用数百万行优化求和/分组查询

2条答案

相关问题

热门标签

最新问答