SNOMED CT术语集关系库建模与IS-A闭包查询优化

简介：面向医疗信息化开发者、数据库管理员与SNOMED CT初学者的术语库构建资料，围绕在关系数据库中表示SNOMED CT这一目标，提供从RF2分发格式到MySQL、PostgreSQL、MSSQL及Neo4j的建库与装载脚本，可用于本地术语服务原型、数据迁移验证和教学实验，也适合需要将术语版本落地到业务系统、做多库性能对比或搭建图数据库演示环境的技术人员。压缩包共115个文件、约434KB，以64个SQL脚本为主体，配合11个Python脚本、8个Markdown说明，以及Shell、Bat、Perl辅助脚本和多份cfg、ini、cnf配置模板，覆盖建表、批量导入、路径配置与图数据库更新检查等环节。已有1104人学习下载。读者可据此快速搭建多数据库环境，理解RF2数据到表结构的映射关系，复用导入流程、配置样例和排错线索，减少从零编写加载脚本的成本，也便于按目录替换版本或扩展其他数据库支持。

1. 一份术语集装进关系库：为什么不能只建一张"字典表"

去年帮一个做临床数据清洗的小组接活，对方扔过来一个压缩包，里面是 SNOMED CT 的全量发布文件，配套需求只有一句："把这些码和名字导进我们现有的关系数据库，用 SQL 直接查就行。" 这话听着简单，实际动手两小时就会发现问题——它压根不是"id 对 name"的字典。一个概念底下挂着多条描述、多条关系，概念之间还织成一张有向无环图。想用 SNOMED CT 做术语检索和编码转换，就必须把这份术语集按关系数据库的规则重新落一遍。这份资源解决的正是这件事：一套把 SNOMED CT 全量发布文件装进关系数据库的建表脚本、装载顺序、IS-A 层级闭包表和常用查询，闭包重建那段我习惯用 SQL PL 写成存储过程。适合后端和数据工程的同学，尤其是要在 SQL 里直接做"按父概念取子孙""按中文名找编码"这类查询的人。

2. 三张骨架表的字段与主键怎么定：从发布文件的列结构反推

2.1 发布文件的行结构决定了列的边界

SNOMED CT 的官方发布包是一堆制表符分隔的文本文件，核心就三个：概念文件、描述文件、关系文件。三者的列顺序是固定的，第一行是表头。概念文件里一行是id / effectiveTime / active / moduleId / definitionStatusId；描述文件多了conceptId / languageCode / typeId / term / caseSignificanceId；关系文件则是sourceId / destinationId / relationshipGroup / typeId / characteristicTypeId / modifierId。

这里有个反直觉的点：这些文件是"按版本行"组织的，不是"按实体行"。同一个描述 id 如果改过三次，全量文件里就会出现三行，effectiveTime不同，active标记不同。所以建表前必须先做一个决定——主键用id，还是用(id, effectiveTime)。

我的选择是前者：正式表只保留每个 id 最新effectiveTime的那一行，历史版本扔进归档表。理由是线上查询 99% 只关心"现在有效的术语是什么"，留着历史行会让每一条 SQL 都多一个effective_time = (select max(...))的子查询，索引全废。真要做版本追溯，再单独建一张*_history表接住全量行，互不干扰。

2.2 建表脚本：BIGINT、字符集与三个索引

-- 概念表：一行一个概念，只存最新版本 CREATE TABLE concept ( id BIGINT NOT NULL, -- SCTID，必须 64 位 effective_time CHAR(8) NOT NULL, -- yyyymmdd，用字符串避免时区问题 active SMALLINT NOT NULL, -- 1 有效 / 0 已停用 module_id BIGINT NOT NULL, definition_status_id BIGINT NOT NULL, CONSTRAINT pk_concept PRIMARY KEY (id) ); -- 描述表：一个概念对应多条描述（FSN、同义词、定义文本） CREATE TABLE description ( id BIGINT NOT NULL, effective_time CHAR(8) NOT NULL, active SMALLINT NOT NULL, module_id BIGINT NOT NULL, concept_id BIGINT NOT NULL, language_code CHAR(2) NOT NULL, type_id BIGINT NOT NULL, term VARCHAR(1024) NOT NULL, case_significance_id BIGINT NOT NULL, CONSTRAINT pk_description PRIMARY KEY (id) ); CREATE INDEX ix_desc_concept ON description (concept_id, active); CREATE INDEX ix_desc_term ON description (term); -- 关系表：IS-A 和属性关系都在这，靠 type_id 区分 CREATE TABLE relationship ( id BIGINT NOT NULL, effective_time CHAR(8) NOT NULL, active SMALLINT NOT NULL, module_id BIGINT NOT NULL, source_id BIGINT NOT NULL, destination_id BIGINT NOT NULL, relationship_group SMALLINT NOT NULL, type_id BIGINT NOT NULL, characteristic_type_id BIGINT NOT NULL, modifier_id BIGINT NOT NULL, CONSTRAINT pk_relationship PRIMARY KEY (id) ); CREATE INDEX ix_rel_src ON relationship (source_id, type_id, active); CREATE INDEX ix_rel_dst ON relationship (destination_id, type_id, active);

