SQL性能优化培训PPT

性能优化讲师:刘建军2011-09SQL性能优化意义SQL优化建议一、SQL性能优化意义1、数据库应用程序的优化程序设计中的一个著名定律是20％的代码用去了80％的时间；两种方式优化：源代码的优化和SQL语句的优化。源代码的优化在时间成本和风险上代价很高；另一方面，源代码的优化对数据库系统性能的提升收效有限。DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执结果返回给用户。、SQL脚本优化的主要原因SQL语句是对数据库(数据)进行操作的惟一途径；SQL语句消耗了70%~90%的数据库资源；SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；SQL语句可以有不同的写法；SQL语句易学，难精通一、SQL性能优化意义二、SQL性能优化建议1、创建索引聚集索引(clusteredindex)是一种数据表的物理顺序与索引顺序相同的索引.非聚集索引(nonclusteredindex)则是一种数据表的物理顺序与索引顺序不相同的索引。、聚集索引和非聚集索引区别汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。二、SQL性能优化建议下面的表总结了何时使用聚集索引或非聚集索引二、SQL性能优化建议子句；根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。二、SQL性能优化建议二、SQL性能优化建议2.WHERE子句中的连接顺序SELECT*FROMEMPEWHERESAL5000ANDJOB=‘MANAGER’AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);SELECT*FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL5000ANDJOB=‘MANAGER’;---性能可能相差数十倍之多。二、SQL性能优化建议讨论：下面哪个条件运作效率更高？方式A：wherecolA=10000ANDcolA=1方式B：wherecolA=1ANDcolA=10000方式C：wherecolAbetween1AND10000讨论：下面三个条件是否查询效率一样？sal24000/12sal2000sal*1224000、用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.二、SQL性能优化建议、用NOTEXISTS替代NOTIN在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.二、SQL性能优化建议、避免相关子查询一个字段的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的字段值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。二、SQL性能优化建议、避免相关子查询selecthm,rqfromTabAwhereitemIN(selectitemformTabBwhereTabB.num=50)selecthm,bffromTabA,TabBwhereTabA.item=TabB.itemANDTabB.num=50二、SQL性能优化建议语句的效率，可以将不需要的记录在groupby之前过滤掉。例如：低效：selectdzxl,avg(hsje)fromreport_sale_accountgroupbydzxlhavingdzxl=‘000001’ordzxl=’000002’;高效：selectdzxl,avg(hsje)fromreport_sale_accountwheredzxl=‘000001’ordzxl=’000002’groupbydzxl;二、SQL性能优化建议二、SQL性能优化建议8.删除全表时，用truncate替代delete,同时注意truncate只能在删除全表时适用例如删除掉一个100万行的数据。TruncatetableA;比deletefromB;至少快1000倍。语言完成查询功能，不同的写法会影响到查询效率。其基本原理是尽可能的用到索引，建立了索引并不代表就一定能正确使用，若条件表达不当，则有可能导致无法使用索引。小结