问题概述

在光储充项目中，查询某台设备一整年的功率曲线时接口直接超时程序卡死。典型的 IoT 时序数据查询瓶颈——全表扫描 2.6 亿行。再排序，导致响应时间几秒甚至无响应。

下面记录从复现到最终解决的完整过程，并兼顾几千台设备同时上报的场景。

一、先把问题说清楚

数据规模估算

先算一下数据量：

500 台设备 × 1 条/分钟 × 60 分钟 × 24 小时 × 365 天 = 0.6 亿条/年

这只是功率一个指标。实际每台设备可能上报十几个字段，数据量乘数可达数倍。

原始表结构

CREATE TABLE device_power (
id BIGINT PRIMARY KEY AUTO_INCREMENT。device_id VARCHAR NOT NULL,power DECIMAL NOT NULL,recorded_at DATETIME NOT NULL
);

问题复现

SELECT device_id,power。recorded_at
FROM device_power
WHERE device_id = 'PV_001'
AND recorded_at BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY recorded_at ASC;

再看执行计划，

EXPLAIN SELECT ...
说到type。ALL ← 全表扫描
至于rows，← 扫了 2.6 亿行
从Extra来看，Using filesort

全表扫描 + 排序可能导致超时。

二、第一层调整：加索引

加哪个索引？

查询条件为的观点是，

WHERE device_id =?AND recorded_at BETWEEN?AND，

联合索引顺序必须是 等值条件放前面范围条件放后面。

CREATE INDEX idx_device_time ON device_power;

为什么这么做？

• 等值 + 范围可以同时利用索引。
• 反过来写 会让范围后面的列失效。
• 加完索引后执行计划变为：

EXPLAIN SELECT ...
至于type，range ← 范围扫描。不再全表
从rows来看，← 只扫这台设备一年内的数据量
至于Extra，Using index condition

查询时间从“超时”降到几秒，但仍不够理想。

三、第二层调整：分区表

为什么要分区？

• 单张大表的索引文件太大，维护成本高；写入速度下降，
• 分区后只扫描相关月份的文件。相当于“自动分表”，无需改业务代码。
• 能显著减少磁盘 I/O 与锁竞争。

按月分区示例

CREATE TABLE device_power (
id BIGINT NOT NULL AUTO_INCREMENT,device_id VARCHAR NOT NULL,power DECIMAL NOT NULL,recorded_at DATETIME NOT NULL,PRIMARY KEY
) PARTITION BY RANGE ) (
PARTITION p01 VALUES LESS THAN,PARTITION p02 VALUES LESS THAN。PARTITION p03 VALUES LESS THAN,PARTITION p04 VALUES LESS THAN,PARTITION p05 VALUES LESS THAN,PARTITION p06 VALUES LESS THAN,PARTITION p07 VALUES LESS THAN,PARTITION p08 VALUES LESS THAN,PARTITION p09 VALUES LESS THAN,PARTITION p10 VALUES LESS THAN,PARTITION p11 VALUES LESS THAN,PARTITION p12 VALUES LESS THAN MAXVALUE
);说起来，

查询时 MySQL 自动裁剪。只访问对应月份的分区，说到例如，

EXPLAIN SELECT *
FROM device_power
WHERE recorded_at BETWEEN '2023‑03‑01' AND '2023‑03‑31';-- partitions accessed: p03 only

四、第三层调整：降采样

痛点说明

• ECharts 折线图最多展示几百个点；把百万级点直接返回会导致网络延迟和前端渲染卡顿。
• 使用者根本看不出区别，却浪费带宽与 CPU。
• 查询结果过大，也拖慢数据库响应时间。

动态聚合粒度选择算法

public String getAggregateInterval {
long days = ChronoUnit.DAYS.娱乐ween;if return "minute";
// 一天以内按分钟
if return "hour";
// 一周以内按小时
if return "day";// 三个月以内按天
return "month";// 更长时间按月聚合
}
}

相应 SQL 聚合示例

SELECT …GROUP BY time_bucket → 返回 ~ 168 条数据 

``

示例:

