软件测试开发技术之数据库优化设计方案

关键字：数据库 优化

　　本文首先讨论了基于第三范式的数据库表的基本设计，着重论述了建立主键和索引的策略和方案，然后从数据库表的扩展设计和库表对象的放置等角度概述了数据库管理系统的优化方案。

　　1 引言

　　数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。为了便于读者阅读和理解，笔者参阅了Sybase、Informix和Oracle等大型数据库系统参考资料，基于多年的工程实践经验，从基本表设计、扩展设计和数据库表对象放置等角度进行讨论，着重讨论了如何避免磁盘I/O瓶颈和减少资源竞争，相信读者会一目了然。

　　2 基于第三范式的基本表设计

　　在基于表驱动的信息管理系统(MIS)中，基本表的设计规范是第三范式(3NF)。第三范式的基本特征是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点：一是消除了冗余数据，节省了磁盘存储空间;二是有良好的数据完整性限制，即基于主外键的参照完整限制和基于主键的实体完整性限制，这使得数据容易维护，也容易移植和更新;三是数据的可逆性好，在做连接(Join)查询或者合并表时不遗漏、也不重复;四是因消除了冗余数据(冗余列)， 在查询(Select)时每个数据页存的数据行就多，这样就有效地减少了逻辑I/O，每个Cash存的页面就多，也减少物理I/O;五是对大多数事务(Transaction)而言，运行性能好;六是物理设计(Physical Design)的机动性较大，能满足日益增长的用户需求。

　　在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论这些基本概念及其重要意义：

　　(1)主键(Primary Key)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值(不能为Null或缺省值，要有相对稳定性)，不含代码信息，易访问。把常用(众所周知)的列作为主键才有意义。短主键最佳(小于25bytes)，主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成，自然主键可以是复合性的，在形成复合主键时，主键列不能太多，复合主键使得Join*作复杂化、也增加了外键表的大小。人为主键是，在没有合适的自然属性键、或自然属性复杂或灵敏度高时，人为形成的。人为主键一般是整型值(满足最小化要求)，没有实际意义，也略微增加了表的大小;但减少了把它作为外键的表的大小。

　　(2)外键(Foreign Key)：外键的作用是建立关系型数据库中表之间的关系(参照完整性)，主键只能从独立的实体迁移到非独立的实体，成为后者的一个属性，被称为外键。

　　(3)索引(Index)：利用索引优化系统性能是显而易见的，对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间;利用索引可以优化或排除耗时的分类*作;把数据分散到不同的页面上，就分散了插入的数据;主键自动建立了唯一索引，因此唯一索引也能确保数据的唯一性(即实体完整性);索引码越小，定位就越直接;新建的索引效能最好，因此定期更新索引非常必要。索引也有代价：有空间开销，建立它也要花费时间，在进行Insert、Delete和Update*作时，也有维护代价。索引有两种：聚族索引和非聚族索引。一个表只能有一个聚族索引，可有多个非聚族索引。使用聚族索引查询数据要比使用非聚族索引快。在建索引前，应利用数据库系统函数估算索引的大小。

　　① 聚族索引(Clustered Index)：聚族索引的数据页按物理有序储存，占用空间小。选择策略是，被用于Where子句的列：包括范围查询、模糊查询或高度重复的列(连续磁盘扫描);被用于连接Join*作的列;被用于Order by和Group by子句的列。聚族索引不利于插入*作，另外没有必要用主键建聚族索引。

原文转自：http://www.ltesting.net