assets/2026-07-01-医数智联系列开篇-医院数字化工作者的工具箱_2026-07-01_18-28-11.jpg

[!ABSTRACT] 核心摘要

  • 本期主题:PostgreSQL 三大杀手锏——JSONB 查询优化、中文全文检索、PostGIS 地理信息
  • 核心结论:PostgreSQL 的「半结构化 + 全文 + 空间」三件套,是医学数据的天生搭档
  • 3 大实战场景:JSONB 索引查询提速 100 倍、中文病历全文检索、院区患者分布分析
  • 核心技术:GIN 索引、tsvector + zhparser、PostGIS 空间函数
  • 阅读时长:约 10 分钟

一、场景引入:第 11 期「跑得慢」的真实问题

[!example] 一个真实的科研项目
某三甲医院用 PostgreSQL 建了「糖尿病视网膜病变」专病库(2.3 万份病历),3 个月后:

  • 信息科反馈:「JSONB 字段查询全表扫描,一份统计报告跑 3 分钟」
  • 临床医生反馈:「全文检索 ‘黄斑水肿’ 漏命中了一半,结果不准」
  • 院长想看:「医院 5 公里范围内有多少潜在糖尿病患者」,MySQL 做不了

这 3 个问题,第 11 期的「入门」解决不了,需要进阶的索引 + 扩展能力。

这是 PostgreSQL 入门的 3 大典型痛点:

痛点 入门做法 进阶做法
JSONB 查询慢 无索引,全表扫描 GIN 索引
中文全文检索漏命中 ILIKE '%关键词%' tsvector + zhparser
地理信息 无 PostGIS 扩展

[!quote] 狼叔的判断
“PostgreSQL 入门看 1 小时就够,但要发挥 100% 威力,需要进阶。” 这一期,带你把 JSONB / 全文检索 / GIS 三个杀手锏全部打通。

二、工具原理:三大杀手锏的核心机制

2.1 JSONB + GIN 索引(查询提速 100 倍)

[!info] GIN 索引是什么?
GIN(Generalized Inverted Index,通用倒排索引) 是 PostgreSQL 用于「字段里包含多个值」场景的索引。

适用场景:

  • JSONB 字段(包含多个键值对)
  • 数组字段(包含多个元素)
  • 全文检索字段(包含多个分词)

类比:GIN 就像一本书的「索引页」,你查「黄斑水肿」,直接翻到索引页找对应章节,不用逐页翻整本书。

2.1.1 不建索引 vs 建 GIN 索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 表结构(承第 11 期)
CREATE TABLE 眼底检查报告 (
  报告ID SERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  检查日期 DATE,
  报告内容 JSONB
);

-- 不建索引:全表扫描,2.3 万行病历跑 3 秒
SELECT * FROM 眼底检查报告
WHERE 报告内容->>'分期' = '增殖期';

-- 建 GIN 索引:倒排索引,2.3 万行病历跑 0.03 秒
CREATE INDEX idx_报告内容_gin ON 眼底检查报告 USING GIN (报告内容);

-- 再次执行同样的查询,速度快 100 倍
SELECT * FROM 眼底检查报告
WHERE 报告内容->>'分期' = '增殖期';

2.1.2 JSONB 查询运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- ->:返回 JSON 字段(对象/数组)
-- ->>:返回 JSON 字段的文本值
-- ?:key 是否存在(JSON 对象)
-- ?| ANY:多个 key 任一存在
-- ?& ALL:多个 key 全部存在
-- @>:左 JSON 包含右 JSON

-- 找出所有「增殖期」病历
SELECT * FROM 眼底检查报告
WHERE 报告内容->>'分期' = '增殖期';

-- 找出所有「包含左眼视力」key 的报告
SELECT * FROM 眼底检查报告
WHERE 报告内容 ? '左眼视力';

-- 找出「建议治疗包含抗VEGF」的报告
SELECT * FROM 眼底检查报告
WHERE 报告内容->'建议治疗' ? '抗VEGF';

-- 找出「包含分期=增殖期 AND 建议治疗=激光」的报告(JSONB 包含)
SELECT * FROM 眼底检查报告
WHERE 报告内容 @> '{"分期": "增殖期", "建议治疗": "激光"}';

2.2 中文全文检索(tsvector + zhparser)

