数据库设计常见错误

为何要讨论错误？

优秀数据库设计的艺术就像游泳。入手相对容易，精通则很困难。如果你想学习设计数据库，一定得有一些理论背景，比如关于数据库设计范式和事务隔离级别的知识。但你还应该尽可能地多加练习，因为可悲的事实就是，我们在犯错中学习得更多。

本文中，通过展示在设计数据库时常犯的一些错误，我们尝试把学习数据库设计变得容易一点。

注意，我们假定读者了解数据库范式并知道一点关系数据库的基础知识，因而不会去讨论数据库规范化。只要有可能，文中所涵盖的主题都将使用 Vertabelo 建模和实例来说明。

本文涵盖了设计数据库的各个方面，但着重于Web应用，因此有些例子可能是特定于web应用程序的。

模型设计

假设我们想要为一个在线书城设计数据库。该系统应当允许用户执行以下活动：

• 通过书名、描述和作者信息浏览与搜索书籍，
• 阅读后对书籍添加评论和评级，
• 定购书籍，
• 查看订单处理的状态。

那么最开始的数据库模型可能如下所示：

 

为了测试该模型，我们使用Vertabelo为其生成SQL，并且在PostgreSQL RDBMS中创建一个新的数据库。

该数据库有8张表，其中没有数据。我们已经往里面填充了一些人工生成的测试数据。现在数据库里包含了一些示范数据，准备好开始模型检查了，包括识别那些现在不可见但将来在真实用户使用时会出现的潜在问题。

1 —— 使用无效的名称

你可以在上面的模型中看到我们用“order”命名了一张表。不过，或许你还记得，“order”在SQL中是保留字! 因此如果你试图发起一个SQL查询：

MySQL

1	SELECT * FROM ORDER ORDER BY ID

数据库管理系统将会抗议。很幸运，在PostgreSQL中用双引号把表名包裹起来就行了，语句仍可以执行：

MySQL

1	SELECT * FROM "order" ORDER BY ID

等等，可是这里的“order”是小写！

没错，这值得深究。如果你在SQL中用双引号把什么包了起来，它就变成分隔标识符，大多数数据库将以区分大小写的方式解释它。由于“order” 是SQL中的保留字，Vertabelo生成SQL会自动把order用双引号包起来：

MySQL

1 2 3 4 5 6

CREATE TABLE "order" (     id int    NOT NULL,     customer_id int    NOT NULL,     order_status_id int    NOT NULL,      CONSTRAINT order_pk PRIMARY KEY (id) );

但是由于标识符被双引号包裹且是小写，表名仍然是小写。现在如果你希望事情变得更复杂，我可以创建另一个表，这次把它名为ORDER（大写），PostgreSQL不会检测到命名冲突：

MySQL

1 2 3 4 5 6

CREATE TABLE "ORDER" (     id int    NOT NULL,     customer_id int    NOT NULL,     order_status_id int    NOT NULL,      CONSTRAINT order_pk2 PRIMARY KEY (id) );

如果一个标识符没有被双引号包裹，它就被称作“普通标识符”，在被使用前自动被转成大写——这是SQL 92标准所要求的。但是标识符如果被双引号包裹

——就被称作“分隔标识符”——要求被保持原样。

底线就是——不要使用关键字来当做对象名称。永远不要。

你知道Oracle中名称长度上限是30个字符吗？

关于给表以及数据库其他元素命好名——这里命好名的意思不仅是“不与SQL关键字冲突”，还包括是自解释的且容易记住——这一点常常被严重低估。在一个小型数据库中，比如我们这个，命名其实并不是件非常重要的事。但是当你的数据库增长到100、200或者500张表，你就会知道在项目的生命周期中为保证模型的可维护性，一致和直观的命名至关重要。

记住你不光是给表和列命名，还包括索引、约束和外键。你应当建立命名约定来给这些数据库对象命名。记住名字的长度也是有限制的。如果你给索引命名太长，数据库也会抗议。

提示：

• 让你的数据库中的名字：
  • 尽可能短，
  • 直观，尽可能正确和具有描述性，
  • 保持一致性；
• 避免使用SQL和数据库引擎特定的关键字作为名字；
• 建立命名约定；

以下是把order表重命名为purchase后的模型：

 

模型中的改变如下：

2 ——列的宽度不足

让我们进一步来看这个模型。如我们所看到的，在book_comment表中，comment列的类型是1000个以内的字符。这意味着什么？

假设这个字段将是GUI（用户只能输入非格式化的评论）中的纯文本，那么它简单地意味着该字段可以存储最多1000个文本字符。如果是这样的话——这里没有错误。

但是如果这个字段允许一些格式化的动作，比如bbcode或者HTML，那么用户实际上输入进去的字符数量是未知的。假如他们输入一个简单的评论，如下：

