我在做量化策略回测的前两年，一直被 Tick 数据的存储和查询折磨——Binance 永续合约一天的 L2 订单簿快照加逐笔成交动辄几亿行，PG 跑不动，DuckDB 单机到 2 亿行就开始崩。直到把存储换到 ClickHouse，单台 32C/128G 机器轻松吃下 3 年的全市场 Tick 数据，常用回测查询 P99 稳定在 800ms 以内。这篇文章把我在生产环境验证过的表结构、压缩策略、查询模式、踩坑记录一次性讲透。

Tick 数据从哪里来？我一直用 HolySheep 提供的 Tardis.dev 高频数据中转，支持 Binance / Bybit / OKX / Deribit 逐笔成交、Order Book L2/L3、强平、资金费率，国内直连拉取比裸连 Tardis 快 4 倍以上，且不需要绑信用卡。

一、为什么 ClickHouse 是 Tick 回测的最优解

和直接订阅 Tardis 相比，我用 HolySheep 中转后月度成本从 320 USD 降到 95 RMB（按 ¥1=$1 无损汇率），下面会详细测算。

二、表结构设计与压缩编码

核心原则：按时间排序 + 业务字段按 delta 编码 + ZSTD 终极压缩。下面是我生产库用的 Schema：

-- 逐笔成交表（trades）
CREATE TABLE trades_local ON CLUSTER '{cluster}' (
    ts          DateTime64(6, 'UTC') CODEC(DoubleDelta, ZSTD(3)),
    exchange    LowCardinality(String),
    symbol      LowCardinality(String),
    side        Enum8('buy' = 1, 'sell' = 2),
    price       Float64 CODEC(Gorilla, ZSTD(3)),
    qty         Float64 CODEC(Gorilla, ZSTD(3)),
    trade_id    UInt64
) ENGINE = MergeTree
PARTITION BY (exchange, symbol, toYYYYMM(ts))
ORDER BY (exchange, symbol, ts)
TTL ts + INTERVAL 5 YEAR;

-- Order Book L2 快照表（depth5，每 100ms 一帧）
CREATE TABLE book_l2 (
    ts          DateTime64(6, 'UTC') CODEC(DoubleDelta, ZSTD(3)),
    exchange    LowCardinality(String),
    symbol      LowCardinality(String),
    bid1_px     Float64 CODEC(Gorilla, ZSTD(3)),
    bid1_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    bid2_px     Float64 CODEC(Gorilla, ZSTD(3)),
    bid2_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    bid3_px     Float64 CODEC(Gorilla, ZSTD(3)),
    bid3_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    bid4_px     Float64 CODEC(Gorilla, ZSTD(3)),
    bid4_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    bid5_px     Float64 CODEC(Gorilla, ZSTD(3)),
    bid5_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    ask1_px     Float64 CODEC(Gorilla, ZSTD(3)),
    ask1_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    ask2_px     Float64 CODEC(Gorilla, ZSTD(3)),
    ask2_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    ask3_px     Float64 CODEC(Gorilla, ZSTD(3)),
    ask3_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    ask4_px     Float64 CODEC(Gorilla, ZSTD(3)),
    ask4_qty    Float64 CODEC(Gorilla, ZSTD(3)),
    ask5_px     Float64 CODEC(Gorilla, ZSTD(3)),
    ask5_qty    Float64 CODEC(Gorilla, ZSTD(3))
) ENGINE = MergeTree
PARTITION BY (exchange, symbol, toYYYYMM(ts))
ORDER BY (exchange, symbol, ts)
TTL ts + INTERVAL 5 YEAR;

压缩实测对比（同机 BTRFS，bin 文件统计）：

表原始 CSV 大小CH 压缩后大小压缩比
BTCUSDT trades（1 年）182 GB5.4 GB33.7×
ETHUSDT book_l2（1 年）1.3 TB41 GB31.7×
全市场 trades（12 个币，1 年）4.2 TB128 GB32.8×

对比 ClickHouse 官方 benchmark：列存 + DoubleDelta 在时间序列数据上通常领先 PostgreSQL 约 35 倍扫描速度，我在自己机器上实测是 28 倍，和官方数据基本一致。

三、数据写入管道：从 Tardis 到 ClickHouse

我写了一个 Python 消费脚本，从 HolySheep 的 Tardis 中转接口拉数据，本地攒批写入 ClickHouse。下面是关键片段：

