专题全面解析数据库的设计原则与开发技巧

专题：全面解析数据库的设计原则与开发技巧数据库设计 ( Database Design )是指对于一个给定的应用环境，构造最优的 数据库 模式 ，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求。 这篇专题主要针对数据库的设计原则与开发技巧进行了简明扼要的总结。一、数 据 库 设 计 经 验 谈数据库模型的设计是否合理会极大影响系统的使用性能。笔者依据多年来设计和使用数据库的经验，提出以下一些设计原则，供同仁们参考。慎用游标（Cursor） 游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机，笔者在某市“住房公积金管理系统”进行日终账户滚积数计息处理时，对一个10万个账户的游标处理时导致程序进入了一个无限期的等待（后经测算需48小时才能完成）(硬件环境：Alpha/4000 128MB RAM ,SCO Unix ,Sybase 11.0)。经修改程序并改用UPDATE语句后，该处理过程得以在20分钟之内完成。示例如下： Declare Mycursor cursor for select countno from COUNTOpen MycursorFetch Mycursor into vcountno While (sqlstatus=0) Begin If vcountno= 条件1 操作1 If vcountno= 条件2 操作2 . Fetch Mycursor into vcountno End. 改为Update COUNT set 操作1 for 条件1 Update COUNT set 操作2 for 条件2 .在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样，可使性能得到明显提高。笔者在某地市“电信收费系统”数据库后台程序设计中，对一个表（3万行中符合条件的30多行数据）进行游标操作(硬件环境：PC服务器，P266 64MB RAM ,Windows NT4.0 MS SQL Server 6.5)。 示例如下：Create tmp / 定义临时表 / ( 字段1 字段2 . ) Insert into tmp select from TOTAL where 条件 Declare Mycursor cursor for select from tmp /对临时表定义游标/ . 索引(Index)的使用技巧 创建索引一般有两个目的：维护被索引列的惟一性和提供快速访问表中数据的策略。大型数据库有两种索引，即簇索引和非簇索引，一个没有簇索引的表是按堆结构存储数据，所有的数据均添加在表的尾部；而建立了簇索引的表，其数据在物理上会按照簇索引键的顺序存储，一个表只允许有一个簇索引，因此，根据B树结构，可以理解添加任何一种索引均能提高按索引列查询的速度，但与此同时会降低插入、更新、删除操作的性能，尤其是当填充因子（Fill Factor）较大时。所以对索引较多的表进行频繁的插入、更新、删除操作时，建表和索引时应设置较小的填充因子，以便在各数据页中留下较多的自由空间，减少页分割及重新组织的工作。 数据的一致性和完整性 为了保证数据库的一致性和完整性，设计人员往往会设计过多的表间关联（Relation），尽可能地降低数据冗余。表间关联是一种强制性措施，建立后，对父表（Parent Table）和子表(Child Table)的插入、更新、删除操作均要占用系统的开销，另外，最好不要用Identify 属性字段作为主键与子表关联。如果数据冗余低，数据的完整性容易得到保证，但增加了表间连接查询的操作。为了提高系统的响应时间，合理的数据冗余也是必要的。使用规则（Rule）和约束（Check）来防止系统操作人员误输入造成数据的错误是，设计人员的另一种常用手段，但是，不必要的规则和约束也会占用系统的不必要开销，需要注意的是，约束对数据的有效性验证要比规则快。所有这些，设计人员在设计阶段应根据系统操作的类型、频度加以均衡考虑。事务的陷阱 事务是在一次性完成的一组操作。虽然这些操作是单个的操作，SQL Server能够保证这组操作要么全部都完成，要么一点儿都不做。正是大型数据库的这一特性，使得数据的完整性得到了极大的保证。 众所周知，SQL Server为每个独立的SQL语句都提供了隐含的事务控制，使得每个DML的数据操作得以完整提交或回滚，但是SQL Server还提供了显式事务控制语句，如：BEGIN TRANSACTION 开始一个事务COMMIT TRANSACTION 提交一个事务ROLLBACK TRANSACTION 回滚一个事务事务可以嵌套，可以通过全局变量trancount检索到连接的事务处理嵌套层次。要特别注意的是，每个显示或隐含的事物开始都使得该变量加1，每个事务的提交使该变量减1，每个事务的回滚都会使得该变量置0，而只有当该变量为0时的事务提交（最后一个提交语句时），才把物理数据写入磁盘。数据类型的选择 数据类型的合理选择对于数据库的性能和操作具有很大的影响，有关这方面的书籍也有不少的阐述，笔者这里主要介绍几点经验：1. Identify字段不要作为表的主键与其它表关联，这将会影响到该表的数据迁移。 2. Text 和Image字段属指针型数据，主要用来存放二进制大型对象（BLOB）。这类数据的操作相比其它数据类型较慢，因此要避开使用。 3. 日期型字段的优点是有众多的日期函数支持，因此，在日期的大小比较、加减操作上非常简单。但是，在按照日期作为条件的查询操作也要用函数，相比其它数据类型速度上就慢许多,因为用函数作为查询的条件时，服务器无法用先进的性能策略来优化查询而只能进行表扫描遍历每行。二、大型数据库的设计原则与开发技巧目前，计算机技术已经广泛地应用于国民经济的各个领域当中，在计算机硬件不断微型化的同时，应用系统也逐渐向着复杂化、大型化的方向发展。数据库是整个系统的核心，它的设计直接关系系统执行的效率和系统的稳定性。因此在软件系统开发中，数据库设计应遵循必要的数据库范式理论，以减少冗余、保证数据的完整性与正确性。只有在合适的数据库产品上设计出合理的数据库模型，才能降低整个系统的编程和维护难度，提高系统的实际运行效率。虽然对于小项目或中等规模的项目开发人员可以很容易地利用范式理论设计出一套符合要求的数据库，但对于一个包含大型数据库的软件项目，就必须有一套完整的设计原则与技巧。 一、成立数据小组 大型数据库数据元素多，在设计上有必要成立专门的数据小组。由于数据库设计者不一定是使用者，对系统设计中的数据元素不可能考虑周全，数据库设计出来后，往往难以找到所需的库表，因此数据小组最好由熟悉业务的项目骨干组成。 数据小组的职能并非是设计数据库，而是通过需求分析，在参考其他相似系统的基础上，提取系统的基本数据元素，担负对数据库的审核。审核内容包括审核新的数据库元素是否完全、能否实现全部业务需求；对旧数据库（如果存在旧系统）的分析及数据转换；数据库设计的审核、控制及必要调整。 二、设计原则 规范命名。所有的库名、表名、域名必须遵循统一的命名规则，并进行必要说明，以方便设计、维护、查询。 控制字段的引用。在设计时，可以选择适当的数据库设计管理工具，以方便开发人员的分布式设计和数据小组的集中审核管理。采用统一的命名规则，如果设计的字段已经存在，可直接引用；否则，应重新设计。 库表重复控制。在设计过程中，如果发现大部分字段都已存在，开发人员应怀疑所设计的库表是否已存在。通过对字段所在库表及相应设计人员的查询，可以确认库表是否确实重复。 并发控制。设计中应进行并发控制，即对于同一个库表，在同一时间只有一个人有控制权，其他人只能进行查询。 必要的讨论。数据库设计完成后，数据小组应与相关人员进行讨论，通过讨论来熟悉数据库，从而对设计中存在的问题进行控制或从中获取数据库设计的必要信息。 数据小组的审核。库表的定版、修改最终都要通过数据小组的审核，以保证符合必要的要求。 头文件处理。每次数据修改后，数据小组要对相应的头文件进行修改（可由管理软件自动完成），并通知相关的开发人员，以便进行相应的程序修改。 三、设计技巧 分类拆分数据量大的表。对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。例如，银行的户主账表原来设计成一张表，虽然可以方便程序的设计与维护，但经过分析发现，由于数据量太大，会影响数据的迅速定位。如果将户主账表分别设计为活期户主账、定期户主账及对公户主账等，则可以大大提高查询效率。 索引设计。对于大的数据库表，合理的索引能够提高整个数据库的操作效率。在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段建立的顺序进行。例如，如果对一个万多条记录的流水表以日期和流水号为序建立复合索引，由于在该表中日期的重复值接近整个表的记录数，用流水号进行查询所用的时间接近秒；而如果以流水号为索引字段建立索引进行相同的查询，所用时间不到秒。因此在大型数据库设计中，只有进行合理的索引字段选择，才能有效提高整个数据库的操作效率。 数据操作的优化。在大型数据库中，如何提高数据操作效率值得关注。例如，每在数据库流水表中增加一笔业务，就必须从流水控制表中取出流水号，并将其流水号的数值加一。正常情况下，单笔操作的反应速度尚属正常，但当用它进行批量业务处理时，速度会明显减慢。经过分析发现，每次对流水控制表中的流水号数值加一时都要锁定该表，而该表却是整个系统操作的核心，有可能在操作时被其他进程锁定，因而使整个事务操作速度变慢。对这一问题的解决的办法是，根据批量业务的总笔数批量申请流水号，并对流水控制表进行一次更新，即可提高批量业务处理的速度。另一个例子是对插表的优化。对于大批量的业务处理，如果在插入数据库表时用普通的语句，速度会很慢。其原因在于，每次插表都要进行一次/操作，花费较长的时间。改进后，可以用语句等缓冲区形式等满页后再进行/操作，从而提高效率。对大的数据库表进行删除时，一般会直接用语句，这个语句虽然可以进行小表操作，但对大表却会因带来大事务而导致删除速度很慢甚至失败。解决的方法是去掉事务，但更有效的办法是先进行操作再进行重建。 数据库参数的调整。数据库参数的调整是一个经验不断积累的过程，应由有经验的系统管理员完成。以数据库为例，记录锁的数目太少会造成锁表的失败；逻辑日志的文件数目太少会造成插入大表失败等，这些问题都应根据实际情况进行必要的调整。 必要的工具。在整个数据库的开发与设计过程中，可以先开发一些小的应用工具，如自动生成库表的头文件、插入数据的初始化、数据插入的函数封装、错误跟踪或自动显示等，以此提高数据库的设计与开发效率。 避免长事务。对单个大表的删除或插入操作会带来大事务，解决的办法是对参数进行调整，也可以在插入时对文件进行分割。对于一个由一系列小事务顺序操作共同构成的长事务（如银行交易系统的日终交易），可以由一系列操作完成整个事务，但其缺点是有可能因整个事务太大而使不能完成，或者，由于偶然的意外而使事务重做所需的时间太长。较好的解决方法是，把整个事务分解成几个较小的事务，再由应用程序控制整个系统的流程。这样，如果其中某个事务不成功，则只需重做该事务，因而既可节约时间，又可避免长事务。 适当超前。计算机技术发展日新月异，数据库的设计必须具有一定前瞻性，不但要满足当前的应用要求，还要考虑未来的业务发展，同时必须有利于扩展或增加应用系统的处理功能。 与小型数据库相比，大型数据库的设计与开发要复杂得多，因此在设计、开发过程中，除了要遵循数据库范式理论、增加系统的一致性和完整性外，还要在总体上根据具体情况进行分布式设计，紧紧把握集中控制、统一审核的基本原则，保证数据库设计结构紧凑、分布平衡、定位迅速。在数据库操作上，要采用一定的技巧提高整个应用系统的执行效率，并注意适当超前，以适应不断变化的应用及系统发展的要求。三、数据表的设计原则1)不应该针对整个系统进行数据库设计，而应该根据系统架构中的组件划分，针对每个组件所处理的业务进行组件单元的数据库设计；不同组件间所对应的数据库表之间的关联应尽可能减少，如果不同组件间的表需要外键关联也尽量不要创建外键关联，而只是记录关联表的一个主键，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性。2)采用领域模型驱动的方式和自顶向下的思路进行数据库设计，首先分析系统业务，根据职责定义对象。对象要符合封装的特性，确保与职责相关的数据项被定义在一个对象之内，这些数据项能够完整描述该职责，不会出现职责描述缺失。并且一个对象有且只有一项职责，如果一个对象要负责两个或两个以上的职责，应进行分拆。3)根据建立的领域模型进行数据库表的映射，此时应参考数据库设计第二范式：一个表中的所有非关键字属性都依赖于整个关键字。关键字可以是一个属性，也可以是多个属性的集合，不论那种方式，都应确保关键字能够保证唯一性。在确定关键字时，应保证关键字不会参与业务且不会出现更新异常，这时，最优解决方案为采用一个自增数值型属性或一个随机字符串作为表的关键字。4)由于第一点所述的领域模型驱动的方式设计数据库表结构，领域模型中的每一个对象只有一项职责，所以对象中的数据项不存在传递依赖，所以，这种思路的数据库表结构设计从一开始即满足第三范式：一个表应满足第二范式，且属性间不存在传递依赖。5)同样，由于对象职责的单一性以及对象之间的关系反映的是业务逻辑之间的关系，所以在领域模型中的对象存在主对象和从对象之分，从对象是从1N或NN的角度进一步主对象的业务逻辑，所以从对象及对象关系映射为的表及表关联关系不存在删除和插入异常。6)在映射后得出的数据库表结构中，应再根据第四范式进行进一步修改，确保不存在多值依赖。这时，应根据反向工程的思路反馈给领域模型。如果表结构中存在多值依赖，则证明领域模型中的对象具有至少两个以上的职责，应根据第一条进行设计修正。第四范式：一个表如果满足BCNF，不应存在多值依赖。7)在经过分析后确认所有的表都满足二、三、四范式的情况下，表和表之间的关联尽量采用弱关联以便于对表字段和表结构的调整和重构。并且，我认为数据库中的表是用来持久化一个对象实例在特定时间及特定条件下的状态的，只是一个存储介质，所以，表和表之间也不应用强关联来表述业务（数据间的一致性），这一职责应由系统的逻辑层来保证，这种方式也确保了系统对于不正确数据（脏数据）的兼容性。当然，从整个系统的角度来说我们还是要尽最大努力确保系统不会产生脏数据，单从另一个角度来说，脏数据的产生在一定程度上也是不可避免的，我们也要保证系统对这种情况的容错性。这是一个折中的方案。8)应针对所有表的主键和外键建立索引，有针对性的（针对一些大数据量和常用检索方式）建立组合属性的索引，提高检索效率。虽然建立索引会消耗部分系统资源，但比较起在检索时搜索整张表中的数据尤其时表中的数据量较大时所带来的性能影响，以及无索引时的排序操作所带来的性能影响，这种方式仍然是值得提倡的。9)尽量少采用存储过程，目前已经有很多技术可以替代存储过程的功能如“对象/关系映射”等，将数据一致性的保证放在数据库中，无论对于版本控制、开发和部署、以及数据库的迁移都会带来很大的影响。但不可否认，存储过程具有性能上的优势，所以，当系统可使用的硬件不会得到提升而性能又是非常重要的质量属性时，可经过平衡考虑选用存储过程。10)当处理表间的关联约束所付出的代价（常常是使用性上的代价）超过了保证不会出现修改、删除、更改异常所付出的代价，并且数据冗余也不是主要的问题时，表设计可以不符合四个范式。四个范式确保了不会出现异常，但也可能由此导致过于纯洁的设计，使得表结构难于使用，所以在设计时需要进行综合判断，但首先确保符合四个范式，然后再进行精化修正是刚刚进入数据库设计领域时可以采用的最好办法。11)设计出的表要具有较好的使用性，主要体现在查询时是否需要关联多张表且还需使用复杂的SQL技巧。12)设计出的表要尽可能减少数据冗余，确保数据的准确性，有效的控制冗余有助于提高数据库的性能。四、大型MIS的开发应重视数据库设计年代初以来，国内许多计算机专家先后深入一些大型企业，力图开发出理想的大型。实践证明，开发出的大型，多数不很理想。原因何在？据作者一孔之见，其中一条重要的原因，就是在开发过程中对的数据库设计重视不够，没有把它当作一件头等大事来处理。一个大型，如果它的数据库设计出了问题，就是出了大问题，或者说从根本上出了问题。这样的，不会成功，只会失败。既然如此，应该怎样来解决它呢？一、的基础是数据库系统包括硬件和软件两部分。的软件，是由文档加程序组成的。它的文档，就是的全部设计说明书。它的程序，就是的全部算法加上相应的数据结构。的算法无非是它的各种录入、修改、查询、处理、输出与菜单程序的算法。的数据结构，主要是指数据库设计中的各种基本表。可以这么说，基本表是的基础。数据库设计既是开发中的重点，又是其难点。说它是重点，因为设计出一套好的基本表需要许多技巧。的发展是分阶段的，不同的阶段，对应不同的数据库。在的初级（初始与扩展）阶段，对应的数据库为应用数据库。所谓应用数据库，就是针对某项具体的应用而设计的基本表的集合，这种数据库的设计、使用与维护均较容易。在的中级（控制与集成）阶段，对应的数据库为主题数据库。所谓主题数据库，就是针对某方面的主题而设计的基本表的集合，它包括本主题范围内的所有应用项目，这种数据库的设计、使用与维护均较复杂。在的高级（数据管理与成熟）阶段，对应的数据库为综合数据库。所谓综合数据库，就是针对某个大型企事业单位的综合管理信息系统而设计的基本表的集合，它包括本单位的所有主题，这种数据库的设计、使用与维护均很复杂，对设计者、用户与的要求均很高。二、数据库设计的一般方法数据库设计分五大步，即数据库需求分析、概念设计、逻辑设计、物理设计与加载测试。需求分析的任务是将业务管理单证流转化为数据流，绘制出数据流程图，并完成相应的数据字典，概念设计的任务是从出发，识别实体及其相互关系，并绘制出实体关系图，即图。逻辑设计的任务是从图出发，确定各个实体及关系的具体属性。物理设计的任务是确定所有属性的类型、宽长与取值范围，设计出基本表的主键与外键，将所有表名与字段名英文化，完成相应的数据字典，在具体的环境上实现物理建库工作。加载测试工作贯穿于程序测试工作的全过程，整个录入、修改、查询、处理、输出工作，均可视为对数据库的加载测试工作。应该指出，大型数据库的设计不大可能一次顺利完成，上述五大步骤，很可能是一个不断迭代的过程。三、基本表与其它表中的数据库是由一组基本表所组成的，一个实体可以用一张基本表来描述，一个复杂关系也可以用一张基本表来描述。所以，基本表可以代表一个实体，也可以代表一个关系。基本表中的字段，就是实体或关系的属性。基本表是存放基础数据的地方，这些基础数据具有五个基本性质。原子性，即表中的数据是元数据。演绎性，即由表中的数据可以生成系统所有的输出数据。稳定性，即表中的数据一次录入、多次使用、长期保存。规范性，即表中的数据满足第三范式。客观性，即表中的数据是客观存在的数据，不是主观想象中的数据。中的表除了基本表之外，还有一些非基本表，如代码表、中间表、临时表与虚表（视图），它们不属于数据库的内容，但均以表的形式出现，为数据的录入、查询、处理、输出提供方便。利用基本表的五个性质，很容易区分基本表与非基本表。非基本表的设计是不难的，基本表的设计是较难的，中的数据库设计，主要是指基本表的设计。四、数据库的设计技巧数据库设计中有两个难点，一是如何处理多对多的关系，二是如何设计主键。处理多对多的关系的办法为：将一个多对多的关系分解为一个一对多的关系加上另一个多对一的关系。例如，若两个表之间存在多对多的关系，就在它俩之间增加一个表，该表的字段中至少要包括前两个表的主键在内。这样，就将一个多对多的关系转化为两个一对多的关系了。在基本表中，主键是记录的唯一标识。一般而言，主键是为索引文件或表间连接服务的。它对用户不透明，只提供给程序员使用。因此，主键的取值最好为一串无物理意义的数值，且由程序自动加来实现。主键是一个永久为非空的字段，一旦产生，便不能修改，但可以被拷贝。通过拷贝，这个表的主键可作为那个表的外键。要设计好数据库，除了克服以上两个难点之外，还应遵循下列原则：即基本表的个数越少越好；主键的个数越少越好；字段的个数越少越好。五、的开发模式结合我国的特点，大型的开发与大型数据库的设计，均应分为两个层次，即内核层与外壳层。内核层对应法治，设计上讲究通用性。外壳层对应人治，设计上讲究专用性。随着中国经济与世界经济接轨进程的发展，的内核层将逐步扩大，外壳层将逐步缩小，通用性将逐步增强。当前我国大型企事业单位的建设，少数单位已跨过了初级阶段，开始迈向中级或高级阶段。与此同时，数据库设计已告别了应用数据库时期，开始向主题数据库或综合数据库过渡。主题数据库或综合数据库的设计，与应用数据库设计的本质区别是：前者是面向数据，后者是面向程序。一个大型企事业单位的建设，是一个长期的反复的过程。在这一过程中，应用程序与输出图表可能逐年变动，但基础数据是稳定不变的。只要我们将基本表设计面向数据，不面向程序，用基本表组织好元数据，就能以不变应万变，避免在建设中的失误。五、影响SQL Server数据库性能设计关键1 逻辑数据库和表的设计 数据库的逻辑设计、包括表与表之间的关系是优化关系型数据库性能的核心。一个好的逻辑数据库设计可以为优化数据库和应用程序打下良好的基础。 标准化的数据库逻辑设计包括用多的、有相互关系的窄表来代替很多列的长数据表。下面是一些使用标准化表的一些好处。 A:由于表窄，因此可以使排序和建立索引更为迅速； B:由于多表，所以多镞的索引成为可能； C:更窄更紧凑的索引； D:每个表中可以有少一些的索引，因此可以提高insert update delete等的速度，因为这些操作在索引多的情况下会对系统性能产生很大的影响； E:更少的空值和更少的多余值，增加了数据库的紧凑性由于标准化，所以会增加了在获取数据时引用表的数目和其间的连接关系的复杂性。太多的表和复杂的连接关系会降低服务器的性能，因此在这两者之间需要综合考虑。 定义具有相关关系的主键和外来键时应该注意的事项主要是:用于连接多表的主键和参考的键要有相同的数据类型。 2 索引的设计 A:尽量避免表扫描 检查你的查询语句的where子句，因为这是优化器重要关注的地方。包含在where里面的每一列(column)都是可能的侯选索引，为能达到最优的性能，考虑在下面给出的例子:对于在where子句中给出了column1这个列。 下面的两个条件可以提高索引的优化查询性能! 第一:在表中的column1列上有一个单索引。 第二:在表中有多索引，但是column1是第一个索引的列。 避免定义多索引而column1是第二个或后面的索引，这样的索引不能优化服务器性能。 例如:下面的例子用了pubs数据库。 SELECT au_id, au_lname, au_fname FROM authorsWHERE au_lname = White按下面几个列上建立的索引将会是对优化器有用的索引 au_lnameau_lname, au_fname而在下面几个列上建立的索引将不会对优化器起到好的作用 au_addressau_fname, au_lname考虑使用窄的索引在一个或两个列上，窄索引比多索引和复合索引更能有效。用窄的索引，在每一页上将会有更多的行和更少的索引级别(相对与多索引和复合索引而言)，这将推进系统性能。 对于多列索引，SQL Server维持一个在所有列的索引上的密度统计(用于联合)和在第一个索引上的histogram(柱状图)统计。根据统计结果，如果在复合索引上的第一个索引很少被选择使用，那么优化器对很多查询请求将不会使用索引。有用的索引会提高select语句的性能，包括insert,uodate,delete。 但是，由于改变一个表的内容，将会影响索引。每一个insert,update,delete语句将会使性能下降一些。实验表明，不要在一个单表上用大量的索引，不要在共享的列上(指在多表中用了参考约束)使用重叠的索引。 在某一列上检查唯一的数据的个数，比较它与表中数据的行数做一个比较。这就是数据的选择性，这比较结果将会帮助你决定是否将某一列作为侯选的索引列，如果需要，建哪一种索引。你可以用下面的查询语句返回某一列的不同值的数目。 select count(distinct cloumn_name) from table_name假设column_name是一个10000行的表，则看column_name返回值来决定是否应该使用，及应该使用什么索引。 Unique values Index5000 Nonclustered index20 Clustered index3 No index镞索引和非镞索引的选择 镞索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个镞索引。由于update,delete语句要求相对多一些的读操作，因此镞索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个镞索引。 在下面的几个情况下，你可以考虑用镞索引: 例如: 某列包括的不同值的个数是有限的(但是不是极少的) 顾客表的州名列有50个左右的不同州名的缩写值，可以使用镞索引。 例如: 对返回一定范围内值的列可以使用镞索引，比如用between,=,=等等来对列进行操作的列上。 select * from sales where ord_date between 5/1/93 and 6/1/93例如: 对查询时返回大量结果的列可以使用镞索引。 SELECT * FROM phonebook WHERE last_name = Smith当有大量的行正在被插入表中时，要避免在本表一个自然增长(例如，identity列)的列上建立镞索引。如果你建立了镞的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。 当一个数据正在被插入(这时这个数据页是被锁定的)，所有的其他插入行必须等待直到当前的插入已经结束。 一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟镞索引的逻辑次序一样的。 一个非镞的索引就是行的物理次序与索引的次序是不同的。一个非镞索引的叶级包含了指向行数据页的指针。 在一个表中可以有多个非镞索引，你可以在以下几个情况下考虑使用非镞索引。 在有很多不同值的列上可以考虑使用非镞索引。 例如:一个part_id列在一个part表中 select * from employee where emp_id = pcm9809f查询语句中用order by 子句的列上可以考虑使用镞索引。 3 查询语句的设计 SQL Server优化器通过分析查询语句，自动对查询进行优化并决定最有效的执行方案。优化器分析查询语句来决定那个子句可以被优化，并针对可以被优化查询的子句来选择有用的索引。最后优化器比较所有可能的执行方案并选择最有效的一个方案出来。 在执行一个查询时，用一个where子句来限制必须处理的行数，除非完全需要，否则应该避免在一个表中无限制地读并处理所有的行。 例如下面的例子， select qty from sales where stor_id=7131是很有效的如下面这个无限制的查询： select qty from sales避免给客户的最后数据选择返回大量的结果集。允许SQL Server运行满足它目的的函数限制结果集的大小是更有效的。 六、数据库设计规范（命名规范）1 目的 规范数据库设计。 2 概述 从数据库的设计原则 设计文档几方面论述数据库设计的规范思想及命名规则。 3 数据库应用结构 根据对一般业务系统的分析，将数据库和程序系统统一进行整体描述，展示数据库的 表之间以及与程序模块间的关系。 3.1 数据表和程序模块的分类 根据“处理特点”，将数据表和程序模块进行分类如下： 数据表分类：业务数据表、基本编码表、辅助编码表、系统信息表、累计数据表、结 算数据表、决策数据表。 程序模块分类：初始化、业务处理、完整性检测与修正、结算处理、统计处理。 3.1.1 数据表分类说明 业务数据表：记录业务发生的过程和结果。如，合同、出仓单、申请单、凭证。 基本编码表：描述业务实体的基本信息和编码。如，产品、客户、供应商、雇员。 辅助编码表：描述属性的列表值。如，合同类型、职称、民族、付款方式。 系统信息表：存放与系统操作、业务控制有关的参数。如，用户信息、权限、用户配 置信息、成本核算方式。 累计数据表：存放业务的当前值和累计值。如，当前库存、当前存款、累计销售、累 计支出、应收账款。 结算数据表：存放各个时期末的结存数。如，月末库存、月末银行存款、应收账款月 结。决策数据表：存放各个时期内发生的统计值。如，月销售统计、月回款统计、出入库 统计。 3.1.2 程序模块分类说明 初始化：系统运行前对系统进行数据的初始化。如，库存初始化。 业务处理：业务过程的控制和结果记录。如，合同录入、费用审批、出入库。 完整性检测与修正：对累计数据表进行检查并自动修正。如对当前库存、当前存款、 累计销售的检查和重新计算。 结算处理：计算并记录各个时期末的结存数。库存月结、应收账款月结。 统计处理：计算并记录各个时期内发生的统计数。如，统计月销售、统计月回款、统 计出入库。 3.2 数据表间的关系 业务数据表基本编码表 主-外键关系。如，合同表客户编码表; 业务数据表辅助编码表 主-外键关系。如，合同表付款方式; 业务数据表、累计数据表、结算数据表：累计数据表=结算数据表(上期末) + 业务数 据表(本期内发生)。如当前库存=上月末库存数+(本月入库数-本月出库数); 决策数据表业务数据表 决策数据表的数据是由业务数据表中数据导出(统计)的； 3.3 数据表与程序模块间的关系 由一个例子(仓库管理)来说明数据表与程序模块之间的关系： . 系统使用前，由初始化模块对库存数(累计数据表)和上月末库存数(结存数据表)进 行初始化； . 当有入库业务发生时，由入库模块(业务处理)将入库单录入并保存到入库单明细帐( 业务数据表)中，同时将入库数累加到库存数(累计数据表)中； . 定期或不定期，库存数核算模块(检查完整性检测与修正)根据上月末的库存数(结存 数据表)、本月已发生数(业务数据表)检查当前的库存数(累计数据表)是否符合，不符合 则给出提示，可手工或自动进行更正(当前库存数=上月末库存数+本月入库数-本月出库数 )； . 每月初，进行上月的月结处理。月结模块(结算处理)根据上月初的库存数(结存数据 表)、上月发生数(业务数据表)计算出上月末的库存数(累计数据表)。公式为：上月末库 存数=上月初库存数+上月入库数-上月出库数； . 每个月月结后，库存业务月统计模块(统计处理)统计上月的各种库存商品的入库和 出库数，便于查询和生成报表，也作为决策支持的数据基础。 3.4 数据表命名时对数据表分类的考虑 . 业务数据表：t_d_。如销售系统的合同表 t_d_SH_Contract 或 t_d_SH_合同； . 基本编码表：t_b_。如客户编码表t_b_Customer 或 t_b_客 户； . 辅助编码表：t_a_。如合同类别t_a_ContType 或 t_a_合同 类别； . 系统信息表：t_s_。如用户表t_s_User 或 t_s_用户； . 累计数据表：t_t_。如当前库存表t_t_SO_Stock 或 t_t_SO_ 库存； . 结算数据表：t_c_。如库存月结表t_c_SO_StockMonth 或 t_c_SO_库存月结； . 决策数据表：t_w_。如月销售统计表t_w_SH_SellMonth 或 t_w_SH_月销售统计； 注：内的内容表示可选。如“t_s_”表示t_s_SH_User 和 t_s_User 都是符合规则的。 4 数据库结构原则 规定除数据库设计所遵循的范式外的一些适用原则，在遵循数据库设计范式的基础上 ，合理地划分表，添加状态和控制字段等。 4.1 辅助编码表 为了使辅助编码表能起到预期的效能，又不因过多的辅助编码表难以管理，故对辅助 编码表的使用作如下规定： 1. 当某辅助编码表的编码允许用户添加时，应设计成“独立”的数据表；否则，将不 允许用户添加编码的各辅助编码表合并成一个“通用”的辅助编码表。 2. “独立”的辅助编码表与主表的列采用主-外约束保证列数据完整性。 3. “通用”的辅助编码表与各主表间没有约束关系，主表列的数据完整性由列说明的 “域”来保证。 4. “通用”的辅助编码表除编码和名称列外，还有一个标识列，用来标识合并前的各 码表，该标识列+编码列作为该表的主键。 5. 对于“独立”的辅助编码表，用户只可添加新的编码和改变名称，并且不能改变一 个编码所代表的意义；对于“通用”的辅助编码表，原则上不允许用户修改，或只有限地 允许修改名称。 4.2 基本编码表 1. 基本编码表可以有如下的标识列：内编码、外编码、助记码、简称、全称。内编码 （唯一编码）作为主键有程序自动生成，用户不可见；外编码（唯一编码）由用户按某种 规则自行定义，用户可见；助记码为拼音缩，方便录入，不唯一，重码时由列表选择；简 称用于列表显示和报表，以便缩短行宽。以上的列在实现时可视情况和习惯加以删减。 2. 当码表的列较多且也行较多时，可将上述的标识列和常用的信息存于一个表，将其 它的信息另表存储。 4.3 业务数据表 1. 设有录入人和录入日期列，由系统自动记录。 2. 记录单据的表中设置“自动单据号”，由两个字符开始以区分单据类型，后跟一数 字序列表示序号。自动单据号由系统自动生成，作为主表的主键，不允许用户修改。 当有对应的纸质单据时，设置“单据号”用于记录纸质单据的单据号。 3. 明细表中设有行序号，自动记录行的录入顺序。 4. 设置“存档标记”列，用于抽取数据到决策数据库时的更新标记。插入新行或修改 已有行时设置该标记；数据抽取后清除该标记。 5. 对于用于查询过滤条件的列，不可为空，以免行“丢失”。 6. 对于数值列，不可为空，“0”作为默认值。 7. 对于必要的“冗余”列，如客户名称，应有相应的程序保持各“冗余”列的同一性 ，以免出现异议。 8. 设置“过程状态”列和“记录状态”列。过程状态列用于记录如创建、审核、记账 、冲红等状态；记录状态用于记录如有效、删除等状态。 5 数据库命名原则 5.1 表名 . 业务数据表：t_d_。 . 基本编码表：t_b_。 . 辅助编码表：t_a_。 . 系统信息表：t_s_。 . 累计数据表：t_t_。 . 结算数据表：t_c_。 . 决策数据表：t_w_。 5.2 视图 v_。视图类型参见表的分类。 5.3 存储过程 p_ 5.4 函数 f_ 5.5 触发器 tr_ (after) ti_ (instead) 5.6 自定义数据类型 ud_ 5.7 Default df_ 5.8 Rule ru_ 5.9 主键 pk_ 5.10 外键 fk_七、数据库设计中的反规范技术探讨数据库设计简述数据库设计是把现实世界的商业模型与需求转换成数据库的模型的过程，它是建立数据库应用系统的核心问题。设计的关键是如何使设计的数据库能合理地存储用户的数据，方便用户进行数据处理。数据库设计完全是人的问题，而不是数据库管理系统的问题。系统不管设计是好是坏，照样运行。数据库设计应当由数据库管理员和系统分析员一起和用户一道工作，了解各个用户的要求，共同为整个数据库做出恰当的、完整的设计。数据库及其应用的性能和调优都是建立在良好的数据库设计的基础上，数据库的数据是一切操作的基础，如果数据库设计不好，则其它一切调优方法提高数据库性能的效果都是有限的。数据的规范化1.1.范式概述规范化理论是研究如何将一个不好的关系模式转化为好的关系模式的理论，规范化理论是围绕范式而建立的。规范化理论认为，一个关系数据库中所有的关系，都应满足一定的规范(约束条件)。规范化理论把关系应满足的规范要求分为几级，满足最低要求的一级叫做第一范式(1NF)，在第一范式的基础上提出了第二范式(2NF)，在第二范式的基础上又提出了第三范式(3NF)，以后又提出了BCNF范式，4NF，5NF。范式的等级越高，应满足的约束集条件也越严格。规范的每一级别都依赖于它的前一级别，例如若一个关系模式满足2NF，则一定满足1NF。下面我们只介绍1NF，2NF，3NF范式。1.2.1NF1NF是关系模型的最低要求，它的规则是：每一列必须是原子的，不能分成多个子列。每一行和列的位置只能有一个值。不能具有多值列。例：如果要求一个学生一行，一个学生可选多门课，则下面的“学生”表就不满足1NF： student(sno,sname,classno)其中：sno为学号，sname为学生姓名，classno为课程号。因为一个学生可选多门课，所以列classno有多个值，所以空不符合1NF。规范化就是把它分成如下两个表：“学生”表和“选课”表，则这两个表就都满足1NF了。student(sno,sname)stuclass(sno,classno)1.3.2NF对于满足2NF的表，除满足1NF外，非主码的列必须依赖于所有的主码，而不是组合主码的一部分。如果满足1NF的表的主码只有一列，则它自动满足2NF。例：下面的“选课”表，不符合2NF。stuclass(sno,classno,classname)其中：classname为课程名称。因为词表的主码是：(sno,classno),非主码列classname依赖于组合主码的一部分classno,所以它不符合2NF。对该表规范化也是把它分解成两个表：“选课”表和“课程”表，则它们就都满足2NF了。stuclass(sno,classno)class(classno,classname)1.4.3NF3NF的规则是除满足2NF外，任一非主码列不能依赖于其它非主码列。例：下面的“课程”表，不符合3NF。class(classno,classname,teacherno,teachername)其中：teacherno为任课教师号，teachername为任课教师姓名。因为非主码列teachername依赖于另一非主码列teacherno,所以它不符合3NF。其解决办法也是把它分解成两个表：“课程”表和“教师”表，则它们就都满足3NF了。class(classno,classname,teacherno)teacher(teacherno,teachername)1.5.小结当一个表是规范的，则其非主码列依赖于主码列。从关系模型的角度来看，表满足3NF最符合标准，这样的设计容易维护。一个完全规范化的设计并不总能生成最优的性能，因此通常是先按照3NF