如何将MySQL聚合查询时间缩短至3秒以内？

问题背景 #

现有一张包含39列、1349827行数据的InnoDB表TRANSACTION_DETAIL，需要统计符合以下条件的1281298行数据中CREDIT_AMOUT列的总和：

SELECT SUM(CREDIT_AMOUT) AS "Total Credit Amount"
FROM TRANSACTION_DETAIL
WHERE SOURCE_ADDRESS = 'XXXXXXXX'
  AND SUBSCRIBER_TYPE = 'POST'
  AND OBJECT_TYPE = 'XXXXXXXXXXXXXXX'
  AND STATUS = 3
  AND REMOTE_TRX_CREATED_TIME BETWEEN '2011-05-01 00:00:00' AND '2012-12-31 23:59:59';

初始查询耗时约2分钟，创建组合索引total_credit_amount(CREDIT_AMOUT, SOURCE_ADDRESS, SUBSCRIBER_TYPE, OBJECT_TYPE, STATUS, REMOTE_TRX_CREATED_TIME)后，耗时缩短至25-30秒，但仍未达到3秒以内的要求。执行计划显示当前索引仅能实现覆盖扫描，但无法高效过滤数据。

优化方案 #

1. 调整组合索引的字段顺序（核心优化） #

当前索引将聚合字段CREDIT_AMOUT放在最前面，违背了索引前缀匹配原则——过滤条件字段应优先放在索引前缀，这样MySQL能快速定位到符合条件的行，最后添加聚合字段实现覆盖索引，避免回表操作。

执行以下语句重建索引：

DROP INDEX total_credit_amount ON TRANSACTION_DETAIL;
CREATE INDEX idx_total_credit ON TRANSACTION_DETAIL(
    SOURCE_ADDRESS, 
    SUBSCRIBER_TYPE, 
    OBJECT_TYPE, 
    STATUS, 
    REMOTE_TRX_CREATED_TIME, 
    CREDIT_AMOUT
);

优化后，MySQL会先通过索引前缀的过滤条件快速缩小数据范围，再直接从索引中读取CREDIT_AMOUT计算总和，预计能将耗时降低至5秒以内。

2. 按时间字段分区表 #

由于查询包含明确的时间范围过滤条件，可对表按REMOTE_TRX_CREATED_TIME进行分区，让查询仅扫描目标时间范围内的分区，减少数据扫描量。

示例按年分区：

ALTER TABLE TRANSACTION_DETAIL 
PARTITION BY RANGE (TO_DAYS(REMOTE_TRX_CREATED_TIME)) (
    PARTITION p2011 VALUES LESS THAN (TO_DAYS('2012-01-01')),
    PARTITION p2012 VALUES LESS THAN (TO_DAYS('2013-01-01')),
    PARTITION p_other VALUES LESS THAN MAXVALUE
);

配合调整后的组合索引，分区能进一步将耗时压缩至3秒以内。

3. 预计算聚合结果（高频查询首选） #

如果这类聚合查询是高频场景，且对数据实时性要求不高，可创建汇总表定期预计算聚合结果，查询时直接读取汇总数据，速度可达毫秒级。

步骤1：创建汇总表

CREATE TABLE TRANSACTION_CREDIT_SUMMARY (
    SOURCE_ADDRESS VARCHAR(120),
    SUBSCRIBER_TYPE VARCHAR(10),
    OBJECT_TYPE VARCHAR(255),
    STATUS INT(11),
    YEAR_MONTH VARCHAR(6),
    TOTAL_CREDIT DECIMAL(19,2),
    PRIMARY KEY (SOURCE_ADDRESS, SUBSCRIBER_TYPE, OBJECT_TYPE, STATUS, YEAR_MONTH)
);

步骤2：定期更新汇总数据

可通过MySQL事件或定时脚本执行更新：

INSERT INTO TRANSACTION_CREDIT_SUMMARY
SELECT 
    SOURCE_ADDRESS, 
    SUBSCRIBER_TYPE, 
    OBJECT_TYPE, 
    STATUS, 
    DATE_FORMAT(REMOTE_TRX_CREATED_TIME, '%Y%m') AS YEAR_MONTH,
    SUM(CREDIT_AMOUT) AS TOTAL_CREDIT
FROM TRANSACTION_DETAIL
GROUP BY SOURCE_ADDRESS, SUBSCRIBER_TYPE, OBJECT_TYPE, STATUS, YEAR_MONTH
ON DUPLICATE KEY UPDATE TOTAL_CREDIT = VALUES(TOTAL_CREDIT);

步骤3：从汇总表查询

SELECT SUM(TOTAL_CREDIT) AS "Total Credit Amount"
FROM TRANSACTION_CREDIT_SUMMARY
WHERE SOURCE_ADDRESS = 'XXXXXXXX'
  AND SUBSCRIBER_TYPE = 'POST'
  AND OBJECT_TYPE = 'XXXXXXXXXXXXXXX'
  AND STATUS = 3
  AND YEAR_MONTH BETWEEN '201105' AND '201212';

4. 调整MySQL内存配置 #

适当增大InnoDB缓冲池，让更多索引和数据缓存到内存，减少磁盘IO开销：
在MySQL配置文件（my.cnf/my.ini）中修改：

innodb_buffer_pool_size = 8G  # 建议设置为服务器内存的50%-70%（专用DB服务器）
innodb_read_io_threads = 16
innodb_write_io_threads = 16

修改后重启MySQL生效，能显著提升索引读取速度。

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