优化MySQL海量时间序列数据多传感器起止时间查询性能

问题背景 #

现有MySQL时间序列表，包含Id、Object_Id、Datetime字段，以及Sensor1至Sensor1000共1000个传感器字段，数据量达数百万条（50-70GB）。需求为获取指定时间范围内多个传感器的最早（最小Datetime）、最晚（最大Datetime）记录。

当前方案为遍历传感器数组，为每个传感器构造Union ALL查询并异步执行最多35个查询，性能极差。

现有方案的核心问题 #

• 重复表扫描：每个传感器的两次查询（取最早、最晚记录）都会扫描符合时间范围的数据集，35个传感器对应70次表扫描，严重浪费IO资源。
• 连接开销：多查询并发带来的数据库连接建立、网络交互开销累积后，成为性能瓶颈。

优化方案 #

方案一：单查询批量获取所有目标传感器的极值记录 #

核心思路是一次性定位时间范围内的最早、最晚时间点，再关联取出所有目标传感器的对应记录，避免多次扫描表。

示例SQL：

WITH time_bounds AS (
    SELECT 
        MIN(datetime) AS min_dt,
        MAX(datetime) AS max_dt
    FROM timeseries
    WHERE datetime BETWEEN '2021-01-20 22:01:00' AND '2024-01-20 00:17:00'
)
SELECT 
    t.datetime,
    t.id,
    t.sensor1,
    t.sensor2,
    -- 按需列出所有需要查询的传感器字段，比如sensor3到sensor35
    t.sensor35
FROM timeseries t
JOIN time_bounds tb ON t.datetime IN (tb.min_dt, tb.max_dt)
WHERE t.datetime BETWEEN '2021-01-20 22:01:00' AND '2024-01-20 00:17:00';

说明：

• 仅需扫描表两次（一次找时间极值，一次取对应记录），无论查询多少传感器，都只执行一次数据库请求。
• 若同一时间点存在多条记录，可根据业务需求添加DISTINCT或按Id筛选。

方案二：添加覆盖索引加速查询 #

针对Datetime过滤条件，创建覆盖索引，让数据库无需回表即可获取所有查询字段：

-- 针对本次查询的传感器字段创建覆盖索引
CREATE INDEX idx_timeseries_datetime_covering ON timeseries (datetime) 
INCLUDE (id, sensor1, sensor2, ..., sensor35);

如果业务中存在按Object_Id分组查询的场景，可创建联合覆盖索引：

CREATE INDEX idx_timeseries_obj_datetime_covering ON timeseries (Object_Id, datetime) 
INCLUDE (id, sensor1, ..., sensor35);

说明：覆盖索引直接包含查询所需的所有字段，能将查询速度提升数倍，是见效最快的优化手段。

方案三：重构表结构（长期根本性优化） #

当前宽表结构（1000个传感器字段）不适用于时间序列数据的高频查询场景，建议重构为窄表结构：

CREATE TABLE timeseries_normalized (
    id INT AUTO_INCREMENT PRIMARY KEY,
    object_id INT NOT NULL,
    datetime DATETIME NOT NULL,
    sensor_name VARCHAR(20) NOT NULL,
    sensor_value DECIMAL(10,2) NOT NULL, -- 根据实际数据类型调整
    INDEX idx_obj_sensor_datetime (object_id, sensor_name, datetime)
);

查询指定传感器极值的SQL示例：

SELECT 
    sensor_name,
    MIN(datetime) AS earliest_datetime,
    (SELECT id FROM timeseries_normalized tn WHERE tn.sensor_name = t.sensor_name AND tn.datetime = MIN(t.datetime)) AS earliest_id,
    (SELECT sensor_value FROM timeseries_normalized tn WHERE tn.sensor_name = t.sensor_name AND tn.datetime = MIN(t.datetime)) AS earliest_value,
    MAX(datetime) AS latest_datetime,
    (SELECT id FROM timeseries_normalized tn WHERE tn.sensor_name = t.sensor_name AND tn.datetime = MAX(t.datetime)) AS latest_id,
    (SELECT sensor_value FROM timeseries_normalized tn WHERE tn.sensor_name = t.sensor_name AND tn.datetime = MAX(t.datetime)) AS latest_value
FROM timeseries_normalized t
WHERE 
    datetime BETWEEN '2021-01-20 22:01:00' AND '2024-01-20 00:17:00'
    AND sensor_name IN ('sensor1', 'sensor2', ..., 'sensor35')
GROUP BY sensor_name;

说明：窄表结构更符合时间序列数据的存储规范，索引效率更高，能精准定位指定传感器的时间范围数据，避免扫描无关字段。

代码层面优化 #

将多查询并发改为单查询调用，简化代码并降低数据库连接压力：

const targetSensors = ['sensor1', 'sensor2', ..., 'sensor35']; // 目标传感器数组
const sensorFields = targetSensors.join(', ');
const query = `
WITH time_bounds AS (
    SELECT 
        MIN(datetime) AS min_dt,
        MAX(datetime) AS max_dt
    FROM timeseries
    WHERE datetime BETWEEN ? AND ?
)
SELECT 
    t.datetime,
    t.id,
    ${sensorFields}
FROM timeseries t
JOIN time_bounds tb ON t.datetime IN (tb.min_dt, tb.max_dt)
WHERE t.datetime BETWEEN ? AND ?;
`;

try {
    const result = await db.query(query, [
        '2021-01-20 22:01:00', 
        '2024-01-20 00:17:00', 
        '2021-01-20 22:01:00', 
        '2024-01-20 00:17:00'
    ]);
    // 按需处理结果，比如按传感器分组整理最早、最晚记录
} catch (err) {
    console.error('查询失败:', err);
}

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