工业软件动态属性架构设计:从思想到落地,AI给不了你的工程实战

像麻雀一样，不长但五脏俱全，代码可跑。

先说结论：100万数据查询，视图方案8.2秒，JSONB方案320毫秒——差距25倍。

一、故事背景：被动态属性折磨的一周

小兵：麻雀哥，救命！我们项目遇到一个棘手的问题。

麻雀：怎么了？看你愁眉苦脸的。

小兵：我们做的是工业MES系统，不同产品类型有完全不同的属性。比如注塑机有"锁模力"、"螺杆直径"，贴片机有"贴装速度"、"元件范围"。现在这些动态属性全塞在JSON字段里，业务方要求能筛选、能排序、能分页！

麻雀：这是工业软件的经典问题——动态属性存储与查询。你们现在怎么做的？

小兵：别提了！最开始用EAV模型建了属性表，查一个产品要JOIN十几次，慢得要死。后来改成JSON存，查询又不会写了。听说视图方案能解决，试了一下性能还是不行。现在业务方天天催...

麻雀：哈哈，你这是把坑都踩了一遍。来，我给你系统讲讲这个问题。

二、先搞懂EAV模型：动态属性的祖宗

EAV 全称是 Entity-Attribute-Value（实体-属性-值），是处理动态属性的经典数据建模模式。

2.1 传统EAV（行存储）

┌─────────────────────────────────────────────────┐│                   产品表（实体）                  ││  id | name | category                           │└─────────────────────────────────────────────────┘                         │                         │ 1:N                         ▼┌─────────────────────────────────────────────────┐│              属性值表（EAV）                      ││  id | product_id | attr_name | attr_value       ││  1  | 1          | color     | 黑色             ││  2  | 1          | screen    | 6.1英寸          ││  3  | 1          | battery   | 3349mAh          │└─────────────────────────────────────────────────┘

问题：查一个产品的所有属性需要JOIN N次，性能极差。

2.2 现代EAV变种（JSON存储）

┌─────────────────────────────────────────────────┐│                   实体表（固定字段）              ││  id | name | category | created_at | ...        │└─────────────────────────────────────────────────┘                         │                         │ 1:1                         ▼┌─────────────────────────────────────────────────┐│               JSONB扩展表（动态字段）             ││  id | entity_id | attributes JSONB              ││                 │ {"color":"黑","screen":6.1}   │└─────────────────────────────────────────────────┘

这种设计结合了两者优势：

• • 固定字段走传统索引，查询快
• • 动态字段用JSON，灵活扩展
• • 关联查询只需一次JOIN

2.3 视图方案和JSONB方案的表结构设计

表结构是专门为对比两种方案设计的：

┌─────────────────────────────────────────────────────────────┐│                      custom_main (主表)                      ││  id (PK) | str1 | str2 | ... | int1 | date1 | col1         ││  固定字段：18个字段（8个字符串、4个整数、4个日期、1个其他）    │└─────────────────────────────────────────────────────────────┘                              │                              │ 1:1 关联 (object_id → id)              ┌───────────────┴───────────────┐              ▼                               ▼┌─────────────────────────┐     ┌─────────────────────────────┐│   custom_json (JSONB方案) │     │    custom_text (视图方案)    ││  id | object_id | json_value │     │   id | object_id | json_value ││  JSONB类型，带GIN索引        │     │   TEXT类型，用于构建视图      │└─────────────────────────┘     └─────────────────────────────┘                                            │                                            │ 数据来源                                            ▼                              ┌─────────────────────────────────┐                              │    custom_main_view (视图)       │                              │  将custom_text的JSON展开为10列    │                              │  key1 | key2 | ... | key10      │                              └─────────────────────────────────┘

🎬 配套视频：工业软件动态属性架构设计

三、方案一：视图方案（不推荐）

3.1 核心思路

把JSON字段展开成虚拟列，通过视图暴露出"固定列"，让业务方像查普通表一样查动态属性。

3.2 表结构

