SQL语句优化东软软件公司课件.ppt

1、SQLSQL语句优化语句优化东软股份社保医疗卫生夏淼主要内容nSQL语句优化原则nPL/SQL Developer用法简介n使用SQL语句监控数据库性能SQL语句优化原则n合理使用索引提高查询速度合理使用索引提高查询速度通常情况下,使用索引要比全表扫描块几倍乃至几千倍！n不合理的索引不合理的索引n检索数据量超过表中30%的记录数，使用索引将没有显著的效率提高，有些时候甚至会比全表扫描慢。n一个表中的索引并不是越多越好！索引过多增加系统开销。用不上索引的用不上索引的SQLSQL语句语句n使用不等于操作符使用不等于操作符(,!=)(,!=)SELECT*FROM dept WHERE staff_

2、num 1000;SELECT*FROM dept WHERE staff_num 1000;n使用使用 is null is null 或或 is not nullis not null任何包含null值的列都将不会被包含在索引中。解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)n索引列使用函数或计算索引列使用函数或计算SELECT*FROM fin_ipb_feeinfo WHERE trunc(fee_date)2007-01-01;SELECT*FROM fin_ipb_feeinfo WHERE fee_date to_date(2007-01-01,yyy

3、y-mm-dd);WHERE sal*1.1950 WHERE sal950/1.1 用不上索引的用不上索引的SQL语句语句n索引列与比较值数据类型不一致索引列与比较值数据类型不一致emp_no：NUMBER型WHERE emp_no=123（好）WHERE emp_no=123（也可）emp_type：CHAR型 WHERE emp_type=123（此时，查询时，不利用索引列）WHERE emp_type=123 n复合索引，必须使用主索引列复合索引，必须使用主索引列例：复合索引(deptno,job)WHERE deptno=20 AND job=MANAGER WHERE deptno

4、=20 WHERE job=MANAGER AND deptno=20 WHERE job=MANAGER 用不上索引的用不上索引的SQL语句语句nlikelike子句子句WHERE name LIKE 王%WHERE name LIKE%王%WHERE name LIKE%王 用不上索引的用不上索引的SQL语句语句nORDER BY子句列的顺序与索引列的顺序一致,列应为非空列。n使用表的别名多表连接时，使用表的别名来引用列。例：SELECT abc002,abd003FROM ab001,ab020 WHERE ab001.col2=ab020.col3 SELECT t1.abc002,t

5、2.abd003FROM ab001 t1,ab020 t2WHERE t1.col2=t2.col3 SQL语句优化语句优化n用NOT EXISTS代替NOT IN 例：SELECT.FROM emp WHERE dept_no NOT IN(SELECT dept_no FROM dept WHERE dept_cat=A);SELECT.FROM emp e WHERE NOT EXISTS(SELECT X FROM dept WHERE dept_no=e.dept_no AND dept_cat=A);SQL语句优化语句优化n用多表连接代替EXISTS子句例：SELECT.FROM

6、 emp WHERE EXISTS(SELECTX FROMdept WHEREdept_no=e.dept_no ANDdept_cat=A);SELECT.FROM emp e,dept d WHERE e.dept_no=d.dept_no AND dept_cat=A;SQL语句优化语句优化n用用UNION ALLUNION ALL替换替换UNION(UNION(如果有可能的话如果有可能的话)当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序。如果用UNION ALL替代UNION,这样排序就不是必要了。效率就会

7、因此得到提高。SQL语句优化语句优化n使用使用ROWIDROWID提高检索速度提高检索速度ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。因此那些基于索引列的查询就可以得到性能上的提高.对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高。例：SELECT rowid INTO v_rowid FROM t1 WHERE con1=.;UPDATE t1 SET col2=.WHERE rowid=v_rowid;SQL语句优化语句优化nWHEREWHERE过滤原

8、则过滤原则过滤记录数最多的条件放在最前面例：SELECT info FROM taba a,tabb b,tabc c WHERE a.acol between:alow and:ahigh AND b.bcol between:blow and:bhigh AND c.ccol between:clow and:chigh AND a.key1=b.key1 AND a.key2=c.key2;其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol列，依次类推。SQL语句优化语句优化n共享SQL语句为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放

9、在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。如经常使用select*from dept where deptno=值;值改为变量select*from dept where deptno=:d;则可以使用共享。可惜的是ORACLE只对简单的表提供高速缓冲(cach

10、e buffering),这个功能并不适用于多表连接查询。SQL语句优化语句优化n清除大量数据truncate替代delete，truncate不用commit，delete需要commit;n尽量避免使用select*ORACLE在解析的过程中,会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。n强制索引对ORACLE优化器缺省指定的索引不满意,需要手工修改，则采用强制索引：SELECT/*+INDEX(表名 索引名)*/列名FROM SQL语句优化语句优化PL/SQL Developer用法简介n界面简介n判断SQL语句是否优化n数据导入导出PL/SQ

11、L Developer用法简介使用SQL语句监控数据库性能n当前数据库各个终端连接数SELECTSELECT 连接数,终端名称 FROMFROM(SELECTSELECT COUNTCOUNT(TERMINAL)ASAS 连接数,TERMINAL ASAS 终端名称 FROMFROM v$session GROUPGROUP BYBY TERMINAL )ORDERORDER BYBY 连接数 DESCDESC;n查询性能最差的SQLSELECTSELECT disk_reads,executions,rows_processed,first_load_time,sql_text FROMFR

12、OM syssys.v_$sqlarea WHEREWHERE disk_reads 10 ANDAND executions 10 ORDERORDER BYBY first_load_time;使用SQL语句监控数据库性能n找使用CPU多的用户sessionSELECT a.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100 valueFROM v$session a,v$process b,v$sesstat cWHERE c.statistic#=12 AND c.sid=a.sid AND a.paddr=b.addr ORDER BY VALUE DESC;使用SQL语句监控数据库性能n当前各用户运行什么SQL语句SELECTSELECT osuser,username,sql_text FROMFROM v$session a,v$sqltext bWHEREWHERE a.sql_address=b.address ORDERORDER BYBY address,piece;使用SQL语句监控数据库性能THE END