几处参数值得单独说。id一律用BIGINT，绝不能用INT——SCTID 是 64 位整数，常见的系统保留码段动辄 9000000000000 开头，早就超过 32 位了，踩过这个坑的人不止一个。active用SMALLINT而不是布尔类型，是为了在 DB2、Oracle、MySQL、PostgreSQL 之间来回导的时候不用改脚本，布尔在 Oracle 里压根没有原生类型。effective_time用CHAR(8)存yyyymmdd，比日期类型省一次格式转换，比对也快。term给到VARCHAR(1024)是留余量，个别全限定名的组合式描述很长，卡 255 会在装载时报截断。

关系表的两个索引方向不同：ix_rel_src服务"往下找子概念"，ix_rel_dst服务"往上找父概念"。两个都要建，只建一个的话另一半查询会变成全表扫。

2.3 首次全量装载：先入暂存表，按 effectiveTime 去重

全量文件动辄上百万行，直接往带主键的正式表灌会非常慢，而且遇到同一 id 的多版本行会直接主键冲突。我一般走"暂存表 → 去重 → 正式表"这条线。以 PostgreSQL 为例：

# 第一步：把原始文件灌进无约束的暂存表（表结构与正式表一致，去掉主键） psql -d termdb -c "\copy stg_description(id, effective_time, active, module_id, concept_id, language_code, type_id, term, case_significance_id) FROM 'sct2_Description_Full.txt' WITH (FORMAT csv, DELIMITER E'\t', HEADER true, ENCODING 'UTF8')"

-- 第二步：每个 id 只取 effective_time 最大的一行，灌进正式表 INSERT INTO description SELECT s.id, s.effective_time, s.active, s.module_id, s.concept_id, s.language_code, s.type_id, s.term, s.case_significance_id FROM stg_description s JOIN (SELECT id, MAX(effective_time) AS mt FROM stg_description GROUP BY id) m ON m.id = s.id AND m.mt = s.effective_time; -- 第三步：把全量历史留在归档表，方便后续查"某描述什么时候被停用" CREATE TABLE description_history AS SELECT * FROM stg_description;

逻辑很直白：GROUP BY id + MAX(effective_time)拿到每个 id 的最新版本号，再回连原表取整行。这一步在百万级的暂存表上大概几十秒，比在正式表上做 upsert 稳妥得多。装载顺序必须是概念 → 描述 → 关系，因为后两者引用的 id 得先存在。外键我一般不建，一是百万级数据建外键会让装载慢一个数量级，二是关系表里的type_id有些指向的是元数据概念，用外键约束反而别扭；约束交给装载后的自检 SQL 兜。

3. IS-A 层级查询的两种解法：递归 CTE 与传递闭包表

3.1 递归查询在小样本上很香，在生产上会失控

IS-A 关系在关系表里就是一堆(source_id, destination_id)的边，type_id = 116680003的那些行。想取某个概念的所有子孙，最自然的写法是递归 CTE：

WITH RECURSIVE descendants (id, depth) AS ( SELECT destination_id, 1 FROM relationship WHERE source_id = :root AND type_id = 116680003 AND active = 1 UNION -- 注意是 UNION 不是 UNION ALL SELECT r.destination_id, d.depth + 1 FROM relationship r JOIN descendants d ON r.source_id = d.id WHERE r.type_id = 116680003 AND r.active = 1 ) SELECT id, MIN(depth) FROM descendants GROUP BY id;