-- 主表：存储18个固定字段CREATE TABLE custom_main (    id SERIAL PRIMARY KEY,    str1 VARCHAR(100),    str2 VARCHAR(100),    str3 VARCHAR(100),    str4 VARCHAR(100),    str5 VARCHAR(100),    str6 VARCHAR(100),    str7 VARCHAR(100),    str8 VARCHAR(100),    int1 INTEGER,    int2 INTEGER,    int3 INTEGER,    int4 INTEGER,    date1 DATE,    date2 DATE,    date3 DATE,    date4 DATE,    col1 VARCHAR(50));-- 扩展属性表：TEXT存储JSONCREATE TABLE custom_text (    id SERIAL PRIMARY KEY,    object_id INTEGER REFERENCES custom_main(id),    json_value TEXT);-- 视图：将JSON展开为10个虚拟列CREATE OR REPLACE VIEW custom_main_view ASSELECT    c.object_id,    (c.json_value::jsonb ->> 'key1') AS key1,    (c.json_value::jsonb ->> 'key2') AS key2,    (c.json_value::jsonb ->> 'key3') AS key3,    (c.json_value::jsonb ->> 'key4') AS key4,    (c.json_value::jsonb ->> 'key5') AS key5,    (c.json_value::jsonb ->> 'key6') AS key6,    (c.json_value::jsonb ->> 'key7') AS key7,    (c.json_value::jsonb ->> 'key8') AS key8,    (c.json_value::jsonb ->> 'key9') AS key9,    (c.json_value::jsonb ->> 'key10') AS key10FROM custom_text cWHERE c.json_value <> '';

3.3 查询方式

-- 视图方案：筛选key1包含'VH'，按key1降序SELECT    a.id, a.str1, a.str2, a.str3,    b.key1, b.key2, b.key3, b.key4FROM custom_main aLEFT JOIN custom_main_view b ON a.id = b.object_idWHERE b.key1 LIKE '%VH%'ORDER BY b.key1 DESC;

3.4 💡 视图方案的坑

四、方案二：JSONB方案（⭐推荐）

4.1 核心思路

利用PostgreSQL的JSONB类型原生支持，配合GIN索引，实现高性能的动态属性查询。

4.2 表结构与索引

-- 主表不变（同上）-- 扩展属性表：JSONB存储CREATE TABLE custom_json (    id SERIAL PRIMARY KEY,    object_id INTEGER REFERENCES custom_main(id),    json_value JSONB);-- 创建GIN索引加速查询（重要！）CREATE INDEX idx_custom_json_value ON custom_json USING gin(json_value);-- 对高频查询字段建表达式索引（可选，进一步提升性能）CREATE INDEX idx_custom_json_key1 ON custom_json ((json_value ->> 'key1'));

4.3 查询方式

-- JSONB方案：筛选key1包含'VH'，按key1降序SELECT    a.id, a.str1, a.str2, a.str3,    b.json_value ->> 'key1' AS key1,    b.json_value ->> 'key2' AS key2,    b.json_value ->> 'key3' AS key3,    b.json_value ->> 'key4' AS key4FROM custom_main aLEFT JOIN custom_json b ON a.id = b.object_idWHERE b.json_value ->> 'key1' LIKE '%VH%'ORDER BY b.json_value ->> 'key1' DESC;

五、硬核实测：差距高达25倍！

我拿一张存了18个固定字段和10个动态字段的表，插入了10万条和100万条数据进行横向对比：

结论：JSONB方案不管数据量多少，查询速度都明显优于视图方案。

六、完整示例代码

6.1 测试数据准备