XHTML

1	I like that book !

那么它会只占用17个字符。然而如果他们使用粗体格式化它，像这样：

XHTML

1	I <b> like </b> that book !

这就需要24个字符的存储空间，而用户在GUI上只会看到17个。

因此如果书城的用户可以使用某种像所见即所得的编辑器来格式化评论内容，那么限制”comment”字段的大小是存在潜在危险的。因为当用户超过了最大评论长度（1000个原始HTML字符），他们在GUI上所看到的仍然会低于1000。这种情况下就应当修改类型为text而不要在数据库中限制长度了。

然而，当设置了文本字段的限制，你应当始终谨记文本的编码方式。

varchar(100)类型在PostgreSQL中代表100个字符，而在Oracle中代表100字节。

避免笼统地解释，我们来看一个例子。在Oracle中，varchar类型被限制到4000个字节，那么这就是一个强限制——没有任何方法可以超过它。因此如果你定义了一个列是varchar(3000 char)，那它意味着你可以存储3000个字符，但只有在它不会使用到磁盘上超过4000个字节的情况下。为何一个3000个字符的文本在磁盘上会超过4000个字节呢？英文字符的情况下是不会发生的，但是其它语言中就可能出现。举个例子，如果你尝试用中文的方式存储”mother”——母亲，且数据库使用UTF-8的方式编码，那么这个字符串会占用磁盘上2个字符但是6个字节。

BMP（Basic Multilingual Plane，基本多语言平面，Unicode零号平面）是一个字符集，支持用UTF-16让每个字符用2个字节进行编码。幸运地是，它覆盖了世界上大多数使用的字符。

注意，不同数据库对于可变长的字符和文本字段会有不同的限制。举些例子：

• 前面提到过，Oracle对varchar类型的列有4000个字节限制。
• Oracle将低于4KB的CLOB直接存储到表中，这种数据访问起来如同任何varchar列一样快。但大些的CLOB读取时就会耗时变长，因为它们存在表的外面。
• PostgreSQL允许一个未限制长度的varchar列存储甚至是千兆字节的字符串，且是默默地把字符串存到后台表，不会降低整个表的性能。

提示：

• 一般而言，考虑到安全和性能，数据库中限制文本列的长度是好的，但有时这个做法可能没有必要或者不方便；
• 不同的数据库对待文本限制可能会有差异；
• 使用英语以外的语言时永远记住编码。

下面是把book_comment的评论类型修改为text后的模型：

模型中修改的地方如下图:

3 ——没有恰当地添加索引

有一个说法是“伟大是实现的，而不是被赠与的”。这个说法同样可以用在性能上——通过精心设计数据库模型，优化数据库参数以及优化数据库应用查询来实现。当然这里我们关注的是模型设计。

在例子中，我们假定书城的GUI设计者决定在首页显示最新的30条评论。为了查询这些评论，我们将使用如下的语句：

MySQL

1	select comment, send_ts from book_comment order by send_ts desc limit 30;

这个查询运行起来有多快？在我的笔记本上花费不到70毫秒。但是如果我们希望应用能够按比例变化（在高负载下快速运行），需要在更大的数据上检测。所以我在book_comment表中插入了更多的记录。为此我将使用一个很长的单词列表，然后使用一个简单的Perl命令将其转成SQL。

现在我要把这个SQL导入到PostgreSQL数据库。一旦导入开始，我就会检测之前那个查询的执行时间。统计结果在如下的表格中：

如你所见，随着 book_comment 中行数的增加，要获取最新30行所花费的查询时间也在成比例地增加。为何耗费时间增长？我们看看这个查询计划：

MySQL

1 2 3 4 5 6 7

db= # explain select comment, send_ts from book_comment order by send_ts desc limit 30;                             QUERY PLAN                                      ------------------------------------------------------------------- Limit   (cost =28244.01..28244.09 rows =30 width =17)    - >   Sort   (cost =28244.01..29751.62 rows =603044 width =17)          Sort Key: send_ts          - >   Seq Scan on book_comment   (cost =0.00..10433.44 rows =603044 width =17)

这个查询计划告诉我们数据库如何处理查询及计算结果的大致时间成本。这里PostgreSQL告诉我们将进行“Seq Scan on book_comment”，这意味着它将逐个检查 book_comment 表的所有记录，以此对send_ts列的值进行排序。貌似PostgreSQL还没有聪明到在不去对所有的600,000条进行排序的条件下查询30个最新记录。

幸运地是，我们可以通过告知PostgreSQL根据send_ts进行排序并保存结果来帮助它。为此，我们先在该列上创建一个索引：

MySQL

1	create index book_comment_send_ts_idx on book_comment(send_ts);

