一次非典型SQL优化：如何通过业务逻辑优化另辟蹊径？

   <p>拿到SQL的时候，发现非常简单，如下：</p>    <p style="text-align: center;"><img src="https://simg.open-open.com/show/6459cb5b56d087e5b7d10ff4b0fe7652.jpg"></p>    <p>在简短沟通后，得到如下信息：</p>    <ol>     <li> <p>该SQL的功能是从TM_TASK_T和TM_TASK_HIS_T表中找出相同的数据，即得到两个表的 <strong>交集</strong> ；</p> </li>     <li> <p>除了该SQL外，还存在另外9个类似功能的SQL，其共同点是获取 <strong>当前表和历史表的交集；</strong></p> </li>     <li> <p>该SQL耗时大约在20S左右，但是全部10个SQL的 <strong>耗时加起来就超出了120S的超时阈值；</strong></p> </li>     <li> <p>这一系列SQL的目的是检验数据的 <strong>唯一性</strong> ，即确保当前表和历史表的数据 <strong>不重复</strong> 。</p> </li>    </ol>    <h3><strong>1 看执行计划</strong></h3>    <p>我开始对该SQL进行分析，SQL看起来非常简洁，就两个表关联，并且全部字段都来自于HIS表。执行计划如下：</p>    <p style="text-align: center;"><img src="https://simg.open-open.com/show/90b3b52a36e2b402adeca7d34041f8a2.jpg">+</p>    <p>因为整个SQL中TM_TASK_T表 <strong>只在关联条件上访问了TASK_ID字段</strong> ，而TASK_ID上又创建了UNIQUE索引TM_TASK_T_PK，所以对TASK表的访问方式是：INDEX FAST FULL SCAN，也就是 <strong>没有“回表”</strong> （table access by index rowid）。</p>    <p>我又查看了两个表的数据量，HIS的数据量为500万+，TASK的数据量接近4000万，TM_TASK_HIS_T为驱动表，并且与TM_TASK_T表HASH JOIN也是正确的选择。</p>    <p>因此，从执行计划上看，似乎没有什么优化的空间了。</p>    <h3><strong>2 等价SQL改写 </strong></h3>    <p>再回到功能上，SQL的功能是获取两个结果集的交集，再看看这两个结果集的关系，从表对象的命名上就可以猜出个大概：即TM_TASK_HIS_T表是TM_TASK_T表的历史表。而 <strong>TASK_ID是TM_TASK_T表的主键</strong> ， 所以在取交集的时候， <strong>只需要TASK_ID字段。</strong></p>    <p>那么，在TM_TASK_HIS_T表中TASK_ID是否也有索引呢？查看了表结构后，发现在TM_TASK_HIS_T表中，TASK_ID上也创建了主键索引：TM_TASK_HIS_T_PK。</p>    <p>此时，一个习惯性优化方案在脑海中闪过： 可以通过这两个主键索引先获取TASK_ID的交集，再根据TASK_ID交集从TM_TASK_HIS_T表中获取字段信息。</p>    <p>根据上述思路，我将SQL改写如下：</p>    <p style="text-align: center;"><img src="https://simg.open-open.com/show/f5826a08a184f006dfa571d8dfbe12de.jpg"></p>    <p>改写后的SQL耗时在10S左右，性能提升了1倍。</p>    <p>我们先对比下前后两个执行计划：</p>    <ul>     <li> <p>改写前的执行计划：</p> </li>    </ul>    <p style="text-align: center;"><img src="https://simg.open-open.com/show/d60c139ee00f9e34ed0245a97e9ae80e.jpg"></p>    <ul>     <li> <p>改写后的执行计划：</p> </li>    </ul>    <p style="text-align: center;"><img src="https://simg.open-open.com/show/7e92ff65a60010671e0f2c447197dd08.jpg"></p>    <p>可以看出，改写后在IO读写上还是有很大提升的。但是，现在的问题是，这种提升似乎还是 <strong>无法从根本上解决性能问题</strong> ，仍然存在超时的风险，而且随着 <strong>数据的增长，风险的概率也越来越大。</strong></p>    <p>然而，基于SQL的优化空间在性能要求面前显得捉襟见肘。</p>    <h3><strong>3 技术方案调整</strong></h3>    <p>既然SQL本身上无法突破，那就退一步，从功能框架上看看是否存在“突破口”。</p>    <p>此时，我想起了当时沟通是得到的一个信息：“除了该SQL外，还存在另外9个类似功能的SQL”。我进一步的了解到这10个功能点是相对独立的，即相互之间 <strong>不存在依赖</strong> ，也就是说，这10个原本串行执行的SQL是可以 <strong>并行执行</strong> 的，这样一来，耗时的计算方法发生了变化：</p>    <p><img src="https://simg.open-open.com/show/b360ac908e25c47963149be6f89cedb7.png"></p>    <p>性能耗时从串行执行的求和变成了并行执行的求最大值，也就是说，如果选择了并行执行，只需要保证单个SQL的最大耗时不超过120S即可。而这点显然是可以完成的。但是开发同事则极力反对，反对的理由是：</p>    <ol>     <li> <p>这一系列SQL被应用程序封装成一个任务，该任务并非定时执行，而是人为手动执行，如果被拆分成10个任务，也就意味着需要用户手工触发10个任务， <strong>用户体验太差</strong> <strong>；</strong></p> </li>     <li> <p>拆分成10个任务，不利于后期的 <strong>维护管理</strong> 。</p> </li>    </ol>    <p>以上的两个理由，尤其是第一个理由，的确无可辩驳。</p>    <h3><strong>4 探究原始需求</strong></h3>    <p>看来由串行改并行，此路不通。尽管如此， <strong>生活还要继续，优化不能停止</strong> 。此路不通，只能另觅他方，再退一步，回到SQL的最原始需求，从最原始的需求出发，看能否找到优化的空间。</p>    <p>解铃还须系铃人，再次与开发同事进行了深入沟通，了解到的信息如下：</p>    <ol>     <li> <p>在日常业务运营中，因为业务需求，需要从当前表清理掉一部分数据，在清理前，程序会将本次要清理的数据 <strong>写入到历史表中，然后再清理；</strong></p> </li>     <li> <p>由于一些异常情况，会导致被清理的数据写入了历史表，而没有从当前表中清理掉；</p> </li>     <li> <p>为了及时发现这些异常数据，在后台启动了一个定时任务，周期性的监控历史表与当前表的数据；</p> </li>     <li> <p>当监控到数据异常（即重复）时，就会 <strong>手工触发</strong> 这一系列的SQL清理数据。</p> </li>    </ol>    <p>此时，我的第一反应是：能不能 <strong>跑增量数据</strong> ？ 目前是全表判断是否重复，如果能做到增量判断，性能的提升肯定是质变的。我设想的增量方案是： 以TM_TASK_HIS_T表为主表，获取到自上次数据修复到本次数据修复期间的TASK_ID，判断这些TASK_ID是否在TM_TASK_T表中存在？</p>    <p>增量的思路是让人兴奋的，但是如何做到增量呢？现有的模型结构是否能支撑增量方案呢？</p>    <p>说到增量，最先让人想到的是时间字段，在HIS表中有CREATION_DATE和LAST_UPDATE_DATE，我们一般都会用LAST_UPDATE_DATE来识别增量数据。 HIS表中的LAST_UPDATE_DATE字段能用作本次的增量识别依据吗？</p>    <p>经过一番论证，发现不能作为增量识别依据，理由是：当数据从当前表写入到历史表时，LAST_UPDATE_DATE字段值是不会发生变化的，也就是说写入到历史表的LAST_UPDATE_DATE值是 <strong>不连续</strong> 的，自然就 <strong>不能用作增量识别依据</strong> 。除非我们在写入到历史表的时候，将LAST_UPDATE_DATE的值赋为sysdate，但是显然是不允许的，因为这样一改写，就破坏了数据的原始性。</p>    <p>LAST_UPDATE_DATE不行，CREATION_DATE就更加不行了。难道增量方案就这样夭折了吗？</p>    <h3><strong>5 山重水复疑无路，柳暗花明又一村</strong></h3>    <p>目前，TM_TASK_HIS_T表缺少这样一个字段：能识别出数据写入到历史表的时间。如果有这样一个字段，我们就能用来作为增量识别的依据。既然没有，那么我们就可以 <strong>新建这样一个字段</strong> ，一方面记录了数据被清理到历史表的时间，以便后续核查；另一方面也满足了增量识别的需求。 <strong>两相欢喜，何乐而不为呢？</strong></p>    <p>一开始，开发同事也不反对，紧接着他好像觉察到了什么？很是抵制，连说三个 “ <strong>不行</strong> ” 。原因是增加了这个字段后，他需要同步修改涉及到新增字段的代码。我淡淡的说：不用担心，在创建字段的时候， <strong>增加sysdate的默认值</strong> 就行了。</p>    <h3><strong>6 总结</strong></h3>    <p>这个优化案例很简单，但是过程却有些曲折漫长，也显得有些另类，因为这不是一个典型的基于SQL的优化案例，它最终是通过优化业务逻辑来满足了性能需求，当然为了支撑这个业务逻辑优化，又涉及到了模型、SQL的变更。这个案例的意义在于它的整个过程涵盖了因SQL引发性能问题的解决方案的 全路径：调整执行计划—>改写等价SQL—>优化技术方案—>优化业务逻辑。</p>    <p>在SQL优化的过程中，我们都习惯性地止步于改写等价SQL，一方面，80%以上的问题都能通过这两步来完成，另一方面这两步我们拥有 <strong>完全的控制权</strong> 。然而，当我们走完前两步仍然没有解决性能问题的时候，就需要考虑再往前迈一步，走出这一步，或许就海阔天空了。而迈出这一步是艰难的，原因如下：</p>    <ol>     <li> <p>迈出这一步后，我们就需要与更多的人员沟通，这是SQL优化人员的普遍通病， 宁愿自己花10个小时闷头苦干，也不愿意花一分钟与相关人员沟通；</p> </li>     <li> <p>迈出这一步后，我们就失去了优化的完全控制权，我们需要面临利害相关人员的 <strong>干预，甚至抵制</strong> ，他们会说：这不行，那也不行。因为我们的优化方案会导致他们做适应性修改，触动了他们的 <strong>利益堡垒</strong> ；</p> </li>     <li> <p>迈出这一步后，我们就需要有一颗强大的心脏，随时准备与相关人员PK，接受他们对优化方案的 <strong>质疑</strong> ，并不断 <strong>完善优化方案；</strong></p> </li>     <li> <p>迈出这一步后，我们就进入了一个利害纠纷的圈子，为了实施我们的优化方案，就需要与涉及到优化方案的相关人员 <strong>博弈。</strong></p> </li>    </ol>    <p> </p>    <p>来自：http://dbaplus.cn/news-21-798-1.html</p>    <p> </p>