数据库设计指南-优快云博客

1. 设计数据库之前

1. 在物理实践之前进行逻辑设计
在深入物理设计之前要先进行逻辑设计。随着大量的 CASE 工具不断涌现出来，你的设计也可以达到相当高的逻辑水准，你通常可以从整体上更好地了解数据库设计所需要的方方面面。

2. 创建数据字典和 ER 图表

一定要花点时间创建 ER 图表和数据字典。其中至少应该包含每个字段的数据类型和在每个表内的主外键。创建 ER 图表和数据字典确实有点费时但对其他开发人员要了解整个设计却是完全必要的。有一份诸如 ER 图表等最新文档其重要性如何强调都不过分，这对表明表之间关系很有用，而数据字典则说明了每个字段的用途以及任何可能存在的别名。

3. 对于一个存储设计，必须充分理解客户的需求，考虑业务特点，并掌握以下信息：

1. 数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节；

2. 数据项：是否有大字段，那些字段的值是否经常被更新；

3. 预计大表及相关联的SQL，每天总的执行量在何数量级？

4. 表中的数据：更新为主的业务还是查询为主的业务

5. 打算采用什么数据库物理服务器，以及数据库服务器架构？

6. 并发如何？

7. 存储引擎选择InnoDB还是MyISAM？

8. 数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等；

9. 数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中；

10. SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？

2. 设计数据表和字段

1. 检查可能的各种变化

我在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说，姓氏就是如此（注意是西方人的姓氏，比如女性结婚后从夫姓等）。所以，在建立系统存储客户信息时，我倾向于在单独的一个数据表里存储姓氏字段，而且还附加起始日和终止日等字段，这样就可以跟踪这一数据条目的变化。

数据库设计时就要考虑效率和优化的问题。一开始就要分析哪些表会存储较多的数据量，对于数据量较大的表的设计往往是粗粒度的，也会冗余一些必要的字段，已达到尽量用最少的表、最弱的表关系去存储海量的数据。并且在设计表时，一般都会对主键建立聚集索引，含有大数据量的表更是要建立索引以提供查询性能。对于含有计算、数据交互、统计这类需求时，还要考虑是否有必要采用存储过程。

2. 根据具体的业务场景选取最合适的存储引擎，设计最合理的索引。

3. 字段名的设计
有意义，好记忆；提房大小写混用；避免特殊字符，保留词；

假如你给每个表的列[字段]名都采用统一的前缀，那么在编写 SQL 表达式的时候会得到大大的简化。Customer 表的前缀是 cu_，所以该表内的子段名如下：cu_name_id，cu_surname。Order 表的前缀是 or_ 。

4. 冗余字段

像“创建时间”、“修改时间”、“备注”、“操作用户IP”和一些用于其他需求（如统计）的字段等，在每张表中必须都要有，不是说只有系统中用到的数据才会存到数据库中，一些冗余字段是为了便于日后维护、分析、拓展而添加的，这点是非常重要的，比如黑客攻击，篡改了数据，我们便就可以根据修改时间和操作用户IP来查找定位。

商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性。

5. 不活跃或者不采用的标识符

增加一个字段表示所在记录是否在业务中不再活跃挺或已经被删除。

6. 给每张表一个数字类型自增的ID；

7. 仔细选择数据存储类型（如smallint，tinyint）， 尽量使数据紧凑，又不影响扩展性。

如果是文本字段长度要留足余量；如果是bool型字段，使用 bit 作为布尔字段，使用整数或者varchar是浪费。同时，这类字段应该以“Is”开头。

8. 尽量避免null

应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

9. 采用视图

为了在你的数据库和你的应用程序代码之间提供另一层抽象，你可以为你的应用程序建立专门的视图而不必非要应用程序直接访问数据表。这样做还等于在处理数据库变更时给你提供了更多的自由。

10. 存在过期概念的表，在其设计之初就必须有过期机制，且有明确的过期时间。过期数据必须迁移至历史表中。

3. 各种小技巧

1. 文档、文档、文档

对所有的快捷方式、命名规范、限制和函数都要编制文档。采用给表、列[字段]、触发器等加注释的数据库工具。是的，这有点费事，但从长远来看，这样做对开发、支持和跟踪修改非常有用。取决于你使用的数据库系统，可能有一些软件会给你一些供你很快上手的文档。

2. 使用常用英语（或者其他任何语言）而不要使用编码

为什么我们经常采用编码（比如 9935A 可能是'青岛啤酒'的供应代码，4XF788-Q 可能是帐目编码）？理由很多。但是用户通常都用英语进行思考而不是编码。工作 5 年的会计或许知道 4XF788-Q 是什么东西，但新来的可就不一定了。在创建下拉菜单、列表、报表时最好按照英语名排序。假如你需要编码，那你可以在编码旁附上用户知道的英语。

3. 保存常用信息

让一个表专门存放一般数据库信息非常有用。我常在这个表里存放数据库当前版本、最近检查/修复（对 FoxPro）、关联设计文档的名称、客户等信息。这样可以实现一种简单机制跟踪数据库，当客户抱怨他们的数据库没有达到希望的要求而与你联系时，这样做对非客户机/服务器环境特别有用。

4. 测试、测试、反复测试

5. 给数据保有和恢复制定计划

考虑数据保有策略并包含在设计过程中，预先设计你的数据恢复过程。采用可以发布给用户/开发人员的数据字典实现方便的数据识别同时保证对数据源文档化。编写在线更新来"更新查询"供以后万一数据丢失可以重新处理更新。

6. 分布式数据系统保证数据完整性

对分布式系统而言，在你决定是否在各个站点复制所有数据还是把数据保存在一个地方之前应该估计一下未来 5 年或者 10 年的数据量。当你把数据传送到其他站点的时候，最好在数据库字段中设置一些标记。在目的站点收到你的数据之后更新你的标记。为了进行这种数据传输，请写下你自己的批处理或者调度程序以特定时间间隔运行而不要让用户在每天的工作后传输数据。本地拷贝你的维护数据，比如计算常数和利息率等，设置版本号保证数据在每个站点都完全一致。