SQL从技巧、案例和工具入手，详解性能优化怎么做
|
《SQL从技巧、案例和工具入手，详解性能优化怎么做》要点： 作者介绍颜圣杰,.NET平台软件工程师,对DDD领域驱动设计感兴趣,目前在研究ABP框架,热爱写作与分享. 最近一段时间系统新版本要发布,在beta客户测试期间,暴露了很多问题,除了一些业务和异常问题外,其它都集中在性能上.有幸接触到这些性能调优的机会,这里跟大家归纳交流一下. 性能优化是一个老生常谈的问题了,典型的性能问题如页面响应慢、接口超时,服务器负载高、并发数低,数据库频繁死锁等.而造成性能问题又有很多种,比如磁盘I/O、内存、网络、算法、大数据量等.我们可以大致把性能问题分为四个层次：代码层次、数据库层次、算法层次、架构层次. 下面我会结合实际性能优化案例,和大家分享下性能调优的工具、方法和技巧. 先说心态说到性能问题,你可能首先就想到的是麻烦或者头大,因为一般性能问题都比较紧急,轻则影响客户体验,重则宕机导致财务损失,而且性能问题比较隐蔽,不易发现.因此一时间无从下手,而这时我们就很容易从心底开始去排斥它,不愿接这烫手的山芋. 恰巧,性能调优也是体现程序员水平的一个重要指标. 因为处理BUG、崩溃、调优、入侵等突发事件比编程本身更能体现平庸程序员与理想程序员的差距.当面对一个未知的问题时,如何定位复杂条件下的核心问题、如何抽丝剥茧地分析问题的潜在原因、如何排除干扰还原一个最小的可验证场景、如何抓住关键数据验证自己的猜测与实验,都是体现程序员思考力的最好场景.是的,在衡量理想程序员的标准上,思考力比经验更加重要. 所以,若你不甘平庸,请拥抱性能调优的每一个机会.当你拥有一个正确的心态,你所面对的性能问题就已经解决了一半. 再说技巧拿到一个性能问题,不要忙着先上工具,先了解问题出现的背景,问题的严重程度.然后大致根据自己的经验积累作出预估.比如客户来了个性能问题说系统宕机了,已经造成资金损失了.这种涉及到钱的问题,大家都比较敏感,根据自己的Level,决定是否要接这个锅.这不是逃避,而是自知之明. 了解问题背景后,下一步就来尝试问题重现.如果在测试环境能够重现,那这种问题会很好跟踪分析.如果问题不能稳定重现或仅能在生产环境重现,那就相对比较棘手,这时要立刻收集现场证据,包括但不限于抓dump、收集应用程序以及系统日志、关注CPU内存情况、数据库备份等,之后不妨再尝试重现,比如恢复客户数据库到测试环境重现. 不管问题能否重现,再下一步,我们要大致对问题进行分类,是代码层次的业务逻辑问题还是数据库层次的操作耗时问题,又或是系统架构的吞吐量问题.那如何确定呢?而我倾向于先从数据库动手.我的习惯做法是,使用数据库监控工具,先跟踪下SQL耗时情况.如果监控到耗时较长的SQL语句,那基本上就是数据库层次的问题,否则就是代码层次.若为代码层次,再研究完代码后,再细化为算法或架构层次问题. 确定问题种类后,是时候上工具来精准定位问题点了：
精准定位问题点后,就是着手优化了.相信到这一步,就是优化策略的选择了,这里就不展开了. 优化后,最后当然要进行测试了,毕竟优化了多少,我们也要做到心里有谱才行. 啰啰嗦嗦有点多,下面直接上案例. 案例分享这里分享下我针对代码层面、数据库层面和算法层面的优化案例. 1. SQL优化案例 案例1：客户反馈某结算报表统计十天内的数据耗时10mins左右. 由于前几天刚学会用RedGate的分析工具,拿到这个问题,本地尝试重现后,就直接想使用工具分析.然而,这工具在使用webdev模式起站点时,总是报错,而当时时一根筋,老是想解决这个工具的报错问题.结果,白白搞了半天也没搞定.最后不得已放弃工具,转而选择使用SQL Server Profiler去监控SQL语句耗时.一跟踪不要紧,问题就直接暴露了,整个全屏的重复SQL语句,如下图：
SQL Profiler监控结果 这下问题就很明显了,八成是代码在循环拼接SQL执行语句.根据抓取到SQL关键字往代码中去搜索,果然如此.
看到这段代码,咱先不评判这段代码的优劣,因为毕竟代码注释清晰,省了我们理清业务的功夫.这段SQL主要是想做去重处理,很显然选用了错误的方案.改后代码如下：
改后测试相同数据量,耗时由10mins降到10s左右. 2.代码优化案例 案例2：客户反馈销售订单100条分录行,保存进行可发量校验时,耗时7mins左右. 拿到这个问题后,本地重现后,监控SQL耗时没有异常,那就着重分析代码了.因为可发量校验的业务逻辑极其复杂,加上又直接再一个类文件实现该功能,3500+行的代码,加上零星注释,真是让人避之不及.逃避不是办法,还是上工具分析一把. 这次我选用的时VS自带的Performance Profiler,开发环境下极其强大的性能调优工具.针对我们当前案例,我们仅需要跟踪指定服务对应的DLL即可,使用步骤如下：
图示步骤 跟踪结束后本案例跟踪到的采样结果如下图：
VS Performance Profiler分析报告 同时Performance Profiler也给出了问题的建议,如下图：
VS Performance Profiler分析提示 其中第1、4条大致说明程序I/O消耗大,第一代的GC上存在未及时释放的垃圾占比过高.而根据上图的采样结果,我们可以直接看出是由于再代码中频繁操作DataTable引起的性能瓶颈.走读代码发现的确如此,所有的数量统计都是在代码中循环遍历DataTable进行处理的.而最终的优化策略,就相当于一次大的重构,将所有代码中通过遍历DataTable的计算逻辑全部挪到SQL中去做.由于代码过多,就不再放出.
同样,我们还是先尝试本地重现.经测试批量引入101张单据,就耗时5mins左右.下一步打开SQL监控工具也未发现耗时语句.但考虑到是批量导入操作,虽然单个耗时不多,但乘以100这个基数,就明显了.下面我们就使用RedGate的Ants Performance Profiler跟踪一下. 该工具比较直观,可以同时监控代码和SQL执行情况.第一步,New Profiler Session,第二步进行设置,如下图.根据自己的应用程序类别,选择相应的跟踪方式.
跟踪设置 针对这个问题,我们跟踪到的调用堆栈和SQL耗时结果如下图：
调用堆栈监控结果
SQL监控结果 首先从调用堆栈中的Hit Count,我们可以首先看出它是一个批量过程,因为入口函数仅调用一次；第二个我们可以代码中是循环处理每一个单据,因为Hit Count与我们批量引入的单据数量相符；第三个,突然来了个10201,如果有一定的数字敏感性的话,这次性能问题的原因就被你找到了.这里就不卖关子了,101 x 101 = 10201. 是不是明白了什么,存在循环嵌套循环的情况.我们走读代码确定一下：
好吧,外层套了一个空循环却什么也没做.修改就很简单了,删除无效外层循环即可. 3.算法优化案例
这个报表是用来跟踪所有单据从下单到出库的业务流程数据流转情况.而所有的流程数据都是按照树形结果存储在数据库表中的,类似这样：
图中的流程为：销售合同-->销售订单-->发货通知单-->销售出库单 为了构造流程图,之前的处理方法是把流程数据取回来,通过代码构造流程图.这也就是性能差的原因. 而针对这种情况,就是考验我们平时经验积累了.对于树形结构的表,我们也是可以通过SQL来进行直接查询的,这就要用到了SQL Server的CTE语法来进行递归查询. 仔细观察上面的表结构,会发现其树形结构的特点：
首先想到的办法就是把流程数据取回来,然后代码构造流程图. 第一个思路：根据根节点循环往下找,吭呲半天,发现没那么简单.因为任何一个源头单据都可以多次下推目标单据. 第二个思路：先找到终极节点,在从终极节点往上找只至根节点为0. 这个思路实现起来也没有那么复杂,逻辑理清,循环遍历,最终也能实现结果.(但在大数据量情况下,易导致性能瓶颈.) 这一次我们换一个思路,让SQL来替我们做这一复杂的递归查询. 1SQL Server 递归查询 基本概念 公用表表达式(CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或CREATE VIEW 语句的执行范围内定义的临时结果集.公用表表达式可以包括对自身的引用,这种表达式称为递归公用表表达式.
MSDN上对CTE的介绍
T-SQL查询进阶--详解公用表表达式(CTE)
CTE 的基本语法结构如下：
即三个部分：
动手实践 根据官网示例我们很简单就可以写出CTE语句应用于我们的应用场景：
在查询中我们指定条件参数WHERETBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625,即可查询到指定节点的完整流程数据. 其中在与公用表TEST_CTE进行关联时,我指定了两个条件CTBIE.FSID=CTE.FTIDAND CTBIE.FSTABLENAME = CTE.FTTABLENAME,因为不同类型的单据各有一套自增的ID,直接用ID进行关联迭代不可行.
需要注意的是OPTION(MAXRECURSION10)是用来限制递归次数,以避免无限递归导致数据库性能消耗严重. 扩展：构造递归路径
基于上一个查询,增加一列手动拼接递归路径.注意SQL中将PATH设置的类型为navarchar(4000),在union中,两边的表结构类型必须保持一致,否则会报错定位点类型和递归部分的类型不匹配.可参考此篇博文 《解决CTE定位点类型和递归部分的类型不匹配》.(http://www.cnblogs.com/ccding13/p/3515393.html)
递归路径查询结果 2Oracle 递归查询基本概念 Oracle中的递归查询语句为start with…connect by prior,为中序遍历算法. 可参考《Oracle 树操作、递归查询(select…start with…connect by…prior)》了解更多.(链接http://www.cnblogs.com/yingsong/p/5035907.html)
其基本语法是： selectcolname from tablename start with条件1 connect by条件2 where 条件3
PRIOR关键字 运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序.
PS：当CONNECT BY后指定多个连接条件时,每个条件都应指定PRIOR关键字. 动手实践 理清了用法,我们用Oracle来对查询一下业务流程.
查询结果 该流程为：销售订单-->发货通知单-->销售出库单-->退货通知单-->销售退货单 其中在指定连接条件时,我指定了两个条件FSID= PRIOR FTID AND FSTABLENAME=PRIOR FTTABLENAME,直接用ID进行关联迭代不可行. 扩展：构造递归路径 Oracle中提供了SYS_CONNECT_BY_PATH函数用来进行连接路径.
基于上个查询,增加了一列SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH用来拼接递归路径.
递归路径查询结果 显示当前节点的根节点 这个时候我们要用到connect_by_root函数,用来记录当前节点的根节点信息.
当前节点的根节点的查询结果 Oracle中的with...as语句 Oracle也有with..as 查询语法,一般用来进行子查询,提高查询效率. 语法： with tempTableName as ( select * from table1 ) select *from tempTableName 拿我们的案例举例就是：
为啥要讲这个呢,我们可以在Oracle递归查询后进行筛选啊. 总结性能调优是一个循序渐进的过程,不可能一蹴而就,重在平时的点滴积累.关于工具的选择和使用,本文并未展开,也希望读者也不要纠结与此.当你真正想解决一个问题的时候,相信工具的使用是难不住你的. 最后就大致总结下我的调优思路：
End. 来源：公众号“DBAplus社群” 运行人员：中国统计网小编(微信号：itongjilove) 微博ID：中国统计网 中国统计网,是国内最早的大数据学习网站,公众号：中国统计网 http://www.itongji.cn 《SQL从技巧、案例和工具入手，详解性能优化怎么做》是否对您有启发，欢迎查看更多与《SQL从技巧、案例和工具入手，详解性能优化怎么做》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