sql SELECT device_id。 DATE_FORMAT AS time_bucket,G AS avg_power,MAX AS max_power,MIN AS min_power FROM device_power WHERE device_id = 'PV_001' AND recorded_at BETWEEN '2023‑01‑01' AND '2023‑01‑07' GROUP BY device_id,time_bucket ORDER BY time_bucket ASC;

---

按天聚合

---

效果从几十万条下降到几百条，接口响应降至毫秒级。

Java 层封装示例

java @Service public class PowerQueryService {

public List queryPowerCurve( String devId。LocalDateTime start,LocalDateTime end ) { String interval = getAggregateInterval;return switch{ case “minute” -> powerMapper.queryByMinute;case “hour” -> powerMapper.queryByHour;case “day”   -> powerMapper.queryByDay;case “month” -> powerMapper.queryByMonth;default       -> powerMapper.queryByDay;}，} }

---

五、第四层调整：冷热分离

为何需要冷热拆分？

因为时间推移，数据不断累积。历史数据被查询频率极低，而热点数据每天都在增长。将两者混在同一张大表里会导致：

场景	问题
热读	热门记录频繁读取导致磁盘 I/O 高
冷写	历史归档压缩耗费资源

方法是将最近 N 天的数据存放在“小而快”的热表，其余归档到压缩存储的冷表。

表结构示例

sql -- 热数据表 CREATE TABLE devicepowerhot ( id               BIGINT PRIMARY KEY AUTOINCREMENT。deviceid        VARCHAR NOT NULL,power            DECIMAL NOT NULL,recordedat      DATETIME NOT NULL,INDEX idxdev_time );

定时归档任务

java @Component public class DataArchiveJob {

@Scheduled // 每30分钟一次可根据需求调整 public void archiveOldData { LocalDateTime cutoff = LocalDateTime.now.minusDays;

int archived = powerMapper.insertColdFromHot;话说回来，// 写入冷表 int deleted = powerMapper.deleteHotBefore;// 删除热表旧记录

log.info;} }

归档 SQL

sql INSERT INTO DevicePowerCold SELECT DeviceID,power。recordedAT FROM DevicePowerHot WHERE Recorded_AT <#{cutoff};

DELETE FROM DevicePowerHot WHERE Recorded_AT <#{cutoff} LIMIT 10000; -- 分批删除避免长时间锁住热表。

---

六、七千台设备同时上报怎么办？

写入侧瓶颈分析

• 每分钟每台设备一次 → ~5000 条 INSERT/分钟。
• 数据库连接池快速耗尽。
• INSERT 排队等待锁 → 写入延迟 ↑。
• 高峰期会出现 “数据库堵塞”“写入积压”。

消息队列 + 批量写入方案

1. 接收阶段 – 所有设备通过 MQTT/Kafka 等协议发送至消息队列。
2. 消费阶段 – 使用者批量取出若干条记录，每 batch 大约 500 条 或者达到一定时间间隔。
3. 写入阶段 – 用 JD娱乐/MyBatis 批量 INSERT 到热表。

示例实现

java @Component public class PowerDataConsumer {

private final List buffer = new CopyOnWriteArrayList<>;

@KafkaListener public void onMessage{ buffer.add;if>= BATCH_SIZE){ flush;} }

@Scheduled public void scheduledFlush{ if){ flush;} }

private synchronized void flush{ if) return;List batch=new ArrayList<>;buffer.clear;powerMapper.batchInsert;// MyBatis 批量插入映射文件见下文。老实说，} }

<="">

xml

如此。将原本每分钟数千次单条 INSERT 转化为 数百次批量 INSERT显著降低数据库压力和网络往返次数。

---

八、效果对比与

调整手段	查询时间	说明

无调整 全表扫描 超时    ≈ 30 s+  

加索引 范围扫描 ≈ 5–8 s      

加分区 仅访问相关月份 ≈ 1–2 s      

降采样 聚合后返回数百条 ≈ 50–200 ms    

冷热分离 热数据小且常驻内存 " 10–50 ms

写入侧批量 + MQ "

总评 "

​