从JSON迁移到SQLite存储K线数据 — 工程笔记

Engineering 技术笔记 — 第1卷

从JSON迁移到SQLite存储OHLC数据

问题背景

DaPex Terminal为30多个交易品种展示7个时间周期（1分钟、5分钟、15分钟、1小时、4小时、1天、1周）的OHLC蜡烛图。每张图表背后都需要历史价格数据，并满足以下要求：

• 查询速度足够快，实现50ms以内的页面加载
• 每分钟从MT5接收新tick并更新数据
• 实时向上聚合生成更高时间周期的数据
• 保留3个月数据，之后自动清理

我们的初始实现将每个品种-时间周期组合存储为独立的JSON文件：

data/klines/
  XAUUSD_1m.json    (2.1 MB, 28,000 行)
  XAUUSD_5m.json    (0.5 MB, 5,600 行)
  XAUUSD_1h.json    (0.1 MB, 720 行)
  EURUSD_1m.json    (1.8 MB, 24,000 行)
  ...
  // 30个品种 x 7个时间周期 = 210个文件

这种方式在启动初期运行良好。但到了第三周，问题开始显现。

三种故障模式

1. 写入竞争

当MT5推送新tick时，一个Python工作进程会打开JSON文件，解析全部28,000条记录，追加一条数据，将整个数组序列化回磁盘，然后关闭文件。在波动剧烈的市场中，10个活跃品种每秒产生6个以上tick时，文件系统I/O成为瓶颈。Flask请求线程会因等待文件锁而阻塞，导致API响应时间从50ms飙升至2秒以上。

2. 原子性失败

JSON文件写入并非原子操作。如果服务器在写入过程中崩溃（云实例因电源波动发生过两次），文件会被截断——丢失自上次备份以来的所有数据。我们不得不从MT5历史数据重放来恢复，每个品种耗时45分钟以上。

3. 查询性能崩溃

从60个1分钟蜡烛图构建1根1小时蜡烛图，需要解析60个JSON文件，在内存中合并并聚合。对于显示100根小时蜡烛图（涵盖6,000分钟数据）的图表，前端平均等待850ms。作为参考，Google Core Web Vitals将100ms以上的响应标记为问题。

备选方案评估

SQLite在三个方面胜出：嵌入式运行（无需独立进程）、支持ACID事务（不再丢失数据）、WAL模式下仅占用600KB内存——仅占我们1GB服务器的0.06%。

迁移过程

表结构设计

关键思路是将原始tick数据与聚合后的蜡烛图数据分离。我们只存储1分钟蜡烛图作为源数据，所有更高时间周期通过SQL聚合计算：

CREATE TABLE kline_1m (
    symbol    TEXT NOT NULL,    -- XAUUSD, EURUSD
    ts        INTEGER NOT NULL, -- 蜡烛图开盘Unix时间戳
    open      REAL NOT NULL,
    high      REAL NOT NULL,
    low       REAL NOT NULL,
    close     REAL NOT NULL,
    volume    INTEGER DEFAULT 0,
    PRIMARY KEY (symbol, ts)
);

CREATE INDEX idx_kline_symbol_ts ON kline_1m(symbol, ts);

使用此表结构，计算任何更高时间周期只需一条查询：

-- 从1分钟数据生成1小时蜡烛图
SELECT
    (ts / 3600) * 3600 AS hour_ts,
    symbol,
    FIRST_VALUE(open) OVER w AS open,
    MAX(high) OVER w AS high,
    MIN(low) OVER w AS low,
    LAST_VALUE(close) OVER w AS close,
    SUM(volume) OVER w AS volume
FROM kline_1m
WHERE symbol = ? AND ts BETWEEN ? AND ?
WINDOW w AS (PARTITION BY (ts / 3600) * 3600 ORDER BY ts);

迁移脚本

我们编写了一次性迁移脚本，执行以下操作：

1. 分块读取每个JSON文件（避免一次性加载导致内存峰值）
2. 按（品种，时间戳）去重——JSON文件因重启边界情况累积了3%的重复数据
3. 使用BEGIN/COMMIT事务批量插入，每批500行
4. 迁移后验证行数匹配
5. 保留JSON文件作为备份72小时，之后删除

import json, sqlite3, os, glob

conn = sqlite3.connect("klines.db")
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")

batch = []
total = 0

for fpath in sorted(glob.glob("data/klines/*.json")):
    symbol = os.path.basename(fpath).split("_")[0]
    with open(fpath) as f:
        rows = json.load(f)

    for row in rows:
        batch.append((
            symbol, row["ts"], row["o"], row["h"],
            row["l"], row["c"], row.get("v", 0)
        ))

        if len(batch) >= 500:
            conn.executemany(
                "INSERT OR IGNORE INTO kline_1m VALUES (?,?,?,?,?,?,?)",
                batch
            )
            conn.commit()
            total += len(batch)
            batch = []

# 处理最后一批
if batch:
    conn.executemany("INSERT OR IGNORE INTO kline_1m ...", batch)
    conn.commit()

print(f"已迁移 {total} 行数据")

该脚本在生产服务器上运行了12秒。我们通过并行查询验证了JSON与SQLite中的行数一致，72小时后删除了JSON目录。

迁移结果

改进建议

1. 从第一天起就使用SQLite。 我们浪费了3周时间调试JSON文件锁问题，而SQLite原生就解决了这个问题。600KB的内存成本即使在1GB服务器上也可忽略不计。
2. 立即启用WAL模式。 我们最初使用DELETE日志模式，导致写入时阻塞读取。切换到WAL（预写日志）模式后，实现了写入时的并发读取——这对在tick到达时同时提供图表服务至关重要。
3. 批量插入。 我们的初始实现是每个tick单独INSERT。将每批500行作为一个事务，写入吞吐量提升了40倍。
4. 第一天就添加数据保留定时任务。 我们第一个月忘记清理旧数据。现在通过crontab中的简单DELETE FROM kline_1m WHERE ts < strftime('%s','now','-3 months')自动处理。

为什么不选PostgreSQL？

我们经常被问到这个问题。PostgreSQL是一款优秀的数据库。但对于需要在1GB内存、每月2美元的VPS上运行的单服务器交易终端来说，它并不合适。PostgreSQL仅共享缓冲区的最小可行内存占用就约200MB。而SQLite进程内运行仅需约600KB。对于我们的用例，这是333倍的差距。

代价是SQLite不擅长处理并发写入。但我们的写入模式是单写入（一个MT5数据泵进程），这正是SQLite的强项。如果将来需要多写入场景，我们会考虑PostgreSQL——但以目前30个品种每秒1-2个tick的规模，SQLite根本不是瓶颈。