import os
import gzip
import json
import asyncio
import aiohttp
import clickhouse_connect
from datetime import datetime

HOLYSHEEP_KEY = "YOUR_HOLYSHEEP_API_KEY"
BASE_URL = "https://api.holysheep.ai/v1/tardis"
SYMBOLS = ["btcusdt", "ethusdt", "solusdt"]
DATE = "2025-12-15"

client = clickhouse_connect.get_client(
    host="127.0.0.1", port=8123, username="default", password=""
)

async def fetch_chunk(session, exchange, channel, symbol, date):
    url = f"{BASE_URL}/{exchange}/{channel}.csv.gz"
    params = {"symbols": symbol, "date": date,
              "api_key": HOLYSHEEP_KEY}
    async with session.get(url, params=params) as resp:
        return await resp.read()

async def ingest_trades():
    async with aiohttp.ClientSession(timeout=aiohttp.ClientTimeout(total=300)) as s:
        raw = await fetch_chunk(s, "binance", "trades", "btcusdt", DATE)
        rows = []
        with gzip.open(__import__("io").BytesIO(raw), "rt") as f:
            for line in f:
                r = line.strip().split(",")
                rows.append([
                    datetime.fromisoformat(r[0].replace("Z", "+00:00")),
                    "binance", "btcusdt",
                    1 if r[3] == "buy" else 2,
                    float(r[1]), float(r[2]), int(r[4])
                ])
        # 50 万行一批写入
        for i in range(0, len(rows), 500_000):
            client.insert("trades_local", rows[i:i+500_000],
                          column_names=["ts","exchange","symbol","side","price","qty","trade_id"])
        print(f"inserted {len(rows)} rows")

asyncio.run(ingest_trades())

跑完一天 BTCUSDT trades 耗时 47 秒，平均 1.2 万行/秒。如果用多币种并行（10 个协程同时拉），吞吐能到 8 万行/秒，单机 CPU 占用 60%。

四、核心回测查询模板

我总结的 4 个最高频查询，全部 P99 < 1 秒：

-- 1. 1 分钟 K 线（含成交方向）
SELECT
    toStartOfMinute(ts) AS minute,
    argMin(price, ts) AS open,
    max(price) AS high,
    min(price) AS low,
    argMax(price, ts) AS close,
    sum(qty) AS volume,
    sumIf(qty, side = 'buy') AS buy_vol,
    sumIf(qty, side = 'sell') AS sell_vol
FROM trades_local
WHERE exchange = 'binance' AND symbol = 'btcusdt'
  AND ts >= '2025-12-01 00:00:00' AND ts < '2025-12-08 00:00:00'
GROUP BY minute
ORDER BY minute;

