物联网平台数据表只涨不缩?Spring Boot 遥测数据保留策略:分批删除 + 索引优化 + 真实压测数据

简介: 本文以真实设备接入平台为例,详解时序数据治理三大核心:分批删除防锁表、复合索引+限量查询保响应、压测聚焦P99长尾。从100台设备日增172万条数据的现实压力出发,手把手拆解MySQL下高效存查清的落地实践与避坑指南。

一台设备每 5 秒上报一次,一天就是 17280 条。100 台设备一天 172 万条,一个月 5000 万条。能收到数据只是第一天,第二天开始真正的难题是:这张表怎么查得动、怎么清得掉。本文用我正在搭的设备接入平台里的真实代码,把"时序数据治理"这条线讲透。

先算一笔账:你为什么一定会遇到这个问题

设备接入层跑通之后,很多人会松一口气——数据能进来、能展示,故事结束了。

但把上报频率代进去就不那么乐观了:

设备数 上报频率 单日条数 单月条数
1 台 5 秒一次 17,280 约 52 万
100 台 5 秒一次 172.8 万 约 5184 万
1000 台 5 秒一次 1728 万 约 5.2 亿

遥测数据是典型的时序数据:只涨不缩。 它没有"删除"这个业务动作,设备只要活着就一直上报。所以等你发现 /api/devices/{id}/data 从 7ms 变成 2 秒,通常表已经几千万行了。

这就是设备接入平台从 M2 走到 M3 时必须补上的一课。下面三块,对应"怎么清、怎么查、怎么证明"。

一、怎么清:分批删除,别一条 DELETE 干到底

最直觉的写法是一句 SQL 清掉 30 天前的数据:

-- 千万别这么写
DELETE FROM data_point WHERE received_at < NOW() - INTERVAL 30 DAY;

这句话在开发机上跑得飞快,在有几千万行业务表上就是事故:一个大事务会长时间持锁、undo log 暴涨、binlog 写满、主从延迟飙到几分钟,期间线上查询全部排队。

正确做法是分批删。这是我项目里的真实实现:

@Slf4j
@Component
@RequiredArgsConstructor
public class DataRetentionJob {
   

    public static final int BATCH_SIZE = 1000;

    private final DataPointRepository dataPointRepository;

    /** 保留天数,application.yml 里 iot.data-retention.days 可覆盖 */
    @Value("${iot.data-retention.days:30}")
    private int retentionDays;

    /** 真正的清理逻辑,返回删除的总行数。public 是为了单元测试直接调用 */
    public int purgeExpired(int days) {
   
        Instant cutoff = Instant.now().minus(days, ChronoUnit.DAYS);
        int total = 0;
        int batch;
        do {
   
            List<DataPoint> oldest = dataPointRepository
                    .findTop1000ByReceivedAtBeforeOrderByIdAsc(cutoff);
            batch = oldest.size();
            if (batch > 0) {
   
                dataPointRepository.deleteAllInBatch(oldest);
                total += batch;
            }
        } while (batch == BATCH_SIZE); // 删满一批说明可能还有,继续;没删满说明清完了
        if (total > 0) {
   
            log.info("数据保留任务:删除 {} 天前的遥测数据 {} 条(截止 {})", days, total, cutoff);
        }
        return total;
    }

    /** 生产节奏:每天凌晨 3 点执行。单实例部署暂不需要分布式锁 */
    @Scheduled(cron = "${iot.data-retention.cron:0 0 3 * * ?}")
    public void run() {
   
        purgeExpired(retentionDays);
    }
}

这段代码里有 5 个点是面试真能聊的:

  1. 分批而不是全删。每批 1000 条,一个短事务一把梭完就提交,锁持有时间毫秒级,不会打扰线上流量。
  2. while (batch == BATCH_SIZE) 这个循环条件是关键。删满一批说明"可能还有",继续;没删满说明已经清空,退出。比"先 count 再算循环次数"省一次全表扫描。
  3. 保留天数走配置。iot.data-retention.days 默认 30,不同环境可以不一样(测试环境保留 3 天就够)。硬编码天数是很多人的第一版写法,改起来要重新发版。
  4. cron 也走配置,默认凌晨 3 点。清理是低优先级任务,要避开业务高峰。
  5. 只删 data_point,device 档案永远保留。一句话原则:数据会过期,档案不会。 设备表是业务实体,删了就等于设备"消失了",历史数据全成孤儿。

如果要更进一步,接着做这三步:received_at 上加索引(否则这个查询本身就在全表扫)、按时间分区表(PARTITION BY RANGE,删数据直接 DROP PARTITION,秒级完成)、上量后换成 TDengine 这类时序库(自带 retention 策略,连定时任务都省了)。

二、怎么查:索引 + 限量 + 不许深分页

清理解决"存得下",查询决定"用起来"。时序表的查询有三条铁律。

