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

文末有赠书

作者介绍

黄浩：从业十年，始终专注于SQL。十年一剑，十年磨砺。3年通信行业，写就近3万条SQL；5年制造行业，遨游在ETL的浪潮；2年性能优化，厚积薄发自成一家。

编者按

《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………..”

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

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

不过瘾？没关系！

11月24日 Gdevops广州站现场

福富研究院副理事长梁敬彬将带来

《收获，不止SQL优化》的精彩演讲及深度课程

赠书来了

在本文微信订阅号（dbaplus）评论区留下足以引起共鸣的真知灼见，并在本文发布后的隔天中午12点成为点赞数最多的一名，可获得以下好书一本~

特别鸣谢博文视点为活动提供图书赞助。

近期热文：

新世界的大门由此打开