V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Richard14
V2EX  ›  数据库

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

  •  
  •   Richard14 · 2022-05-17 00:57:07 +08:00 · 1854 次点击
    这是一个创建于 682 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

    27 条回复    2022-05-19 09:32:12 +08:00
    T0m008
        1
    T0m008  
       2022-05-17 01:03:44 +08:00
    传感器之前不存在关系的话肯定是分开存比较好
    Livid
        2
    Livid  
    MOD
       2022-05-17 01:09:43 +08:00
    试试这个:

    https://www.timescale.com/

    或者就用 MySQL 的分表和 partition ,不过到亿这个级别的计算,肯定是不能实时出结果的了。
    eason1874
        3
    eason1874  
       2022-05-17 01:13:31 +08:00
    看需求啊,如果要整体排序,一万张表联合查询,那 SQL 太美我不敢想象

    我比较倾向于按内容水平拆分,基本信息一张表,扩展信息另一张表。如果数据时效性强,就再按时间分表,比如按年份每年一张
    dayeye2006199
        4
    dayeye2006199  
       2022-05-17 01:22:23 +08:00
    如果关系查询都是针对单个传感器而言的话,建议直接使用数据库自己带的分表功能即可,例如 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
        5
    hefish  
       2022-05-17 08:00:10 +08:00
    传感器的数据,不是用时序数据库合适一些吗?
    tramm
        6
    tramm  
       2022-05-17 08:42:22 +08:00
    2L 说得不错, 就用 TimeScale 不错.

    话说, 一亿条数据也不多啊.
    Vaspike
        7
    Vaspike  
       2022-05-17 08:54:38 +08:00
    感谢 OP 的帖子,重新让我去看了下时序数据库相关
    leonhao
        8
    leonhao  
       2022-05-17 08:59:15 +08:00   ❤️ 1
    @Livid 可以用 continuous aggregate, 传感器数据一般都是固定模式聚合查询。
    ohmycorolla
        9
    ohmycorolla  
       2022-05-17 10:02:48 +08:00
    读写分离,按需分 partition
    littlewing
        10
    littlewing  
       2022-05-17 10:28:08 +08:00
    看你查询的需求了,如果拆了后查询的时候一个查询需要查询所有分表,或者有跨分表 join ,那还不如不拆
    iotbase
        11
    iotbase  
       2022-05-17 20:44:06 +08:00   ❤️ 1
    可以试试这个: 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
        12
    iotbase  
       2022-05-17 20:52:24 +08:00
    > 肯定是不能实时出结果的了。
    @Livid 通常的 TP 确实是,但其实现代硬件真实的实力是,10 亿行数据单机 2-3 列的单表聚合可以在 10ms-1s 以内完成。
    iotbase
        13
    iotbase  
       2022-05-17 21:03:44 +08:00
    @ohmycorolla 如果是监控类分析,异步的读写分离通常不可接受,同步的读写本质没有分离,还会恶化性能
    iotbase
        14
    iotbase  
       2022-05-17 21:13:46 +08:00
    @leonhao continuous aggregate 其实就是物化试图,本质是 pg 在后台帮你 refresh ,但是量大了,物化试图机制同样产生问题,当然有些手段可以调整,但毕竟只能治标。
    leonhao
        15
    leonhao  
       2022-05-17 21:42:17 +08:00
    @iotbase continuous aggregate 和物化视图有本质区别,物化视图只能全刷新,continuous aggregate 可以根据参数增量刷新。我现在的项目每天一亿多条新增,使用起来没有任何问题。
    joesonw
        16
    joesonw  
       2022-05-17 23:16:58 +08:00 via iPhone
    influxdb 等适合存时序数据的呗,一般能支持很复杂的 aggregation 。
    Richard14
        17
    Richard14  
    OP
       2022-05-18 09:22:21 +08:00
    @iotbase 感谢,很高屋建瓴的分析,项目也很感兴趣

    @joesonw 时序数据库对高读的表现并不是很好,再加上还需要关系型运算的支持,以前做过规模测试最后决定不用时序数据库
    iotbase
        18
    iotbase  
       2022-05-18 09:34:25 +08:00
    @leonhao 所谓增量还是全量刷新,其实只是物化视图的实现细节[1]

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

    [1] https://wiki.postgresql.org/wiki/Incremental_View_Maintenance
    joesonw
        19
    joesonw  
       2022-05-18 09:34:48 +08:00 via iPhone
    @Richard14 时序数据库一般预聚合效果会好一些。如果搜索条件变来变去确实扛不住。
    iotbase
        20
    iotbase  
       2022-05-18 09:43:09 +08:00
    @joesonw 实话实话,influxdb 其实已经 out ,不管重技术层还是表示层。aggregation 其实是一个 low hanging fruit ,通常用 sql 都能组合出来,当然效率会比定制算子低一些,但通常据我所知,用户期待使用太复杂的算法的场合并不多(当然选择多总是欢迎的)。如果真要用,我建议还是一般性的 sql 模型,某些场景的 join 在现代硬件上其实可以高效的,只是现在时序厂商们的认知还没有到。单表局限场景,是可以的,但业务线发展了,还想一张表走天下,不可行。
    iotbase
        21
    iotbase  
       2022-05-18 10:23:51 +08:00
    @Richard14 谢谢!

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

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

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

    如果大家看到这个帖子愿意进来试试,我都愿意提供终身免费的企业级服务(包括从硬件选型到版本升级的所有问题)(也许要设个数量限制,哈哈)。
    leonhao
        22
    leonhao  
       2022-05-18 10:53:50 +08:00
    @iotbase 你的说法存在误导性。对于一般的 PG 用户来说,物化视图就是指 PG 的 materialized view, refresh materialized view 是个全刷新的过程,你把 timesacledb 的 continuous aggregate 和 materialized view 划等号,自然给人一种速度很慢的印象。跳出业务谈技术没有意义,工厂的产线几乎不可能扩大 300 倍,当时选择 timescaledb 也是根据公司的业务规模选择的。
    iotbase
        23
    iotbase  
       2022-05-18 11:36:20 +08:00
    @leonhao 握手,你说的没错,选择了就好,存在即合理,我并非要否定你的选择。我不跳出业务谈技术,恰恰我结合业务帮大家展望,10 万点秒级采样的场景都是真实用户案例。我是想站在物联网领域的一般性角度分析问题,看看大家如何能做的更好。

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

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

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

    当然如果说量也不大,性能也没问题,也不需要扩展,也不需考虑经费问题,确实选型此时不重要啦。
    ohmycorolla
        24
    ohmycorolla  
       2022-05-18 14:00:38 +08:00
    @iotbase 因为楼主说了不考虑时序数据库 传统的数据库用时间去切分 partition 是很合理的用法 如果不做读写分离 数据库压力很大根本吃不消 同步的时间没有你想象的那么夸张 但是因为异常导致同步出现问题这个情况确实是有的 我之前的项目组也是做监控 一天几十亿数据就是这样的方案 只是需要用到数据库中间件 mysql 实例多一些
    Richard14
        25
    Richard14  
    OP
       2022-05-18 19:42:42 +08:00
    @ohmycorolla 所以没理解错的话,老哥之前在类似需求下的方案是用大表,并根据时间做 partition ,然后用集群解决性能问题?目前跑下来有什么感受吗,类似这套方案可行性如何,坑点和可改进之处之类的
    ohmycorolla
        26
    ohmycorolla  
       2022-05-19 09:22:19 +08:00
    ohmycorolla
        27
    ohmycorolla  
       2022-05-19 09:32:12 +08:00
    @Richard14 我们是按照日期分 partition 然后分钟维度 小时维度 天维度 月维度都创建了一张表 分钟是实际采集到的数据 其他维度都是定时任务汇总出来的 可行性是没问题的 需要提前预估你的数据量适配的硬盘大小(和存的时间长短有很大关系) 还有带宽是否支持 坑的话 遇到最多的就是因为设备异常或者某个数据库实例挂了后恢复实例 但是数据出现很大的延迟 读库同步数据需要一段时间
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2924 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 11:10 · PVG 19:10 · LAX 04:10 · JFK 07:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.