中国领先的IT技术网站
|
|

限定两小时!一次由权限类型归集引发的紧急SQL优化案例

《SQL性能优化与批判》是黄浩老师的系列新作,他将从过往在项目技术支持中碰到的诸多案例入手,细化到每一条问题SQL的内在病因,反思每一个案例的背后深思。今天跟大家分享的是第四个案例:获取责任人。

作者:黄浩来源:DBAplus社群|2017-11-01 16:15

Tech Neo技术沙龙 | 11月25号,九州云/ZStack与您一起探讨云时代网络边界管理实践


限定两小时!一次由权限类型归集引发的紧急SQL优化

编者按

《SQL性能优化与批判》是黄浩老师的系列新作,他将从过往在项目技术支持中碰到的诸多案例入手,细化到每一条问题SQL的内在病因,反思每一个案例的背后深思。今天跟大家分享的是第四个案例:获取责任人,需要回顾前情的同学请戳这里:案例一案例二案例三

一、案例

这一天,I项目组的一个迭代版本需要上线,这是一个大版本,需要全员现场支撑,并要求上线后三天待命。

1、不速之客,来者不善

而就在上线前两天,即9月24日下午4点钟,一直以来波澜不惊有惊无险的性能优化,突然被放了一个大招,某个页面被测出了严重的性能问题,大致情况如下:

测试人员在性能环境做了一轮压力测试,数据增加了5倍,其它功能点基本上达到了性能指标,而该功能则需要6s,整整超出了3s。

瞬间,大家都紧张起来了。

由于0926版本是公司级的大版本,不光是I项目组发布版本,H公司的其它系统也会同时发布版本。为了控制风险,会提前两天冻结代码。按照“不带BUG上生产”的原则,我们必须要在版本冻结截至时(9月24日18点准)“毙”掉这个性能BUG单。而距离18点还不到2个小时。

PM在得知这一消息后也高度关注,责令优化小组全力攻关,要人给人。这样,组长、模块SE及我就组成了临时应急小组。大家全力以赴,很快就把问题梳理出来了,大致如下:

该页面加载共需要执行8条SQL,单条SQL的执行都不长,都在性能指标范围内,但是加起来超过了5s;

剩下的2s耗在页面的逻辑处理。

当时,组长当机立断,一方面要求对这些SQL进行优化,优化到2s左右;另一方面将页面的处理耗时降到1s内,这样就能确保3s的性能要求。

SQL优化任务自然落在我的头上,8个SQL的代码如下:

2、兵分两路,把鸡蛋放在两个篮子里

看着这8个不长不短整整齐齐的SQL,我的第一反应是:一个页面加载怎么会存在8个SQL语句?这8个SQL之间又有着什么样的关联关系?是否还可以合并成一个?

如果做SQL合并的话,就意味着我需要详读这8个SQL,但时间的指针已经指在了17:00,离18:00下班不足一个小时。用中国足球赛事评论员的话说就是“现在留给中国对的时间已经不多了”,已经没有时间让我解读这8个SQL;况且,即便能快速解读,也未必能合并。

那么就要像组长提议的:寻求单个SQL的优化突破。而8个SQL优化到2秒,也就是说单个SQL平均耗时在0.25秒,这个压力也是非常大的。

我在与组长简短商议后,为了降低风险,不至于孤注一掷,做出了如下决定:兵分两路,由我执行合并方案,优化小组的DBA负责单个SQL进行优化。

3、原来如此,不过如此

按照以往的习惯,我肯定会先自己解读这8个SQL,因为我相信别人的时间也是时间,能自己解决的尽量不要占用别人的时间。但这次不行了,因为时间不允许了,我必须要快速了解8个SQL的业务功能。

于是我跟SE表达了我诉求,SE立即安排了开发责任人跟我对接。在与开发人员长达20分钟的沟通后,终于理清了这个8个SQL的逻辑与关系,如下:

查询任务列表,共3个SQL,共耗时1s,主SQL,包括了count和详情

查询责任人:4个SQL,共耗时3s,但是页面自上而下共耗时5s

查询网络节点:1个SQL共耗时0.5s

这是个重大发现:6s多的时间中,查询责任人花费了5s,这是要重点照顾的对象。我继续向开发责任人了解更多的信息:

“查询责任人SQL,SQL单独运行是3s,为何页面却花费了5s?”

“因为页面需要对SQL返回的数据集进行判断。”

“都做了哪些逻辑处理?”

“这四个SQL分别对应四类权限,权限的最小单元是实体DU,在任务列表中获取的DU,先用第一个SQL判断哪些DU具有第一类权限,比如有100个,那么传入第二个SQL的DU就是90个DU,由此类推,知道完成了4类权限的判。”

