葫芦生 - 产品核心数据库设计文档

🎯 设计目标

为AR+LBS景区探索小程序《葫芦生》设计完整的数据架构，特别针对《赵籁》篇章提供专业的历史文化数据支撑。

1. 数据库概述

1.1 设计原则

🎯 业务导向

围绕AR+LBS探索体验设计，支持用户成长、葫芦养成、NPC交互等核心功能

⚡ 性能优先

针对移动端高并发场景优化，支持地理位置查询、实时交互等性能要求

🔧 扩展性强

支持水平分表、垂直分库，为未来多景区、多活动扩展预留空间

🛡️ 数据安全

完整的数据约束、事务保证、备份恢复机制，确保用户数据安全

1.2 技术规范

项目	规范	说明
数据库引擎	MySQL 8.0 InnoDB	支持事务、外键约束、行级锁
字符集	utf8mb4	支持emoji和特殊字符
排序规则	utf8mb4_unicode_ci	不区分大小写，支持多语言
主键策略	BIGINT AUTO_INCREMENT	支持大数据量，便于分库分表
时间字段	TIMESTAMP	统一使用UTC时区

2. 用户系统表

2.1 users（用户表）核心表

存储用户基础信息，支持微信登录和用户状态管理。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
openid	VARCHAR	64	微信openid	NOT NULL	UK
unionid	VARCHAR	64	微信unionid	NULL	INDEX
nickname	VARCHAR	64	用户昵称	NOT NULL	-
avatar_url	VARCHAR	512	头像地址	NULL	-
current_gourd_id	BIGINT	-	当前激活的葫芦ID	NULL	FK, INDEX
level	INT	-	用户等级	DEFAULT 1	INDEX
experience	INT	-	经验值	DEFAULT 0	INDEX
total_activities_completed	INT	-	已完成活动总数	DEFAULT 0	INDEX
status	TINYINT	-	用户状态(0:禁用,1:正常)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	INDEX
updated_at	TIMESTAMP	-	更新时间	ON UPDATE CURRENT_TIMESTAMP	-
last_login_at	TIMESTAMP	-	最后登录时间	NULL	INDEX

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    openid VARCHAR(64) NOT NULL UNIQUE COMMENT '微信openid',
    unionid VARCHAR(64) COMMENT '微信unionid',
    nickname VARCHAR(64) NOT NULL COMMENT '用户昵称',
    avatar_url VARCHAR(512) COMMENT '头像地址',
    current_gourd_id BIGINT UNSIGNED COMMENT '当前激活的葫芦ID',
    level INT UNSIGNED DEFAULT 1 COMMENT '用户等级',
    experience INT UNSIGNED DEFAULT 0 COMMENT '经验值',
    total_activities_completed INT UNSIGNED DEFAULT 0 COMMENT '已完成活动总数',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '用户状态(0:禁用,1:正常)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    last_login_at TIMESTAMP NULL COMMENT '最后登录时间',
    
    INDEX idx_openid (openid),
    INDEX idx_unionid (unionid),
    INDEX idx_current_gourd (current_gourd_id),
    INDEX idx_level (level),
    INDEX idx_experience (experience),
    INDEX idx_activities (total_activities_completed),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_last_login (last_login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
                

📚 篇章管理系统

支持《赵籁》7个历史篇章的完整管理，包括篇章报幕、历史背景、NPC分组等功能。

chapters（篇章表）

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
name	VARCHAR	64	篇章名称	NOT NULL
subtitle	VARCHAR	128	篇章副标题	NOT NULL
story_phase	VARCHAR	32	故事阶段	NOT NULL	INDEX
theme	VARCHAR	128	篇章主题	NOT NULL
story_context	TEXT	-	故事脉络描述	NOT NULL
prologue_text	TEXT	-	篇章报幕文本	NOT NULL
prologue_video_url	VARCHAR	512	篇章报幕视频	NULL
epilogue_text	TEXT	-	篇章总结文本	NOT NULL
epilogue_audio_url	VARCHAR	512	篇章总结音频	NULL
historical_period_start	INT	-	历史时期开始年份	NOT NULL	INDEX
historical_period_end	INT	-	历史时期结束年份	NOT NULL	INDEX
npc_count	INT	-	该篇章NPC数量	NOT NULL
display_order	INT	-	显示顺序(1-7)	NOT NULL	UK
unlock_conditions	JSON	-	解锁条件	NULL
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

📜 《赵籁》数据初始化

7个篇章的基础数据结构，支撑完整的赵国兴衰史体验。

-- 插入7个篇章数据
INSERT INTO chapters (id, name, subtitle, story_phase, theme, story_context, prologue_text, epilogue_text, historical_period_start, historical_period_end, npc_count, display_order) VALUES
(1, '赵氏初啼·铁血奠基', '马足光阴赋', '起源', '赵氏起源，传承奠基', 
 '造父驭马，叔带择主，赵氏在西周末年开始崭露头角', 
 '造父执辔，为王驾车平乱，因功封于赵城，赵氏由此而始...', 
 '马蹄踏碎光阴，赵城初启时，不过一握黄沙，却是赵国七百年兴衰的起点...', 
 -1000, -800, 2, 1),

(2, '血火涅槃·孤魂泣血', '残烛照孤赋', '发展', '忠义传承，血泪复仇', 
 '赵氏在晋国立足，却遭遇灭门之祸，程婴舍子救孤，赵武复仇雪恨', 
 '赵氏在晋国立足，却遭遇灭门之祸。程婴舍子救孤，赵武复仇雪恨...', 
 '下宫的雪，冻住了多少哭声？程婴捧起的，不是赵氏孤儿，是自己的半条命...', 
 -800, -600, 5, 2),

(3, '春秋争霸·六卿并立', '权谋天下赋', '崛起', '政治智慧，权谋天下', 
 '赵氏成为晋国六卿之一，在春秋乱世中展现政治智慧', 
 '春秋无义战，唯有智者生。赵氏在晋国六卿中崭露头角...', 
 '六卿并立的晋国，如同六匹烈马拉车，赵氏已握住了缰绳...', 
 -600, -453, 6, 3),

(4, '三家分晋·立国之基', '开国雄风赋', '独立', '开国立业，雄风初显', 
 '三家分晋，赵国正式建立，开启独立发展的新篇章', 
 '韩赵魏三家分晋，赵国正式登上历史舞台...', 
 '分晋之日，便是立国之时。赵国的旗帜，终于在这片土地上飘扬...', 
 -453, -350, 4, 4),

(5, '胡服骑射·变法图强', '革新强国赋', '鼎盛', '改革创新，国力鼎盛', 
 '赵武灵王胡服骑射，军事改革让赵国成为战国强国', 
 '胡服骑射，这四个字改变了赵国的命运...', 
 '当赵国的骑兵踏过燕山，当胡服成为时尚，变革的力量震撼天下...', 
 -350, -260, 7, 5),

(6, '长平血战·由盛转衰', '悲歌壮士赋', '衰落', '悲壮抗争，由盛转衰', 
 '长平之战，赵国元气大伤，从此走向衰落', 
 '长平一战，四十万赵军埋骨他乡...', 
 '长平的风，至今还在哭泣。那四十万英魂，是赵国由盛转衰的分水岭...', 
 -260, -228, 5, 6),

(7, '邯郸陷落·王朝终章', '末路英雄赋', '灭亡', '末路英雄，王朝终章', 
 '秦军破邯郸，赵国灭亡，赵氏王朝落下帷幕', 
 '邯郸城破之日，便是赵国终结之时...', 
 '七百年兴衰，终归尘土。但赵国的精神，却如星火般传承不息...', 
 -228, -222, 1, 7);
                

3. 葫芦系统表

3.1 gourds（葫芦表）核心表

存储可选择的葫芦分身信息，每个葫芦有不同的属性和特色。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
name	VARCHAR	64	葫芦名称	NOT NULL	UK
description	TEXT	-	葫芦描述	NOT NULL	-
preview_video_url	VARCHAR	512	预览视频地址	NULL	-
transform_video_url	VARCHAR	512	变身视频地址	NULL	-
base_strategy	INT	-	基础谋略值	DEFAULT 50	INDEX
base_warfare	INT	-	基础武力值	DEFAULT 50	INDEX
base_benevolence	INT	-	基础仁德值	DEFAULT 50	INDEX
base_chivalry	INT	-	基础侠义值	DEFAULT 50	INDEX
base_versatility	INT	-	基础才艺值	DEFAULT 50	INDEX
story	TEXT	-	葫芦背景故事	NULL	-
unlock_level	INT	-	解锁等级要求	DEFAULT 1	INDEX
rarity	TINYINT	-	稀有度(1-5)	DEFAULT 1	INDEX
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	INDEX
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

3.2 user_gourds（用户葫芦表）关联表

存储用户拥有的葫芦及其成长数据。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
user_id	BIGINT	-	用户ID	NOT NULL	FK, INDEX
gourd_id	BIGINT	-	葫芦ID	NOT NULL	FK, INDEX
current_strategy	INT	-	当前谋略值	NOT NULL	INDEX
current_warfare	INT	-	当前武力值	NOT NULL	INDEX
current_benevolence	INT	-	当前仁德值	NOT NULL	INDEX
current_chivalry	INT	-	当前侠义值	NOT NULL	INDEX
current_versatility	INT	-	当前才艺值	NOT NULL	INDEX
level	INT	-	葫芦等级	DEFAULT 1	INDEX
experience	INT	-	葫芦经验值	DEFAULT 0	INDEX
is_active	TINYINT	-	是否当前激活	DEFAULT 0	INDEX
obtained_at	TIMESTAMP	-	获得时间	DEFAULT CURRENT_TIMESTAMP	INDEX
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

4. 活动系统表

4.1 activities（活动表）核心表

存储景区内的各种活动信息。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
chapter_id	BIGINT	-	所属篇章ID	NOT NULL	FK, INDEX
title	VARCHAR	128	活动标题	NOT NULL	INDEX
description	TEXT	-	活动描述	NOT NULL	-
activity_type	TINYINT	-	活动类型(1:故事,2:挑战,3:收集)	NOT NULL	INDEX
min_level	INT	-	最低等级要求	DEFAULT 1	INDEX
ar_marker_url	VARCHAR	512	AR标记图片地址	NULL	-
npc_video_url	VARCHAR	512	NPC视频地址	NULL	-
reward_experience	INT	-	奖励经验值	DEFAULT 0	INDEX
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	INDEX
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

4.2 activity_locations（活动位置表）地理表

存储活动的地理位置信息，支持LBS功能。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
activity_id	BIGINT	-	活动ID	NOT NULL	FK, UK
latitude	DECIMAL	10,8	纬度	NOT NULL	INDEX
longitude	DECIMAL	11,8	经度	NOT NULL	INDEX
radius	INT	-	触发半径(米)	DEFAULT 50	-
address	VARCHAR	256	地址描述	NULL	-
landmark	VARCHAR	128	地标名称	NULL	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

5. 交互系统表

5.1 npcs（NPC表）核心表

存储历史人物NPC信息，支持《赵籁》的历史教育功能。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
chapter_id	BIGINT	-	所属篇章ID	NOT NULL	FK, INDEX
activity_id	BIGINT	-	所属活动ID	NOT NULL	FK, INDEX
name	VARCHAR	64	NPC姓名	NOT NULL	INDEX
title	VARCHAR	128	NPC称号/身份	NULL	-
historical_background	TEXT	-	历史背景	NOT NULL	-
personality	VARCHAR	256	性格特点	NULL	-
avatar_url	VARCHAR	512	头像地址	NULL	-
voice_url	VARCHAR	512	语音地址	NULL	-
interaction_count	INT	-	可交互次数	DEFAULT 3	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

5.2 interactions（交互记录表）记录表

记录用户与NPC的交互历史，支持三模块交互流程。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
user_id	BIGINT	-	用户ID	NOT NULL	FK, INDEX
npc_id	BIGINT	-	NPC ID	NOT NULL	FK, INDEX
interaction_type	TINYINT	-	交互类型(1:两难抉择,2:人生遗憾,3:哲思执念)	NOT NULL	INDEX
question_id	BIGINT	-	问题ID	NOT NULL	FK, INDEX
strategy_id	BIGINT	-	选择的策略ID	NULL	FK, INDEX
attribute_changes	JSON	-	属性变化记录	NULL	-
rewards	JSON	-	获得奖励	NULL	-
completion_status	TINYINT	-	完成状态(0:进行中,1:已完成)	DEFAULT 0	INDEX
created_at	TIMESTAMP	-	交互时间	DEFAULT CURRENT_TIMESTAMP	INDEX
completed_at	TIMESTAMP	-	完成时间	NULL	INDEX

6. 收藏系统表

6.1 collections（收藏品表）奖励表

存储用户可获得的收藏品信息。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
name	VARCHAR	128	收藏品名称	NOT NULL	INDEX
description	TEXT	-	收藏品描述	NOT NULL	-
type	TINYINT	-	类型(1:文物,2:诗词,3:典故,4:人物)	NOT NULL	INDEX
rarity	TINYINT	-	稀有度(1-5)	NOT NULL	INDEX
icon_url	VARCHAR	512	图标地址	NULL	-
image_url	VARCHAR	512	详情图片地址	NULL	-
historical_value	TEXT	-	历史价值说明	NULL	-
cultural_significance	TEXT	-	文化意义	NULL	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

7. 系统配置表

7.1 system_configs（系统配置表）配置表

存储系统全局配置信息。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
config_key	VARCHAR	128	配置键	NOT NULL	UK
config_value	TEXT	-	配置值	NOT NULL	-
config_type	VARCHAR	32	配置类型	NOT NULL	INDEX
description	VARCHAR	256	配置描述	NULL	-
is_public	TINYINT	-	是否公开(0:否,1:是)	DEFAULT 0	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

🏛️ 历史人物关系

historical_figures（历史人物表）

存储《赵籁》中涉及的历史人物详细信息。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
name	VARCHAR	64	人物姓名	NOT NULL	INDEX
dynasty	VARCHAR	32	所属朝代	NOT NULL	INDEX
birth_year	INT	-	出生年份	NULL	INDEX
death_year	INT	-	逝世年份	NULL	INDEX
biography	TEXT	-	人物传记	NOT NULL	-
achievements	TEXT	-	主要成就	NULL	-
historical_significance	TEXT	-	历史意义	NULL	-
portrait_url	VARCHAR	512	画像地址	NULL	-
related_chapters	JSON	-	相关篇章ID列表	NULL	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

🎭 交互系统优化

针对《赵籁》三模块交互流程（两难抉择→人生遗憾→哲思执念）的专业数据库设计。

dilemma_questions（两难抉择问题表）

存储NPC提出的两难抉择问题，每个NPC有3个不同的问题。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
npc_id	BIGINT	-	所属NPC ID	NOT NULL	FK, INDEX
question_title	VARCHAR	256	问题标题	NOT NULL	-
question_content	TEXT	-	问题详细描述	NOT NULL	-
historical_context	TEXT	-	历史背景说明	NOT NULL	-
difficulty_level	TINYINT	-	难度等级(1-5)	DEFAULT 1	INDEX
required_attributes	JSON	-	所需属性要求	NULL	-
question_order	TINYINT	-	问题顺序(1-3)	NOT NULL	INDEX
unlock_conditions	JSON	-	解锁条件	NULL	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

strategies（策略选择表）

存储每个两难问题的上中下三策选择方案。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
question_id	BIGINT	-	所属问题ID	NOT NULL	FK, INDEX
strategy_type	TINYINT	-	策略类型(1:上策,2:中策,3:下策)	NOT NULL	INDEX
strategy_title	VARCHAR	128	策略标题	NOT NULL	-
strategy_description	TEXT	-	策略详细描述	NOT NULL	-
required_attributes	JSON	-	所需属性值	NOT NULL	-
attribute_rewards	JSON	-	属性奖励	NOT NULL	-
success_probability	DECIMAL	5,2	成功概率(0.00-1.00)	DEFAULT 1.00	INDEX
historical_wisdom	TEXT	-	历史智慧阐释	NULL	-
consequence_description	TEXT	-	选择后果描述	NULL	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

regret_stories（人生遗憾表）

存储NPC的人生遗憾故事，在完成两难问题后触发。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
npc_id	BIGINT	-	所属NPC ID	NOT NULL	FK, UK
regret_title	VARCHAR	128	遗憾标题	NOT NULL	-
regret_story	TEXT	-	遗憾故事内容	NOT NULL	-
emotional_tone	VARCHAR	32	情感基调	NOT NULL	INDEX
historical_lesson	TEXT	-	历史教训	NULL	-
comfort_options	JSON	-	安慰选项列表	NOT NULL	-
trigger_conditions	JSON	-	触发条件	NULL	-
attribute_bonus	JSON	-	属性加成(偶尔)	NULL	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

philosophy_insights（哲思执念表）

存储NPC的人生哲思和执念，在安慰后有条件触发。

字段名	类型	长度	说明	约束	索引
id	BIGINT	-	主键	PK, AUTO_INCREMENT	PK
npc_id	BIGINT	-	所属NPC ID	NOT NULL	FK, UK
philosophy_title	VARCHAR	128	哲思标题	NOT NULL	-
philosophy_content	TEXT	-	哲思内容	NOT NULL	-
wisdom_category	VARCHAR	32	智慧类别	NOT NULL	INDEX
life_insight	TEXT	-	人生感悟	NOT NULL	-
historical_reference	TEXT	-	历史典故引用	NULL	-
trigger_probability	DECIMAL	5,2	触发概率(0.00-1.00)	DEFAULT 0.50	INDEX
required_comfort_level	TINYINT	-	所需安慰等级	DEFAULT 1	INDEX
reward_items	JSON	-	奖励道具	NULL	-
attribute_bonus	JSON	-	属性加成(偶尔)	NULL	-
status	TINYINT	-	状态(0:禁用,1:启用)	DEFAULT 1	INDEX
created_at	TIMESTAMP	-	创建时间	DEFAULT CURRENT_TIMESTAMP	-
updated_at	TIMESTAMP	-	更新时间	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	-

🔄 三模块交互流程设计

🎯 模块1：两难抉择

用户面临历史情境中的道德或策略选择，通过上中下三策展现不同的智慧层次

💔 模块2：人生遗憾

NPC分享内心深处的遗憾，用户给予安慰，体现人文关怀和历史共情

🧠 模块3：哲思执念

触发NPC的人生哲思，获得珍贵道具和深层历史智慧，完成精神升华

📈 渐进式体验

从理性决策到情感共鸣再到哲学思辨，层层递进的交互深度设计

🎮 交互系统优化策略

🤖 智能NPC选择

基于用户交互历史和完成率，智能推荐最适合的NPC进行交互

SELECT n.*, 
       COUNT(i.id) as interaction_count,
       AVG(CASE WHEN i.completion_status = 1 THEN 1 ELSE 0 END) as completion_rate
FROM npcs n 
LEFT JOIN interactions i ON n.id = i.npc_id AND i.user_id = ?
WHERE n.chapter_id = ? AND n.status = 1
GROUP BY n.id
HAVING interaction_count < n.interaction_count
ORDER BY interaction_count ASC, RAND()
LIMIT 1;
                        

🎯 个性化问题推荐

根据用户葫芦属性值，推荐合适难度的两难问题

SELECT dq.*, 
       CASE 
           WHEN JSON_EXTRACT(dq.required_attributes, '$.strategy') <= ug.current_strategy THEN 1
           ELSE 0 
       END as can_attempt
FROM dilemma_questions dq
JOIN user_gourds ug ON ug.user_id = ? AND ug.is_active = 1
WHERE dq.npc_id = ? AND dq.status = 1
ORDER BY can_attempt DESC, dq.question_order ASC;
                        

⚡ 动态奖励计算

实时更新用户葫芦属性值和经验值，确保数值平衡

UPDATE user_gourds ug
SET current_strategy = LEAST(current_strategy + ?, 100),
    current_benevolence = LEAST(current_benevolence + ?, 100),
    experience = experience + ?
WHERE ug.user_id = ? AND ug.is_active = 1;
                        

🔄 交互流程控制

确保三模块交互的正确顺序和触发条件

-- 检查是否可以触发人生遗憾模块
SELECT rs.* FROM regret_stories rs
WHERE rs.npc_id = ? 
AND EXISTS (
    SELECT 1 FROM interactions i 
    WHERE i.npc_id = rs.npc_id 
    AND i.user_id = ? 
    AND i.interaction_type = 1 
    AND i.completion_status = 1
);
                        

🔗 外键约束

定义表间关系，确保数据完整性和一致性。

🏗️ 核心业务约束

👤 用户相关约束

-- 用户当前葫芦约束
ALTER TABLE users 
ADD CONSTRAINT fk_users_current_gourd 
FOREIGN KEY (current_gourd_id) 
REFERENCES user_gourds(id) 
ON DELETE SET NULL;

-- 用户葫芦关联约束
ALTER TABLE user_gourds 
ADD CONSTRAINT fk_user_gourds_user 
FOREIGN KEY (user_id) 
REFERENCES users(id) 
ON DELETE CASCADE;

ALTER TABLE user_gourds 
ADD CONSTRAINT fk_user_gourds_gourd 
FOREIGN KEY (gourd_id) 
REFERENCES gourds(id) 
ON DELETE CASCADE;
                        

🎭 活动系统约束

-- 活动篇章约束
ALTER TABLE activities 
ADD CONSTRAINT fk_activities_chapter 
FOREIGN KEY (chapter_id) 
REFERENCES chapters(id) 
ON DELETE CASCADE;

-- 活动位置约束
ALTER TABLE activity_locations 
ADD CONSTRAINT fk_activity_locations_activity 
FOREIGN KEY (activity_id) 
REFERENCES activities(id) 
ON DELETE CASCADE;
                        

🏛️ NPC系统约束

-- NPC篇章约束
ALTER TABLE npcs 
ADD CONSTRAINT fk_npcs_chapter 
FOREIGN KEY (chapter_id) 
REFERENCES chapters(id) 
ON DELETE CASCADE;

-- NPC活动约束
ALTER TABLE npcs 
ADD CONSTRAINT fk_npcs_activity 
FOREIGN KEY (activity_id) 
REFERENCES activities(id) 
ON DELETE CASCADE;
                        

💬 交互系统约束

-- 交互用户约束
ALTER TABLE interactions 
ADD CONSTRAINT fk_interactions_user 
FOREIGN KEY (user_id) 
REFERENCES users(id) 
ON DELETE CASCADE;

-- 交互NPC约束
ALTER TABLE interactions 
ADD CONSTRAINT fk_interactions_npc 
FOREIGN KEY (npc_id) 
REFERENCES npcs(id) 
ON DELETE CASCADE;
                        

🎯 三模块交互约束

❓ 两难问题约束

-- 问题NPC约束
ALTER TABLE dilemma_questions 
ADD CONSTRAINT fk_dilemma_questions_npc 
FOREIGN KEY (npc_id) 
REFERENCES npcs(id) 
ON DELETE CASCADE;

-- 策略问题约束
ALTER TABLE strategies 
ADD CONSTRAINT fk_strategies_question 
FOREIGN KEY (question_id) 
REFERENCES dilemma_questions(id) 
ON DELETE CASCADE;
                        

💔 人生遗憾约束

-- 遗憾NPC约束
ALTER TABLE regret_stories 
ADD CONSTRAINT fk_regret_stories_npc 
FOREIGN KEY (npc_id) 
REFERENCES npcs(id) 
ON DELETE CASCADE;

-- 确保每个NPC只有一个遗憾故事
ALTER TABLE regret_stories 
ADD CONSTRAINT uk_regret_stories_npc 
UNIQUE (npc_id);
                        

🧠 哲思执念约束

-- 哲思NPC约束
ALTER TABLE philosophy_insights 
ADD CONSTRAINT fk_philosophy_insights_npc 
FOREIGN KEY (npc_id) 
REFERENCES npcs(id) 
ON DELETE CASCADE;

-- 确保每个NPC只有一个哲思
ALTER TABLE philosophy_insights 
ADD CONSTRAINT uk_philosophy_insights_npc 
UNIQUE (npc_id);
                        

🎁 收藏系统约束

-- 用户收藏约束
ALTER TABLE user_collections 
ADD CONSTRAINT fk_user_collections_user 
FOREIGN KEY (user_id) 
REFERENCES users(id) 
ON DELETE CASCADE;

ALTER TABLE user_collections 
ADD CONSTRAINT fk_user_collections_collection 
FOREIGN KEY (collection_id) 
REFERENCES collections(id) 
ON DELETE CASCADE;
                        

8. 索引设计

针对《葫芦生》业务场景设计的高性能索引策略，支持地理位置查询、用户交互等核心功能。

🗺️ 地理位置索引

🌍 空间索引

使用MySQL空间索引优化地理位置查询，支持快速范围搜索

-- 地理位置空间索引
CREATE SPATIAL INDEX idx_location_spatial 
ON activity_locations (POINT(longitude, latitude));
                        

📍 复合地理索引

传统复合索引作为空间索引的补充，提升查询兼容性

-- 复合地理索引
CREATE INDEX idx_location_lat_lng 
ON activity_locations (latitude, longitude);
                        

🎯 业务查询索引

👤 用户活动索引

优化用户交互历史查询，支持时间范围和分页查询

-- 用户活动查询索引
CREATE INDEX idx_user_activity_time 
ON interactions (user_id, created_at);

-- 用户完成状态索引
CREATE INDEX idx_user_completion 
ON interactions (user_id, completion_status, created_at);
                        

📚 篇章活动索引

优化篇章内活动查询，支持状态筛选和类型分组

-- 篇章活动查询索引
CREATE INDEX idx_chapter_activity_status 
ON activities (chapter_id, status, activity_type);

-- 篇章NPC索引
CREATE INDEX idx_chapter_npc_status 
ON npcs (chapter_id, status);
                        

🎭 用户葫芦索引

优化用户葫芦数据查询，支持激活状态和属性排序

-- 用户葫芦查询索引
CREATE INDEX idx_user_gourd_active 
ON user_gourds (user_id, is_active);

-- 葫芦等级索引
CREATE INDEX idx_gourd_level_exp 
ON user_gourds (level, experience);
                        

🎲 交互系统索引

优化三模块交互查询，支持类型筛选和NPC关联

-- 交互类型索引
CREATE INDEX idx_interaction_type_status 
ON interactions (interaction_type, completion_status);

-- NPC问题索引
CREATE INDEX idx_npc_question_order 
ON dilemma_questions (npc_id, question_order, status);
                        

9. 性能优化

针对AR+LBS高并发场景的全方位性能优化方案，确保用户体验流畅。

🏗️ 架构优化策略

📊 交互记录分表

按月分表存储用户交互记录，提高查询性能

-- 按月分表示例
CREATE TABLE interactions_202501 LIKE interactions;
CREATE TABLE interactions_202502 LIKE interactions;

-- 分表路由逻辑
SELECT table_name FROM information_schema.tables 
WHERE table_name = CONCAT('interactions_', DATE_FORMAT(NOW(), '%Y%m'));
                        

🗂️ 用户数据分库

按用户ID哈希分库，支持水平扩展

-- 分库策略
-- 库1: user_id % 4 = 0
-- 库2: user_id % 4 = 1  
-- 库3: user_id % 4 = 2
-- 库4: user_id % 4 = 3

SELECT CONCAT('hulusheng_db_', (user_id % 4)) as target_db;
                        

🚀 缓存策略

热点数据Redis缓存，减少数据库压力

-- 缓存键设计
user:gourd:{user_id}           # 用户当前葫芦
chapter:activities:{chapter_id} # 篇章活动列表
npc:questions:{npc_id}         # NPC问题列表
location:activities:{lat}:{lng} # 地理位置活动

-- 缓存过期时间
SET user:gourd:123 "gourd_data" EX 3600    # 1小时
SET chapter:activities:1 "activities" EX 1800  # 30分钟
                        

📈 读写分离

主从复制，读写分离，提升并发能力

-- 读写分离配置
# 写操作 -> 主库
INSERT, UPDATE, DELETE -> master_db

# 读操作 -> 从库  
SELECT -> slave_db_1, slave_db_2

# 负载均衡
SELECT slave_db FROM (slave_db_1, slave_db_2) 
ORDER BY connection_count ASC LIMIT 1;
                        

⚡ 查询优化方案

🗺️ 地理位置查询优化

使用空间函数和索引，实现高效的LBS功能

-- 优化后的地理位置查询
SELECT a.*, al.latitude, al.longitude,
       ST_Distance_Sphere(
           POINT(al.longitude, al.latitude), 
           POINT(?, ?)
       ) as distance
FROM activities a 
JOIN activity_locations al ON a.id = al.activity_id 
WHERE ST_Distance_Sphere(
    POINT(al.longitude, al.latitude), 
    POINT(?, ?)
) <= al.radius 
AND a.status = 1
ORDER BY distance ASC
LIMIT 20;
                        

🎭 用户成长数据优化

优化用户葫芦数据查询，减少JOIN操作

-- 用户葫芦数据查询优化
SELECT ug.*, g.name as gourd_name,
       g.base_strategy, g.base_warfare,
       (ug.current_strategy - g.base_strategy) as strategy_growth
FROM user_gourds ug 
JOIN gourds g ON ug.gourd_id = g.id 
WHERE ug.user_id = ? AND ug.is_active = 1;

-- 使用索引提示
SELECT /*+ USE INDEX(idx_user_gourd_active) */ 
       ug.* FROM user_gourds ug 
WHERE ug.user_id = ? AND ug.is_active = 1;
                        

📚 篇章进度查询优化

优化篇章完成度统计，使用子查询避免大表JOIN

-- 篇章进度查询优化
SELECT c.*,
       COALESCE(progress.completed_interactions, 0) as completed_count,
       COALESCE(progress.total_npcs, 0) as total_npcs
FROM chapters c 
LEFT JOIN (
    SELECT n.chapter_id,
           COUNT(DISTINCT n.id) as total_npcs,
           COUNT(DISTINCT i.npc_id) as completed_interactions
    FROM npcs n 
    LEFT JOIN interactions i ON n.id = i.npc_id 
        AND i.user_id = ? AND i.completion_status = 1
    WHERE n.status = 1
    GROUP BY n.chapter_id
) progress ON c.id = progress.chapter_id
WHERE c.status = 1 
ORDER BY c.display_order;
                        

🎯 交互系统查询优化

优化三模块交互查询，支持智能推荐和状态管理

-- 智能NPC推荐优化
SELECT n.*, 
       interaction_stats.interaction_count,
       interaction_stats.last_interaction
FROM npcs n
LEFT JOIN (
    SELECT npc_id, 
           COUNT(*) as interaction_count,
           MAX(created_at) as last_interaction
    FROM interactions 
    WHERE user_id = ?
    GROUP BY npc_id
) interaction_stats ON n.id = interaction_stats.npc_id
WHERE n.chapter_id = ? AND n.status = 1
AND COALESCE(interaction_stats.interaction_count, 0) < n.interaction_count
ORDER BY interaction_stats.interaction_count ASC, 
         RAND() 
LIMIT 1;
                        