现在我们的查询语句从600,000条记录中查询出最新30条所花费的时间又是67毫秒了。查询计划差别非常大：

MySQL

1 2 3 4 5

db= # explain select comment, send_ts from book_comment order by send_ts desc limit 30;                            QUERY PLAN                                                        ------------------------------------------------------------------ -- Limit   (cost =0.42..1.43 rows =30 width =17)    - >    Index Scan Backward using book_comment_send_ts_idx on book_comment   (cost =0.42..20465.77 rows =610667 width =17)

“Index Scan”指不是逐行扫描book_comment表，而是数据库会扫描我们刚刚创建的索引。估计查询成本小于1.43,低于之前的2.8万倍。

你遇到了性能问题？第一次尝试解决就应当是找到运行时间最长的查询，让你的数据库来解释它们，并且寻找全表扫描。如果你找到了，也许增加一些索引可以快速提升速度。

不过，数据库性能设计是一个庞大的主题，超出了本文的范围。
我们在如下提示中列出一些重要的方面。

提示：

• 经常检查运行时间长的查询，或许可以用上EXPLAIN功能；大多数现代数据库都有该功能；
• 在创建索引时：
  • 记住它们不会一直被用到；数据库如果计算出使用索引所耗费的时间长于全表扫描或其它操作时，将不会使用索引；
  • 记住使用索引带来的代价是——在被索引的表上INSERT和DELETE会变慢
  • 如果需要索引请考虑非默认类型的索引；如果你的索引工作得不是很好，请查阅数据库手册；
• 有时候你需要优化查询，而不是模型；
• 不是每一个性能问题都可以通过创建一个索引来解决；有很多其它解决性能问题的方式；
  • 各个应用层的缓存，
  • 调优数据库参数和缓冲区大小，
  • 调优数据库连接池大小或者线程池大小，
  • 调整数据库事务隔离级别，
  • 在夜间安排批量删除，避免不必要的锁表，
  • 其它等等。

在book_comment.send_ts列上带有索引的模型如下：

4 ——没有考虑到可能的数据量或流量

通常你可以得到有关可能的数据量的附加信息。如果你正在构建的系统是另一个已存在项目的迭代，你可以通过查看老系统的数据量来计算出系统数据的预期大小。

如果你的书城非常成功，purchase表的数据量可能会非常大。你卖得越多，purchase表里的数据行数增加越多。假如你事先知道这一点，你可以把当前已处理的订单与完成的订单分开。你可以用两个表：purchase表记录当前的订单，archived_purchase表记录完成的订单，而不是用一张单一的purchase表。因为当前的订单一直在被检索：它们的状态在被更新，由于客户经常查看订单的信息。另一方面，完成的订单只会被作为历史数据保存。它们很少被更新或者检索，所以可以在这张表上安排更长的访问时间。订单分离之后，经常使用的表能保持比较小，但我们仍然保存着所有数据。

类似地，你应当优化频繁更新的数据。想象一个系统的部分用户信息经常由另一个外部系统（例如，该外部系统计算同一类的奖励积分）更新。在我们的user表中也有其它信息，如他们的登陆账号、密码和全名。这些基本信息也经常被检索。频繁更新降低了获取用户基本信息的速度。最简单的解决方案就是把这些数据分离到两个表里面：一个记录基本信息（经常被读取），另一个记录奖励积分相关的信息（频繁被更新）。这样更新操作不会减缓读的操作。

分离频繁和不频繁使用的数据到多个表中不是处理大数据量的唯一方法。例如，如果你希望书的描述（description字段）非常长，你可以使用应用级别的缓存，这样你不用经常检索这个重量级的数据。书的描述很可能保持不变，所以这是一个很好的可被缓存的候选对象。

提示：

• 你的客户必须使用业务、领域特定的知识，预估预期你将处理的数据库中的数据量。
• 分离频繁更新和频繁读取的数据。
• 对重量级、更新少的数据考虑使用应用级别的缓存。

以下是修改后的书城模型：

5 ——忽略时区

如果书城是面向全世界的呢？客户来自世界各地并且使用不同的时区。管理时区的date和datetime字段算是跨国系统中一个重要的问题。

系统必须始终为用户呈现准确的日期和时间，最好是以他们自己的时区。

举例，特殊供应的过期时间（这是任何商城中最重要的功能）必须让所有用户理解一致。如果你只是说“促销于12月24日结束”，他们会假定是在自己时区的12月24日半夜12点结束。如果你是指自己所在时区的圣诞前夜午夜12点，你必须说“12月24日,23.59 UTC”(即无论你的时区是什么)。对于某些用户，它将是“December 24, 19.59”，对另外一些用户则是“December 25, 4.49”。用户必须看到以他们所在时区为准的促销时间。

