简介这份数据库脚本集合面向需要将 SNOMED CT 术语集部署到关系型或图数据库的开发者与数据工程师重点解决从 RF2 分发格式到 MySQL、PostgreSQL、MSSQL 与 Neo4j 的建库、装载和参数配置问题。压缩包共 115 个文件、约 434KB以 64 个 SQL 脚本为主体配合 11 个 Python 脚本、8 个 Markdown 说明及多种批处理与模板配置文件覆盖建表、导入、路径设置和跨库适配等环节。脚本按数据库类型分目录组织并保留建表、装载、校验与版本更新相关文件便于按目标库快速定位入口。已有 1104 人学习说明它在医学术语库构建场景中具有参考价值。读者可了解不同数据库中的表结构设计、RF2 数据装载流程与脚本组织方式并借助现成脚本减少从零搭建术语库的重复工作对学习术语编码、表关系映射和批量导入流程也有帮助。1. 三十多万条概念进关系库SNOMED CT 落地为什么绕不开数据库第一次打开 SNOMED CT 的 RF2 全量包很多人第一反应是上三元组库或图数据库——它本质就是一部带层级的有向无环图。可真到业务侧要按诊断名模糊搜索、按科室过滤、再和病历主表 join图库那一层反而成了额外负担。在关系数据库里表示 SNOMED CT说到底是把一部图拆成几张带主键的宽表再用索引和闭包结构把“图”补回来。这件事适合三类人正在做临床数据平台、要把术语接进业务库的工程师要给检索和统计提供标准概念层的分析师以及被术语升级折磨过一次、想提前把表结构定对的团队。下面按实际落地顺序讲先拆表再解决层级查询最后说那些跑过一遍才会遇到的坑。2. RF2 拆表概念、描述、关系怎么落成三张主表RF2 发布包看起来是一堆制表符分隔的文本文件命名规则里带 Full、Snapshot、Delta 三种视图。Full 保留每个 id 的全部历史版本Snapshot 每个 id 只留最新一行Delta 只有本次发布变更的行。生产库我一般导两份Snapshot 进主表供线上查询Delta 进一张带 (id, effective_time) 复合键的历史表做追溯。这个选择必须在建表那天定下来后面再改代价很大——主表用单列主键还是复合主键直接决定你能不能不丢历史。2.1 三条主轴先看清再决定表结构不管发布包里有多少文件核心永远是三张Concept、Description、Relationship。Concept 是一行一个概念只有状态和定义性质Description 是一对多一个概念至少一条全称名FSN若干条同义词Relationship 是边表里面既有 IS A 这种层级边也有“发生在某解剖部位”这种属性边。SCTID 本身带分区信息末两位之前的那一段是 partition identifier00 表示概念、01 表示描述、02 表示关系1x 段是各种 Refset。所以 concept.id 和 description.id 永远不会撞三张表各用各的 id 做主键是安全的。这个特性值得在评审时讲清楚否则总有人问“为什么不搞一张统一的 entity 表”。不建议把三类东西揉进一张宽表。描述是一对多硬塞进概念表就得用数组或者拼接字符串检索和排序立刻失控关系是标准的边表单独一张表才能建 (source_id, type_id) 这种复合索引。Refset 另开一组表语言偏好、映射、历史关联都挂在那里不要和主表混。2.2 PostgreSQL 建表字段类型和几个必须现在定死的选择id 一律 bigint。SCTID 最长 18 位bigint 完全放得下用 varchar 存会让每一个 join 都多一次字符串比较几十万行上看不出上千万行的闭包表上就是另一回事。active 在文件里是 1/0落库转 boolean别偷懒存 smallint后面每个 where 都要写 active 1很容易漏。term 用 text不要 varchar(255)——真实数据里确实存在超长描述。CREATE SCHEMA IF NOT EXISTS snomed; -- 概念partition identifier 00 CREATE TABLE snomed.concept ( id bigint PRIMARY KEY, effective_time date NOT NULL, active boolean NOT NULL, module_id bigint NOT NULL, definition_status_id bigint NOT NULL -- 原语 / 完全定义 ); -- 描述partition identifier 01一个概念多条 CREATE TABLE snomed.description ( id bigint PRIMARY KEY, effective_time date NOT NULL, active boolean NOT NULL, module_id bigint NOT NULL, concept_id bigint NOT NULL REFERENCES snomed.concept(id), language_code char(2) NOT NULL, type_id bigint NOT NULL, -- 全称名 / 同义词 term text NOT NULL, case_significance_id bigint NOT NULL ); -- 关系partition identifier 02IS A 也在这张表里 CREATE TABLE snomed.relationship ( id bigint PRIMARY KEY, effective_time date NOT NULL, active boolean NOT NULL, module_id bigint NOT NULL, source_id bigint NOT NULL REFERENCES snomed.concept(id), destination_id bigint NOT NULL REFERENCES snomed.concept(id), relationship_group smallint NOT NULL, type_id bigint NOT NULL, characteristic_type_id bigint NOT NULL, -- 推断 / 断言 modifier_id bigint NOT NULL );主键的选择是有代价的。上面这份只存 Snapshot所以 id 单列主键如果打算把 Full 也留在同一张表里做历史追溯主键必须改成 (id, effective_time)否则同一 id 的历史行根本插不进去。我在两个项目里分别用过两种方案结论是业务查询走 Snapshot 单表、历史追溯另开一张后缀 _hist 的表比一张复合主键表混着用要清爽得多查询计划也稳定。提示装载阶段先别建外键把 REFERENCES 注释掉等数据灌完再 ALTER TABLE 补上。外键检查会让 COPY 慢一个数量级。2.3 用 COPY 装载别用 INSERT一百多万条描述、几百万条关系用 INSERT 一条条送是纯粹浪费时间。RF2 是干净的制表符分隔文本直接用 COPY。用 psql 的话一行就够# 文件名里的 release 换成你拿到的发布标识不要写死日期 psql -d terminology -c \copy snomed.concept (id, effective_time, active, module_id, definition_status_id) \ FROM sct2_Concept_Snapshot_INT_release.txt \ WITH (FORMAT text, DELIMITER E\t, NULL )需要做字段映射1/0 转 true/false或者要边导边校验时用 Python 包一层按批次提交避免一个大事务撑爆 WALimport csv, io, psycopg2 def load_rf2(conn, path, table, columns, bool_idx(), batch20000): 按批次把 RF2 文件 COPY 进表bool_idx 是需要 1/0 转 t/f 的列下标。 stmt fCOPY {table} ({,.join(columns)}) FROM STDIN total 0 with open(path, encodingutf-8-sig, newline) as fh, conn.cursor() as cur: buf [] # RF2 不使用引号包裹字段内不含换行QUOTE_NONE 避免误判 for row in csv.reader(fh, delimiter\t, quotingcsv.QUOTE_NONE): for i in bool_idx: row[i] t if row[i] 1 else f buf.append(\t.join(row)) if len(buf) batch: cur.copy_expert(stmt, io.StringIO(\n.join(buf) \n)) conn.commit() total len(buf) buf.clear() if buf: cur.copy_expert(stmt, io.StringIO(\n.join(buf) \n)) conn.commit() total len(buf) return total conn psycopg2.connect(dbnameterminology) load_rf2(conn, sct2_Description_Snapshot-en_INT_release.txt, snomed.description, [id,effective_time,active,module_id,concept_id, language_code,type_id,term,case_significance_id], bool_idx(2,))几个参数值得说清楚。encodingutf-8-sig是为了吃掉文件头可能存在的 BOM否则第一条记录的 id 会带不可见字符主键插入失败而报错信息完全看不出原因。batch20000是经验值太小提交次数多太大单次事务的 WAL 压力上来遇到锁等待不好中断。quotingcsv.QUOTE_NONE必须加RF2 里描述文本本身可能出现引号让 csv 模块去解析引号会把一行拆错。如果某一列存在空值COPY 的 text 格式默认把\N当 NULL而 RF2 里空字段通常直接是两个制表符相邻所以上面用NULL 显式声明空串即 NULL。这一条不写导入后会出现一堆空字符串和 NULL 混在一起后面IS NULL判断全失效。2.4 装完先跑四个校验别急着写业务查询数据灌进去不等于导对了。每次升级后我都会先跑这四条任何一条结果异常就不往下走。-- 1) 概念量级与生效比例用来和上一个版本对比 SELECT count(*) AS total, count(*) FILTER (WHERE active) AS active FROM snomed.concept; -- 2) 生效概念里有没有缺全称名的正常应为 0 SELECT count(*) FROM snomed.concept c WHERE c.active AND NOT EXISTS ( SELECT 1 FROM snomed.description d WHERE d.concept_id c.id AND d.active AND d.type_id 900000000000003001); -- 3) 关系两端的 id 是否都在概念表里孤儿边正常应为 0 SELECT count(*) FROM snomed.relationship r WHERE r.active AND ( NOT EXISTS (SELECT 1 FROM snomed.concept c WHERE c.id r.source_id) OR NOT EXISTS (SELECT 1 FROM snomed.concept c WHERE c.id r.destination_id)); -- 4) IS A 是否存在自环正常应为 0 SELECT count(*) FROM snomed.relationship WHERE active AND type_id 116680003 AND source_id destination_id;第 2 条是最容易被忽略的某些本地扩展模块的概念只有同义词没有全称名做展示时取不到名字界面上就是一串数字。第 3、4 条用来兜住文件截断和增量包错位——关系表少导一半查询不会报错只会静默地少返回结果这种问题在业务侧往往要一两周才被发现。3. 层级查询递归 CTE 撑不住的时候闭包表怎么建层级是 SNOMED CT 最核心的用法查“某个诊断下所有子类”“某个部位包含哪些结构”。关系表里 IS A 的 type_id 是固定的 116680003这条边把整部术语连成一棵严格说是张有向无环图一个概念可以有多个父类。业务 SQL 里天天写递归 CTE性能迟早出问题。3.1 递归 CTE 的最小写法与环保护向上找祖先是最常见的需求比如给一个概念打上所有上位标签WITH RECURSIVE up AS ( SELECT r.destination_id AS ancestor_id, 1 AS depth, ARRAY[r.source_id] AS path FROM snomed.relationship r WHERE r.active AND r.type_id 116680003 AND r.source_id :sctid UNION ALL SELECT r.destination_id, up.depth 1, up.path || r.source_id FROM up JOIN snomed.relationship r ON r.source_id up.ancestor_id WHERE r.active AND r.type_id 116680003 AND up.depth 32 AND NOT r.destination_id ANY(up.path) -- 环保护 ) SELECT DISTINCT ancestor_id FROM up;path数组是防护措施。正常情况下 IS A 不会成环但本地扩展模块加上人工维护的数据什么都有可能一旦成环没有 path 判断的递归会一直跑到内存耗尽把连接池占死排查起来非常费劲。depth 32是第二道闸SNOMED CT 的实际深度不会超过二十层超过基本可以判定数据有问题。向下找后代更贵因为一个高层概念比如“临床发现”下面挂着十几万个子类递归 CTE 要走十几万次索引扫描。单次查询可能几百毫秒QPS 一上来就顶不住。这时候不是调优的问题是模型选错了。3.2 闭包表一次算完换掉每次查询的递归闭包表就是把“祖先-后代-层数”这三元组全部物化下来。代价是体积收益是任意层级查询都退化成一次主键索引扫描。DROP TABLE IF EXISTS snomed.isa_closure; CREATE TABLE snomed.isa_closure ( ancestor_id bigint NOT NULL, descendant_id bigint NOT NULL, depth smallint NOT NULL, PRIMARY KEY (ancestor_id, descendant_id) ); INSERT INTO snomed.isa_closure (ancestor_id, descendant_id, depth) WITH RECURSIVE edge AS ( SELECT source_id, destination_id FROM snomed.relationship WHERE active AND type_id 116680003 ), walk AS ( SELECT source_id AS ancestor_id, destination_id AS descendant_id, 1 AS depth FROM edge UNION -- 用 UNION 而不是 UNION ALL 去重 SELECT w.ancestor_id, e.destination_id, w.depth 1 FROM walk w JOIN edge e ON e.source_id w.descendant_id WHERE w.depth 32 ) SELECT ancestor_id, descendant_id, min(depth) FROM walk GROUP BY 1, 2; -- 自己到自己深度 0方便写「含自身」的查询 INSERT INTO snomed.isa_closure SELECT id, id, 0 FROM snomed.concept WHERE active;这里UNION是关键。一个概念可以同时通过多条路径到达同一个祖先多父类场景用UNION ALL会让中间结果按路径数指数膨胀几百万行能撑出几十亿行中间集直接把临时表空间写满。加上顶层min(depth)取最短路径深度字段才有意义——用来做“只取 N 层以内”的裁剪。体积上闭包表通常比概念表大一两个数量级这个开销得提前和存储方沟通。我在一个项目里为了省空间试过只存到第五层结果业务侧要做“某大类下所有子类”的统计时直接缺数据最后老老实实全量重建。省这个空间不值得。有了闭包表查询就变成这样-- 「临床发现」及其所有后代含自身 SELECT c.id, d.term FROM snomed.isa_closure cl JOIN snomed.concept c ON c.id cl.descendant_id AND c.active JOIN snomed.description d ON d.concept_id c.id AND d.active WHERE cl.ancestor_id 404684003;3.3 增量升级时闭包表怎么重建最省事也最稳的做法是全量重建新版本导进主表在一个事务里重建isa_closure_new校验通过后ALTER TABLE ... RENAME换名。全量重建的耗时和概念数成正比通常在一杯咖啡的范围内比维护一套增量逻辑出错率低得多。真要做增量思路是只重算受影响的子图把本次 Delta 里 IS A 边有增删的 source_id 收集起来向上取这些节点的全部祖先集合 A向下取全部后代集合 D删除ancestor_id ∈ A AND descendant_id ∈ D的行再对 A 里每个节点重跑一次向下递归补回去。逻辑不复杂但边界特别多新增的边可能让两个原本无关的子树连起来没有专门的回归用例就不要碰。注意闭包表的 PRIMARY KEY (ancestor_id, descendant_id) 本身就是最好的索引不要再额外建 (descendant_id) 单列索引除非确实有反向查询否则只是白白拖慢重建。4. 避坑与排查升级、失效概念和 Refset 上最容易翻车的地方前三章是“怎么建”这一章是“建完为什么会出错”。下面五条都是我在真实升级窗口里踩过的按现象、原因、解决写能对号入座就省一次通宵。4.1 增量包直接追加上去历史版本把查询打乱现象升级后同一批检索条件返回的结果翻倍界面上同一个诊断名出现两遍但数据库里的概念总数看起来又没错。原因RF2 的 Delta 是“变更行”而不是“替换行”同一个 id 会因为状态变化、描述修订出现多行。直接往单列主键的表里 append要么主键冲突整批失败要么当初为了“能插进去”把主键去掉了结果同一个概念留下 active 为真和为假的两行。业务 SQL 里只写了WHERE concept_id ?没带 active 判断重复就此产生。解决Delta 一律先进 staging 表按 (id, effective_time) 去重保留最新一行再做 upsert。别在业务表上直接跑 MERGE 逻辑。-- 1) staging 表内同 id 只留 effective_time 最大的那一行 DELETE FROM stg.description s USING stg.description t WHERE s.id t.id AND s.effective_time t.effective_time; -- 2) 再合并进主表只接受更新的版本 INSERT INTO snomed.description AS d (id, effective_time, active, module_id, concept_id, language_code, type_id, term, case_significance_id) SELECT id, effective_time, active, module_id, concept_id, language_code, type_id, term, case_significance_id FROM stg.description ON CONFLICT (id) DO UPDATE SET effective_time excluded.effective_time, active excluded.active, term excluded.term WHERE excluded.effective_time d.effective_time;WHERE excluded.effective_time d.effective_time这句不能省。重跑一次导入脚本时没有它就会用旧版本覆盖新版本而这种回退在数据上看不出任何异常只有等到某个新概念查不到才发现。4.2 概念失效了旧病历里的编码突然查不到名字现象历史病历展示时一部分诊断名变成空白或者直接显示数字 id。查库发现那个概念的 active 已经是 false。原因概念被停用后通常会给出历史关联指向替代概念。这部分数据不在三张主表里而在历史关联类 Refset 中很多人导数据时只导了 Concept/Description/Relationship把这组文件漏了。于是所有“旧编码到新编码”的映射断掉。解决把历史关联 Refset 一并导入单独建一张映射表字段至少包含旧 id、关联类型、新 id。展示层取名字的顺序改成当前概念名 → 历史关联找到的替代概念名 → 原 id 兜底。同时给业务侧一个明确的“该概念已停用”标记不要静默替换临床语义上停用和替换不是一回事。4.3 stated 和 inferred 混在一张表里层级少了一半现象用 IS A 做后代查询某些概念明明有上级却查不到它属于哪个大类或者统计出来的子类数量比预期少很多。原因关系表里混着两种性质的关系——人工断言的stated和推理机推导的inferred。断言关系只覆盖部分概念完整层级在推断关系里。装载时没做区分查询里也没按 characteristic_type_id 过滤两种数据混在一起结果就是既不完整也不一致。更隐蔽的情况是只导了断言关系看起来数据量正常层级却处处是断点。解决主表里保留全部关系但在建闭包表和做层级查询时统一按推断关系的 characteristic_type_id 过滤把条件封进视图别让业务方自己写。视图里同时固定active AND type_id 116680003这三个条件应该是绑定在一起的。4.4 term 检索大小写、变音符号和全称名混用现象搜索“diabetes”能搜到搜“Diabetes”结果变少带重音的拉丁字母完全搜不到列表里展示的诊断名长得不像人话一堆括号和“disorder”。原因LIKE %xx%在默认排序规则下是大小写敏感的变音符号没做归一化展示时取的是全称名而不是语言 Refset 里标记为首选的同义词。全称名是给人和机器做唯一性判断用的带语义标签不适合直接上界面。解决建表达式索引lower(term)或者装 unaccent 扩展后对归一化结果建 GIN 索引展示名走语言 Refset 的首选标记取不到再退化到全称名。这两件事都应该在建表当天定好事后补索引要锁表几百万行的表上就是一次停机。提示给展示名的取数逻辑单独做一个视图业务方只查视图不查 description 表。这样语言偏好规则调整时改一处就够了。4.5 索引建在装载之前全量导入慢十倍现象一次全量升级从二十分钟变成三个多小时磁盘 IO 打满其他业务跟着抖。原因主表和关系表上已经有四五个索引加外键每条 COPY 进去的行都要维护一遍 B-tree、检查一次外键引用。数据量翻倍时这个代价是非线性的。解决升级流程固定成“删索引 → 清表 → COPY → 建索引 → 加约束 → ANALYZE”。ANALYZE这一步不能省尤其闭包表刚建完没有统计信息时优化器会选错连接顺序一条本该 50 毫秒的查询跑成 5 秒还找不到原因。5. 进阶把子集和“可临床使用”视图提前算好最后说一个让整套方案真正好用的技巧不要让别人直接面对原始表。业务方要的从来不是“SNOMED CT 全库”而是几个稳定子集——诊断、操作、解剖部位外加“当前可临床使用”的过滤条件。这些应该在库里算好而不是让每个查询自己拼。做法是用闭包表把常用根节点的后代预计算成视图把 active、模块、关系性质这些条件全部封进去CREATE MATERIALIZED VIEW snomed.mv_clinical_finding AS SELECT c.id, d.term FROM snomed.concept c JOIN snomed.description d ON d.concept_id c.id AND d.active AND d.type_id 900000000000013009 JOIN snomed.isa_closure cl ON cl.descendant_id c.id WHERE c.active AND cl.ancestor_id 404684003; CREATE UNIQUE INDEX ON snomed.mv_clinical_finding (id);建完唯一索引就能用REFRESH MATERIALIZED VIEW CONCURRENTLY在线刷新升级窗口不用停查询。根节点换成“操作”“解剖结构”就是另外两个视图业务侧按视图查规则调整只改一处。配套的是一个回归用例表。每次升级前后跑同一批断言任何一条不符合预期就直接回滚不靠人眼比对断言期望不过说明什么生效概念数相对上版波动在 ±5% 内包不完整或被截断缺全称名的生效概念数0描述文件漏导孤儿关系边数0关系文件和概念文件版本不配套三个核心根节点的后代数与上版偏差在 ±10% 内闭包表重建失败或关系性质混用已知概念的祖先链路与基准一致层级数据被错误过滤这套东西的价值在第二次升级时才体现出来。第一次升级我图快只比对了总数结果描述文件少导了一个语言界面上大半诊断名变英文被业务方追着问了两天。从那以后我的习惯是任何一次术语升级先跑断言表过了再刷新视图两步都留日志。术语库这种“查询不报错但结果不对”的系统后悔药只能提前吃。希望帮到你。本文还有配套的精品资源点击获取