[!info] 全文检索的核心机制
PostgreSQL 的全文检索分两步:

  1. 分词:把文本切成「词」(「黄斑水肿」切成「黄斑」「水肿」)
  2. 索引:用 GIN 索引存词和文档的映射

中文分词需要扩展,推荐 zhparser(基于 SCWS 中文分词库)。

2.2.1 启用 zhparser

1
2
3
4
5
6
7
8
9
10
11
-- 安装 zhparser 扩展(需 DBA 操作)
-- Ubuntu: apt install postgresql-15-zhparser
-- CentOS: yum install postgresql-15-zhparser
-- Windows: 下载编译好的 dll,放到 PostgreSQL 的 lib 目录

-- 创建扩展
CREATE EXTENSION zhparser;

-- 配置中文解析器
CREATE TEXT SEARCH CONFIGURATION chinese_zh (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese_zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;

2.2.2 创建全文检索列 + 触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 加全文检索列(tsvector 类型)
ALTER TABLE 眼底检查报告 ADD COLUMN 报告全文_tsv tsvector;

-- 创建触发器,自动把 JSONB 文本提取到 tsv 列
CREATE OR REPLACE FUNCTION 报告全文提取() RETURNS trigger AS $$
BEGIN
  NEW.报告全文_tsv :=
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.报告内容->>'主诉', '')), 'A') ||
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.报告内容->>'现病史', '')), 'B') ||
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.报告内容->>'查体', '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_报告全文提取
BEFORE INSERT OR UPDATE ON 眼底检查报告
FOR EACH ROW EXECUTE FUNCTION 报告全文提取();

-- 给 tsv 列建 GIN 索引
CREATE INDEX idx_报告全文_tsv ON 眼底检查报告 USING GIN (报告全文_tsv);

2.2.3 全文检索查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 普通关键词检索
SELECT 病案号, 检查日期
FROM 眼底检查报告
WHERE 报告全文_tsv @@ plainto_tsquery('chinese_zh', '黄斑水肿')
ORDER BY 检查日期 DESC
LIMIT 100;

-- 多关键词(AND):必须同时包含
SELECT 病案号, 检查日期
FROM 眼底检查报告
WHERE 报告全文_tsv @@ to_tsquery('chinese_zh', '黄斑 & 水肿 & 增殖期');

-- 多关键词(OR):任一包含
SELECT 病案号, 检查日期
FROM 眼底检查报告
WHERE 报告全文_tsv @@ to_tsquery('chinese_zh', '黄斑 | 水肿 | 增殖期');

-- 高亮显示匹配的关键词(用于前端展示)
SELECT
  病案号,
  ts_headline(
    'chinese_zh',
    报告内容->>'主诉',
    plainto_tsquery('chinese_zh', '黄斑水肿'),
    'StartSel=<mark>,StopSel=</mark>'
  ) AS 主诉_高亮
FROM 眼底检查报告
WHERE 报告全文_tsv @@ plainto_tsquery('chinese_zh', '黄斑水肿')
LIMIT 10;

2.3 PostGIS 地理信息

[!info] PostGIS 是什么?
PostGIS 是 PostgreSQL 的地理信息扩展,业界 GIS 标杆(开源)。

核心能力:

  • 存储几何对象(点、线、面、多边形)
  • 空间查询(距离、范围、包含、相交)
  • 坐标系转换(WGS-84、GCJ-02、BD-09)

2.3.1 启用 PostGIS

1
2
3
4
5
CREATE EXTENSION postgis;

-- 验证版本
SELECT postgis_version();
-- 返回:3.4 USE_GEOS=1 USE_PROJ=1 USE_GDAL=1

三、医院实战:3 大场景深挖

3.1 场景一:JSONB + GIN 索引优化首页数据

[!example] 实战 ①:JSONB 索引提速
场景:医院 LIS 系统每天产生 5000+ 检验报告,JSONB 存项目结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 表结构(承第 11 期)
CREATE TABLE 检验报告 (
  报告ID SERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  检验日期 DATE,
  检验类型 VARCHAR(50),
  项目结果 JSONB
);

-- 建 GIN 索引
CREATE INDEX idx_项目结果_gin ON 检验报告 USING GIN (项目结果);

-- 场景 1:找出所有「血小板异常」报告(带索引,0.05 秒)
SELECT 病案号, 检验日期, 项目结果->'血小板'
FROM 检验报告
WHERE 项目结果 @> '{"血小板": {"异常": true}}';

