搜索
SQL性能优化交流资料1交流提纲SQL性能优化意义SQL优化建议2一、SQL性能优化意义（一）ORACLE性能优化主要方法硬件升级(CPU、内存、硬盘)；版本及参数设置；应用程序设计（框架、调用方式---源代码）；SQL语句优化；3一、SQL性能优化意义1、硬件升级CPU：在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志，并且ORACLE是一个提供并行能力的数据库系统，如果运行队列数目超过了CPU处理的数目，性能就会下降；内存：衡量机器性能的另外一个指标就是内存的多少了，在ORACLE中内存和我们在建数据库中的交换区进行数据的交换，读数据时，磁盘I/O必须等待物理I/O操作完成，在出现ORACLE的内存瓶颈时，我们第一个要考虑的是增加内存，由于I/O的响应时间是影响ORACLE性能的主要参数；网络条件：NET*SQL负责数据在网络上的来往，大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响，还有交换机、集线器等等网络设备的性能对网络的影响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。4一、SQL性能优化意义2、版本及参数设置5一、SQL性能优化意义3、数据库应用程序的优化程序设计中的一个著名定律是20％的代码用去了80％的时间；两种方式优化：源代码的优化和SQL语句的优化。源代码的优化在时间成本和风险上代价很高；另一方面，源代码的优化对数据库系统性能的提升收效有限。DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。6一、SQL性能优化意义4、SQL脚本优化的主要原因SQL语句是对数据库(数据)进行操作的惟一途径；SQL语句消耗了70%~90%的数据库资源；SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；SQL语句可以有不同的写法；SQL语句易学，难精通7交流提纲SQL性能优化意义SQL优化建议8二、SQL优化建议原则1、选用合适的ORACLE优化器主要有3种:a.RULE(基于规则)b.COST(基于成本)c.CHOOSE(选择性)。设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其进行覆盖；9二、SQL优化建议原则1、选用合适的ORACLE优化器为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),你必须经常运行analyze命令,以增加数据库中的对象统计信息(objectstatistics)的准确性。在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(fulltablescan),你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器10二、SQL优化建议原则1、选用合适合ORACLE优化器访问Table的两种方式：全表扫描和通过ROWID访问表；全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描；基于ROWID的访问方式情况,能提高访问表的效率,ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高11二、SQL优化建议原则2、合理使用索引在建索引时，也不是索引越多越好，当一个表的索引达到4个以上时，ORACLE的性能可能还是改善不了，因为OLTP系统每表超过5个索引即会降低性能，而且在一个sql中，Oracle从不能使用超过5个索引；当我们用到GROUPBY和ORDERBY时,ORACLE就会自动对数据进行排序,而ORACLE在INIT.ORA中决定了sort_area_size区的大小,当排序不能在我们给定的排序区完成时,ORACLE就会在磁盘中进行排序,也就是我们讲的临时表空间中排序,过多的磁盘排序将会令freebufferwaits的值变高,而这个区间并不只是用于排序的12二、SQL优化建议原则2、合理使用索引select,update,delete语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片.在使用索引时一定要按索引对应字段的顺序进行引用。用（+）比用NOTIN更有效率。13二、SQL优化建议原则2、合理使用索引例子：createindextest_idxontest(hm,rq,xx)索引顺序：号码hm日期rq标志xxwherehm=“P1234”andrq=“06/06/1999”andxx=“DDD”wherexx=“DDD”andrq=“06/06/1999”andhm=“P1234”14二、SQL优化建议原则2、合理使用索引SELECT＊FROMordersWHERE(cust_num=126ANDorder_num1001)ORorder_num=1008；SELECT＊FROMordersWHEREcust_num=126ANDorder_num1001UNIONALLSELECT＊FROMordersWHEREorder_num=1008；15二、SQL优化建议原则3、选择最有效率的表名顺序(基于规则的优化器中有效)ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理；在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。16二、SQL优化建议原则3、选择最有效率的表名顺序(基于规则的优化器中有效)例如:表TAB116,384条记录表TAB21条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1,tab2执行时间0.96秒选择TAB1作为基础表(不佳的方法)selectcount(*)fromtab2,tab1执行时间26.09秒17二、SQL优化建议原则3、选择最有效率的表名顺序(基于规则的优化器中有效)如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。例如:EMP表描述了LOCATION表和CATEGORY表的交集.SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN----本脚本更有效率SELECT*FROMEMPE，LOCATIONL,CATEGORYCWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN18二、SQL优化建议原则4、WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句；根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。19二、SQL优化建议原则4、WHERE子句中的连接顺序SELECT*FROMEMPEWHERESAL5000ANDJOB=‘MANAGER’AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);SELECT*FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL5000ANDJOB=‘MANAGER’;---性能可能相差数十倍之多。20二、SQL优化建议原则讨论：下面哪个条件运作效率更高？方式A：wherecolA=10000ANDcolA=1方式B：wherecolA=1ANDcolA=10000方式C：wherecolAbetween1AND1000021二、SQL优化建议原则5、SELECT子句中避免使用“*”当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.实际上,ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间22二、SQL优化建议原则6、减少访问数据库的次数当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量23二、SQL优化建议原则6、减少访问数据库的次数方式一:SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=291;24二、SQL优化建议原则6、减少访问数据库的次数方式二:DECLARECURSORC1(E_NONUMBER)ISSELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=E_NO;BEGINOPENC1(342);FETCHC1INTO…,..,..;…..OPENC1(291);FETCHC1INTO…,..,..;CLOSEC1;END;25二、SQL优化建议原则6、减少访问数据库的次数方式三:SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;;26二、SQL优化建议原则7、使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKE‘SMITH%’;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKE‘SMITH%’;27二、SQL优化建议原则7、使用DECODE函数来减少处理时间SELECTCOUNT(DECODE(DEPT_NO,0020,’X’,NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,’X’,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SALFROMEMPWHEREENAMELIKE‘SMITH%’;28二、