校园墙微信小程序数据库设计文档

一、总体设计概述

本数据库设计服务于校园墙微信小程序，涵盖用户管理、内容发布、社交互动、教务系统对接等核心模块。采用MySQL/MariaDB存储，遵循第三范式（3NF）设计原则，同时结合实际业务需求进行适度反规范化优化。

二、核心表结构说明

1. 用户体系模块

1.1 用户主表 (user)

优化建议：

• 将id改为BIGINT自增主键，openid设唯一索引
• phone字段使用VARCHAR(11)并添加正则校验

1.2 学生绑定表 (user_student)

2. 内容发布模块

2.1 动态表 (status)

优化建议：

• 增加status_type字段区分普通/置顶动态
• 添加location字段存储地理信息

2.2 评论表 (comments)

3. 二手交易模块 (shop)

优化建议：

• 增加category字段分类（书籍/数码等）
• 添加view_count字段记录浏览数

三、关键关系模型

1. ER图概要

erDiagram
    USER ||--o{ STATUS : "发布"
    USER ||--o{ COMMENTS : "评论"
    STATUS ||--o{ COMMENTS : "包含"
    USER ||--o{ SHOP : "出售"
    USER ||--o{ CHAT_MESSAGE : "发送"
    SHOP }|--|| USER_STUDENT : "卖家认证"

2. 关联关系说明

四、性能优化方案

1. 索引策略优化

2. 分表策略

• status表按月分表：status_202308
• comments表按status_id哈希分表

3. 字段优化

五、数据完整性方案

1. 外键约束

ALTER TABLE shop 
ADD CONSTRAINT fk_shop_user
FOREIGN KEY (seller_openid) 
REFERENCES user(openid)
ON DELETE CASCADE;

2. 检查约束

ALTER TABLE user_student
ADD CONSTRAINT chk_studentId
CHECK (studentId REGEXP '^[0-9]{10}$');

3. 触发器示例

CREATE TRIGGER trg_status_count 
AFTER INSERT ON status
FOR EACH ROW
UPDATE user_stat 
SET status_count = status_count + 1 
WHERE openid = NEW.openid;

六、安全设计方案

1. 敏感数据处理

-- 手机号加密存储
ALTER TABLE user 
MODIFY COLUMN phone 
VARBINARY(128) COMMENT 'AES加密存储';

2. 审计日志

CREATE TABLE audit_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(40),
    action VARCHAR(50),
    detail TEXT,
    ip_address VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

3. 权限控制

-- 创建视图限制数据访问
CREATE VIEW v_public_user AS
SELECT id, nickName, avatarUrl 
FROM user;

七、扩展性设计

1. 预留字段

ALTER TABLE status 
ADD COLUMN ext_data JSON COMMENT '扩展字段';

2. 版本管理

CREATE TABLE schema_version (
    version VARCHAR(20) PRIMARY KEY,
    applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

八、运维监控建议

1. 慢查询日志

# my.cnf配置
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

2. 监控指标

• 表空间使用率
• 索引命中率（Handler_read_key）
• 线程缓存命中率（Threads_cached）

附：数据库部署建议

1. 生产环境配置主从复制
2. 定期执行pt-online-schema-change进行无锁表结构变更
3. 使用Percona Monitoring进行实时监控
4. 重要表设置自动分区（按时间范围）