当前位置： 首页 > news >正文

实战指南：基于快马平台利用postgresql的jsonb与全文搜索构建商品系统

news 2026/8/13 0:37:10

实战指南：基于PostgreSQL的JSONB与全文搜索构建商品系统

最近在开发一个电商项目时，遇到了商品属性管理的问题。传统的关系型数据库表结构在面对频繁变化的商品属性时显得力不从心，每次新增属性都要修改表结构。经过调研，我发现PostgreSQL的JSONB数据类型和全文搜索功能可以完美解决这个问题。

为什么选择JSONB存储商品属性

  1. 灵活的数据结构：商品属性如颜色、尺寸、品牌等经常变化，JSONB允许我们以键值对形式存储这些动态属性，无需频繁修改表结构。

  2. 高效的查询性能：PostgreSQL对JSONB类型提供了专门的索引和查询操作符，查询速度接近传统关系型数据。

  3. 完整的SQL支持：虽然存储的是JSON，但依然可以使用所有SQL功能，包括JOIN、事务等。

实现步骤详解

1. 创建商品表结构

首先我们创建一个products表，包含基本字段和一个JSONB类型的attributes字段：

CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL, attributes JSONB NOT NULL DEFAULT '{}' );

attributes字段将存储如{"color": "红色", "size": "XL", "brand": "耐克"}这样的动态属性。

2. 实现JSONB属性筛选

为了根据JSONB属性筛选商品，我创建了一个SQL函数：

CREATE OR REPLACE FUNCTION filter_products_by_attributes(attribute_filters JSONB) RETURNS SETOF products AS $$ BEGIN RETURN QUERY SELECT * FROM products p WHERE (attribute_filters->>'color' IS NULL OR p.attributes->>'color' = attribute_filters->>'color') AND (attribute_filters->>'size' IS NULL OR p.attributes->>'size' = attribute_filters->>'size') AND (attribute_filters->>'brand' IS NULL OR p.attributes->>'brand' = attribute_filters->>'brand'); END; $$ LANGUAGE plpgsql;

这个函数接受一个JSONB参数，可以灵活地根据传入的属性条件进行筛选。

3. 全文搜索实现

为了提升搜索体验，我为商品名称和属性中的品牌名创建了全文搜索索引：

-- 创建全文搜索配置（如果需要中文搜索，可以添加中文分词扩展） CREATE TEXT SEARCH CONFIGURATION product_search (COPY = simple); -- 创建GIN索引加速搜索 CREATE INDEX idx_product_search ON products USING gin((to_tsvector('product_search', name) || to_tsvector('product_search', attributes->>'brand')));

然后编写搜索函数：

CREATE OR REPLACE FUNCTION search_products(keyword TEXT) RETURNS SETOF products AS $$ BEGIN RETURN QUERY SELECT * FROM products WHERE to_tsvector('product_search', name) || to_tsvector('product_search', attributes->>'brand') @@ plainto_tsquery('product_search', keyword); END; $$ LANGUAGE plpgsql;

4. 组合筛选与搜索

实际应用中，我们通常需要同时支持属性筛选和关键词搜索：

CREATE OR REPLACE FUNCTION search_and_filter_products( keyword TEXT, attribute_filters JSONB ) RETURNS SETOF products AS $$ BEGIN RETURN QUERY SELECT * FROM products WHERE (keyword IS NULL OR (to_tsvector('product_search', name) || to_tsvector('product_search', attributes->>'brand') @@ plainto_tsquery('product_search', keyword))) AND (attribute_filters->>'color' IS NULL OR attributes->>'color' = attribute_filters->>'color') AND (attribute_filters->>'size' IS NULL OR attributes->>'size' = attribute_filters->>'size') AND (attribute_filters->>'brand' IS NULL OR attributes->>'brand' = attribute_filters->>'brand'); END; $$ LANGUAGE plpgsql;

5. 提供API接口

最后，我用Python Flask框架实现了一个简单的API端点：

from flask import Flask, request, jsonify import psycopg2 import json app = Flask(__name__) # 数据库连接配置 conn = psycopg2.connect( dbname="your_db", user="your_user", password="your_password", host="localhost" ) @app.route('/api/products/search', methods=['GET']) def search_products(): keyword = request.args.get('keyword', None) color = request.args.get('color', None) size = request.args.get('size', None) brand = request.args.get('brand', None) # 构建属性筛选条件 filters = {} if color: filters['color'] = color if size: filters['size'] = size if brand: filters['brand'] = brand cur = conn.cursor() cur.execute( "SELECT * FROM search_and_filter_products(%s, %s)", (keyword, json.dumps(filters)) ) results = cur.fetchall() cur.close() # 格式化返回结果 products = [] for row in results: products.append({ 'id': row[0], 'name': row[1], 'price': float(row[2]), 'attributes': row[3] }) return jsonify(products) if __name__ == '__main__': app.run(debug=True)

性能优化建议

  1. 索引优化：为常用查询条件创建专门的索引，例如：

    CREATE INDEX idx_attributes_color ON products ((attributes->>'color')); CREATE INDEX idx_attributes_size ON products ((attributes->>'size'));

  2. 部分索引：如果某些属性查询频率很高但值的选择性很强，可以考虑部分索引：

    CREATE INDEX idx_high_end_brands ON products ((attributes->>'brand')) WHERE attributes->>'brand' IN ('耐克', '阿迪达斯', '苹果');

  3. 连接池管理：在生产环境中使用连接池(如PgBouncer)管理数据库连接，避免频繁创建新连接。

  4. 查询分析：定期使用EXPLAIN ANALYZE分析慢查询，优化SQL语句。