小范围试的时候挺顺，但放到几十万概念的规模上，问题就出来了。第一，一次从根节点展开的查询会反复扫描关系表，层级越深扫得越多，单条查询跑到十几秒很常见。第二，UNION ALL碰上数据里跨版本的自指行（source_id = destination_id）会无限展开，查询挂死。我第一次写的时候就是UNION ALL加没做去重，跑了两分钟没出结果，最后只能 kill 掉。改成UNION能靠去重兜住环，代价是每一层都要做一次排序去重，更慢。

3.2 闭包表的结构与索引

生产环境我一般直接上传递闭包表：把所有"祖先 → 后代"的路径预先算好存下来，查询时退化成一次带索引的等值 join。

CREATE TABLE concept_isa_closure ( ancestor_id BIGINT NOT NULL, descendant_id BIGINT NOT NULL, depth SMALLINT NOT NULL, -- 最短 IS-A 路径长度，0 表示自己 CONSTRAINT pk_isa_closure PRIMARY KEY (ancestor_id, descendant_id) ); CREATE INDEX ix_isa_desc ON concept_isa_closure (descendant_id, depth);

depth我用的是"最短路径长度"，不是"任一层的深度"。这个语义在做"往上取 N 级父概念"的时候刚好对得上——depth <= 3就是三级以内。自反行（ancestor_id = descendant_id, depth = 0）一定要保留，"包含自己"在术语查询里是个很常见的需求，比如"这个诊断及其所有亚型"。

行数量级在百万级，主键(ancestor_id, descendant_id)同时充当"从祖先往下的覆盖索引"，ix_isa_desc反过来服务"从后代往上的覆盖索引"。两个方向都覆盖到，才不会有漏网的顺序扫描。

3.3 用 SQL PL 存储过程把闭包灌出来

闭包表的重建我写成 SQL PL 过程，主键自带的唯一约束顺便帮忙挡住重复插入。思路是先铺第 0 层（每个概念指向自己），然后一轮一轮往前接一跳，直到某一轮插不进新行为止。

CREATE OR REPLACE PROCEDURE rebuild_isa_closure (IN max_rounds INT DEFAULT 64) LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE v_inserted INTEGER DEFAULT 1; DECLARE v_round INTEGER DEFAULT 0; DELETE FROM concept_isa_closure; -- 第 0 层：每个有效概念是自己的祖先 INSERT INTO concept_isa_closure (ancestor_id, descendant_id, depth) SELECT id, id, 0 FROM concept WHERE active = 1; -- 逐层推进，直到不动点；max_rounds 是防死循环的保险 WHILE v_inserted > 0 AND v_round < max_rounds DO INSERT INTO concept_isa_closure (ancestor_id, descendant_id, depth) SELECT DISTINCT cl.ancestor_id, r.destination_id, cl.depth + 1 FROM concept_isa_closure cl JOIN relationship r ON r.source_id = cl.descendant_id WHERE r.active = 1 AND r.type_id = 116680003 AND cl.depth = v_round AND NOT EXISTS ( -- 已经算过的路径不再插入 SELECT 1 FROM concept_isa_closure x WHERE x.ancestor_id = cl.ancestor_id AND x.descendant_id = r.destination_id); GET DIAGNOSTICS v_inserted = ROW_COUNT; SET v_round = v_round + 1; END WHILE; END

关键在cl.depth = v_round这个条件。它保证每轮只从"上一轮刚铺好的那一层"往前推一跳，同一个祖先到同一个后代只会在最短路径那一轮被写进去，后面的更长路径会被NOT EXISTS挡住。这样depth自然就是最短距离，不会因为多条路径把同一对祖先后代重复算。max_rounds默认 64 是血泪经验的产物——数据里一旦出现意外的环，没有这个上限过程会一直跑下去，把临时表空间撑爆。GET DIAGNOSTICS ... = ROW_COUNT取上一句影响的行数，为 0 就退出循环。

3.4 验证：拿递归结果和闭包表对拍

闭包表最怕的是"算漏了但没人发现"。我固定会跑两个检查：

