《数据库有优化案例培训》由会员分享，可在线阅读，更多相关《数据库有优化案例培训（28页珍藏版）》请在金锄头文库上搜索。
1、ICD数据问题总结培训,李凌志 37090,CONTENTS,重大事故总结 重大事故分类 主要数据库问题 数据库案例 总结,Page 3,2006年与数据库相关的事故、重大事故,Page 4,数据库问题总结,SQL性能问题，是引起重大事故的主要原因 人为操作不规范，也是引起重大事故的原因 ORACLE的BUG，引起业务中断诱发重大事故,Page 5,数据库性能案例,局点 新疆电信 日期 20060614 表现症状 座席接入很慢，数据库的IDLE几乎为0,IOWAIT到达60%。 SQL语句写法有问题 索引使用不合理，使用的效率比较低 对大表、分区表的查询，没有使用到索引 分区表的查询，没有添加
2、分区条件 数据库参数配置不合理 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_co
3、mmaccepterno 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(*) FR
4、OM 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_SKILLS
5、TAFFAUTH(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 问题
6、描述：上面的SQL语句,使用的是OBJECTID字段的索引，通过查询该表的数据分析,发现该字段为有限的几类数据。 问题解决：通过对RECORDERNUM 字段创建索引，使SQL语句用到新的索引，提高了查询效率。,Page 10,主要有问题的SQL使用的索引选择性不好,SELECT count(*) FROM (SELECT 1 FROM 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(2
7、005-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 =
8、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,S
9、UBSTR(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
10、 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%忙，虽然用到了serialnum
11、ber字段的索引，但是由于查询条件是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,
12、 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.Se
13、rviceClassID = 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(
14、+) 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 wher
15、e 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 =(sele
16、ct 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)
