MariaDB多sKey时间分段平均值查询性能优化咨询

我有一个结构如下的MariaDB数据库（实际包含30余种不同sKey）：

+----+------+------+---------------------+
|ID  | sKey | sVal |      timestamp      |
+----+------+------+---------------------+
| 1  | temp |   19 | 2023-07-14 20:32:06 |
| 2  | humi |   60 | 2023-07-14 20:33:06 |
| 3  | temp |   20 | 2023-07-14 20:34:06 |
| 4  | humi |   65 | 2023-07-14 20:35:06 |
| 5  | pres | 1023 | 2023-07-14 20:36:06 |
| 6  | temp |   22 | 2023-07-14 20:37:06 |
| 7  | temp |   21 | 2023-07-14 20:38:06 |
| 8  | pres | 1028 | 2023-07-14 20:39:06 |
| 9  | temp |   20 | 2023-07-14 20:40:06 |
|10  | temp |   19 | 2023-07-14 20:43:06 |  <-时间跳变
|11  | pres | 1022 | 2023-07-14 20:44:06 |
|12  | temp |   19 | 2023-07-14 20:45:06 |
|13  | humi |   66 | 2023-07-14 20:46:06 |
|14  | humi |   63 | 2023-07-14 20:47:06 |
|15  | temp |   19 | 2023-07-14 20:48:06 |
|16  | pres | 1029 | 2023-07-14 20:49:06 |
|20  | temp |   19 | 2023-07-14 20:50:06 | <-ID不连续（有记录被删除）
|21  | pres | 1022 | 2023-07-14 20:61:06 |
|22  | temp |   19 | 2023-07-14 20:62:06 |
|23  | pres | 1029 | 2023-07-14 20:63:06 |
+----+------+------+---------------------+

需求是在指定时间区间内，以固定时长（示例为3分钟）为分段，计算每个sKey对应的sVal平均值。目前用3条独立SQL查询实现：

SELECT AVG(`sVal`), `timestamp` FROM `Test` WHERE sKey='temp'  AND timestamp between '2023-07-14 20:34:06' and '2023-07-14 20:51:06' GROUP BY FLOOR(TO_SECONDS(`timestamp`)/180)
SELECT AVG(`sVal`), `timestamp` FROM `Test` WHERE sKey='humi'  AND timestamp between '2023-07-14 20:34:06' and '2023-07-14 20:51:06' GROUP BY FLOOR(TO_SECONDS(`timestamp`)/180)
SELECT AVG(`sVal`), `timestamp` FROM `Test` WHERE sKey='pres'  AND timestamp between '2023-07-14 20:34:06' and '2023-07-14 20:51:06' GROUP BY FLOOR(TO_SECONDS(`timestamp`)/180)

但数据库已有超过200万条记录，单条查询耗时约3秒，性能不足。希望将查询合并为单条或通过其他方案优化性能，同时确认基于timestamp进行分段分组是否为正确的实现思路（因存在记录删除导致ID不连续、时间跳变的情况，无法按ID分组）。

1. 时间分组的正确性确认 #

基于timestamp分段分组是完全正确的选择——ID因记录删除不连续、时间存在跳变，无法反映时间维度的分段逻辑，只有时间字段能准确划分固定时长的区间。

2. 合并查询与性能优化 #

（1）合并为单条查询 #

可以同时按sKey和时间分组区间聚合，一次查询获取所有sKey的统计结果：

SELECT 
    `sKey`,
    AVG(`sVal`) AS avg_sVal,
    -- 生成区间起始时间，让结果更直观
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`timestamp`)/180)*180) AS interval_start
FROM `Test` 
WHERE 
    `timestamp` BETWEEN '2023-07-14 20:34:06' AND '2023-07-14 20:51:06'
    -- 若仅需特定sKey，可保留此条件，否则删除
    -- AND `sKey` IN ('temp', 'humi', 'pres')
GROUP BY 
    `sKey`,
    FLOOR(UNIX_TIMESTAMP(`timestamp`)/180)
ORDER BY 
    interval_start,
    `sKey`;

说明：用UNIX_TIMESTAMP替代TO_SECONDS性能更优，且结果一致；生成interval_start是为了明确每个分组对应的时间区间，避免返回随机的timestamp值。

（2）添加复合索引 #

200万条记录的性能瓶颈大概率是缺少合适索引，建议创建覆盖查询条件和聚合字段的复合索引：

CREATE INDEX idx_test_skey_timestamp_sval ON `Test` (`sKey`, `timestamp`, `sVal`);

该索引可实现覆盖索引扫描，避免回表查询，大幅降低查询耗时。

（3）其他优化建议 #

• 若查询时间区间固定且频繁，可做预聚合：定期将统计结果存入单独的统计表（如按3分钟粒度预计算各sKey的平均值），查询时直接读取预聚合表，性能会显著提升。
• 若使用MariaDB 10.5+，可用更简洁的DATE_TRUNC语法实现时间分组：

  SELECT 
      `sKey`,
      AVG(`sVal`) AS avg_sVal,
      DATE_TRUNC('MINUTE', `timestamp`, 3) AS interval_start
  FROM `Test` 
  WHERE `timestamp` BETWEEN '2023-07-14 20:34:06' AND '2023-07-14 20:51:06'
  GROUP BY `sKey`, DATE_TRUNC('MINUTE', `timestamp`, 3)
  ORDER BY interval_start, `sKey`;
  

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