-- 场景 2:找出所有「肝功能异常」报告
SELECT 病案号, 检验日期, 项目结果
FROM 检验报告
WHERE 项目结果 @> '{"ALT": {"异常": true}}'
   OR 项目结果 @> '{"AST": {"异常": true}}'
   OR 项目结果 @> '{"TBIL": {"异常": true}}';

-- 场景 3:统计每种异常的出现次数
SELECT
  jsonb_object_keys(项目结果) AS 项目,
  COUNT(*) AS 异常次数
FROM 检验报告
WHERE 项目结果->jsonb_object_keys(项目结果)->>'异常' = 'true'
GROUP BY 项目
ORDER BY 异常次数 DESC;

[!TIP] 性能对比

场景 无索引 有 GIN 索引
2.3 万行 JSONB 查询 3.2 秒 0.03 秒
50 万行 JSONB 查询 47 秒(超时) 0.4 秒

3.2 场景二:中文病历全文检索(从 30 秒到 0.05 秒)

[!example] 实战 ②:全文检索
场景:5 年内 50 万份病历,关键词检索「急性心肌梗死」。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 表结构
CREATE TABLE 病历 (
  病历ID BIGSERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  主诉 TEXT,
  现病史 TEXT,
  既往史 TEXT,
  入院记录 TEXT  -- 整段入院记录
);

-- 加全文检索列
ALTER TABLE 病历 ADD COLUMN 全文检索_tsv tsvector;

