[ＳＱＬ查询优化技术] 数据库sql实验 查询优化

　　摘要：数据查询是数据库应用程序的重要组成部分，查询语句的执行速度已成为数据库整体性能的瓶颈。本文阐述了影响查询效率的因素，并根据具体情况，给出了优化的方法，以提高查询语句的执行效率。?
　　关键词：SQL；查询优化；查询语句?
　　中图分类号：TP311.13 文献标识码：A??
　　
　　1 引言?
　　
　　目前，随着计算机技术的不断发展，数据库技术应用越来越广泛，特别是大型数据库的广泛使用。而基于数据库的应用程序在执行时，应用程序需要在数据库中查询相应的信息。SQL（Structured Query Language）语言是关系数据库的标准语言，集数据操纵、数据定义和数据控制等功能于一体，数据库的查询又是数据库的核心操作，也是最常用的操作。但人们在使用SQL语言往往只注意结果的准确，忽视了其执行的效率，影响了系统的整体性能。据统计，90％的性能问题主要来源于程序员或用户使用了不恰当的SQL查询语句。由此可见，SQL查询语句的质量对于数据库管理信息系统的质量有较大的影响，如何保证在结果正确的条件下，提高查询效率成为一个急需解决的问题。?
　　
　　2 查询语句效率的影响因素分析?
　　
　　查询语句的效率既可以利用逻辑方法进行查询优化，也可以采用物理的方法进行查询优化，但在实际中，CPU的处理速度比从磁盘上读写信息的速度快得多，要进行查询优化，关键是要减少对磁盘的访问。RDBMS 优化器的输入是一条查询语句，输出是一个执行策略。执行策略是执行查询所需要的一系列步骤。优化器的优化算法影响数据库的反应速度，优化器的优化策略还与所查询表的内容和其他一些与服务器有关的因素有关，如cache大小、cache策略、I/O 大小等。通常硬盘访问是成本最高的操作，因此对查询优化来讲，最为重要的选择就是使用什么索引和采用何种表的连接手段。?
　　影响查询效率的因素，主要有以下几点：①索引是否合适；②查询条件中的数据类型是否兼容；③查询条件中操作符使用是否得当；④多个选择条件查询时，选择条件的次序是否合理；⑤ 是否合理安排连接选择运算．⑥多表查询是否合理等。?
　　
　　3 SQL语句的优化方法?
　　
　　SQL 查询语句执行都涉及到存取用户或系统对象的表页或索引项。一般而言，系统存取页面的速度越快，SQL 语句的性能就越高。因此对SQL查询语句进行优化，应尽量减少页面存取，或使用内存的页面代替访问磁盘。本文以SQL SERVER 2000为例，给出了进行查询优化的几种方法。?
　　3.1 选取合适的索引字段?
　　对于查询优化来说，提高速度最快的方法是建立索引。在数据库中建立合适的索引能避免表扫描并减少因查询而产生的输入/输出开销，使查询速度大为提高。?
　　SELECT查询中的WHERE语句是查询优化器处理的重点。WHERE语句中的每一列都是索引的候选列，但对每一列都建立索引会形成很多索引，增加系统的开销，故应尽量分析出每一个重要查询的使用频度，将常查询的列建立索引。?
　　尽量覆盖一个查询。例如：如果一个查询只选择列甲和列乙，把列甲和列乙作为索引键建立复合索引，这个查询就是覆盖的，这样被提取的数据存放在索引键自身中，根本不需要读取实际数据。因而会减少输入/输出总量，大大提高查询速度。?
　　在经常要进行插入、删除、修改、更新操作的表上不要建立太多不必要的索引。因为表数据修改后，所有的索引都必须做相应的调整，所有的分页操作都被记录在系统日志中。这也会增加输入/输出操作，从而影响数据库性能。?
　　3.2 避免使用不兼容的数据类型?
　　在SQL中，float、int、varchar binary和varbinary是不兼容的。数据的不兼容使得无法使用预先定义好的索引，影响查询速度，如语句SELECT NAME FROM CUSTOMERWHERE COST>1000在执行时因COST是MONEY型数据，1000是整型，形成数据类型不兼容，无法使用建立在COST上的索引，系统在执行时要先将1000转换成MONEY型，再与COST进行比较。故应当在编成程序时，将1000转化成MONEY型，而不是等到运行时转化。可将其转化成SELECT NAME FROM CUSTOMERWHERE COST>$1000。?
　　3.3 避免对搜索参数使用其它操作符（如数学，字符串函数等）?
　　若在WHERE子句中，存在一个数学表达式，查询优化器就不能使用分布统计信息，影响查询效率。如查询语句SELECT NAME?ID FROMCUSTOMERWHERE COST *5>3000应写成SELECT NAME?ID FROMCUSTOMERWHERE COST >600。?
　　例如：SELECT AU?LNAMEFROM AUTHORS WHERE SUBSTRING（STATE，1，1）= ‘A’ ?
　　应写成SELECT AU?LNAMEFROM AUTHORSWHERESTATELIKE‘A%’。?
　　3.4 避免使用非聚合表达式?
　　非聚合表达式很难利用索引，故在执行含有非聚合表达式的SQL语句时，必须进行大规模的扫描，降低了执行的速度，例如不等关系符号“<>”是一个排斥性的的操作符，故含有“<>”符号的SQL语句在执行时，在扫描整个表之前无法确定子句的选择范围，必须扫描整个表。像“NOT”，“NOT IN”，“NOT LIKE”，“NOT EXIST”等都是非聚合表达式，应避免使用。例如：SELECT SNAME FROM STUDENT WHERE AGE<>20可改写成SELECT SNAME FROM STUDENT WHERE AGE20。?
　　3.5避免通配符（%）在搜寻词首出现?
　　通配符（%）出现在搜寻词首，系统不使用索引，会降低SQL语句的执行速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：?
　　SELECTAU?LNAMEFROMAUTHORSWHERESTATELIKE‘A%’；?
　　3.6 对于连接查询，适当冗余?
　　缺省情况下多于4个表的连接查询，按每次4个处理。对每个表集找到并保存最佳的外表，其余的组合则用来估计下一个次外表。如果在查询中应当提供一些冗余的搜索参数，使优化器有更多的选择余地，可提高查询速度。?
　　如下面两个查询是相同的?
　　SELECTGRADEFROMST，KCWHEREST.SNO=KC.SNOANDST.NO=‘20060101’?
　　SELECTGRADEFROMST，KCWHEREST.SNO=KC.SNOANDST.NO=‘20060101’ ANDKC.NO=‘20060101’?
　　但后一个查询的速度更快。?
　　3.7 注意选择条件的排列顺序?
　　选择条件的排列顺序不仅能影响索引的选取，还关系到临时表的大小，对系统的性能有较大的影响。?
　　例如：比较SELECT*FROMAUTHORSWHERE STATE=‘CA’ANDAU?LNAME=‘WHITE’和SELECT*FROMAUTHORSWHEREAU?LNAME=‘WHITE’ ANDSTATE=‘CA’ 的查询效率。假设AUTHORS表中共有20万条记录，其中满足STATE=‘CA’的记录为12万条，满足AU?LNAME=‘WHITE’的记录为2万条。在SQL SERVER 2000中查询条件的选取是从左至右的。第一条SQL语句在执行时，首先返回一个12万条记录的临时表，然后在从中进行选择。第二条SQL语句在执行时，首先返回一个2万条记录的临时表，然后在从中进行选择。显然，第二条SQL语句的查询效率比第一条要高。一般来说，为提高查询速度，将条件严格的写在前面。?
　　3.8 将多表的连接运算改为嵌套SQL语句来实现?
　　连接运算会产生很大的临时表，特别是未优化的多表连接查询。未经过优化的SQL查询语句会产生巨大的计算工作量，可将其改为嵌套SQL语句来提高查询效率。例如：?
　　SELECT STUDENT.SNAME?
　　FROMSTUDENT， SC
　　WHERE STUDENT.SNO = SC.SNO?
　　AND SC.CNO= ‘ 2 ’?
　　可改为?
　　SELECT SNAME FROM STUDENT?
　　WHERE SNO IN （SELECT SNOFROM SC WHERE CNO= ‘ 2 ’）?
　　假设STUDENT表中有1000条记录，SC表中有5000条记录，其中CNO为2的有500条，在第一个SQL语句中，首先形成1000×5000＝5000000条记录的临时表，再进行选择运算。若采用第二个SQL语句，先对SC表进行选择运算，有500条记录满足条件，然后再与STUDENT表进行连接运算，很显然，后者的计算量要比前者少很多，故对于多个数据表的连接选择运算过程，先做选择运算后做连接运算，比先做连接运算后做选择运算计算量小，查询响应时间短，查询效率高。?
　　3.9 使用存储过程?
　　存储过程是编译好、优化过且存储在数据库中的SQL语句和控制流语言的集合，设计优良的应用程序都应当使用存储过程。如果在程序中利用好存储过程，可极大地增强SQL语言的效率、功能和灵活性。编译进存储过程的SQL语句在执行时可省去大量的处理时间。存储过程在客户端执行请求使用网络的效率往往比将等效的SQL语句发送到服务器高。?
　　
　　4 结束语?
　　
　　分析了查询语句效率影响因素的基础上，提出了具体的优化方法。在系统开发和维护过程中须针对具体的情况，综合运用多种优化方法，不断加以调整，可以提高SQL语句的执行效率，取得满意的运行效果。?
　　
　　参考文献?
　　
　　[1]萨师煊，珊著.库系统概论[M].北京：高等教育出版社，2001.?
　　[2]章立民著.SQL SERVER 2000 TRANSACT-SQL 程序设计[M].北京：中国铁道出版社，2002.?
　　[3]胡百敬著.MICROSOFT SQL SERVER性能调校[M].北京：电子工业出版社，2005.

相关热词搜索：优化 查询 技术 ＳＱＬ查询优化技术 mysql查询优化技术 mysql分页查询优化