输入与输出

输入：

1. 脑图+功能清单
2. 产品原型图
3. 《需求规格说明书》或《产品需求文档》（PRD）
4. 《XXX项目设计分工》干特图
5. 《概要设计文档》

过程活动：做数据库设计

参与者：项目经理、团队主力

输出：《数据库设计文档》

干货

1. 数据库设计是系统开发的基础环节，不论如何敏捷，都要做好数据库设计，在数据库上多下功夫都不为过。管理好核心领域的“数据模型”的太重要了，直接影响性能、可维护性和扩展性。
2. 由其是给旧有的核心表加字段时要慎重考虑，是加字段还是加扩展表？我就遇到过核心表有130个字段，极其难以维护，字段定义也不清晰，单单价格类的字段就有：批发价、服务站售价、采购价、维修厂采购指导价、会员中心售价、商城指导价、内网价、外网价、划线价，真是搞清什么时候用哪个字段。
3. 接手了一套烂表，由于业务在跑旧表又不好改动，如何使用触发器+视图重新构建出各种业务领域的“数据模型”，都是数据库设计的工作内容。

数据库设计的基本步骤

1. 需求分析
   数据库设计的第一步是对业务需求进行深入分析，明确系统需要存储哪些数据以及这些数据之间的关系。

   • 目标：确定表结构、字段及其约束条件。
   • 方法：与业务方沟通，梳理业务流程，识别核心实体和属性。

2. 概念设计
   在这一阶段，通过绘制ER图（实体-关系图）来定义实体及其之间的关系。

   • 工具：可以使用PowerDesigner、Visio等工具绘制ER图。
   • 输出：明确每个实体的属性、主键、外键以及它们之间的关联。

3. 逻辑设计
   将概念设计转化为具体的数据库模型，定义表结构、字段类型、索引等。

   • 重点：确保字段命名规范、数据类型合理、索引设计高效。
   • 输出：生成初步的建表语句。

4. 物理设计
   根据硬件环境和性能要求，对数据库进行优化设计，包括存储引擎选择、分库分表策略等。

   • 重点：关注性能瓶颈、冷热数据分离、分布式架构等。

5. 实施与优化

   • 实施：根据设计文档创建数据库表，导入初始数据。
   • 优化：在实际运行中持续监控性能，调整索引、SQL语句等。

数据库设计的核心原则与规范

1. 字符集与排序规则

• 统一字符集：推荐使用utf8mb4字符集和utf8mb4_bin排序规则，支持完整的Unicode字符（包括emoji表情）并区分大小写。
• 避免混用：同一个数据库实例内应保持字符集和排序规则一致，以防止性能问题和大小写判断错误。

2. 命名规则

• 避免关键字：不使用MySQL关键字作为对象名称（如count、user、type等）。
• 见名知义：命名应语义化，关键词之间用下划线分割（如user_info），避免无意义字符。

3. 存储引擎

• 推荐InnoDB：对于MySQL 5.0及以上版本，优先使用InnoDB存储引擎，因其支持事务、行级锁、更好的数据恢复能力和并发性能。
• 避免MyISAM：MyISAM不支持事务，在高并发场景下性能较差。

4. 表结构设计

• 新增数据表：
  • 字段名称：使用英文单词或缩写，避免无意义字符。
  • 数据类型：选择占用空间较小的类型（如INT优于VARCHAR）。
  • 约束条件：设置主键、唯一性、非空等约束。
  • 默认值：为日期字段设置当前时间，状态字段设置默认值。
  • 是否索引：根据查询需求决定是否建立索引。
• 修改数据表：评估对现有数据的影响，尤其是字段删除或类型变更。

5. 主键与自增列

• 每张表必须设置一个自增列作为主键（推荐使用BIGINT UNSIGNED），提升插入性能并降低二级索引的空间占用。
• 自增列有助于减少页碎片，提高内存命中率。

6. 字段设计规范

• 字段数量：单表字段不宜超过50个。
• 字段长度：每行记录的字段长度总和不应超过8000字节。
• 数据类型选择：
  • 尽量使用简单数据类型（如INT优于CHAR，TINYINT优于INT）。
  • 使用DECIMAL存储金额数据，避免浮点数精度问题。
  • 定长字段使用CHAR，不定长字段使用VARCHAR并设置合理长度。
  • 非负数字段建议使用UNSIGNED。
• 时间字段：推荐使用DATETIME(3)（精确到毫秒）而非TIMESTAMP。

7. 索引设计

• 索引字段：需设置为NOT NULL，以提高查询性能。
• 覆盖索引：尽量减少查询字段，尽可能使用覆盖索引查询，减少IO操作。
• 避免冗余索引：只在必要时建立索引，避免过多索引影响写入性能。
• 多表连接：确保关联字段有索引，驱动表应选择过滤性较强的表。

8. 约束设计

• 主键约束：确保每张表都有唯一的标识。
• 非空约束：关键字段应设置为NOT NULL。
• 唯一约束：防止重复数据。
• 默认值：为常见字段（如日期、状态）设置默认值。

9. 公共字段建议

• 时间戳字段：create_date（创建时间）、update_date（更新时间）。
• 操作者字段：create_by（创建者）、update_by（更新者）。
• 删除标记：del_flag（逻辑删除标记）。
• 备注信息：remarks（备注说明）。

10. 查询优化

• 避免SELECT *：仅查询需要的字段，使用覆盖索引。
• 合理分页：对于大数据量分页，使用主键范围定位代替LIMIT偏移量。
• 慎用ORDER BY RAND()：该操作会消耗大量CPU资源，影响性能。
• 统计记录数：使用COUNT(字段名)而非COUNT(*)，忽略NULL值。

11. DML语句优化

• INSERT：显式指定列名，避免因字段变更导致程序报错。
• DELETE/TRUNCATE：清空全表数据时，优先使用TRUNCATE而非DELETE。
• 批量操作：对大批量数据操作加LIMIT，防止长时间锁表。

12. 冷热数据分离与分库分表

• 冷热数据分离：将访问频率低的大字段拆分到单独的表中存储，提升缓存命中率。
• 分库分表：单表数据量超过1000万时，开始需考虑分库分表。

13. 实体关系建模

• ER图：使用ER图可视化数据关系，明确核心业务实体及其关系（1:1、1:N、M:N）。
• 范式与反范式：遵循第三范式（3NF）消除冗余，合理使用反范式优化（如订单总金额冗余）。
• 文档管理：维护数据字典，记录业务含义变更历史。