mysql sum多个字段千万级数据如何查询优化？

Question

统计数据表中多个sum千万级数据超时。由于业务需要实时 所以做不来快照表

我加了索引似乎也不管用

SELECT    f.*,    c.c_name,    u.username,    sum( f.dy_sc_num ) AS dy_sc_num_total,    sum( f.dy_gz_num ) AS dy_gz_num_total,    sum( f.dy_like_num ) AS dy_like_num_total,    sum( f.ks_gz_num ) AS ks_gz_num_total,    sum( f.ks_dz_num ) AS ks_dz_num_total,    sum( f.tt_gz_num ) AS tt_gz_num_total FROM    `f_like` AS f    LEFT JOIN f_company AS c ON c.id = f.company_id    LEFT JOIN system_user AS u ON u.id = f.user_id    where f.create_time >= '2024-02-27 00:00:00'    and f.create_time <= '2024-02-27 23:59:59'    GROUP BY f.user_id,f.device_id    ;

后来为了不联表 我直接把快照写入进去了

SELECT    f.*,        sum( f.dy_sc_num ) AS dy_sc_num_total,    sum( f.dy_gz_num ) AS dy_gz_num_total,    sum( f.dy_like_num ) AS dy_like_num_total,    sum( f.ks_gz_num ) AS ks_gz_num_total,    sum( f.ks_dz_num ) AS ks_dz_num_total,    sum( f.tt_gz_num ) AS tt_gz_num_total FROM    `f_like` AS f    where f.create_time >= '2024-02-27 00:00:00'    and f.create_time <= '2024-02-27 23:59:59'    GROUP BY f.user_id,f.device_id    ;

Answer 1

索引的存在是为了跳过不需要的记录，减少记录扫描数，所以对于任何全表操作都是无效的。因为既然你的 SQL 需要每条记录都参与计算，那么索引就没有意义。

如果你需要频繁的更新 sum，同时数据量极大，那么每次都扫描全部记录肯定是不现实的，就算你做了读写分离，只读库的负载依旧会非常高。所以优化思路有两个：

1. 控制 SQL 的执行频率，让一个线程定时执行，并将结果放入缓存。好处是保持设计不过于复杂，缺点是在某种程度上牺牲了实时性；
2. 将计算方式改为增量，例如把 sum 值放在缓存里，设计专门的逻辑来对其增量更新。好处是能做到实时，缺点是增加了复杂性。

另外，为什么你要一次查全部用户呢？如果一次只查询一个用户，那么就不会有这个问题。

Answer 2

create_time 这个字段加一下索引

SELECT    f.user_id,    f.device_id,    c.c_name,    u.username,    SUM(f.dy_sc_num) AS dy_sc_num_total,    SUM(f.dy_gz_num) AS dy_gz_num_total,    SUM(f.dy_like_num) AS dy_like_num_total,    SUM(f.ks_gz_num) AS ks_gz_num_total,    SUM(f.ks_dz_num) AS ks_dz_num_total,    SUM(f.tt_gz_num) AS tt_gz_num_totalFROM    f_like AS f    LEFT JOIN f_company AS c ON c.id = f.company_id    LEFT JOIN system_user AS u ON u.id = f.user_idWHERE    f.create_time >= '2024-02-27 00:00:00'    AND f.create_time <= '2024-02-27 23:59:59'GROUP BY    f.user_id,    f.device_id,    c.c_name,    u.username