【Python量化系统工程化 #03】数据量大了查询就卡？分区表+索引策略让千万级K线零等待

痛点开场

上两篇我们把数据落下来了，也知道怎么增量更新、不重复拉取。

然后你写了第一个"真正的"查询：

SELECT AVG(c) FROM klines WHERE code = '600519.SH' AND t BETWEEN '2025-01-01' AND '2025-06-30'

本地测试 5000 条，0.3 毫秒，爽。

三个月后，脚本跑了 5000 只股票 × 500 交易日 = 250 万条。同样的查询——3 秒。

再半年，2500 万条——30 秒。

你以为是 SQL 写错了，加了个 LIMIT 10 试了一下，还是 30 秒。根因不是语法，是结构：单表无索引 = 全表扫描，数据量每翻 10 倍，查询时间也翻 10 倍。

本文解决的就是这个：让千万级时序数据的查询，从秒级回到毫秒级。

本文你将得到什么

1. 一个真实可跑的对比：10000 条 K 线，无索引 vs 有索引 vs 分区表 vs 直连年表的速度差异
2. 三条索引策略：code 单列索引、t 单列索引、(code, t) 联合索引——什么时候用哪条
3. 按年分区表 + 联合视图的完整代码，兼容"单表写法"、查询走分区索引
4. ATTACH DATABASE 跨库联合查询——模拟"按月拆文件"的工程实践

一、单表之痛：为什么数据一多就卡

SQLite 单表默认是堆表——行按插入顺序堆在一起，没有"目录"。

当你执行 WHERE code = '600519.SH' 时，数据库要从第一行扫到最后一行，逐行比对 code 字段。这叫全表扫描。

数据量 预估全表扫描时间 有索引时间
1 万条 ~1 ms ~0.1 ms
100 万条 ~100 ms ~0.1 ms
2500 万条 ~2.5 s ~0.2 ms

注：上表为理论估算；本文实测在 10000 条级别，后文给出真实数字。

核心问题不是"数据量大"，而是"没有索引告诉数据库去哪找"。

二、索引：从堆表到 B+ 树

索引的本质是额外维护一个有序结构（SQLite 用 B+ 树），让 WHERE 条件能"跳"到目标位置，而不是逐行扫描。

2.1 建索引一句话

CREATE INDEX idx_klines_code ON klines(code);
CREATE INDEX idx_klines_t ON klines(t);
CREATE INDEX idx_klines_code_t ON klines(code, t);

三条索引分工不同：

• idx_klines_code：只查 code 时走它（如"某股全部历史"）
• idx_klines_t：只查日期范围时走它（如"某天全部股票"）
• idx_klines_code_t：同时查 code + t 范围时走它（如"某股某时间段"）——最常用，建议优先建

2.2 实测对比（10000 条 K 线，100 次查询取平均）

样本：20 只白马股 × 约 500 交易日 = 10000 条，单表存入 SQLite。

查询模式 无索引 有索引 提速倍数
WHERE code = ? 70.6 ms 18.7 ms 3.8 x
WHERE code = ? AND t BETWEEN ? AND ? 70.6 ms（全表扫） 6.7 ms 10.5 x

10000 条级别差距"只有"几倍，但当数据量到 100 万条时，无索引查询会到秒级，有索引仍在毫秒级——差距放大到 100 倍以上。

结论：索引是时序数据库的刚需，不是可选优化。

三、分区表：按时间切分，查询只扫 relevant 分区

索引解决了"单表内快速定位"，但还有一个问题：表本身太大。

2500 万行的单表，即使走索引，B+ 树深度也会增加；备份一个表要拷整个文件；删除三年前数据要 DELETE 扫描全表。

分区表的核心思想：把物理存储按时间切开，逻辑上仍是一张表。

3.1 按年分表 + 联合视图

