MSSQL大批量数据量优化一

1　引　言

  数据库设计的重要标准之一，是具有快速的响应速度，特别是对客户/服务器形式的数据库开发，查询速度的快慢直接影响着数据库的推广和应用。对于大型数据库来说，这一点显得尤为突出。为了达到这一目的，除了在数据库的物理设计、关系规范化等方面进行改进外，一个简单、有效的方法是提高查询响应速度，这对已经投入运行的数据库，尤为重要。SQL SERVER6.5关系数据库管理系统提供了良好的数据管理能力和用户接口，但当数据量较大时，查询响应速度不够理想，因而，优化查询速度尤为重要。本文分析了实践过程总结出的SQL SERVER6.5中，提高查询性能的一些方案，对于其他关系数据库系统也适用。

2　分散数据存储优化查询

  数据库的性能主要与下列组成部分有关：CPU配置、磁盘I/O性能、网络速度。由于数据库管理系统主要进行检索操作，因而磁盘I/O速度对系统性能具有很大的影响。在实现中可以采取分散数据的存储，把数据分散到多个磁盘上，使不同的控制器使用不同的磁盘驱动器并行的操作，使数据库管理系统能够迅速的读取数据。对于网络，在带宽确定的情况下，可适当安排任务的执行时间，避免网络拥塞。

3　选取索引的字段优化查询性能

  在关系数据库系统的表上建立合适的索引可以极大提高查询的执行速度，索引指定表中记录的逻辑顺序，为查询提供优化的工具。索引分为聚集索引和非聚集索引，按照索引所包含的列数可分为单列索引和复合索引，SQL Server 6.5中，与索引有关的一个重要参数是填充因子，正确的设置索引的字段和填充因子，可以有效的提高系统性能。
3.1　索引设置的一般原则
  索引的有用性取决于数据查询的选取性，选取性基于对查询将返回的结果集占全部记录集的百分比。SQL Server 6.5中，查询优化器根据数据页和索引页的分布统计情况来给索引打分的方式来选取可用的索引，当分值低于阈值时采用表扫描，存在多个索引时选取分值最高，当表中存在多个索引时选取使用的索引不一定是最优的，而且索引过多会增加系统的开销，降低修改和插入的性能。此外，查询返回结果集的大小对索引的使用也有不同影响，当查询的结果集返回行数超过一定比例，索引对查询性能提高具有很小的作用，一般来说，表中数据量越大，查询返回的结果越少，索引的使用效果越明显。因而，应该分析对表的操作，建立合适的索引。
  考察select 语句中的where子句，这是查询优化器的首要的焦点。Where子句中包含的每列都是创建索引的候选者。但是对每列都建索引，同一个表拥有大量的索引，不仅多占空间，而且索引的动态维护将会增加系统开销，所以应考虑在频繁搜索的条件中，即where子句中经常使用的列才建索引。
  对于select 操作来说，索引的影响总是正面的。但对于update，delete来说，索引既有正面影响，又具有负面效应，update，delete操作的第一步是找到该记录，索引可以加快这步操作，但为维护索引的顺序，第二步必须修改索引，这又不可避免的影响update，delete操作的完成时间，权衡的原则是：对于update，delete操作比select操作频繁的表，尽量少建或不建索引，避免同一个表上的索引之间有相同的列存在。
3.2　聚集索引和非聚集索引
  聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。建议使用聚集索引的场合为：
  a.此列包含有限数目的不同值；
  b.查询的结果返回一个区间的值；
  c.查询的结果返回某值相同的大量结果集。
  非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。建议使用非聚集索引的场合为：
  a.此列包含了大量数目不同的值；
  b.查询的结束返回的是少量的结果集；
  c.order by 子句中使用了该列。
3.3　填充因子的设置
  不论是聚集还是非聚集索引，改变表中的记录时会引起索引顺序的改变，因为索引是严格排序的，所以新添加的索引项必须置于索引页的相应位置,为了空出位置以容纳新的索引项,从相应位置开始的所有索引项必须向后移动,这在SQL Server中称为“索引分裂”。这个过程会花费不少系统时间，从而降低了系统执行的速度。在SQL Server6.5中，为延缓索引改变，引入填充因子的概念，来指定每个索引页的填满程度，从而来延缓“索引”分裂的发生，可以适当的设置填充因子的大小，以改善索引的性能。对于具有大量插入、删除、修改操作的表，一般可以设为10%到20%，对于批量录入，很少改动的表，可设为80%以上，一般的用于事务处理的表的索引，可以设为50%左右。

4　尽量使用存储过程少用光标

  存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序。光标可以提供灵活的数据操作能力，但降低了系统的效率。因此，应尽量用存储过程来替代光标的使用。

5　高性能应用程序的设计

  在关系数据库中，除在数据库的物理设计、关系规范化等方面进行优化外，一个简单直接有效的方法是对SQL语句进行调整，减少计算量和内存需求，提高响应速度。
  a.对同一表格进行多个选择运算
  选择条件的排列顺序对性能有较大影响，因为不仅影响索引的选取，而且关系到临时表的大小。现以下面的查询语句为例进行说明：
  select * from customer
  where city=’beijing’ and fname=’li’
  若表中存在100万条记录，其中city=’beijing’的10万，fname=’li’的为2万，其中city=’beijing’的为2千，在SQL Server中，查询条件的选取是从左到右使用的，因而，执行第一个条件结果返回一个10万行的临时表，然后再从中进行选择，从而得到最终结果。如果把选择条件改为where fname=’li’ and city=’beijing’，则先得到一个2万行的临时表，再得到同样的结果。由此可见，选择条件的选取极大的影响着查询语句的计算量，所以，要提高查询的响应速度，可以将较严格的条件写在前面，较弱的条件放在后面。
  b.多个表格的联结选择运算
  联结运算是数据库中常用的运算之一，由于联结运算会产生很大的临时表，特别是数个表格的联结运算，不优化的SQL查询语句会产生巨大的计算量，对于联结选择运算，如果可以由嵌套SQL 语句实现，就采用嵌套SQL 语句形式减少数据量。例如：
  SELECT *
  FORM Customer ,Address
  Where Customer.Tel=Address.Tel
  And Customer.PayDate=’10-AUG-98’
  可以修改为：
  SELECT *
  FROM Address
  Where Tel=
  (SELECT Tel FROM Cutomer
  where PayDate=’10=zug-98’)
  对于不能改写的联结选择运算，可以采用调整表的排列循序和选择条件，减少临时表中的列数，优化系统性能。

6　结论

  在数据库的开发和维护过程中，查询的优化设计可以提高系统性能，对于数据量大的数据库系统，尤为重要。优化的方法除充分利用硬件性能外，可采用软件方法来解决，主要采用选择恰当的索引字段及设计高性能的应用程序以提高查询效率的方法来解决。本文主要从这两个方面分析了SQL SERVER中的优化方案，所提出的方法对其它的数据库系统也具有较好的应用和参考价值。