-- 插入主表数据INSERT INTO custom_main (str1, str2, str3, str4, str5, str6, str7, str8, int1, int2, int3, int4, date1, date2, date3, date4, col1)VALUES('A001', '产品A', '类型1', '品牌X', '规格1', '颜色红', '材质钢', '等级A', 100, 200, 300, 400, '2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01', '正常'),('B002', '产品B', '类型2', '品牌Y', '规格2', '颜色蓝', '材质铝', '等级B', 150, 250, 350, 450, '2024-01-15', '2024-02-15', '2024-03-15', '2024-04-15', '促销'),('C003', '产品C', '类型1', '品牌Z', '规格3', '颜色绿', '材质塑料', '等级C', 120, 220, 320, 420, '2024-01-20', '2024-02-20', '2024-03-20', '2024-04-20', '新品');-- JSONB方案数据INSERT INTO custom_json (object_id, json_value) VALUES(1, '{"key1": "VH001", "key2": "value2", "key3": "value3", "key4": "value4", "key5": "value5"}'),(2, '{"key1": "VH002", "key2": "data2", "key3": "data3", "key4": "data4", "key5": "data5"}'),(3, '{"key1": "XY001", "key2": "info2", "key3": "info3", "key4": "info4", "key5": "info5"}');-- 视图方案数据INSERT INTO custom_text (object_id, json_value) VALUES(1, '{"key1": "VH001", "key2": "value2", "key3": "value3", "key4": "value4", "key5": "value5"}'),(2, '{"key1": "VH002", "key2": "data2", "key3": "data3", "key4": "data4", "key5": "data5"}'),(3, '{"key1": "XY001", "key2": "info2", "key3": "info3", "key4": "info4", "key5": "info5"}');

6.2 Java测试代码

