SQL 优化培训


Agenda . 目的 . SQL优化的必备基础知识 . 写高效sql的常用原则 . Topsql优化实例 .理解SQL优化的基本理论知识 .共享SQL优化的经验 .在设计及编程阶段考虑应用的优化 目的 SQL优化的必备知识 – sql处理流程 .Sql执行三个主要阶段 - Parse (分析阶段) - Execute (执行) - Fetch (结果返回) .每个阶段都消耗系统资源 - 减少parse次数,提高sql的共享性 - 优化执行计划,提高执行效 SQL优化的必备知识 – sql执行的三个主要 阶段 .Parse (分析阶段) - 检查语法,检查语义,权限 - 在内存(shared pool) 中寻找语句(并再次做语义检查, 目的判断是否为同一用户下语句) - 生成执行计划 SQL优化的必备知识 – sql执行的三个主要 阶段(续1) .Execute (执行) - 应用Parse阶段生成的执行计划 - 如果数据在内存中则完成逻辑读,否则物理读 - 对于UPDATE、DELETE语句先锁住有关的行 .Fetch (结果返回) - 如果是select 语句,还要返回执行结果 - 根据需要完成排序 SQL优化的必备知识 – sql执行的三个主要 阶段(续2) 共享SQL语句  为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE 将SQL语句存放在内存中。执行一个SQL语句(游标)时,如果它 和之前的执行过的语句完全相同,ORACLE就能很快获得已经 被解析的语句以及最好的执行路径。  ORACLE采取的是一种严格匹配,要达成共享,SQL语句必须完全 相同(包括空格,换行等)。  共享的语句必须满足三个条件:  A. 字符级的比较  B. 两个语句所指的对象必须完全相同  C. 两个SQL语句中必须使用相同的名字的绑定变量 .表的访问 - Table Access Full(Multi-blocks I/O,可并行) - Table Access By Rowid (先索引访问,得到Rowid) .索引的访问 - Index Unique Scan (Single Block I/O,不可并行) . 返回单个值;唯一索引,等于查询 . 例: select id from tb_location_his a where a.id = :1 - Index Range Scan (Single Block I/O,不可并行) . 返回多行记录;范围查询 . 例: select id from tb_location_his a where a.id <= :1 SQL优化的必备知识 – 常见的数据访问路 径 . 索引的访问 - Index Full Scan . 对Index做全部的扫描,返回所有Index的值 . 例:select id from tb_location_his a order by id - Index Fast Full Scan(Multi-blocks I/O,可并行) . 可以并行的访问整个Index . select id from tb_location_his a - Index skip Scan . 跳过复合索引中的领先列,适用于领先列选择性低的情况 如,(fee_type,finist_time) SQL优化的必备知识 – 常见的数据访问路 径(续) .Nested Loop Joins - 对驱动表中每条满足条件的记录,找出被驱动表中的对 应记录;驱动表中满足条件的记录数决定循环次数 - 适合与返回结果少的查询 - 被驱动表上一般有索引 .Hash Joins - 对连接中较小的表,在内存中建hash table - 适合与CBO下,返回结果较多的等于查询 SQL优化的必备知识 – 常见的表的连接方 法 .Sort Merge Joins - 两个表分别按连接键排序,再合并 - RBO下,适合于返回结果较多的连接查询 - CBO下,适合于返回结果较多的非等于连接查询 - 如果两个表已经预先排序(如索引字段),则效率比较 高 .Cartesian Joins - 没有连接条件,或是多对多的连接 - 一般要避免 SQL优化的必备知识 – 常见的表的连接方 法(续) .工具 - 最常用的是PL/SQL .步骤 - File->New->explain plan window->F8[execute button] 对已有sql语句按F5 SQL优化的必备知识 – 如何看执行计划 .如何看内容 - 从最底层开始看(从内到外,从上往下) - 驱动表 - 访问路径 - 连接方法 - 连接顺序 .例子 SQL优化的必备知识 – 如何看执行计划 (续1) 例: SQL优化的必备知识 – 如何看执行计划 (续2) SELECT子句中避免使用 ‘ * ‘ ORACLE在解析的过程中, 会将‘*’ 依次转换成所有的列名, 这个工作是通过查 询数据字典完成的。 常用数据字典 user_tables user_tab_cloumns user_indexes user_objects user_source … SQL优化的必备知识-如何提高SQL的执行 效率 分析函数 Function([arguments]) OVER (partition by col1 order by col2) 开窗函数的的理解: 开窗函数指定了分析函数工作的数据窗口大小,举例如 下: over( partition by deptno order by salary)按照部门分 区,salary排序进行累计,order by是个默认的开窗函数 SQL优化的必备知识 – 分析函数 .union :会把结果集中相同的记录合并成一条记录, 相当于做了一次distinct。 .union all: 不会对结果集做任何处理 .union=>union all :当明确知道不同union子句的结 构集记录不会有重复,或者最终结果集不要求合并 重复记录时,使用union all 写高效sql的一些原则– Union & Union All .Like:%写在后面,使用index 例1,使用index: select certi_code from t_customer where real_name like ‘王%’; 例2,不使用index: Select certi_code from t_customer where certi_code like ‘%032’; 写高效sql的一些原则– Like 操作 . Where子句条件左边不使用函数 - 函数在左边使Index不被引用 - 例: Select access_time,login_time From t_sesion_trace Where 24 * 3600 * (sysdate - a.access_time) < 300; - 解决方法:使用函数索引或改写语句 Select access_time,login_time From t_sesion_trace Where access_time > sysdate - 300/ (24 * 3600); 补充:组合索引中,只有使用了引导列,才会走组合索引,否则不会使用此索引 如:idx_emp(col1,col2,col3….),则where col1 = ?用到了索引,但where col2 = ?不会使用该索引 写高效sql的一些原则–条件左边不使用函 数 .隐含数据转换导致索引不被引用 - 例:policy_type char(1) Select policy_id from t_contract_master Where policy_type=2; - 解决方法:建立规范,避免不必要的类型转换,如 uw_status/change_status/policy_type等等 写高效sql的一些原则 – 避免用隐含数据转 换 写高效sql的一些原则 –where替换 having  用Where子句替换HAVING子句  避免使用HAVING子句, HAVING 只会在检索出所有 记录之后才对结果集进行过滤。 这个处理需要排序, 总计等操作。 如果能通过WHERE子句限制记录的 数目,那就能减少这方面的开销。  HAVING 中的条件一般用于对一些函数的比较,如 COUNT() > 等。 除此之外,一般的条件应该写在 WHERE子句中 。 . 并非exists/not exists都比in/not in快 看执行计划及数据分布决定那种更好 IN的执行流程 select * from T1 where x in ( select y from T2 ) 可以理解为: select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y; EXISTS的执行流程 select * from t1 where exists ( select null from t2 where y = x ) 可以理解为: for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loop 写高效sql的一些原则– in/exists;not in/not exists 在未对表进行分析前,若两个表数据量差异很大,则外层表是 大表时使用IN较快, 外层表是小表时使用EXISTS较快;若两表数据量接近,则使 用IN较快。 分析表后无论用IN还是EXISTS都变得更快,由于执行计划一 样,所以速度一样; 写高效sql的一些原则– in/exists;not in/not exists(续1) NOT EXISTS的执行流程 select ..... from rollup R where not exists ( select 'Found' from title T where R.source_id = T.Title_ID); 可以理解为: for x in ( select * from rollup ) loop if ( not exists ( that query ) ) then OUTPUT end if; end; 写高效sql的一些原则– in/exists;not in/not exists(续2) NOT EXISTS 与 NOT IN 不能完全互相替换,看具体的需求。 如果选择的列可以为空,则不能被替换。 对于not in 和 not exists的性能区别 not in 只有当子查询中,select 关键字后的字段有not null约束或者有这种暗示时用not in 如果主查询中表大,子查询中的表小但是记录多,则应 当使用not in,并使用anti hash join. 写高效sql的一些原则– in/exists;not in/not exists(续3) 写高效sql的一些原则– 尽量用表连接 用表连接替换EXISTS 通常来说 , 采用表连接的方式比EXISTS更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’); 更高效 SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A’ ; 带有DISTINCT,UNION,MINUS,INTERSECT, ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排 序(SORT)功能。 DISTINCT需要一次排序操作, 而其他 的至少需要执行两次排序。 例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行 UNION时, 又一个唯一排序(SORT UNIQUE)操作被执 行而且它只能在前面的嵌入排序结束后才能开始执行。 嵌入的排序的深度会大大影响查询的效率。 写高效sql的一些原则– 避免使用消耗资源 的操作 .驱动表的记录越少,效率越高 .在不影响结果的前提下,增加驱动表的条件 .去掉不必要的连接表 - 即不出现在select list中,也未作为where中的过滤条件 .减少中间结果集,再与后续表连接,提高效率 .避免CARTESIAN连接 : 无条件连接或多对多连接 . 外连接使索引不被引用,部分外连接可避免 写高效sql的一些原则 –其他 不会使用索引的情况  用UNION替换OR (适用于索引列)  通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。  Order by语句  任何在Order by语句的非索引项或者有计算表达式(函数、联接或者附 加等)都将降低查询速度。  解决这个问题的办法就是重写Order by语句以使用索引,也可以为所使 用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达 式。  ORDER BY 子句只在两种严格的条件下使用索引  ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。  ORDER BY中所有的列必须定义为非空  避免在索引列上使用NULL  避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引。对 于单列索引,如果列包含空值,索引中将不存在此记录。 对于复合索引, 如果每个列都为空,索引中同样不存在此记录。 如果至少有一个列不 为空,则记录存在于索引中。 . Bind变量的使用提高了sql在内存中的共享性,减少Parse次数及 Parse cpu时间 . 对数据分布均匀的列(如主键ID)使用bind变量 - 例: select * from tb_terminal a where a.id = :b 对数据分布不均匀的列,如状态字段,类型字段等列,使用值(非bind变量),保证执行计划 的正确 Select * from t_xxx where status=‘2’; select count(*) from tb_test_a a where a.type = 1 and a.name = '1a'; 原因:使用绑定变量会导致优化器不能使用数据库中的列统计,从而选择了较差的执行计 划。硬编码的sql则可以使用列统计 如何统计分析列产生直方图: analyze table ictmap1082.tb_test_a compute statistics for columns type size 10; 或者dbms_stats.gather_table_stats(xxxx); select * from dba_tables where table_name = upper('tb_test_a'); select * from dba_tab_columns a where a.table_name = upper('tb_test_a'); select * from dba_tab_histograms a where a.table_name = upper('tb_test_a'); endpoint_value表示列值;endpoint_number累积值; 写高效sql的一些原则 –合理使用bind变量 . 使用hint可以强制使用某个执行计划,一般用于sql优化,弥补优 化器给出的某些执行计划的缺陷 . 当数据状况变化后,hint给出的执行计划不会改变,可能引起新的 问题 例:select /*+index(a, PK_TB_TERMINAL)*/ a.type from tb_terminal a 尽量通过改写sql或分析数据使执行计划正确 . 使用hint的语句修改时,一定要看修改后的语句是否需要hint 写高效sql的一些原则 – 谨慎使用hint . 逐单处理一般比批处理效率低 . 在oltp中,逐单处理的transaction长时间锁住记录,会造 成系统阻塞 . 将效率低的逐单处理改为bulk处理 写高效sql的一些原则 – 慎用逐单处理模式 . 将效率低的逐单处理改成队列处理或晚上批处理模式 写高效sql的一些原则 – 慎用逐单处理模式 (续) . With as子句,提供子查询结果的重用,在子查询含计算、汇总操 作时,可提高性能 . 例: 写高效sql的一些原则 –报表类查询:with as的使用 . 有些子句可以合并,减少表的查询次数,提高效率 . 例: 写高效sql的一些原则 –报表类查询:多个 子句的合并 . 例:修改后 写高效sql的一些原则 –报表类查询:多个 子句的合并 (续) 写高效sql的一些原则:避免表的多次访问  例如:  优化前:  select a.name, a.staff_all, b.staff_diff  from (select tr.id, tr.name, count(*) as staff_all  from t_report tr  group by tr.id, tr.name) a,  (select tr.id, tr.name, count(*) as staff_diff  from t_report tr  where tr.diff > 0  group by tr.id, tr.name) b,  (select tr.id, tr.name, count(*) as staff_type  from t_report tr  where tr.type = 2  and tr.name = 'xxx'  and exists  (select 1 from t_report_other tro where tr.id = tro.id)  group by tr.id, tr.name) c  where a.id = b.id  and a.name = b.name  order by a.id, b.name; 写高效sql的一些原则:避免表的多次访问  优化后:  select count(*) as staff_all,  sum(case  when tr.diff > 0 then  1  else  0  end) as staff_diff,  sum(case  when tr.type = 2 and tr.name = 'xxx' and exists  (select 1 from t_report_other tro where tr.id = tro.id) then  1  else  0  end) as staff_type  from t_report tr  group by tr.id, tr.name 高效sql举例  假设要进行装运(shippmengt)处理,一次装运由一些不同的订单组成, 每张订单都必须分别做准备;只有装运涉及的每张订单都完成时装运才准备 就绪。现判断装运涉及的所有订单都已完成:  优化前:  Select shipment_id  From shipments  Where not exists (select null from orders where order_complete = ‘n’ and orders.shipment_id = shipments.shipment_id) 高效sql举例  优化后:  Select shipment_id  From orders  Group by shipment_id  Having sum(case when order_complete = ‘n’ then 1 else 0 end) = 0 Discussion Time Thanks ! Any Question ?
还剩40页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 9 人已下载

下载pdf

pdf贡献者

ggnanqi

贡献于2012-04-15

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf