扶贫办门户网站建设管理办法,如何做游戏推广,灯箱广告制作公司,网站创建网站上周给南京某客户一个重要业务系统的数据库做优化#xff0c;能实施的马上做了实施#xff0c;优化前后性能对比非常明显#xff0c;系统最为严重的IO负载过重问题基本得到解决#xff1a;优化前一天的物理读是48亿次#xff0c;优化后是15亿次#xff0c;效果那是刚刚的… 上周给南京某客户一个重要业务系统的数据库做优化能实施的马上做了实施优化前后性能对比非常明显系统最为严重的IO负载过重问题基本得到解决优化前一天的物理读是48亿次优化后是15亿次效果那是刚刚的业务处理效率明显改善磁盘的寿命也会有大幅提升。 这些还只是优化建议的一部分。 如果开发人员能把改SQL和业务逻辑的优化建议都完成一天物理读少于5亿次应该也是完全没问题的。如果不知道优化能够带来如此巨大的提升可能业务部门会考虑花个上百万换个闪存存储都有可能实际上当前的普通存储对业务要求来说已经绰绰有余。 当前的性能提升主要是通过调整索引完成的创建了11个索引。同时还做了调整数据库参数把optimizer_index_cost_adj参数由20改回默认的100再有通过hintsql profile让sql选择高效执行计划。这些事情DBA可以搞定。但是如果SQL写的不好就只能改代码这个周期可能就比较长了。下面就列举其中的几种情况1、部分sql没有使用绑定变量每秒硬解析次数将近200。这是一个超标很多的数值(硬解析小于10次/秒的系统基本还算OK)。合理使用绑定变量是OLTP系统的一个最最基本的要求但是目前还是有很多系统没有使用绑定变量只能说目前我们国内的很多开发团队的数据库开发水平还很初级。有些公司会要求DBA要有OCP、OCM等资质才能上岗但是对开发人员却非常宽容只要功能实现了就算万事大吉如果经过一些简单的培训这种不使用绑定变量的低级问题应该不会出现。 这个客户的另一套数据库在某个时段的硬解析接近1000次/秒CPU使用率接近100%当DBA告知开发人员这是因为没有使用绑定变量的原因开发人员居然振振有词的说以前没有出现类似问题这就是典型的无知者无畏。 不使用绑定变量的一个无奈的解决办法是改数据库参数将cursor_sharing有默认的exact改成force。这个改动非常不建议很有可能引起其他性能问题有很多bug与之相关。2、这是一个非常低级而且普遍存在的写法问题对一个日期字段做to_charwhere to_char(starttime,yyyy-mm-dd)20200110。这个starttime字段上已经存在一个单字段索引还有一个to_char(starttime,yyyymmdd)函数索引。但是上面的sql写法如果不改写就只能再创建一个to_char(starttime,yyyy-mm-dd)的函数索引。如果开发人员又写出 to_char(starttime,yyyy/mm/dd)的sql呢是不是还得再创建一个函数索引这种sql的存在说明开发团队还不知道什么是开发规范和SQL审核。如果你是开发人员上面两个写法都命中了你(可能有的开发人员仍不知绑定变量写法是个什么概念建议马上百度一下)那么只能说你的数据库开发水平还有非常巨大的进步空间。3、select xxseq.nextval from (select 1 from all_objects where rownum10) ; 这个sql是借助all_objects视图生成10个sequence序列值。 这个写法在功能是没问题但是sql频繁的执行大量all_objects视图的调用把这些本来对资源消耗可以忽略不计的sql变成了top cpu SQL。 建议改成select xxseq.nextval from dual connect by level10;4、select ...... from xxtab where name like %这是一个字段内容的完整值%; SQL(简化版)涉及的表比较大消耗了大量的IO资源。 随便选择几个两个百分号中间的内容到表里按等值查询都能查得到说明两个百分号是可以去掉的同时like 可以改成。如果业务上线初期表只有几千或是几万条记录这个sql消耗的资源也很少但是随着时间的增长表的数据量达到千万或上亿你这样一个SQL就能把大部分IO资源都消耗掉。 凡是使用两个百分号模糊查询为主要过滤条件的多从业务角度多考虑一下尽量避免在大表上使用。5、不明所以照猫画出狗(不是虎)的分页写法(xxtab这个表很大sql全表扫描消耗了大量IO资源)SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM xxtab) A) WHERE RN 1 and rownum 3000 and ((type typename) and (state 0));这个分页写法不知道是从哪里学来的错的离谱。type和state两个字段上的索引根本用不上只能使用全表扫描的执行计划。正常的写法应该是SELECT A.*, ROWNUM RNFROM (SELECT * FROM xxtab where rownum 3000 and ((type typename) and (state 0))) A; 这种写法大表就能用上索引了IO消耗也会大幅减少。6、select ... from xxtab where (:b1 is null or col1:b1) and (:b2 is null or col2:b2) ; 这种写法可能来源于某些搜索需求两个输入框不管有没有输入查询条件都可以使用上面的sql。写法简单了效率却差了很多不管b1、b2是否有输入col1和col2字段上即使有索引也都只能选择全表扫描。 这个在业务上可以要求必须有一个输入条件那么sql就可以根据不同的输入生成不同的内容比如b1有输入b2无输入select .... from xxtab where col1b1; 两个都有输入对应sql是select ... from xxtab where col1:b1 and col2:b2; 这样才能高效的使用索引。7、用rownum分页写法取数SELECT *FROM ( SELECT t.*, ROWNUM RN FROM ( select t1.*,ROWNUM from ( select * from p left join c on c.objectid p.objectid left join o on o.objectid p.objectid left join e on e.objectid o.objectid ) t1 ) t WHERE ROWNUM 1500000 ) WHERE RN 1200000; 这个分页sql的框架完全正确满分。但是使用场景却是严重不合适的p表7700万记录(o表也很大)4表left join没有谓词条件主查询结果集应该也至少是7700万每次取30万(1500000-1200000)条要执行250多次执行计划都是全表扫描需要两个大表做250多次全表扫描。如果这些sql是串行执行那么还有一个逻辑问题就是这些表的数据都是动态变化的用rownum分页这种方法一定会造成漏取或重复取数。其实这个sql的最佳做法就是不加分页一次完成。表扫描只需要一次而且也不会出现漏取或重复取数的问题。总结 SQL优化对大部分系统来说都能带来非常大的性能提升。很多问题靠更换高级硬件是无法解决的比如一个大表全表扫描现有硬件条件执行时间10分钟更换硬件后执行时间1分钟。而通过优化SQL建个合适的索引在不更换硬件的情况下可能用不到10毫秒就能出结果。 类似的sql写法问题可能还有很多这次只是挑选了其中一部分top sql做分享。SQL写法灵活多变性能好的SQL都是符合规范的性能差的SQL会暴露你的开发水平。
