MySQL时序数据转指定JSON格式的性能与内存问题求助

一、Node端：用流式处理替代全量加载 #

• 不要一次性把百万级查询结果加载到内存，改用MySQL驱动的流式查询API（比如mysql2的stream），边读取数据边转换JSON，直接写入输出流（如HTTP响应），彻底避免堆内存溢出。

  const mysql = require('mysql2/promise');
  
  async function streamQuery() {
    const conn = await mysql.createConnection({ /* 数据库配置 */ });
    // 开启流式查询
    const stream = conn.query(`
      SELECT Datetime, sensor1, sensor2, ..., sensor40 
      FROM your_table 
      WHERE Object_Id = ? AND Datetime BETWEEN ? AND ?
    `, [1, '2023-01-01', '2023-06-30']).stream();
  
    // 直接向响应流写入JSON结构，无需缓存所有数据
    process.stdout.write('{"series": [');
    let firstRow = true;
    stream.on('data', (row) => {
      if (!firstRow) process.stdout.write(',');
      firstRow = false;
      // 逐行转换为目标JSON格式
      const item = JSON.stringify({
        datetime: row.Datetime,
        sensors: {
          sensor1: row.sensor1,
          sensor2: row.sensor2,
          // ... 其他传感器
        }
      });
      process.stdout.write(item);
    });
    stream.on('end', () => {
      process.stdout.write(']}');
      conn.end();
    });
  }
  

• 临时调整Node内存上限：启动服务时添加--max-old-space-size=8192（根据服务器内存设置，如8G），但这只是临时缓解，流式处理才是根本解决办法。

二、MySQL端：重构查询逻辑，避免大JSON聚合 #

1. 分批次查询+客户端拼接 #

将大时间范围拆分为多个小批次（比如按月份拆分6个月为6次查询），每次返回部分数据，在Node端逐批次拼接最终JSON，避免单次生成超大JSON对象。
示例SQL（按月拆分）：

SELECT Datetime, sensor1, sensor2,... 
FROM your_table 
WHERE Object_Id = ? 
AND Datetime BETWEEN '2023-01-01' AND '2023-02-01'

2. 转置表结构（长期最优方案） #

当前宽表（sensor1-sensor1000）不适合时序数据批量查询，建议转成窄表结构：

• 原表：id, object_id, datetime, sensor1, sensor2,...sensor1000
• 新表：id, object_id, datetime, sensor_code, sensor_value

转置后优势：

• 查询N个传感器时，用WHERE sensor_code IN ('sensor1','sensor2',...)配合object_id+datetime+sensor_code联合索引，查询效率大幅提升。
• 聚合JSON时，每个传感器单独聚合，不会触发JSON大小限制：

  SELECT 
    sensor_code,
    JSON_ARRAYAGG(JSON_OBJECT('datetime', datetime, 'value', sensor_value)) AS data
  FROM new_time_series
  WHERE object_id = ? 
    AND datetime BETWEEN ? AND ?
    AND sensor_code IN ('sensor1','sensor2',...)
  GROUP BY sensor_code
  

• 数据迁移可以用批量INSERT转换：

  INSERT INTO new_time_series (object_id, datetime, sensor_code, sensor_value)
  SELECT object_id, datetime, 'sensor1', sensor1 FROM old_table
  UNION ALL
  SELECT object_id, datetime, 'sensor2', sensor2 FROM old_table
  -- 循环处理所有传感器列，或用存储过程批量生成语句
  

3. 临时调整MySQL JSON大小限制 #

如果必须保留原表结构，修改max_json_length参数（默认1G），在my.cnf中设置：

max_json_length = 4294967296 # 4G

重启MySQL后生效，但这只是缓解，大聚合仍会因内存计算导致查询缓慢。

三、混合方案：预聚合+实时查询结合 #

对于高频使用的传感器组合和时间范围，用定时任务（如crontab）提前将聚合结果存储到预聚合表中，查询时直接读取结果，避免实时计算开销。

• 预聚合表结构：object_id, time_range, sensor_codes, aggregated_json
• 定时任务示例SQL：

  INSERT INTO pre_aggregated_data (object_id, time_range, sensor_codes, aggregated_json)
  SELECT 
    object_id,
    '2023-01-01至2023-06-30' AS time_range,
    'sensor1,sensor2,...sensor40' AS sensor_codes,
    JSON_ARRAYAGG(JSON_OBJECT('datetime', datetime, 'sensor1', sensor1, ...)) AS aggregated_json
  FROM your_table
  WHERE Object_Id = ? AND Datetime BETWEEN '2023-01-01' AND '2023-06-30'
  GROUP BY object_id
  ON DUPLICATE KEY UPDATE aggregated_json = VALUES(aggregated_json)
  

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