-- 检查一：depth = 0 的行数应当与有效概念数完全相等 SELECT (SELECT COUNT(*) FROM concept WHERE active = 1) AS concepts, (SELECT COUNT(*) FROM concept_isa_closure WHERE depth = 0) AS depth0; -- 检查二：抽一个概念，用递归 CTE 现算子孙集合，与闭包表比数量 WITH RECURSIVE d (id, depth) AS ( SELECT destination_id, 1 FROM relationship WHERE source_id = :root AND type_id = 116680003 AND active = 1 UNION SELECT r.destination_id, d.depth + 1 FROM relationship r JOIN d ON r.source_id = d.id WHERE r.type_id = 116680003 AND r.active = 1 ) SELECT (SELECT COUNT(*) FROM d) AS recursive_cnt, (SELECT COUNT(*) FROM concept_isa_closure WHERE ancestor_id = :root AND depth > 0) AS closure_cnt;

两个数不一致就说明闭包漏了。抽查我一般随机取 20 个不同分支的概念，如果 20 个全对，剩下的出错概率很低。

4. 编码查询怎么提速：四个高频 SQL 模式与索引取舍

4.1 按中文术语找概念：从模糊匹配到前缀索引

最常见的需求是"医生输入'高血压'，返回候选编码"。最直接的写法是：

SELECT c.id, d.term FROM description d JOIN concept c ON c.id = d.concept_id WHERE d.active = 1 AND c.active = 1 AND d.language_code = 'zh' AND d.type_id = 900000000000013009 -- 只取同义词，排除全限定名 AND d.term LIKE '%高血压%';

这条 SQL 的问题是前置通配符，ix_desc_term完全用不上，百万级的描述表上跑一次要几秒。我的做法分两层：常用短词（两三个字的症状名）走前缀匹配LIKE '高血压%'，能吃到索引；长句和包含式查询交给外部检索引擎做倒排，SQL 层只负责按 id 回表取详情。另外记得把type_id过滤加上——一个概念下面既有全限定名（带括号注释的那种长串），也有同义词，全限定名混进候选列表里，用户看到的是一堆没法点的长字符串。

4.2 按父概念取子孙：闭包表 join 与去重

SELECT c.id, MIN(d.term) AS display_term FROM concept_isa_closure cl JOIN concept c ON c.id = cl.descendant_id AND c.active = 1 JOIN description d ON d.concept_id = c.id AND d.active = 1 AND d.language_code = 'zh' AND d.type_id = 900000000000013009 WHERE cl.ancestor_id = :root AND cl.depth <= :max_depth GROUP BY c.id;

GROUP BY c.id是必须的。一个概念常有多个中文同义词，不做聚合的话，同一个概念会在结果里出现三四次，前端列表直接重复。MIN(d.term)是个偷懒的首选词策略，严格点应该按语言参考集里标记为 preferred 的那条来描述——如果资源里带了语言参考集文件，就把首选词单独抽成一张concept_preferred_term表，查询时直接 join 这张表，省掉每次聚合。

cl.depth <= :max_depth是控制结果规模的关键。不加深度限制的话，从"临床发现"这种顶层概念往下取，几十万行全出来，连接池直接被打满。

4.3 分页与计数：别在闭包表上做大 join 的 count

列表页要总数，很多人会写成SELECT COUNT(*) FROM (上面那一大坨 join)。这条 SQL 会把你刚建的所有索引都走一遍，耗时是取数据的十几倍。我一般拆成两步：先只在闭包表上算数量（SELECT COUNT(*) FROM concept_isa_closure WHERE ancestor_id = ? AND depth <= ?，走主键，毫秒级），再取当前页的 20 个 id，最后才拿这 20 个 id 回描述表取词。数量会略有偏差，因为停用的概念没排除，但列表页的"共 N 条"本来就不需要精确到这个程度。

4.4 索引取舍

ix_desc_term是唯一一个可以商量要不要建的。如果检索完全交给外部引擎，这个索引可以砍掉，能省下不少空间和装载时间。我一般先在本地保留，等库体积顶到磁盘告警了再砍。

5. 避坑清单：装载、编码、层级、版本的五个翻车点

5.1 SCTID 用 INT 存，装载到一半报溢出

现象：脚本跑到一半抛出数值超出范围的错误，或者更糟——不报错，但部分 id 变成了另一批数字。原因：SCTID 是 64 位整数，900000000000013009这种值远超 32 位上限，INT存不下，某些数据库在非严格模式下会静默截断。解决：所有 id 相关列一律BIGINT（Oracle 用NUMBER(18)），暂存表也别偷懒用INT，改一处漏一处最要命。