听完后,我豁然开朗,逻辑流程图如下:

4、对症下药,一蹴而就

至此,我已成竹在胸。

四个SQL对应四种权限,如果我们把TASK_ID比作学生,把USER_ID比作班级,而将权限比作是学生选修的四门学科。那么“权限责任人查询”就转变成查询当前班级每个学生最高分的科目。

这是典型的按优先级排序后取最大值的需求。当前的方案是:

  • 依次从DB中获取四种权限对应的DU_ID;
  • 在JAVA中根据DB返回的权限判断权限类型。

该方案存在两个性能瓶颈:

  • 将权限数据从DB传输到JAVA服务器是要一定的成本开销的;
  • 当JAVA拿到权限数据数据时,需要循环逐一归集权限类型,这个过程也会带来一定的性能问题。

如果我们能将权限类别归集放在DB中完成,即DB只需要返回当前用户的DUID所属权限类别即可,那么至少省却了4次数据传输的时耗。当然,权限类型归集无论是放在DB还是JAVA,都是需要成本开销,就看谁的算法更具优势。事实上Oracle则提供了完整的解决方案,即用rank over来实现优先级排序。

此时时间已经到了17:20,我来不及多想,立马对查询责任人的4个SQL进行合并改写,合并后的SQL如下:

改写后,放在DB中执行,耗时0.98秒。这意味着,责任人查询从5s成功降到了1s内,足足下降了4s;这样,整体上也完全满足性能要求。

我在17:25将SQL移交给了开发,留给开发人员35分钟时间去开发验证。

结果自然是皆大欢喜,项目顺利上线。

二、心得

1、学无止境的态度

当SE拿到我合并的SQL后,满脸的疑惑:

“这个SQL会不会有问题?”

“我是按照业务需求改写的,如果我没有错误理解需求的话,SQL就是正确的。”

“也是,我测试了好几种场景,结果看起来都是正确的。”

接着我又详细讲解了Rank的功用和用法。SE长吁一声说道:“早知道Oracle有如此“神器”,当初就也不用费老大劲在Java中做权限类型归集了,还弄出了性能问题。看来真的是学无止境呀。”

在此,我无意于苛求SE“早知如此,何必当初”,毕竟术业有专攻。唯一不解的是,偌大的一个项目组(近200人),居然没有配置一名DB开发工程师。建表,写SQL这些活都是由Java开发人员包办。而在与Java开发工程师沟通中了解到,部分人员根本没有SQL基础,更不用说是开发经验。而他们写SQL的方式即简单又粗暴,是从同事那里拿一个功能类似的SQL,直接在此基础上修改,也不知道该SQL的具体含义。

这种现学现卖的方式也直接导致了很大的性能问题。正是因为确实了DB开发工程师的岗位配置,大大弱化了SQL功能,使得DB退化成为仅仅是数据存储功能,失去了真正的核心:组织和管理功能。

作为不仅仅是世界500强的企业,作为国内代表顶尖开发水准的企业,在企业管理系统的开发项目中,尚且不配置专职DB开发工程师,而其它企业的开发团队的人员配置就更可想而知了。

2、点到为止的哲学

在组长的运筹帷幄下,性能优化小组在紧张备战1017版本性能攻关的同时,很好地保障了926版本的性能需求,使得926版本顺利上线,I项目PM也扬眉吐气了一把:在性能红线上,终于没有求爷爷告奶奶放一马了。在926版本上线后,一方面为表谢意,另一方面也为1017版本打气,PM宴请了小组成员,席间问起:

“黄工,就你来看,项目在SQL这方面还有多大的优化空间?”

“这要看领到对性能的要求和优化的决心了。”

“怎么说?”

“真正的优化,最大的空间还是在于从底层的模型设计,以及写出规范和优秀的SQL,因此应该在项目上配置专职的DBA………..”

“呃,黄工,这样可不行,如果真的是这样了,那你们干嘛呢?”

领导就是领导,不正面冲突,在轻描淡写中已经说明了一切,而后来我在内部资料中看到“现固化,再僵化,后优化”的流程策略时,就更明白了。

【编辑推荐】

  1. 关于MySQL优化原理,你不得不知道的那些事儿
  2. SQL优化器原理 - Auto Hash Join
  3. MySQL数据库的“十宗罪”(附10大经典错误案例)
  4. 关于MySQL的SQL优化之覆盖索引
  5. 干货 | 一个MySQL 5.7分区表性能下降的案例分析
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

Java for Flash动态网站开发手札

本书深入浅出地说明了如何利用Java、Flash及XML进行Flash富媒体应用程序的开发。 本书知识丰富,内容结构合理,包括:Flash影片应用程序与...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊
× Phthon,最神奇好玩的编程语言