数据库设计规范

1. 概述

本文档定义了项目数据库设计的通用规范，适用于所有项目。规范基于阿里Java开发规范，结合最佳实践，确保数据库设计的一致性、可维护性和高性能。

2. 表结构设计规范

2.1 主键设计

• 主键类型：优先使用自增BIGINT主键，避免使用UUID
• 命名规范：主键字段统一命名为id
• 注释要求：主键字段必须添加注释说明

2.2 字段设计

• 数据类型：选择合适的数据类型，避免过度设计
• 默认值：所有字段必须设置合理的默认值
• 非空约束：关键业务字段必须设置NOT NULL约束
• 字段命名：使用蛇形命名法，如user_name、created_at

2.3 外键约束

• 禁止使用外键：数据库层面不使用外键和级联操作
• 关系维护：所有关系维护在应用层实现
• 数据一致性：通过业务逻辑确保数据完整性

3. 索引设计规范

3.1 索引设计原则

1. 主键索引：所有表的主键自动创建聚簇索引
2. 唯一索引：频繁作为查询条件的唯一字段建唯一索引
3. 普通索引：普通查询字段建普通索引
4. 组合索引：组合索引需让关键查询形成索引覆盖
5. 避免索引滥用：避免创建过多索引，减少写入开销
6. 索引命名规范：索引名以idx_为前缀，后跟表名和字段名

3.2 高频查询索引

-- 用户表索引
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_dept_id ON users(dept_id);
CREATE INDEX idx_users_status ON users(status);

-- 业务表通用索引
CREATE INDEX idx_table_user_id ON table_name(user_id);
CREATE INDEX idx_table_status ON table_name(status);
CREATE INDEX idx_table_created_at ON table_name(created_at);

3.3 组合索引设计

-- 组合索引示例：用户状态查询
CREATE INDEX idx_users_dept_status ON users(dept_id, status);

-- 组合索引示例：业务列表查询
CREATE INDEX idx_table_user_status ON table_name(user_id, status);

3.4 索引使用注意事项

• 避免仅用组合主键的一部分作为查询条件
• 索引字段长度不宜过长，避免索引文件过大
• 定期分析索引使用情况，删除无效索引
• 大表索引创建需在业务低峰期进行

4. 数据完整性与最佳实践

4.1 阿里Java规范要点

• 无外键约束：数据库层面不使用外键和级联
• 不严格遵循3NF：允许通过业务字段冗余减少表关联
• 优先逻辑删除：所有数据删除操作使用逻辑删除
• 索引设计优化：为逻辑删除字段、常用查询条件创建合适的索引
• 业务字段冗余：关键业务信息进行适当冗余，避免复杂关联查询

4.2 数据约束

• 唯一约束：业务实体表的关键字段设置唯一约束
• 非空约束：关键业务字段设置NOT NULL约束
• 默认值设置：状态字段和时间戳字段设置默认值

4.3 逻辑删除

• 所有业务表都实现逻辑删除机制（is_deleted/is_active字段）
• 逻辑删除字段创建索引，优化查询性能
• 应用层统一处理逻辑删除数据的过滤和恢复

5. 扩展性设计

5.1 JSON扩展字段

所有主要表都包含ext_data_json字段，支持以下扩展场景：

• 动态字段：无需修改表结构即可添加新字段
• 配置信息：存储业务配置和参数
• 临时数据：存储计算过程中的临时数据
• 第三方集成：存储外部系统的数据映射

5.2 业务字段冗余

为提高查询性能，减少复杂关联，合理引入业务字段冗余：

• 关键业务信息适当冗余，避免多表关联
• 冗余字段定期同步，确保数据一致性
• 常用查询场景优先考虑性能，适当牺牲部分存储冗余

5.3 版本管理

• 流程定义表包含version字段，支持流程版本管理
• 历史记录表完整记录数据变更历史
• 时间戳字段支持数据的时间维度分析

6. 性能优化考虑

6.1 数据类型优化

