搞新药研发这行最烦的一件事就是“查药”。立项之前要查竞品、查临床进度、查全球上市状态一查就是十几个网站来回切。FDA橙皮书、ClinicalTrials.gov、EMA公开审评报告、DrugBank、药智网、NMPA数据……信息不是没有而是碎成一地。我去年接手了一个内部项目领导只说了一句“搭一个全球新药信息查询数据库以后立项调研别再用Excel了”前后折腾了三个多月。今天把整个项目的思路、表结构设计、数据清洗过程和运维经验完整写出来希望能帮到正在做类似事情的同行。这篇文章适合药企里的立项调研、医学信息、BD拓展、数据团队的同学看也适合想了解药物情报数据怎么落地成系统的产品和开发。我会把数据源怎么选、表怎么建、数据怎么洗、库怎么养、坑怎么避这几个环节全部讲透。下面全是实操记录不是理论科普。1. 为什么非要自己搭一个查询库1.1 现有数据源看着全用起来全是坑药物研发里有个词叫“情报调研”。BD团队立项要看全球同类药物有多少在研医学信息团队要看竞品的适应症布局和开发阶段注册团队要跟踪不同国家的申报状态。这些需求听起来都是“查一下就知道”但真正做起来没有任何一个单一数据源能给你完整答案。拿我平时最常用的几个源来对比数据源覆盖范围优点痛点ClinicalTrials.gov全球临床试验注册免费、接口开放、字段标准只覆盖临床阶段早期管线查不到FDA橙皮书与DrugFDA美国上市药信息官方权威、更新稳定只覆盖美国无在研早期数据EMA公开数据库欧盟审评信息官方、有完整审评报告数据分散下载麻烦DrugBank药物分子与靶点信息结构化好适合做关联商业在研管线覆盖有限商业管线库Citeline等全球研发管线信息全、更新快价格高普通团队难承担这些源各有各的好问题就出在“各有各的”。同一个药物在不同数据源里可能叫不同的名字同一个适应症有的按ICD编码、有的按MedDRA术语开发阶段更是五花八门有的写“Phase 2”有的写“临床II期”有的干脆只写一个“研究中”。我们调研同事为了确认一个药的全球开发状态经常要开八个网页挨个核对核对完还要手工填Excel。一上午废掉是常有的事。我当时建这个库的核心目标就一句话把分散在十几个地方的新药信息统一收进一个库让同事用一条SQL就能查全。这个目标听起来简单真正落地的时候牵扯出来的问题远超预期。1.2 自建库解决的三个核心问题真正用起来以后我总结这个库解决的核心问题就是三个而且这三个问题直接决定了后面的表结构设计。第一个是名称统一。同一个药可能有研发代码、通用名、商品名、不同国家的注册名再加上各种历史曾用名。调研的时候如果你想查一个“代号为AB-123”的药结果库里存的是“Abcipletinib”这个通用名那就全完蛋。必须建一张主数据表把所有别名映射到同一个药物主ID上。第二个是状态可追溯。一个药从分子发现到临床、到上市、再到撤市开发状态一直是动态的。库里面不能只存“当前处于临床III期”这一个静态值要存状态变更的时间线。有了时间线后面做竞品趋势分析、管线盘点、阶段分布统计才有据可查不然数据就是一张没有时间维度的死表。第三个是口径可比较。不同数据源对“III期”的定义不一样有的按试验启动算有的按首例入组算有的按顶会报告算。建库的时候必须做一个统一的状态归一口径把各家数据翻译成同一套标准。这件事最费劲但也是这个库最有价值的地方——数据本身不产生价值统一口径后的数据才产生价值。这三个问题想明白之后我才开始动手设计表。很多人习惯拿到需求就打开Navicat新建库直接建表建到一半发现查询要join五张表、某个字段类型选错了再回头改那个痛苦我替大家踩过了。2. 数据源选型官方接口优先商业库做补充2.1 免费官方源的接入姿势数据源是整个库的地基选错了后面全白干。我的原则很简单有官方接口的优先走接口没有接口的再看有没有结构化文件下载最后才考虑人工整理。ClinicalTrials.gov是我首先接的源。它提供完整的API支持按条件检索试验返回JSON格式的数据字段包括试验标题、药物干预、适应症、分阶段、入组人数、申办方、状态、开始日期、完成日期等。我们用它把全球注册的临床试验数据全量拉了一遍大概花了几个晚上。这里有个实操建议API有频率限制不要一次性把几万条数据全部拉完按条件分段拉比如按“首次发布时间”按月切分每个时间窗拉一次既稳定又不触发限流。FDA这边主要接两个部分一个是橙皮书里的上市药品目录字段包括活性成分、商品名、申报号、剂型、规格、参比制剂标志等另一个是DrugFDA里的审评文档这个偏文档性质我们暂时只取元数据不下载PDF正文。FDA的数据有批量下载文件直接下载解压后入库比调接口快得多而且全量文件更新频率是每天一次适合做增量同步。EMA的数据稍微麻烦一点公开的药品目录可以下载但审评报告是分页面展示的没有标准的批量接口。后来我们的做法是写一个爬虫按药品逐个抓取公开页面里的关键字段比如产品名称、活性物质、治疗领域、上市许可状态、授权日期、MAH上市许可持有人。这里要提醒一句爬取任何网站前先看对方的robots协议和条款EMA的公开信息可以用于研究用途但别拿去做商业二次分发。2.2 商业库与公开数据的取舍商业管线库确实香字段全、更新快、还有分析师评论但价格也真的劝退。一个账号一年费用几十万起对于大多数团队来说很难承受。我这边只保留了一个试用账号用来做数据校验——我在公开数据里发现某个药的开发阶段和美国官网信息对不上的时候就去商业库里交叉验证一下确认以哪个为准。这里有一个很实用的经验**不要把商业库当主数据源把它当校验源。**公开数据源再散胜在可持续、可追溯、无版权纠纷。你用FDA和ClinicalTrials.gov的数据搭建主流程商业库做抽查验证既能省钱又能保证数据质量。我们当时跑了一个验证脚本随机抽了500个药物对比公开库和商业库的阶段标注准确率大概在92%左右剩下的8%基本都是时间差导致的比如商业库更新到本月而FDA官方数据还停留在上季度这种情况以官方源为准并记一条更新日志。另外一个容易被忽略的源是DrugBank。它免费版的数据集是XML格式里面包含药物名称、分子式、靶点、通路、ATC编码等结构化信息。它跟临床库互补得很好——临床库解决“这个药进展到哪了”DrugBank解决“这个药是什么、作用机制是什么”。我们把DrugBank的数据导入后再做一层关联就能实现“输入一个靶点查出所有作用于该靶点的在研药物”这种查询做立项调研时太常用了。3. 数据库设计与建模先想清楚再动手3.1 五张核心表的设计逻辑设计阶段我花了比建表更长的时间因为我深知数据库表结构一旦定下来后面改起来代价极大。最终确认了五张核心表外加两张辅助表。先看主数据表CREATE TABLE dim_drug ( drug_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 药物主ID, generic_name VARCHAR(200) COMMENT 通用名, trade_name VARCHAR(200) COMMENT 商品名, drug_code VARCHAR(100) COMMENT 研发代码, molecular_type VARCHAR(50) COMMENT 分子类型化学药/生物药/细胞治疗等, target VARCHAR(255) COMMENT 主要靶点, atc_code VARCHAR(20) COMMENT ATC编码, highest_status VARCHAR(50) COMMENT 归一化开发状态, highest_status_date DATE COMMENT 达到最高状态的时间, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_generic_name (generic_name), KEY idx_target (target), KEY idx_status (highest_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT药物主数据表;这张表是整个库的核心字段设计的思路就是“常查的字段建索引用来关联的字段必须唯一”。通用名唯一键很重要因为后面所有临床数据、注册数据都要通过通用名关联到这个主表上。第二张是别名映射表。这张表解决的是一药多名问题CREATE TABLE dim_drug_alias ( alias_id BIGINT PRIMARY KEY AUTO_INCREMENT, drug_id BIGINT NOT NULL COMMENT 关联dim_drug.drug_id, alias_name VARCHAR(200) NOT NULL COMMENT 别名/曾用名/代码名, alias_type VARCHAR(20) COMMENT 别名类型研发代码/曾用名/商品名等, source_system VARCHAR(50) COMMENT 来源数据源, UNIQUE KEY uk_alias_name (alias_name), KEY idx_drug_id (drug_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT药物别名映射表;第三张是开发状态事实表。这张表记录一个药的状态变更历史是时间线分析的基础CREATE TABLE fact_dev_status ( id BIGINT PRIMARY KEY AUTO_INCREMENT, drug_id BIGINT NOT NULL, status VARCHAR(50) COMMENT 归一化开发状态, status_date DATE COMMENT 状态生效日期, source_system VARCHAR(50), source_id VARCHAR(100) COMMENT 来源记录ID用于去重, detail_json JSON COMMENT 原始信息快照, KEY idx_drug_id_date (drug_id, status_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT开发状态时间线表;第四张是临床试验事实表第五张是注册上市信息表。临床表关联ClinicalTrials.gov的试验编号注册表关联FDA/EMA等官方数据。两张表都以“通用名适应症数据源”作为唯一逻辑键从机制上防止重复导入。辅助表是数据字典表和ETL日志表。数据字典表维护状态枚举、适应症术语映射ETL日志表记录每次抽取的时间、条数、异常数。日志表特别重要后面对账、排查问题全靠它。3.2 字段类型、字符集与索引的细节踩过的坑在这里集中说一下。第一字符集必须用utf8mb4不要问为什么。药物名称里出现的生僻字、特殊符号、上下标字符utf8根本存不下导入的时候会变成问号等发现时脏数据已经进去了只能删了重导。这种问题在药品数据里不是罕见情况所以直接从源头上选utf8mb4。第二所以涉及名称、靶点、适应症的字段都建议建索引。但不要滥用我见过有人把每个字段都加上索引结果插入性能差到离谱。索引的原则是“查询频率最高的字段优先建”比如target、highest_status、generic_name。联合索引idx_drug_id_date这种是为了满足“查某个药的状态历史”这种高频查询。第三JSON字段慎用。fact_dev_status里我放了一个detail_json用来存原始数据快照方便回溯。但JSON字段不能作为查询条件走索引MySQL的JSON索引支持有限。如果后续要按JSON内部字段筛选还是拆成独立字段比较稳。我现在的做法是快照只做展示用所有查询字段全部单独列出来。4. 数据采集与清洗ETL才是重头戏4.1 全量拉取与增量更新的实操方法建表之后真正耗时间的不是建库而是“喂数据”。我们的数据采集分了三个阶段。第一个阶段是全量初始化。对ClinicalTrials.gov我写了一个Python脚本按月份分片拉取全部试验数据解析JSON后写入临时表再用SQL做去重和关联。这个阶段不要直接往主表插我建议所有原始数据先进临时表或者staging表清洗完再insert into select进主表。好处是万一清洗逻辑出问题主表数据还是干净的重新跑一版就行。第二阶段是增量同步。每天凌晨跑定时任务拉取近24小时更新的试验和药品信息。增量同步比全量简单很多难点在于识别“更新”还是“新增”。我的处理方式是维护一张source_sync_log表记录每个数据源的最新同步时间每天按时间范围拉取后用source_id去主表查存在就做更新不存在就做插入。批量处理的时候有几种工具可用。数据量小可以用Python开多线程直连数据库数据量大建议上专门的数据库同步工具。我们内部试过用DataX做流式同步因为对接的数据源很多DataX的插件机制比较省事。自己开发脚本的话要注意事物控制建议每500条提交一次事务避免大批量插入时锁表时间过长把业务查询拖死。4.2 去重、别名映射与状态归一清洗阶段有三个事必须做好去重、别名映射、状态归一。去重听起来简单实际很容易出错。同一个药物来自FDA、EMA、ClinicalTrials.gov的记录名称拼写可能不完全一样。比如有的源写“Abiraterone”有的写“Abiraterone Acetate”通用名和盐基形式分不开。这个时候不能只靠名字匹配要搭配分子式或者CAS号。我的方案是优先用CAS号关联没有CAS号再看“通用名靶点”组合最后才靠人工规则确认。去重完了之后要把最终保留的那条记录的source_id记下来下次如果再进来同一条直接跳过杜绝重复。别名映射是工作量最大的环节。一个药从研发代码到最终商品名可能换四五个名字。我建了一张映射表之后又做了一个自动生成规则把原来数据源的原始名称全部整理成小写去掉空格和特殊符号生成一个“归一化检索名”。查询的时候先按归一化检索名查查不到再走别名表。这样做有一个好处——用户不需要记得这个药到底叫什么只要他输入的名字是别名表里的任意一个都能定位到同一个drug_id。我实际测试过用商品名、研发代码、曾用名、乃至拼写不太标准的名称都能正确命中药物主表。状态归一这一项是把各数据源的状态枚举翻译成我们自己的五级标准发现、临床前、临床、申请上市、已上市。每个大状态下面再细分临床分为I期、II期、III期。源数据里写的“Phase 2/3合并试验”我们策略是归到较高的III期并在detail_json里留原始描述。这样虽然损失了一点细节但换来的是统计口径的一致性。做管线条数统计、竞品阶段对比的时候口径统一的价值会体现得淋漓尽致。5. 查询库的日常维护与性能优化5.1 更新机制与同步策略的落地库建好只是开始能不能一直好用靠的是日常维护。我们采用了“主库汇总库”的结构。主库存明细数据给开发和数据团队用汇总库跑预统计给业务同事做报表查询。每天早上增量同步完成后接着跑一套预统计任务把“各阶段管线数量”“各靶点分布”“各企业研发进度”等常用维度算好物化成汇总表。业务同事查询汇总表秒开不需要每次都对几万条明细做group by。这个设计非常推荐因为药物数据库的特点是写少读多而且查询模式相对固定非常适合物化。数据同步这件事前期用原生的定时任务后来数据源多了原生的脚本越来越难维护各种依赖、重跑、失败重试的逻辑全揉在一起。我们改成了专门的数据库同步工具配置化地管理所有同步任务。工具的好处是它能管理同步的断点比如某个源昨天晚上因为网络问题断了第二天启动时它会从断点继续而不是把全量再拖一遍。这类工具网上有不少开源方案选型的时候重点看三点能不能可视化监控、失败重试机制是否完善、性能有没有瓶颈。药数据的更新还有一个特别的地方回填修正。比如FDA突然撤回了一个加速审批那这个药的状态就要往回改历史上“已上市”的记录不能删要保留在时间线里只是追加一条“撤市/暂停”的新状态记录。所以我在状态表设计时保留的是时间线而非最新值任何更新都是在时间线上追加只有drug主表的highest_status是跟随最新的。这种设计保证了历史的真实性做回溯分析时才不会把过程数据搞丢。5.2 查询性能优化索引、连接池与慢查询业务同事感受最深的就是“输入关键词查得有多快”。这里我把性能调优的实操记录列一下。第一批慢查询出现在“按靶点查全系列药物”的场景。target字段本身有索引但查询时同事习惯用模糊匹配比如“%PD-1%”索引直接失效全表扫描几百万行慢到崩溃。后来把两个方案结合起来精确查询走索引模糊查询走ElasticSearch。我们把药物名称、别名、靶点、适应症同步到ES里所有全文搜索类需求都走ES关系库只处理精确匹配和联表统计。各司其职之后查询响应从十几秒降到几百毫秒。关系库侧的性能配置也有讲究。MySQL的连接池是必配项不要用默认值。我遇到过开发环境一切正常生产环境一压测就报too many connections后来才发现连接池最大连接数设了200但业务服务开了10个实例每实例默认创建了50个连接高峰期直接把MySQL连接数打满。正确做法是连接池最大连接数乘以服务实例数要明显低于数据库max_connections的80%上限同时设置合理的最小空闲连接数和超时回收时间。连接是稀缺资源不是开得越多越好。索引优化这块我建议大家定期开慢查询日志。我每个月看一次慢查询日志把执行超过1秒的SQL捞出来分析是缺索引、SQL写法问题、还是数据量增长导致的。很多慢SQL的解法非常简单比如where条件里字段明明是drug_id但传参类型是字符串MySQL没法用整型索引加一个类型转换就快了几十倍这种问题排查资料上很少写。6. 常见问题与排查实录6.1 数据重复与ID冲突的处理运行了两个月之后遇到的第一个重大问题就是数据重复和ID冲突。具体场景是同一个药物来自FDA的记录和来自ClinicalTrials.gov的记录经过关联匹配后本应该合并成一条主数据但因为两边记录的drug_code格式不一致一边是“AB-123”一边是“AB 123”自动匹配失败生成了两条主数据。业务同事查询时发现同一种药出现两条阶段信息还不一样直接在群里问是不是数据错了。我的排查思路是这样先查ETL日志确认两边记录的source_id都在再查dim_drug_alias发现两条主数据之间没有建立别名映射最后就是修正——把这第二条的generic_name定为别名指向第一条的drug_id并把相关的事实表记录全部重新关联。这里有一个教训主数据合并要趁早数据量小的时候手工merge很轻松等到几千条脏数据了再处理光写矫正SQL就能写一天。ID冲突的另一个来源是自增主键。虽然我们用AUTO_INCREMENT但增量同步时如果用“主键存在就更新”的逻辑很容易因为source_id不是主键而造成重复插入。解决方法是给事实表的逻辑键加唯一索引比如fact_dev_status的(drug_id, source_system, source_id)组合唯一一旦重复插入就报错让ETL任务失败从而触发告警而不是无声无息地把数据写进去。6.2 数据库连接与锁问题的现场复盘有一阵子生产库频繁出现“数据库死锁”报错业务同事反馈查询偶尔卡死几秒。我打开监控一看死锁基本都集中在对fact_dev_status表的批量插入和业务查询同时发生时。原因是当时ETL任务每批插入500条插入同时还有一个后台统计任务要对该表做大范围group by两个事务互相持有锁并等待对方释放形成了死锁。现场处理办法有两个一是把批量插入改成每批100条减少单次事务持有锁的时间二是把统计任务改成读汇总表而不是直接查明细表从根本上避开锁冲突。MySQL有死锁自动检测机制会牺牲一个事务回滚重试但这不是根治方案结构上避免才是正道。连接问题也值得单独说。我们开始用Navicat直连生产库排查数据后来发现业务高峰期Navicat占用的空闲连接一直不释放几次把连接数顶到上限。这些图形化数据库管理工具用起来爽但大团队里一定要约束使用规范能走只读账号的绝不授予写权限能不用生产账号的绝对不用。我后来给每个同事开了独立的只读账号写权限只有自动化脚本服务持有。另外还处理过一个小问题有同事拿SQLite导了一份数据做临时分析图省事把SQLite文件直接放在共享盘上结果多人同时打开时出现“database is locked”。这个不算数据库软件的错是使用场景选错了。SQLite只适合单机小数据量的临时分析团队共享还是要走正式的关系库哪怕是PostgreSQL在Docker里起一个也比SQLite硬扛强。6.3 数据质量对账的一些体会最后说一个不是技术问题的技术问题——数据对账。库跑了半年后我们发现FDA官网上的某个药物状态和我们库里的不一致。查了一圈原因是我们对“状态变更”的识别逻辑漏掉了一种情况FDA更新了状态但该药物在ClinicalTrials.gov上的对应试验信息没变导致我们增量同步时判断“无变更”没有更新主表。从那以后我养成了一个习惯每个季度做一次全量对账拿官方源的最新数据和库里的数据做全字段比对自动生成差异报告。不要只依赖增量同步定期全量对账是保证数据质量的底线。这次对账还顺带发现了一个之前没注意到的细节——EMA的授权日期和上市日期是两个概念好多人混淆我们在数据字典里单列了字段说明后才规范下来。做这个库最大的体会就是两个字耐心。数据源碎、口径杂、更新节奏不一这些困难都可以靠合理的表结构和规范的ETL流程解决。最难的不是技术而是让每个环节都按规矩来。查药这件事原来要开八个网页现在打开内部系统一条SQL带走。一开始建库觉得是给团队添了活现在看节省下来的调研时间早就把建库的投入赚回来了。