-- 触发器:自动提取所有文本字段
CREATE OR REPLACE FUNCTION 病历全文提取() RETURNS trigger AS $$
BEGIN
  NEW.全文检索_tsv :=
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.主诉, '')), 'A') ||
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.现病史, '')), 'B') ||
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.既往史, '')), 'C') ||
    setweight(to_tsvector('chinese_zh', COALESCE(NEW.入院记录, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_病历全文提取
BEFORE INSERT OR UPDATE ON 病历
FOR EACH ROW EXECUTE FUNCTION 病历全文提取();

-- 建 GIN 索引
CREATE INDEX idx_病历全文_gin ON 病历 USING GIN (全文检索_tsv);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 查询:找出所有「急性心肌梗死」病历
SELECT 病案号, ts_rank(全文检索_tsv, plainto_tsquery('chinese_zh', '急性心肌梗死')) AS 相关度
FROM 病历
WHERE 全文检索_tsv @@ plainto_tsquery('chinese_zh', '急性心肌梗死')
ORDER BY 相关度 DESC
LIMIT 100;

-- 进阶查询:找出「急性心肌梗死」且年龄 ≥ 60 的病历
SELECT a.病案号, b.年龄
FROM 病历 a
LEFT JOIN 首页主表 b ON a.病案号 = b.病案号
WHERE a.全文检索_tsv @@ plainto_tsquery('chinese_zh', '急性心肌梗死')
  AND b.年龄 >= 60
ORDER BY a.全文检索_tsv DESC
LIMIT 100;

-- 高亮显示匹配关键词
SELECT
  病案号,
  ts_headline(
    'chinese_zh',
    主诉,
    plainto_tsquery('chinese_zh', '急性心肌梗死'),
    'StartSel=<mark>,StopSel=</mark>,MaxFragments=3'
  ) AS 主诉_高亮
FROM 病历
WHERE 全文检索_tsv @@ plainto_tsquery('chinese_zh', '急性心肌梗死')
LIMIT 10;

[!success] 性能对比

  • 50 万行病历 + GIN 索引:0.05 秒
  • 50 万行病历 + ILIKE 查询:30+ 秒

3.3 场景三:院区患者分布 + 5 公里分析(PostGIS)

[!example] 实战 ③:患者地理分布
场景:医院有 3 个院区,要做「院区 5 公里范围内患者分布」分析。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 启用 PostGIS
CREATE EXTENSION postgis;

-- 院区表(经纬度)
CREATE TABLE 院区 (
  院区ID SERIAL PRIMARY KEY,
  院区名称 VARCHAR(50),
  位置 GEOGRAPHY(POINT, 4326)  -- 4326 = WGS-84 坐标系
);

INSERT INTO 院区 (院区名称, 位置) VALUES
('总院', ST_MakePoint(114.0579, 22.5431)::geography),  -- 深圳
('分院 A', ST_MakePoint(114.1234, 22.5678)::geography),
('分院 B', ST_MakePoint(113.9876, 22.5123)::geography);

-- 患者地址表
CREATE TABLE 患者地址 (
  患者ID SERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  地址 VARCHAR(200),
  位置 GEOGRAPHY(POINT, 4326)
);

-- 批量插入患者(从 HIS 同步,示例 1000 条)
INSERT INTO 患者地址 (病案号, 地址, 位置)
SELECT
  'BA' || LPAD(i::text, 8, '0'),
  '某沿海城市某区某路' || i || '号',
  ST_MakePoint(
    114.0579 + (random() - 0.5) * 0.1,  -- 围绕总院 0.05 度(约 5 公里)
    22.5431 + (random() - 0.5) * 0.1
  )::geography
FROM generate_series(1, 1000) AS i;

-- 给患者地址建空间索引
CREATE INDEX idx_患者地址_gist ON 患者地址 USING GIST (位置);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 查询 1:总院 5 公里范围内的患者
SELECT COUNT(*) AS 患者数
FROM 患者地址
WHERE ST_DWithin(
  位置,
  (SELECT 位置 FROM 院区 WHERE 院区名称 = '总院'),
  5000  -- 5 公里
);

-- 查询 2:每个院区 5 公里范围内的患者数
SELECT
  y.院区名称,
  COUNT(p.患者ID) AS 患者数
FROM 院区 y
LEFT JOIN 患者地址 p ON ST_DWithin(p.位置, y.位置, 5000)
GROUP BY y.院区名称
ORDER BY 患者数 DESC;

-- 查询 3:距离总院最近的 10 个患者
SELECT
  病案号,
  地址,
  ST_Distance(位置, (SELECT 位置 FROM 院区 WHERE 院区名称 = '总院')) AS 距离_米
FROM 患者地址
ORDER BY 距离_米 ASC
LIMIT 10;

-- 查询 4:总院周边患者分布密度(按 1km × 1km 网格统计)
SELECT
  ROUND(ST_X(位置)::numeric, 3) AS 经度_网格,
  ROUND(ST_Y(位置)::numeric, 3) AS 纬度_网格,
  COUNT(*) AS 患者数
FROM 患者地址
WHERE ST_DWithin(位置, (SELECT 位置 FROM 院区 WHERE 院区名称 = '总院'), 10000)
GROUP BY 经度_网格, 纬度_网格
ORDER BY 患者数 DESC;

四、避坑指南:5 个让进阶「失灵」的常见错误

[!warning] 雷区 ①:JSONB 字段不建 GIN 索引
症状:JSONB 查询慢,2.3 万行跑 3 秒,大数据量超时。
解决:给 JSONB 字段建 GIN 索引:CREATE INDEX ... USING GIN (jsonb_field);

[!warning] 雷区 ②:中文全文检索不装 zhparser
症状:to_tsvector('simple', '中文') 把整段当一个 token,所有中文检索全部失效。
解决:必须装 zhparser,并配置 TEXT SEARCH CONFIGURATION。

[!warning] 雷区 ③:触发器内调用复杂函数
症状:触发器内做正则替换、JSON 解析,每次 INSERT/UPDATE 都跑,写入速度慢 5 倍。
解决:把计算逻辑移到应用层,数据库触发器只做简单的 tsvector 拼接。

[!warning] 雷区 ④:PostGIS 用错坐标系
症状:用 ST_Distance 计算 2 个城市的距离,返回 1.5(单位是「度」),不是「米」。
原因:4326 是「地理坐标系」,单位是「度」,不是平面距离。
解决:

  • 小范围(城市内):用 GEOGRAPHY 类型,ST_Distance 返回米;
  • 大范围(跨国):用 GEOMETRY 类型 + 投影坐标系。

[!warning] 雷区 ⑤:PostGIS 字段不建空间索引
症状:ST_DWithin 查询 10 万患者,全表扫描 30 秒。
解决:给空间字段建 GIST 索引:CREATE INDEX ... USING GIST (位置);

五、下期预告

[!quote] 第 13 期
SQL 性能优化 —— 让首页查询从 30 秒到 0.5 秒

索引、EXPLAIN 执行计划、查询重写、表结构优化,MySQL 和 PostgreSQL 通用。

[!TIP] 互动
如果你觉得这篇「医数智联·第 12 期」对你有用,请点赞、在看、转发给科里的同事。

我是白衣狼,一个用数字化工具把医院管理做「轻」的实战派。咱们下期见。