如何优化含GROUP BY与WHERE的SQL？附流量数据表查询案例

这个场景太常见了——处理时间范围过滤+多维度分组聚合的慢查询，尤其是数据量上去之后，单靠单字段索引根本不够用。结合你的表结构和查询需求，我整理了几个最有效的优化方向：

一、创建覆盖式复合索引，避免回表开销 #

你之前觉得sip/dip/app索引没用是对的，因为查询的过滤条件是dtime范围，单字段索引无法覆盖分组和聚合的需求。但我们可以创建一个把过滤列放在最前面、包含分组列和聚合列的复合索引，让MySQL全程在索引里完成查询，不用回表读原始数据：

CREATE INDEX idx_dtime_sip_dip_app_up_down ON data(dtime, sip, dip, app, up, down);

为什么这个索引有效： #

1. 索引首列是dtime，能快速定位到你要查询的时间范围（不管是1小时还是30天），过滤掉大部分无关数据；
2. 后面跟着sip/dip/app，刚好匹配你的GROUP BY顺序——MySQL可以利用索引的有序性直接进行分组，不需要额外排序；
3. 最后包含up/down，属于覆盖索引：查询需要的所有字段都在索引里，完全不用访问表的物理数据，这对30天这种大数据量查询的速度提升非常明显。

二、按时间分区，减少扫描范围 #

你的数据是按时间线性增长的，每月1000万条，非常适合用RANGE分区。分区后，查询指定时间范围的数据时，MySQL只会扫描对应的分区，而不是全表：

创建分区表的示例： #

DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
 `sip` varbinary(16) DEFAULT NULL,
 `dip` varbinary(16) DEFAULT NULL,
 `app` char(96) DEFAULT NULL,
 `up` bigint(20) DEFAULT NULL,
 `down` bigint(20) DEFAULT NULL,
 `dtime` datetime DEFAULT CURRENT_TIMESTAMP,
 KEY `dtime` (`dtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(dtime)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

注意事项： #

• 分区粒度建议按月份或周来设置，不要太细（比如按天）会导致分区数量过多；
• 后续要定期新增分区（比如每月初），避免新数据都落到p_future分区里；
• 分区可以和复合索引结合使用，效果更佳。

三、预聚合（汇总表），从根源减少计算量 #

如果你的业务允许一定的数据延迟（比如5分钟或1小时），预聚合绝对是提升查询速度最显著的方案——提前把原始数据按时间粒度（小时/天）聚合到汇总表，查询时直接从汇总表取数据，不用每次都扫几百万条原始记录。

具体实现步骤： #

1. 创建小时级汇总表：

CREATE TABLE `data_summary_hour` (
    `sip` varbinary(16) NOT NULL,
    `dip` varbinary(16) NOT NULL,
    `app` char(96) NOT NULL,
    `hour_time` datetime NOT NULL, -- 格式如 '2024-03-01 10:00:00'
    `sum_up` bigint(20) NOT NULL DEFAULT 0,
    `sum_down` bigint(20) NOT NULL DEFAULT 0,
    PRIMARY KEY (`hour_time`, `sip`, `dip`, `app`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 定时任务增量更新汇总表：
   用crontab或者数据库事件，每小时执行一次，把最近1小时的原始数据聚合到汇总表：

INSERT INTO data_summary_hour (sip, dip, app, hour_time, sum_up, sum_down)
SELECT sip, dip, app, DATE_FORMAT(dtime, '%Y-%m-%d %H:00:00') AS hour_time, SUM(up), SUM(down)
FROM data
WHERE dtime >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY sip, dip, app, hour_time
ON DUPLICATE KEY UPDATE sum_up = sum_up + VALUES(sum_up), sum_down = sum_down + VALUES(sum_down);

3. 查询时直接用汇总表：
   比如查最近30天的流量：

SELECT INET6_NTOA(sip), INET6_NTOA(dip), app, SUM(sum_up) AS up, SUM(sum_down) AS down
FROM data_summary_hour
WHERE hour_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY sip, dip, app;

四、其他小优化 #

• 如果你的dtime字段不需要精确到秒，可以考虑改成TIMESTAMP类型，节省存储空间，索引也会更小；
• 调整MySQL配置参数：比如增大sort_buffer_size（分组排序用）、read_buffer_size（顺序读索引用），不过这些是辅助优化，优先级低于前面的方案。

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