理论说得够多了!通过实例来学习数据库建模为何要讨论错误?优秀数据库设计的艺术就像游泳。入手相对容易,精通则很困难。如果你想学习设计数据库,一定得有一些理论背景,比如关于数据库设计范式和事务隔离级别的知识。但你还应该尽可能地多加练习,因为可悲的事实就是,我们在犯错中学习得更多。本文 ...
理论说得够多了!通过实例来学习数据库建模
为何要讨论错误?
优秀数据库设计的艺术就像游泳。入手相对容易,精通则很困难。如果你想学习设计数据库,一定得有一些理论背景,比如关于数据库设计范式和事务隔离级别的知识。但你还应该尽可能地多加练习,因为可悲的事实就是,我们在犯错中学习得更多。
本文中,通过展示在设计数据库时常犯的一些错误,我们尝试把学习数据库设计变得容易一点。
注意,我们假定读者了解数据库范式并知道一点关系数据库的基础知识,因而不会去讨论数据库规范化。只要有可能,文中所涵盖的主题都将使用 Vertabelo 建模和实例来说明。
本文涵盖了设计数据库的各个方面,但着重于Web应用,因此有些例子可能是特定于web应用程序的。
模型设计
假设我们想要为一个在线书城设计数据库。该系统应当允许用户执行以下活动:
- 通过书名、描述和作者信息浏览与搜索书籍,
- 阅读后对书籍添加评论和评级,
- 定购书籍,
- 查看订单处理的状态。
那么最开始的数据库模型可能如下所示:
为了测试该模型,我们使用Vertabelo为其生成SQL,并且在PostgreSQL RDBMS中创建一个新的数据库。
该数据库有8张表,其中没有数据。我们已经往里面填充了一些人工生成的测试数据。现在数据库里包含了一些示范数据,准备好开始模型检查了,包括识别那些现在不可见但将来在真实用户使用时会出现的潜在问题。
1 —— 使用无效的名称
你可以在上面的模型中看到我们用“order”命名了一张表。不过,或许你还记得,“order”在SQL中是保留字! 因此如果你试图发起一个SQL查询:
SELECT * FROM ORDER ORDER BY ID |
数据库管理系统将会抗议。很幸运,在PostgreSQL中用双引号把表名包裹起来就行了,语句仍可以执行:
SELECT * FROM "order" ORDER BY ID |
等等,可是这里的“order”是小写!
没错,这值得深究。如果你在SQL中用双引号把什么包了起来,它就变成分隔标识符,大多数数据库将以区分大小写的方式解释它。由于“order” 是SQL中的保留字,Vertabelo生成SQL会自动把order用双引号包起来:
但是由于标识符被双引号包裹且是小写,表名仍然是小写。现在如果你希望事情变得更复杂,我可以创建另一个表,这次把它名为ORDER(大写),PostgreSQL不会检测到命名冲突:
如果一个标识符没有被双引号包裹,它就被称作“普通标识符”,在被使用前自动被转成大写——这是SQL 92标准所要求的。但是标识符如果被双引号包裹
——就被称作“分隔标识符”——要求被保持原样。
底线就是——不要使用关键字来当做对象名称。永远不要。
你知道Oracle中名称长度上限是30个字符吗?
关于给表以及数据库其他元素命好名——这里命好名的意思不仅是“不与SQL关键字冲突”,还包括是自解释的且容易记住——这一点常常被严重低估。在一个小型数据库中,比如我们这个,命名其实并不是件非常重要的事。但是当你的数据库增长到100、200或者500张表,你就会知道在项目的生命周期中为保证模型的可维护性,一致和直观的命名至关重要。
记住你不光是给表和列命名,还包括索引、约束和外键。你应当建立命名约定来给这些数据库对象命名。记住名字的长度也是有限制的。如果你给索引命名太长,数据库也会抗议。
提示:
- 让你的数据库中的名字:
- 尽可能短,
- 直观,尽可能正确和具有描述性,
- 保持一致性;
- 避免使用SQL和数据库引擎特定的关键字作为名字;
- 建立命名约定;
以下是把order表重命名为purchase后的模型:
模型中的改变如下:
2 ——列的宽度不足
让我们进一步来看这个模型。如我们所看到的,在book_comment表中,comment列的类型是1000个以内的字符。这意味着什么?
假设这个字段将是GUI(用户只能输入非格式化的评论)中的纯文本,那么它简单地意味着该字段可以存储最多1000个文本字符。如果是这样的话——这里没有错误。
但是如果这个字段允许一些格式化的动作,比如bbcode或者HTML,那么用户实际上输入进去的字符数量是未知的。假如他们输入一个简单的评论,如下:
那么它会只占用17个字符。然而如果他们使用粗体格式化它,像这样:
这就需要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条评论。为了查询这些评论,我们将使用如下的语句:
select comment, send_ts from book_comment order by send_ts desc limit 30; |
这个查询运行起来有多快?在我的笔记本上花费不到70毫秒。但是如果我们希望应用能够按比例变化(在高负载下快速运行),需要在更大的数据上检测。所以我在book_comment表中插入了更多的记录。为此我将使用一个很长的单词列表,然后使用一个简单的Perl命令将其转成SQL。
现在我要把这个SQL导入到PostgreSQL数据库。一旦导入开始,我就会检测之前那个查询的执行时间。统计结果在如下的表格中:
如你所见,随着 book_comment 中行数的增加,要获取最新30行所花费的查询时间也在成比例地增加。为何耗费时间增长?我们看看这个查询计划:
db=# explain select comment, send_ts from book_comment order by send_ts desc limit 30; |
这个查询计划告诉我们数据库如何处理查询及计算结果的大致时间成本。这里PostgreSQL告诉我们将进行“Seq Scan on book_comment”,这意味着它将逐个检查 book_comment 表的所有记录,以此对send_ts列的值进行排序。貌似PostgreSQL还没有聪明到在不去对所有的600,000条进行排序的条件下查询30个最新记录。
幸运地是,我们可以通过告知PostgreSQL根据send_ts进行排序并保存结果来帮助它。为此,我们先在该列上创建一个索引:
create index book_comment_send_ts_idx on book_comment(send_ts); |
现在我们的查询语句从600,000条记录中查询出最新30条所花费的时间又是67毫秒了。查询计划差别非常大:
“Index Scan”指不是逐行扫描book_comment表,而是数据库会扫描我们刚刚创建的索引。估计查询成本小于1.43,低于之前的2.8万倍。
你遇到了性能问题?第一次尝试解决就应当是找到运行时间最长的查询,让你的数据库来解释它们,并且寻找全表扫描。如果你找到了,也许增加一些索引可以快速提升速度。
不过,数据库性能设计是一个庞大的主题,超出了本文的范围。
这是逐字排序的结果,从左到右。
但是这些单词是法语,所以这才是正确的:
db=# select title from book where id between 1 and 4 order by title collate "en_GB" ; |
这两个结果不同,因为正确的单词顺序由排序规则决定——法语中的排序规则是在给定的单词中最后一个重音决定顺序。这是该特殊语言的一个特点。因此—— 语言的内容可以影响排序结果,而忽略语言会导致意想不到的排序结果。
提示:
原标题:数据库设计的 7 个常见错误
关键词:数据库
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。