package com.example.dynamic.test;import java.sql.*;import java.util.ArrayList;import java.util.List;public class DynamicAttributeComparison {    private static final String PG_URL = "jdbc:postgresql://localhost:5432/testdb";    private static final String PG_USER = "postgres";    private static final String PG_PASSWORD = "123456";    public static void main(String[] args) {        System.out.println("=== 动态属性筛选和排序方案对比 ===\n");        try {            Class.forName("org.postgresql.Driver");            testPostgreSQL();        } catch (Exception e) {            e.printStackTrace();        }    }    private static void testPostgreSQL() throws SQLException {        try (Connection conn = DriverManager.getConnection(PG_URL, PG_USER, PG_PASSWORD)) {            // 1. 视图方案查询            System.out.println("1. 视图方案 - 筛选key1包含'VH'的记录，按key1降序:");            String viewQuery =                "SELECT a.id, a.str2, b.key1, b.key2, b.key3 " +                "FROM custom_main a " +                "LEFT JOIN custom_main_view b ON a.id = b.object_id " +                "WHERE b.key1 LIKE '%VH%' " +                "ORDER BY b.key1 DESC";            long startTime = System.nanoTime();            try (Statement stmt = conn.createStatement();                 ResultSet rs = stmt.executeQuery(viewQuery)) {                List<String> results = new ArrayList<>();                while (rs.next()) {                    results.add(String.format("ID:%d, 产品:%s, key1:%s, key2:%s",                         rs.getInt("id"), rs.getString("str2"),                         rs.getString("key1"), rs.getString("key2")));                }                long endTime = System.nanoTime();                for (String result : results) {                    System.out.println("  " + result);                }                System.out.printf("  视图方案耗时: %.2f ms%n", (endTime - startTime) / 1_000_000.0);            }            // 2. JSONB方案查询            System.out.println("\n2. JSONB方案 - 筛选key1包含'VH'的记录，按key1降序:");            String jsonbQuery =                "SELECT a.id, a.str2, " +                "b.json_value ->> 'key1' AS key1, " +                "b.json_value ->> 'key2' AS key2, " +                "b.json_value ->> 'key3' AS key3 " +                "FROM custom_main a " +                "LEFT JOIN custom_json b ON a.id = b.object_id " +                "WHERE b.json_value ->> 'key1' LIKE '%VH%' " +                "ORDER BY b.json_value ->> 'key1' DESC";            startTime = System.nanoTime();            try (Statement stmt = conn.createStatement();                 ResultSet rs = stmt.executeQuery(jsonbQuery)) {                List<String> results = new ArrayList<>();                while (rs.next()) {                    results.add(String.format("ID:%d, 产品:%s, key1:%s, key2:%s",                         rs.getInt("id"), rs.getString("str2"),                         rs.getString("key1"), rs.getString("key2")));                }                long endTime = System.nanoTime();                for (String result : results) {                    System.out.println("  " + result);                }                System.out.printf("  JSONB方案耗时: %.2f ms%n", (endTime - startTime) / 1_000_000.0);            }            // 3. 动态排序测试            System.out.println("\n3. JSONB方案 - 按key2升序排序:");            String orderQuery =                "SELECT a.id, a.str2, " +                "b.json_value ->> 'key1' AS key1, " +                "b.json_value ->> 'key2' AS key2 " +                "FROM custom_main a " +                "LEFT JOIN custom_json b ON a.id = b.object_id " +                "ORDER BY b.json_value ->> 'key2' ASC";            try (Statement stmt = conn.createStatement();                 ResultSet rs = stmt.executeQuery(orderQuery)) {                while (rs.next()) {                    System.out.printf("  ID:%d, 产品:%s, key1:%s, key2:%s%n",                        rs.getInt("id"), rs.getString("str2"),                        rs.getString("key1"), rs.getString("key2"));                }            }            // 4. GIN索引优化            System.out.println("\n4. 创建GIN索引优化JSONB查询:");            try (Statement stmt = conn.createStatement()) {                stmt.execute("CREATE INDEX IF NOT EXISTS idx_custom_json_value ON custom_json USING gin(json_value)");                System.out.println("  ✓ GIN索引已创建，可加速JSONB字段查询");            } catch (SQLException e) {                System.out.println("  索引创建失败: " + e.getMessage());            }        }    }}

6.3 运行结果

=== 动态属性筛选和排序方案对比 ===1. 视图方案 - 筛选key1包含'VH'的记录，按key1降序:  ID:2, 产品:产品B, key1:VH002, key2:data2  ID:1, 产品:产品A, key1:VH001, key2:value2  视图方案耗时: 850.23 ms2. JSONB方案 - 筛选key1包含'VH'的记录，按key1降序:  ID:2, 产品:产品B, key1:VH002, key2:data2  ID:1, 产品:产品A, key1:VH001, key2:value2  JSONB方案耗时: 120.45 ms3. JSONB方案 - 按key2升序排序:  ID:2, 产品:产品B, key1:VH002, key2:data2  ID:3, 产品:产品C, key1:XY001, key2:info2  ID:1, 产品:产品A, key1:VH001, key2:value24. 创建GIN索引优化JSONB查询:  ✓ GIN索引已创建，可加速JSONB字段查询

七、JSONB操作符速查表

八、踩坑提醒

坑1：-> 和 ->> 傻傻分不清

-- ❌ 错误：用->结果带引号SELECT json_value -> 'key1' FROM custom_json;-- 结果: "VH001" （带双引号）-- ✅ 正确：用->>返回纯文本SELECT json_value ->> 'key1' FROM custom_json;-- 结果: VH001 （无引号）

坑2：GIN索引没生效

-- ❌ 错误：用->>操作符不走GIN索引EXPLAIN SELECT * FROM custom_json WHERE json_value ->> 'key1' = 'VH001';-- ✅ 正确：用@>包含操作符才能用GIN索引EXPLAIN SELECT * FROM custom_json WHERE json_value @> '{"key1":"VH001"}';-- 💡 或者对高频字段建表达式索引CREATE INDEX idx_key1 ON custom_json ((json_value ->> 'key1'));

坑3：视图方案的类型转换开销

-- 视图方案每行都要做类型转换，性能差(c.json_value::jsonb ->> 'key1')::text-- JSONB方案直接操作，无需转换b.json_value ->> 'key1'

九、总结

核心结论

1. 1. JSONB方案完胜视图方案：查询速度快7~25倍，存储空间节省30%
2. 2. 查询/排序统一用->>：避免引号问题
3. 3. 建GIN索引是关键：CREATE INDEX ... USING gin(json_value)
4. 4. 固定字段+JSONB扩展：兼顾性能和灵活性

什么时候用哪种方案？

下一篇预告

JSONB方案的动态SQL构建——如何让前端动态传入筛选和排序字段，实现灵活查询？

📍 关于我

我是麻雀，6年央国企实战派，专注分布式系统。每周一篇硬核技术文。

公众号/B站：麻雀聊技术（关注领配套资料）

如果觉得有用，欢迎点赞、评论、转发。有问题评论区见。