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

索引建了吗？数据文档的字段是否都是一致的，是的话用单字段索引，不是的话用稀疏索引。试试，按照你的描述很可能没有索引导致全集合扫描了。

postgres 不就可以么？

@celaraze 索引没建立，我是按照一个时间戳字段去查询，这个值一直在变，没法用索引吧。
@flmn 我查一下这个。

@celaraze 文档的字段都是一致的，估计是，因为文档数量非常的多，每秒一条，一天有 8 万多条了。

采集的数据吗, 我们乙方用的时序数据库(TDengine), 不存数组, 按采集点位存的数据, 每个时间每台设备每个点位一条数据, 暂时没什么问题(但是我没具体了解过我们对性能的要求)

1.MongoDB 本来就非常吃内存，高性能都是建立在内存的基础上的
2.给时间戳字段加上索引
3.看一下单表数据量，实在太多考虑将查询功能放到 ES(需要评估)

@iamtuzi3333 如果按照时间戳查询，建议用时序数据库（ TSDB ），把你要存储的值当作度量处理。你这个场景感觉 IoT 方向啊，怎么会选型用 mongo 。

@corcre 一个点位的一个数的话时序数据库很方便，但是数据就很麻烦，我之前找过 influxdb ，感觉不太行。

@corcre 你的建议是对的。

@celaraze 数据是 loT 。但是时序数据库一个麻烦的点就是我没有找到有很好的字段支持，很多时序数据库都是一个时间点对应一个值，我的数据字段比较多，还有数组类型在里面，不好处理。
@seedhk 时间戳的值会一直变，这样索引更新不是更加消耗资源吗，单表数据量目前是 600 多万条文档了，每天 8 多万增加。

我们已经转时序 tdengine 了，10w 台设备，频率每天 1-24 条不等，目前没啥问题

@yoyolichen 数据字段是一个值吗，就一个时间字段映射一个值？ 现在麻烦的就是字段特别多，不同的类型也多，有数组这些。

clickhouse 试试呢

@iamtuzi3333 不知道你测试的是哪个数据库，例如 influxDB 中 Point 可以包含多个 Fields 的。一条记录多个属性值。试试看吧，IoT 都用时序的。

mongodb 5.0 之后也支持时序功能
https://www.mongodb.com/zh-cn/docs/manual/core/timeseries-collections/
建议先去尝试优化

@celaraze 我先搜一下，只有我一个人，顶着公司走，难受。。。
@Desdemor 似乎接口有点复杂，我先看看。
@zhazi 好的，我先去试试优化，其实 MongoDB 存是非常的方便，就是吃内存很严重，即使修改了配置文件的内容。

你不建索引，每一次的查询都是全集合扫描，随着数据越多，查询只会越来越慢，索引带来的内存和 CPU 消耗绝对比你每一次都是全集合扫描少得多，索引是肯定利大于弊的，用 MongoDB 的话，数据量实在太大，可以上分片

每次看到使用 MongoDB 的，我就想和他说一句“没有银弹”。。。

这个肯定要用时序数据库了.

MongoDB 用了多少内存

IoT 不涉及到修改数据吧，什么叫“时间戳的值会一直变”？难道你会频繁修改已经入库数据的时间戳？

高性能 与 free scheme ，低内存那是天然冲突。。。

时间戳字段为什么一直会变？监控数据不是一直追加上去的吗？只存储设备的当前状态，不断 update 吗？

所有 DB 本质都是一样的，查找数据要么遍历，要么走索引，数据量一大，遍历必然是很慢的，要通过高区分度的索引加速查找，若没法建立高质量索引，那只能 case by case 特别优化了

@sunxvvv 现在有一个字段是 int 整形，存储的是当前时间戳的值，每条写入的时候都有会，这样子适合建立吗，真实查询数据的场景不是很多，偶尔需要查询。
@halov 直接占了快 60G ，同时他的 mapper file 会持续上涨，备用内存全是这个内容，导致可用内存最后剩下不到 100M ，服务器 192GB 的内存都给干满了。
@Mithril 是的，不涉及修改，就是现在数据入库我为了查询方便，就加了一个字段，这个字段存的值就是当前时间戳的值。
@encro 唉，难受，这个确实没办法完美解决，而且其他数据库适合存的情况下接口又不方便。哭了

@sagaxu 数据不是追加的，每秒都会有新的数据入库，设备数据采集频率很高，所以数据很多，比如说 200Hz ，就说明每秒都有一条数据出来，其中的 data 数组就存储了 200 个浮点数，这种情况下如何编写索引，遍历随着数据量增长，到后面基本没办法找去年的数据出来了。

用时序数据库没问题阿，点多有什么关系，数组类型的可以拆开存吧

