大表多次UNION查询的高效子集化方案咨询

针对你遇到的宽表转长表、多次UNION导致查询效率极低的问题，结合千万级数据+每分钟数十次请求的实时API场景，我给你几个更优的实现思路，性能能提升一大截：

1. 单次扫描+横向展开（最优实时方案） #

这种方案只需要从大表中读取目标ID的一行数据，然后通过关联小数据集的方式展开成多行，完全避免了多次扫描大表的开销。不同数据库的语法略有差异，但核心逻辑一致：

通用SQL示例（适配多数数据库） #

SELECT 
    m.measure,
    -- 匹配对应的actual字段
    CASE m.measure
        WHEN 1 THEN tb.measure_1_actual
        WHEN 2 THEN tb.measure_2_actual
        WHEN 3 THEN tb.measure_3_actual
        WHEN 4 THEN tb.measure_4_actual
        WHEN 5 THEN tb.measure_5_actual
    END AS actual,
    -- 匹配对应的predicted字段
    CASE m.measure
        WHEN 1 THEN tb.measure_1_predicted
        WHEN 2 THEN tb.measure_2_predicted
        WHEN 3 THEN tb.measure_3_predicted
        WHEN 4 THEN tb.measure_4_predicted
        WHEN 5 THEN tb.measure_5_predicted
    END AS predicted
FROM tb
-- 关联一个包含1-5的小数据集，用来生成measure编号
CROSS JOIN (
    VALUES (1), (2), (3), (4), (5)
) AS m(measure)
WHERE tb.id = ? -- 替换为请求的目标ID

为什么高效？

• 大表只被扫描一次：通过WHERE id=?快速定位单行（前提是id是主键或有唯一索引），不会像UNION那样每次子查询都扫一遍大表。
• 计算开销极低：只是简单的CASE匹配和关联小数据集，数据库能快速完成。

数据库专属优化版 #

• PostgreSQL/MySQL 8.0+：可以用LATERAL JOIN替代CROSS JOIN，逻辑更清晰：

  SELECT 
      m.measure,
      m.actual,
      m.predicted
  FROM tb
  LATERAL (
      VALUES 
          (1, tb.measure_1_actual, tb.measure_1_predicted),
          (2, tb.measure_2_actual, tb.measure_2_predicted),
          (3, tb.measure_3_actual, tb.measure_3_predicted),
          (4, tb.measure_4_actual, tb.measure_4_predicted),
          (5, tb.measure_5_actual, tb.measure_5_predicted)
  ) AS m(measure, actual, predicted)
  WHERE tb.id = ?
  

• SQL Server：用CROSS APPLY，和上面的LATERAL逻辑一致：

  SELECT 
      m.measure,
      m.actual,
      m.predicted
  FROM tb
  CROSS APPLY (
      VALUES 
          (1, tb.measure_1_actual, tb.measure_1_predicted),
          (2, tb.measure_2_actual, tb.measure_2_predicted),
          (3, tb.measure_3_actual, tb.measure_3_predicted),
          (4, tb.measure_4_actual, tb.measure_4_predicted),
          (5, tb.measure_5_actual, tb.measure_5_predicted)
  ) AS m(measure, actual, predicted)
  WHERE tb.id = ?
  

2. 预生成窄表（适合允许轻微延迟的场景） #

如果你的实时API对延迟要求不是绝对的毫秒级（比如允许1-5分钟的延迟），可以通过ETL定时任务把宽表转换成窄表结构，后续查询直接查窄表，性能是最高的：

窄表结构 #

操作步骤 #

1. 创建窄表，给(id, measure)建复合主键或唯一索引。
2. 定时运行同步任务（比如每分钟一次），把宽表的新数据同步到窄表（用INSERT ON DUPLICATE KEY UPDATE或者MERGE语句）。
3. API查询时直接执行：

   SELECT measure, actual, predicted FROM narrow_tb WHERE id = ?
   

这种方案的查询速度几乎是毫秒级的，因为直接命中索引，非常适合高并发场景。

3. 用数据库内置的Unpivot函数 #

很多数据库提供了专门的宽表转长表的函数，语法更简洁，性能和第一种方案差不多：

示例 #

• SQL Server：

  SELECT 
      measure,
      actual,
      predicted
  FROM (
      SELECT 
          id,
          measure_1_actual, measure_1_predicted,
          measure_2_actual, measure_2_predicted,
          measure_3_actual, measure_3_predicted,
          measure_4_actual, measure_4_predicted,
          measure_5_actual, measure_5_predicted
      FROM tb
      WHERE id = ?
  ) AS src
  UNPIVOT (
      actual FOR measure_actual IN (measure_1_actual, measure_2_actual, measure_3_actual, measure_4_actual, measure_5_actual)
  ) AS up_actual
  UNPIVOT (
      predicted FOR measure_predicted IN (measure_1_predicted, measure_2_predicted, measure_3_predicted, measure_4_predicted, measure_5_predicted)
  ) AS up_predicted
  WHERE 
      SUBSTRING(measure_actual, 9, 1) = SUBSTRING(measure_predicted, 11, 1) -- 匹配measure编号
  

• PostgreSQL：

  SELECT
      unnest(array[1,2,3,4,5]) AS measure,
      unnest(array[measure_1_actual, measure_2_actual, measure_3_actual, measure_4_actual, measure_5_actual]) AS actual,
      unnest(array[measure_1_predicted, measure_2_predicted, measure_3_predicted, measure_4_predicted, measure_5_predicted]) AS predicted
  FROM tb
  WHERE id = ?
  

关键优化点 #

• 确保原表的id字段是主键或有唯一索引：这样WHERE id=?能直接定位到单行，避免全表扫描。
• 优化API连接池：实时高并发场景下，复用数据库连接能减少连接开销。
• 避免临时表/视图：你之前考虑的临时表方案会增加数据库的资源开销，高并发下反而可能拖慢性能，不推荐。

内容的提问来源于stack exchange，提问作者boot-scootin