从建模思路看 MySQL 和 TDengine 哪个更适合处理量化交易场景下的海量时序数据

在“量化投资分析”场景中,系统需要从数据接口、网络上等各个地方获取证券的信息,其中往往以“实时的价格变动信息”为主要数据,然后再对这些数据进行实时的分析与存储,供盘中和盘后使用。某企业遇到的问题如下:“我们要对 500 个证券品种进行监控,在开盘时,每 5 秒会更新一次价格数据。这样算下来的话,每个证券品种一天就会产生 2880 条记录,如果是 500 个的话,就会有 144 万条数据。而这,还仅仅是一天中产生的数据。如果使用 MySQL 数据库,我们该如何设计数据库和表,来承载这样的数据量呢?”

从上述场景及问题出发,我们邀请到 TDengine 解决方案架构师进行回复,并产出本篇文章。

144 万条的数据量对于关系型数据库来说,确实是个有一定规模的日增量。但从场景上看,上述问题场景还算不上「量化分析投资」的核心,只能称之为数据抓取的场景。其中抓取对象为「证券」,规模 N = 500, 抓取时间间隔 T = 5s。我们可以假设每次抓取的数据有:

{scrawlTime: '2023-01-01 00:00:00'stock_code: 12345,price: 12.00,volumn: 134,bid_price_1: 12.01,bid_pridce_2: 12.02}

如果要与常见的场景进行类比,可以使用 IT 服务器的运维监控对比。数据如下:

{timestamp: '2023-01-01 00:00:00'ip: '172.16.8.1',cpu_usage: 0.81,memory_usage: 0.23}

通过上述对比我们可以看到,两种场景很相似。因此,从概念上讲,上述问题场景下的监控数据可以归纳为 metric —— 测量值,并且是随时间变化的。这是很典型的时序数据,问题场景就是一种经典的时序数据存储场景。

基于 MySQL 的建模

如果企业要用 MySQL 的话,其实核心要考虑的问题应该是

  • 如何保证能够及时写入:500 rows/5s = 100 rows/s(但这个基本不是问题)。
  • 如何保证能够快速查出?从 IT 运维看,常见的查询包括:
    • 查询单个证券:
      • 基于时间范围查询:ts in [startTs, endTs)
      • 基于监控值的过滤:WHERE bid_price_1 >= 10.00;
      • 最新值查询:ORDER BY ts DESC LIMIT 1
    • 查询多个证券:在单个证券相同的情况下,只需要更快地返回,能在 1 个查询里返回更好。
    • 基于时间的计算:
      • 滑动窗口:如 5 日均线图
      • 状态窗口:根据成交量分段统计
      • 。。。

基于以上的查询场景,我们可以选择两大路线:

  • N 个证券,每 K 个证券,放在 1 个 table 中
    • K = 1 时,相当于 1 个证券 1 个 table
    • K = N 时,相当于用 1 个 table 存放所有数据

假设你使用 InnoDB 引擎(innodb_百度百科),不管怎么选,为了性能你都会建索引。而 InnoDB 的索引使用 B-Tree 结构,这个数据结构在 Rows > 2000w (经验值)时,数据写入会因为索引的维护成本上升而下降,查询性能也一样。只是 K = 1 的时候,这个问题才没那么明显:

2000w / 2880/day = 6944 days = 19 years

也就是说 1 个证券 1 个 table 的时候,存放 19 个自然年数据时,才会明显感知到。

当然,我们对这个问题有另外一种处理方法:按照时间(一般以天为单位)在进行分表(或分库):

  • N 个 证券,每 K 个证券,每 D 天 放在 1 个 table
    • 当 K = 1 时
      • D = 1,相当于 1 个证券 1 天 1 个 table。1 年下来有 N/K x 365 = 182,500 个 table。
      • D = 30, 相当于 1 个证券 30 天 1 个 table。1 年下来 有 6083 个 table。
      • D = INF,相当于 1 个证券 1 个 table。
    • K = N 时,相当于用 1 个 table 存放所有数据
      • D = 1,相当于所有证券 1 天 1 个 table。1 年下来 365 个 table。
      • D = 30, 相当于 30 天 1 个 table。 1 年下来 12 个 table。
      • D = INF,相当于 1 个 table。

这种方式在一定程度上也能有效避免问题,但是分库分表还会引来查询侧改造的工作量,仍然无法彻底解决问题。但是如果我们换用专用的时序数据库,就能更好地解决这个问题。

基于 TDengine 建模

TDengine 作为国内 Top 的开源时序数据库,产品定位为「分布式时序数据库」,产品功能专门针对时序数据场景设计和优化,已经被广泛运用于金融、车联网、工业互联网等时序数据场景中。已经落地的「量化投资分析」场景方案有《TDengine在同花顺组合管理业务中的优化实践》《TDengine 在弘源泰平量化投资中的实践》

回到上面基于 MySQL 的建模思路,TDengine 的设计里面,也是 1 个证券 1 个 table 的理念,通过超级表(stable)的语法糖,快速并行查询多个证券的数据;同时针对常见的业务查询场景做了定向的性能优化,从而保证在「海量」数据的情况下,性能依旧表现坚挺;而且还设置了很多有趣的特性,助力时序处理更加简单。

标准 SQL 语法

TDengine 支持标准 SQL 语法,比老一代的时序数据库,具备更好的上手体验。

TDengine Database

动态与静态数据分离

在 TDengine 当中,超级表(stable)结构引入了标签(tag)的概念,这样一来,我们可以把证券的维度信息放在标签当中,减少数据存储空间,提升查询性能。同时,在建模上采用 1 个证券 1 张表的方式,以此保证高性能读写。

TDengine Database

通过超级表语法糖,TDengine 实现了并行查询的能力,大大减少 SQL 的复杂度:

TDengine Database

以上便是两种数据库对于上述问题场景的具体解决思路,你觉得如果是你会选择哪一种呢?可以在评论区进行留言,一起讨论。

总而言之,不管是传统的关系型数据库,还是 NoSQL 数据库,如果我们没有针对性地去对应时序数据特点,在性能提升上极为有限,只能依靠集群技术,投入更多的计算资源和存储资源来处理,系统的运营维护成本也会因此急剧上升。如果你也面临着海量时序数据处理难题,不妨可以加一下小T vx:tdengine1,进入 TDengine 用户交流群,和大家一起来探讨解决路径。