讲师中心 微信公众号

工具推荐

视频效率加速
Seko
Seko

AI短剧一键生成成片

剧云
剧云

中文短剧剧本制作平台

AniShort
AniShort

AI短剧协同制作平台

updream

一键生成高质量推广成片

如何在PostgreSQL 14 SQL中优化大批量JSON数据的JOIN?

雨婷大大_4173

雨婷大大_4173

发布时间:2026-11-22 09:50:03

|

709人浏览过

|

来源于php中文网

原创

在postgresql 14中优化大批量json数据的join,核心做法是避免对json字段进行运行时解析和函数调用,而是将json数据提取为物理存储的列并建立索引。具体而言,应使用生成列(generated columns)或触发器将json中的关键字段持久化到普通数据类型列中,随后对这些列创建b-tree索引。这样做可以将原本昂贵的`jsonb_path_query`或`->>`操作转化为高效的索引扫描,从而显著降低join操作的cpu开销和执行时间。

如何在PostgreSQL 14 SQL中优化大批量JSON数据的JOIN?

为什么直接JOIN JSON字段性能差

直接在JSON字段上进行JOIN会导致全表扫描和逐行解析,因为数据库无法利用索引加速非结构化数据的匹配。当两个大表通过`jsonb`类型关联时,PostgreSQL必须对每一行的JSON文档进行反序列化并提取键值,这在数据量大时会产生巨大的I/O和CPU负担。这种模式通常表现为执行计划中出现大量的`Seq Scan`和`Function Call`,且无法有效利用并行处理。

如何在PostgreSQL 14 SQL中优化大批量JSON数据的JOIN?

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    data jsonb NOT NULL
);

-- 低效查询:直接对JSON字段进行JOIN
SELECT o.id, c.name 
FROM orders o
JOIN customers c ON o.data->>'customer_id' = c.id;

上述代码展示了典型的低效模式。`orders`表若包含百万级记录,每次查询都需要遍历所有行并解析`data`字段。预期结果是查询耗时随数据量线性增长,且无法通过常规索引优化。实际测试中,此类查询在100万行数据下可能耗时数秒至数十秒,而优化后应降至毫秒级。

Exa JS Web Search SDK 用于 AI 应用程序
Exa JS Web Search SDK 用于 AI 应用程序

exa-js 是 Exa 官方 JavaScript 软件开发工具包,Exa 是一个为 AI 工作流构建的网络搜索 API。它为代理提供了一种具体的方式来搜索网络、检索页面内容,并从代码中生成有来源的答案,而不是手动拼接原始 HTTP 调用。

下载

如何使用生成列和索引优化JOIN

使用生成列将JSON中的关键字段提取为物理列并建立索引,是实现高效JOIN的标准做法。通过在表定义中添加`GENERATED ALWAYS AS (...) STORED`子句,数据库会在插入或更新时自动计算并存储提取的值,后续查询可直接利用该列的索引。这种方法将JSON解析成本从查询时转移到写入时,适合读多写少的场景。

如何在PostgreSQL 14 SQL中优化大批量JSON数据的JOIN?

CREATE TABLE orders_optimized (
    id SERIAL PRIMARY KEY,
    data jsonb NOT NULL,
    customer_id INT GENERATED ALWAYS AS (data->>'customer_id')::INT STORED
);

CREATE INDEX idx_orders_customer_id ON orders_optimized(customer_id);

-- 高效查询:利用生成列的索引
SELECT o.id, c.name 
FROM orders_optimized o
JOIN customers c ON o.customer_id = c.id;

此代码演示了优化后的结构。`customer_id`作为生成列被物理存储,并通过`idx_orders_customer_id`索引加速查找。预期结果是JOIN操作变为`Index Scan`,查询速度提升数个数量级。需注意,生成列会增加存储空间和写入延迟,因此仅应对高频JOIN的字段使用。

生成列在复杂JSON嵌套中的表现

