数据库有优化案例培训.ppt

ICD数据问题总结培训,李凌志 37090,CONTENTS,重大事故总结重大事故分类主要数据库问题数据库案例总结,Page 3,2006年与数据库相关的事故、重大事故,Page 4,数据库问题总结,SQL性能问题，是引起重大事故的主要原因人为操作不规范，也是引起重大事故的原因ORACLE的BUG，引起业务中断诱发重大事故,Page 5,数据库性能案例,局点 新疆电信 日期 20060614表现症状 座席接入很慢，数据库的IDLE几乎为0,IOWAIT到达60%。SQL语句写法有问题 索引使用不合理，使用的效率比较低 对大表、分区表的查询，没有使用到索引 分区表的查询，没有添加分区条件数据库参数配置不合理 db_block_buffers 的值设置太小。问题解决情况：通过优化后，使系统的cpu的idle由0左右提升到业务高峰期的40%。,Page 6,主要有问题的SQL大表的查询没有使用到索引,SELECT MAX(T.SERIALNO)FROM T_PUB_COMMONINFO T WHERE T.ACCEPTERNO=:b1 AND PARTID=:b2问题描述该语句虽然按分区条件查询，但没有在字段ACCEPTERNO 上创建索引，导致对整个分区扫描 优化方式 对该字段创建分区索引，避免对分区扫描 create index ix_pub_commaccepterno on t_Pub_CommonInfo(ACCEPTERNO)online nologging tablespace service_hist_idx,Page 7,主要有问题的SQL对分区表查询没有使用到分区条件,SELECT COUNT(*)FROM T_AG_RECORDFILE WHERE SERIALNO=:b1问题描述：该表T_AG_RECORDFILE是分区表，数据量非常大，上面的查询，虽然用到了流水号对应的索引，但是没有添加分区条件，导致对每个分区都访问，增加了系统的开销。优化方式：添加按分区条件查询SELECT COUNT(*)FROM T_AG_RECORDFILE WHERE SERIALNO=:b1 AND PARTID=substr(:b1,5,4),Page 8,主要有问题的SQL索引创建和使用不合理,SELECT 1 FROM T_WF_SKILLSTAFFAUTH T WHERE T.SKILLID=:b1 AND T.STAFFNO=:b2问题描述：该表存在字段SKILLID的单独索引以及SKILLID和STAFFNO的组合索引。上面的SQL语句通过SKILLID字段的单独索引访问，效率比较低。优化方式：索引1 create index ix_skillid on T_WF_SKILLSTAFFAUTH(SKILLID)索引2 create index ix_skillid on T_WF_SKILLSTAFFAUTH(SKILLID,STAFFNO)当字段SKILLID单独出现在查询条件中时，可以通过索引2代替索引1，而这两个索引同时存在的时，ORACLE优先使用单个字段的索引，因此索引1是多余的,通过删除字段SKILLID对应的单独索引,使SQL语句使用到复合索引,Page 9,主要有问题的SQL使用的索引选择性不好,SELECT COUNT(*)FROM T_QC_INFO WHERE OBJECTID=:b1 AND RECORDERNUM=:b2问题描述：上面的SQL语句,使用的是OBJECTID字段的索引，通过查询该表的数据分析,发现该字段为有限的几类数据。问题解决：通过对RECORDERNUM 字段创建索引，使SQL语句用到新的索引，提高了查询效率。,Page 10,主要有问题的SQL使用的索引选择性不好,SELECT count(*)FROM(SELECT 1FROM t_Pub_CommonInfo a,T_PUB_USERSATISFY b,T_Pub_Orga c,t_ag_language d,t_Pub_Receptmethod e WHERE AcceptBeginTime BETWEEN TO_DATE(2005-06-01 00:00:00,YYYY-MM-DD HH24:MI:SS)AND TO_DATE(2006-06-14 23:59:59,YYYY-MM-DD HH24:MI:SS)And a.USERSATISFY=b.ID(+)And a.UserCity=c.OrgaID And a.LANGUAGE=d.LANGUAGEID(+)AND a.ACCEPTID=e.ID(+)AND a.servicecity in(select cityid from t_pm_cityauth where staffno=2049)AND(A.PartID=0601 OR A.PartID=0614)AND A.StaffCity=01 AND A.CalleeNo=5867696),上面的SQL使用的是蓝色字体所标注的字段对应的索引，该索引选择性非常差，因为该表是按日期范围分区，通过对CalleeNo字段创建索引；使SQL语句使用新的索引后，性能得到很大提高,Page 11,主要有问题的SQL没有办法通过索引进行优化,SELECT SERVICECLASSID,A.SERVICENAME,A.SUPERID,A.HANDLEHOURLIMIT,DECODE(SUBSTR(A.SERVICECLASSID,1,2),03,SUBSTR(A.SERVICECLASSID,3)|-|A.SERVICENAME,SUBSTR(A.SERVICECLASSID,-2)|-|A.SERVICENAME)SERVICEIDNAME FROM T_PUB_SERVICECLASS A WHERE NOT EXISTS(SELECT 1 FROM T_WF_SERVICECLASSSHIELD D WHERE A.SERVICECLASSID LIKE D.SERVICECLASSID|%)AND(A.CITYID=00 OR A.CITYID IN(SELECT CITYID FROM T_PM_CITYAUTH WHERE STAFFNO=:b1)ORDER BY A.SUPERID,A.SERVICECLASSID优化说明：上面的SQL语句没有办法使用到索引，需要根据业务逻辑的需要，对代码进行重写。,Page 12,使用到索引，但扫描范围大，查询效率低,SELECT MIN(t.classid)FROM t_97_interface t WHERE t.serialnumber LIKE:b1|%问题描述:上面的SQL语句，是来话接入流程中调用的语句，该语句调用非常频繁，对系统的性能影响对大，导致系统的CPU资源100%忙，虽然用到了serialnumber字段的索引，但是由于查询条件是LIKE:b1|%方式同时使用了min函数，导致查询效率低。问题解决：通过修改相应的业务逻辑后，按下面的方式对SQL语句进行优化后，问题立即解决。SELECT MIN(t.classid)from t_97_interface t where t.serialnumber i_CallerNo;,Page 13,主要有问题的SQL令人晕菜的SQL语句,select*from(SELECT 1 FROM t_Wf_Disposalstatus A,T_PUB_ServiceClass B,T_PUB_ORGA C,T_WF_NODE D,T_WF_ServiceInfo E,T_PUB_COMMONINFO F,T_wf_UserSatisfy G,T_wf_topayrecord H WHERE A.Accepttime=TO_DATE(2006-03-31 00:00:00,YYYY-MM-DD HH24:MI:SS)AND A.Accepttime=TO_DATE(2006-03-31 00:00:00,YYYY-MM-DD HH24:MI:SS)AND A.Accepttime TO_DATE(2006-03-31 23:59:59,YYYY-MM-DD HH24:MI:SS)AND A.ServiceClassID=B.SERVICECLASSID AND A.Servicecity=C.ORGAID AND C.Superid=0 AND A.satisfyid=G.id(+)AND A.SERIALNO=H.SERIALNO(+)AND A.SERVICECLASSID=H.SERVICECLASSID(+)AND A.NODEID=D.NODEID AND A.Serialno=E.Serialno AND A.ServiceClassID=E.ServiceClassID AND A.Serialno=F.Serialno(+)AND E.StaffCity=00 AND A.MONTHDAY BETWEEN 0331 AND 0331 AND E.MONTHDAY BETWEEN 0331 AND 0331)ORDER BY SERIALNO,优化说明：一条SQL语句对12个表访问，并且有三个是大的分区表，查询条件还有外连接，可以通过报表或修改表结构，增加冗余的方式减少表的访问达到优化效果。,Page 14,有问题的SQL令系统宕机的SQL语句,select count(t.dealid)as fuhegpend from t_wf_processhis t,t_pub_staff m where substr(t.tonodeid,-4)=0001 and t.handlestaff=m.staffno and m.orgaid like(select orgaid from t_pub_staff where staffno=860)|%and t.handledate=to_date(2006-03-08 00:00:00,yyyy-mm-dd hh24:mi:ss)and t.handledate=to_da te(2006-03-08 23:59:59,yyyy-mm-dd hh24:mi:ss)and t.dealid=(select max(dealid)from t_wf_processhis where serialno=t.se rialno and serviceclassid=t.serviceclassid),上面的SQL语句，通过页面定期刷新有多少待处理的工单，该语句由于没有使用到分区条件、存在嵌套查询，对系统资源消耗非常大。,Page 15,有问题的SQL优化后的SQL语句,select count(1)as fuhegpend from(select distinct t.serialno from t_wf_processhis t,t_pub_staff m where substr(t.tonodeid,-4)=0001 and t.handlestaff=m.staffno and m.orgaid like(select orgaid from t_pub_staff where staffno=860)|%and t.handledate=to_date(2006-03-08 00:00:00,yyyy-mm-dd hh24:mi:ss)and t.handledate=to_date(2006-03-08 23:59:59,yyyy-mm-dd hh24:mi:ss),问题分析：查询的目的是查出某个部门的工单数，由于一个部门一天的工单数，不可能很多结果，通过添加distinct去掉嵌套查询，达到优化SQL的目录,Page 16,有问题的SQL相同的问题不同的SQL,SELECT SERIALNO FROM T_WF_DISPOSALSTATUS A WHERE(ACCEPTPHONE=:b1 OR CALLERNO=:b2 OR USERPHONE1=:b3)AND ACCEPTTIME SYSDATE-:b4 AND ROWNUM=1 UNION SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS A WHERE(ACCEPTPHONE=:b1 OR CALLERNO=:b2 OR USERPHONE1=:b3)AND ACCEPTTIME SYSDATE-:b4 AND ROWNUM=1,Page 17,有问题的SQL优化后的SQL,SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS WHERE(ACCEPTPHONE=:B4)AND ACCEPTTIME SYSDATE-:B1UNION SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS WHERE(CALLERNO=:B3)AND ACCEPTTIME SYSDATE-:B1UNION SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS A WHERE(USERPHONE1=:B2)AND ACCEPTTIME SYSDATE-:B1,优化方式：通过用union 代替or，通过创建索引，避免了全表扫描，由于每个or对应的条件不同，因此此处不能用union all代替or,否则会改变查询结果,Page 18,有问题的SQL相同的问题的不同的SQL,SELECT count(*)FROM(SELECT/*+index(a,IX_COMMONINFO_CALLERNO)*/SerialNo,DECODE(UserName,没有此客户资料!,无资料!,UserName)as UserName,FROM t_Pub_CommonInfo a,T_PUB_USERSATISFY b,T_Pub_Orga c WHERE AcceptBeginTime BETWEEN TO_DATE(2006-05-01 00:00:00,YYYY-MM-DD HH24:MI:SS)AND TO_DATE(2006-05-26 23:59:59,YYYY-MM-DD HH24:MI:SS)And a.USERSATISFY=b.ID(+)And a.UserCity=c.OrgaID AND A.PartID=0501 AND A.PartID=0526 AND(AND A.UserCity=02 AND A.StaffCity=02),此SQL语句同上面的SQL语句存在同样的问题。此语句虽然用到了AcceptBeginTime 对应的索引，但是此索引的效率非常差，反而会使查询效率更低,Page 19,有问题的SQL使用的索引不是最优的,Page 20,有问题的SQL优化后的SQL语句,【优化前的SQL】SELECT DISTILLSTAFF,HANDLESKILLID,HANDLESTATUS FROM T_WF_PROCESS WHERE SERIALNO=:B2 AND SERVICECLASSID=:B1 AND HANDLESTATUS IN(0,4)【优化后的SQL】SELECT DISTILLSTAFF,HANDLESKILLID,HANDLESTATUS FROM T_WF_PROCESS WHERE SERIALNO=:B2 AND SERVICECLASSID=:B1 AND HANDLESTATUS0 IN(0,4),优化说明：通过添加表达式，屏蔽选择性比较差的索引,Page 21,有问题的SQL优化后的SQL语句执行计划,Page 22,有问题的SQL驱动表全表扫描,Page 23,有问题的SQL优化后的SQL,select distinct a.nextnodeid as nodeid,a.nextnodeid|c.nodename as nodename from t_wf_nodeskill a,t_wf_skillstaff b,t_wf_node c where b.skillid=a.nextskillid and b.staffno=:P1 and c.nodeid=a.nextnodeid通过创建下面的索引 create index ix_wf_nodeskillnextskillid on t_wf_nodeskill(nextskillid)tablespace service_hist_idx create index ix_wf_ndeskillstaffno on t_wf_skillstaff(staffno)tablespace service_hist_idx,Page 24,有问题的SQL优化后的SQL语句的执行计划,Page 25,那些情况需要关注执行计划,对大表的查询，包括分区表、历史记录表对多个表的查询，有很过多表的关联查询，超过3个。调用非常频繁的SQL语句，有的SQL语句调用非常频繁，每次调用虽然不特别消耗资源，但是调用次数多，累计效应对系统的性能影响非常明显。,Page 26,对分区表的查询,尽可能的用到索引，尽可能的避免对全表或分区扫描查询分区范围尽可能的少尽可能用到选择性比较好的分区索引。,Page 27,常用的SQL调优手段,通过表达式，屏蔽性能比较差的索引通过指定索引，使用SQL语句用到比较优的索引通过选择CBO或RBO的方式，改变执行计划，从而使用SQL语句用到最佳的索引通过优化对应SQL语句的写法，达到优化效果通过修改表结构，减少过多表的关联查询，尤其是对历史表、大分区表的查询。,