时间戳

首页 > 解决方案 > 在 SQL 子查询上使用 SUM() 时如何提高速度？

mysql - 在 SQL 子查询上使用 SUM() 时如何提高速度？

问题描述

我有一个非常基本的大型事务数据库（1.5m 事务）。

我想计算过去 30 天的每日总数。我首先选择过去 30 天的交易，这很快，然后我尝试执行 SUM()，这非常慢。

ts是事务的时间戳，is_complete是 0 或 1 标志

SELECT DATE(ts) as date, ts, is_complete, cash_in, cash_out 
FROM transactions t
 WHERE ts > DATE_SUB(NOW(), INTERVAL 30 DAY)

这运行速度很快（36 毫秒）并返回约 56k 事务。

现在我尝试对总数进行分组和求和。

SELECT date, SUM(complete)
FROM (SELECT DATE(ts) as date, ts, is_complete, cash_in, cash_out
      FROM transactions t
      WHERE ts > DATE_SUB(NOW(), INTERVAL 30 DAY) 
     ) a
GROUP BY date

这需要 42 秒。为什么从 36 毫秒到 42 秒有如此大的增长？

然后，当我开始向 SUM() 添加字段时，它变得越来越慢。

SELECT date, SUM(complete), SUM(cash_in + cash_out)
FROM (SELECT DATE(ts) as date, ts, is_complete, cash_in, cash_out
      FROM transactions t 
      WHERE ts > DATE_SUB(NOW(),INTERVAL 30 DAY) 
     ) a
GROUP BY date

这需要 140 秒。

在 SQL 查询之后，我应该停止尝试使用 MySQL SUM() 并在我的本地代码中执行 SUM 吗？或者我可以通过适当的索引来提高速度吗？

我注意到这个查询的速度随着表的大小而增长，即使子查询（过去 30 天）的大小总是相似的。过去 30 天总是大约 50k 事务，正在求和，但是当全表是 150 万事务而不是 10 万事务时，查询花费的时间要长得多。当我只对子查询的结果求和时，我不太明白为什么完整的表大小会有所不同。

标签： mysqlsql

解决方案

没有子查询的查询需要多长时间？

SELECT DATE(ts) as date, SUM(complete)
FROM transactions t
WHERE ts > DATE_SUB(NOW(), INTERVAL 30 DAY) 
GROUP BY date;

一种可能性是 36 ms 的测量是测量到第一行的时间而不是所有行的时间。您是说返回了 56k 行——而且 36 毫秒似乎很快就可以返回这么多行。

另一方面，当您执行 a 时GROUP BY，必须返回所有数据。然后开始聚合处理。

现在，这就解释了为什么聚合比没有聚合的查询要慢得多。添加SUM()虽然不会显着影响处理时间。时间通常以移动数据为主，而不是累加数字。也许服务器上正在发生其他事情或锁定表。

推荐阅读