论文部分内容阅读
摘要：通常如何提高查询执行效率是检验关系数据库系统性能好坏的重要标志，需要进行优化设计的地方很多，因此可以说查询速度决定了关系数据库的性能优劣。本文重点讨论查询优化的一些方法，并结合它在实际应用中的例子进行分析。
关键词：SQLServer；查询；优化；索引
中图分类号：TP311 文献标识码：A 文章编号：1007-9599 （2013） 02-0000-02
目前程序开发中几乎都使用到数据库，作为中小规模开发的数据库主流是SQLServer数据库，其查询性能好坏跟整个系统运行效率有着极大的关系，但实际应用中，会出现查询等待时间过长、响应速度慢的现象，究其原因，一方面是硬件方面的原因，另一方面就是对查询没有进行很好的优化。
本文所谓的查询优化是指为了查询选择出最有效的查询计划的过程，查询优化一方面在关系代数级进行优化，力图找出与给定表达式等价，而且执行效率更高的一个表达式另一方面涉及查询语句处理策略的选择。
1 SQLServer查询优化机制
SQLServer中有一个“查询分析优化器”，它能实现自动优化，即查询提供一个最佳的执行策略.对于SQL的查询优化器，输入是一条查询语句，输出是一个执行策略.一条SQL查询语句可以有多种执行策略，优化器将会考察解决查询各个部分（如WHERE条件、连接和函数），估计出每个操作的每项逻辑开销，并考虑索引、硬件的限制和数据的统计信息，执行其中所需时问最少的所谓成本最低的那一种方法.虽然查询优化器可以根据WHERE子句自动的进行查询优化，但有时查询优化器就会不按照您的本意进行快速查询.因此，SQL数据库应用系统中，查询性能与查询设计的好坏有关.对影响SQL查询语句执行效率的主要因素进行分析，是优化SQL查询语句，提高查询效率的重要途径。
2 查询速度慢的原因种类
查询速度慢的原因很多，常见如下几种：没有索引或者没有用到索引；I/O吞吐量小，形成瓶颈效应；没有创建计算列导致查询不优化或者计算列失效了；内存不足；网络速度慢；查询出的数据量过大；锁或者死锁；一个服务器同时存在OLTP和OLAP查询；返回了不必要的列；查询语句不好，没有优化。
3 常见的查询优化方法
把数据、日志、索引放到不同的I/O设备上，增加读取速度，Tempdb应放在RAID0上。数据量（尺寸）越大，提高I/O越重要；纵向、横向分割表，减少表的尺寸；升级硬件；根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建），不要对有限的几个值的字段建单一索引如性别字段；提高网速；扩大服务器的内存，增加服务器CPU个数；收缩数据和日志DBCCSHRINKDB，DBCCSHRINKFILE。设置自动收缩日志。
4 查询优化在T-SQL中的具体体现
4.1 T-SQL语句写法的影响
T-SQL语句的写法上有很大的讲究，下面列出常见的要点：在最常见查询SELECT语句中，最好是使用WHERE子句限制返回的行数，避免对表进行全部扫描，如果不进行限制，就会返回很多不必要的数据，既浪费了服务器的I/O资源，又加重了网络的负担降低性能，特别是表很大的时候，再对表全扫描，很可能造成在扫描期间将表锁住，导致禁止其他的联接访问表，造成后果严重。
4.2 设置锁的时间对查询优化的影响
SETLOCKTIME设置锁的时间；用SELECTTOP10/10Percent来限制用户返回的行数或者SETROWCOUNT来限制操作的行，尽量不要用如下的字句：ISNULL、<>、！=、！>、！<、NOT、NOTEXISTS、NOTIN、NOTLIKE，因为这些关键字不走索引而是表全扫描，但是改成like"500%"就会走索引。也不要在WHERE字句中的列名加函数，诸如Convert，substring等，如果却是需要用函数的时候，可以通过创建计算列再创建索引来替代，一定要将函数和列名分开，并且索引不能建得太多和太大。
4.3 联接对查询优化的影响
NOTIN会多次扫描表，使用EXISTS、NOTEXISTS，IN，LEFTOUTERJOIN来替代，特别是左连接，而EXISTS比IN更快，最慢的是NOT操作；使用QueryAnalyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL；存储过程就没有这些动作；BETWEEN比IN速度更快，BETWEEN能够很快地根据索引找到范围。
4.4 借助索引/临时表实现查询优化
除以上常见的情况，还包括如下不太常见的情况：在必要是对全局或者局部临时表创建索引；不要建没有作用的事物例如产生报表时，浪费资源；用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用到索引有关，如果查询需要用到联合索引，用UNION，all执行的效率更高。多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配；尽量少用视图，它的效率低。对视图操作比直接对表操作慢，可以用storedprocedure来代替她。特别的是不要用视图嵌套，嵌套视图增加了寻找原始资料的难度；没有必要时不要用DISTINCT和ORDERBY，这些动作可以改在客户端执行。
4.5 设计查询时常见基本注意项
应尽量避免过于频繁创建和删除临时表，从而减少系统表资源的消耗，以便为别的基本表腾出更多的空间来进行存储与运算；如果需要设计存储过程和触发器的时候，可以在创建或者使用存储过程和触发器的开始处设置“SETNOCOUNTON”，在结束时设置“SETNOCOUNTOFF”，从而减少向客户端发送信息所造成的时间和空间上的浪费；如果需要设计事务的时候，可以将大事务操作分解成许多个小事务来实现大事务的功能，这就是软件工程学里面所讲的采用自顶向下的编程方法，这样就可以大大提高数据库系统的并发操作能力；同时还应尽量不使用不兼容的数据类型，诸如浮点型和整形、字符型和文本型等是不兼容的，数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。
在设计查询表达式的时候要充分考虑索引、数据的存储分布和存取路径，以便提高查询的效率。诸如选择字段、连接字段上是否有索引都会对查询效率有影响。最好的办法是利用索引和HAVING算法可以快速地存取包含索引属性特定值的记录，并且依照接近于物理的顺序读取文件中的记录是非常有效的，这种接近物理顺序读取文件记录的索引称为聚集索引。聚集索引使我们可以利用存储块中的记录物理聚集的优点，加快查询的力度。
5 结语
本文着重从实际的角度讨论了查询优化一些方法，但从根本上说要想从根本上解决查询优化的问题，还要从设计上进行优化，尽量使用大的内存，数据可适度沉余，库结构优化，并对频繁使用的属性建立索引，采用面向對象的数据库设计方法等手段。
关系数据库的查询优化对提高数据库系统执行效率至为重要，其中查询语句的好坏对于查询优化影响非常之大。实际操作设计的时候，要根据具体情况统筹考虑，各方面权衡利弊，尽可能使数据库系统效率达到最优。
参考文献：
[1]彭勇.SQLServer查询性能分析与查询效率提高[J].西南民族大学学报，2012，3.
[2]杨海俊.SQLServer数据库在实际运用中的策略分析探究[J]，计算机光盘软件与应用，2012，5.
[3]谭磊，顾国强，王占宏.基于优化器的提高海量数据查询效率方法研究[J].计算机应用与软件，2012，1.
[4]姚旭.有关数据库查询优化技术在局域网中的应用研究[J].企业文化，2012，2.
[5]王兰春.基于统计的关系数据库查询优化器模型分析与研究[J].现代计算机，2011，11.
[作者简介]楚书来（1982-），男，河南沈丘人，硕士研究生，周口职业技术学院讲师，主要从事计算机专业教学；肖尧（1985-），男，河南沈丘人，本科，周口科技职业学院教师，主要从事计算机的教学。
关键词：SQLServer；查询；优化；索引
中图分类号：TP311 文献标识码：A 文章编号：1007-9599 （2013） 02-0000-02
目前程序开发中几乎都使用到数据库，作为中小规模开发的数据库主流是SQLServer数据库，其查询性能好坏跟整个系统运行效率有着极大的关系，但实际应用中，会出现查询等待时间过长、响应速度慢的现象，究其原因，一方面是硬件方面的原因，另一方面就是对查询没有进行很好的优化。
本文所谓的查询优化是指为了查询选择出最有效的查询计划的过程，查询优化一方面在关系代数级进行优化，力图找出与给定表达式等价，而且执行效率更高的一个表达式另一方面涉及查询语句处理策略的选择。
1 SQLServer查询优化机制
SQLServer中有一个“查询分析优化器”，它能实现自动优化，即查询提供一个最佳的执行策略.对于SQL的查询优化器，输入是一条查询语句，输出是一个执行策略.一条SQL查询语句可以有多种执行策略，优化器将会考察解决查询各个部分（如WHERE条件、连接和函数），估计出每个操作的每项逻辑开销，并考虑索引、硬件的限制和数据的统计信息，执行其中所需时问最少的所谓成本最低的那一种方法.虽然查询优化器可以根据WHERE子句自动的进行查询优化，但有时查询优化器就会不按照您的本意进行快速查询.因此，SQL数据库应用系统中，查询性能与查询设计的好坏有关.对影响SQL查询语句执行效率的主要因素进行分析，是优化SQL查询语句，提高查询效率的重要途径。
2 查询速度慢的原因种类
查询速度慢的原因很多，常见如下几种：没有索引或者没有用到索引；I/O吞吐量小，形成瓶颈效应；没有创建计算列导致查询不优化或者计算列失效了；内存不足；网络速度慢；查询出的数据量过大；锁或者死锁；一个服务器同时存在OLTP和OLAP查询；返回了不必要的列；查询语句不好，没有优化。
3 常见的查询优化方法
把数据、日志、索引放到不同的I/O设备上，增加读取速度，Tempdb应放在RAID0上。数据量（尺寸）越大，提高I/O越重要；纵向、横向分割表，减少表的尺寸；升级硬件；根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建），不要对有限的几个值的字段建单一索引如性别字段；提高网速；扩大服务器的内存，增加服务器CPU个数；收缩数据和日志DBCCSHRINKDB，DBCCSHRINKFILE。设置自动收缩日志。
4 查询优化在T-SQL中的具体体现
4.1 T-SQL语句写法的影响
T-SQL语句的写法上有很大的讲究，下面列出常见的要点：在最常见查询SELECT语句中，最好是使用WHERE子句限制返回的行数，避免对表进行全部扫描，如果不进行限制，就会返回很多不必要的数据，既浪费了服务器的I/O资源，又加重了网络的负担降低性能，特别是表很大的时候，再对表全扫描，很可能造成在扫描期间将表锁住，导致禁止其他的联接访问表，造成后果严重。
4.2 设置锁的时间对查询优化的影响
SETLOCKTIME设置锁的时间；用SELECTTOP10/10Percent来限制用户返回的行数或者SETROWCOUNT来限制操作的行，尽量不要用如下的字句：ISNULL、<>、！=、！>、！<、NOT、NOTEXISTS、NOTIN、NOTLIKE，因为这些关键字不走索引而是表全扫描，但是改成like"500%"就会走索引。也不要在WHERE字句中的列名加函数，诸如Convert，substring等，如果却是需要用函数的时候，可以通过创建计算列再创建索引来替代，一定要将函数和列名分开，并且索引不能建得太多和太大。
4.3 联接对查询优化的影响
NOTIN会多次扫描表，使用EXISTS、NOTEXISTS，IN，LEFTOUTERJOIN来替代，特别是左连接，而EXISTS比IN更快，最慢的是NOT操作；使用QueryAnalyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL；存储过程就没有这些动作；BETWEEN比IN速度更快，BETWEEN能够很快地根据索引找到范围。
4.4 借助索引/临时表实现查询优化
除以上常见的情况，还包括如下不太常见的情况：在必要是对全局或者局部临时表创建索引；不要建没有作用的事物例如产生报表时，浪费资源；用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用到索引有关，如果查询需要用到联合索引，用UNION，all执行的效率更高。多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配；尽量少用视图，它的效率低。对视图操作比直接对表操作慢，可以用storedprocedure来代替她。特别的是不要用视图嵌套，嵌套视图增加了寻找原始资料的难度；没有必要时不要用DISTINCT和ORDERBY，这些动作可以改在客户端执行。
4.5 设计查询时常见基本注意项
应尽量避免过于频繁创建和删除临时表，从而减少系统表资源的消耗，以便为别的基本表腾出更多的空间来进行存储与运算；如果需要设计存储过程和触发器的时候，可以在创建或者使用存储过程和触发器的开始处设置“SETNOCOUNTON”，在结束时设置“SETNOCOUNTOFF”，从而减少向客户端发送信息所造成的时间和空间上的浪费；如果需要设计事务的时候，可以将大事务操作分解成许多个小事务来实现大事务的功能，这就是软件工程学里面所讲的采用自顶向下的编程方法，这样就可以大大提高数据库系统的并发操作能力；同时还应尽量不使用不兼容的数据类型，诸如浮点型和整形、字符型和文本型等是不兼容的，数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。
在设计查询表达式的时候要充分考虑索引、数据的存储分布和存取路径，以便提高查询的效率。诸如选择字段、连接字段上是否有索引都会对查询效率有影响。最好的办法是利用索引和HAVING算法可以快速地存取包含索引属性特定值的记录，并且依照接近于物理的顺序读取文件中的记录是非常有效的，这种接近物理顺序读取文件记录的索引称为聚集索引。聚集索引使我们可以利用存储块中的记录物理聚集的优点，加快查询的力度。
5 结语
本文着重从实际的角度讨论了查询优化一些方法，但从根本上说要想从根本上解决查询优化的问题，还要从设计上进行优化，尽量使用大的内存，数据可适度沉余，库结构优化，并对频繁使用的属性建立索引，采用面向對象的数据库设计方法等手段。
关系数据库的查询优化对提高数据库系统执行效率至为重要，其中查询语句的好坏对于查询优化影响非常之大。实际操作设计的时候，要根据具体情况统筹考虑，各方面权衡利弊，尽可能使数据库系统效率达到最优。
参考文献：
[1]彭勇.SQLServer查询性能分析与查询效率提高[J].西南民族大学学报，2012，3.
[2]杨海俊.SQLServer数据库在实际运用中的策略分析探究[J]，计算机光盘软件与应用，2012，5.
[3]谭磊，顾国强，王占宏.基于优化器的提高海量数据查询效率方法研究[J].计算机应用与软件，2012，1.
[4]姚旭.有关数据库查询优化技术在局域网中的应用研究[J].企业文化，2012，2.
[5]王兰春.基于统计的关系数据库查询优化器模型分析与研究[J].现代计算机，2011，11.
[作者简介]楚书来（1982-），男，河南沈丘人，硕士研究生，周口职业技术学院讲师，主要从事计算机专业教学；肖尧（1985-），男，河南沈丘人，本科，周口科技职业学院教师，主要从事计算机的教学。