基于SQL Server的数据库性能优化方案研究

摘 要：性能是数据库应用的永恒话题，也是衡量一个项目成功与否的重要指标。但是，想让系统达到最优性能从来不是一件简单的任务，涉及多个层面：如硬件、网络、操作系统、数据库、应用软件、涉及时间周期、系统涉及、开发和测试、产品运行和维护等。本文重点探讨SQL Server 数据库在系统方案设计过程中的优化方案，主要从逻辑设计、主键、外键、物理设计、索引、锁等多个方面着手。

关键词： SQL Server；数据库；优化设计方案

性能调优不是一件简单的事，一般来说需要广泛的经验与知识，不单单是数据库的经验，还要对商业逻辑、系统架构设计、编写应用程序、操作系统、架设网络环境、使用各种监测与监控工具程序、安全与防毒等都有基本的了解，才能在复杂的系统中，找到症结所在。本从先从衡量性能的指標，然后对SQL Server数据库设计中的多个方面进行探讨。

1、衡量性能问题的关键指标

1.1 响应时间（ResponseTime）

一般指的是一条SQL语句执行后得出结果耗费的时间。而一般用户使用来说，比如BS结构，响应时间大家一般会认为是访问页面到页面呈现结束，这样的感官时间。这个时间就需要考虑更多的因素。比如网络、浏览器等等。曾经碰到的CASE页面打开速度超慢，但是数据库正常，后来发现是页面中潜入的一个很小的GIF影响了，所以要系统来分析。

而执行SQL语句获得的响应时间是最为纯粹的反馈，也是能够得到准备信息的步骤。在系统跟踪的话，可以用SQLprofile来跟踪响应的内容，分析语句的反馈时间，之后再来详细讲解。

1.2 吞吐量（Thougput）

反映系统到底有多繁忙的指标，了解此指标可以更为清晰的知晓系统的使用状况。性能监视器中可以用SQLBatchRequest/Sec，SQLTransactions/Sec等指标来获取。

1.3 基线（BaseLine）

反映系统日常状况的指标，如果知晓了系统的各种基线值。那么就清楚了底在哪里，顶在哪里。这样才能更容易去判断和解决问题。基线值需要依靠长期经验和数据获取的。

1.4 瓶颈（bottleneck）

系统一旦产生了瓶颈，我们就要去判断瓶颈，而瓶颈一般来说多会有关联性。比如内存不足可能导致IO过高，IO过高也可能导致CPU等待。所以准确的知道瓶颈在哪里，这是需要去判断的。使用性能监视器和分析功能可以快捷的帮助大家分析瓶颈。

数据库性能优化的基本原则就是：通过尽可能少的磁盘访问获得所需要的数据。 数据库的优化应该采取自顶向下的优化原则，从需求设计阶段到数据库运行阶段进行控制。在设计阶段：对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下，系统性能达到最佳，系统开销达到最小；数据库运行阶段：采取操作系统级、数据库级的一些优化措施来使系统性能最佳。

评价数据库的性能，需要在数据库调节前后比较其评价指标即响应时间和吞吐量之间的权衡、数据库的可用性、数据库的命中率以及内存的使用效率，以此来衡量调节措施的效果和指导调整的方向。

2数据库设计

2.1逻辑设计的规范化

数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

第一规范：是关于消除重复数据组和保证原子性（数据是自包含和独立）的规范化信息，这是数据库设计的最低要求。 第二规范： 是进一步减少重复数据的出现（不一定是数据组），第二范式有以下两个规则：表必须符合第一范式的规则（规范化是一个类似于堆积模块的过程，如果没有前面的积木就不能堆第三块）；每列必须依赖于整个键。大部分情况下，数据库设计都应该达到第二范式。 第三规范：这只是规范化方面的一个相对终点，第三范式有三个规则：表必须符合第二范式；任何列都不能依赖于非键列；不可以有派生的数据。 达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

2.2冗余

完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

2.3主键的设计

主键是每行的唯一标识符，必须包含唯一的值（因此不能为NULL）。由于主键在关系数据库中的重要性，因此它是所有键和约束中最重要的。主键是必要的，SQL Server的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。 在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

2.4 外键

外键既能确保数据完整性，也能表现表之间的关系。如果添加外键到一个表，就在定义的外键的表和外键引用的表之间创建依赖关系。外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序。

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

A、数据类型尽量用数字型，数字型的比较比字符型的快很多。

B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

E、 自增字段要慎用，不利于数据迁移。

2.6数据库物理存储和环境的设计

在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

2.7 系统设计

整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。

系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

2.8 索引的设计

索引是数据库规划和系统维护的一个关键部分，他们为SQL Server 以及任何其他的数据库系统提供了查找数据和定位到数据物理位置的快捷方式的其他方法，通过添加正确的所以可以大大减少查询执行时间。

在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

关于索引的选择，应改主意： A、 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。 B、 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。 C、 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重復率低的字段放在前面。 D、 一个表不要加太多索引，因为索引影响插入和更新的速度。

2.9物理结构设计

为一个给定的逻辑数据模型设计一个最合适应用要求的物理结构的过程。数据库的建立、数据表的建立、索引的建立、视图的建立、 触发器的建立、 存储过程设计、用户自定义函数设计、对关系模式的数据项加以约束，如检查约束、主键约束、参照完整性约束以保证数据正确性等。

2.10 锁升级和锁对性能的影响

锁是一种防止在某对象执行动作的一个进程与已在该对象上执行的其他进程相冲突的机制。也就是说，如果其他人在执行某个对象，那么就不能在该对象上进行操作。锁也在描述正在执行的操作的一种方式，因此系统可以知道第二个进程动作是否和第一个集成相兼容。如系统可处理的1个、2个、10个、100个或者任意多个用户连接数，通常可能在同一时间共享同一块数据，只要他们都是只读访问记录。SQL Server的锁管理器就是管理多个用户连接模式的。

升级是指能够认识到维持一个较细的粒度（例如，行锁不是页锁）只在被锁定的项数较少时有意义。而随着越来越多的项被锁定，维护这些锁的系统开销实际上会影响性能。这会导致锁持续更长的时间（这样会产生争议问题—锁的时间越长，其他用户想使用这个特定记录的可能性越大）。当考虑这个问题的时候，可能会需要一种权衡的办法，这个就是锁管理器使用升级的用意。当维持锁的数量达到一定的限度时，则锁升级为下一个更高的层次，并且将不需要再如此紧密地管理低层次的锁（释放资源，而且有助于提高速度）。

3 结束语

优化是一个持续的过程，永无止境，解决了当前“最大”的瓶颈后，下一个“最大”的瓶颈又会出现要知道何时停止优化优化的内容应该是基于业务需求的优化关注二投资回报率（ROI），工程师的时间也是投入，因此要懂得投资回报，需要懂得停止优化！改变选项是最有意义的优化策略，有的优化是业务决定，那么无法改变的时候是否可以改变业务逻辑。实际上，足够好的性能就足够了。很多时候足够即可，而不是去寻找极限！

作者简介：

苗改梅，女（1988 --），山西，硕士，工程师，研究方向：企业信息化.