“数据其实很简单，但是量很多，都是传感器数据，现在每秒都有数据入库”

这是典型的时序数据库需求，可以试试

@iamtuzi3333 #24

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

（ ID ，设备 ID ，毫秒时间戳，浮点数据），其中加了（设备 ID ，毫秒时间戳）索引，

你查询需求是：某设备，在某时间段内的，所有浮点数据？

@iamtuzi3333 时间戳字段建个索引试试看吧，数据量大，新增索引可能要建个几分钟几十分钟的。建完索引，再查查看，如果还慢，把查询计划贴出来分析分析。

@lancelock 有一个关键字段就是数组，里面有 200 个元素，就是拆除 200 个 key 对应 value 么
@LieEar 开源的 influxdb 想试试，刚看了一下，发现还行，就是要拆数据字段，不知道查询效果怎么样，比如说多传感器能不能多表存储，就一个传感器一张表

@wxf666 是的，浮点数据这个字段是一个数组，里面数量就看设备的采样率决定。查询需要翻找某一段时间的所有浮点数数据出来。
@sagaxu 主要是更新索引很耗资源，当集合文档数上百万的时候，这个时候维护索引估计很难，而且不止一个集合，有几百个传感器

我用的 TDengine ，目前几十亿数据，查询很快

@corcre 专业的事情 专业的数据库干

@iamtuzi3333 “当集合文档数上百万的时候，这个时候维护索引估计很难”，这是哪里看到的理论啊，再烂的索引，支撑个上亿级别的数据量，也是没有问题的。这个时间戳是固定的，一条数据入库之后就不会更新，不会损失很多性能的，时间戳的分布也很均匀，区分度很高，是个优质索引。

@sagaxu 真的吗，那我就开干了，可以在已经插入数据的情况再去建立索引吗

@iamtuzi3333 这不就是正常建立索引的东西吗？

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

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

https://www.mongodb.com/zh-cn/docs/manual/core/timeseries-collections/

@iamtuzi3333 #31

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


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

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

光是存浮点数据，共计就要 2600W x 200 x 4 / 2 ^ 30 = 19.3 GB ？

@Mithril #36 这种普通关系型数据库，应该也没问题吧？

表结构：（ 主键：《设备 ID ，毫秒时间戳》，200 个浮点数组）

一天 8W 条，一年也才 2400W 数据，怎么都不算多啊，我感觉你就确认下有没有索引，把索引建上，啥都不干查询性能的事情就解决了。内存的问题，就只能换数据库了，随便 mysql 还是 postgre 加个索引也够了

@Geekerstar 这么厉害，我去了解一下，谢谢~

@otakustay 目前是没有索引，但是数据集合比较多，有几百个，
@wxf666 差不多是这个量，后续还会增加设备，采样率会变，就是这个精度会变，这样建立索引方便吗，
@Mithril 刚才 explain 一下，目前是没有索引，全扫，花了 16 秒，500 多万条文档，下午给字段 checkTime 加上索引试一试看看效果如果。

@wxf666 可以是可以，时序数据库的优点是针对时间戳作为主键这种场景做了特殊优化，但其中很多优化手段你在普通的关系型数据库里也能做。其它的你需要修改数据库本身，比如 TimeScale 就是基于 PostgreSQL 改的。

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

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

就算你想把其它的关系型数据和这些东西放到同一个数据库里，但这些大体积数据的查询也一样会拖累你数据库实例的性能，还不如拆出去呢。

一天才 8w 条。。。，mongoDB 完全没问题，根本不用担心性能问题，楼上觉得 mongodb 性能不行，完全是偏见，mongoDB 单 collection 存查 10 亿级别的数据完全没有问题。提前建好索引就行

mongodb 性能完全够用了，优化点：
1. 建索引
2. 分库分表

先建索引啊，之前优化过 mongoDB 的查询，一个索引就搞定。不要担心索引占空间，用空间换时间是常用的优化策略。

我们的硬件数据存在了 Doris 数据库中 传感器数据要响应很快

找个开源的时序数据库就解你的问题了。如果不想用时序库，上 Doris 也可以

@sospopo101343793 一个集合 8 万多，有几百个集合，一起同时写入。不是说性能不好，目前我是没有建立索引，尝试建索引看看效果，目前查询是全集合扫描。
@2686291180 第二个操作暂时不会，先尝试建立索引看看效果。

@forschers 这个数据库第一次听，主要是存储方便，读取简单。
@brant2ai 第一次听，我查询看看这种。

600W 总量，每天新增 8W 这数据量不就是洒洒水么？ 居然还能有性能瓶颈
1 、 换数据库
1.1 时序数据库，比如 TDengine ，InfluxDB 楼上很多人说过了
1.2 列式数据库，比如 ClickHouse ，Druid 等
2 、不换数据库
1.2 磁盘换 SSD 、写前转换成强 schema 数据，建索引，分库、分表等等

