MySQL多分辨率数据查询优化：替代UNION的高效方案问询

嘿，这个场景我太熟了——用UNION凑结果确实能搞定，但当数据量上来之后，两次全表扫描的开销会越来越明显，完全有更高效的实现方式，核心思路就是把两次查询的逻辑合并成一次，避免重复扫描数据集。下面分不同数据库场景给你具体方案：

1. 时序数据库的原生最优解 #

如果你用的是InfluxDB、TimescaleDB、Prometheus这类专门的时序数据库，它们天生就为这类时间聚合场景做了优化，根本不需要用UNION：

• 比如InfluxDB中，你可以直接按分钟分组，同时根据每分钟的数据点数动态选择聚合方式（比如数据多就取平均，数据少就保留最后一个点）：

SELECT
  time,
  CASE
    WHEN count(*) > 10 THEN mean(value)  -- 每分钟数据点超10个就取平均值
    ELSE last(value)                     -- 否则取最新的那个点
  END AS value
FROM measurement
WHERE time >= now() - 1h
GROUP BY time(1m)

• TimescaleDB的话，用time_bucket()函数配合条件聚合，一次扫描就能完成：

SELECT
  time_bucket('1 minute', timestamp) AS bucket,
  CASE
    WHEN COUNT(*) > 5 THEN AVG(value)
    ELSE MAX(value)
  END AS aggregated_value
FROM sensor_data
WHERE timestamp >= NOW() - INTERVAL '2 hours'
GROUP BY bucket
ORDER BY bucket;

2. 关系型数据库的窗口函数方案 #

如果是PostgreSQL、MySQL 8.0+这类支持窗口函数的关系型数据库，可以用CTE（公共表表达式）先统计每分钟的数据量，再通过条件逻辑生成对应分辨率的数据：
举个PostgreSQL的例子：

WITH minute_groups AS (
  SELECT
    date_trunc('minute', created_at) AS minute_bucket,
    value,
    -- 统计当前分钟内的总数据点数
    COUNT(*) OVER (PARTITION BY date_trunc('minute', created_at)) AS points_per_minute
  FROM your_table
  WHERE created_at >= NOW() - INTERVAL '1 hour'
)
SELECT
  minute_bucket,
  -- 根据点数选择聚合方式，同时确保每个分钟只返回一个点
  CASE
    WHEN points_per_minute > 8 THEN AVG(value) OVER (PARTITION BY minute_bucket)
    ELSE value
  END AS result_value
FROM minute_groups
DISTINCT ON (minute_bucket)  -- 每个分钟只留一条结果
ORDER BY minute_bucket, created_at DESC;

这种方式只需要扫描一次表，比两次查询+UNION的IO开销小得多，数据量越大优势越明显。

3. 额外的性能优化小技巧 #

• 一定要给时间列加索引：不管用哪种方案，时间索引都是提升查询速度的核心，没有索引的话，任何优化都是白搭。
• 避免UNION的去重开销：如果你的两条查询结果没有重复数据，可以用UNION ALL代替UNION，省去去重的计算步骤。
• 尽量缩小查询范围：通过WHERE子句限定时间区间，不要扫描全表。

总的来说，核心就是用一次扫描完成分组、统计和条件聚合，代替两次独立查询的合并，这样能大幅降低数据库的计算和IO压力，尤其是在数据量较大的场景下效果非常明显。

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