【网学网提醒】：网学会员为您提供SQL优化培训参考，解决您在SQL优化培训学习中工作中的难题，参考学习。
SQL优化培训
2011-5-27
一、培训背景培训背景
开发人员通常很少注意SQL代码的效率，而更着眼于功能的实现，至于性能问题通常认为是次要的，而且在应用系统开发初期，由于数据量较小，对于SQL语句不容易体现出写法的差异。但是一旦这些应用作为生产系统上线运行，随着数据库中数据量的增加，大量并发访问，系统的响应速度就可能成为系统需要面对的最主要的问题之一。在少量用户下性能可以接受的SQL,在大量用户并发条件下就可能成为性能瓶颈。有时候开发人员很难相信一条SQL就导致数据库性能的下降，然后事实就是如此，一条低效的SQL语句就可能毁掉整个数据库。所以在系统设计及开发过程中，必须考虑诸多细节。
二、培训目的培训目的
了解SQL性能调整的一般方法明确开发人员性能调整的责任（结合性能问题处理流程性能调整方法，团队协作（dba，设计及开发人员、现场维护人员等））【系统性能调整方法：调整业务功能、调整数据设计、调整过程设计、调整SQL语句、调整内存分配、调整IO、调整资源争用、调整OS】角色分配：应用系统开发人员的责任是调整SQL语句
优化基础三、SQL优化基础
数据库中的SQL代码，是影响数据库运行效率的关键因素，在程序开发过程中，要时刻注意性能的优化。良好的SQL程序应满足以下两个条件：SQL语句能被Oracle优化器高效解析（共享SQL）SQL语句能尽快的访问并返回数据如何满足第一条，我们需要了解SQL语句的处理过程，如下所述：（1）Parse在sharedpool中搜寻相同的语句、检查句法、检查权限、用子查询代替视图定义、确定执行计划
（2）Bind扫描语句查找bind变量、为变量赋值（3）执行Execute应用执行计划、进行必要的IO和排序动作（4）Fetch从查询结果获得结果（行），以排序的结果返回。从上述SQL执行过程中，我们可以了解到共享SQL优势：减少parse、动态内存调整、增进内存使用效率如何做到SQL共享，共享SQL要求：相同的语句：--大小写相同、单词间隔相同（空格等）、注释都要相同、涉及的数据库对象相同、捆绑变量类型相同。举例说明如下：下述语句是不同的select*fromempwhereempno=7788;select*Fromempwhereempno=7788;使用绑定变量的情况：select*fromempwhereempno=:c;select*fromempwhereempno=:d;如果:c和:d变量类型不同，则语句不同，但命名不同没关系，Oracle内部重新内部表示。上述两条语句会被内部表示成：select*fromempwhereempno=:b1;编写共享SQL使用存储过程和包（package）使用触发器（trigger）使用其他的库函数和过程注意：
存储过程中的动态SQL，要使用using语句进行绑定变量，以营销数据库中的程序作为讲解示例（在数据库中搜索using关键字）
Java中的绑定变量未绑定变量的情况：Stringv_id='xxxxx';Stringv_sql='selectnamefromtable_awhereid='+v_id;以上代码，看起来是使用了变量v_id，但这却是java的程序变量，而不是oracle的绑定变量，语句传递到数据库后，此java的程序变量已经被替换成具体的常量值，变成：select*fromtable_awherename='xxxxx';假定这个语句第一次执行，会进行硬分析。后来，同一段java代码中v_id值发生变化（v_id='yyyyyy'），数据库又接收到这样的语句：select*fromtable_awherename='yyyyyy';因此对第二条语句会又做一次硬分析。这两条语句的执行计划可是一样的！其实，只需将以上java代码改成以下这样，就使用了oracle的绑定变量：Stringv_id='xxxxx';Stringv_sql='selectnamefromtable_awhereid=?';//嵌入绑定变量stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//为绑定变量赋值stmt.executeQuery();营销系统WEB应用程序中绑定变量的写法，如下所示：StringsqlStatement="Selectuser_no,user_namefromUSER_FILESwhereUSER_NO=?";
ArrayListarrayParam=newArrayList();arrayParam.add(userNo);ResultSetrs=dbTool.executeQuery(sqlStatement,arrayParam);如何满足良好SQL第二条，需要了解以下内容了解CBO优化器CBO优化器依赖于所收集的统计信息（用dbms_stats/Analyze收集信息），统计信息大致包括以下内容：
表有多少行，占用多少数据块列有多少个Null值、不同值列的最大值和最小值，及值的分布情况索引的层次、结点数、叶结点数，及行的分布状况(Cluster)根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引了解索引优化索引的使用，对于提高查询速度来说非常重要。当从表中访问数据时，Oracle提供了两个选择：从表中读取每一行(即全表扫描)，或者通过ROWID一次读取一行。当访问大型表的少量行时，您可能想使用索引。例如，如果只访问大型表中5%的行，并且使用索引标识读取的块，则可以执行较少的I/O。如果没有使用索引，则要读取表中所有的块。表中采用了索引并不意味者Oracle一定会使用索引，Oracle认为索引并不是在任何情况下都可以提高查询效率，它通过自身的优化器进行分析判断以决定使用索引(index)扫描还是全局(recursive)扫描方法进行数据检索，一般情况下，用户查询的数据量超过表数据量的5%时就会执行全表扫描。注：在不同的资料中，对是否使用索引的读取记录的百分比不太一致，基本上是一个经验值。不过，可
以肯定的是读取记录的百分比越低，使用索引越有效。建立索引可以提高查询的性能，索引能提高速度的关键是索引所占的空间要比表小得多。但是由于必须同时维护数据和索引，会增加DML（insert、update、delete）操作的代价。因此，应该有选择性的建立索引。而对于未使用的索引应该及时清除。在选择索引的列时应该考虑：在WHERE语句中经常使用的列；经常被用于连接到其他表的列；B树索引的索引列应该具有较高的选择性，比如唯一性索引；不要在只包含少量不同值的列或表达式上建立B树索引，比如在50000条员工记录表上的性别只有两个值，选择性不好；不要索引经常修改的列；
如果一些列出现在WHERE语句中，但都是在函数或运算中使用，这些列不适合建立B树索引（在等号的右边使用函数或者建立基于函数的索引）；为了提高并发访问，外键列应该建立索引（父表的DML会对子表加锁）；选择一个索引列时，应该从查询的性能收益和对于INSERT、UPDATE和DELETE操作的影响已经占用空间两个方面来进行权衡。索引能提高速度的关键就是索引所占的空间要比表小得多。需要重点注意一下复合索引，复合索引具有以下优点：提高选择性：和单列索引相比，复合索引的选择性更高。降低I/O：如果Oracle需要访问的所有列都存在于复合索引中，则可以根据索引直接返回结果，避免表扫描。复合索引应该选择的列：经常在WHERE语句中使用AND操作连接在一起使用，且组合起来比单个列具有更高的选择性的列。如果多个查询包含相同列构成的结果集，可以考虑将这些列组合起来建立复合索引。复合索引中确定列的顺序，这一点很重要，举例说明：Createtabletest(indanumber,indbnumber,descrvarchar2(10));createindexmultindexonmultiindexusage(inda,indb);select*fromtestwhereinda=1;select*fromtestwhereindb=1;在WHERE语句中使用的列应该放到前面；频繁出现在WHERE语句中的列应该放到复合索引的最前面，保证指定这些列的查询可以使用索引；如果所有列的使用几率相差不多，把选择性高的列放到前面；如果所有列的使用几率相差不多，而表的数据是按照某个列的键值顺序进行物理存储的，则将这一列放到最前面。和一般索引相比，复合索引对DML的影响更大，而且占用磁盘空间也更多，因此应该权衡收益之后使用。
未用到索引的原因：类型不匹配对列使用了函数，而索引只是基于列的使用索引实际会降低速度很长时间没有分析表了，表的增长较快，这样CBO会作出错误的判断。结果集返回的比例过大，优化器认为全表扫描是更
有效率数据库参数设置不合理，导致函数索引不可用Oracle提供多种不同类型的索引，B树索引是最常用的索引，其构造是基于二叉树的,由分支块和叶子块组成，包括每个被索引列的值和行所对应的ROWID，其目标是尽可能的减少Oracle查找数据所花费的时间。Oracle会在以下情形下自动建立或使用B树索引：定义主键约束定义唯一性约束下面介绍几种特殊类型的索引：函数索引：将函数的表达式建立在索引中，使得对列的函数操作也可以使用索引。只有使用CBO才能利用函数索引。CREATEINDEXFBI_UPPER_LASTNAMEONCUSTOMERS(upper(cust_last_name));SELECT*FROMCUSTOMERSWHEREUPPPER(CUST_LAST_NAME)=’SMITH’;函数索引的使用：方便频繁计算的表达式方便大小写敏感的查询
BITMAP索引：建立在选择性低的列上，对于多个列上的BITMAP索引的AND和OR操作具有很高的执行效率。BITMAP索引占用空间小，建立速度快，对批量操作只处理一次，因此特别合适在数据仓库中使用。但是BITMAP索引的锁粒度较大，大量的并发DML操作会极大的影响性能，因此不适合OLTP系统。BITMAP索引也只会被CBO使用。SQL优化实践，部分案例讲解
营销系统中客服发送电费短信程序，减少PL/SQL程序的单元迭代和迭代的时间，提升业务处理性能任何牵涉到循环逻辑的PL/SQL程序单元都可能存在大幅度提高性能的空间。可以通过两种方式来改善这种类型的程序潜在的性能。第一种方法是通过逻辑的重构来减少迭代的数量，并保持功能性结果不变。第二种方法是减少每次迭代的时间。无论采用哪一种方法，都可以大幅度提高系统运行的性能。在更清晰地阐述该观点之前，让我们假设这样一种情况：我们需要在PL/SQL程序中处理9000个雇员的记录，假设每处理一个雇员的记录需要花费2s。这样总共需要花费18000s，也就是5h。如果每处理一个雇员的记录的时间可以减少到1s，那么处理9000个雇员的记录所需花费的时间也就减少了9000s或者2.5h——差异是如此巨大！以KF_P_SMS_SEND_FEE_AUTO20110526和KF_P_SMS_SEND_FEE_AUTO20110527为例讲解。【此类程序还包括电费回收率的job】综上所述，调整SQL一般方法添加索引重写SQL只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。以（proc_df_ds_balance）为例
四、培训总结
本次培训不是为了教给大家SQL优化的技巧，而只是讲述一些SQL优化的入门性的基础知识，是一般性的优化方法。不应迷信一些所谓的技巧，SQL性能优化涉及较多Oracle数据库知识，希望各位能够在以后的时间里继续了解以下
内容：认真思考理解CBO的部分原理，阅读并理解执行计划查出目前数据库中各个索引与表的大小的比值，考虑索引存在的必要性，或索引列的选择是否正确了解和合理使用OptimizerHint，但是需要注意，使用Hint的会导致较高的维护代价（数据库版本变化、数据库重组，初始参数变化等可能导致执行计划的不稳定）了解CBO中的JOIN方法，例如NESTLOOP、HASHJOIN等
用数据库快照（awr）找出一些reads比较大的SQL，使用CBO，并在CBO下进行调整2011-5-27<全文完>
