在“量化投资分析”场景中,系统需要从数据接口、网络上等各个地方获取证券的信息,其中往往以“实时的价格变动信息”为主要数据,然后再对这些数据进行实时的分析与存储,供盘中和盘后使用。某企业遇到的问题如下:“我们要对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的话,其实核心要考虑的问题应该是
如何保证能够及时写入:500rows/5s=100rows/s。如何保证能够快速查出?从IT运维看,常见的查询包括:查询单个证券:基于时间范围查询:tsin[startTs,endTs)基于监控值的过滤:WHEREbid_price_1>=00;最新值查询:ORDERBYtsDESCLIMIT1查询多个证券:在单个证券相同的情况下,只需要更快地返回,能在1个查询里返回更好。基于时间的计算:滑动窗口:如5日均线状态窗口:根据成交量分段统计。。。
基于以上的查询场景,我们可以选择两大路线:
N个证券,每K个证券,放在1个table中K=1时,相当于1个证券1个tableK=N时,相当于用1个table存放所有数据
假设你使用InnoDB引擎,不管怎么选,为了性能你都会建索引。而InnoDB的索引使用B-Tree结构,这个数据结构在Rows>2000w时,数据写入会因为索引的维护成本上升而下降,查询性能也一样。只是K=1的时候,这个问题才没那么明显:
也就是说1个证券1个table的时候,存放19个自然年数据时,才会明显感知到。
我们对这个问题有另外一种处理方法:按照时间在进行分表:
N个证券,每K个证券,每D天放在1个table当K=1时D=相当于1个证券1天1个table。1年下来有N/Kx365=182,500个table。D=30,相当于1个证券30天1个table。1年下来有6083个table。D=INF,相当于1个证券1个table。K=N时,相当于用1个table存放所有数据D=相当于所有证券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的理念,通过超级表的语法糖,快速并行查询多个证券的数据;同时针对常见的业务查询场景做了定向的性能优化,从而保证在「海量」数据的情况下,性能依旧表现坚挺;而且还设置了很多有趣的特性,助力时序处理更加简单。
标准SQL语法
TDengine支持标准SQL语法,比老一代的时序数据库,具备更好的上手体验。
动态与静态数据分离
在TDengine当中,超级表结构引入了标签的概念,这样一来,我们可以把证券的维度信息放在标签当中,减少数据存储空间,提升查询性能。在建模上采用1个证券1张表的方式,以此保证高性能读写。
通过超级表语法糖,TDengine实现了并行查询的能力,大大减少SQL的复杂度:
以上便是两种数据库对于上述问题场景的具体解决思路,你觉得如果是你会选择哪一种呢?可以在评论区进行留言,一起讨论。
文章为作者独立观点,不代表股票交易接口观点