查询优化及SQL查询语句的优化方法

第5卷第2期浙江工贸职业技术学院学报Vol.5 No.22005年6月JOURNAL OF ZHEJIANG INDUSTRY&TRADE POLYTECHNICJun.2005查询优化及SQL查询语句的优化方法程有娥(浙江工贸职业技术学院，浙江温州325003)摘要:查询是数据库应用系统中使用最频繁的操作，查询语句的优劣直接影响数据库应用系统的性能。本文在分析查询优化策略的基础上，重点介绍了几种SQL查询语句的优化方法。关键词:查询优化; SQ;关联;索引中图分类号: F274文献标识码: B文章编号: 1672- 0105 (2005) - 02- 0053- 04Enquiry Optimizing and SQLCheng Youer( Zhejiang Industry and Trade PolytechnicWenzhou Zhejiang 325003)Abstract: Enquiry is frequently used in data base application system which depends on whether the enquiry statement issuperior or inferior. The author introduces some methods of SQL through analysis of enquiry optimizing strategy.Keywords: Enquiry optimizing; SQL; Iterrelation; Index一、关于查询优化对数据库的查询操作在数据库应用系统中都占有很大的操作比例。所谓查询优化就是对于给定的查询选择代价最小的操作序列，使查询过程既节省时间，又节省空间。关系数据库系统，对于用户给出的查询语句，系统中的查询处理程序能自动实现查询优化。但是，对于一个较复杂的查询要求，通常都可以用几种不同的查询语句来表达，虽然它们的结果是相同的，但执行过程可能有很大的差别。所以，作为数据库应用系统开发人员也同样有必要进行查询优化的探讨，对特定的查询操作写出最优的查询语句，从而在系统开销最小的情况下对查询进行尽可能的优化。查询优化既有逻辑方法的优化(如关系代数表达式的优化)，也有物理方法的优化(如存取路径和存取方法的优化)。由于磁盘读写比CPU的处理在速度上慢得多，因此查询优化的关键就是减少对磁盘的访问。具体策略主要有: 1. -元选择首先做(指针对一一个关系、只涉及-一个属性的选择); 2. 投影、选择同时做; 3. 乘积、选择合并做(把笛卡儿与随后以选择形式出现的连接条件-起做连接运算); 4. 索引、排序预先做。具体的实现步骤是利用关系代数等价变换规则对查询表达式进行变换，最后得到一个优化代价合理、查询效率较高的查询计划。收稿日期: 2005-03-23中国煤化工作者简介:程有娥(1963- )，女，副教授。MYHCNMHG5.浙江工贸职业技术学院学报二、基于SQL查询语句的查询优化办法在SQL中要提高查询效率，除了提高网速、把数据、日志、索引放到不同的I/O设备上，以增加读取速度等措施以外，对查询语句进行优化也是常用的提高查询效率的办法。下面以学生档案管理系统为例，介绍在SQ中查询语句的优化办法。假如在一个学生档案管理系统中，有学生基本信息表Students(St udent_ Id, St udent_ nane, Photo, Sex,Nati onality, Brith, Pol iti cal _ party, Fanily_ pl ace, Id_ card, Hone_ .phone, Resi dence, Post_ code, Indate, a ass_ Id)和学生成绩表G(Student_ Id, Ks_ Id, St udent_ nane, Score)。(-)合理建立和使用索引索引是数据库中重要的数据结构，也是查询优化的基础，建立索引的根本目的是提高查询效率。索引的使用要恰到好处，其使用原则如下:1.在频繁进行排序或分组( 即进行group by或order by 操作)的列上建立索引。2.在经常进行连接的列上建立索引，而不经连接的字段则由优化器自动生成索引。3.不要对只有有限几个值的字段建立单一索引。例如若对“性别”字段建立了索引不但不会提高查询效率，反而会严重降低更新速度。4.如果待排序的列有多个，可在这些列上建立复合索引。.(二)合理使用查询条件，充分发挥系统优化器的作用，避免对数据表记录的顺序读取表的顺序读取是导致系统性能直线下降的原因。在以下几种情况下，从理论上讲可以使用索引进行优化。但实际上会使优化器忽略相应的查询语句或者无法进行优化处理，而导致对数据表的顺序读取。1.在表达式中使用了非法参数(SQ Server 2000 中，搜索参数的合法操作符包括“=，<，>，<=,>=”中的任何一个)例如: SELECT * FRCM St udents WERE St udent_ id like‘20039%6改进后: SELECTFRCM St udentsWERE (Student_ id>' 2003’AND Student. i d<' 2004' )2.对列进行了算术、比较或函数等运算如以下三个查询都不能通过系统优化器进行优化SLECT * FROMG WHERE Sorce*0. 9>80 ( 对列进行了算术运算)SLECT * FROMG WERE uppercase( xm)=’SMTH'(对列进行了函数运算)SLECT * FRCMGWERE Student. id=Ks_ id ( 列与列之间进行了比较)3.使用了IN或者CR等时可能导致查询没有使用索引优化，可使用显式申明指定索引如查询SELECT * FROMG WERE St udent_ i d=100 OR Sorce=60 在执行时如果发现没有使用索引优化，可使用显式申明指定索引。注意:在查询语句中应极力避免使用“<>” 或NOT操作符，因为，这些操作符都会导致对表的顺序读取。(三)使用多个条件对同一表进行查询时，FRQM子 句中将条件严格的表放在前面.中国煤化工例查找2004年9月5号入学的Pol itical - Party为PartyYHCNMHG.程有娥:查询优化及SQL查询语句的优化方法SELECT St udent_ _nane, Sex, Fi nely_ pl aceFRQM St udent sWHERE Pol itical_ party=' party’AND indata=' 5- SEP- 2004'如果St udents中有10°个记录，其中Party记录有10个，而表的所有记录中2004年9月5号入学的记录有10个，其中Party的有100个。在上述两种查询语句中有两个选择条件: Pol iti cal- Party=’Party’ 和InData= “5- SEP- 2004' ,在执行完第-个选择条件后，得到一个105行的临时表格，第二个选择条件是对这个10行的临时表再进行选择运算，从而得到所需结果。如果将选择条件的次序对换，即将WERE语句改写为WERE I ndat e=‘5- SEP- 2004’AND Political_ Party= ‘Party'， 则先得到10行的临时表格，然后再从这个10行的临时表中选择出符合条件的记录，可见，选择条件的次序极大地影响查询语句的计算量，前者临时表格的行数是后者的100倍，所以要提高查询响应速度，可将较严格的选择条件放在前面，较宽松的条件放在后面，这样在执行过程中可以将不满足的行迅速删除，从而是第二次选择提高速度。(四)对多个表查询时，将返回结果集记录数较少的表放在后面对多个表格进行查询操作时，FRQM子句中表格的次序也影响查询的响应速度。设学生档案管理系统中还有一个与st udents结构类似的表st udents1，students1 中2004年9月5号入学的学生有200个，若要查询st udents和st udents1两个表中2004年9月5号入学的学生，则SQ语句将返回包含所有1200个记录的表，相应的SQ查询语句如下:SELECT Student_ nane, Sex, Fi nely_ .pl aceFRQM St udent , St udent 1WHERE i ndat a=’5- SEP- 2004'在具体的执行过程中，先对表格students进行选择运算,得到1000个记录的临时表，再对st udents1进行选择运算,然后将得到的符合条件的200个记录插入到1000个记录的临时表中,需200次插入运算。若将FROM语句后面的表次序对换，即为“FROM St udents1, St udent ds”,则先得到-一个200行的临时表后，需要做1000次的插入运算，其工作量远大于第-种情况，故可将返回较少记录的表格排在后面，较多记录的表格排在前面，以便减少插入运算。(五)对多个表进行多条件查询时，使用相关子查询联结运算是数据库查询中经常使用的运算之-，由于联结运算会产生-个很大的临时表格，特别是多个表的联结运算，不优化的SQ查询语句会产生巨大的运算量，且增加对内存的需求，严重时可使软件在执行中出现异常。例如查询2004年9月5号入学的学生成绩,利用下面两个sQL语句都可以单独完成:1) SELECT * FROMStudents, qWERE St udents. Student_ i d=G. Student_ i d AND Students. indata =’5- SEP- 2004'2) SELECT *FROM gWERE St udent_ i d=( SELECT St udent_ idFROMSt udents WERE i ndat a=’5- SEP- 2004' )中国煤化工MYHCNMHG5:.浙江工贸职业技术学院学报两个查询语句返回同样的数据结果，但查询响应时间不同，在1)中先进行两个表格的联结运算,得到一个10行的临时表格，再进行选择运算，得到所需的结果。而2)中先对表格St udents进行选择运算，立刻删除了大量多余数据，得到一个仅有1000行的临时表(由前可知)，再对这个表与g进行联结运算而得到最终结果。可见1)中的计算量要比2)大得多，这是因为在1)中的联结运算的两个表都是10°行，选择运算也是在巨大的表上进行，而2)中的联结运算仅对一-个1000行的表进行。可见在对多个表进行多条件查询时，使用相关子查询可以使查询优化。(六)适当建立关联统计表或临时表，减少对大型表的直接查询把表的一个子集进行排序并创建临时表，避免了对数据表的直接操作，从而提高了查询效率。例如:SELECT St udents. St udent_ nane, g . score, St udents. Post_ codeFORMSt udents, gWVERE St udents. St udent_ i d=Gj . student. id ANDg. score>60 AND St udents. Post_ Code>' 5100'ORDER BY St udents. St udent_ nane如果这个查询要被执行数次，可以把所有及格的学生找出来放在-个临时表文件中，并按学生的名字进行排序: .FORMSt udents, GWKERE St udents. St udent. i d=G. student. id AND G . score>60INTO TEMP Student. _wi th Score :然后按下面的方式在临时表中查询: :SELECT St udents. Student_ nane, g . score, St udents. Post codeFORMSt udent_ with_ ScoreWHERE Post_ Code>’ 5100'临时表中的行比主表少，而且物理顺序就是所要求的顺序，减少了磁盘1/0所以查询效率大大增加。注意:临时表创建后不会反映主表的修改。三、结束语.查询优化是数据库应用程序设计中一个关键性的问题，本文是作者在多年的教学中总结出的一些方法，相信对相关课程的教学和数据库应用系统的开发有-定的指导作用。参考文献:[1]史嘉权.数据库系统教程[M.清华大学出版社.[2]靳学辉、数据库原理与应用[M.电子工业出版社, 1997.[3] [美]微软公司. Querying Mcrosoft SQ Server 2000 W thTr ans中国煤化工2001.YHCNMHG56.