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

[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 ，在一个表里可以轻松的完成：）

如果有兴趣，欢迎联系我： [email protected]

[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 在现代硬件上其实可以高效的，只是现在时序厂商们的认知还没有到。单表局限场景，是可以的，但业务线发展了，还想一张表走天下，不可行。

[iotbase]

@Richard14 谢谢！

作为一个中国工程师，一直想告诉世界，我们能做最好的基础设施，没有之一。

这“闺女长大了要出阁”，我其实也挺紧张。iotbase 虽然免费开放，但还不是一个开源产品。所以我们不遵循一般开源项目地线路，先开出来，然后让用户去趟坑。我们已经有了严格的测试，我们的标准是：可以有功能没实现，但所有实现和开放的功能必须是生产级的。还有一些功能，比如 csv 格式 paload ，高可用 replication ，复合分区等，我们已经完成但尚未更新文档。所有物联网领域，我们认识到需要的一般性关系模型的功能，其他家有的没的，我们都会有，只是时间问题。但如果用户只需要基本的关系模型聚合，我们其实真很 solid 。

而且，iotbase 非常易用，2 分钟可用：半分钟解压启动，半分钟创建用户（我们安全 in core our mind ，我们没有默认用户，不创建用户，连上帝也没法使用，“妈妈再也不担心我被 default 脱库啦”），半分钟创建表，半分钟写入一条数据，半分钟 select *。作为一名数据库专家，我负责地保证，如果你是 pg/timescale 初次使用，光权数据目录准备和权限处理，没半小时，你都搞不定。

如果大家看到这个帖子愿意进来试试，我都愿意提供终身免费的企业级服务（包括从硬件选型到版本升级的所有问题）（也许要设个数量限制，哈哈）。

[leonhao]

@iotbase 你的说法存在误导性。对于一般的 PG 用户来说，物化视图就是指 PG 的 materialized view, refresh materialized view 是个全刷新的过程，你把 timesacledb 的 continuous aggregate 和 materialized view 划等号，自然给人一种速度很慢的印象。跳出业务谈技术没有意义，工厂的产线几乎不可能扩大 300 倍，当时选择 timescaledb 也是根据公司的业务规模选择的。

[iotbase]

@leonhao 握手，你说的没错，选择了就好，存在即合理，我并非要否定你的选择。我不跳出业务谈技术，恰恰我结合业务帮大家展望，10 万点秒级采样的场景都是真实用户案例。我是想站在物联网领域的一般性角度分析问题，看看大家如何能做的更好。

pg 的可以通过 extension 来来实现增量视图，但一般用户不懂，所以 timescale 有优势，没错。但增量其实也有增量的问题，要不 pg 为什么不放入主线呢？其中一些问题 pg 主页也列出来了。

第一，有些问题在不同场景下会有大放大：比如数据由于弱网环境，到来是有延迟的，有时甚至有按天计的大延迟。增量就是不停发生，为了保持同步，这种 naive 的机制下你必然不停同步。数据量大了，问题会非常多：比如即便是是增量，也不可能同步，相反资源的竞争会让你想减少同步的时机，这时你发现它开始玩不转。

第二，物化机制不管是对于 adhoc 查询基本无效。单一类型查询负载限制，导致你必然组合其他系统。你会想要对近期和长期数据进行复盘吗？就按你这个量，假定你有一个 1 年的数据库了，你会让你的运营会在生产节点上进行多样性的数据分析吗？ ok ，多加一个 replication 的 instance 吧，但数据库不要钱，硬件也要钱，对吧。所以，毋庸置疑，如果对这些问题进行原生的支持，一定会有更好的性价比。

当然如果说量也不大，性能也没问题，也不需要扩展，也不需考虑经费问题，确实选型此时不重要啦。

[ohmycorolla]

@iotbase 因为楼主说了不考虑时序数据库 传统的数据库用时间去切分 partition 是很合理的用法 如果不做读写分离 数据库压力很大根本吃不消 同步的时间没有你想象的那么夸张 但是因为异常导致同步出现问题这个情况确实是有的 我之前的项目组也是做监控 一天几十亿数据就是这样的方案 只是需要用到数据库中间件 mysql 实例多一些

[Richard14]

@ohmycorolla 所以没理解错的话，老哥之前在类似需求下的方案是用大表，并根据时间做 partition ，然后用集群解决性能问题？目前跑下来有什么感受吗，类似这套方案可行性如何，坑点和可改进之处之类的

[ohmycorolla]

@Richard14

[ohmycorolla]

@Richard14 我们是按照日期分 partition 然后分钟维度 小时维度 天维度 月维度都创建了一张表 分钟是实际采集到的数据 其他维度都是定时任务汇总出来的 可行性是没问题的 需要提前预估你的数据量适配的硬盘大小（和存的时间长短有很大关系） 还有带宽是否支持 坑的话 遇到最多的就是因为设备异常或者某个数据库实例挂了后恢复实例 但是数据出现很大的延迟 读库同步数据需要一段时间