• 使用合适的数据类型，如DECIMAL处理金额、JSON存储结构化数据
• 字符串字段根据实际需求设置合理的长度
• 时间字段使用DATETIME类型，支持毫秒级精度

6.2 索引优化

• 为常用查询条件创建复合索引
• 为文本搜索字段创建全文索引
• 为逻辑删除标记字段创建索引
• 定期分析和优化索引使用情况
• 避免创建过多索引，减少写入开销

6.3 查询优化

• 避免N+1查询问题，使用JOIN和预加载
• 使用分页查询处理大量数据
• 使用覆盖索引减少回表查询
• 合理使用缓存减少数据库压力

6.4 数据归档策略

• 历史数据定期归档，保持活跃数据集的性能
• 大表分区策略，按时间或业务维度分区
• 冷热数据分离，不同存储介质存储

7. SQL与运维规范

7.1 SQL编写规范

7.1.1 禁止使用的语法

• 禁止使用游标：避免逐行执行拖慢性能，使用集合操作替代
• 禁止使用低效函数：避免使用会导致全表扫描的低效函数
• 禁止使用NOT、!=、%前缀模糊查询：这些语法会导致全表扫描
• 禁止使用SELECT *：明确指定需要的字段，避免不必要的字段查询
• 禁止使用子查询：优先使用JOIN替代子查询，提高查询性能

7.1.2 事务管理规范

• 事务边界明确：事务需明确边界，避免嵌套事务
• 事务长度控制：事务执行时间不宜过长，避免锁等待
• 事务隔离级别：根据业务需求合理设置事务隔离级别
• 异常处理：事务中必须有完整的异常处理机制

7.1.3 查询优化规范

• 使用EXPLAIN分析：复杂查询必须使用EXPLAIN分析执行计划
• 避免隐式类型转换：确保查询条件与字段类型一致
• 合理使用索引：确保查询条件能够命中索引
• 分页查询优化：大数据量分页使用游标分页或基于主键的分页

7.2 运维安全规范

7.2.1 连接安全

• 禁止明文密码：禁止将数据库密码直接写在源代码中
• 连接加密：数据库连接建议使用SSL/TLS加密
• 连接池配置：合理配置连接池参数，避免连接泄露
• 访问控制：严格控制数据库访问权限，遵循最小权限原则

7.2.2 监控与告警

• 性能监控：监控数据库性能指标，如QPS、连接数、慢查询等
• 容量监控：监控数据库存储容量，及时扩容
• 错误告警：设置数据库错误告警，及时发现异常
• 备份监控：监控备份任务执行状态，确保数据安全

7.2.3 变更管理

• DDL变更审核：所有DDL变更必须经过审核和测试
• 变更窗口：数据库变更在业务低峰期进行
• 回滚预案：每次变更必须有完整的回滚预案
• 版本控制：数据库Schema变更纳入版本控制管理

8. 安全性设计

8.1 数据加密

• 敏感字段如password_hash使用高强度加密
• 支持字段级加密，如手机号、邮箱等个人信息
• 支持传输层加密，确保数据传输安全

8.2 访问控制

• 基于角色的访问控制（RBAC）模型
• 支持行级安全（RLS）控制
• 支持数据脱敏和访问审计

8.3 备份策略

• 定期全量备份和增量备份
• 支持点时间恢复（PITR）
• 异地备份和容灾策略

9. 实施指南

9.1 新项目实施

• 新项目必须遵循本规范进行数据库设计
• 设计阶段需进行规范评审
• 开发阶段需进行代码审查确保规范落实

9.2 现有项目改造

• 现有项目逐步改造，优先改造性能瓶颈表
• 改造过程需有完整的测试和回滚方案
• 改造完成后需进行性能对比测试

9.3 规范维护

• 规范定期更新，适应技术发展和业务变化
• 收集项目实践反馈，持续优化规范内容
• 建立规范执行检查机制，确保规范落地

---

版本历史

• v1.0 (2024-01-01)：初始版本，基于阿里Java规范制定

适用项目

• FlowMind 智能审批系统
• 淘票票 票务系统
• 邮院通 校园管理系统
• 其他Java后端项目