量化交易場景下日增 144 萬條資料，使用 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 的索引使用 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 的開源時序資料庫，產品定位為「分散式時序資料庫」，產品功能專門針對時序資料場景設計和最佳化，已經被廣泛運用於金融、車聯網、工業網際網路等時序資料場景中。已經落地的「量化投資分析」場景方案有 、 等 。

回到上面基於 MySQL 的建模思路，TDengine 的設計裡面，也是 1 個證券 1 個 table 的理念，透過超級表（stable）的語法糖，快速並行查詢多個證券的資料；同時針對常見的業務查詢場景做了定向的效能最佳化，從而保證在「海量」資料的情況下，效能依舊錶現堅挺；而且還設定了很多有趣的特性，助力時序處理更加簡單。

標準 SQL 語法

TDengine 支援標準 SQL 語法，比老一代的時序資料庫，具備更好的上手體驗。

動態與靜態資料分離

在 TDengine 當中，超級表（stable）結構引入了標籤（tag）的概念，這樣一來，我們可以把證券的維度資訊放在標籤當中，減少資料儲存空間，提升查詢效能。同時，在建模上採用 1 個證券 1 張表的方式，以此保證高效能讀寫。

透過超級表語法糖，TDengine 實現了並行查詢的能力，大大減少 SQL 的複雜度：

以上便是兩種資料庫對於上述問題場景的具體解決思路，你覺得如果是你會選擇哪一種呢？可以在評論區進行留言，一起討論。

總而言之，不管是傳統的關係型資料庫，還是 NoSQL 資料庫，如果我們沒有針對性地去對應時序資料特點，在效能提升上極為有限，只能依靠叢集技術，投入更多的計算資源和儲存資源來處理，系統的運營維護成本也會因此急劇上升。如果你也面臨著海量時序資料處理難題，不妨可以加一下小T vx：tdengine1，進入 TDengine 使用者交流群，和大家一起來探討解決路徑。