做量化回测两年多我印象最深的一次崩溃不是策略回撤而是眼看着Tushare接口返回限流报错手里的日线数据就差最后一个交易日。从那以后我想明白一件事行情数据必须落到自己的数据库里就算前期搭管道麻烦一点也比每次写策略都去蹭公开接口强。这篇文章是“Tushare量化实战”系列的第五篇重点讲我用Tushare做数据源之后怎么设计本地库的表结构、怎么写数据管道、怎么把SQL查询从秒级压到毫秒级。内容适合已经能跑通Tushare基础接口、但正在纠结“数据到底存哪、查询越来越慢”的量化开发者和数据分析师我会把建表、灌数据、调优、踩坑这几件事一次讲透。1. 为什么说本地数据库存储是Tushare量化路上的必经一站很多人一开始喜欢“用时现取”写个函数从Tushare拉数据分析完就丢。数据量小的时候没毛病但只要你开始做全市场扫描、做因子回测、做分钟级策略这条路立刻走不通。1.1 积分制与调用频率这笔账越早算越明白Tushare的接口权限和积分挂钩积分越高每分钟能调用的次数越多。但就算积分足够行情接口也不是无限给你刷的。比如日线行情daily接口一次调用返回某个交易日全市场约5000只股票的数据看起来不多但如果你策略里需要每只股票近十年的历史日线实时拉取就意味着要发成百上千次请求。再加上网络延迟、JSON解析、字段校验一次完整的数据准备可能要好几分钟。我见过不少朋友把Tushare调用写成同步阻塞方式策略每次运行前都全量拉一遍数据结果一半时间浪费在等接口返回上。更麻烦的是接口偶尔还会因为频率限制直接报错策略跑一半卡住后面的信号全部作废。数据落库之后这些问题基本消失Tushare只负责按计划定时同步增量数据策略执行时只读本地库速度和稳定性完全是两个级别。1.2 重复解析与网络延迟是看不见的隐形开销每次调Tushare接口你得到的是一堆JSON或DataFrame要经过网络传输、解码、类型转换、列名整理才能用。就算你加了缓存数据还是散落在不同进程的内存里换个终端、重启个服务缓存直接失效。本地数据库相当于把数据固化成了一个可反复读的“数据仓库”。第一次从Tushare拉完后面不管是回测、实盘信号、还是写一些临时分析脚本全部直接查询本地库。而且数据库本身就支持按需过滤、聚合、多表关联不需要在Python侧反复做DataFrame拼接。这一点在日常盘中尤其关键——盘中实时信号只查最新的几条记录本地查询快、稳定不会因为外网波动耽误下单窗口。1.3 从“接口调用者”变成“数据所有者”数据落在本地之后你才对数据有了完整的控制权。你可以自己写校验脚本检查脏数据可以把行情表和财务表、指数成分表做JOIN可以建自定义因子库甚至可以在非交易日离线反复回测同一套策略。这些事如果全部依赖Tushare实时接口基本不可能实现。我当时下决心搭本地库的导火索就是发现自己的回测脚本每次运行都要等接口返回十几年数据中间还断过一次网。落库之后回测时间从“十分钟起步”降到“几秒钟读完数据”这效率提升非常直观。所以我的建议是哪怕你只做一只股票的简单策略也值得把数据管起来越早做越轻松。2. 建表设计——数据地基没打好后面全是坑本地库的核心是表结构设计。Tushare日线行情返回的字段其实不复杂但字段类型、主键、索引这些如果一开始没选对后面数据量上来会非常痛苦。2.1 字段类型选择别让 trade_date 成为性能隐患日线行情表里最常用的过滤条件就是股票代码和时间范围trade_date这个字段的设计尤其重要。我见过有人图方便存成VARCHAR(8)也有人存成DATE还有人直接存INT比如20240101。三种都能跑但查询性能差别不小。存储类型示例值优点缺点VARCHAR(8)20240101可读性好和Tushare原样对齐比较是字符串比较范围查询性能略弱DATE2024-01-01语义清晰日期函数丰富和Tushare的字符串格式要转换INT20240101比较最快适合做分区键可读性差日期函数要转我实际用的是INT类型存trade_date因为日线查询基本都是按整数范围做过滤和排序整数比较比字符串快而且按年分区的PARTITION BY RANGE (trade_date)写起来也顺手。如果你更看重语义化用DATE也没问题只是从Tushare拿到数据写入时要多一步转换。注意一个容易踩的坑Tushare日线接口的vol字段单位是“手”amount字段单位是“千元”建表注释里最好写清楚不然半年后你自己回来看可能都会懵。2.2 主键与唯一约束杜绝重复数据的唯一方法Tushare的日线数据天然有唯一性标识一只股票在某个交易日只有一条记录。所以主键直接设计成联合主键(ts_code, trade_date)是最合理的。联合主键的第一个字段是股票代码第二个是交易日期这样既能保证同一天不会重复插入同一只股票的数据也能天然给后续查询提供索引基础。别用ts_code单独做主键因为同一只股票会有多年历史数据单字段主键会直接报错。联合唯一索引UNIQUE KEY uk_code_date (ts_code, trade_date)也是一样的效果如果你担心主键过长影响性能可以用自增id做主键但一定要保留这个联合唯一索引来防重。很多莫名其妙的重复数据问题就是建表时漏了这一步。2.3 索引规划复合索引的顺序决定查询生死建表时我会默认加两个索引一个是联合唯一索引另一个是(ts_code, trade_date)复合索引。很多人分不清“既然唯一索引里已经包含了这两个字段为什么还要单独建复合索引”。实际上联合唯一索引本身就能覆盖WHERE ts_codexxx AND trade_date BETWEEN ...这类查询如果你不需要额外索引其实可以不加。但如果你经常按trade_date单独查询全市场某一天的数据那就需要再建一个trade_date单列索引否则前面的联合索引帮不上忙。我自己的经验是最常用的查询是“取某只股票在某段时间内的K线”这种查询只要命中(ts_code, trade_date)这个复合索引就非常快。等数据量到千万行级别你会发现有索引和没索引完全是两个世界。我试过从没有索引的全表扫描到加上复合索引之后同样一条查询从4秒多降到30毫秒左右这就是索引的价值。2.4 要不要分区表按时间维度做水平拆分当数据积累到千万行以后单表即使有索引也会慢慢变重。日线数据有个天然优势它是严格按时间递增的非常适合按时间范围分区。一年大约有250个交易日全市场5000只股票就是125万行。如果你打算存10年数据就是1250万行。这个量级在单表里其实还能扛但如果你还要做全市场扫描、跑因子计算分区表的优势就非常明显了。MySQL可以用PARTITION BY RANGE (YEAR(trade_date))按年分区PostgreSQL可以用声明式分区SQLite暂时没有原生的分区功能。我个人的判断标准是如果表数据量超过500万行且查询经常带trade_date范围过滤就值得做分区。分区之后查询只需要扫描对应年份的分区而不是整张表性能提升非常直接。另外如果你用INT存日期分区边界写起来也简单比如PARTITION p2023 VALUES LESS THAN (20240101)。3. 从Tushare拉数据到入库——这条管道怎么搭最省心数据库表建好以后下一步就是把Tushare的数据灌进来。这里有两个关键点全量初始化怎么拉增量以及写入时怎么保证幂等、不重复。3.1 全量初始化先拿交易日历再按天拉数据做全量初始化时不建议按股票代码循环去拉每只股票的历史日线那样请求次数会放大到几千次很容易触发限流而且时间也长。我的做法是先用trade_cal接口拿到所有交易日然后按“日期”维度循环调用daily接口一次请求就能拿到全市场当天所有股票的行情。这样做的好处很明显A股约5000只股票按股票拉一遍要5000多次请求按交易日拉一年的数据只需要约250次请求10年也才2500次。配合适当的sleep间隔基本不会碰到频率限制。初始化的代码逻辑大概长这样import tushare as ts import pymysql import time pro ts.pro_api(你的token) # 1. 获取交易日历 cal pro.trade_cal(exchangeSSE, is_open1) trade_dates sorted(cal[cal_date].tolist()) # 2. 按交易日拉取日线并入库 for date in trade_dates: df pro.daily(trade_datedate) if df.empty: continue # 这里做字段筛选、重命名、类型转换 df df[[ts_code, trade_date, open, high, low, close, pre_close, change, pct_chg, vol, amount]] # 写入数据库建议使用下面的批量写入方式 write_to_db(df) time.sleep(0.3) # 控制请求频率注意trade_cal接口返回的cal_date是字符串格式比如20240101直接用就行和建表时的INT字段正好对上。3.2 增量更新的核心逻辑记住“上次拉到哪一天”全量初始化跑完之后日常维护就是一个增量更新的问题。核心逻辑很简单查本地表里trade_date的最大值从下一天开始拉。伪代码如下# 查本地最大日期 cursor.execute(SELECT MAX(trade_date) FROM daily) max_date cursor.fetchone()[0] # 比如 20240115 # 从 max_date 之后的交易日继续拉 new_dates [d for d in trade_dates if d max_date] for date in new_dates: df pro.daily(trade_datedate) write_to_db(df) time.sleep(0.3)这里容易忽略一个坑增量拉取时千万别用自然日循环一定要以交易日历为准。Tushare的trade_cal接口里is_open1的才是交易日否则你会浪费很多请求在周末和节假日上而且还会因为非交易日没有数据白白浪费时间。另一个细节是假如某天程序中断了下一次跑的时候MAX(trade_date)还是停留在上次成功的日期断点续传自然就实现了。3.3 pandas.to_sql 不见得好用批量写入和 UPSERT 才是正道很多教程会让你直接df.to_sql(daily, conn, if_existsappend, indexFalse)这个方法在小数据量时凑合能用但有两个明显问题它是一行一行执行INSERT的几万行数据写入会非常慢。如果表里有唯一索引重复写入就直接抛异常中断整个任务。更好的做法是使用pandas先把数据组织好然后通过executemany批量插入或者拼接成一条INSERT INTO ... VALUES (...), (...), ... ON DUPLICATE KEY UPDATE语句。以MySQL为例下面这种写法可以防重def write_to_db(df): # 构建批量插入SQL rows list(df.itertuples(indexFalse, nameNone)) placeholders (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) insert_sql fINSERT INTO daily (ts_code, trade_date, open, high, low, close, pre_close, change, pct_chg, vol, amount) VALUES {placeholders} ON DUPLICATE KEY UPDATE closeVALUES(close) cursor.executemany(insert_sql, rows) conn.commit()如果你用的是PostgreSQL对应的是INSERT ... ON CONFLICT (ts_code, trade_date) DO UPDATE SET ...。核心思路一样不要单纯INSERT而是让数据库自己处理唯一键冲突。这样即使增量任务被重复执行也不会产生重复数据。3.4 幂等设计网络中断和程序重启不可怕做数据管道一定要把“任务是幂等的”当成默认要求。意思是同一个任务跑两遍和跑一遍最终数据库里的结果是一致的。上面的ON DUPLICATE KEY UPDATE方案就保证了写入层幂等即使某天数据被拉了两次第二次只是覆盖更新不会新增脏数据。我还会额外维护一张拉取状态表记录每次同步任务的日期、状态、拉取行数、耗时、错误信息。别小看这个表它能在后面排查问题时省很多时间。比如某天发现数据缺了几天直接查状态表就能看出是哪天同步失败而不是对着日志翻半天。这张状态表的结构很简单大致字段如下CREATE TABLE sync_log ( id INT AUTO_INCREMENT PRIMARY KEY, sync_date INT NOT NULL COMMENT 拉取日期, status TINYINT NOT NULL COMMENT 1成功 0失败, rows_count INT DEFAULT 0, elapsed_ms INT DEFAULT 0, error_msg VARCHAR(500) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );4. SQL查询优化从慢查询到毫秒级响应数据进了库接下来就是怎么查得快。很多人遇到慢查询的第一反应是“加索引”这话没错但如果你不知道慢在哪里加索引也可能是盲人摸象。我建议先学会看执行计划再针对性地优化。4.1 先看 EXPLAIN不要拍脑袋优化MySQL里一条EXPLAIN SELECT ...就能看到数据库执行这条查询的思路。重点看几个指标type字段ALL是全表扫描range是范围扫描ref是普通索引匹配rows是预估扫描行数Extra里有没有Using filesort或Using temporary。比如下面这条查询EXPLAIN SELECT ts_code, trade_date, close FROM daily WHERE ts_code 000001.SZ AND trade_date BETWEEN 20230101 AND 20231231;如果type是ALL说明它没走到索引全表扫了一遍如果type是range且rows很小说明复合索引生效了。优化前我建议先跑一次EXPLAIN把慢的原因定位清楚再动手。4.2 覆盖索引让查询连数据行都不用回普通索引查询通常是先查索引再根据索引里的主键回表查完整数据行。但如果查询需要的字段都已经包含在索引里数据库就不用回表了这叫“覆盖索引”。覆盖索引能让查询快一大截。比如我经常执行SELECT ts_code, trade_date, close, vol FROM daily WHERE ts_code 000001.SZ AND trade_date 20240101 ORDER BY trade_date DESC LIMIT 10;这条查询如果只建了(ts_code, trade_date)复合索引SQL查完索引后还要回表拿close和vol字段。如果改成建一个(ts_code, trade_date, close, vol)的覆盖索引查询直接就从索引里拿到所有需要的数据连回表都省了。当然覆盖索引不是越多越好因为每个字段都会增加写入时的维护成本。我的原则是只对“高频查询”建覆盖索引低频查询用普通复合索引就够了。4.3 隐式类型转换和函数陷阱最容易被人忽略很多慢查询不是没索引而是SQL写法导致索引失效。最典型的是隐式类型转换如果trade_date列是INT类型你查WHERE trade_date 20240101数据库可能把字符串转成数字也可以走索引但如果你建表时用了VARCHAR却拿20240101这个数字去比较索引可能直接失效。另一个经典坑是对索引列使用函数。比如你把trade_date存成了DATE类型然后查询WHERE DATE(trade_date) 2024-01-01这就导致索引列被包了函数数据库没办法直接走索引只能扫描全表再逐个算函数。正确写法是改成范围查询WHERE trade_date 2024-01-01 AND trade_date 2024-01-02还有一个容易忽略的坑是字符集不一致。如果daily表的ts_code列是utf8mb4而联动表用的是latin1JOIN的时候索引会失效。建表时最好统一字符集避免后面分析时莫名慢半拍。4.4 排序和分页ORDER BY 也有大学问查询K线数据时经常要按日期倒序取最近的N条。如果你建的复合索引是(ts_code, trade_date)那么下面这条查询通常能顺利走索引SELECT ts_code, trade_date, close FROM daily WHERE ts_code 000001.SZ ORDER BY trade_date DESC LIMIT 10;但如果查询条件里没有ts_code直接ORDER BY trade_date DESC那复合索引就帮不上忙数据库只能先排序再返回Using filesort就出来了。这里可以看实际场景决定要不要单独建trade_date索引。分页也有一个经典坑LIMIT 500000, 20这种深翻页会越来越慢因为数据库要先扫500020条再丢前500000条。正确思路是用“键集分页”也就是记住上一页最后一条记录的位置下一页从它之后开始查-- 第一页 SELECT ts_code, trade_date, close FROM daily WHERE ts_code 000001.SZ ORDER BY trade_date DESC LIMIT 20; -- 下一页假设上次最大的 trade_date 是 20240115 SELECT ts_code, trade_date, close FROM daily WHERE ts_code 000001.SZ AND trade_date 20240115 ORDER BY trade_date DESC LIMIT 20;这样数据库始终只需要扫描很小的区间不会因为页码变大而越来越慢。4.5 能用SQL聚合的别拉回Python再算我见过不少人习惯先SELECT *把数据拉回Python然后再用pandas的groupby做聚合。数据量小的时候无所谓数据量一旦变大这种写法既浪费内存又浪费时间。数据库本身就是为聚合计算的场景优化的能用SQL做的事尽量在SQL里做。比如我想算每只股票在2023年的平均收盘价和累计成交量一条SQL就能搞定SELECT ts_code, COUNT(*) AS trading_days, ROUND(AVG(close), 2) AS avg_close, SUM(vol) AS total_vol FROM daily WHERE trade_date BETWEEN 20230101 AND 20231231 GROUP BY ts_code;全市场5000只股票一年的数据在本地数据库里做这个聚合基本是秒回。如果拉到Python里光是传输几百万行数据就要好几秒内存峰值也高不少。数据库端的聚合计算天然就是为这类场景设计的别和它对着干。5. 避坑实录Tushare数据落库过程中遇到的那些诡异问题数据管道本身不难难的是上了生产之后冒出来的各种“反直觉”问题。下面几个坑都是我真实踩过的写出来给大家提个醒。5.1 停牌日不是bug是正常的“缺行”A股股票经常停牌停牌期间没有行情数据所以daily表里某只股票在两个交易日之间的行是不连续的。很多新手跑完数据后一查发现“怎么少了几天”以为管道漏了。这通常不是你的问题而是事实本身就是如此。如果你的策略计算需要“每个交易日都有数据”我的做法是单独维护一张交易日历表再用交易日历表做基准对目标股票做LEFT JOIN。缺失的部分可以填充前值或者标记为停牌不要在SQL里臆造不存在的行情。5.2 复权因子前复权、后复权别把价格表搞乱股票分红送股之后价格会出现跳空。直接用原始close计算收益率会在除权日前后产生巨大的假收益。Tushare单独提供了复权因子接口强烈建议在本地单独存一张复权因子表而不是直接把复权价覆盖到原始价格里。我的习惯是daily表只存原始行情另外一张表存复权因子需要计算收益率时再通过因子算前复权或后复权。这样想算真实收益就用后复权想画当前视角的K线就用前复权两边都不耽误。如果直接把复权价覆盖到daily表里后面想恢复原始价格就很麻烦。5.3 重复数据排查一条SQL就能揪出来就算写了唯一索引也架不住数据管道里有人为操作失误。做数据质量检查时我经常跑下面的SQLSELECT ts_code, trade_date, COUNT(*) FROM daily GROUP BY ts_code, trade_date HAVING COUNT(*) 1;正常情况下这个查询应该返回空结果。一旦查出重复记录就说明写入逻辑或表约束有问题。清理时也要小心不要随随便便DELETE全表先确认哪条记录才是正确的再按主键保留一条。如果表忘记建唯一索引现在的数据修完以后记得把唯一索引补上从源头防止再犯。5.4 NULL值陷阱AVG、COUNT、JOIN里的小暗坑SQL里NULL和0完全不是一回事。比如AVG(close)计算时会忽略NULL但如果你把缺失值填成了0平均值就会被拉低结论直接出错。再有COUNT(*)统计的是行数COUNT(close)统计的是非NULL的个数两者结果可能不一样。写SQL时我习惯明确处理NULL该用COALESCE就用COALESCE该加WHERE close IS NOT NULL就加上。NULL不能直接用比较判断空值只能用IS NULL这个基础但又关键的点很多人都会不小心写错。我自己在把Tushare数据落库这套体系搭完之后最明显的变化是回测迭代效率直线上升。原来跑一个全市场策略光是准备数据就要等几分钟现在几十秒内读数、算因子、出信号全部完成。如果你刚准备把Tushare数据落库我建议别急着把字段设计得又多又全先聚焦最核心的三张表日线行情、复权因子、交易日历把这条链路跑通后面再逐步扩展财务、资金流、指数成分等表。等到数据积累到百万行级别索引、分区、UPSERT这些技术点就会真正派上用场。就算你目前只做一只自选股的简单策略一个设计良好的本地库也能让后续的每一步都走得稳很多。