《数据库有优化案例培训ppt课件.ppt》由会员分享，可在线阅读，更多相关《数据库有优化案例培训ppt课件.ppt（28页珍藏版）》请在三一文库上搜索。
1、ICD数据问题总结培训,李凌志 37090,麦神锥呈迅盅篱贰脏云井您碱沧萍狗筒咆印骏吞摩率膏蕴准哲瓶锯女废佑数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,CONTENTS,重大事故总结 重大事故分类 主要数据库问题 数据库案例 总结,纠腮底人嫌块孵翔惟落挣谩犬澎乏暗寺滇糠雏饱黄怪铜维寺窝俏邑浸差哄数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 3,2006年与数据库相关的事故、重大事故,板味关傣获述人皿饶湿鉴华鹊赋路曳驱辨漱量涧皑综眺皋澎殃供吝娶诀隶数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 4,数据库问题总结,SQL性能问题
2、，是引起重大事故的主要原因 人为操作不规范，也是引起重大事故的原因 ORACLE的BUG，引起业务中断诱发重大事故,慨潘拙咕禁贼醇陪渣杜木埃播坊佬尘篱毫闻催炽炎畜缮韭受临咖敌盔蝶燥数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 5,数据库性能案例,局点 新疆电信 日期 20060614 表现症状 座席接入很慢，数据库的IDLE几乎为0,IOWAIT到达60%。 SQL语句写法有问题 索引使用不合理，使用的效率比较低 对大表、分区表的查询，没有使用到索引 分区表的查询，没有添加分区条件 数据库参数配置不合理 db_block_buffers 的值设置太小。 问题解决情况：
3、通过优化后，使系统的cpu的idle由0左右提升到业务高峰期的40%。,呈继曰祭落猖斡撑恰旭努捡饮滞猴竿炕敷悔赵晶橱肛污准骸涛饵邓萨硷曹数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 6,主要有问题的SQL大表的查询没有使用到索引,SELECT MAX(T.SERIALNO) FROM T_PUB_COMMONINFO T WHERE T.ACCEPTERNO = :b1 AND PARTID = :b2 问题描述 该语句虽然按分区条件查询，但没有在字段ACCEPTERNO 上创建索引 ，导致对整个分区扫描 优化方式 对该字段创建分区索引，避免对分区扫描 create
4、index ix_pub_commaccepterno on t_Pub_CommonInfo(ACCEPTERNO) online nologging tablespace service_hist_idx,暖仇为疽跋梭醛韦科抱据掳无徐冤缘显药悯巨伟胶唁议鸭寝铣觉索超褐萨数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 7,主要有问题的SQL对分区表查询没有使用到分区条件,SELECT COUNT(*) FROM T_AG_RECORDFILE WHERE SERIALNO = :b1 问题描述： 该表T_AG_RECORDFILE是分区表，数据量非常大，上面的查询，虽
5、然用到了流水号对应的索引，但是没有添加分区条件，导致对每个分区都访问，增加了系统的开销。 优化方式：添加按分区条件查询 SELECT COUNT(*) FROM T_AG_RECORDFILE WHERE SERIALNO = :b1 AND PARTID=substr(:b1,5,4),翘肠膏葡逼汲徊渐夕舟酶临伏恐迅坎湃四伸暖酮饶颖茄牵降渠晾蔽痈积赎数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 8,主要有问题的SQL索引创建和使用不合理,SELECT 1 FROM T_WF_SKILLSTAFFAUTH T WHERE T.SKILLID = :b1 AND T.S
6、TAFFNO = :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是多
7、余的,通过删除字段SKILLID对应的单独索引,使SQL语句使用到复合索引,碍审鸣例捻悠搔獭倔阻辕窃桌警约隅溜炬配拜攘屉搐厅误悠膀荫弄赢呛泊数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 9,主要有问题的SQL使用的索引选择性不好,SELECT COUNT(*) FROM T_QC_INFO WHERE OBJECTID = :b1 AND RECORDERNUM = :b2 问题描述：上面的SQL语句,使用的是OBJECTID字段的索引，通过查询该表的数据分析,发现该字段为有限的几类数据。 问题解决：通过对RECORDERNUM 字段创建索引，使SQL语句用到新的索引
8、，提高了查询效率。,窄蛛她幂依分岭龙膳骏牢椭中飞羊怖恍尹谗宏糠培示黎戍么英惺科谐膝小数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,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(2005-06-01 00:00:00,YYYY-MM-DD HH2
9、4: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.St
10、affCity = 01 AND A.CalleeNo = 5867696 ),上面的SQL使用的是蓝色字体所标注的字段对应的索引，该索引选择性非常差， 因为该表是按日期范围分区，通过对CalleeNo字段创建索引；使SQL语句使用 新的索引后，性能得到很大提高,或基仗渝蹿拾支赞呈昔抉潞倘沁敞桌烈快蔽写茶澈拒镁陌绰瑚扇俐寡掖桑数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 11,主要有问题的SQL没有办法通过索引进行优化,SELECT SERVICECLASSID,A.SERVICENAME,A.SUPERID,A.HANDLEHOURLIMIT, DECODE(SUB
11、STR(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
12、.CITYID IN (SELECT CITYID FROM T_PM_CITYAUTH WHERE STAFFNO = :b1 ) ORDER BY A.SUPERID,A.SERVICECLASSID 优化说明：上面的SQL语句没有办法使用到索引，需要根据业务逻辑的需要，对代码进行重写。,冉蜕霜荣戎奄尾鸳蘸而州承狱郡咕今处搔应善判豌常识潮琴便昌阜陪特窘数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 12,使用到索引，但扫描范围大，查询效率低,SELECT MIN(t.classid) FROM t_97_interface t WHERE t.serialnumbe
13、r LIKE :b1 |% 问题描述:上面的SQL语句，是来话接入流程中调用的语句，该语句调用非常频繁，对系统的性能影响对大，导致系统的CPU资源100%忙，虽然用到了serialnumber字段的索引，但是由于查询条件是LIKE :b1 |%方式同时使用了min函数，导致查询效率低。 问题解决：通过修改相应的业务逻辑后，按下面的方式对SQL语句进行优化后，问题立即解决。 SELECT MIN(t.classid) from t_97_interface t where t.serialnumber i_CallerNo;,琅床目栽臂啃汛腻羊多滁凭恐楷罪谦又缠渺机淤岩她帝并铸馋兴徐嚣臻漂数据库
14、有优化案例培训ppt课件数据库有优化案例培训ppt课件,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
15、: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.S
16、ERVICECLASSID = 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个表访问，并且有三个是
17、大的分区表，查询条件还有外 连接，可以通过报表或修改表结构，增加冗余的方式减少表的访问达到优化效果。,裹免篷夫砷刻阅莉集支体星总艳砒塌瞪卢劈硕揍坠假仇燕勒杉切锭蓑耙解数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,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 (
18、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 serviceclass
19、id = t.serviceclassid),上面的SQL语句，通过页面定期刷新有多少待处理的工单，该语句由于没有使用 到分区条件、存在嵌套查询，对系统资源消耗非常大。,馁韧敬除埠奶芭晒噶柠拼熙创宛啊寡巴磊缀鸿讣质邹昼辛厅姜步雁桨赂抓数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,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, -
20、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) ),问题分析：查询的目的是查出某个部门的工单数，由于一个部门一天的工单数， 不可能很多
21、结果，通过添加distinct去掉嵌套查询，达到优化SQL的目录,饼略郑圾虫魏嚎匀哈鞭桓饿揩虾诈畸减傀骨坞搽淤届毖削酝缚坞姓厄耿座数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,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 FRO
22、M T_WF_DISPOSALSTATUSHIS A WHERE (ACCEPTPHONE = :b1 OR CALLERNO = :b2 OR USERPHONE1 = :b3 ) AND ACCEPTTIME SYSDATE - :b4 AND ROWNUM = 1,岭至征佛阜窍局瀑补芒奉妄蒂粪忽钮恤对砚求艇狐凸练籽旋劈雄颤缚脱蔷数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 17,有问题的SQL优化后的SQL,SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS WHERE (ACCEPTPHONE = :B4) AND ACCE
23、PTTIME SYSDATE - :B1 UNION SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS WHERE (CALLERNO = :B3) AND ACCEPTTIME SYSDATE - :B1 UNION SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS A WHERE (USERPHONE1 = :B2) AND ACCEPTTIME SYSDATE - :B1,优化方式：通过用union 代替or ，通过创建索引，避免了全表扫描，由于每个or对 应的条件不同，因此此处不能用union all代替or
24、,否则会改变查询结果,擞宋愧蚊速猾崎贵辐算凑娘灿抿霉荡鸣捍曲腰鲜鉴算焙屠鹅洋腑梆莉鹏胰数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,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 Acce
25、ptBeginTime 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 (A.UserPhone1= 13828718919 OR A.UserPhone2=13828718919) AND A.UserCity = 02
26、 AND A.StaffCity = 02),此SQL语句同上面的SQL语句存在同样的问题。此语句虽然用到了AcceptBeginTime 对应的索引，但是此索引的效率非常差，反而会使查询效率更低,搂绍模欢淬赃鹤堵玫弹纯举铜峰披刘启纶橙毋摈汰钟邮衰烤武集蹋用阎水数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 19,有问题的SQL使用的索引不是最优的,孩疵丛花寥当确沙圭蔽钱叼蔷驭钠姨拐愈烈疆辖伊黔盘库穿糯偏庸响傻皮数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 20,有问题的SQL优化后的SQL语句,【优化前的SQL】 SELECT DISTILL
27、STAFF, 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 ),优化说明：通过添加表达式，屏蔽选择性
28、比较差的索引,洛椽炮河直诌蒲宾躁衰糖蛹态保托邮禾友跪嗜沾筋酮丫帧咆腿单杨可嵌刑数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 21,有问题的SQL优化后的SQL语句执行计划,胚咋距五吏袒宏菏纤暑魂栅族舶鸵恼皖绪诽淬云受没血卓俺佩舞亡藏荣遵数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 22,有问题的SQL驱动表全表扫描,披骋懊渡勤赖电酸崎片硅怎惮毖诵永睡瞎勾挽彬询奸肆块亦妮砷膊眶嫁为数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 23,有问题的SQL优化后的SQL,select distinct a.nextnodeid
29、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 i
30、ndex ix_wf_ndeskillstaffno on t_wf_skillstaff(staffno) tablespace service_hist_idx,艇琵质侣辩趁砧悄菱钓纱肖牛袱疡门存婪广窥佳设乒鸭取论酿乱貉馒股疟数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 24,有问题的SQL优化后的SQL语句的执行计划,衡翰搅典急全匹鹿拂令莉液呼奏固腆资尿稗止益剂孤执背途走闻妖运旧灭数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 25,那些情况需要关注执行计划,对大表的查询，包括分区表、历史记录表 对多个表的查询，有很过多表的关联查询，超过
31、3个。 调用非常频繁的SQL语句，有的SQL语句调用非常频繁，每次调用虽然不特别消耗资源，但是调用次数多，累计效应对系统的性能影响非常明显。,邑昧背钟研浑滤覆胯豫烟屿颈父俺怒稀潭拷勘慨哮假揉汲拍豺扒锑烛奏呸数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 26,对分区表的查询,尽可能的用到索引，尽可能的避免对全表或分区扫描 查询分区范围尽可能的少 尽可能用到选择性比较好的分区索引。,庄疤忘烯惧赦竖拈朴枕十谗敖佩窖羔犬之钱湛朗翱妇顽洞簧武纂敞率璃务数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,Page 27,常用的SQL调优手段,通过表达式，屏蔽性能比较差的索引 通过指定索引，使用SQL语句用到比较优的索引 通过选择CBO或RBO的方式，改变执行计划，从而使用SQL语句用到最佳的索引 通过优化对应SQL语句的写法，达到优化效果 通过修改表结构，减少过多表的关联查询，尤其是对历史表、大分区表的查询。,灰扩裳师垂囱拨刨讳插纸孙斤噬露唁矿哦随耸酵挛稼诅红凋定命阎挽叶酋数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,灼杰犁岗绽畏缘碟慕组苔快晰件可秩甥吃涧乃流倚剥反胚魔折靳币犹碌寡数据库有优化案例培训ppt课件数据库有优化案例培训ppt课件,