85ba15c564
- Go + Gin + GORM + PostgreSQL backend - RESTful API for material management - Docker deployment support - Database partitioning for billion-scale data - API documentation 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
258 lines
6.8 KiB
Markdown
258 lines
6.8 KiB
Markdown
# 数据库设计文档
|
||
|
||
## 概述
|
||
|
||
Material Texture 使用 PostgreSQL 数据库存储材质信息和绑定关系。
|
||
|
||
- **数据库**: PostgreSQL 15+
|
||
- **ORM**: GORM v1.31
|
||
- **特性**: 分区表、pg_trgm 模糊搜索
|
||
|
||
---
|
||
|
||
## ER 图
|
||
|
||
```mermaid
|
||
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 | 添加乐观锁版本字段 |
|
||
|
||
### 执行迁移
|
||
|
||
```bash
|
||
# 进入数据库容器
|
||
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 扩展
|
||
|
||
```sql
|
||
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
||
```
|
||
|
||
- **目的**: 支持模糊搜索 `ILIKE '%keyword%'`
|
||
- **原理**: 将字符串拆分为三元组 (trigram)，建立 GIN 倒排索引
|
||
|
||
### 3. 连接池配置
|
||
|
||
| 参数 | 值 | 说明 |
|
||
|------|-----|------|
|
||
| MaxIdleConns | 50 | 最大空闲连接数 |
|
||
| MaxOpenConns | 200 | 最大连接数 |
|
||
| ConnMaxLifetime | 5min | 连接最大生命周期 |
|
||
| ConnMaxIdleTime | 2min | 空闲连接最大生命周期 |
|
||
|
||
---
|
||
|
||
## 数据示例
|
||
|
||
### Material 示例
|
||
|
||
```json
|
||
{
|
||
"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 示例
|
||
|
||
```json
|
||
{
|
||
"id": 1,
|
||
"material_id": 4,
|
||
"group_id": "20251021105716587535",
|
||
"created_at": "2025-12-09T10:00:00Z"
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 常用查询
|
||
|
||
### 1. 按名称模糊搜索材质
|
||
|
||
```sql
|
||
SELECT * FROM materials
|
||
WHERE name ILIKE '%金属%'
|
||
ORDER BY id DESC
|
||
LIMIT 20;
|
||
```
|
||
|
||
### 2. 获取材质关联的所有 group_id
|
||
|
||
```sql
|
||
SELECT group_id FROM material_bindings
|
||
WHERE material_id = 4
|
||
ORDER BY created_at DESC
|
||
LIMIT 20 OFFSET 0;
|
||
```
|
||
|
||
### 3. 根据 group_id 批量查询材质
|
||
|
||
```sql
|
||
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. 统计各分区数据量
|
||
|
||
```sql
|
||
SELECT tableoid::regclass AS partition, COUNT(*)
|
||
FROM material_bindings
|
||
GROUP BY tableoid;
|
||
```
|