传感器数据存入数据库是单表存一亿条还是拆分成小表？

[Richard14]

传感器约一万个，是拆分成一万张小表，还是把所有数据都汇总储存到一张大表中（行数会超过一亿条）然后分库分表比较合适？有没有有类似设计经验的大佬？

因为需要运用不少的关系型运算，读写一样高，暂不考虑时序数据库，只考虑传统关系型数据库。

[T0m008]

传感器之前不存在关系的话肯定是分开存比较好

[Livid]

试试这个：

https://www.timescale.com/

或者就用 MySQL 的分表和 partition ，不过到亿这个级别的计算，肯定是不能实时出结果的了。

[eason1874]

看需求啊，如果要整体排序，一万张表联合查询，那 SQL 太美我不敢想象

我比较倾向于按内容水平拆分，基本信息一张表，扩展信息另一张表。如果数据时效性强，就再按时间分表，比如按年份每年一张

[dayeye2006199]

如果关系查询都是针对单个传感器而言的话，建议直接使用数据库自己带的分表功能即可，例如 https://dev.mysql.com/doc/refman/8.0/en/partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.html

如果单表还是大，甚至可以考虑 传感器+日期作为分区的 key 。

好处是不需要更改读写 SQL 的形式，数据库的 query 优化器可以帮助减少读写不必要的分区，只需要更改 DDL 就可以。

[hefish]

传感器的数据，不是用时序数据库合适一些吗？

[tramm]

2L 说得不错, 就用 TimeScale 不错.

话说, 一亿条数据也不多啊.

[Vaspike]

感谢 OP 的帖子,重新让我去看了下时序数据库相关

[leonhao]

@Livid 可以用 continuous aggregate, 传感器数据一般都是固定模式聚合查询。

[ohmycorolla]

读写分离，按需分 partition

[littlewing]

看你查询的需求了，如果拆了后查询的时候一个查询需要查询所有分表，或者有跨分表 join ，那还不如不拆

[iotbase]

可以试试这个： https://iotbase.io/

我们在进行一些发布前的准备。 第一个真正意义上的物联网数据库：SQL 语法，MQTT 消息直写（也兼容 pg 线协议，所以当准 pg 用也可以），顶级的写入性能，顶级的分析查询性能（引擎内部对标 ClickHouse ，比 ClickHouse 快；外部对标 Timescale ，比 Timescale 快的 2-3 个数量级），免运维设计，极好的工程稳定性，支持常用 SQL 函数。有待改进的是，目前只支持单机（但支持 HA ），复杂的 SQL 分析函数和 JOIN 还在进行中。

对于你这个问题，应该用一个表搞定。分库分表以及类似设计其实已经不应该再用了。读写一样高+关系运算，说明你们“分析”的需求强，传感器单点数值基本是没意义的。iot 领域一个强需求是：实时监控报警。在这样的场景里，你需要分析所有传感器近期的状态和进行异常判定。设想，你每分钟向 1 万个表发起 1 万个查询，来告诉你 10000x60 个点的组合中，哪个超出了设定值。通常的 TP 数据库，包括 MySQL/Postgres/Timescale ，是不可能的，因为你是“读写一样高”。而用我们 iotbase ，在一个表里可以轻松的完成：）

如果有兴趣，欢迎联系我： [e-mail]

[iotbase]

> 肯定是不能实时出结果的了。
@Livid 通常的 TP 确实是，但其实现代硬件真实的实力是，10 亿行数据单机 2-3 列的单表聚合可以在 10ms-1s 以内完成。

[iotbase]

@ohmycorolla 如果是监控类分析，异步的读写分离通常不可接受，同步的读写本质没有分离，还会恶化性能

[iotbase]

@leonhao continuous aggregate 其实就是物化试图，本质是 pg 在后台帮你 refresh ，但是量大了，物化试图机制同样产生问题，当然有些手段可以调整，但毕竟只能治标。

[leonhao]

@iotbase continuous aggregate 和物化视图有本质区别，物化视图只能全刷新，continuous aggregate 可以根据参数增量刷新。我现在的项目每天一亿多条新增，使用起来没有任何问题。

[joesonw]

influxdb 等适合存时序数据的呗，一般能支持很复杂的 aggregation 。

[Richard14]

@iotbase 感谢，很高屋建瓴的分析，项目也很感兴趣

@joesonw 时序数据库对高读的表现并不是很好，再加上还需要关系型运算的支持，以前做过规模测试最后决定不用时序数据库

[iotbase]

@leonhao 所谓增量还是全量刷新，其实只是物化视图的实现细节[1]

另外，每天 1 亿其实只能算小数据，查询在 500ms 和 5ms 完成可能没直观感觉（但其实快还是能给你省钱，至少你可以把机器 instance 配置减到最低）。10 万个点，每个点数十个指标，数十秒一次上报。他们其实还想更快的数据收集，但系统做不到。你可以试一下，把你一年的数据压到 1 天（相当于你业务扩大 300 倍），看看 timescale 工作如何。

[1] https://wiki.postgresql.org/wiki/Incremental_View_Maintenance

[joesonw]

@Richard14 时序数据库一般预聚合效果会好一些。如果搜索条件变来变去确实扛不住。

[iotbase]

@joesonw 实话实话，influxdb 其实已经 out ，不管重技术层还是表示层。aggregation 其实是一个 low hanging fruit ，通常用 sql 都能组合出来，当然效率会比定制算子低一些，但通常据我所知，用户期待使用太复杂的算法的场合并不多（当然选择多总是欢迎的）。如果真要用，我建议还是一般性的 sql 模型，某些场景的 join 在现代硬件上其实可以高效的，只是现在时序厂商们的认知还没有到。单表局限场景，是可以的，但业务线发展了，还想一张表走天下，不可行。