mysql sum函数优化

Question

问题：mysql 的sum()函数在对多列进行求和时，性能很差，数据量20w左右
amount字段的值是前面聚合好的source(preValues)中的结果，只有id上有索引

select id,
        sum(case when amount_0 > 0 then amount_0 else 0 end) as total_amount_0,
        sum(case when amount_1 > 0 then amount_1 else 0 end) as total_amount_1,
        sum(case when amount_2 > 0 then amount_2 else 0 end) as total_amount_2,
        sum(case when amount_3 > 0 then amount_3 else 0 end) as total_amount_3,
        sum(case when amount_4 > 0 then amount_4 else 0 end) as total_amount_4,
        sum(case when amount_5 > 0 then amount_5 else 0 end) as total_amount_5
from preValues
group by id

Answer 1

以下参考免费微信小程序：皆我百晓生 作答，并由阿齐Archie整理回复，若还有疑问可评论或留言。

在处理大量数据时，MySQL 的 SUM() 函数可能会因为全表扫描而变得缓慢。以下是一些优化查询性能的建议：

1. 索引优化：

   • 确保 id 字段上有索引，因为这是 GROUP BY 的依据。
   • 如果 amount_0 到 amount_5 字段经常用于条件判断，考虑在这些字段上创建索引，尤其是当这些字段的值分布不均匀时。

2. 避免使用 CASE 语句：

   • CASE 语句在聚合函数中可能会导致性能问题，因为它们需要对每一行进行计算。如果可能，尝试重写查询以避免使用 CASE 语句。

3. 使用条件聚合：

   • 你可以使用 IFNULL() 或者 COALESCE() 函数来代替 CASE 语句，这样可以减少计算量。

4. 使用 SUM() 函数的条件版本：

   • MySQL 8.0 引入了 SUM() 函数的条件版本，即 SUM(CASE WHEN ... THEN ... ELSE ... END) 可以简化为 SUM(IFNULL(CASE WHEN ... THEN ... ELSE 0 END, 0))。

5. 预计算：

   • 如果数据不经常变化，可以考虑预计算这些求和结果并将它们存储在另一个表中，这样可以避免每次查询时都进行计算。

6. 分批处理：

   • 如果数据量非常大，可以考虑将数据分批处理，每次处理一部分数据，然后将结果合并。

7. 使用缓存：

   • 如果这些求和结果被频繁查询，可以考虑将结果缓存起来，这样在下一次查询时可以直接从缓存中获取结果。

8. 查询重写：

   • 重写查询以减少不必要的计算，例如，如果 amount_0 到 amount_5 字段的值都是非负的，那么 CASE 语句就没有必要。

下面是一个优化后的查询示例，假设 amount_0 到 amount_5 字段的值都是非负的：

SELECT id,
       SUM(amount_0) AS total_amount_0,
       SUM(amount_1) AS total_amount_1,
       SUM(amount_2) AS total_amount_2,
       SUM(amount_3) AS total_amount_3,
       SUM(amount_4) AS total_amount_4,
       SUM(amount_5) AS total_amount_5
FROM preValues
GROUP BY id;

如果 amount_0 到 amount_5 字段中可能包含负值，并且你只想对正值进行求和，那么可以使用 IFNULL 或 COALESCE 来替换 CASE 语句：

SELECT id,
       SUM(IFNULL(amount_0, 0)) AS total_amount_0,
       SUM(IFNULL(amount_1, 0)) AS total_amount_1,
       SUM(IFNULL(amount_2, 0)) AS total_amount_2,
       SUM(IFNULL(amount_3, 0)) AS total_amount_3,
       SUM(IFNULL(amount_4, 0)) AS total_amount_4,
       SUM(IFNULL(amount_5, 0)) AS total_amount_5
FROM preValues
GROUP BY id;

在实际应用中，你需要根据你的具体数据库版本和数据分布来调整这些建议。如果这些优化措施仍然不能满足性能要求，可能需要考虑对数据库架构进行更深入的调整，或者使用专门的数据仓库解决方案来处理大规模数据聚合。