material_texture/docs/DATABASE.md

数据库设计文档

概述

Material Texture 使用 PostgreSQL 数据库存储材质信息和绑定关系。

• 数据库: PostgreSQL 15+
• ORM: GORM v1.31
• 特性: 分区表、pg_trgm 模糊搜索

---

ER 图

erDiagram
    materials ||--o{ material_bindings : "has"

    materials {
        bigint id PK "自增主键"
        varchar name "材质名称"
        float diffuse_r "漫反射-红"
        float diffuse_g "漫反射-绿"
        float diffuse_b "漫反射-蓝"
        float alpha "透明度"
        float shininess "光泽度"
        float specular_r "高光-红"
        float specular_g "高光-绿"
        float specular_b "高光-蓝"
        float ambient_r "环境光-红"
        float ambient_g "环境光-绿"
        float ambient_b "环境光-蓝"
        float metallic "金属度(PBR)"
        float roughness "粗糙度(PBR)"
        float reflectance "反射率(PBR)"
        bigint version "乐观锁版本"
        timestamp created_at "创建时间"
        timestamp updated_at "更新时间"
    }

    material_bindings {
        bigint id PK "绑定ID"
        bigint material_id FK "材质ID"
        varchar group_id "叶子节点ID"
        timestamp created_at "创建时间"
    }

---

表结构详情

1. materials 表（材质表）

存储 3D 材质的属性信息，包括传统渲染属性和 PBR 属性。

字段	类型	约束	默认值	说明
id	BIGSERIAL	PRIMARY KEY	自增	材质唯一标识
name	VARCHAR(255)	NOT NULL	-	材质名称
diffuse_r	FLOAT	NOT NULL	0	漫反射颜色-红 (0-255)
diffuse_g	FLOAT	NOT NULL	0	漫反射颜色-绿 (0-255)
diffuse_b	FLOAT	NOT NULL	0	漫反射颜色-蓝 (0-255)
alpha	FLOAT	NOT NULL	1	透明度 (0-255)
shininess	FLOAT	NOT NULL	0	光泽度/高光强度
specular_r	FLOAT	NOT NULL	0	高光颜色-红 (0-255)
specular_g	FLOAT	NOT NULL	0	高光颜色-绿 (0-255)
specular_b	FLOAT	NOT NULL	0	高光颜色-蓝 (0-255)
ambient_r	FLOAT	NOT NULL	0	环境光颜色-红 (0-255)
ambient_g	FLOAT	NOT NULL	0	环境光颜色-绿 (0-255)
ambient_b	FLOAT	NOT NULL	0	环境光颜色-蓝 (0-255)
metallic	FLOAT	NOT NULL	0	PBR 金属度 (0-1)
roughness	FLOAT	NOT NULL	0.5	PBR 粗糙度 (0-1)
reflectance	FLOAT	NOT NULL	0.5	PBR 反射率 (0-1)
version	BIGINT	NOT NULL	0	乐观锁版本号
created_at	TIMESTAMP	-	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	-	CURRENT_TIMESTAMP	更新时间

索引

索引名	类型	字段	用途
materials_pkey	PRIMARY KEY	id	主键
idx_materials_name_trgm	GIN	name	模糊搜索 (ILIKE '%keyword%')
idx_materials_created_at	BTREE	created_at DESC	按创建时间排序
idx_materials_updated_at	BTREE	updated_at DESC	按更新时间排序

---

2. material_bindings 表（材质绑定表）

存储材质与叶子节点（group_id）的多对多绑定关系。

重要: 此表使用 HASH 分区，按 material_id 分为 16 个分区，支持亿级数据规模。

字段	类型	约束	默认值	说明
id	BIGSERIAL	复合主键	自增	绑定记录ID
material_id	BIGINT	NOT NULL, FK	-	关联的材质ID
group_id	VARCHAR(255)	NOT NULL	-	叶子节点ID
created_at	TIMESTAMP	-	CURRENT_TIMESTAMP	创建时间

约束

• 主键: (material_id, id) - 复合主键，支持分区
• 外键: material_id → materials(id) ON DELETE CASCADE
• 唯一约束: (material_id, group_id) - 防止重复绑定

分区策略

material_bindings (父表)
├── material_bindings_p0  (material_id % 16 = 0)
├── material_bindings_p1  (material_id % 16 = 1)
├── material_bindings_p2  (material_id % 16 = 2)
│   ...
└── material_bindings_p15 (material_id % 16 = 15)

每个分区的索引

索引名	类型	字段	用途
idx_bindings_pX_unique	UNIQUE	(material_id, group_id)	防止重复绑定
idx_bindings_pX_group	BTREE	group_id	按 group_id 查询

---

迁移文件

文件	版本	说明
001_init.sql	v1.0	初始化基础表结构
002_partition_bindings.sql	v1.1	将 bindings 表转为 16 分区
003_add_indexes.sql	v1.2	添加 pg_trgm 和时间索引
004_add_version_column.sql	v1.3	添加乐观锁版本字段

执行迁移

# 进入数据库容器
docker compose exec db psql -U postgres -d material_db

# 或直接执行
psql -h localhost -p 5433 -U postgres -d material_db -f migrations/001_init.sql

---

性能优化说明

1. 分区表

• 目的: 支持亿级 binding 记录
• 策略: HASH 分区，按 material_id 分 16 片
• 优势:
  • 单分区最大约 600-1000 万行
  • 查询时自动路由到相关分区
  • 可在线添加新分区

2. pg_trgm 扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

• 目的: 支持模糊搜索 ILIKE '%keyword%'
• 原理: 将字符串拆分为三元组 (trigram)，建立 GIN 倒排索引

3. 连接池配置

参数	值	说明
MaxIdleConns	50	最大空闲连接数
MaxOpenConns	200	最大连接数
ConnMaxLifetime	5min	连接最大生命周期
ConnMaxIdleTime	2min	空闲连接最大生命周期

---

数据示例

Material 示例

{
  "id": 4,
  "name": "红色金属",
  "diffuse_r": 255,
  "diffuse_g": 0,
  "diffuse_b": 0,
  "alpha": 255,
  "shininess": 80,
  "specular_r": 255,
  "specular_g": 200,
  "specular_b": 200,
  "ambient_r": 50,
  "ambient_g": 0,
  "ambient_b": 0,
  "metallic": 0.9,
  "roughness": 0.2,
  "reflectance": 0.8,
  "version": 0,
  "created_at": "2025-12-09T09:53:33Z",
  "updated_at": "2025-12-09T09:53:33Z"
}

Binding 示例

{
  "id": 1,
  "material_id": 4,
  "group_id": "20251021105716587535",
  "created_at": "2025-12-09T10:00:00Z"
}

---

常用查询

1. 按名称模糊搜索材质

SELECT * FROM materials
WHERE name ILIKE '%金属%'
ORDER BY id DESC
LIMIT 20;

2. 获取材质关联的所有 group_id

SELECT group_id FROM material_bindings
WHERE material_id = 4
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

3. 根据 group_id 批量查询材质

SELECT mb.group_id, m.*
FROM material_bindings mb
JOIN materials m ON mb.material_id = m.id
WHERE mb.group_id IN ('group1', 'group2', 'group3');

4. 统计各分区数据量

SELECT tableoid::regclass AS partition, COUNT(*)
FROM material_bindings
GROUP BY tableoid;