SQL语句优化,东软股份社保医疗卫生 夏淼,主要内容,SQL语句优化原则 PL/SQL Developer用法简介 使用SQL语句监控数据库性能,SQL语句优化原则,合理使用索引提高查询速度 通常情况下,使用索引要比全表扫描块几倍乃至几千倍！ 不合理的索引 检索数据量超过表中30%的记录数，使用索引将没有显著的效率提高，有些时候甚至会比全表扫描慢。 一个表中的索引并不是越多越好！索引过多增加系统开销。,用不上索引的SQL语句,使用不等于操作符(, !=) SELECT * FROM dept WHERE staff_num 1000; SELECT * FROM dept WHERE staff_num 1000; 使用 is null 或 is not null 任何包含null值的列都将不会被包含在索引中。 解决这个问题的办法就是：建表时把需要索引的列 定义为非空(not null),索引列使用函数或计算 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,yyyy-mm-dd); WHERE sal*1.1950 WHERE sal950/1.1 ,用不上索引的SQL语句,索引列与比较值数据类型不一致 emp_no： NUMBER型 WHERE emp_no=123（好） WHERE emp_no= 123（也可） emp_type：CHAR型 WHERE emp_type=123 （此时，查询时，不利用索引列） WHERE emp_type=123 复合索引，必须使用主索引列 例：复合索引(deptno,job) WHERE deptno=20 AND job=MANAGER WHERE deptno=20 WHERE job=MANAGER AND deptno=20 WHERE job=MANAGER ,用不上索引的SQL语句,like子句 WHERE name LIKE 王% WHERE name LIKE %王% WHERE name LIKE %王 ,用不上索引的SQL语句,ORDER BY子句 列的顺序与索引列的顺序一致 ,列应为非空列。 使用表的别名 多表连接时，使用表的别名来引用列。 例： SELECT abc002,abd003 FROM ab001 ,ab020 WHERE ab001.col2=ab020.col3 SELECT t1.abc002,t2.abd003 FROM ab001 t1,ab020 t2 WHERE t1.col2=t2.col3 ,SQL语句优化,用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语句优化,用多表连接代替EXISTS子句 例： SELECT FROM emp WHERE EXISTS ( SELECT X FROM dept WHERE dept_no=e.dept_no AND dept_cat=A); SELECT FROM emp e,dept d WHERE e.dept_no=d.dept_no AND dept_cat=A; ,SQL语句优化,用UNION ALL替换UNION(如果有可能的话) 当SQL语句需要UNION两个查询结果集合时,这两个结果 集合会以UNION-ALL的方式被合并, 然后在输出最终结果 前进行排序。 如果用UNION ALL替代UNION, 这样排序就不是必要了。 效率就会因此得到提高。,SQL语句优化,使用ROWID提高检索速度 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语句优化,WHERE过滤原则 过滤记录数最多的条件放在最前面 例：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语句优化,共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语 句存放在内存中.这块位于系统全局区域SGA(system global area)的共 享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因 此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执 行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好 的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内 存的使用。 如经常使用select * from dept where deptno=值; 值改为变量 select * from dept where deptno=:d; 则可以使用共享。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这 个功能并不适用于多表连接查询。,SQL语句优化,清除大量数据 truncate替代delete， truncate不用commit，delete需要commit; 尽量避免使用select * ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作 是通过查询数据字典完成的, 这意味着将耗费更多的时间。 强制索引 对ORACLE优化器缺省指定的索引不满意,需要手工修改，则采用强制 索引： SELECT /*+INDEX(表名 索引名)*/ 列名 FROM ,SQL语句优化,PL/SQL Developer用法简介,界面简介 判断SQL语句是否优化 数据导入导出,PL/SQL Developer用法简介,使用SQL语句监控数据库性能,当前数据库各个终端连接数 SELECT “连接数“,“终端名称“ FROM ( SELECT COUNT(TERMINAL) AS 连接数, TERMINAL AS 终端名称 FROM v$session GROUP BY TERMINAL ) ORDER BY 连接数 DESC; 查询性能最差的SQL SELECT disk_reads,executions,rows_processed, first_load_time,sql_text FROM sys.v_$sqlarea WHERE disk_reads 10 AND executions 10 ORDER BY first_load_time;,使用SQL语句监控数据库性能,找使用CPU多的用户session SELECT a.sid,spid,status,substr(a.program,1,40) prog, a.terminal,osuser,value/60/100 value FROM v$session a,v$process b,v$sesstat c WHERE c.statistic#=12 AND c.sid=a.sid AND a.paddr=b.addr ORDER BY VALUE DESC;,使用SQL语句监控数据库性能,当前各用户运行什么SQL语句 SELECT osuser, username, sql_text FROM v$session a, v$sqltext b WHERE a.sql_address =b.address ORDER BY address, piece;,使用SQL语句监控数据库性能,THE END,