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_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,
    c.c_name,
    u.username