在一个跨时区系统中日期列类型是不会有效存在的。它应当一直是一个timestamp类型。

当登录事件在跨时区系统中发生时，可以采取类似的方式。事件的时间应该总是以某个选中的时区为准的标准化方式记录的，例如UTC，因此你能够毫无疑问地将时间从老到新排序。

数据库必须与应用代码合作以备处理时区问题。各种数据库存储日期和时间的数据类型有所不同。某些类型存储时间时带有时区信息，而有些则没有。程序员应当在系统中开发标准化的组件来自动处理时区问题。

提示：

• 检查你的数据库中日期和时间数据类型的细节。SQL Server中Timestamp与PostgreSQL的timestamp完全不同。
• 用UTC的方式存储日期与时间。
• 处理好时区问题需要数据库和应用代码直接的合作。确保你理解了数据库驱动的细节。这里有相当多的陷阱。

6  ——缺少审计跟踪

如果有人删除或者修改了我们书城中的一些重要数据，可我们在3个月之后才发现，发生了什么事情？我认为我们遇到了严重的问题。

也许我们有3个月前的一个备份，所以可以恢复备份到一些新的数据库以访问到数据。此后我们将有一个契机来恢复这些数据避免损失。但是为完成这个过程，必须满足许多因素

• 我们需要拥有那个合适的备份——哪一个才是合适的？
• 我们必须成功寻找到数据，
• 我们必须能不费太大力气就恢复数据。

当我们最终恢复了数据（但确定这就是最正确的版本吗？），就面临第二个问题——谁干的？谁在三个月前毁掉了数据？他们的IP/用户名是多少？我们如何核实？为了确定这一点，我们需要：

• 至少保存3个月的系统访问日志——这不太有希望，它们或许可能已经被轮转替换了。
• 可以把删除数据的情况与访问日志中的某些URL关联起来。

这无疑会花费大量时间，而且没有多大成功的胜算。

我们的模型所缺失的，就是某种意义上的审计跟踪。有许多方式来达到这个目标：

• 数据库中的表可以有创建和更新时间戳，及所创建/修改行的用户标示。 完整的审计日志可以用触发器或者其它对正在使用的数据库管理系统有效的机制来实现。一些审计日志可以存储在单独的数据库以确保无法修改和删除，
• 数据能够防止数据丢失，通过：
  • 不删除它，而是打上一个被删除的标记，
  • 版本化修改。

按照惯例，保持黄金分割是最好的方式。你应当在数据安全和模型简易性中找到平衡。保存版本和记录事件使得数据库更复杂。忽略数据安全可能导致意外的数据丢失或者恢复丢失数据的高成本。

提示：

• 考虑哪个数据重要到需要跟踪修改/版本化，
• 考虑风险和成本之间的平衡；记住帕雷托定律指出大约80%的影响来自20%的原因；不要在不太可能的事故场景中保护你的数据，关注那些可能发生的场景。

这是对purchase和archived_purchase表加了基本审计跟踪功能的书城模型。

模型中的修改如下（以purchase表为例）：

7  ——忽略排序规则

最后的错误是一个棘手的问题，因为它只出现在一些系统中，主要是在多语种系统里。将它添加在这里，是因为我们经常遇到它，但它似乎并不广为人知。

通常来说，根据字母在字母表中的顺序，我们假定在一种语言中对单词排序与逐字排序一样容易。但是这里有两种陷阱：

• 首先，哪个字母表？如果我们的内容只有一种语言，那很显然，但是如果内容中有15到30种语言，该由哪一个字母表来决定顺序？
• 其次，当重音起作用时，逐字排序有时会有错误。

我们将在这个法文的简单SQL查询中举例说明：

MySQL

1 2 3 4 5 6 7

db= # select title from book where id between 1 and 4 order by title collate "POSIX"; title ------- cote coté côte côté

这是逐字排序的结果，从左到右。

但是这些单词是法语，所以这才是正确的：

MySQL

1 2 3 4 5 6 7

db= # select title from book where id between 1 and 4 order by title collate "en_GB"; title ------- cote côte coté côté

这两个结果不同，因为正确的单词顺序由排序规则决定——法语中的排序规则是在给定的单词中最后一个重音决定顺序。这是该特殊语言的一个特点。因此—— 语言的内容可以影响排序结果，而忽略语言会导致意想不到的排序结果。

提示：
在单一语言的应用中，初始化数据库总是要用合适的区域设置，
在多语言应用中，用默认的区域设置初始化数据库，在每一个需要排序的地方决定在SQL查询中该使用哪种排序规则：
也许你应当使用针对当前用户的排序规则，
有时你可能希望使用特定于被浏览数据的语言。
如果可以，应用排序规则到列和表——看文章了解更多。

这是我们的书城最终的版本：