搜索
SQL优化实验报告专业班级：软件2班姓名：罗杨学号：130105021106指导老师：王志1.问题分析1.1现象描述某企业客户内部知识管理系统基于微软sharepoint服务器产品并进行了应用扩展开发，nlb负载均衡部署，后台数据库采用sqlserver2000企业版，双核4c8g内存两节点群集。在两三年的使用过程中，随着系统用户的增多，出现了数据库服务器cpu占用过高的情况，导致前端访问响应速度慢，经常超时等问题。1.2性能计数器分析用户连接经过对sqlserver关键性能指标的采集和分析，发现用户连接指标数值过大。用户连接的数据基本保持在700-1000之间，不仅是在忙时段(am:10)，且在闲时段(pm:6)也基本保持不变，基本可以确定是数据库连接池配置不当或有代码没有释放可用连接，需要通过应用代码进行问题排查。锁请求/秒经过向用户的了解，该系统为多数读取，少数写入的系统，但从性能计数器的观测值发现锁请求/秒的指标值平均约为158418.485，最高值可达到558870.266，锁操作总体过大，应该从应用层面进行分析优化。完全扫描/秒完全扫描/秒计数器指示有多少不使用索引而进行的全表扫描，测量过程中显示平均值达到100左右，最高值达到832.998，应分析sql查询语句和数据库索引的对应关系，追加必要的索引以减少全表扫描的次数。1.3sql工具分析通过使用sql事件探查器和查询分析器等工具对sqlserver内部语句执行的性能状况列出了明细，并可将其中的cpu占用较高的任务列出，如第一行显示的大量数据连接导致cpu占用较高、第二行复杂子查询join下存在部分索引未创建、wf_instance_track表有大量过期的历史数据时变慢等问题。1.4应用代码分析经过对系统源代码的粗略分析，发现以下一些问题：a.sqlhelper中的getconnection每次都是创建一个全新的数据库连接而返回给调用代码，导致连接无法被重用，每次全新创建也会增加服务器的负担；b.sqlhelper中的testconnection每次都是创建一个全新的数据库并且打开连接以测试连接的可用性，但是并不关闭就返回了。c.acceptupdate中的selectdb调用sqlhelper中的getconnection获得连接后进行数据库查询操作，但使用后并不关闭相应连接d.acceptupdate中的updateds调用sqlhelper中的getconnection获得连接后进行数据库更新操作，但使用后并不关闭相应连接e.colselect.aspx中的btn_ok_serverclick调用sqlhelper中的getconnection获得连接后进行数据库更新操作，但使用后并不关闭相应连接2.优化方案2.1代码优化a.由统一的代码管理数据库连接；b.使用数据库连接池技术管理连接；c.使用后必须关闭数据库连接；d.减少全新创建数据库连接的次数（如减少不必要的testconnection操作）e.优化sql语句，减少表锁；f.优化sql语句，使查询能尽量使用索引，减少全表扫描；g.适当使用临时表，以减少sql复杂度和子查询；h.其他与数据库性能有关的代码排查；2.2数据库优化a.创建经常被查询用到的索引；b.适当调整sql实例性能相关的参数，以使资源使用最大化（但要考虑为操作系统保留小部分资源）；c.备份和分离过期的历史数据(如2006年的状态跟踪数据)，并建立定期的数据库清理机制；d.定期观测和记录sql性能计数器，了解性能状况变化；e.升级到更高版本的sqlserver产品，使用分区表等新技术能够发挥更佳的服务器性能；2.3优化工作量估算代码优化和测试验证：约需10-15个工作日（依原有代码质量和数量决定）数据库优化和测试验证：约需5-7个工作日3.优化实施3.1代码优化对代码结构进行了性能分析，发现了一些代码质量问题。目录名文件名方法名app_code\siteacceptupdate.csselectdbapp_code\siteacceptupdate.csupdatedsframepagecolselect.aspx.csbtn_ok_serverclickapp_codesqlhelper.csgetconnection分析、修改、部署共计3人天3.2数据库优化对执行性能差但使用频率较高的部分数据表进行了索引创建。表名索引列索引名略分析、修改、部署和测试和报告共计5人天4.优化总结4.1性能对比性能参考对象优化前优化后（闲）优化后（忙）系统cpu利用率86.235%左右15.183%左右45.583%左右完全扫描/秒109.337左右23.175左右42.965左右锁请求/秒158418.48537101.09069444.232索引搜索/秒9847225374436534.2待决问题由于担心影响业务逻辑的正确性和测试的复杂性，没有对以下几个部分进行优化：1.数据库连接较多的问题，整体解决需要重新架构设计2.复杂度较高的sql语句以及视图的优化3.存储过程的优化，防止表锁4.工作流引擎内部机制不了解4.3系统建议1、数据库中表的数据量不是很大，单个简单的查询对整个系统的影响较小，较复杂的视图或存储过程优化有性能问题，随着数据量的增大影响而更明显，所以可定期清除不需要的历史数据。2、对条件字段的一些优化采用函数处理的字段不能利用索引，如：substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bhlike‘5400%’trunc(sk_rq)=trunc(sysdate)，优化处理：sk_rq=trunc(sysdate)andsk_rqtrunc(sysdate+1)进行了显式或隐式的运算的字段不能进行索引，如：ss_df+2050，优化处理：ss_df30‘X’||hbs_bh’X5400021452’，优化处理：hbs_bh’5400021542’sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5hbs_bh=5401002554，优化处理：hbs_bh=’5401002554’，注：此条件对hbs_bh进行隐式的to_number转换，因为hbs_bh字段是字符型。条件内包括了多个本表的字段运算时不能进行索引，如：ys_dfcx_df，无法进行优化qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’andkh_bh=’250000’4.4总结通过增加对数据量较大的表以及查询较频繁的表增加索引，能够减轻数据库完全扫描的压力，使cpu利用率下降。以上对比显示，优化效果较明显。