实际应用中的经验

  1. 数据验证：虽然JSONB提供了灵活性，但仍需要在应用层验证数据格式，确保attributes字段符合预期结构。

  2. 迁移策略：从传统表结构迁移到JSONB时，可以采用双写策略，新旧结构并行运行一段时间。

  3. 文档化：即使使用动态属性，也要为attributes字段维护一个文档，说明支持的字段和数据类型。

  4. 监控：监控JSONB字段的大小增长，过大的JSONB文档会影响性能。

遇到的挑战与解决方案

  1. 中文搜索问题：默认的全文搜索配置对中文支持不好。解决方案是安装zhparser扩展：

    CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);

  2. 复杂查询性能：当JSONB文档很深或很大时，查询会变慢。解决方案是：

    • 扁平化JSON结构，减少嵌套
    • 对常用查询路径创建专门的索引
    • 考虑将频繁查询的属性提取到单独列

  3. 事务管理：在更新JSONB字段的特定属性时，要注意事务隔离级别，避免丢失更新。

扩展应用场景

这种基于JSONB和全文搜索的方案不仅适用于商品系统，还可以应用于：

  1. 内容管理系统：存储文章的元数据和标签
  2. 用户画像系统：存储用户的动态属性和偏好
  3. 物联网应用：存储设备的各种传感器数据
  4. 医疗系统：存储病人的检查报告和病历

使用InsCode(快马)平台的体验

在实现这个功能的过程中，我使用了InsCode(快马)平台来快速验证PostgreSQL的各种高级特性。这个平台最让我惊喜的是：

  1. 无需本地安装：直接在线创建PostgreSQL数据库实例，省去了繁琐的环境配置。

  2. 一键部署：将完成的API代码直接部署到线上环境，立即可以测试效果。

  3. 实时预览：在开发过程中可以随时查看数据库状态和API返回结果。

对于需要快速验证技术方案或构建原型的场景，这种即开即用的体验确实能节省大量时间。特别是当需要演示JSONB查询效果时，可以直接在平台上运行SQL语句并立即看到结果，比本地开发效率高很多。

http://www.cnnetsun.cn/news/1629414.html

相关文章：

  • 3大挑战：如何打造完美的自托管音乐播放体验？Feishin为你提供完整解决方案
  • LSTM时间序列预测项目实战：Pixel Epic · Wisdom Terminal 代码生成与调优
  • 黑苹果终极配置指南：用Hackintool轻松搞定显卡、音频和USB驱动
  • Granite TimeSeries FlowState R1入门：C语言开发者调用模型API的简明指南
  • WAN2.2-14B-Rapid-AllInOne：3步实现专业级AI视频生成，低显存部署全攻略
  • 从CSP到NOI：信息学竞赛晋级路径全解析
  • 别再乱装Python了！手把手教你用Anaconda和Miniconda搞定多版本环境管理（附国内镜像源配置）
  • Qwen3-14B开源大模型实战：基于start_api.sh构建批量推理微服务
  • 麒麟V10离线环境通过Docker部署MongoDB全流程解析
  • 如何高效提取图片文字：免费离线OCR软件Umi-OCR终极实用指南
  • xLua技术优化实战指南：从架构诊断到性能验证的完整闭环
  • Qwen3.5-9B部署教程：HTTPS反向代理（Nginx）安全访问配置
  • 愚人节最大“乌龙”：不是玩笑！Claude Code 51万行源码裸奔，AI独角兽栽在低级失误里
  • 深入解析Python中ort.InferenceSession的底层实现与性能优化
  • 实战应用：基于快马平台构建带界面的视频号视频下载桌面工具
  • 5分钟掌握Postman便携版：Windows开发者的API测试终极指南 [特殊字符]
  • Graphormer在药物ADMET预测中的拓展应用：LogS、BBB穿透性等属性迁移学习
  • 基于C++实现一个简单的（控制台）班级成绩管理系统
  • 内存暴涨却查不到源头？Python对象引用图谱分析法，手把手教你用tracemalloc+objgraph揪出“幽灵引用”
  • Pixel Aurora Engine 企业级应用：基于大模型的智能营销素材批量生成
  • Janus-Pro-7B快速原型开发：10分钟构建智能问答应用
  • LumiPixel Canvas Quest教育应用：生成历史人物或文学角色形象辅助教学
  • 如何把自己手动安装的 node 给 nvm 管理
  • UNIT-00模型在Markdown文档创作中的效果展示：以Typora风格为例
  • OpenClaw从入门到应用——频道：BlueBubbles
  • Ruoyi-Cloud整合Seata2.0踩坑实录：从Nacos配置到分布式事务实战
  • 电脑风扇噪音难忍？FanControl让散热管理变简单 - 开源智能风扇控制解决方案全解析
  • 利用Pixel Couplet Gen进行A/B测试：优化春节活动页面转化率
  • 5大核心功能解锁网页资源：猫抓开源工具让媒体捕获效率提升300%
  • 为什么WindTerm成为开发者的首选终端工具？深度评测与替代方案对比