如何加速MySQL中大数据集SUM()结合GROUP BY的查询性能？

针对你120万行数据的分组汇总查询慢的问题，以下是几个直接可行的优化手段：

1. 创建覆盖索引 #

当前仅为position建索引，查询时需要回表读取balance字段，这会带来大量IO开销。创建包含position和balance的联合覆盖索引，让MySQL直接从索引完成聚合计算，无需回表：

CREATE INDEX idx_pos_balance ON your_table(position, balance);

执行EXPLAIN SELECT SUM(balance), position from your_table group by position;查看执行计划，若Extra列显示Using index，说明索引生效，能大幅降低查询耗时。

2. 改用预聚合汇总表 #

因为业务需要频繁全量汇总，每次扫描全表效率极低，建议构建一张实时/定时更新的汇总表：

• 首先创建汇总表：

CREATE TABLE balance_summary (
    position VARCHAR(5) PRIMARY KEY,
    total_balance FLOAT
);

• 初始化数据：

INSERT INTO balance_summary (position, total_balance)
SELECT position, SUM(balance) FROM your_table GROUP BY position;

• 同步更新方式：
  • 若数据更新频率不高，用定时任务（如crontab）定期执行以下语句刷新数据：

    INSERT INTO balance_summary (position, total_balance)
    SELECT position, SUM(balance) FROM your_table GROUP BY position
    ON DUPLICATE KEY UPDATE total_balance = VALUES(total_balance);
    

  • 若需要实时同步，给原表添加INSERT/UPDATE/DELETE触发器，每次数据变更时同步更新汇总表：

    DELIMITER //
    CREATE TRIGGER trg_update_summary_after_insert AFTER INSERT ON your_table
    FOR EACH ROW
    BEGIN
        INSERT INTO balance_summary (position, total_balance)
        VALUES (NEW.position, NEW.balance)
        ON DUPLICATE KEY UPDATE total_balance = total_balance + NEW.balance;
    END //
    
    CREATE TRIGGER trg_update_summary_after_update AFTER UPDATE ON your_table
    FOR EACH ROW
    BEGIN
        UPDATE balance_summary 
        SET total_balance = total_balance - OLD.balance + NEW.balance
        WHERE position = OLD.position;
    END //
    
    CREATE TRIGGER trg_update_summary_after_delete AFTER DELETE ON your_table
    FOR EACH ROW
    BEGIN
        UPDATE balance_summary 
        SET total_balance = total_balance - OLD.balance
        WHERE position = OLD.position;
    END //
    DELIMITER ;
    

之后查询直接从汇总表取数，耗时可降至毫秒级：

SELECT total_balance, position FROM balance_summary;

3. 优化数据类型 #

• position是VARCHAR(5)，若其取值是固定的枚举值（如部门编码、岗位编码），可改为ENUM或INT类型：
  • 比如改为INT：先建立映射表，将字符串position转为整数ID，原表存储INT类型的position_id，索引体积会更小，分组计算更快；
• balance用FLOAT可能存在精度丢失问题，若业务对精度有要求，建议改为DECIMAL(18,2)，虽然对性能影响不大，但能保证数据准确性。

4. 调整MySQL配置参数 #

针对分组排序的开销，可适当调整以下参数（需根据服务器内存情况调整，避免内存溢出）：

• sort_buffer_size：增大分组排序的缓冲区，比如设置为64M（默认可能只有256K）；
• read_rnd_buffer_size：优化随机读取的缓冲区大小，可设置为16M；
  修改后需重启MySQL生效，或用SET GLOBAL临时生效（重启后失效）。

内容的提问来源于stack exchange，提问作者Kinimod