简介这份资源面向医疗信息化开发者、数据工程师及SNOMED CT术语集使用者提供在关系数据库中表示SNOMED CT的完整脚本方案解决将RF2分发格式的术语版本导入MySQL、PostgreSQL、MSSQL或Neo4j等数据库的实际问题适合具备一定SQL与数据库运维基础的中高级技术人员。压缩包共115个文件约434KB以64个SQL脚本为核心辅以11个Python脚本、8个Markdown说明文档以及Perl、Shell、批处理、Cypher、配置模板等文件分别承担建表填充、数据转换、加载调度与图数据库更新等职责。目前已有1100人学习下载。读者可获取按数据库类型分目录组织的加载脚本覆盖从RF2术语版本到关系型或图数据库的完整落地路径并借助配置模板与校验脚本快速搭建可复用的术语服务环境同时参考贡献说明扩展其他数据库支持减少自行摸索成本。1. 把 SNOMED CT 塞进关系库为什么“能存”和“能用”是两回事很多团队第一次接触 SNOMED CT都会下意识把它当成一张大字典一个 concept 一行配个 description 表再挂个 relationship 表导入完事。真到写查询的时候才发现想找“某概念的所有后代”“某概念到根的路径”“两个概念之间的最短关系链”SQL 要么写不出来要么写出来慢到没法上线。问题不在数据库在于 SNOMED CT 本质是一张有向无环图而关系库擅长的是集合运算不是图遍历。这篇笔记讲的就是 SNOMED-CT-Database 这个方向怎么在关系数据库里表示 SNOMED CT让它既能承载全量概念、描述和关系又能支撑层级查询、语义检索和推理前置处理。适合正在做临床术语服务、电子病历语义层、医学 NLP 词典构建的工程师也适合手里已经有一份 SNOMED CT 分发包、却卡在“怎么建表、怎么查、怎么不炸”的人。下面按建表、导入、查询、避坑、进阶的顺序把可复现的路径拆开。2. 先定模型再建表SNOMED CT 的三张核心表怎么切2.1 概念、描述、关系为什么不能只建一张宽表SNOMED CT 的发布文件通常按 RF2 格式组织核心是 Concept、Description、Relationship 三类。概念表存 conceptId 和 active 标志描述表存 conceptId 对应的术语文本、语言、类型FSN 或 synonym关系表存 sourceId、destinationId、typeId、characteristicType。很多人图省事把描述直接拼进概念表结果一个概念有多个同义词时行数爆炸更新时还要处理文本去重。更关键的是关系表。SNOMED CT 的 IS-A 关系只是 relationship 表里 typeId 等于某个特定值的一个子集除此之外还有“finding site”“associated morphology”等语义关系。如果只建一张 concept 表加一个 parentId 字段就只能表达单继承树而 SNOMED CT 是多父节点 DAG。常见做法是保留三张主表再加一张关系类型维表把 IS-A 和其他关系分开索引。-- 概念表只存概念标识和状态不存文本 CREATE TABLE snomed_concept ( concept_id BIGINT PRIMARY KEY, active SMALLINT NOT NULL DEFAULT 1, effective_time DATE NOT NULL, module_id BIGINT NOT NULL ); -- 描述表一个概念多条描述用 type_id 区分 FSN 和同义词 CREATE TABLE snomed_description ( description_id BIGINT PRIMARY KEY, concept_id BIGINT NOT NULL REFERENCES snomed_concept(concept_id), term TEXT NOT NULL, language_code VARCHAR(8) NOT NULL, type_id BIGINT NOT NULL, active SMALLINT NOT NULL DEFAULT 1 ); -- 关系表source 到 destinationtype_id 区分 IS-A 和属性关系 CREATE TABLE snomed_relationship ( relationship_id BIGINT PRIMARY KEY, source_id BIGINT NOT NULL, destination_id BIGINT NOT NULL, type_id BIGINT NOT NULL, characteristic_type_id BIGINT NOT NULL, active SMALLINT NOT NULL DEFAULT 1 );这三张表的字段不是随便定的。concept_id 用 BIGINT 是因为 SNOMED CT 的标识符长度会超出 32 位整型范围effective_time 保留是为了做增量版本对比description 表里的 language_code 和 type_id 必须建联合索引否则按语言和术语类型过滤时会全表扫。relationship 表上 source_id、destination_id、type_id 三个字段各自建索引IS-A 查询走 source_id type_id 组合索引。2.2 索引与约束哪些字段必须加哪些加了反而拖慢导入导入阶段和查询阶段的索引策略是矛盾的。全量 SNOMED CT 的关系表通常在百万行级别如果导入前就把所有索引建好每插一行都要更新 B 树导入时间会翻几倍。我一般会先建表不建索引用批量导入把数据灌进去再统一建索引。必须建的索引有三个description 表上 (concept_id, active) 用于按概念取术语relationship 表上 (source_id, type_id, active) 用于向下找子节点relationship 表上 (destination_id, type_id, active) 用于向上找父节点。唯一约束只加在主键上不要给 term 加唯一索引因为不同概念可以有相同文本加了会直接导入失败。-- 导入完成后再建索引顺序按查询频率排 CREATE INDEX idx_desc_concept ON snomed_description (concept_id, active); CREATE INDEX idx_rel_source ON snomed_relationship (source_id, type_id, active); CREATE INDEX idx_rel_dest ON snomed_relationship (destination_id, type_id, active); CREATE INDEX idx_desc_term ON snomed_description USING gin (to_tsvector(simple, term));最后那个 GIN 索引是给全文检索用的PostgreSQL 里用 to_tsvector 把术语文本转成 tsvector。如果数据库是 MySQL就换成 FULLTEXT 索引但中文和特殊字符的分词效果会差一些需要额外处理。参数上maintenance_work_mem 在重建索引时调大能明显缩短建索引时间我一般设成 512MB 到 1GB视机器内存而定。2.3 用递归 CTE 表达 IS-A 层级最小可用查询长什么样关系库表达层级最直接的工具是递归 CTE。给定一个概念找它所有后代用 WITH RECURSIVE 从源节点出发沿着 IS-A 关系向下展开。这里 type_id 的具体数值取决于你用的 SNOMED CT 版本常见做法是先从关系类型表里查出 IS-A 对应的标识再作为参数传入不要硬编码。-- 查某概念的所有后代含自身IS-A 的 type_id 用参数传入 WITH RECURSIVE descendants AS ( SELECT concept_id FROM snomed_concept WHERE concept_id :root_id UNION SELECT r.destination_id FROM snomed_relationship r JOIN descendants d ON r.source_id d.concept_id WHERE r.type_id :is_a_type_id AND r.active 1 ) SELECT c.concept_id, d.term FROM descendants c JOIN snomed_description d ON d.concept_id c.concept_id WHERE d.active 1 AND d.type_id :fsn_type_id;这段查询的逻辑是锚点成员是根概念本身递归成员每次从当前结果集出发找 source_id 匹配且 type_id 为 IS-A 的目标节点UNION 去重防止环。参数 :root_id 是起点概念:is_a_type_id 是 IS-A 关系类型标识:fsn_type_id 是 FSN 描述类型标识。注意 UNION 而不是 UNION ALL因为 DAG 里同一个节点可能通过多条路径到达UNION ALL 会产生重复行数据量大时结果集膨胀得很快。3. 从 RF2 文件到可查库导入流程与批量优化3.1 RF2 文件解析字段顺序和转义不能想当然SNOMED CT 分发包里的 RF2 文件是制表符分隔的文本每行一条记录首行是列名。概念文件通常包含 id、effectiveTime、active、moduleId、definitionStatusId描述文件多出 conceptId、languageCode、typeId、term、caseSignificanceId关系文件包含 sourceId、destinationId、typeId、characteristicTypeId、modifierId。解析时最容易翻车的是 term 字段里可能包含制表符或换行必须用真正的 TSV 解析器不能简单按 \t 切分。import csv def parse_rf2(path): with open(path, r, encodingutf-8, newline) as f: reader csv.DictReader(f, delimiter\t, quotingcsv.QUOTE_NONE) for row in reader: # 跳过非当前有效记录减少导入量 if row.get(active) ! 1: continue yield row # 概念文件示例只取需要的列避免全字段入库 for rec in parse_rf2(sct_concept_full.txt): concept_id int(rec[id]) module_id int(rec[moduleId]) eff_time rec[effectiveTime] # 后续写入数据库这里用 csv.DictReader 并指定 delimiter 为制表符quoting 设为 QUOTE_NONE避免引号被误解析。active 过滤放在解析阶段能直接砍掉历史失效记录导入量通常能减少三到五成。effectiveTime 是字符串格式的日期入库前转成 DATE 类型方便后续做版本对比。3.2 批量写入一次提交多少行才不炸逐行 INSERT 在百万级数据面前不可接受。PostgreSQL 用 COPYMySQL 用 LOAD DATA LOCAL INFILE或者用驱动提供的 execute_values 批量提交。批量大小不是越大越好我一般从 5000 行一批开始试观察内存和 WAL 增长再往上调。批量太大时事务日志膨胀回滚代价高太小则网络往返次数多。import psycopg2 from psycopg2.extras import execute_values def bulk_insert(conn, rows, batch_size5000): sql INSERT INTO snomed_description (description_id, concept_id, term, language_code, type_id, active) VALUES %s with conn.cursor() as cur: for i in range(0, len(rows), batch_size): batch rows[i:i batch_size] execute_values(cur, sql, batch, page_sizebatch_size) conn.commit() # 每批提交避免长事务execute_values 会把多行拼成一条 INSERTpage_size 控制单次发送的行数。每批 commit 一次好处是失败时只丢当前批不用重跑全量。代价是提交次数多整体导入时间略长但可控性高。如果追求极致速度可以关掉 synchronous_commit导入完再打开但生产库上要谨慎。3.3 导入后校验行数、孤儿节点和环检测导入完成不等于数据可用。至少要跑三类校验行数对比确认概念、描述、关系的行数和源文件一致孤儿节点检测找 description 或 relationship 里引用了不存在 concept_id 的记录环检测确认 IS-A 关系里没有形成循环。环检测可以用递归 CTE 加路径数组一旦发现某节点重复出现在自己的祖先路径里就说明有环。-- 孤儿描述检测描述表里引用了不存在的概念 SELECT d.description_id, d.concept_id FROM snomed_description d LEFT JOIN snomed_concept c ON c.concept_id d.concept_id WHERE c.concept_id IS NULL LIMIT 100; -- 环检测沿 IS-A 向上找祖先路径里出现重复即报警 WITH RECURSIVE ancestors AS ( SELECT source_id, destination_id, ARRAY[source_id] AS path FROM snomed_relationship WHERE type_id :is_a_type_id AND active 1 UNION ALL SELECT a.source_id, r.destination_id, a.path || r.source_id FROM ancestors a JOIN snomed_relationship r ON r.source_id a.destination_id WHERE r.type_id :is_a_type_id AND r.active 1 AND NOT r.destination_id ANY(a.path) ) SELECT * FROM ancestors WHERE destination_id ANY(path) LIMIT 10;孤儿检测用 LEFT JOIN 找空值简单直接。环检测里用 path 数组记录走过的节点NOT ... ANY 阻止继续展开已访问节点最后再筛出 destination 出现在 path 里的行。正常 SNOMED CT 不应该有环如果查出来多半是导入时把非 IS-A 关系误当成了 IS-A或者版本混用。4. 查询与推理前置把图遍历写成可维护的 SQL4.1 找祖先、后代和共同祖先三个模板够用日常查询里最高频的是三类找某概念的所有祖先、所有后代、两个概念的共同祖先。祖先查询把递归方向反过来从当前节点沿 destination_id 向上找 source_id共同祖先则是分别求出两个概念的祖先集合再取交集。这三个模板覆盖了术语浏览、分类导航和简单的语义相似度计算。-- 找某概念的所有祖先含自身 WITH RECURSIVE ancestors AS ( SELECT concept_id FROM snomed_concept WHERE concept_id :concept_id UNION SELECT r.source_id FROM snomed_relationship r JOIN ancestors a ON r.destination_id a.concept_id WHERE r.type_id :is_a_type_id AND r.active 1 ) SELECT concept_id FROM ancestors; -- 两个概念的共同祖先分别求祖先集后取交集 WITH anc_a AS ( /* 同上根为 :id_a */ ), anc_b AS ( /* 同上根为 :id_b */ ) SELECT concept_id FROM anc_a INTERSECT SELECT concept_id FROM anc_b;参数 :concept_id、:id_a、:id_b 是查询起点:is_a_type_id 是 IS-A 类型标识。共同祖先查询里用 INTERSECT 而不是 JOIN是因为 INTERSECT 会自动去重结果更干净。如果概念层级很深递归 CTE 的深度可能触及数据库默认限制PostgreSQL 里可以用 statement_timeout 和 max_recursion_depth 相关参数控制必要时把中间结果物化成临时表。4.2 把传递闭包物化什么时候该用闭包表换性能递归 CTE 写起来直观但每次查询都要现场展开概念层级深、并发高时 CPU 吃不消。常见优化是建一张闭包表提前把每个节点到所有祖先的路径存下来查询时直接等值匹配。闭包表的代价是存储膨胀一个深度为十层的概念会贡献十条路径记录全量闭包表通常是关系表行数的数倍。-- 闭包表ancestor_id 到 descendant_id 的所有路径含自身 CREATE TABLE snomed_closure ( ancestor_id BIGINT NOT NULL, descendant_id BIGINT NOT NULL, depth INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id) ); -- 从关系表一次性生成闭包depth 表示相隔层数 INSERT INTO snomed_closure (ancestor_id, descendant_id, depth) WITH RECURSIVE paths AS ( SELECT source_id AS ancestor_id, destination_id AS descendant_id, 1 AS depth FROM snomed_relationship WHERE type_id :is_a_type_id AND active 1 UNION ALL SELECT p.ancestor_id, r.destination_id, p.depth 1 FROM paths p JOIN snomed_relationship r ON r.source_id p.descendant_id WHERE r.type_id :is_a_type_id AND r.active 1 ) SELECT ancestor_id, descendant_id, MIN(depth) FROM paths GROUP BY ancestor_id, descendant_id;闭包表建好后查后代就是 SELECT descendant_id FROM snomed_closure WHERE ancestor_id :id走主键索引毫秒级返回。depth 字段保留最小层数用于计算语义距离。注意闭包表要在数据导入完成后生成之后每次增量更新都要同步维护否则会出现新旧数据不一致。4.3 语义检索术语文本和概念层级的联合过滤只按文本搜“糖尿病”会命中一堆包含该词的描述但用户往往想要的是某个概念及其下位概念。做法是先用全文索引筛出候选概念再用闭包表或递归 CTE 把候选概念的后代一并纳入。两步分开执行比在一条 SQL 里同时做文本匹配和图遍历更容易调优。-- 第一步全文检索拿到候选概念 WITH matched AS ( SELECT DISTINCT concept_id FROM snomed_description WHERE to_tsvector(simple, term) plainto_tsquery(simple, :keyword) AND active 1 ) -- 第二步把候选概念的后代一起查出来 SELECT c.concept_id, d.term FROM matched m JOIN snomed_closure cl ON cl.ancestor_id m.concept_id JOIN snomed_concept c ON c.concept_id cl.descendant_id JOIN snomed_description d ON d.concept_id c.concept_id WHERE d.active 1 AND d.type_id :fsn_type_id;plainto_tsquery 会把关键词转成查询表达式simple 配置不做词干还原适合医学术语这种不能随便截断的场景。如果术语里有中文需要额外装分词扩展或者退化成 LIKE 加前缀索引。第二步用闭包表展开后代比递归 CTE 稳定代价是闭包表必须提前建好并保持同步。5. 避坑与排查导入和查询阶段最容易翻车的五件事5.1 现象导入到一半报主键冲突重跑又从头开始原因通常是源文件里存在重复行或者上一次导入中断后残留了部分数据而导入脚本没有做幂等处理。RF2 文件在合并多个版本时同一个 id 可能出现多次active 状态不同。解决方式是在解析阶段按 id 去重保留 effectiveTime 最新的一条导入时用 INSERT ... ON CONFLICT DO NOTHING 或先清空目标表再灌。重跑前务必确认目标表状态不要盲目重来。5.2 现象递归查询报错或返回结果不全递归 CTE 默认有深度限制层级特别深的概念可能在展开中途被截断。另一个常见原因是 UNION 和 UNION ALL 用混了用 UNION ALL 时 DAG 的多路径会导致结果重复用 UNION 时又可能因为去重把合法的不同路径合并。排查时先单独跑一层确认 IS-A 的 type_id 是否正确再看递归条件里的 active 过滤有没有把有效关系误杀。必要时把中间结果落临时表分段验证。5.3 现象全文检索搜不到明明存在的术语多半是分词配置和文本预处理不匹配。simple 配置按空格和标点切分如果术语里带连字符或括号查询词和索引词的切分结果可能不一致。另一个原因是索引没建在 active 过滤后的数据上失效描述混进了索引。解决方式是统一用 plainto_tsquery 而不是 to_tsquery避免手动拼查询语法索引表达式和查询表达式保持完全一致定期 REINDEX 清理膨胀。5.4 现象闭包表生成后查询变快但数据更新后结果对不上闭包表是物化视图性质的表源关系表变更后不会自动同步。增量更新时如果只插不删旧路径会残留如果全量重建期间查询会看到不一致的中间状态。常见做法是在事务里先删受影响节点的闭包记录再重新生成或者用版本号字段做双表切换。更新频率高的场景闭包表维护成本可能超过收益要权衡。5.5 现象多语言描述混在一起按语言过滤后结果为空description 表里 language_code 的取值在不同分发包里可能不一致有的用 ISO 代码有的用内部标识。如果查询时硬编码了某个值换一个分发包就查不到。解决方式是先把 language_code 的 distinct 值查出来确认实际取值再作为参数传入。同时注意 FSN 和 synonym 的 type_id 也可能因版本而异同样不要硬编码。6. 进阶技巧用物化路径和版本对比把维护成本压下来闭包表解决了查询性能但维护成本不低。如果概念层级相对稳定可以考虑物化路径方案给每个概念存一个从根到自身的路径字符串查询后代时用前缀匹配。路径可以用斜杠分隔的 id 串表示配合 B 树索引LIKE prefix% 就能命中所有后代。代价是路径长度随层级增长超深节点会撑大索引而且节点移动时要批量更新路径。-- 物化路径path 形如 /root/child/grandchild/ ALTER TABLE snomed_concept ADD COLUMN path TEXT; CREATE INDEX idx_concept_path ON snomed_concept (path text_pattern_ops); -- 查某概念的所有后代前缀匹配 SELECT concept_id FROM snomed_concept WHERE path LIKE :node_path || %;text_pattern_ops 让 LIKE 前缀匹配能走索引这是 PostgreSQL 上的关键细节不加的话会退化成全表扫。node_path 是目标概念的路径末尾带斜杠避免 /a/b 误匹配 /a/bc。物化路径适合读多写少、层级不频繁变动的场景比如术语服务对外提供查询接口内部数据按季度更新。版本对比是另一个实用技巧。SNOMED CT 按版本发布concept、description、relationship 都有 effective_time。把两个版本的快照分别导入带版本字段的表用 FULL OUTER JOIN 对比就能列出新增、失效和修改的概念。这对做术语变更审计和下游系统同步很有用。-- 对比两个版本的概念差异 SELECT COALESCE(a.concept_id, b.concept_id) AS concept_id, CASE WHEN a.concept_id IS NULL THEN added WHEN b.concept_id IS NULL THEN removed ELSE changed END AS change_type FROM snomed_concept_v1 a FULL OUTER JOIN snomed_concept_v2 b USING (concept_id) WHERE a.concept_id IS NULL OR b.concept_id IS NULL OR a.active b.active;这段查询里 v1 和 v2 是同一张表按版本过滤出来的视图或临时表。change_type 只区分了增删改实际使用时还可以加上字段级对比比如 module_id 或 definitionStatusId 是否变化。我一般会把差异结果落一张变更日志表下游系统按日志增量同步避免每次全量拉取。最后说个血泪经验别在业务库里直接跑全量闭包表生成那个 INSERT 会锁住关系表线上查询直接排队。我现在的习惯是先在只读副本或临时库上生成校验行数和抽样路径正确后再通过表切换上线。版本对比也一样先在小版本上验证 SQL 逻辑再放到全量数据上跑。希望帮到你。本文还有配套的精品资源点击获取