第一条:deviceId + receivedAt 必须有索引。 时序表是全库最大的表,没有索引的时间范围查询就是灾难。我的实体上先建了设备维度索引:

@Entity
@Table(indexes = @Index(name = "idx_datapoint_device", columnList = "deviceId"))
public class DataPoint {
   

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Long deviceId;
    private String productKey;

    /** 原始 JSON,如 {
   "temp":25.5,"humi":60} */
    @JsonIgnore
    private String payload;

    /** 服务端收到的时间 */
    private Instant receivedAt;
}

注意 payload 上是 @JsonIgnore——别把原始 JSON 大字段顺手塞进列表接口的响应里。这是个很容易犯的错:一个设备 100 条数据,每条 payload 200 字节,响应就凭空多出 20KB,网络和前端渲染全被拖慢。

第二条:接口必须限量,且要有硬上限。 设备上报是无界的,全量查询一定会把内存打爆:

private static final int DEFAULT_LIMIT = 100;
private static final int MAX_LIMIT = 1000;

int size = limit == null ? DEFAULT_LIMIT : Math.min(limit, MAX_LIMIT);
Pageable pageable = PageRequest.of(0, size, Sort.by(Sort.Direction.DESC, "id"));
return views(dataPointRepository.findByDeviceId(deviceId, pageable));

Math.min(limit, MAX_LIMIT) 这一句是整个接口最重要的防线。如果有人传 limit=999999,你不拦住,数据库就得把整张表捞出来。开放给外部调用的分页参数,一定要有服务端上限。

第三条:返回倒序,且不给深分页。 我的接口默认返回最新 100 条而非最旧 100 条——看板要画的是"最近的曲线",limit=100 就该拿到最新的 100 条。同时接口只做"最新 N 条"和"时间范围查询",故意不做 offset 深分页:LIMIT 1000000, 100 这种查询 MySQL 要扫前面 100 万行,越翻越慢。历史数据要靠时间范围定位,不是靠翻页。

时间范围参数还要做格式校验,别让非法输入变成 500:

private Instant parse(String value, String param) {
   
    try {
   
        return Instant.parse(value);
    } catch (DateTimeParseException e) {
   
        throw new IllegalArgumentException(
                param + " 需要 ISO-8601 格式,例如 2026-09-22T00:00:00Z");
    }
}

配合全局异常处理,这个 IllegalArgumentException 会变成 400 + 明确提示,用户拿到的是"参数格式不对",而不是"服务器内部错误"。

三、怎么证明:压测要看分位数,不是平均数

代码写完了,"性能好"不能靠感觉,得压。我对最热的查询接口 GET /api/devices/{id}/data?limit=100 做了基准压测(JMeter 5.6,100 并发线程、20 秒 ramp-up、持续 60 秒)。先说清环境:压测端、应用、MySQL 全在同一台开发笔记本上,这是个诚实但很受限的条件。

实测结果(我记录在 loadtest/README.md 里):

指标 数值
总请求数 136,892
实际时长 59.9 s
吞吐量 2,285 req/s
平均响应 36.5 ms
P50 7 ms
P90 104 ms
P95 191 ms
P99 422 ms
最大 1,712 ms
错误率 0.00%

比数字更重要的是怎么读这组数字,这里有两个反直觉的点:

第一,瓶颈不在服务端。 100 线程 × 平均 36.5ms ≈ 理论上限 2700 req/s,实测 2285 req/s,逼近这个天花板了。这说明瓶颈是"压测端的并发模型",不是我的接口——服务端还远没打满。想压出服务端真实极限,得把 JMeter 放到另一台机器上。

第二,P50 和 P99 差了 60 倍。 P50 只有 7ms,P99 却是 422ms。长尾从哪来?GC 停顿 + JMeter 和被测应用抢同一台机器的 CPU。如果只报"平均响应 36.5ms",这条长尾就被平均数完全掩盖了。 面试被问"你的接口性能如何",回答"平均 36ms"基本等于没回答;回答"P50 7ms、P99 422ms,长尾来自 GC 和压测端同机抢核,所以我下一步要分离压测机"——这才是做过压测的人。

读者常见三问

Q:为什么不用 DELETE ... LIMIT 1000 这样一句 SQL 批量删,非要先查出来再删?

A:DELETE ... LIMIT 在 MySQL 单表上可以用,但有两个现实问题:一是有 LIMIT 的 DELETE 在部分场景(多表、有外键、需要主从一致)支持不佳;二是先 SELECT 出主键列表再按主键删,主从复制更安全,而且这个 SELECT 走索引、结果可预测。我选的是后者。关键从来不是选哪种语法,而是"别让一个事务删几百万行"这个原则本身。

Q:分批删除会不会删到一半失败,留下不一致状态?

A:不会。每一批是独立事务,删掉的就是真删掉了,剩下的下一轮继续删——最终一致。清理任务不需要 ACID 跨批事务,因为它本来就是个"幂等的收敛过程"。真出异常,第二天凌晨 3 点还会再来一次。