-- 2024 年数据
CREATE TABLE klines_2024 (code TEXT, t TEXT, o REAL, h REAL, l REAL, c REAL, v INTEGER, a REAL, pc REAL);
CREATE INDEX idx_klines_2024_code_t ON klines_2024(code, t);

-- 2025 年数据
CREATE TABLE klines_2025 (code TEXT, t TEXT, o REAL, h REAL, l REAL, c REAL, v INTEGER, a REAL, pc REAL);
CREATE INDEX idx_klines_2025_code_t ON klines_2025(code, t);

-- 联合视图：对外仍像一张表
CREATE VIEW klines_all AS
SELECT * FROM klines_2024
UNION ALL
SELECT * FROM klines_2025;

查询时：

-- 走视图（兼容旧代码）
SELECT AVG(c) FROM klines_all WHERE code = '600519.SH';

-- 直连年表（最快，明确知道只查 2025 年）
SELECT AVG(c) FROM klines_2025 WHERE code = '600519.SH';

3.2 分区表实测

查询方式 100 次平均耗时 说明
单表 + 索引 18.7 ms 基准
分区视图 + 索引 19.4 ms 与单表持平，兼容旧写法
直连年表 + 索引 7.1 ms 快 2.6 倍，只扫 1/3 数据

结论：分区表不牺牲查询速度（视图与单表持平），直连年表还能更快；收益在"管理"层面——删旧数据 DROP TABLE 秒完成，备份只拷当年的文件。

四、ATTACH DATABASE：跨文件联合查询

分区表在一个数据库文件内，如果数据量继续膨胀（比如分钟线），一个文件可能到几十 GB。这时可以按季度/月份拆成独立数据库文件，需要联合查询时用 ATTACH。

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("ATTACH DATABASE 'klines_2025q1.db' AS q1")
conn.execute("ATTACH DATABASE 'klines_2025q2.db' AS q2")

rows = conn.execute("""
SELECT 'Q1' as src, code, t, c FROM q1.klines
UNION ALL
SELECT 'Q2' as src, code, t, c FROM q2.klines
ORDER BY t
""").fetchall()

适用场景：

• 分钟线数据：一个月一个 db 文件，查询时只 ATTACH 需要的月份
• 多策略隔离：策略 A 的数据存在 strategy_a.db，策略 B 存在 strategy_b.db，联合归因时 ATTACH

五、常见坑

1. 建了索引但查询不走：WHERE code = 600519（没引号，数字类型不匹配索引字段类型）→ 索引失效。确保类型一致：TEXT 字段用 '600519.SH'
2. 联合索引顺序写反：(t, code) vs (code, t)。如果查询总是先 code 再 t，索引必须按 (code, t) 顺序建，否则只能用到前缀
3. 分区表忘了给子表建索引：视图不会自动继承索引，每个 klines_YYYY 都要独立 CREATE INDEX
4. ATTACH 后路径是相对路径：如果脚本工作目录变了，ATTACH 'klines_2025q1.db' 会找不到文件。用绝对路径或基于 file 拼接
5. SQLite 单文件上限：官方说 281 TB，但实践中文件超过 10 GB 后，某些文件系统（FAT32）或备份工具会出问题。>10 GB 建议拆分文件 + ATTACH

六、小结 + 下篇预告

03 一句话：索引让查询从"全表扫描"变"精准定位"，分区表让管理从"大而笨"变"小而灵"；两者叠加，千万级时序数据也能毫秒响应。

下篇（#04）我们聊一个比"存得快"更紧迫的问题：100 只股票顺序拉要 10 秒，怎么压到 1 秒？——答案是「并发 + 批量 + 信号量限流」。我们会实测顺序 / 并发 / 批量接口的耗时差异，看怎么从 12 秒压到 0.1 秒。

免责声明

本文所有代码与数据仅用于量化工程方法演示，不构成任何投资建议，亦不构成收益承诺。投资有风险，决策需谨慎。

---

下篇预告：【Python量化系统工程化 #04】100 只股票顺序拉要十分钟？并发批量让速度提升 26 倍