- 时间戳加个索引就好了

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

- 最新的 mongodb 也支持时序库了，但没在生产上使用过

换 clickhouse

用时序数据库

一天 8W 条 真的是太少了。我见过 1s 几万的。
如果你 mongo 不太会就用 MySQL 存都行，分库分表或者表分区

@abcfyk 一个集合 8 万多，有几百个集合。数据库暂时不换了，目前建了索引，发现速度快了。
@wupher 也关心数据，目前加了索引，速度起来了。
@fengpan567 没有用过。

@wenxueywx 还在调研中。
@iyaozhen 目前我知道插入不是问题，目前是查询较慢，现在建立了索引，快起来了。

谢谢各位大佬啦，小弟现在把对应的集合建立了时间戳字段建立了索引，速度立马起来了，优化到几十 ms 级别的查询时间；至于性能这个问题，写入肯定是没有问题，MongoDB 确实很优秀，简单好用；目前唯一的问题就是确实吃内存，这个修改了配置文件的参数目前还是没有办法避免，这个空间换时间确实无法避免；小弟去年刚毕业，来的一家小公司，只有我是搞开发的，领导把整个项目都丢给我了，小弟经验不足，所以有很多不同的地方，还请各位大佬多多指教。

吃内存就加，钱能解决的问题都不是问题

@iamtuzi3333 #56 既然查询很少，基本的数据库就能满足需求，不需要用到 MongoDB ，把数据放到内存里

厚脸皮推荐下 GreptimeDB 是时序数据库，内存也吃得挺少，查询性能不错，还有 pipeline 可以预处理 json 再写入（如果数据结构相对确定的话

@cavemannb 数据比较多，基本的关系型还是差点意思，同行业的很多公司是直接存文件，这个难度更大一点。查数据方面。
@zglzy 感谢，我查查，数据结构比较稳定。

如果觉得数据较多，可以尝试将旧数据定时导出备份，使数据库的数据始终保持最近一段时间内的量，需要查询旧数据了，再把备份导入到本地的数据库查询

@iamtuzi3333 #56 很高兴看到大家的答案能解决你的问题。
但是还是要提醒一下，索引自然是会加快速度，但是也会加重写入负担（你可以理解为每次写入都会更新索引），一旦你写入数据量大、频次高，写入的开销会很大，记录的数据很难保证原子性（如果有连续的业务处理），我建议你当前尽速解决眼前的问题后，花点时间研究下 TSDB ，毕竟是针对 IoT 场景的特效药。比你给时间戳加索引靠谱的多。

单个集合就存储一个传感器的数据, 相当于设计上已经将传感器分表了，加时间索引完全没有问题。
长久来看还是得时序数据库，除了速度快，还有很多优秀功能，比如 influxdb 的连续查询，你可以查任意时间间隔下的特征值，比如你现在时间间隔是 1s 的，我查一周的数据，点太多不好展示，我想切换为间隔为 1 分钟的，那 1 分钟的 60 个的点，是取第一个，最后一个，平均值，最大/小值，都很方便，秒出结果。

@rickiey 这个数据对 MongoDB 还算正常，只是比较吃内存。
@celaraze 是的大佬，目前我给单表的时间戳字段都建立了索引，写入过程需要一直维护这个索引，确实开销大，目前我的写入逻辑是批量写，异步定时每间隔 5 秒写入一批数据，数据接收村存到了 redis 的队列中，后面再从 list 中读取数据入库操作，这里采用了多线程，redis 的分布式锁，保证数据不重复不交叉，暂时应该还好，后续我想着继续把写入逻辑延迟，积累到一定数据量再写入，比如说几百条甚至上万条。小弟目前公司就我一个人，这个项目也是我全程一个人推进的，有点心累，接下来我花时间看看 TDengine 这个数据库，似乎这个还不错，非常谢谢各位大佬提供解决方案，小弟是真的感谢！
@wxiao333 是的，单集合存单传感器分表这个逻辑比较合理，索引就是维护需要系统开销。目前也在关注时序数据库，大佬说的查询那个功能确实比较优秀，接下来会重点花时间去了解时序数据库，目前物联网比较适合这类数据库，不过就是学习成本有点高，公司不等我哈哈哈。

用 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 数据库，文件结构信息分析

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

@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 随机写速度了。。

@wxf666 我的意思是单表存一个传感器设备的数据，这样相对来说方便后续读取，数据字段可能还会添加。查询这个还好，现在加上了索引，即使我查询前几个月的数据也能够很快就响应了。写入这个问题暂时不管了，mongo 数据库就是吃内存，空间换时间了，还是谢谢大佬的指点，很强，这个测试能力。