当JSON数据存在多层嵌套时,生成列仍能准确提取目标值,但表达式需精确匹配路径。例如,若`customer_id`位于`data->'address'->'primary'`路径下,生成列表达式应反映这一结构。PostgreSQL 14支持在生成列中使用复杂的JSON操作符,确保提取逻辑与业务需求一致。验证时需检查提取值的类型转换是否正确,避免因类型不匹配导致索引失效。

热门AI工具

更多
讯飞绘文

讯飞绘文是一款由科大讯飞推出的一站式 AIGC 内容运营平台。

UP简历
UP简历 Hot

一款AI办公效率工具,主要用于基于AI技术的免费在线简历制作工具,适合需要提升相关任务效率的用户。

UpDream
UpDream Hot

一款AI视频创作工具,主要用于哔哩哔哩推出的自研AI视频创作工具,适合需要提升相关任务效率的用户。

咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

PixTV
PixTV Hot

PixTV是一款面向AIGC内容创作的AI视频生成工具。

Loomy
Loomy Hot

一款AI工具,主要用于科大讯飞发布的桌面级 AI 助理,比 OpenClaw 更易用、更安全!,适合需要提升相关任务效率的用户。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

相关专题

更多
json数据格式
json数据格式

JSON是一种轻量级的数据交换格式。本专题为大家带来json数据格式相关文章,帮助大家解决问题。

2415

2023.08.07

json是什么
json是什么

JSON是一种轻量级的数据交换格式,具有简洁、易读、跨平台和语言的特点,JSON数据是通过键值对的方式进行组织,其中键是字符串,值可以是字符串、数值、布尔值、数组、对象或者null,在Web开发、数据交换和配置文件等方面得到广泛应用。本专题为大家提供json相关的文章、下载、课程内容,供大家免费下载体验。

4042

2023.08.23

jquery怎么操作json
jquery怎么操作json

操作的方法有:1、“$.parseJSON(jsonString)”2、“$.getJSON(url, data, success)”;3、“$.each(obj, callback)”;4、“$.ajax()”。更多jquery怎么操作json的详细内容,可以访问本专题下面的文章。

1336

2023.10.13

go语言处理json数据方法
go语言处理json数据方法

本专题整合了go语言中处理json数据方法,阅读专题下面的文章了解更多详细内容。

4699

2025.09.10

postgresql常用命令
postgresql常用命令

postgresql常用命令psql、createdb、dropdb、createuser、dropuser、l、c、dt、d table_name、du、i file_name、e和q等。本专题为大家提供postgresql相关的文章、下载、课程内容,供大家免费下载体验。

233

2023.10.10

常用的数据库软件
常用的数据库软件

常用的数据库软件有MySQL、Oracle、SQL Server、PostgreSQL、MongoDB、Redis、Cassandra、Hadoop、Spark和Amazon DynamoDB。更多关于数据库软件的内容详情请看本专题下面的文章。php中文网欢迎大家前来学习。

5109

2023.11.02

postgresql常用命令有哪些
postgresql常用命令有哪些

postgresql常用命令psql、createdb、dropdb、createuser、dropuser、l、c、dt、d table_name、du、i file_name、e和q等。更详细的postgresql常用命令,大家可以访问下面的文章。

667

2023.11.16

postgresql常用命令介绍
postgresql常用命令介绍

postgresql常用命令有l、d、d5、di、ds、dv、df、dn、db、dg、dp、c、pset、show search_path、ALTER TABLE、INSERT INTO、UPDATE、DELETE FROM、SELECT等。想了解更多postgresql的相关内容,可以阅读本专题下面的文章。

1416

2023.11.20

Navicat创建数据库教程
Navicat创建数据库教程

本专题整理Navicat创建数据库相关教程,介绍新建前的准备、基础设置和创建后的检查方法。内容兼顾入门学习及实际使用需求,帮助用户了解不同数据库类型下的操作差异,为后续建表、保存数据和日常管理提供参考。

0

2026.11.20

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
WEB前端教程【HTML5+CSS3+JS】
WEB前端教程【HTML5+CSS3+JS】

共101课时 | 21.5万人学习

JS进阶与BootStrap学习
JS进阶与BootStrap学习

共39课时 | 5万人学习

关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn