十年DBA老兵:重Java轻SQL乃性能大忌

运维 数据库运维
《SQL性能优化与批判》是黄浩老师的系列新作,他将从过往在项目技术支持中碰到的诸多案例入手,细化到每一条问题 SQL 的内在病因,反思每一个案例的背后深思,抽丝剥茧,层层深入。

 《SQL性能优化与批判》是黄浩老师的系列新作,他将从过往在项目技术支持中碰到的诸多案例入手,细化到每一条问题 SQL 的内在病因,反思每一个案例的背后深思,抽丝剥茧,层层深入。

[[203675]]

今天跟大家分享的是 WM_CONCAT 优化,这是一次凭借技术+经验+运气三重加成才得以解决的案例,are you ready?

案例

01.初来乍到,如临深渊

公元 2015 年 7 月 20 日,天气还是一如既往的炙热,徐徐海风也吹不散身上的热量。在经过近一个小时的班车加徒步,我正式开启了在 H 公司 I 项目技术支持的***天。

因为信息安全的缘故,***次进入项目现场的外协人员需要办理接待电子流。因为是非研发区域,倒也快捷,经过两重关卡后,顺利进入到项目现场。

妈呀,一个足球场般大小的办公场地,一排排的办公桌和电脑井然有序,但桌面上的办公用品却凌乱狼藉,而座位跟座位之间没有任何的遮挡。

当时已经九点多,基本上座无虚席,虽然开着空调,仍然能感觉到一股由电脑散发出来的掺杂着铁锈及灰尘味的热气,以及由此带来的压抑感。

在与现场同事简短的寒暄后,我便立马投入到工作——当然是交接工作。与同事的沟通中,我获取了如下信息:

  • 这位同事来这个项目不足两周。
  • 离职的原因是适应不了外包的工作方式。
  • 项目组性能优化工作开展很困难,项目组在这方面的投入不够,重视度也不够。

综合起来就是一个字:坑,而且是巨坑。原本担心我主观上的能力问题会影响到工作,没想到客观环境也是如此糟糕,我的心情跌倒了冰点。

明天是这位同事在项目组的 last day,所以交接工作必须在今天内完成。好在同事进项目不久,还没有接触到太多的工作内容,手头上就一个在优化的 SQL。

因为这个 SQL 的优化已经持续了几天时间,所以到目前显得有些紧迫:该 SQL 的优化被安排在周六上线,因此必须要在周三前给出优化方案。

离周三只有不到 2 天的时间了,而目前的优化进度还停留在问题定位阶段,还不确定问题处在哪里?换句话说,不是工作交接,而是从零开始。

我在同事的交接文档中找到了问题 SQL,代码如下:

02.战战兢兢,如履薄冰

没有任何的注释,代码中的表呀,字段呀什么的,我一个也不认识,唯一亲切的就是 select from where join group 这些被标绿的 SQL 关键字。

“这个 SQL 有什么性能症状?”

“跑起来很慢。”

“慢到什么程度?”

“大概需要半个多小时才能跑完。”

“数据量很大吗?”

“可能吧,我还没有执行过,只是听开发人员这么说的。”

看来我不能从这位同事这里得到更多有价值的信息了。

按下 F5 查看执行计划:

执行计划中,表访问方式基本上都是 index scan,而且也并无大成本的操作。奇怪了,问题处在哪里呢?我又回到 SQL 窗口,按下 F8,果然只见时间过,不见数据出来。

在长期与 SQL 相伴的日子里,我养成了一个习惯,喜欢在边看着 Oracle 执行,一边分析代码,大有“我忙着分析,你也别闲着偷懒”的“小人嘴脸”。

这个 SQL 有两个部分,***部分是用 with 封装了一个结果集,第二部分是对***部分的结果集进行 group by 处理。根据过往经验,我将 SQL 复制到了另一个 SQL 窗口,选中 with 子句单独执行,秒出呀。

排除了子查询的性能嫌疑,那么很显然问题是出在第二部分的 SQL。第二部分 SQL 包含了 group by,难道是 group by 产生了性能问题。要知道,group by 等聚合操作的性能对数据量是极其敏感的。难道是 with 子查询的数据量非常大?

我赶紧 count 了***部分 SQL 的结果集,显示不到 20 万数据。那就不应该呀,20 万数据做 group by 也不至于慢成“蜗牛”呀。

继续分析第二部分 SQL 代码,在 select 子句中,惊现 wm_concat 函数。此时,我还是有些小激动的,因为在之前也遇到过由于 wm_concat 引发的性能问题。为了验证判断,我将 wm_concat 注释掉,按F8 运行,果然飞快,不到 1s 就出结果。

至此,通过排除法,病因是找到了:由 wm_conca t引发了性能问题。

03.顺藤摸瓜,顺手牵羊

原因已经找到,那么对症又该如何下药呢?显然,从 SQL 功能上,wm_concat 是必须的,我也尝试过用 listagg 来替代 wm_concat,但是会因超过 4000 字符而报错。

其实 wm_concat 函数之所以慢,就是因为以 task_name 为维度需要拼凑的数据量太大导致的。难道就无解了吗?

我转念一想,为什么要用 wm_concat 函数?应用程序在拿到这个字段后做什么用呢?在前端页面显示吗?

这种显示是没有多大意义的,因为 wm_concat 的结果可能非常大,根本就显示不了。既然显示不完整,那么为什么又要从 DB 中获取完整的内容呢?

带着这些疑惑,我与 SQL 开发人员进行了沟通,原来,应用程序拿到这个 SQL 的数据后,并不是在前端页面展现,而是在应用程序中继续加工处理,在经过若干复杂的逻辑处理后,以另一种形式在页面展现。

此时,多年的从业经验告诉我:既然可以用 Java 来实现的业务逻辑,那么肯定也能在 DB 中通过 SQL 来实现,这样就可以避开 wm_concat 函数。

于是我决心深入了解业务功能,希望能从业务方案上有所突破。这样就形成了一个初步的工作计划:了解整体业务功能及逻辑-->了解应用程序处理逻辑-->改写 SQL 语句-->功能性测试-->性能轮回调整。

在大约两个小时的一对一讲解后,我基本上掌握了整体业务功能及逻辑、应用技术架构及处理逻辑。

这个其实是一个报表展现功能,是按区域、里程碑展现两个相邻里程碑之间的时间间隔,包括计划间隔时间与实际间隔天数(平均)。

报表格式大致如下:

在 DB 中,里程碑的计划与实际时间是存在二维表中,结构示意如下:

在这里,就存在一个行列转换的问题,即将 TASK_NAME 从以行存储转换成以列展现。

为了实现这种结构转换,当时的架构设计如下:

  • 通过 SQL 从 DB 获取每个里程碑、交付区域的 plan_start_time、plan_end_time、actural_start_time、actural_end_time 及 du 集合,即 SQL 中的 wm_concat 拼凑后的结果。
  • Java 应用程序拿到这个结果后,循环结果集,并依次分解由 wm_concat 拼凑的内容:计算每一个里程碑内 DU 的平均时间间隔;判断里程碑的前后置关系;计算前后置里程碑间的天数间隔;最终将计算结果展现在前端页面。

04.水到渠成,一战而定

从上述描述中,我们可以提炼出如下信息:

  • WM_CONCAT 拼凑的内容只是过渡的,在 Java 中还需要依次分解。
  • Java 处理的几个步骤完全可以由 SQL 来实现。

这样就可以省却以下几个“麻烦”:

  • 省却了大量数据从 DB 传输到 Java 服务器的成本开销。
  • 可以顺理成章的拔掉 wm_concat 这根刺。

那么,如果用 SQL 来实现上述逻辑功能,存在两个难点,其一是如何判断里程碑(task_name)前后置关系,其二是计算前后置里程碑的时间差。

进一步分析后发现,里程碑(task_name)前后置关系可以通过 SQL 来获取,而在时间间隔的计算上,可以通过 lead 窗口分析函数获取后置时间,然后相减即可。

改造后的 SQL 如下:

将 SQL 在 DB 中运行,不到 3 秒就执行完成。

心得

01.心有余悸,学无止境

值得一提的是,这个 SQL 并非一蹴而就的,从***次改写,到最终上线,经历了好几个版本,但整体结构并没有变动,只是对某些特殊场景做了调整。

我来项目的***个 SQL 优化就这样跌跌撞撞、歪打正着的完成了。由于时间紧迫,整个过程都是绷紧了神经。

现在回想起来,既是庆幸又是后怕,庆幸的是问题得到了及时解决;后怕的是,当时可谓是不知者无畏,完全是在不熟悉环境,不熟悉利害关系的情况下解决了问题。如果放在几个月后,我想一定没有当时的勇气和决心来完成这件事情。

回过头来看,这起由 wm_concat 引发的性能事件还是给了我们很多的启发:

SQL 优化不是孤立的存在

SQL 优化并不是孤立的,也就是说并不是所有的 SQL 本身都存在优化的空间。当 SQL 本身无法优化的时候,或者优化的空间不足以满足用户需求时,就需要从全局需求突破。

尝试着按另一种方式得到结果:殊途同归讲的不就是这个道理吗?正所谓山重水复疑无路,柳暗花明又一村,关键在于你是否愿意主动寻求和突破。

SQL 优化其实很朴素

SQL 优化并不需要多么高深的知识和高级的技术,SQL 优化也并不那么神秘,一点点技术,一点点经验,再加上一点点运气就足够了。

一点点技术

这里说的技术是 SQL 技术。SQL 语言我认为是除汇编外所有语言中最神奇、最简单、***艺术化的语言。

说简单,就 select 查询而言,就 select from where and or group order 等***的几个关键字,拿 SQL 而言也就 select、update、delete、insert 四种功能。而且通俗易懂。

说神奇,因为就这些关键字,无需排列组合,便可以千变万化。在当今的信息化大时代,无外乎就是增删改查;大千世界,芸芸众生,概莫能外。

就拿人类自身来说,其***哲学就是:生老病死,出生就是 insert,岁月催人老就是 update,众里寻他千百度就是 select,荣登极乐就是 delete。

说艺术化,简单而不简约,这就是艺术,能以数个关键字撑起世间万物的起起落落,这就是艺术。

这里说的掌握 SQL 技术,不仅仅是掌握这几个关键字,用这几个关键字变幻出种种结果,更是要掌握如何通过这几个关键字来实现这种艺术化的效果。

一点点经验

经验这东西是美妙的,一旦你拥有了某个知识点的经验,下次再遇到时,你会不费吹灰之力就能解决了。

比如这次的 wm_concat 函数,我相信,之前的同事没有定位出问题所在,就是他没有遇到过 wm_concat 这个函数。所以总结经验是绝对正确的,虽然经验并不一定有用得上的机会。

一点点运气

所学的一点点知识和积累的一点点经验恰好被用上了,这就是运气。因此运气也是辩证的,表面上是因为运气解决了这个问题,实则不然,如果没有那么一点点知识和经验,也不会这么顺利的解决。可见偶然中也有必然。

批判

7 月 25 日周末上线,周一一大早,开发兄弟像报喜一样告诉我,优化效果明显,用户非常满意。看着他稚嫩中略带青涩的笑脸,我也长舒一口气,毕竟这是我的***个优化案例。

“黄工,你是怎么知道可以这样处理的?”

面对他的这个问题,我一时哑口,该如何回答呢?

“那你当初为什么要将 SQL 返回中间结果集,然后又在 Java 中做逻辑处理呢?”

“一方面,我们的架构规范就是这样的,要求尽量在 Java 中完成逻辑处理,减少 DB 的负载;另一方面,我也写不出这么复杂的 SQL,说实话,你给我的 SQL,我到现在还没有看明白。”

原来如此,我就告诉他:

“在二维关系的系统里面,Java 能处理的二维数据,在 SQL 中都能实现”

“哦”

“对了,你是怎么选择 wm_concat 这个函数的?”我知道这个函数很少用,也是 Oracle 公司未公开的内部函数。

“我是在网上查到的资料,看到这个函数可以实现功能,就拿来用了,没想到会带来这么大的性能问题。”

看得出来,他仍然保持了学生意气,有些自责,他好像又想起了什么来,赶紧补充说“因为时间太紧迫了,现在是敏捷开发,每两周一个版本,如果时间充裕的话,我想我也能通过查资料把这个 SQL 写出来的。”

他说着有些激动,但事实上他是认真的,也真的做到了。在后来的开发过程中,他写出了连我都写不出来的复杂 SQL。

通过与他的对话,我大致可以勾画出这个项目的一些基本元素:敏捷开发,双周迭代,无开发型 DBA,重 Java 轻 SQL。

这些是国内大多数项目的通病,本来是见怪不怪,但是出现在世界 500 强,国内 IT 软件天堂的大公司,还是让我有些意外,更让人感到后脊凉凉的。

敏捷开发要求快速交付,功能优先性能,急功近利;偌大的一个企业级平台项目,居然没有匹配一个专职的开发 DBA,SQL 的质量令人担忧。

而重 Java 轻 SQL 在信息管理系统中是一个大忌,会暗藏很多性能风险,这些都是性能的催化剂。这意味着我接下来的道路势必坎坷曲折、荆棘丛生。

责任编辑:武晓燕 来源: DBAplus社群
相关推荐

2018-05-29 14:38:06

IT

2011-08-05 10:07:01

DBA职业之路

2022-03-28 11:41:21

物联网物联网市场智能电网

2020-12-10 07:24:25

DPDK微引擎代码

2022-11-22 16:39:21

2017-10-17 09:08:07

2019-12-13 16:08:57

戴尔

2017-07-10 10:36:54

CTO IT绩效

2017-06-02 10:17:57

腾讯运维

2013-06-18 09:34:39

软件开发

2013-01-14 10:04:16

2012-07-16 13:18:35

2022-03-18 13:46:20

物联网数据技术

2019-02-26 13:53:07

PythonJava编程语言

2012-10-17 14:24:07

2021-08-29 18:36:17

MySQL技术面试题

2019-10-09 13:17:49

智能手机旧手机系统

2011-05-11 11:08:57

2013-02-19 09:26:17

2020-11-05 22:59:15

技能工业革命技术
点赞
收藏

51CTO技术栈公众号