MariaDB行转列场景下批量插入多列表的实现方案

核心思路 #

弃用逐列插入的关联逻辑——也就是当前每次单条插入只填一个detector列，后续再逐次插入其他列的模式，这类模式需要对3600万行的源表反复扫描100次，还要反复做宽表的关联匹配，不仅耗时数周，还容易因为大事务冲突导致锁表。
直接按时间戳做条件聚合，一次扫描源表即可生成全部100个目标列的取值，从根本上解决单条SQL只能填充单列、其余列全为NULL的问题。

前置准备 #

• 提前按照列名规则创建目标宽表table2，字段包含：id（用varchar(36)类型存储UUID值）、timestamp（与源表时间戳类型保持一致）、100个按detector[unit值][detector值]规则命名的数值型字段，覆盖所有unit和detector的组合关系
• 给源表table1建覆盖索引，避免聚合时全表回表：

CREATE INDEX idx_table1_cover ON table1 (timestamp, unit, detector, value);

索引顺序与分组、过滤、取数逻辑完全匹配，3600万行数据的聚合扫描性能可提升10倍以上

单条批量插入SQL结构 #

不需要逐列关联，直接在分组聚合语句里把所有组合的取值用CASE WHEN计算出来，UUID值在插入时自动生成：

INSERT INTO table2 (id, timestamp, detector11, detector12, ..., detector100)
SELECT
  UUID() AS id,
  timestamp,
  -- 每个目标列对应一个unit-detector组合的条件判断
  MAX(CASE WHEN unit = 1 AND detector = 1 THEN value END) AS detector11,
  MAX(CASE WHEN unit = 1 AND detector = 2 THEN value END) AS detector12,
  -- 其余97个组合的判断逻辑依次类推
  MAX(CASE WHEN unit = 10 AND detector = 10 THEN value END) AS detector100
FROM table1
-- 大表场景下加范围过滤，分批处理，避免大事务
WHERE timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 00:00:00'
GROUP BY timestamp;

• 用MAX函数的原因：同一时间戳、同一unit和detector组合下只有唯一监测值，MAX可以自动将不符合条件的行值转为NULL，完成聚合行的压缩，不需要额外做关联匹配
• 分批处理的设计：按照时间戳范围切分数据，每批处理对应10万-50万行源表数据，单独提交事务，避免一次性插入3600万行导致的事务日志暴涨、内存溢出、锁表超时问题

100个组合的自动化生成方法 #

手动写100个CASE WHEN判断逻辑容易出错，可以直接从源表的组合数据自动拼接完整SQL：

1. 先查询所有存在的unit和detector组合：

   SELECT DISTINCT unit, detector FROM table1;
   

2. 对每个查询到的组合，自动拼接对应的CASE WHEN片段：
   MAX(CASE WHEN unit = [组合的unit值] AND detector = [组合的detector值] THEN value END) AS detector[拼接后的列名]
3. 将所有片段拼接到上述插入SQL的SELECT列表里，即可得到完整的可执行SQL，不需要手动逐行编辑

性能优化要点 #

• 宽表table2在全量数据插入完成前，不要创建额外的二级索引、外键约束，减少插入时的额外开销
• 大表转宽表过程中，临时关闭宽表的自动提交校验，每批数据插入完成后手动提交事务
• 如果存在同一时间戳下同一组合的重复值，可提前在子查询中去重：先按timestamp, unit, detector分组取唯一value，再做行转列聚合，避免重复计算

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