Mysql基础(三):规范化过程
规范化过程
本章介绍把原始数据库分解为表的过程,这被称为规格化。数据库开发人员利用规格化过程来设计数据库,使其更便于组织和管理,同时确保数据在整个数据库里的正确性。这一过程在各种RDBMS中都是一样的。
规范化数据库
规格化是去除数据库里冗余数据的过程,在设计和重新设计数据库时使用。它是一组减少数据冗余来优化数据库的指导方针,具体的方针被称为规格形式,稍后将详细介绍。在本书中是否应该包含介绍规格化的内容是个两难的决定,因为其规则对于 SQL 初学者来说过于复杂了。然而,规格化是个十分重要的过程,对它的理解会加深我们对 SQL 的掌握。本章尽量简化对规格化的介绍,不会过于关注规格化的细节,而且着重于让读者理解其基本概念。
原始数据库
在没有经过规格化的数据库里,有些数据可能会出现在多个不同的表里,而且没有什么明显的原因。这样对安全、磁盘利用、查询速度、数据库更新都不好,特别是可能产生数据完整性的问题。在规格化之前,数据库里的数据并没有从逻辑上被分解到较小的、更易于管理的表里,图4.1展示了本书所使用的数据库在规格化之前的状态。
在数据库逻辑设计过程中,确定原始数据库里的信息由什么组成是第一个也是最重要的步骤,我们必须了解组成数据库的全部数据元素,才能有效地使用规格化技术。只有用必要的时间收集所需的数据集,才能避免因为丢失数据元素而重新设计数据库。
数据库设计逻辑
任何数据库设计都要考虑到终端用户。数据库逻辑设计,也被称为逻辑建模,是把数据安排到逻辑的、有组织的对象组,以便于维护的过程。数据库的逻辑设计应该减少数据重复,甚至是完全消除这种现象。毕竟,为什么要把数据存储两遍呢?另外,数据库逻辑设计应该努力让数据库易于维护和更新,同时也要保持数据库里的命名规范与逻辑。
一、 什么是终端用户的需求 在设计数据库时,终端用户的需求应该是最重要的考虑因素。记住,终端用户是最终使用数据库的人。利用用户的前端工具(允许用户访问数据库的客户程序),数据库的使用应该是相当简单的,但是在设计数据库时如果没有考虑到用户的需求,这也许就不能达到。性能优化也是如此。在设计时要考虑的与用户相关的因素包括:
-
数据库里应该保存什么数据? -
用户如何访问数据库? -
用户需要什么权限? -
数据库里的数据如何分组? -
哪些数据最经常被访问? -
全部数据与数据库如何关联? -
采取什么措施保证数据的正确性? -
采取什么措施减少数据冗余? -
采取什么措施让负责维护数据的用户更易于使用数据库?
规则形式
这一章讨论规格形式,这是数据库规格化过程中必不可少的一个概念。规格形式是衡量数据库被规格化级别(或深度)的一种方式。数据库的规格化级别是由规格形式决定的。下面是规格化过程中最常见的3种规格形式:
-
第一规格形式; -
第二规格形式; -
第三规格形式。
除此之外,还有其他规格形式,但都不常用。在这3种主要的规格形式中**,每一种都依赖于前一种形式所采用的规格化步骤。举例来说,如果想以第二规格形式对数据库进行规格化,数据库必须处于第一种规格形式。**
第一规格形式
第一规格形式的目标是把原始数据分解到表中。在所有表都设计完成之后,给大多数表或全部表设置一个主键。从第3章中可以知道,主键必须是个唯一的值,所以在选择主键时应该尽量选择能够从本质上唯一区别数据的元素。图4.2展示了图4.1所示原始数据库使用第一规格形式重新设计之后的情况
从图中可以看出,为了达到第一规格形式,数据被分解为包含相关信息的逻辑单元,每个逻辑单元都有一个主键,而且任何表里都没有重复的数据组。现在的数据库不再是一个大表,而是被分解为较小的、更易于管理的表:EMPLOYEE_TBL、CUSTOMER_TBL 和PRODUCTS_TBL。主键通常是表里的第一列,本例中分别是EMP_ID、CUST_ID和PROD_ID。这种命名方式是在设计数据库时常用的规范,确保了各种名称的可读性。 主键也可以由表中的多个列构成。这类主键所涉及的数据通常不是数据库自动生成的数字,而是有逻辑意义的数据,例如生产商的名称或者一本书的 ISBN 编号。这类数据被称为自然主键,即使不在数据库中,也可以通过它们来区分不同的对象。在为表选择主键的时候,需要注意的一点就是**,主键必须能够唯一地定义表中的一条记录。否则,查询的结果可能会返回重复的记录,而且也无法通过主键来删除一条特定的记录。**
第二规格形式
第二规格形式的目标是提取对主键仅有部分依赖的数据,把它们保存到另一个表里
从图中可以看出,第二规格形式以第一规格形式为基础,把两个表进一步划分为更明确的单元。
-
EMPLOYEE_TBL被分解为两个表,分别是EMPLOYEE_TBL和EMPLOYEE_PAY_TBL。
-
雇员个人信息是依赖于主键(EMP_ID)的,保留在EMPLOYEE_TBL表里的都是如此(EMP_ID、LAST_NAME、FIRST_NAME、MIDDLE_NAME、ADDRESS、CITY、STATE、ZIP、PHONE和PAGER)。而在另一方面,与EMP_ID仅部分依赖的信息被转移到EMPLOYEE_PAY_TBL(包括EMP_ID、POSITION、POSITION_DESC、DATE_HIRE、PAY_RATE和DATE_LAST_RAISE)。注意到两个表都包含列EMP_ID,这是每个表的主键,用于在两个表之间匹配对应的数据。
-
CUSTOMER_TBL被分解为两个表,分别是CUSTOMER_TBL和ORDERS_TBL,具体情况类似于EMPLOYEE_TBL,仅部分依赖于主键的列被转移到另一个表。顾客的订单信息依赖于每一个CUST_ID,但与顾客的一般信息没有直接依赖关系。
第三规格形式
第三规格形式的目标是删除表里不依赖于主键的数据
这里又创建了一个新表来实现第三规格形式。EMPLOYEE_PAY_TBL被分解为两个表:一个表保存雇员的实际支付信息,另一个表保存职位描述。这的确不需要保存在EMPLOYEE_PAY_TBL里,列POSITION_DESC与主键EMP_ID完全不相关。从上述介绍可以看出,规格化过程就是采取一系列步骤,把原始数据分解为由关联数据形成的多个表。
命名规范
命名规范是在数据库规格化过程中最重要的考虑因素之一。名称是我们引用数据库对象的方式**。表的名称应该能够描述所保存信息的类型,以便于我们找到需要的数据。对于没有参加数据库设计而需要查询数据库的用户来说,具有描述性的名称更为重要。** 应该在公司范围内统一命名规范,不仅是数据库里表的命名,而是用户、文件和其他相关对象的命名都应该遵守。命名规范还让我们更容易判断表的用途和数据库系统里文件的位置,从而有助于数据库管理。设计和坚持命名规范是公司开发成功数据库实现的第一步。
规格化的优点
规格化为数据库带来了很多好处,主要包括以下几点:
-
更好的数据库整体组织性; -
减少冗余数据; -
数据库内部的数据一致性; -
更灵活的数据库设计; -
更好地处理数据库安全; -
加强引用整体性的概念。
组织性是由规格化过程所产生的,让从访问数据库的用户到负责管理数据库所有对象的管理员(DBA)的所有人都感到更轻松。数据冗余被减少了,从而简化了数据结构,节约了磁盘空间。由于重复数据被尽量减少了,所以数据不一致的可能大大降低。举例来说,某人在一个表的姓名可能是STEVE SMITH,而在另一个表里是STEPHEN R. SMITH。减少重复数据提高了数据完整性,或者说数据库里数据的一致性和准确性。数据库规格化之后,分解为较小的表,便于我们更灵活地修改现有的结构。显然,修改包含较少数据的小表,要比修改包含数据库全部重要数据的一个大表要轻松得多。最后,DBA能够控制特定用户对特定表的访问,从而提高了安全性。在进行了规格化之后,安全就更容易控制了。引用完整性表示一个表里某列的值依赖于另一个表里某列的值。举例来说,如果某个顾客要在表ORDERS_TBL里有一条记录,则必须首先在表CUSTOMER_TBL里有一条记录。完整性约束还可以限制列的取值范围,它应该在创建表时设置。引用完整性一般是通过使用主键和外键来控制的。在一个表里,外键(通常是一个字段)直接引用另一个表里的主键来实现引用完整性。在前一个图里,表ORDERS_TBL里的CUST_ID就是一个外键,它引用表CUSTOMER_TBL里的CUST_ID。规格化过程把数据从逻辑上分解为由主键引用的子集,从而有助于加强和坚持这些约束。
规格化的缺点
虽然大多数成功的数据库都在一定程度上进行了规格化,但规格化的确有一个不可回避的缺点:
-
降低数据库性能。性能降低的程度取决于查询或事务被提交给数据库的时机,其中涉及多个因素,比如 CPU 使用率、内存使用率和输入/输出(I/O)。简单来说, -
规格化的数据库比非规格化的数据库需要更多的CPU、内存和I/O来处理事务和查询。 -
规格化的数据库必须找到所需的表,然后把这些表的数据结合起来,从而得到需要的信息或处理相应的数据
去规格化数据库
去规格化是修改规格化数据库的表的构成,在可控制的数据冗余范围内提高数据库性能。尝试提高性能是进行去规格化数据库的唯一原因。去规格化的数据库与没有进行规格化的数据库不一样,去规格化是在数据库规格化基础上进行一些调整,因为规格化的数据库需要频繁地进行表的结合而降低了性能(关于表的结合请见第13章)。去规格化会把一些独立的表合成在一起,或是创建重复的数据,从而减少在数据检索时需要结合的表的数量,进而减少所需的I/O和CPU时间。这在较大的数据仓库程序中会有明显的好处,其中的计算可能会涉及表里数以百万行的数据。 去规格化也是有代价的。它增加了数据冗余,虽然提高了性能,但需要付出更多的精力来处理相关的数据。程序代码会更加复杂,因为数据被分散到多个表,而且可能更难于定位。另外,引用完整性更加琐碎,因为相关数据存在于多个表里。规格化与去规格化都有好处,但都需要我们对实际的数据和公司的详细业务需求有全面的了解。在确定要着手进行去规格化时,一定要仔细记录所采取的过程,以便于更好地处理像数据冗余这样的问题,维护系统内部的数据完整性。
小结
在进行数据库设计时,必须做出一个困难的决定——规格化或去规格化,这的确是个问题。一般来说**,数据库问题需要进行一定程度的规格化**,但到什么程度才不至于严重影响性能呢?答案取决于程序本身。数据库有多大?其用途是什么?什么样的用户要访问数据?本章介绍了3种最常见的规格形式、规格化过程的底层概念、数据的完整性。规格化过程包含多个步骤,大多数都不是必需的,但对于数据库的功能和性能来说都是很重要的。无论决定进行什么程度的规格化,总是会存在便于维护与性能降低,或复杂维护与更好性能之间的平衡。最终,设计数据库的个人(或团队)必须做出决定,并对此负责。
问与答
问:在设计数据库时为什么要考虑最终用户的需求?
答:最终用户才是真正使用数据库的人,从这种角度来说,他们应该是任何数据库设计的中心。数据库设计人员只不过是帮助组织数据而已。
问:规格化要比去规格化好吗?
答:可能是这样的,但是到达一定程度时,去规格化可能会更好,这其中受到很多因素的影响。我们会对数据库进行规格化来减少其中的重复数据,到达一定程度之后可能又会转回头来,通过去规格化来改善性能。
实践
1.判断正误:规格化是把数据划分为逻辑相关组的过程。
(对)规格化是把数据划分为逻辑相关组的过程。
2.判断正误:让数据库里没有重复或冗余数据,让数据库里所有内容都规格化,总是最好的方式。
(错)不一定。规格化会让更多的表需要结合,增加I/O和CPU时间,从而降低数据库性能。
3.判断正误:如果数据是第三规格形式,它会自动属于第一和第二规格形式。
(对)如果数据是第三规格形式,它会自动属于第一和第二规格形式。
4.与规格化数据库相比,去规格化数据库的主要优点是什么?
最大优点是改善性能。
5.去规格化的主要缺点是什么?
冗余和重复数据会占据额外的空间,难以编程,需要更多的数据维护工作。
6.在对数据库进行规格化时,如何决定数据是否需要转移到单独的表?答:如果表包含冗余的数据组,这些数据就可以转移到单独的表里。
7.对数据库设计进行过度规格化的缺点是什么?
答:过度规格化会大量占用CPU和内存资源,给服务器造成很大的压力。
练习
1.为一家小公司开发一个新数据库,使用如下数据,对其进行规格化。记住,即使是一家小公司,其数据库的复杂程度也会超过这里给出的范例。雇员:顾客:顾客订单:
2.像第 3 章介绍的那样登录到你新建的数据库。可以输入以下命令来确保使用的是learnsql数据库:在Oracle中,这条命令意味着进入规划。默认情况下,用户在自己的规划中创建对象。进入数据库后,根据练习 1中定义的信息,用CREATE TABLE命令创建相应的表。