SQL语句的优化

第19卷第3期天津理工学院学报Vol. 19 No.32003年9月JOURNAL OF TIANJIN INSTTTUTE OF TECHNOLOGYSep. 2003文章编号104-2262(0 2003 )3-007703SQL语句的优化.高天迎.(天津城市建设学院计算机科学与技术系天津300384 )摘要在数据库应用系统中随着数据库中数据的增加系统的响应速度就成为系统需要解决的主要的问题之-.系统优化中一个很重要的方面就是SQL语句的优化.本文从应用的角度论述了SQL语句优化的主要方法并提供了实例.关键词数据库;优化;SQL;索引中图分类号:TP311.132.2文献标识码ASQL optimizationGAO Tian-ying( Dept. of Computer Sci. and Tech. ,Tianjin Institute of Urban Construction , Tianjin 300384 , China )Abstract :In database application systems , performance becomes one of its main problems needed to be resolvedwith the increase of data in it. SQL optimization is a very important aspect in system optimization. This paperstates the main methods of SQL optimization from the applied angle and offers some instances..Keywords database optimization SQL jindex条原本糟糕的SQL语句经过系统的优化之后不可能1问题的提出变得高效因此用户所写SQL语句的优劣至关重要.数据库管理系统是管理信息系统( MIS )的核心，2问题的解决基于数据库的联机事务处理(OLTP)以及联机分析处理(OLAP)是银行、企业、政府等部门最为重要的计算下面以SQLServer为例介绍-些优化SQL语句机应用之一.从大多数系统的应用实例来看随着数据的方法.库中数据的增加系统的响应速度就成为系统需要解2.1避免顺序存取决的最主要的问题之一.系统优化中一个很重要的方在嵌套查询中对表的顺序存取对查询效率可能面就是SQL语句的优化.对于海量数据,劣质SQL语产生致命的影响.比如采用顺序存取策略,一个嵌套3句和优质SQL语句之间的速度差别可以达到上百倍.层的查询,如果每层都查询1000行,那么这个查询就SQL语言是面向结果而不是面向过程的关系数据要查询10亿行数据.避免这种情况的主要方法就是对库查询语言,在实际的数据库产品如SQL Server、Or-连接的列进行索引利用索引路径处理查询.acle等)的高版本中大都采用基于代价的优化方法这例如:SELECT * FROM titles ,titleauthor WHERE种优化能根据从系统字典表所得到的信息来估计不同title. title_ id = titleauthor. title_ _id ;规划的代价然后选择一个最优的规划1.虽然现在应中国煤化工上建立索引.的数据库产品在SQL语句优化方面已经做得越来越还YHCNMHG来避免顺序存取.尽管好但是用户提交的SQL语句是系统优化的基础,-在所有的检查列上都有索引但某些形式的where子收稿日期_2003_06-23作)者鬻夫趣1977-)男助教天津理工学院学报第19卷第3期句强迫优化器使用顺序存取(2].2.4 避免对搜索参数使用其它操作符(如数学、字符例如SELECT * FROMauthors WHERE( au_ _id=串函数等)172-32-1176'ANDzip>94618')ORzip=例如:SELECT au_ Iname FROM authors WHERE95428' ;SUBSTRING( state ,1 ,1) =' M';虽然在au_ id”和zip" 上建有索引,但是上面的SELECT title FROM titles WHERE ytd_ sales .语句还是顺序扫描整个表,因为这个语句要检索的是* 5> 30000分离的行的集合.所以应该改为如下语句:WHERE子句中对列的任何操作结果都是在SQLSELECT * FROM authors WHERE au_ _id = 172 -语句运行时逐列计算得到的,因此它不得不进行表搜32- 1176' AND zip> 94618'索,而没有使用该列上面的索引31.因此将SQL语句UNION SELECT * FROM authors WHERE zip =重写成下面这样:SELECT au _ lname FROM authors WHERE state2.2 提供一些冗余的搜索参数,使优化器有更多的LIKE M%';选择余地SELECT title FROM titles WHERE ytd_ sales >例如3] :itle 和titleauthor两张表是一对 多的关6000 ;系同样的查询条件有3种表现方法:2.5 避免使用不兼容的数据类型●SELECT title _ id ,title FROM titles ,titleauthor数据类型的不兼容可能使优化器无法执行- -些本WHERE titles. title_ id = titleauthor. title_ id AND title-来可以进行的优化操作.例如:SELECT title FROM ti-author. title_ id =' TC7777' ;tles WHERE price > 100 ;在这条语句中"，price" 字段是money" 型的优化WHERE titles. title_ id = titleauthor. title_ id AND titles.器很难对其进行优化因为100是个整型数.应当在编title_ _id =' TC7777' ;程时将整型转化成为货币型而不要等到运行时转化.2.6 避免使用相关子查询WHERE titles. title_ id = titleauthor. tile_ _id AND titles.相关查询执行效率不高,因为对于外层查询的每title_ id =' TC7777' AND titleauthor. title_ id ='一个元组都要进行-次内层子查询的运算,从而可能TC7777' ;导致大量的随机磁盘I/O操作.可以用一个具有连接显然3种方法-种比-种要好,因为后者为优化的查询去替代嵌套子查询.如果子查询不可避免那么器提供了更多的选择机会.要在子查询中过滤掉尽可能多的行.2.3 避免使用< >”或NOT"这样的操作符例如:SELECT title FROM titles WHERE EXIST”<>”是排斥性的操作符而不是包括性的操作( SELECT * FROM titleauthor WHERE titles. title_ id =符这会使系统无法使用索引而只能直接搜索表中的titleauthor. title_ id );数据3].应该改为:例如:SELECT * FROM authors WHERE au_SELECT title FROM titles , titleauthor WHERE title-lname =' Green' ;author. title_ id = titles. title_ id ;优化器将无法通过索引来确定将要命中的行数.2.7避免通配符(%)在搜寻词首出现试着使用:通配符( % )出现在搜寻词首,系 统不使用索引,SELECT * FROM authors WHERE au_ .lname<会降低SQL语句的执行速度.然而当通配符出现在字Green' OR au_ lname >' Green' ;符串其他位置时优化器就能利用索引.在下面的查询如果要使用NOT ,应在取反的短语前面加上括中索引得到了使用号并在短语前面加上NOT运算符. NOT运算符包含中国煤化工: authors WHERE state在另外一个逻辑运算符中这就是< >”运算符.LIKEYHCNMHG例如SELECT * FROM authors WHERE NOT( au2.8 索引列值不能是NULL值_Iname =' Green' );如果索引列值可以是NULL值在SQL语句中那些需要返回NULL值的操作将不会用到索引.2003年9月高天迎SQL语句的优化79.例如在WHERE子句中使用IS NULL或IS NOT是在更多的情况下往往需要反复试验比较不同的语NULL的语句优化器是不允许使用索引的.句以得到最佳方案.此外SQL的性能优化是- -个复杂2.9 COMMIT 提交的数据量不宜过大的过程,以 上着重从实现的角度讨论了SQL语句优如果-次COMMIT提交的数据量过大,则会占用化实际上要想根本解决查询优化问题还会涉及数据大量的系统内存造成COMMIT的时间过长,系统性库的结构设计与资源配置、网络的流量控制以及操作能下降.-次COMMIT提交的数据量大小应根据系统系统的总体设计等等.的具体情况在程序中加以控制.参考文献:2.10 要善于使用存储过程存储过程是编译好、优化过且存储在数据库中的[1 ] Patrick 0 Neil , Elizabeth Oneil. DATABASE :Principles ,SQL语句和控制流语言的集合如果在程序中利用好Programming and Performance[ M ] Bejing :Higher Educa-存储过程,可极大地增强SQL语言的效率、功能和灵tion Press ,Morgan Kaufmann Publishers 2001.[2]孟小峰王珊.数据库系统导论[M]北京机械工业出活性.版社2001.[3] 袁鹏飞. SQL Server7.0 数据库系统管理与应用开发3结束语[ M ]北京人民邮电出版社1999.[4]微软公司. Microsoft SQL Server 7.0系统管理[ M]北京:以上提到的是-些基本的提高SQL语句效率的北京希望电子出版社1999.方法其中的关键就是合理的建立并充分利用索引.但(上接第38页)使用身份.管理员负责系统设置、变量的确定和初始数够分析计算、数据输出,而且能够绘制图形输出,使结据的输入而普通用户则只负责数据的运行、计算结果果形象、一目了然.另外在系统的设计中还引入了一的输出实现分级负责管理管理员和普通用户分别有些技巧比如对于因素个数聚集状态的个数能够动态口令进入系统非法用户不能进入和使用系统，从而保地改变窗口的排列既能纵向又能横向等等.因此,本证了系统的安全性.系统的功能强.3 )数学模型先进:本系统采用了两种数学模型.一个是经典的统计分析方法一主成份分析法一个是6结论现代的评价分析方法一因素重构分析法.主成份分析法可以进行降维处理、减少指标、简化结构使分析容该系统在天津市发展计划委员会发展规划处测易进行而因素重构分析法则是近十年来我国系统科试、使用后系统试运行良好.该系统使用功能完善实学研究人员新研究的且经实践证明是先进的评价分析践预测结果可行符合天津市的实际情况.可作为各级方法.两种方法可以互补使用.因此,本系统的数学模领导及经济规划部门]进行区域经济规划决策的辅助工型具有先进性.具因此,该系统的研制开发是成功的具有广阔的应4)良好的可维护性和可扩充性:油于本系统采用用前景.自顶向下的模块结构设计不同模块可分别进行设计和调试因此可维护性好;另外可根据需要较容易地增加或减少功能和模块,有良好的可扩充性.[1 ]罗积玉.邢 瑛.经济统计分析方法及预测[ M]北京:5)系统功能强本系统不仅能够进行统计计算分清华大学出版社,1987.析,而且能够进行预测分析;既能预测某年的经济总[2]舒中国煤化工M ]北京:中国科学院系量，又能根据经济总量预测出各指标的增长值不仅能统YHCNMH G