十年DBA老手：重Java轻SQL是性能大忌，反思每一个案例背后的深层思考，抽丝剥茧，层层深入。今天给大家分享一下WM_CONCAT的优化。这是一个只有技术+经验+运气三重加成才能解决的案子。你准备好了吗？案例01.新人，如临深渊。2015年7月20日，天气还是一如既往的炎热，即使是轻柔的海风也无法驱散身上的热气。经过近一个小时的摆渡车和步行，正式开始了H公司I项目技术支持的第一天。出于信息安全考虑，外包人员首次进入项目现场需办理接待电子流程。因为是非研发领域，所以也快。经过两个检查站后，顺利进入项目现场。天啊，足球场那么大的办公空间里，一排排的办公桌和电脑井井有条，唯独桌面上的办公用品杂乱无章，座椅之间没有任何遮挡物。当时已经九点多了，基本座无虚席。虽然开着空调，但还是能感觉到电脑里夹杂着铁锈和灰尘的热气，以及随之而来的压抑感。与现场同事简单打招呼后，我立即投入工作——当然是交接。在和同事的交流中，我得到了如下信息：这位同事做这个项目还不到两周。离职原因是适应不了外包的工作方式。项目组开展性能优化工作难度很大，项目组在这方面投入不够，重视不够。一个字可以概括：坑，而且是个大坑。本来担心自己的主观能力会影响工作，没想到客观环境这么差，心情一下子降到了冰点。明天是项目组这位同事的最后一天，所以交接工作必须在今天之内完成。好在同事入项目不久，接触的工作内容也不是太多，所以手头有一个优化过的SQL。因为这条SQL的优化已经进行了好几天了，目前看来还有些急迫：这条SQL的优化定于周六上线，所以必须在周三之前给出优化方案。距离周三还有不到2天的时间，目前优化进度还处于问题定位阶段，不知道问题出在哪里？也就是说，这不是工作交接，而是从零开始。在同事的交接文档中发现了问题SQL。代码如下：02.本人谨小慎微，如履薄冰，不做任何评论。我不知道代码中的任何表和字段。唯一厚道的是selectfromwherejoingroup这些都是绿色标记的SQL关键字。“这条SQL的性能症状是什么？”“它跑得很慢。”“有多慢？”“跑完大概需要半个小时。”“数据量大吗？”“也许吧，我还没有执行，只是听开发商说的。”看来从这位同事那里获取不到更多有价值的信息了。按F5查看执行计划：在执行计划中，表的访问方式基本都是索引扫描，没有任何代价高的操作。奇怪，问题出在哪里？回到SQL窗口按F8，果然，只有时间过去了，没有数据出来。在长期接触SQL的日子里，养成了一个习惯。我喜欢边看Oracle执行边分析代码。这个SQL有两部分，第一部分是用with封装一个结果集，第二部分是对第一部分的结果集进行groupby处理。按照以往的经验，我把SQL复制到另一个SQL窗口，选择with子句单独执行，秒出来了。如果排除了子查询的性能嫌疑，很明显问题出在SQL的第二部分。SQL的第二部分包括分组依据。会不会是那个groupby导致的性能问题。请注意，groupby等聚合操作的性能对数据量极为敏感。会不会是with子查询的数据量很大？我快速统计了一下SQL第一部分的结果集，结果显示不到20万条数据。那就不应该了，20万数据做groupby不会慢下来变成“蜗牛”。继续分析SQL代码的第二部分。在select子句中，出现了wm_concat函数。说到这里，我还是有些激动的，因为我之前遇到过wm_concat导致的性能问题。为了验证判断，我把wm_concat注释掉，按F8运行。真的很快，不到1秒就出结果了。至此，通过排除的方法，找到了原因：性能问题是wm_concat引起的。03.顺藤摸瓜，找到原因了，如何对症下药？很明显，从SQL函数来看，wm_concat是必须的。我也试过用listagg代替wm_concat，但是会报错，因为超过4000个字符。其实wm_concat函数之所以慢，是因为需要以task_name为维度拼凑的数据量太大。没有解决办法吗？我又想了想，为什么要用wm_concat函数呢？应用拿到这个字段后要做什么？是否显示在首页？这个显示是没有意义的，因为wm_concat的结果可能很大，根本显示不出来。既然显示不完整，为什么还要从DB中获取完整的内容呢？带着这些疑惑，我和SQL开发人员进行了沟通。原来，应用程序获取SQL数据后，并没有显示在前端页面上，而是在应用程序中继续处理，经过一些复杂的逻辑处理后，会以另一种形式显示在页面上.这时候，多年的行业经验告诉我：业务逻辑既然可以用Java实现，那么也必须通过SQL在DB中实现，这样才能避免wm_concat函数。于是决定深入了解业务功能，希望在业务解决方案上有所突破。这样就形成了一个初步的工作计划：理解整体业务功能和逻辑-->理解应用处理逻辑-->重写SQL语句-->功能测试-->性能周期调整。经过大约两个小时的一对一讲解，基本掌握了整体的业务功能和逻辑、应用技术架构和处理逻辑。这其实是一个报表显示功能，就是按地区和里程碑显示两个相邻里程碑之间的时间间隔，包括计划间隔时间和实际间隔天数（平均）。报表格式大致如下：在DB中，里程碑计划和实际时间存储在一个二维表中，结构如下：这里有一个行列转换的问题，即转换TASK_NAME从基于行的存储到基于列的显示。为了实现这种结构改造，当时的架构设计是这样的：通过SQL从DB中获取配送区的各个里程碑、plan_start_time、plan_end_time、actual_start_time、actual_end_time和du集合，也就是SQL中wm_concat的结果。Java应用得到这个结果后，循环遍历结果集，依次分解wm_concat拼凑的内容：计算DU在每个里程碑的平均时间间隔；判断里程碑的前后关系；计算前后里程碑之间的天数间隔；最后在前端页面显示计算结果。04.会是水到渠成的事情，还要看一战情况从上面的描述中，我们可以提炼出如下信息：WM_CONCAT拼凑的内容只是过渡性的，需要在Java中依次分解。Java处理的几个步骤完全可以用SQL来实现。这样可以省去下面的“麻烦”：省去了从DB向Java服务器传输大量数据的成本。wm_concat这根刺顺理成章拔掉了。那么，如果使用SQL来实现上述逻辑功能，有两个难点。一个是如何判断里程碑（task_name）和另一个的关系。进一步分析发现可以通过SQL获取里程碑（task_name）的前后关系，在时间间隔的计算中，可以通过leadwindow分析函数获取post时间，然后相减.修改后的SQL如下：在DB中运行SQL，不到3秒执行完毕。心得01.心有余悸，学无止境。值得一提的是，这条SQL不是一蹴而就的。从第一次改写到最后上线，经历了几个版本，但整体架构没有变化。只是针对一些特殊场景做了调整。我来到项目的第一个SQL优化就是这样磕磕绊绊完成的。由于时间关系，整个过程都很紧张。现在回想起来，既庆幸又害怕。好在问题得到及时解决；怕的是那时候可以说是不知者无畏，不熟悉环境，不熟悉利害关系就解决了问题。如果放在几个月之后，我想我当时一定没有完成这件事的勇气和决心。回过头来看，这次wm_concat引发的性能事件还是给了我们很多启发：SQL优化不是孤立存在的，也就是说，并不是所有的SQL本身都有优化的空间。当SQL本身无法优化，或者优化后的空间不足以满足用户需求时，就需要从全局需求上进行突破。尝试换一种方式得出结果：这不就是同一个目标的真相吗？俗话说，走投无路，又一村，关键是你是否愿意积极寻求和突破。SQL优化其实很简单。SQL优化不需要高深的学识和高深的技术，SQL优化也没有那么神秘。一点技术，一点经验，一点运气就够了。一点点技术这里说的技术就是SQL技术。我觉得SQL语言是除了汇编以外所有语言中最神奇、最简单、最艺术的语言。简单的说，就select查询而言，就selectfromwhere、orgrouporder等关键字而言，在SQL方面，有四种功能：select、update、delete、insert.并且易于理解。很神奇，因为这些关键词可以千变万化，不需要排列组合。在当今信息化时代，无非就是增删改查；大千世界，众生无一例外。以人自身为例，人生哲学是：生老病死，生是插入，岁月让人老是更新，千百度搜他是选，乐在其中是删.说到艺术，简单而不简单，这就是艺术，几个关键词就能撑起世间万物的沉浮，这就是艺术。这里所说的SQL技术的掌握，不仅仅是掌握这几个关键字，用这几个关键字来改变各种结果，还要掌握如何用这几个关键字来达到这种艺术效果。一点点体会经验是一件很美妙的事情，一旦你对某个知识点有了经验，下次遇到它，你就能不费吹灰之力地解决了。比如这次的wm_concat函数，相信之前的同事没有定位问题，也就是他没有遇到过wm_concat函数。所以总结经验是绝对正确的，虽然经验不一定是有用的机会。一点点运气，一点点学到的知识，一点点积累的经验，就用上了，这就是运气。所以，运气也是辩证的。表面上看是侥幸解决了问题，其实不然。没有一点知识和经验，是不会这么顺利解决的。可见偶然中也有必然性。批评是在7月25日周末发起的。周一早上开发师兄跟我说优化效果很明显，用户很满意。看着他那稚嫩略带青涩的笑脸，我也长长地舒了一口气，毕竟这是我的第一个优化案例。“黄公，你怎么知道可以这样处理的？”面对他的问题，我一时语塞，怎么回答？“一方面，我们的架构规范是这样的，要求逻辑处理尽量用Java来做，减少DB的负载；另一方面，我也写不出这么复杂的SQL，我还没有还不明白我的SQL。”于是，我就告诉他：“在二维关系系统中，Java可以处理的二维数据，用SQL都可以实现”“哦”“对了，你是怎么选择wm_concat函数的？”我知道这个功能很少用到，也是Oracle公司未公开的内部功能。“在网上找了资料，看到这个函数可以实现功能，就用了，没想到会造成这么大的性能问题。”看得出来，他还是保持着学生气，自责不已，他似乎又想到了什么，连忙补充道，“因为时间太紧了，现在是敏捷开发，两周出一个版本。”如果时间充裕，我想我也可以通过查资料来写这条SQL。”他说的有些激动，但实际上他是认真的，真的做到了。在后来的开发过程中，他写了连我都不会写的复杂SQL。通过与他的交谈，我可以大致勾勒出这个项目的一些基本要素：敏捷开发、双周迭代、无开发DBA、重Java轻SQL。这些都是中国大部分项目的通病。初看并不意外，但出现在世界500强，国内IT软件天堂的大公司，还是让我感到意外，让人后背发凉。敏捷开发要求快速交付，功能优先绩效，急功近利；庞大的企业级平台项目配不上专职开发DBA，SQL质量堪忧。但是，重Java轻SQL是信息管理系统的大忌，会隐藏很多性能风险，是性能的催化剂。这意味着我接下来的路，注定是坎坷的，曲折的，布满荆棘的。
