论文部分内容阅读
摘要：该文根据作者多年从事数据库应用系统开发及教学方面的经验，分析了SQL Server数据库在三个层次的性能优化及其注意事项，并介绍了进行性能优化所使用的工具。
关键词：数据库；性能；优化
中图分类号：TP311文献标识码：A文章编号：1009-3044(2012)23-5516-02
Analysis of SQL Server Database Performance Optimization
GAO Shui-juan
(Department of Electronic Information Engineering,Nanjing Communications Institute of Technology, Nanjing 211188,China)
Abstract:According to the author for many years engaged in the database application system development and teaching experience, analysis of the SQL Server database in three levels of performance optimization and its attention to matters, and introduced the optimization tools.
Key words：database；performance；optimization
SQL Server作为一种重要的关系型数据库，具有和强大的管理功能和安全性能，被广泛应用于后台数据库，在数据库应用系统中，数据库的性能对整个软件的性能起着重要的决定作用，因此，如何提高数据库的性能，是当前研究的重点。
一个应用软件与数据库管理系统结合起来构成了一个非常复杂的系统，它的许多不同的方面都可以被优化。最低层次的性能优化是硬件层次的调优。为了提高性能，系统必须提供大量的主存空间、足够多的CPU和二级存储设备，以备足够的通信能力。数据库管理系统处于上一层，这一层的性能问题包括磁盘中的物理数据组织、缓冲区管理等。这个层次的决策很大程度上是数据库管理员的管理范围，因此应用程序员只能间接对其产生影响。SQL代码和模式处于最高层，在这一层进行性能优化涉及的问题包括如何表达一个查询、创建什么样的索引等。
该文并不是对如何优化数据库性能作详尽的阐述，而是根据作者这几年从事数据库应用系统开发及教学方面的经验，提出以下三个方面对数据库性能优化有所增益，以供大家参考。
1高速缓存调优
我们先来了解一下高速缓存是如何工作的。高速缓存是数据库管理系统内部的主存缓冲区，最近被访问的数据库页被存放在高速缓存中，当事务访问一个数据库数据项的时候，数据库管理系统将包含这一项的数据库磁盘页读入高速缓存，然后把这一项的值从高速缓存拷入应用程序缓冲区。这一页通常被保留在高速缓存中，我们可以合理假设，应用程序稍后更新这个数据项或读取同一页中另外一项的可能性非常大，或者另外一个应用程序可能在并行地访问这一页中的一项，由于可以直接从高速缓存中访问这一页，这两种情况都可以节省一次磁盘访问。如果一个数据库数据项被更新，包含这一项的数据库页必须先被读入高速缓存，高速缓存中的这一页就是被修改的页的高速缓存版本。最终高速缓存将被填满，从数据库中获取的任何一个新页必须覆盖高速缓存中的一页p，如果自从p被读入高速缓存还没有被修改过，高速缓存页的内容与数据库页的内容是一致的，因此新的一页可以直接覆盖它。然而如果自从p被读入高速缓存后被更新过，在其占据的高速缓存页被覆盖前必须将其写回数据库。那么应用程序设计人员及数据库管理员如何优化数据库管理系统利用高速缓存的方式来提高应用程序的性能呢？
1）SQL Server数据库管理系统允许高速缓存的一部分被分割出来，作为一个独立的高速缓存。然后程序员可以把一个特定的数据库数据项与这个分出来的高速缓存绑定在一起，以便这个数据库数据项的所有页都被缓冲在高速缓存中。例如，T1表和T2表被绑定在不同的高速缓存中，T1表的一页便不可能覆盖T2表的一页。如果对T2表的访问不太频繁但访问它的应用需要快速的响应，这个方法是可行的。
2）SQL Server数据库管理系统允许把一个特定的高速缓存分割成几个大小不同的缓冲区池。例如在默认情况下，高速缓存中的缓冲区大小可能为2K，我们可以重新分配高速缓存，分别创建大小为2K、4K、8K的缓冲区池。如果一个表被装入这样的高速缓存，查询优化器可以选择每次I/O传输的数据量的大小，使其更加适应访问这个表的查询计划。
3）SQL Server数据库管理系统提供了可以指定某个特定高速缓存的页替换策略的命令，在利用多个高速缓存的时候，这个命令尤其有用。可以利用这个命令指定对高速缓存中的数据项进行替换的恰当策略。
2模式调优
当我们为一个特定应用设计的数据库模式处于应用的核心时，如果模式设计得好，可能设计出高效的SQL语句。我们在应用层的调优策略首先就应该是设计一个规范化的数据库，并估计表的大小、属性值的分布、查询的特征及其执行频率，以及可能对数据库执行的更新等。对规范化的模式进行调整以提高操作的执行效率是依赖于上述这些估计的。添加索引、反向规范化及分片都是重要的调优方法。
1）索引
索引看上去可能是最重要的数据库调优技术，但索引也带来了相关的存储开销。更为重要的是，额外的索引可能极大地增大对数据库进行修改的语句的处理时间，这是因为每当索引对应的表被更新的时候，对应的索引也要更新。当创建索引而带来性能增益时，我们也要思考这是否足以补偿处理对表进行修改的语句的额外开销。因此在创建索引时我们要作如下考虑： （1）由于一个表最多只能有一个聚集索引，我们不能将这个索引浪费在不能利用聚集索引的优势的属性上。数据库管理系统通常在主码上创建了一个聚集索引，但主码上的非聚集索引也能确保主码值的唯一性，因此如果我们不想按照主码对行进行排序，我们就不应该在主码上创建聚集索引。用另外一个聚集索引替换一个已经存在的聚集索引也是一件非常耗时工作，因为在这个过程中包含对存储结构的完全重组。所以应该事先对应用进行分析，考虑应用可能会执行哪些查询以及每个查询的执行频率，从而创建一个可能带来最大收益的聚集索引。
（2）只有获取整个表的很少一部分行的时候，利用非聚集索引才是恰当的。如果获取整个表的大部分行，全表扫描是恰当的。另外如果属性值倾向于在域内均匀分布，不要在对应一个较小的域的列上创建索引。
（3）聚集把可能作为一个结果集输出的行组合在一起是非常有用的。这些行能够被组合在一起是因为这些行在一个属性上具有相同的属性值，或者这些行在一个属性值上的属性值都落在一个范围内。在各种情况下，当必须选择在哪个属性上进行聚集时，应该基于对应用程序对应的结果集的大小的估计来进行选择。
2）反向规范化
反向规范化是指通过一个表中添加冗余信息来设法提高只读查询的性能。这个过程是关系规范化的逆过程。应用程序设计的开始步骤之一就是表的设计，表是数据存储的载体，因此在设计之初就应该做好规范化工作。非规范化关系存在着许多的缺点，但有时候为了性能的优化，却不得不按昭反向规范化思想来设计关系模式。这主要是应用在某些特定的查询，如不需要经常更新的关系，反向规范化反而能提高效率。因此在设计档案数据时采用反向规范化方法，在设计在线数据时采用规范化方法。
3）分片
通过显式地把一个表分成多个片段，这样可以把表中频繁访问的数据与很少访问的数据区分开来，从而降低访问非常大的表的开销。利用水平分片，每个片段具有相同的列集合，包含了整个表的所有行的一个子集。对行进行分片都基于把所有行划分成不相交的子集，如在学生信息表中可以将已毕业的学生和当前在读的学生分成两个片段，因为对当前在读的学生的访问量要大得多的多。利用垂直分片，表中所有列的子集构成了一个片段。当一个表有很多列的时候，这可能是有用的，因为这样的表包含很多行，每一行中的某些列都很少被访问。如果没有分片，当获取所需要的列对应的数据的时候，还要把不需要的列对应的数据从磁盘中传入缓冲区，这严重降低了性能。是否分片，必须要权衡，因为在分片的情况下，付出的代价就是增加了访问及管理多个表的额外复杂性，因此只有在性能收益非常明显的情况下才能使用这个技术。
3数据操纵语言调优
数据库的查询效率是衡量数据库性能的一个重要的指标，但SQL Server的查询会消耗大量资源，在保证查询效率的同时也产生了一些负面的影响，下面就如何优化查询和提高查询性能提出以下见解。
1）避免排序
排序的开销很大，所以尽量避免排序。我们首先要了解哪些类型的查询可能导致查询优化器在查询计划中引入排序，这样可以避免执行这类的查询。消除重复包含了排序操作，因此除非对应用来说非常重要，否则不要用DISTINCT关键词；集合操作（如UNION及EXCEPT）也会引入排序操作以消除重复；对于处理ORDER BY子句来说排序是不可避免的，因此应该考虑清楚对结果排序是不是必须的。
2）避免不必要的扫描
在WHERE子句中包含“不等于”条件可能会导致一次扫描，对于WHERE LIKE等条件仍旧需要表扫描。因此在查询子句中最好避免这样的条件出现。
3）使通信量最小化
通常，客户机与服务器之间的通信开销非常大，因此应该尽可能地降低通信开销。带来通信开销的主要原因之一是利用游标，每获取一行都要引发一次通信，因此在更新一个表时设法利用UPDATE语句，而不是获取一行后修改并写回。另外在检索聚集信息时，可以利用存储过程计算聚集，只返回结果值到客户端，如果必须在应用程序代码中分析表中的每一行，就要检查使用的数据库管理系统是否支持一次获取多行，我们所使用的SQL Server数据库正是支持这一应用的，所以就可成组获取以降低通信开销。
4调优工具
数据库管理系统供应商提供了各种用于对数据库进行调优的工具。这些工具通常情况下需要创建一个试验数据库，在这个数据库里对各种查询执行计划进行试验。在大多数数据库管理系统中，一个这样的典型工具是EXPLAIN PLAN语句，使数据库管理系统生成一个查询执行计划，并把这个查询执行计划当成一个元组集存储在PLAN_TABLE关系中，然后可以通过执行对PLAN_TA BLE表的查询来获取这个查询执行计划。基于文本的查询执行计划的检测功能非常强大，但是目前只有热衷于这种方式的人才使用。大多数数据库管理系统都提供了图形界面的调优工具，如微软提供了Query Analyzer工具，这些工具不仅显示查询计划，而且能够建议你创建索引以提高各种查询的执行速度。
参考文献：
[1]魏银珍,陈征兵. SQL Server数据库的查询优化策略研究[J].电脑知识与技术2011(7).
[2]徐丽媛,张亚宾.基于SQL Server数据库查询优化的几点思考[J].科技信息2010(20).
[3]李荣瀚.SQL Server数据库性能优化[J].科技探索,2011(3).
[4]余秋风.试论SQL Server数据库性能的优化[J].信息与电脑,2012(4).
[5]吴春庆.数据库性能优化主要方法[J].硅谷,2010(3).
[6] Robert Vieira.SQL Server2005高级程序设计[M].北京:人民邮电出版社,2008.
[7]杨晖.SQLServer数据库性能优调技术分析[J].信息与电脑,2011 (11).
[8]年玮.SQLSERVER数据库性能优化分析[J].信息与电脑,2010 (8).
关键词：数据库；性能；优化
中图分类号：TP311文献标识码：A文章编号：1009-3044(2012)23-5516-02
Analysis of SQL Server Database Performance Optimization
GAO Shui-juan
(Department of Electronic Information Engineering,Nanjing Communications Institute of Technology, Nanjing 211188,China)
Abstract:According to the author for many years engaged in the database application system development and teaching experience, analysis of the SQL Server database in three levels of performance optimization and its attention to matters, and introduced the optimization tools.
Key words：database；performance；optimization
SQL Server作为一种重要的关系型数据库，具有和强大的管理功能和安全性能，被广泛应用于后台数据库，在数据库应用系统中，数据库的性能对整个软件的性能起着重要的决定作用，因此，如何提高数据库的性能，是当前研究的重点。
一个应用软件与数据库管理系统结合起来构成了一个非常复杂的系统，它的许多不同的方面都可以被优化。最低层次的性能优化是硬件层次的调优。为了提高性能，系统必须提供大量的主存空间、足够多的CPU和二级存储设备，以备足够的通信能力。数据库管理系统处于上一层，这一层的性能问题包括磁盘中的物理数据组织、缓冲区管理等。这个层次的决策很大程度上是数据库管理员的管理范围，因此应用程序员只能间接对其产生影响。SQL代码和模式处于最高层，在这一层进行性能优化涉及的问题包括如何表达一个查询、创建什么样的索引等。
该文并不是对如何优化数据库性能作详尽的阐述，而是根据作者这几年从事数据库应用系统开发及教学方面的经验，提出以下三个方面对数据库性能优化有所增益，以供大家参考。
1高速缓存调优
我们先来了解一下高速缓存是如何工作的。高速缓存是数据库管理系统内部的主存缓冲区，最近被访问的数据库页被存放在高速缓存中，当事务访问一个数据库数据项的时候，数据库管理系统将包含这一项的数据库磁盘页读入高速缓存，然后把这一项的值从高速缓存拷入应用程序缓冲区。这一页通常被保留在高速缓存中，我们可以合理假设，应用程序稍后更新这个数据项或读取同一页中另外一项的可能性非常大，或者另外一个应用程序可能在并行地访问这一页中的一项，由于可以直接从高速缓存中访问这一页，这两种情况都可以节省一次磁盘访问。如果一个数据库数据项被更新，包含这一项的数据库页必须先被读入高速缓存，高速缓存中的这一页就是被修改的页的高速缓存版本。最终高速缓存将被填满，从数据库中获取的任何一个新页必须覆盖高速缓存中的一页p，如果自从p被读入高速缓存还没有被修改过，高速缓存页的内容与数据库页的内容是一致的，因此新的一页可以直接覆盖它。然而如果自从p被读入高速缓存后被更新过，在其占据的高速缓存页被覆盖前必须将其写回数据库。那么应用程序设计人员及数据库管理员如何优化数据库管理系统利用高速缓存的方式来提高应用程序的性能呢？
1）SQL Server数据库管理系统允许高速缓存的一部分被分割出来，作为一个独立的高速缓存。然后程序员可以把一个特定的数据库数据项与这个分出来的高速缓存绑定在一起，以便这个数据库数据项的所有页都被缓冲在高速缓存中。例如，T1表和T2表被绑定在不同的高速缓存中，T1表的一页便不可能覆盖T2表的一页。如果对T2表的访问不太频繁但访问它的应用需要快速的响应，这个方法是可行的。
2）SQL Server数据库管理系统允许把一个特定的高速缓存分割成几个大小不同的缓冲区池。例如在默认情况下，高速缓存中的缓冲区大小可能为2K，我们可以重新分配高速缓存，分别创建大小为2K、4K、8K的缓冲区池。如果一个表被装入这样的高速缓存，查询优化器可以选择每次I/O传输的数据量的大小，使其更加适应访问这个表的查询计划。
3）SQL Server数据库管理系统提供了可以指定某个特定高速缓存的页替换策略的命令，在利用多个高速缓存的时候，这个命令尤其有用。可以利用这个命令指定对高速缓存中的数据项进行替换的恰当策略。
2模式调优
当我们为一个特定应用设计的数据库模式处于应用的核心时，如果模式设计得好，可能设计出高效的SQL语句。我们在应用层的调优策略首先就应该是设计一个规范化的数据库，并估计表的大小、属性值的分布、查询的特征及其执行频率，以及可能对数据库执行的更新等。对规范化的模式进行调整以提高操作的执行效率是依赖于上述这些估计的。添加索引、反向规范化及分片都是重要的调优方法。
1）索引
索引看上去可能是最重要的数据库调优技术，但索引也带来了相关的存储开销。更为重要的是，额外的索引可能极大地增大对数据库进行修改的语句的处理时间，这是因为每当索引对应的表被更新的时候，对应的索引也要更新。当创建索引而带来性能增益时，我们也要思考这是否足以补偿处理对表进行修改的语句的额外开销。因此在创建索引时我们要作如下考虑： （1）由于一个表最多只能有一个聚集索引，我们不能将这个索引浪费在不能利用聚集索引的优势的属性上。数据库管理系统通常在主码上创建了一个聚集索引，但主码上的非聚集索引也能确保主码值的唯一性，因此如果我们不想按照主码对行进行排序，我们就不应该在主码上创建聚集索引。用另外一个聚集索引替换一个已经存在的聚集索引也是一件非常耗时工作，因为在这个过程中包含对存储结构的完全重组。所以应该事先对应用进行分析，考虑应用可能会执行哪些查询以及每个查询的执行频率，从而创建一个可能带来最大收益的聚集索引。
（2）只有获取整个表的很少一部分行的时候，利用非聚集索引才是恰当的。如果获取整个表的大部分行，全表扫描是恰当的。另外如果属性值倾向于在域内均匀分布，不要在对应一个较小的域的列上创建索引。
（3）聚集把可能作为一个结果集输出的行组合在一起是非常有用的。这些行能够被组合在一起是因为这些行在一个属性上具有相同的属性值，或者这些行在一个属性值上的属性值都落在一个范围内。在各种情况下，当必须选择在哪个属性上进行聚集时，应该基于对应用程序对应的结果集的大小的估计来进行选择。
2）反向规范化
反向规范化是指通过一个表中添加冗余信息来设法提高只读查询的性能。这个过程是关系规范化的逆过程。应用程序设计的开始步骤之一就是表的设计，表是数据存储的载体，因此在设计之初就应该做好规范化工作。非规范化关系存在着许多的缺点，但有时候为了性能的优化，却不得不按昭反向规范化思想来设计关系模式。这主要是应用在某些特定的查询，如不需要经常更新的关系，反向规范化反而能提高效率。因此在设计档案数据时采用反向规范化方法，在设计在线数据时采用规范化方法。
3）分片
通过显式地把一个表分成多个片段，这样可以把表中频繁访问的数据与很少访问的数据区分开来，从而降低访问非常大的表的开销。利用水平分片，每个片段具有相同的列集合，包含了整个表的所有行的一个子集。对行进行分片都基于把所有行划分成不相交的子集，如在学生信息表中可以将已毕业的学生和当前在读的学生分成两个片段，因为对当前在读的学生的访问量要大得多的多。利用垂直分片，表中所有列的子集构成了一个片段。当一个表有很多列的时候，这可能是有用的，因为这样的表包含很多行，每一行中的某些列都很少被访问。如果没有分片，当获取所需要的列对应的数据的时候，还要把不需要的列对应的数据从磁盘中传入缓冲区，这严重降低了性能。是否分片，必须要权衡，因为在分片的情况下，付出的代价就是增加了访问及管理多个表的额外复杂性，因此只有在性能收益非常明显的情况下才能使用这个技术。
3数据操纵语言调优
数据库的查询效率是衡量数据库性能的一个重要的指标，但SQL Server的查询会消耗大量资源，在保证查询效率的同时也产生了一些负面的影响，下面就如何优化查询和提高查询性能提出以下见解。
1）避免排序
排序的开销很大，所以尽量避免排序。我们首先要了解哪些类型的查询可能导致查询优化器在查询计划中引入排序，这样可以避免执行这类的查询。消除重复包含了排序操作，因此除非对应用来说非常重要，否则不要用DISTINCT关键词；集合操作（如UNION及EXCEPT）也会引入排序操作以消除重复；对于处理ORDER BY子句来说排序是不可避免的，因此应该考虑清楚对结果排序是不是必须的。
2）避免不必要的扫描
在WHERE子句中包含“不等于”条件可能会导致一次扫描，对于WHERE LIKE等条件仍旧需要表扫描。因此在查询子句中最好避免这样的条件出现。
3）使通信量最小化
通常，客户机与服务器之间的通信开销非常大，因此应该尽可能地降低通信开销。带来通信开销的主要原因之一是利用游标，每获取一行都要引发一次通信，因此在更新一个表时设法利用UPDATE语句，而不是获取一行后修改并写回。另外在检索聚集信息时，可以利用存储过程计算聚集，只返回结果值到客户端，如果必须在应用程序代码中分析表中的每一行，就要检查使用的数据库管理系统是否支持一次获取多行，我们所使用的SQL Server数据库正是支持这一应用的，所以就可成组获取以降低通信开销。
4调优工具
数据库管理系统供应商提供了各种用于对数据库进行调优的工具。这些工具通常情况下需要创建一个试验数据库，在这个数据库里对各种查询执行计划进行试验。在大多数数据库管理系统中，一个这样的典型工具是EXPLAIN PLAN语句，使数据库管理系统生成一个查询执行计划，并把这个查询执行计划当成一个元组集存储在PLAN_TABLE关系中，然后可以通过执行对PLAN_TA BLE表的查询来获取这个查询执行计划。基于文本的查询执行计划的检测功能非常强大，但是目前只有热衷于这种方式的人才使用。大多数数据库管理系统都提供了图形界面的调优工具，如微软提供了Query Analyzer工具，这些工具不仅显示查询计划，而且能够建议你创建索引以提高各种查询的执行速度。
参考文献：
[1]魏银珍,陈征兵. SQL Server数据库的查询优化策略研究[J].电脑知识与技术2011(7).
[2]徐丽媛,张亚宾.基于SQL Server数据库查询优化的几点思考[J].科技信息2010(20).
[3]李荣瀚.SQL Server数据库性能优化[J].科技探索,2011(3).
[4]余秋风.试论SQL Server数据库性能的优化[J].信息与电脑,2012(4).
[5]吴春庆.数据库性能优化主要方法[J].硅谷,2010(3).
[6] Robert Vieira.SQL Server2005高级程序设计[M].北京:人民邮电出版社,2008.
[7]杨晖.SQLServer数据库性能优调技术分析[J].信息与电脑,2011 (11).
[8]年玮.SQLSERVER数据库性能优化分析[J].信息与电脑,2010 (8).