一次因表变量导致SQL执行效率变慢的实战记录
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>场景</li><li>
job执行流程分析</li><li>
逐一排除问题</li><li>
解决问题<ul class="second_class_ul"><li>
1.通过使用临时表代替表变量</li><li>
2.修改目标tableb的写入逻辑</li></ul></li><li>
总结<ul class="second_class_ul"></ul></li></ul></div><div id="navcategory">
<h5 class="catalogue">
目录</h5>
<ul class="first_class_ul">
<li>
场景</li>
<li>
job执行流程分析</li>
<li>
逐一排除问题</li>
<li>
解决问题
<ul class="second_class_ul">
<li>
1.通过使用临时表代替表变量</li>
<li>
2.修改目标tableb的写入逻辑</li>
</ul>
</li>
<li>
总结
<ul class="second_class_ul"></ul>
</li>
</ul>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
场景</h2>
<p>
最近工作中，发现某同步job在执行中经常抛出sql执行超时的问题，查看日志发现每次sql执行的时间都是线性增长的，循环执行50次以后执行时间甚至超过了5分钟</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
job执行流程分析</h2>
<p>
首先，对于job流程进行分析，查看是否是job设计上的问题</p>
<p>
<img title="一次因表变量导致SQL执行效率变慢的实战记录" alt="一次因表变量导致SQL执行效率变慢的实战记录" loading="lazy" src="https://zhuji.jb51.net/uploads/img/202305/34830ae6f64c96c787abb0f0a2dec2b6.jpg"></p>
<p>
通过对流程的分析，发现每次获取的需要同步的数据最多只有一万条，不存在大数据写入导致超时的问题。</p>
<p>
那么在对获取详细信息这个过程进行分析，发现关联的表中最多的数据已经上亿了，可能是这里导致了整体sql执行变慢的原因。这里能算可疑点一。</p>
<p>
再接着往下一个流程看与表b对比重复数据时，随着循环执行表b的数据会越来越多，那么会不会这里是导致循环执行下执行时间称线性增长的主要原因呢。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
逐一排除问题</h2>
<p>
之前我们通过分析job执行流程，发现了两个可疑点，那么现在具体分析sql的问题</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_297630">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
<div class="line number44 index43 alt1">
44</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">#tabletemp (</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段a </code><code class="sql keyword">int</code> <code class="sql color1">null</code><code class="sql plain">,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">字段b </code><code class="sql keyword">int</code> <code class="sql color1">null</code><code class="sql plain">,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段c </code><code class="sql keyword">int</code> <code class="sql color1">null</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">#tabletemp(</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段a,</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">字段b</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql plain">)</code><code class="sql keyword">select</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.字段a,</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段b</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">from</code> <code class="sql plain">servera.dbo.tableb a </code><code class="sql keyword">with</code><code class="sql plain">(nolock)</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql color2">left</code> <code class="sql color1">join</code> <code class="sql plain">dbo.tablea b </code><code class="sql keyword">with</code><code class="sql plain">(nolock) a.id = b.id</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql keyword">update</code> <code class="sql plain">a</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">a.字段c = b.字段d</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql keyword">from</code> <code class="sql plain">#tabletemp a</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql color2">left</code> <code class="sql color1">join</code> <code class="sql plain">dbo.tablec b </code><code class="sql keyword">with</code><code class="sql plain">(nolock) </code><code class="sql keyword">on</code> <code class="sql plain">a.字段a =b.id</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number23 index22 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number24 index23 alt1">
<code class="sql spaces"> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">dbo.目标tablea(</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code><code class="sql plain">字段a,</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段b</code>
</div>
<div class="line number27 index26 alt2">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number28 index27 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code>
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql plain">字段a,</code>
</div>
<div class="line number30 index29 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段b</code>
</div>
<div class="line number31 index30 alt2">
<code class="sql spaces"> </code><code class="sql keyword">from</code> <code class="sql plain">#tabletemp </code><code class="sql keyword">with</code><code class="sql plain">(nolock)</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number33 index32 alt2">
<code class="sql spaces"> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">dbo.目标tableb(</code>
</div>
<div class="line number34 index33 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段a,</code>
</div>
<div class="line number35 index34 alt2">
<code class="sql spaces"> </code><code class="sql plain">字段b,</code>
</div>
<div class="line number36 index35 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段c</code>
</div>
<div class="line number37 index36 alt2">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql keyword">distinct</code> </div>
<div class="line number39 index38 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.字段a,</code>
</div>
<div class="line number40 index39 alt1">
<code class="sql spaces"> </code><code class="sql plain">a.字段b,</code>
</div>
<div class="line number41 index40 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.字段c</code>
</div>
<div class="line number42 index41 alt1">
<code class="sql spaces"> </code><code class="sql keyword">from</code> <code class="sql plain">#tabletemp a </code><code class="sql keyword">with</code><code class="sql plain">(nolock)</code>
</div>
<div class="line number43 index42 alt2">
<code class="sql spaces"> </code><code class="sql color2">left</code> <code class="sql color1">join</code> <code class="sql plain">dbo.目标tableb b </code><code class="sql keyword">on</code> <code class="sql plain">a.字段a = b.字段a </code><code class="sql color1">and</code> <code class="sql plain">a.字段b = b.字段b</code>
</div>
<div class="line number44 index43 alt1">
<code class="sql spaces"> </code><code class="sql keyword">where</code> <code class="sql plain">a.pk </code><code class="sql keyword">is</code> <code class="sql color1">null</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
先来查看可疑点一，是不是这里出了问题。因为表tablec数据已经是几亿的量，但单独将该sql执行发现，因为索引的存在发现执行并不是特别慢，所以可以排除掉该问题</p>
<p>
那么来看看可疑点二呢</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_109525">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">dbo.目标tableb(</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段a,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">字段b,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">字段c</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql keyword">distinct</code> </div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.字段a,</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">a.字段b,</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.字段c</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">from</code> <code class="sql plain">#tabletemp a </code><code class="sql keyword">with</code><code class="sql plain">(nolock)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql color2">left</code> <code class="sql color1">join</code> <code class="sql plain">dbo.目标tableb b </code><code class="sql keyword">on</code> <code class="sql plain">a.字段a = b.字段a </code><code class="sql color1">and</code> <code class="sql plain">a.字段b = b.字段b</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql keyword">where</code> <code class="sql plain">a.pk </code><code class="sql keyword">is</code> <code class="sql color1">null</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
可以看到该sql插入的同时还查询了自身是否存在条件下相同的数据，查看表目标tableb发现，该表没有主键也没有索引，再通过dba那边提供的sql分析发现，这句sql对于dbo.目标tableb进行了全表扫描，再加上插入的1w条数据，相当于对于dbo.目标tableb全表扫描了1w次，随着循环的执行该表数据越来越多，执行时间也就越来越长，看来这里就是导致执行时间线性增长的主要原因了。</p>
<p>
<img title="一次因表变量导致SQL执行效率变慢的实战记录" alt="一次因表变量导致SQL执行效率变慢的实战记录" loading="lazy" src="https://zhuji.jb51.net/uploads/img/202305/c0c4d42210e6479ce1212509f1edb618.jpg"></p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
解决问题</h2>
<p>
根据上面问题的排除，我们已经得知问题的关键所在就是进行了1w次的全表扫描，导致了sql执行时间过长，那么解决问题的关键所在就是避免这么多次的全表扫描。那么最直接的解决方法，就是建立索引避免全表扫描</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
1.通过使用临时表代替表变量</h3>
<p>
<img title="一次因表变量导致SQL执行效率变慢的实战记录" alt="一次因表变量导致SQL执行效率变慢的实战记录" loading="lazy" src="https://zhuji.jb51.net/uploads/img/202305/862b68f8e1c95fb6f848ad3a2b83d67a.jpg"></p>
<p>
先来看看，表变量与临时表的区别，可以看到表变量是无法使用索引的，所以我们使用索引避免全表扫描的话必须要代替掉表变量，然后在临时表的字段a上我们创建索引</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
2.修改目标tableb的写入逻辑</h3>
<p>
现有写入逻辑会先判断是否在目标tableb中是否存在，不存在时则写入表中，保持业务的情况下，我们稍微修改下逻辑，再写入之前先排除掉与目标tableb中的数据，将剩余数据写入表中，就能避免循环1w次的目标tableb表查询了</p>
<p>
通过这两处修改后，再执行该job发现问题得到了完美的解决。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
总结</h2>
<p>
到此这篇关于因表变量导致sql执行效率变慢的文章就介绍到这了,更多相关表变量导致sql执行变慢内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://www.cnblogs.com/TheBestTxt/p/15517867.html</p>
頁:
[1]