5.2 用表格软件预处理文件，id 全变成科学计数法

现象：导入后概念 id 变成9.00000000000001E+17，大量行撞成同一个值，主键冲突一片。原因：表格软件默认把长数字按浮点解析，只保留约 15 位有效数字，另存出去精度已经丢了。解决：原始 TSV 文件不要经手任何表格软件，直接用批量加载工具按字符串读进来，或者在脚本里显式转BIGINT。这条我吃过一次，重下了一遍发布包。

5.3 不按 effectiveTime 取最新行，历史描述全被装进来

现象：同一个概念下面出现三四条一模一样的中文同义词，active全是 1。原因：全量文件是按版本行组织的，同一个描述 id 的多个历史版本都带着各自当时的active值，不去重就全灌进去了。解决：装载时按id + MAX(effective_time)取最新行，历史版本单独进归档表；或者干脆只用增量文件做首次装载，前提是你拿得到基线。

5.4 层级查询忘了过滤 type_id，结果混进一堆属性关系

现象：查某个疾病的子孙，返回结果里混着"部位""形态学"这类完全不相干的概念。原因：关系表里除了 IS-A 关系，还有大量属性关系，source_id和destination_id的语义完全不同，不过滤就把它们当成父子边了。解决：凡是做层级遍历，SQL 里必须显式带上type_id = 116680003和active = 1。我现在的习惯是建一个只含 IS-A 边的视图，所有层级查询只从视图走，从源头上断掉漏写条件的可能。

5.5 中文术语导入后是问号

现象：term字段里全是?，英文倒是正常。原因：数据库默认字符集是单字节编码，建库时没指定。解决：建库就指定 UTF-8，装载命令里也显式声明ENCODING 'UTF8'，两头都要对上。已经建错的库改字符集代价很大，不如重建。

6. 增量更新与自检：把每月的变化装进同一套表

全量包不是每天发的，日常维护走增量文件。增量文件的行结构和全量完全一样，区别是只包含本次变化的部分：新概念的active = 1行，被停用概念的active = 0行。装载逻辑跟全量完全不同——不能只追加，得先按 id 删掉旧行再插新行，否则同一个 id 会同时存在新旧两条记录。

-- 增量应用：先删后插，一个事务包住 BEGIN; DELETE FROM concept WHERE id IN (SELECT id FROM stg_concept_delta); INSERT INTO concept SELECT id, effective_time, active, module_id, definition_status_id FROM stg_concept_delta; COMMIT;

描述表和关系表同理。麻烦的地方在闭包表：只要关系表里有一条 IS-A 边被停用，原本算好的祖先后代路径就可能失效。我的做法是每次增量应用完成后，无条件重建一遍闭包表，而不是去做增量维护。百万级的闭包重建在批量插入模式下几分钟能跑完，比写一套增量维护逻辑再花两周 debug 划算得多。

重建完必须跑自检，我固定跑这几条：

-- 1. 孤儿关系：引用了不存在的概念 SELECT COUNT(*) FROM relationship r WHERE r.active = 1 AND (NOT EXISTS (SELECT 1 FROM concept c WHERE c.id = r.source_id) OR NOT EXISTS (SELECT 1 FROM concept c WHERE c.id = r.destination_id)); -- 2. 自指关系：source 和 destination 相同，递归查询的隐形杀手 SELECT COUNT(*) FROM relationship WHERE active = 1 AND type_id = 116680003 AND source_id = destination_id; -- 3. 有效概念没有中文同义词：通常意味着语言参考集没装对 SELECT COUNT(*) FROM concept c WHERE c.active = 1 AND NOT EXISTS (SELECT 1 FROM description d WHERE d.concept_id = c.id AND d.active = 1 AND d.language_code = 'zh' AND d.type_id = 900000000000013009);

第一条和第二条都应该返回 0，第三条的数值应该很小——正常情况下绝大多数概念都有中文描述，跑出几十万说明语言文件压根没导进去。这三个数字我每次更新完都会记一行日志，连续几次对比下来，异常一眼就能看出来。

从那以后我每次装完增量都强制把这三条自检加闭包重建一起跑完才允许切流，宁可多花十分钟，也不想再经历一次线上层级查询返回空结果、排查到半夜的场面。希望帮到你。

本文还有配套的精品资源，点击获取