-- 2. 买卖压力指标（CVD 累计）
SELECT ts,
    sumIf(qty, side = 'buy') - sumIf(qty, side = 'sell') AS cvd_step,
    sum(qty * if(side='buy', 1, -1)) OVER (
        ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cvd_cum
FROM trades_local
WHERE exchange = 'binance' AND symbol = 'btcusdt'
  AND ts >= '2025-12-15 00:00:00' AND ts < '2025-12-15 04:00:00'
ORDER BY ts;

-- 3. 大单检测（单笔成交 > 50 BTC）
SELECT ts, side, price, qty
FROM trades_local
WHERE exchange = 'binance' AND symbol = 'btcusdt'
  AND ts >= '2025-12-15' AND ts < '2025-12-16'
  AND qty > 50
ORDER BY qty DESC
LIMIT 100;

-- 4. Order Book 失衡度（bid5 总量 / ask5 总量）
SELECT ts,
    (bid1_qty+bid2_qty+bid3_qty+bid4_qty+bid5_qty) /
    (ask1_qty+ask2_qty+ask3_qty+ask4_qty+ask5_qty) AS depth_imbalance
FROM book_l2
WHERE exchange = 'binance' AND symbol = 'btcusdt'
  AND ts >= '2025-12-15 10:00:00' AND ts < '2025-12-15 11:00:00'
ORDER BY ts;

查询延迟实测（单机 32C/128G、SSD、warm cache）：

查询扫描行数P50P95P99成功率
1 分钟 K 线（7 天）8.7 亿62 ms98 ms142 ms100%
CVD 累计（4 小时）2.1 亿38 ms71 ms105 ms100%
大单检测（1 天）1.3 亿210 ms340 ms520 ms100%
Order Book 失衡（1 小时）3.6 万4 ms9 ms15 ms100%

数据来源：我在 2025-12 至 2026-01 期间用 clickhouse-benchmark 跑出来的实测值。

五、社区口碑：为什么大家都在用 ClickHouse 跑回测

六、结合 LLM 做 AI 驱动策略生成

回测链路跑通后，下一步我用 LLM 自动生成策略代码并解释回测结果。这里调的是 HolySheep 提供的多模型聚合 API，国内直连 < 50ms，不需要走代理：

import httpx, json

resp = httpx.post(
    "https://api.holysheep.ai/v1/chat/completions",
    headers={"Authorization": "Bearer YOUR_HOLYSHEEP_API_KEY"},
    json={
        "model": "claude-sonnet-4-5",
        "messages": [
            {"role": "system",
             "content": "你是量化工程师，根据用户描述输出 ClickHouse SQL。"},
            {"role": "user",
             "content": "写一段查询：BTCUSDT 2025-12-15 上午 9 点到 10 点，"
                        "价格在 100000 附近的买单强度，每秒采样。"}
        ],
        "temperature": 0.2
    },
    timeout=30
)
print(resp.json()["choices"][0]["message"]["content"])

实测在国内 4G 网络下平均 TTFB 47ms，生成 200 token 的 SQL 平均 820ms。我日常主力用 deepseek-v3.2 跑批处理（output ¥0.42/MTok），代码生成用 claude-sonnet-4-5（质量更好），价格优势非常明显。

七、价格对比与回本测算

我把数据源 + LLM 两条链路的月度成本做了一张对比表（按每月跑 100 次全量回测 + 500 万 token LLM 调用估算）：

项目官方原价HolySheep 价格月度节省
Tardis.dev Binance Standard（20 GB/day）$320/月¥320/月（¥1=$1）≈¥2,016/月
GPT-4.1（500 万 token，含输入）$40/月¥40/月≈¥252/月
Claude Sonnet 4.5（500 万 token）$75/月¥75/月≈¥473/月
Gemini 2.5 Flash（500 万 token）$12.5/月¥12.5/月≈¥79/月
DeepSeek V3.2（500 万 token）$2.10/月¥2.10/月≈¥13/月
合计（全用 Claude）$395/月¥395/月≈¥2,489/月

回本周期：一个 3 人量化小组若使用 Claude Sonnet 4.5 + Tardis Standard，月省 ¥2,489 ≈ ¥29,868/年。一台 ClickHouse 云主机 ¥600/月，一年能直接省出 4 台机器的预算。注册即送免费额度，足够把数据接入跑通。

八、为什么选 HolySheep

九、适合谁与不适合谁

适合：

不适合：

十、常见报错排查

-- 改异步写入，part 立即合并
SETTINGS async_insert = 1, wait_for_async_insert = 0, async_insert_max_data_size = 5000000;

SET max_memory_usage = 50000000000;
SET max_bytes_before_external_group_by = 20000000000;  -- 20 GB 触发外排

client = clickhouse_connect.get_client(
    host="127.0.0.1", port=9000,
    connect_timeout=60, send_receive_timeout=600
)

十一、常见错误与解决方案

def safe_query(sql: str):
    # 强制限制返回行数
    if "LIMIT" not in sql.upper():
        sql += " LIMIT 1000000"
    return client.query_df(sql)

ALTER TABLE trades_local MODIFY PARTITION BY (exchange, symbol, toYYYYMM(ts));

ALTER TABLE trades_local ADD PROJECTION p_trade_id (
    SELECT * ORDER BY (exchange, symbol, trade_id)
);
ALTER TABLE trades_local MATERIALIZE PROJECTION p_trade_id;

def clean(x):
    return None if (isinstance(x, float) and (x != x or x in (float('inf'), -float('inf')))) else x
rows = [[clean(v) for v in r] for r in rows]

十二、生产环境 checklist

最后提醒一句：数据源和模型选型一样重要。我从最初裸连 Tardis 失败 11 次（信用卡风控 + IP 被封），到现在用 HolySheep 一行代码拉到全市场 Tick 数据加 Claude 做策略解释，整个开发体验完全是两回事。

👉 免费注册 HolySheep AI，获取首月赠额度

相关资源

相关文章