论文部分内容阅读
摘要：应用SQL不仅要确保它的正确性，同时也要保证SQL有良好的执行性能。该文分析介绍了几种常用的SQL优化方法。论述了SQL Server数据库优化机制。
关键词：SQL Server；数据库；优化
中图分类号：TP311文献标识码：A文章编号：1009-3044(2008)35-2536-02
SQL Optimization Based on SQL Database
YANG Ya-ping
(Chien-shiung Institute of Technology, Taicang 215411, China)
Abstract: Not only Correctness but also efficiency one writes SQL statements. This article introduces and analyses of SQL, which include proper indexes design .This paper research，some questions of SQL performance optimization with SQ L SERVER such as operator operation. some suggestion of optimization bate on pure theory would misadvise user of SQL.
Key words: SQL Server; database; optimization
1 前言
微软公司的SQL Server数据库因为其价格便宜、功能强大、容易操作等特点已经被中国中小企业广泛应用，但是SQL Server数据库优化是一个常常被人们忽视的问题。甚至有人认SQL Server数据库优化并不重要，但事实是SQL Server数据库是一种高性能的数据库管理系统，在客户/服务器体系结构中得到了广泛的应用。基于SQL Server的数据库应用系统的性能包括系统的响应时间、吞吐量、系统的并行处理能力等。从数据库设计、查询结构、并行处理、客户/服务器模式、应用编程等方面进行优化调整，可以有效地提高系统的性能。SQL Server数据库的优化方法还有很多，比如优化内存、优化数据库磁盘等等。下面就SQL Server环境下的SQL优化方法探讨做些简单的探讨。
2 与SQL Server数据库有关的硬件设计性能的优化
与SQL Server有关的硬件设计包括CPU、内存、磁盘子系统和网络。
2.1 系统处理器（CPU）
根据自己的具体需要确定CPU结构的过程就是估计在硬件平台上占用CPU的工作量的过程。我们可以使用 System Monitor确定CPU的使用率，如果以75%或更高的速率长时间运行，就可能碰到了CPU瓶颈问题，这时应该升级CPU。
2.2 内存（RAM）
为SQL Server方案确定合适的内存设置对实现良好的性能是至关重要的。SQL Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多能利用 2GB虚拟内存，这也是最大的设置值。还有点必须考虑的是Windows2003 Server和它的所有和关服务也要占用内存。
2.3 磁盘I/O系统
设计1个好的磁盘I/O系统是实现良好的SQL Server方案的一个很重要的方面。这里讨论的磁盘I/O系统至少有1个磁盘控制设备和1个或多个硬盘单元，考虑到磁盘设置和文件系统，有如下特点：控制器有高速缓存；总线主板上有处理器，可以减少对系统CPU的中断；异步读写支持；32位RAID支持；高速SCSTU 320控制器，读写速度320M B/S。
由I/O系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件有关的问题。配置很差的I/O系统引起性能问题的严重程度仅次于编写很差的SQL语句。解决I/O系统有关的问题最简单的办法就是增加磁盘驱动器。
3 SQL Server数据库的查询优化
查询优化首先要注意的一个问题是存储过程，设计优良的应用程序都应当使用存储过程，不论是否将应用程序的业务逻辑写入存储过程都应如此，甚至连没有业务逻辑组件的标准SQL语句，在用参数打包成存储过程后也能获得性能收益，编译进存储过程的SQL语句在执行时可省去大量的处理。
存储过程的另一个优点是客户端执行请求使用网络的效率往往比将等效的SQL语句发送到服务器高。例如。在SQL Server中假设应用程序需要将一个人的二进制值插入image数据列，为使用INSERT语句发送数据，该应用程序必须将该二进制值换为字符串(其大小翻两倍)，然后发送到服务器，服务器再将该值转换回二进制格式以存储在image列中。相反，应用程序可以创建下列格式的存储过程：CREATE PROCEDURE Procedurel (l pl image)ASINSERT Tablel VALUES (a pl)。当客户端应用程序请求执行过程P时，image参数值将一直以二进制格式发送到服务器，从而节省处理时间并减少网络流量。数据库系统的并发控制也是影响性能的一个重要方面。为了避免多个用户同时操作可能导致的数据不一致，SQL Server采用了封锁机制。SQL Server中的锁可分为共享锁(shared lock)：由读取页的进程所使用。共享锁只在特定页的读取过程中有效。修改锁(update lock)，用于将要修改数据的进程，当数据发生变化时，修改锁自动改为独占锁。独占锁(exclusive lock)，用于当前正在修改数据的进程。独占锁作用于所有影响到的页上直至事务结束。
4 SQL Server数据库设计的优化
4.1 SQL Server数据库的规范化
数据库设计方面包括表的设计、表的数量和大小、表的设计规范程序、游标的使用等等都会对运行效率有重要影响。其中涉及到数据库规范化问题时，数据库被规范化后，减少了数据的冗余，数据行趋向变窄这样SQL SERVER数据库的每页可以包含更多的行，从而加速表的扫描和返回多行的查询，改进了单个表的查询性能。但是，另一方面，当查询涉及多个表时，则需要更多的连接把信息从多个表中组合到一起，这会引起大量的CPU和I/O处理。所以有时有意违反规范化设计的规则，这是数据库的非规范化。就像刚才说的数据库的规范化原则会产生较少的列和更多的表，但表关系也许需要通过复杂的合并来处理, 这样会降低系统的性能。在某些情况下，非规范化可以改善系统的性能，通过重新定义实体以减少外部属性数据或行数据的开支。
4.2 SQL Server数据库索引的选择
索引是常见的数据库对象，它的设置好坏、使用是否得当，极大地影响数据库应用程序和数据库的性能。在良好的数据库设计基础上，能有效地使用索引是 SQL SERVER取得高性能的基础。如果对于一个未建立索引的表执行查询操作， SQL SERVER必须进行表扫描，从磁盘上读表的每一个数据页，从而挑选出所有符合条件的数据行。特别是当一个表有很多行时，就会浪费大量时间，效率太低。然而在建立索引之后，SQL SERVER将根据索引的指示，直接定位到需要查询的数据行，从而加快 SQL SERVER 的数据检索操作。这样利用索引可以避免表扫描，并减少因查询而造成的 I/O 开销。
建立索引是以最小的方法得到所需数据的有效方法。对于每一个查询SQL Server优化器将确定是否有相关的索引可以用于访问数据。一个利用索引的访问与全表扫描相比，可以大大减少查询时间。索引可分为聚集索引与非聚集索引两种，前者对数据进行物理排序，速度快，但一个表只能建立一个；后者仅对数据进行逻辑排序，速度相对聚集索引慢，但一个表可以建立多个。索引的建立虽然加快了查询，另一方面却降低了数据更新的速度，这是因为新数据不仅要增加到表中，也要增加到索引中。另外，索引还需要额外的磁盘空间和维护开销。因此设计时应选择有效的索引，避免过多引用索引。通常只为对应用程序起关键作用的查询，或者被很多用户频繁使用的查询创建索引。所以也要注意在使用索引的时候一定要谨慎，要选择恰当的索引，尽量减少磁盘访问的次数，提高整个系统的性能。
5 结束语
随着 SQL 数据库中数据量的增加，运行效率将是数据库应用与管理中的关键问题。而影响其效率的因素是多方面的，本文首先介绍了SQL Server环境下的SQL优化的情况，然后本文对SQL Server数据库有关的硬件设计性能的优化、SQL Server数据库查询符号的优化、数据库的设计等方面提出了一些对数据库的优化技术，当然实现优化的方法还有很多，要根据具体情况而定。对于不同的应用情况，我们应该具体情况具体分析，各方面优化措施综合运用，本文对从优化方法的探讨对有效提高整个数据库的运行效率有一定的帮助。
参考文献：
[1] 刘波,李陶深,杨柳,严毅.MIS中数据库性能优化的研究[J].计算机工程.2002,27(7):98-100.
[2] 谷震离.查询语句对SQL Server数据库查询性能优化分析[J].福建电脑,2007(3):21-22.
[3] 王振辉,吴广茂.SQL查询语句优化研究[J].计算机应用,2005,25(12):207-208.
[4] 杨庚.关系数据库SQL语言查询过程的分析和优化设计[J].计算机工程与应用,1999(11):87-88.
关键词：SQL Server；数据库；优化
中图分类号：TP311文献标识码：A文章编号：1009-3044(2008)35-2536-02
SQL Optimization Based on SQL Database
YANG Ya-ping
(Chien-shiung Institute of Technology, Taicang 215411, China)
Abstract: Not only Correctness but also efficiency one writes SQL statements. This article introduces and analyses of SQL, which include proper indexes design .This paper research，some questions of SQL performance optimization with SQ L SERVER such as operator operation. some suggestion of optimization bate on pure theory would misadvise user of SQL.
Key words: SQL Server; database; optimization
1 前言
微软公司的SQL Server数据库因为其价格便宜、功能强大、容易操作等特点已经被中国中小企业广泛应用，但是SQL Server数据库优化是一个常常被人们忽视的问题。甚至有人认SQL Server数据库优化并不重要，但事实是SQL Server数据库是一种高性能的数据库管理系统，在客户/服务器体系结构中得到了广泛的应用。基于SQL Server的数据库应用系统的性能包括系统的响应时间、吞吐量、系统的并行处理能力等。从数据库设计、查询结构、并行处理、客户/服务器模式、应用编程等方面进行优化调整，可以有效地提高系统的性能。SQL Server数据库的优化方法还有很多，比如优化内存、优化数据库磁盘等等。下面就SQL Server环境下的SQL优化方法探讨做些简单的探讨。
2 与SQL Server数据库有关的硬件设计性能的优化
与SQL Server有关的硬件设计包括CPU、内存、磁盘子系统和网络。
2.1 系统处理器（CPU）
根据自己的具体需要确定CPU结构的过程就是估计在硬件平台上占用CPU的工作量的过程。我们可以使用 System Monitor确定CPU的使用率，如果以75%或更高的速率长时间运行，就可能碰到了CPU瓶颈问题，这时应该升级CPU。
2.2 内存（RAM）
为SQL Server方案确定合适的内存设置对实现良好的性能是至关重要的。SQL Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多能利用 2GB虚拟内存，这也是最大的设置值。还有点必须考虑的是Windows2003 Server和它的所有和关服务也要占用内存。
2.3 磁盘I/O系统
设计1个好的磁盘I/O系统是实现良好的SQL Server方案的一个很重要的方面。这里讨论的磁盘I/O系统至少有1个磁盘控制设备和1个或多个硬盘单元，考虑到磁盘设置和文件系统，有如下特点：控制器有高速缓存；总线主板上有处理器，可以减少对系统CPU的中断；异步读写支持；32位RAID支持；高速SCSTU 320控制器，读写速度320M B/S。
由I/O系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件有关的问题。配置很差的I/O系统引起性能问题的严重程度仅次于编写很差的SQL语句。解决I/O系统有关的问题最简单的办法就是增加磁盘驱动器。
3 SQL Server数据库的查询优化
查询优化首先要注意的一个问题是存储过程，设计优良的应用程序都应当使用存储过程，不论是否将应用程序的业务逻辑写入存储过程都应如此，甚至连没有业务逻辑组件的标准SQL语句，在用参数打包成存储过程后也能获得性能收益，编译进存储过程的SQL语句在执行时可省去大量的处理。
存储过程的另一个优点是客户端执行请求使用网络的效率往往比将等效的SQL语句发送到服务器高。例如。在SQL Server中假设应用程序需要将一个人的二进制值插入image数据列，为使用INSERT语句发送数据，该应用程序必须将该二进制值换为字符串(其大小翻两倍)，然后发送到服务器，服务器再将该值转换回二进制格式以存储在image列中。相反，应用程序可以创建下列格式的存储过程：CREATE PROCEDURE Procedurel (l pl image)ASINSERT Tablel VALUES (a pl)。当客户端应用程序请求执行过程P时，image参数值将一直以二进制格式发送到服务器，从而节省处理时间并减少网络流量。数据库系统的并发控制也是影响性能的一个重要方面。为了避免多个用户同时操作可能导致的数据不一致，SQL Server采用了封锁机制。SQL Server中的锁可分为共享锁(shared lock)：由读取页的进程所使用。共享锁只在特定页的读取过程中有效。修改锁(update lock)，用于将要修改数据的进程，当数据发生变化时，修改锁自动改为独占锁。独占锁(exclusive lock)，用于当前正在修改数据的进程。独占锁作用于所有影响到的页上直至事务结束。
4 SQL Server数据库设计的优化
4.1 SQL Server数据库的规范化
数据库设计方面包括表的设计、表的数量和大小、表的设计规范程序、游标的使用等等都会对运行效率有重要影响。其中涉及到数据库规范化问题时，数据库被规范化后，减少了数据的冗余，数据行趋向变窄这样SQL SERVER数据库的每页可以包含更多的行，从而加速表的扫描和返回多行的查询，改进了单个表的查询性能。但是，另一方面，当查询涉及多个表时，则需要更多的连接把信息从多个表中组合到一起，这会引起大量的CPU和I/O处理。所以有时有意违反规范化设计的规则，这是数据库的非规范化。就像刚才说的数据库的规范化原则会产生较少的列和更多的表，但表关系也许需要通过复杂的合并来处理, 这样会降低系统的性能。在某些情况下，非规范化可以改善系统的性能，通过重新定义实体以减少外部属性数据或行数据的开支。
4.2 SQL Server数据库索引的选择
索引是常见的数据库对象，它的设置好坏、使用是否得当，极大地影响数据库应用程序和数据库的性能。在良好的数据库设计基础上，能有效地使用索引是 SQL SERVER取得高性能的基础。如果对于一个未建立索引的表执行查询操作， SQL SERVER必须进行表扫描，从磁盘上读表的每一个数据页，从而挑选出所有符合条件的数据行。特别是当一个表有很多行时，就会浪费大量时间，效率太低。然而在建立索引之后，SQL SERVER将根据索引的指示，直接定位到需要查询的数据行，从而加快 SQL SERVER 的数据检索操作。这样利用索引可以避免表扫描，并减少因查询而造成的 I/O 开销。
建立索引是以最小的方法得到所需数据的有效方法。对于每一个查询SQL Server优化器将确定是否有相关的索引可以用于访问数据。一个利用索引的访问与全表扫描相比，可以大大减少查询时间。索引可分为聚集索引与非聚集索引两种，前者对数据进行物理排序，速度快，但一个表只能建立一个；后者仅对数据进行逻辑排序，速度相对聚集索引慢，但一个表可以建立多个。索引的建立虽然加快了查询，另一方面却降低了数据更新的速度，这是因为新数据不仅要增加到表中，也要增加到索引中。另外，索引还需要额外的磁盘空间和维护开销。因此设计时应选择有效的索引，避免过多引用索引。通常只为对应用程序起关键作用的查询，或者被很多用户频繁使用的查询创建索引。所以也要注意在使用索引的时候一定要谨慎，要选择恰当的索引，尽量减少磁盘访问的次数，提高整个系统的性能。
5 结束语
随着 SQL 数据库中数据量的增加，运行效率将是数据库应用与管理中的关键问题。而影响其效率的因素是多方面的，本文首先介绍了SQL Server环境下的SQL优化的情况，然后本文对SQL Server数据库有关的硬件设计性能的优化、SQL Server数据库查询符号的优化、数据库的设计等方面提出了一些对数据库的优化技术，当然实现优化的方法还有很多，要根据具体情况而定。对于不同的应用情况，我们应该具体情况具体分析，各方面优化措施综合运用，本文对从优化方法的探讨对有效提高整个数据库的运行效率有一定的帮助。
参考文献：
[1] 刘波,李陶深,杨柳,严毅.MIS中数据库性能优化的研究[J].计算机工程.2002,27(7):98-100.
[2] 谷震离.查询语句对SQL Server数据库查询性能优化分析[J].福建电脑,2007(3):21-22.
[3] 王振辉,吴广茂.SQL查询语句优化研究[J].计算机应用,2005,25(12):207-208.
[4] 杨庚.关系数据库SQL语言查询过程的分析和优化设计[J].计算机工程与应用,1999(11):87-88.