千万级日增量时序数据：TimescaleDB 性能调优实战指南

        摘要： 本文详细记录了在电力物联网场景下，面对单表 2000 万+ 数据、日增量 1000 万行的挑战。我们将查询性能从 MySQL 的 30秒+ 优化至 TimescaleDB 的 1.4秒，实现了 20 倍以上的性能飞跃。
    

30s+ MySQL 查询耗时

5s Timescale (未压缩)

1.4s Timescale (优化后)

1. 场景与挑战

我们面临的业务场景是典型的高频写入、多维范围查询。原始表结构定义如下（基于 mysql-timescale.txt）：

CREATE TABLE `dms_data_gzdy` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `record_time` datetime DEFAULT NULL COMMENT '记录时间',
  `station_name` varchar(255) ... COMMENT '厂站名',
  `switch_oid` varchar(100) ... COMMENT '开关OID',
  `switch_status` int DEFAULT NULL COMMENT '开关状态',
  `voltage_uab` decimal(18,6) DEFAULT NULL COMMENT 'A相电压',
  `current_a` decimal(18,6) DEFAULT NULL COMMENT 'A相电流',
  -- ... 其他数十个电气量字段
  PRIMARY KEY (`id`),
  KEY `idx_record_time` (`record_time`),
  KEY `idx_query_perf` (`record_time`, `switch_oid`) 
) ENGINE=InnoDB;

痛点： 当数据量突破千万级时，执行如下典型查询，MySQL 即使配置了 8核/16G 硬件，耗时仍超过 30秒：

SELECT * FROM dms_data_gzdy 
WHERE record_time BETWEEN '2025-09-17' AND '2025-09-18' 
AND voltage_uab > 0 AND current_a <= 5000 
ORDER BY switch_oid, record_time;

2. 根因分析：为什么 MySQL 慢？

通过分析 MySQL 的执行逻辑，我们发现主要瓶颈在于：

随机 I/O (Random I/O)： MySQL 使用 B+Tree 索引。虽然能通过时间索引找到 ID，但回表读取 voltage_uab 等非索引字段时，需要大量的随机磁盘读取。
排序开销 (Filesort)： 查询条件是范围查询 (Range Scan)，导致无法利用联合索引 `(record_time, switch_oid)` 进行预排序，必须在内存或磁盘中重新排序。
大树效应： 在 2000 万行的 B+Tree 中查找数据，不仅深度深，且缓存命中率低。

3. 解决方案：TimescaleDB 架构优化

TimescaleDB 通过 Chunk（分片） 和 Columnar Compression（列式压缩） 彻底解决了上述问题。

3.1 核心优化一：按时间分片 (Hypertable)

将大表转换为超表，按天切分。查询 2 天的数据时，数据库只打开 2 个物理文件，忽略其他 99% 的历史数据。

-- 将表转换为 Hypertable，按 1 天切分
SELECT create_hypertable('dms_data_gzdy', 'record_time', chunk_time_interval => INTERVAL '1 day');

3.2 核心优化二：开启列式压缩 (Native Compression)

这是性能提升的关键。压缩不仅节省空间（90%+），更利用了 向量化计算 和 元数据跳过 技术。

关键配置点： 为了达到极致性能，压缩的物理存储顺序必须与查询的 ORDER BY 完全一致！

我们调整了默认的压缩策略，强制数据按 switch_oid 分组，并按 record_time ASC 排序：

ALTER TABLE dms_data_gzdy SET (
    timescaledb.compress,
    -- 1. Segment By: 类似分区。按设备ID分组，查询特定设备时只读相关块
    timescaledb.compress_segmentby = 'switch_oid', 
    
    -- 2. Order By: 必须与查询语句 'ORDER BY switch_oid, record_time' 保持一致
    -- 这样读取出来的数据天然有序，无需执行 Sort 操作
    timescaledb.compress_orderby = 'record_time ASC' 
);

3.3 核心优化三：配置自动策略

确保历史数据自动转换为压缩格式。注意：TimescaleDB 默认不压缩“热数据”（防止影响写入）。

-- 压缩 7 天前的数据
SELECT add_compression_policy('dms_data_gzdy', INTERVAL '7 days');

4. 性能与执行计划对比

特性	MySQL 8.0	TimescaleDB (优化后)
查询耗时	30s ~ 60s	1.4s
扫描方式	Index Range Scan + 回表	Vectorized Filter (向量化过滤)
IO 特征	随机 IO (Random IO)	顺序 IO + 元数据跳过 (Metadata Skipping)
排序机制	Using filesort (昂贵)	Merge Append (天然有序，零开销)

优化后的执行计划 (Explain Analyze)

可以看到 Sort 节点消失了，取而代之的是高效的 Merge Append，且过滤条件走了 Vectorized Filter。

Limit  (actual time=1247.732..1407.886)
   ->  Merge Append  (Sort Key: switch_oid, record_time)
         ->  Custom Scan (DecompressChunk)
               -- 向量化过滤：CPU 批量比对，极快
               Vectorized Filter: ((record_time >= ...))
               -- 索引扫描压缩元数据
               ->  Index Scan using ..._ts_meta_idx

5. 运维常用命令速查

查看压缩状态：

SELECT chunk_name, range_start, is_compressed 
FROM timescaledb_information.chunks 
WHERE hypertable_name = 'dms_data_gzdy'
ORDER BY range_start DESC;

手动强制压缩（用于测试或修补）：

-- 注意：_timescaledb_internal. 是内部 Schema 前缀
SELECT compress_chunk('_timescaledb_internal._hyper_2_5_chunk');

重置压缩策略（解决策略不生效问题）：

SELECT remove_compression_policy('dms_data_gzdy', if_exists => true);
SELECT add_compression_policy('dms_data_gzdy', INTERVAL '7 days');