Q:什么时候该放弃 MySQL,换 TDengine / TimescaleDB?

A:三个信号同时出现就该换了:① 单表超过 1 亿行;② 查询从"某设备最近数据"变成"多设备跨时间聚合"(比如按小时算平均值);③ 你开始手写分区、归档、降采样这些时序库自带的功能。换成时序库之后,接口层几乎不用改——这也是为什么我从一开始就把查询包在 DataPointView 这种 DTO 里,而不是把实体直接抛出去。

下一步

到这里,设备接入平台的"数据侧"闭环了:接入 → 入库 → 限量查询 → 定时清理 → 压测验证。再往后是两件事:一是把数据量堆上去,用分区表或 TDengine 重跑同一份压测脚本,看拐点在哪;二是做规则引擎和告警,这才是物联网平台真正产生业务价值的地方。


我是软件工程在读,正在从零搭一个物联网设备接入平台(Spring Boot + EMQX + MySQL),所有代码和踩坑记录都会发出来。欢迎关注,一起卷物联网后端。

相关文章
|
Linux iOS开发 UED
Linux简介和比较:开源操作系统的优势与差异
Linux简介和比较:开源操作系统的优势与差异
1360 0
|
1月前
|
人工智能 Linux 芯片
【保姆级】Ollama下载安装+本地模型部署+避坑指南一篇搞定
Ollama是本地运行AI模型的轻量工具,支持Windows/macOS/Linux。安装后可离线下载Qwen3:4b等中文模型,无需注册、不联网也能聊天、改写、整理文本,隐私安全,适合个人日常AI辅助。
569 0
|
3月前
|
人工智能 监控 前端开发
开眼了!AI 应用还从零搭?11.7 万 Star awesome-llm-apps 把 Agent/RAG/MCP 模板摆成夜市
Shubhamsaboo/awesome-llm-apps 是一个可运行的 LLM 应用模板库,覆盖 Agent、RAG、MCP、Voice、Memory、Fine-tuning 等方向,适合开发者找灵感、做原型、学 AI 应用工程化。
334 0
|
4月前
|
人工智能 安全 调度
QwenPaw:你的私人AI助理 —— 数据归你、记忆进化、多端触达的开源个人智能体
在AI智能体快速普及的当下,绝大多数通用AI助手存在三大无法规避的痛点:用户所有对话记录、个人偏好画像、私密记忆数据统一托管至第三方远端服务器,数据隐私不受自身掌控;平台内置功能固定,无法根据个人业务、学习、创作需求自定义拓展能力;多办公软件、社交渠道数据割裂,在网页端配置的人设、记忆无法同步至办公IM工具,使用体验碎片化。由AgentScope开源生态团队打造的QwenPaw,以**本地优先、全开源、数据自持**为核心设计思路,彻底解决以上行业痛点,推出一款面向个人与小型团队的私有AI智能体框架,采用Apache 2.0开源协议,无商用限制,仅需3行命令即可完成全量部署,内置Web Codi
1004 1
|
前端开发 JavaScript 流计算
React 18 流式渲染:解锁极致性能优化实践
React 18 流式渲染:解锁极致性能优化实践
854 80
|
9月前
|
机器学习/深度学习 TensorFlow API
TensorFlow 入门指南
TensorFlow 是 Google 开源的主流机器学习框架,支持从研究到生产的全周期开发。其核心优势在于:灵活的数据流图、Keras 高级API易上手、底层API可深度定制,以及TensorBoard、TFLite、TF Serving等完善生态,广泛应用于图像识别、自然语言处理等领域。(239字)
|
7月前
|
人工智能 自然语言处理 安全
[点击下载】5 分钟搭建 OpenClaw 本地 AI 智能体完整流程
OpenClaw(小龙虾)是一款本地运行的开源AI智能体,支持Windows一键部署,5分钟即可搭建。无需编程、不联网、数据全留本地,具备文件整理、邮件发送、浏览器自动化等办公能力,真正零门槛、高安全、开箱即用。(239字)
|
8月前
|
传感器 机器学习/深度学习 资源调度
Figure 03自主清洁147秒引关注,马斯克亲自下场追问
Figure 03人形机器人搭载Helix 02模型,147秒全自主完成客厅清洁,无干预、无加速。其端到端神经网络实现感知-决策-控制一体化,凭真实家庭数据驱动技能迭代,展现卓越环境适应力与全身协同能力,加速人形机器人家用落地进程。
|
11月前
|
存储 弹性计算 Linux
阿里云服务器怎么购买?阿里云服务器便宜购买教程
阿里云服务器怎么购买?阿里云服务器提供三种不同的购买入口,每种方式在配置灵活性、操作复杂度等方面各有特点,用户可根据自身需求选择合适的购买方式,以下是详细介绍:

热门文章

最新文章