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

大佬们,想请教一下数据库设计

  •  1
     
  •   iamtuzi3333 · 39 天前 · 5316 次点击
    这是一个创建于 39 天前的主题,其中的信息可能已经有所发展或是发生改变。
    小弟目前遇到一个棘手的问题,就是现在咱们的公司用的数据库是 MongoDB ,目前出现吃内存严重现象,同时查询效率不高,数据其实很简单,但是量很多,都是传感器数据,现在每秒都有数据入库,都是一条条的 json ,现在用的 MongoDB ,单个集合就存储一个传感器的数据,但是我发现查询接口太慢了,查询过程只有一个字段去比较,就是大于 and 小于这个值的字段的所有数据,这个都很慢,数据关键一个字段就是 data 数组,200 个浮点数。大佬们有其他数据库推荐吗,不涉及多表联合查询,都是单表操作。
    68 条回复    2024-10-09 14:31:35 +08:00
    celaraze
        1
    celaraze  
       39 天前
    索引建了吗?数据文档的字段是否都是一致的,是的话用单字段索引,不是的话用稀疏索引。试试,按照你的描述很可能没有索引导致全集合扫描了。
    flmn
        2
    flmn  
       39 天前
    postgres 不就可以么?
    iamtuzi3333
        3
    iamtuzi3333  
    OP
       39 天前
    @celaraze 索引没建立,我是按照一个时间戳字段去查询,这个值一直在变,没法用索引吧。
    @flmn 我查一下这个。
    iamtuzi3333
        4
    iamtuzi3333  
    OP
       39 天前
    @celaraze 文档的字段都是一致的,估计是,因为文档数量非常的多,每秒一条,一天有 8 万多条了。
    corcre
        5
    corcre  
       39 天前
    采集的数据吗, 我们乙方用的时序数据库(TDengine), 不存数组, 按采集点位存的数据, 每个时间每台设备每个点位一条数据, 暂时没什么问题(但是我没具体了解过我们对性能的要求)
    seedhk
        6
    seedhk  
       39 天前
    1.MongoDB 本来就非常吃内存,高性能都是建立在内存的基础上的
    2.给时间戳字段加上索引
    3.看一下单表数据量,实在太多考虑将查询功能放到 ES(需要评估)
    celaraze
        7
    celaraze  
       39 天前   ❤️ 1
    @iamtuzi3333 如果按照时间戳查询,建议用时序数据库( TSDB ),把你要存储的值当作度量处理。你这个场景感觉 IoT 方向啊,怎么会选型用 mongo 。
    iamtuzi3333
        8
    iamtuzi3333  
    OP
       39 天前
    @corcre 一个点位的一个数的话时序数据库很方便,但是数据就很麻烦,我之前找过 influxdb ,感觉不太行。
    celaraze
        9
    celaraze  
       39 天前
    @corcre 你的建议是对的。
    iamtuzi3333
        10
    iamtuzi3333  
    OP
       39 天前
    @celaraze 数据是 loT 。但是时序数据库一个麻烦的点就是我没有找到有很好的字段支持,很多时序数据库都是一个时间点对应一个值,我的数据字段比较多,还有数组类型在里面,不好处理。
    @seedhk 时间戳的值会一直变,这样索引更新不是更加消耗资源吗,单表数据量目前是 600 多万条文档了,每天 8 多万增加。
    yoyolichen
        11
    yoyolichen  
       39 天前
    我们已经转时序 tdengine 了,10w 台设备,频率每天 1-24 条不等,目前没啥问题
    iamtuzi3333
        12
    iamtuzi3333  
    OP
       39 天前
    @yoyolichen 数据字段是一个值吗,就一个时间字段映射一个值? 现在麻烦的就是字段特别多,不同的类型也多,有数组这些。
    Desdemor
        13
    Desdemor  
       39 天前
    clickhouse 试试呢
    celaraze
        14
    celaraze  
       39 天前
    @iamtuzi3333 不知道你测试的是哪个数据库,例如 influxDB 中 Point 可以包含多个 Fields 的。一条记录多个属性值。试试看吧,IoT 都用时序的。
    zhazi
        15
    zhazi  
       39 天前
    mongodb 5.0 之后也支持时序功能
    https://www.mongodb.com/zh-cn/docs/manual/core/timeseries-collections/
    建议先去尝试优化
    iamtuzi3333
        16
    iamtuzi3333  
    OP
       39 天前
    @celaraze 我先搜一下,只有我一个人,顶着公司走,难受。。。
    @Desdemor 似乎接口有点复杂,我先看看。
    @zhazi 好的,我先去试试优化,其实 MongoDB 存是非常的方便,就是吃内存很严重,即使修改了配置文件的内容。
    sunxvvv
        17
    sunxvvv  
       39 天前
    你不建索引,每一次的查询都是全集合扫描,随着数据越多,查询只会越来越慢,索引带来的内存和 CPU 消耗绝对比你每一次都是全集合扫描少得多,索引是肯定利大于弊的,用 MongoDB 的话,数据量实在太大,可以上分片
    encro
        18
    encro  
       39 天前
    每次看到使用 MongoDB 的,我就想和他说一句“没有银弹”。。。
    zhangeric
        19
    zhangeric  
       39 天前
    这个肯定要用时序数据库了.
    halov
        20
    halov  
       39 天前
    MongoDB 用了多少内存
    Mithril
        21
    Mithril  
       39 天前
    IoT 不涉及到修改数据吧,什么叫“时间戳的值会一直变”?难道你会频繁修改已经入库数据的时间戳?
    encro
        22
    encro  
       39 天前
    高性能 与 free scheme ,低内存那是天然冲突。。。
    sagaxu
        23
    sagaxu  
       39 天前
    时间戳字段为什么一直会变?监控数据不是一直追加上去的吗?只存储设备的当前状态,不断 update 吗?

    所有 DB 本质都是一样的,查找数据要么遍历,要么走索引,数据量一大,遍历必然是很慢的,要通过高区分度的索引加速查找,若没法建立高质量索引,那只能 case by case 特别优化了
    iamtuzi3333
        24
    iamtuzi3333  
    OP
       39 天前
    @sunxvvv 现在有一个字段是 int 整形,存储的是当前时间戳的值,每条写入的时候都有会,这样子适合建立吗,真实查询数据的场景不是很多,偶尔需要查询。
    @halov 直接占了快 60G ,同时他的 mapper file 会持续上涨,备用内存全是这个内容,导致可用内存最后剩下不到 100M ,服务器 192GB 的内存都给干满了。
    @Mithril 是的,不涉及修改,就是现在数据入库我为了查询方便,就加了一个字段,这个字段存的值就是当前时间戳的值。
    @encro 唉,难受,这个确实没办法完美解决,而且其他数据库适合存的情况下接口又不方便。哭了
    iamtuzi3333
        25
    iamtuzi3333  
    OP
       39 天前
    @sagaxu 数据不是追加的,每秒都会有新的数据入库,设备数据采集频率很高,所以数据很多,比如说 200Hz ,就说明每秒都有一条数据出来,其中的 data 数组就存储了 200 个浮点数,这种情况下如何编写索引,遍历随着数据量增长,到后面基本没办法找去年的数据出来了。
    lancelock
        26
    lancelock  
       39 天前
    用时序数据库没问题阿,点多有什么关系,数组类型的可以拆开存吧
    LieEar
        27
    LieEar  
       39 天前
    “数据其实很简单,但是量很多,都是传感器数据,现在每秒都有数据入库”

    这是典型的时序数据库需求,可以试试
    wxf666
        28
    wxf666  
       39 天前
    @iamtuzi3333 #24

    换成一般关系数据库,你的表结构是这样吗:

    ( ID ,设备 ID ,毫秒时间戳,浮点数据),其中加了(设备 ID ,毫秒时间戳)索引,

    你查询需求是:某设备,在某时间段内的,所有浮点数据?
    sagaxu
        29
    sagaxu  
       39 天前
    @iamtuzi3333 时间戳字段建个索引试试看吧,数据量大,新增索引可能要建个几分钟几十分钟的。建完索引,再查查看,如果还慢,把查询计划贴出来分析分析。
    iamtuzi3333
        30
    iamtuzi3333  
    OP
       39 天前
    @lancelock 有一个关键字段就是数组,里面有 200 个元素,就是拆除 200 个 key 对应 value 么
    @LieEar 开源的 influxdb 想试试,刚看了一下,发现还行,就是要拆数据字段,不知道查询效果怎么样,比如说多传感器能不能多表存储,就一个传感器一张表
    iamtuzi3333
        31
    iamtuzi3333  
    OP
       39 天前
    @wxf666 是的,浮点数据这个字段是一个数组,里面数量就看设备的采样率决定。查询需要翻找某一段时间的所有浮点数数据出来。
    @sagaxu 主要是更新索引很耗资源,当集合文档数上百万的时候,这个时候维护索引估计很难,而且不止一个集合,有几百个传感器
    Geekerstar
        32
    Geekerstar  
       39 天前
    我用的 TDengine ,目前几十亿数据,查询很快
    gabriel_zhen
        33
    gabriel_zhen  
       39 天前
    @corcre 专业的事情 专业的数据库干
    sagaxu
        34
    sagaxu  
       39 天前
    @iamtuzi3333 “当集合文档数上百万的时候,这个时候维护索引估计很难”,这是哪里看到的理论啊,再烂的索引,支撑个上亿级别的数据量,也是没有问题的。这个时间戳是固定的,一条数据入库之后就不会更新,不会损失很多性能的,时间戳的分布也很均匀,区分度很高,是个优质索引。
    iamtuzi3333
        35
    iamtuzi3333  
    OP
       39 天前
    @sagaxu 真的吗,那我就开干了,可以在已经插入数据的情况再去建立索引吗
    Mithril
        36
    Mithril  
       39 天前
    @iamtuzi3333 这不就是正常建立索引的东西吗?

    你试试查询的时候 explain 一下,到底是命中索引了,还是直接全扫了。你这个数据量不加索引搜不出来东西的。

    建议好好看看 MongoDB 文档,如何处理时间序列数据。MongoDB 支持建立时序集合的。

    https://www.mongodb.com/zh-cn/docs/manual/core/timeseries-collections/
    wxf666
        37
    wxf666  
       39 天前
    @iamtuzi3333 #31

    你们一天,就要存几千万行,共计 几十 GB 数据吗?


    如,假设 300 设备,每设备每秒 200 单精度浮点,

    算下来,一天就有 300 x 86400 = 2600W 行数据?

    光是存浮点数据,共计就要 2600W x 200 x 4 / 2 ^ 30 = 19.3 GB ?
    wxf666
        38
    wxf666  
       39 天前
    @Mithril #36 这种普通关系型数据库,应该也没问题吧?

    表结构:( 主键:《设备 ID ,毫秒时间戳》,200 个浮点数组)
    otakustay
        39
    otakustay  
       39 天前
    一天 8W 条,一年也才 2400W 数据,怎么都不算多啊,我感觉你就确认下有没有索引,把索引建上,啥都不干查询性能的事情就解决了。内存的问题,就只能换数据库了,随便 mysql 还是 postgre 加个索引也够了
    gongguowei02
        40
    gongguowei02  
       39 天前
    @Geekerstar 这么厉害,我去了解一下,谢谢~
    iamtuzi3333
        41
    iamtuzi3333  
    OP
       39 天前
    @otakustay 目前是没有索引,但是数据集合比较多,有几百个,
    @wxf666 差不多是这个量,后续还会增加设备,采样率会变,就是这个精度会变,这样建立索引方便吗,
    @Mithril 刚才 explain 一下,目前是没有索引,全扫,花了 16 秒,500 多万条文档,下午给字段 checkTime 加上索引试一试看看效果如果。
    Mithril
        42
    Mithril  
       39 天前
    @wxf666 可以是可以,时序数据库的优点是针对时间戳作为主键这种场景做了特殊优化,但其中很多优化手段你在普通的关系型数据库里也能做。其它的你需要修改数据库本身,比如 TimeScale 就是基于 PostgreSQL 改的。

    通用的关系型数据库,一般用的都是行存储。但你这 200 个列基本都不会同时用到,很多时候你只基于其中某个或者某几个数据进行统计。那么以时间戳分片,按列式存储的数据库可能更高效一些。

    你当然可以按照时间范围拆表去限制检索范围,然后以时间戳做主键去做聚类索引。但你这么一套折腾下来效率还是没专门的时序数据库高,完全没必要这么搞。

    就算你想把其它的关系型数据和这些东西放到同一个数据库里,但这些大体积数据的查询也一样会拖累你数据库实例的性能,还不如拆出去呢。
    sospopo101343793
        43
    sospopo101343793  
       39 天前
    一天才 8w 条。。。,mongoDB 完全没问题,根本不用担心性能问题,楼上觉得 mongodb 性能不行,完全是偏见,mongoDB 单 collection 存查 10 亿级别的数据完全没有问题。提前建好索引就行
    2686291180
        44
    2686291180  
       39 天前
    mongodb 性能完全够用了,优化点:
    1. 建索引
    2. 分库分表
    darkengine
        45
    darkengine  
       39 天前
    先建索引啊,之前优化过 mongoDB 的查询,一个索引就搞定。不要担心索引占空间,用空间换时间是常用的优化策略。
    forschers
        46
    forschers  
       39 天前
    我们的硬件数据存在了 Doris 数据库中 传感器数据要响应很快
    brant2ai
        47
    brant2ai  
       39 天前
    找个开源的时序数据库就解你的问题了。如果不想用时序库,上 Doris 也可以
    iamtuzi3333
        48
    iamtuzi3333  
    OP
       39 天前
    @sospopo101343793 一个集合 8 万多,有几百个集合,一起同时写入。不是说性能不好,目前我是没有建立索引,尝试建索引看看效果,目前查询是全集合扫描。
    @2686291180 第二个操作暂时不会,先尝试建立索引看看效果。
    iamtuzi3333
        49
    iamtuzi3333  
    OP
       39 天前
    @forschers 这个数据库第一次听,主要是存储方便,读取简单。
    @brant2ai 第一次听,我查询看看这种。
    abcfyk
        50
    abcfyk  
       39 天前
    600W 总量,每天新增 8W 这数据量不就是洒洒水么? 居然还能有性能瓶颈
    1 、 换数据库
    1.1 时序数据库,比如 TDengine ,InfluxDB 楼上很多人说过了
    1.2 列式数据库,比如 ClickHouse ,Druid 等
    2 、不换数据库
    1.2 磁盘换 SSD 、写前转换成强 schema 数据,建索引,分库、分表等等
    wupher
        51
    wupher  
       39 天前
    - 时间戳加个索引就好了

    - 如果只关心时间,时序数据库更方便

    - 最新的 mongodb 也支持时序库了,但没在生产上使用过
    fengpan567
        52
    fengpan567  
       39 天前
    换 clickhouse
    wenxueywx
        53
    wenxueywx  
       39 天前
    用时序数据库
    iyaozhen
        54
    iyaozhen  
       39 天前
    一天 8W 条 真的是太少了。我见过 1s 几万的。
    如果你 mongo 不太会就用 MySQL 存都行,分库分表或者表分区
    iamtuzi3333
        55
    iamtuzi3333  
    OP
       39 天前
    @abcfyk 一个集合 8 万多,有几百个集合。数据库暂时不换了,目前建了索引,发现速度快了。
    @wupher 也关心数据,目前加了索引,速度起来了。
    @fengpan567 没有用过。

    @wenxueywx 还在调研中。
    @iyaozhen 目前我知道插入不是问题,目前是查询较慢,现在建立了索引,快起来了。
    iamtuzi3333
        56
    iamtuzi3333  
    OP
       39 天前   ❤️ 3
    谢谢各位大佬啦,小弟现在把对应的集合建立了时间戳字段建立了索引,速度立马起来了,优化到几十 ms 级别的查询时间;至于性能这个问题,写入肯定是没有问题,MongoDB 确实很优秀,简单好用;目前唯一的问题就是确实吃内存,这个修改了配置文件的参数目前还是没有办法避免,这个空间换时间确实无法避免;小弟去年刚毕业,来的一家小公司,只有我是搞开发的,领导把整个项目都丢给我了,小弟经验不足,所以有很多不同的地方,还请各位大佬多多指教。
    raptor
        57
    raptor  
       39 天前
    吃内存就加,钱能解决的问题都不是问题
    cavemannb
        58
    cavemannb  
       39 天前
    @iamtuzi3333 #56 既然查询很少,基本的数据库就能满足需求,不需要用到 MongoDB ,把数据放到内存里
    zglzy
        59
    zglzy  
       39 天前
    厚脸皮推荐下 GreptimeDB 是时序数据库,内存也吃得挺少,查询性能不错,还有 pipeline 可以预处理 json 再写入(如果数据结构相对确定的话
    iamtuzi3333
        60
    iamtuzi3333  
    OP
       39 天前
    @cavemannb 数据比较多,基本的关系型还是差点意思,同行业的很多公司是直接存文件,这个难度更大一点。查数据方面。
    @zglzy 感谢,我查查,数据结构比较稳定。
    rickiey
        61
    rickiey  
       39 天前
    如果觉得数据较多,可以尝试将旧数据定时导出备份,使数据库的数据始终保持最近一段时间内的量,需要查询旧数据了,再把备份导入到本地的数据库查询
    celaraze
        62
    celaraze  
       39 天前   ❤️ 2
    @iamtuzi3333 #56 很高兴看到大家的答案能解决你的问题。
    但是还是要提醒一下,索引自然是会加快速度,但是也会加重写入负担(你可以理解为每次写入都会更新索引),一旦你写入数据量大、频次高,写入的开销会很大,记录的数据很难保证原子性(如果有连续的业务处理),我建议你当前尽速解决眼前的问题后,花点时间研究下 TSDB ,毕竟是针对 IoT 场景的特效药。比你给时间戳加索引靠谱的多。
    wxiao333
        63
    wxiao333  
       38 天前
    单个集合就存储一个传感器的数据, 相当于设计上已经将传感器分表了,加时间索引完全没有问题。
    长久来看还是得时序数据库,除了速度快,还有很多优秀功能,比如 influxdb 的连续查询,你可以查任意时间间隔下的特征值,比如你现在时间间隔是 1s 的,我查一周的数据,点太多不好展示,我想切换为间隔为 1 分钟的,那 1 分钟的 60 个的点,是取第一个,最后一个,平均值,最大/小值,都很方便,秒出结果。
    iamtuzi3333
        64
    iamtuzi3333  
    OP
       38 天前
    @rickiey 这个数据对 MongoDB 还算正常,只是比较吃内存。
    @celaraze 是的大佬,目前我给单表的时间戳字段都建立了索引,写入过程需要一直维护这个索引,确实开销大,目前我的写入逻辑是批量写,异步定时每间隔 5 秒写入一批数据,数据接收村存到了 redis 的队列中,后面再从 list 中读取数据入库操作,这里采用了多线程,redis 的分布式锁,保证数据不重复不交叉,暂时应该还好,后续我想着继续把写入逻辑延迟,积累到一定数据量再写入,比如说几百条甚至上万条。小弟目前公司就我一个人,这个项目也是我全程一个人推进的,有点心累,接下来我花时间看看 TDengine 这个数据库,似乎这个还不错,非常谢谢各位大佬提供解决方案,小弟是真的感谢!
    @wxiao333 是的,单集合存单传感器分表这个逻辑比较合理,索引就是维护需要系统开销。目前也在关注时序数据库,大佬说的查询那个功能确实比较优秀,接下来会重点花时间去了解时序数据库,目前物联网比较适合这类数据库,不过就是学习成本有点高,公司不等我哈哈哈。
    wxf666
        65
    wxf666  
       35 天前
    用 SQLite 试了一下,亿级数据,上万并发,好像没啥问题?



    - 单表数据:1.3 亿,100 GB

    - 事务每秒:4.6 W 随机读,1 W 随机写

    - 内存占用:16 MB ( Python 脚本,包括 SQLite 内存缓存)

    - 测试硬件:六七年前轻薄本,SATA 低端固态

    - 测试内容:模拟 500 设备,每秒各保存 200 浮点数据,连续三天



    ## 脚本使用方法

    - 随机写入测试

    ```shell
    # 从上次保存时间戳开始(不存在则为年初),每递增一秒,就写入 500 设备,各 200 浮点数据。直至写入 1W 记录为止
    $ python3 test.py -w -d 设备数(默认 500 ) -n 写入行数(默认 1W )
    ```

    - 随机读取测试

    ```shell
    # 从 500 设备中,随机选一台,再随机选某个时间,取数据。直至读取 1W 记录为止
    $ python3 test.py -r -d 设备数(默认 500 ) -n 读取行数(默认 1W )

    # 最多运行 10 秒
    $ timeout -s INT 10 python3 test.py -r

    # 八进程同时测试
    $ seq 8 | xargs -P 8 -I{} python3 test.py -r
    ```



    ## 测试脚本代码

    ```python
    # V 站吞空格,缩进改为全角空格了

    import time
    import apsw
    import random
    import struct
    import argparse
    import itertools
    from datetime import datetime, timezone

    DEFAULT_DEVICES = 500
    DEFAULT_RECORDS = 10000
    SQLITE_MAX_WAL_PAGES = 10000
    DB_PATH = '/数据库路径/文件名.db'
    DEFAULT_START_TIME = int(datetime.strptime('2024-01-01 00:00:00', '%Y-%m-%d %H:%M:%S').replace(tzinfo=timezone.utc).timestamp())

    count = 0
    db: apsw.Connection
    devices = DEFAULT_DEVICES
    records = DEFAULT_RECORDS
    dev_time_range: list[range] = []


    def parse_args():
       parser = argparse.ArgumentParser(description="SQLite 测试读写多传感器数据")
       group = parser.add_mutually_exclusive_group(required=True)
       group.add_argument('-r', action='store_true', help="随机读取")
       group.add_argument('-w', action='store_true', help="随机写入")
       parser.add_argument('-d', type=int, default=DEFAULT_DEVICES, help=f"设备数(默认 {DEFAULT_DEVICES})")
       parser.add_argument('-n', type=int, default=DEFAULT_RECORDS, help=f"要测试的记录数(默认 {DEFAULT_RECORDS})")

       global devices, records
       args = parser.parse_args()
       devices = args.d
       records = args.n
       return args


    # 随机写的页面足够多时,确保落盘并重置 WAL 文件
    def sqlite3_wal_hook(db: apsw.Connection, name: str, pages: int):
       if pages > SQLITE_MAX_WAL_PAGES:
         db.wal_checkpoint(mode=apsw.SQLITE_CHECKPOINT_RESTART)
       return apsw.SQLITE_OK


    def init_db():
       global db
       db = apsw.Connection(DB_PATH)
       db.execute('PRAGMA journal_mode = WAL')
       db.execute('PRAGMA busy_timeout = 5000')
       db.execute('PRAGMA synchronous = NORMAL')
       db.setwalhook(sqlite3_wal_hook)

       db.execute('''
         CREATE TABLE IF NOT EXISTS device_data (
           id     INTEGER PRIMARY KEY,
           dev_id   AS (id >> 32),
           created AS (id & 0xFFFFFFFF),
           data    BLOB
        )
      ''')


    # 获取每个设备,已保存数据的时间范围
    def get_dev_time_range():
       rows = db.execute('''
         SELECT dev_min.created, dev_max.created + 1
         FROM (SELECT (max(id) >> 32) + 1 dev_count FROM device_data)
         JOIN json_each(REPLACE(FORMAT('[%*.*s]', dev_count, dev_count, '0'), ' ', '0,')) dev
         JOIN device_data dev_min ON dev_min.id = (SELECT min(id) FROM device_data WHERE id >= dev.key << 32)
         JOIN device_data dev_max ON dev_max.id = (SELECT max(id) FROM device_data WHERE id <= dev.key << 32 | 0xFFFFFFFF)
      ''').fetchall()

       dev_time_range.extend(list(itertools.starmap(range, rows))[:devices])
       dev_time_range.extend([range(DEFAULT_START_TIME, DEFAULT_START_TIME)] * max(devices - len(rows), 0))


    def test_read():
       global count
       items = list(enumerate(dev_time_range))
       weights = list(itertools.accumulate(map(lambda i: i.stop - i.start, dev_time_range)))

       while count < records:
        # 以每设备时长为权重,随机抽取一个设备,再从其时间范围随机抽取时间点
         dev, time_range = random.choices(items, cum_weights=weights)[0]
         db.execute('''
           SELECT data
           FROM device_data
           WHERE id = ? << 32 | ?
        ''', (dev, random.choice(time_range))).fetchone()
         count += 1


    def test_write():
       global count
       start_time = min(dev_time_range, key=lambda i: i.stop).stop

       for ts in itertools.count(start_time):
         for dev in range(devices):
           if count >= records:
             return
           elif ts in dev_time_range[dev]:
             continue

           floats = [random.random() for i in range(200)]
           data = struct.pack('200f', *floats)

           db.execute('BEGIN IMMEDIATE')
           db.execute('''
             INSERT INTO device_data (id, data)
             VALUES (? << 32 | ?, ?)
          ''', (dev, ts, data))
           db.execute('COMMIT')
           count += 1


    def test(is_read: bool):
       init_db()
       get_dev_time_range()
       start_time = time.time()
       try:
         test_read() if is_read else test_write()
       except KeyboardInterrupt:
         pass
       finally:
         duration = time.time() - start_time
         print(f'在 {duration:6.2f} 秒内,随机{"写读"[is_read]} {count:6d} 行,平均 {count / duration:8.2f} 行/秒')


    if __name__ == '__main__':
       args = parse_args()
       test(args.r)
    ```



    ## 1.3 亿 100 GB 数据库,文件结构信息分析

    iamtuzi3333
        66
    iamtuzi3333  
    OP
       27 天前
    @wxf666 哇塞,首先谢谢大佬指点,大佬很强,不好意思,我假期没看论坛。目前我是建了索引,基本上解决问题。看了您的测试,太强了,我深感自己缺少这个精神,汗颜。不过我觉得单表不适合多传感器数据的存储,一开始我就 pass 了,数据太过分散,不方便后续读取维护。sqllite 我看到了乙方存的是记录,他们用文件存数据,然后有记录索引,用起始位置来标记数据,这个方案比较难,对我个人来说;所以就考虑用 mongoDB ,现在确实好用,有了索引查询效率瞬间上来了,之前占用内存大可能因为写入较频繁,每秒实时写入。我现在改成了异步延时写入,一开始存到了 redis 的 list ,然后再去 list 取数据写入到数据库,算是减少了内存消耗。不过大佬的实践很强,有时间我试试该数据库以及您说的方案,再次感谢大佬的指点!!!
    wxf666
        67
    wxf666  
       26 天前
    @iamtuzi3333 #66

    1. 《数据太过分散,不方便后续读取维护》是啥意思呢。。

    这个测试的主键类似于(设备 ID ,时间戳),如果你要查今天所有设备的数据,可以:

    ```sql
    SELECT *
    FROM data
    JOIN generate_series(1, 500) 设备
    WHERE 设备 ID = 设备.value
    AND 时间戳 BETWEEN '今天' AND '现在'
    ```

    我觉得单表数据过大,需要担心的是 B+ 树层级过高,每次读取需要再耗费一个 IO 。。



    2. 测试中,我也是用了类似《先缓存写入的行,一定数量后再刷写回数据库》方法,提高写事务速度的。

    设定缓存最多 1W 行,平均每设备 20 行,每 5 行一个 4K 页,即每设备 16KB ,

    所以就能参考 16KB 随机写速度了。。
    iamtuzi3333
        68
    iamtuzi3333  
    OP
       26 天前
    @wxf666 我的意思是单表存一个传感器设备的数据,这样相对来说方便后续读取,数据字段可能还会添加。查询这个还好,现在加上了索引,即使我查询前几个月的数据也能够很快就响应了。写入这个问题暂时不管了,mongo 数据库就是吃内存,空间换时间了,还是谢谢大佬的指点,很强,这个测试能力。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   982 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 22:10 · PVG 06:10 · LAX 14:10 · JFK 17:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.