优化MySQL时间序列数据查询：提升多传感器起止记录性能

数据表结构 #

该表为时间序列数据表，包含sensor1至sensor1000共千个传感器列，有数百万条记录，数据量约50-70GB，示例结构如下：

需求场景 #

获取指定多个传感器（如sensor1、sensor2）的非空值对应的最早时间记录和最晚时间记录。

当前实现方式 #

为每个传感器单独编写查询语句，示例如下：

Sensor1查询语句 #

(SELECT   
    datetime, id, sensor1
FROM   
    timeseries
WHERE  
    sensor1 IS NOT NULL
ORDER BY  
    datetime ASC limit 1)

Union ALL

(SELECT   
    datetime, id, sensor1
FROM   
    timeseries
WHERE  
    sensor1 IS NOT NULL
ORDER BY  
    datetime DESC limit 1)

Sensor2查询语句 #

(SELECT   
    datetime, id, sensor2
FROM   
    timeseries
WHERE  
    sensor2 IS NOT NULL
ORDER BY  
    datetime ASC limit 1)

Union ALL

(SELECT   
    datetime, id, sensor2
FROM   
    timeseries
WHERE  
    sensor2 IS NOT NULL
ORDER BY  
    datetime DESC limit 1)

在Node/Express应用中，通过Promise.all并行执行所有查询，代码如下：

const queryResults = await Promise.all(
  queries.map(async (query) => {
    return new Promise((resolve, reject) =>
      db.query(query, [], (err, result) => {
        if (err) {
          return resolve([]); // 单个查询失败时返回空数组
        } else {
          return resolve(result);
        }
      })
    );
  })
);  

性能问题 #

当前方案在小数据量（如150条记录）Demo中运行流畅，但生产环境大数据量下，仅查询2个传感器就耗时很久，查询多个传感器时性能问题更严重。

优化方案 #

1. 合并查询，减少表扫描次数 #

将多个传感器的查询合并为单条SQL，避免多次全表扫描：

-- 获取所有目标传感器的最小、最大datetime
WITH sensor_dates AS (
    SELECT
        'sensor1' AS sensor_name,
        MIN(datetime) AS min_dt,
        MAX(datetime) AS max_dt
    FROM timeseries
    WHERE sensor1 IS NOT NULL
    UNION ALL
    SELECT
        'sensor2' AS sensor_name,
        MIN(datetime) AS min_dt,
        MAX(datetime) AS max_dt
    FROM timeseries
    WHERE sensor2 IS NOT NULL
)
-- 根据最小、最大datetime匹配对应的记录
SELECT
    sd.sensor_name,
    t.datetime,
    t.id,
    CASE sd.sensor_name
        WHEN 'sensor1' THEN t.sensor1
        WHEN 'sensor2' THEN t.sensor2
    END AS sensor_value
FROM sensor_dates sd
JOIN timeseries t ON (t.datetime = sd.min_dt OR t.datetime = sd.max_dt)
AND (
    (sd.sensor_name = 'sensor1' AND t.sensor1 IS NOT NULL)
    OR (sd.sensor_name = 'sensor2' AND t.sensor2 IS NOT NULL)
)
ORDER BY sd.sensor_name, t.datetime;

2. 添加复合索引 #

针对每个传感器列和datetime组合创建复合索引，大幅提升查询效率：

-- 为sensor1创建复合索引
CREATE INDEX idx_sensor1_datetime ON timeseries(sensor1, datetime);
-- 为sensor2创建复合索引
CREATE INDEX idx_sensor2_datetime ON timeseries(sensor2, datetime);

若需查询大量传感器，可创建包含id列的覆盖索引：

CREATE INDEX idx_sensor1_datetime_id ON timeseries(sensor1, datetime, id);

3. 控制查询并发数 #

当前Promise.all并行执行所有查询，可能导致数据库连接池耗尽或负载过高。可使用p-limit库限制并发数：

const pLimit = require('p-limit');
const limit = pLimit(5); // 限制同时执行5个查询

const queryResults = await Promise.all(
  queries.map(query => limit(async () => {
    return new Promise((resolve, reject) =>
      db.query(query, [], (err, result) => {
        if (err) {
          return resolve([]);
        } else {
          return resolve(result);
        }
      })
    );
  }))
);

4. 优化数据结构 #

若经常需查询多个传感器的时间范围，可将宽表转为长表（行转列），示例结构如下：

这种结构下，创建(Sensor_Name, Sensor_Value, Datetime, Id)复合索引，即可高效查询任意传感器的最早、最晚记录。

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