★ FEATURED ARTICLE 简介面向医疗信息化开发者、数据库管理员与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的那些行。想取某个概念的所有子孙最自然的写法是递归 CTEWITH 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 索引取舍索引覆盖列服务的查询代价pk_conceptconcept(id)所有回表、关联几十万行很小ix_desc_conceptdescription(concept_id, active)按概念取所有描述百万级写入时略慢ix_desc_termdescription(term)前缀匹配检索大字段索引占空间最多ix_rel_srcrelationship(source_id, type_id, active)向下遍历、闭包重建重建闭包时最热ix_rel_dstrelationship(destination_id, type_id, active)向上找父概念用得少但缺了就扫全表pk_isa_closureconcept_isa_closure(ancestor_id, descendant_id)向下取子孙百万级重建时清空再灌ix_desc_term是唯一一个可以商量要不要建的。如果检索完全交给外部引擎这个索引可以砍掉能省下不少空间和装载时间。我一般先在本地保留等库体积顶到磁盘告警了再砍。5. 避坑清单装载、编码、层级、版本的五个翻车点5.1 SCTID 用 INT 存装载到一半报溢出现象脚本跑到一半抛出数值超出范围的错误或者更糟——不报错但部分 id 变成了另一批数字。原因SCTID 是 64 位整数900000000000013009这种值远超 32 位上限INT存不下某些数据库在非严格模式下会静默截断。解决所有 id 相关列一律BIGINTOracle 用NUMBER(18)暂存表也别偷懒用INT改一处漏一处最要命。5.2 用表格软件预处理文件id 全变成科学计数法现象导入后概念 id 变成9.00000000000001E17大量行撞成同一个值主键冲突一片。原因表格软件默认把长数字按浮点解析只保留约 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第三条的数值应该很小——正常情况下绝大多数概念都有中文描述跑出几十万说明语言文件压根没导进去。这三个数字我每次更新完都会记一行日志连续几次对比下来异常一眼就能看出来。从那以后我每次装完增量都强制把这三条自检加闭包重建一起跑完才允许切流宁可多花十分钟也不想再经历一次线上层级查询返回空结果、排查到半夜的场面。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
