背景介绍
数据库设计是后端开发的基础，一个好的表结构设计可以为后续的业务发展打下良好基础。反之，不合理的设计会导致性能问题、数据冗余和维护困难。
基于多个项目的经验，总结了以下数据库设计原则和最佳实践。
一、字段类型选择
1. 整数类型选择：
| 类型 | 范围 | 使用场景 |
|---|---|---|
| TINYINT | -128 到 127 | 状态字段、布尔值 |
| SMALLINT | -32768 到 32767 | 数量较少的枚举值 |
| INT | -21亿 到 21亿 | 主键ID、大部分数值字段 |
| BIGINT | -9e18 到 9e18 | 超大数值、雪花ID |
2. 字符串类型选择：
- VARCHAR(n)：可变长度字符串，n 为最大长度，适合存储姓名、标题等
- CHAR(n)：固定长度字符串，适合存储身份证号、手机号等固定长度数据
- TEXT：大文本，最大 65KB，适合存储文章内容、描述等
- LONGTEXT：超大文本，最大 4GB，谨慎使用
3. 时间类型选择：
- DATETIME：范围 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
- TIMESTAMP：范围 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'
- DATE：仅日期，无时间部分
建议：使用 TIMESTAMP 存储需要时区转换的时间，使用 DATETIME 存储业务时间。
二、索引设计原则
1. 索引类型：
- B-Tree 索引：最常用，适合等值查询和范围查询
- Hash 索引：仅适合等值查询，不支持范围查询
- 全文索引：适合全文搜索
2. 最左前缀原则：
复合索引 (a, b, c) 可以加速以下查询：
- WHERE a = ?
- WHERE a = ? AND b = ?
- WHERE a = ? AND b = ? AND c = ?
但不能加速 WHERE b = ? 或 WHERE b = ? AND c = ?
3. 索引使用场景：
- WHERE 条件中的字段
- JOIN 条件中的字段
- ORDER BY / GROUP BY 字段
4. 索引注意事项：
- 索引不是越多越好，会影响写入性能
- 定期检查并删除冗余索引
- 避免在高基数字段上创建索引（如密码哈希）
三、表结构规范化
数据库范式：
- 第一范式：列不可再分
- 第二范式：非主键列完全依赖主键
- 第三范式：非主键列不依赖其他非主键列
反范式优化：
- 冗余字段：在查询频繁的表中增加冗余字段减少 JOIN
- 合并表：对于关联紧密的小表可以考虑合并
- 数据预计算：提前计算并存储统计结果
四、常见设计错误
- 使用 NULL 作为默认值：NULL 会使索引失效，建议使用空字符串或 0
- 过度使用 VARCHAR(255)：根据实际需求设置合理长度
- 谨慎使用外键约束：根据业务复杂度和维护方式权衡完整性与扩展性
- 字符集选择不当：统一使用 utf8mb4 支持 emoji
- 没有主键：每个表都应该有主键
- 使用自增ID作为业务主键：可能导致数据泄露，建议使用业务无关的ID
五、分表分库策略
水平分表：
- 按时间分表：适合日志、订单等时间序列数据
- 按ID分表：使用取模或范围分片
垂直分表：
- 将大表拆分成多个小表
- 将不常用的字段分离出去
六、设计检查清单
- 每个表是否有主键？
- 字段类型是否合理？
- 是否有必要的索引？
- 是否避免了数据冗余？
- 是否考虑了未来的扩展性？
- 字符集是否统一？
总结
数据库设计是一个权衡的过程，需要在规范化和性能之间找到平衡。
- 前期规划：充分理解业务需求
- 遵循原则：合理选择字段类型和索引
- 持续优化：定期审查和优化表结构
一个好的数据库设计可以为项目节省大量的后期维护成本。