sql优化实战 把full join改为left join +union all(从5分钟降为10秒)
<p>今天收到一个需求，要改写一个报表的逻辑，当改完之后，再次运行，发现运行超时。</p>
<p>
因为特殊原因，无法访问客户的服务器，没办法查看sql的执行计划、没办法知道表中的索引情况，所以，尝试从语句的改写上来优化。</p>
<p>
<strong>一、原始语句如下：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_270934">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql color2">isnull</code><code class="sql plain">(vv.customer_id,v.customer_id) </code><code class="sql keyword">as</code> <code class="sql plain">customer_id,</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql color2">isnull</code><code class="sql plain">(vv.business_date,</code><code class="sql color2">replace</code><code class="sql plain">(v.business_date,</code><code class="sql string">'-'</code><code class="sql plain">,</code><code class="sql string">''</code><code class="sql plain">)) </code><code class="sql keyword">as</code> <code class="sql plain">business_date,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">v.prod_id,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">v.sales,</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">vv.visit_count,</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">v.all_sales</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">from</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql plain">a.customer_id ,</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code><code class="sql color2">month</code><code class="sql plain">)+</code><code class="sql string">'-01'</code> <code class="sql keyword">as</code> <code class="sql plain">business_date,</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.PROD_ID ,</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql color2">SUM</code><code class="sql plain">(</code><code class="sql color2">CAST</code><code class="sql plain">(VALUE </code><code class="sql keyword">AS</code> <code class="sql keyword">NUMERIC</code><code class="sql plain">(38, 3))) sales,</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql color2">sum</code><code class="sql plain">(</code><code class="sql color2">SUM</code><code class="sql plain">(</code><code class="sql color2">CAST</code><code class="sql plain">(VALUE </code><code class="sql keyword">AS</code> <code class="sql keyword">NUMERIC</code><code class="sql plain">(38, 3)))) over(partition </code><code class="sql keyword">by</code> <code class="sql plain">a.customer_id) </code><code class="sql keyword">as</code> <code class="sql plain">all_sales</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql keyword">FROM</code> <code class="sql plain">TB_IMPORT_SALES a </code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql keyword">WHERE</code> <code class="sql plain">a.customer_id </code><code class="sql keyword">IS</code> <code class="sql color1">NOT</code> <code class="sql color1">NULL</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql color1">AND</code> <code class="sql plain">a.PROD_ID </code><code class="sql keyword">IS</code> <code class="sql color1">NOT</code> <code class="sql color1">NULL</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql color1">and</code> <code class="sql plain">a.</code><code class="sql color2">month</code><code class="sql plain">=</code><code class="sql string">'2016-11'</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql keyword">GROUP</code> <code class="sql keyword">BY</code> <code class="sql plain">a.customer_id ,</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql plain">a.PROD_ID</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">)v</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql keyword">full</code> <code class="sql color1">join</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">(</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql plain">customer_id, </code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(a.business_date) </code><code class="sql keyword">as</code> <code class="sql plain">business_date,</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql spaces"> </code><code class="sql color2">COUNT</code><code class="sql plain">(*) </code><code class="sql keyword">AS</code> <code class="sql plain">VISIT_COUNT </code>
</div>
<div class="line number27 index26 alt2">
<code class="sql spaces"> </code><code class="sql keyword">FROM</code> <code class="sql plain">TB_CALL_STORE a </code><code class="sql keyword">WITH</code><code class="sql plain">(NOLOCK)</code>
</div>
<div class="line number28 index27 alt1">
<code class="sql spaces"> </code><code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">TB_TIME d</code>
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql keyword">on</code> <code class="sql plain">a.business_date = d.t_date </code>
</div>
<div class="line number30 index29 alt1">
<code class="sql spaces"> </code><code class="sql keyword">where</code> <code class="sql plain">d.</code><code class="sql keyword">section</code> <code class="sql plain">=</code><code class="sql string">'2016-11'</code>
</div>
<div class="line number31 index30 alt2">
<code class="sql spaces"> </code><code class="sql keyword">GROUP</code> <code class="sql keyword">BY</code> <code class="sql plain">customer_id</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql plain">)vv</code>
</div>
<div class="line number33 index32 alt2">
<code class="sql keyword">on</code> <code class="sql plain">v.customer_id = vv.customer_id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
原来是left join，虽然查询比较慢，但是2分钟能查出来，现在按照业务要求，需要看到所有数据，所以改成了full join，改了之后5分钟都查不出结果。</p>
<p>
<strong>二、改写后的代码</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_19150">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">v.customer_id,</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql color2">replace</code><code class="sql plain">(</code><code class="sql keyword">max</code><code class="sql plain">(v.business_date),</code><code class="sql string">'-'</code><code class="sql plain">,</code><code class="sql string">''</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql plain">business_date,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">v.prod_id,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(v.sales_volume) sales_volume ,</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(v.visit_count) visit_count,</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(v.all_sales_volume) all_sales_volume</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">from</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql plain">a.customer_id ,</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(biz_month)+</code><code class="sql string">'-01'</code> <code class="sql keyword">as</code> <code class="sql plain">business_date,</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.PROD_ID ,</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql color2">SUM</code><code class="sql plain">(</code><code class="sql color2">CAST</code><code class="sql plain">(VALUE1 </code><code class="sql keyword">AS</code> <code class="sql keyword">NUMERIC</code><code class="sql plain">(38, 8))) sales_volume,</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql color2">sum</code><code class="sql plain">(</code><code class="sql color2">SUM</code><code class="sql plain">(</code><code class="sql color2">CAST</code><code class="sql plain">(VALUE1 </code><code class="sql keyword">AS</code> <code class="sql keyword">NUMERIC</code><code class="sql plain">(38, 8)))) over(partition </code><code class="sql keyword">by</code> <code class="sql plain">a.customer_id) </code><code class="sql keyword">as</code> <code class="sql plain">all_sales_volume,</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql color1">null</code> <code class="sql keyword">as</code> <code class="sql plain">visit_count</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><code class="sql keyword">FROM</code> <code class="sql plain">TB_IMPORT_SALES a </code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql keyword">WHERE</code> <code class="sql plain">a.customer_id </code><code class="sql keyword">IS</code> <code class="sql color1">NOT</code> <code class="sql color1">NULL</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql color1">AND</code> <code class="sql plain">a.PROD_ID </code><code class="sql keyword">IS</code> <code class="sql color1">NOT</code> <code class="sql color1">NULL</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql color1">and</code> <code class="sql plain">a.</code><code class="sql color2">month</code><code class="sql plain">=</code><code class="sql string">'2016-11'</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql keyword">GROUP</code> <code class="sql keyword">BY</code> <code class="sql plain">a.customer_id ,</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql plain">a.PROD_ID</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code><code class="sql keyword">union</code> <code class="sql color1">all</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">SELECT</code> <code class="sql plain">customer_id, </code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(a.business_date) </code><code class="sql keyword">as</code> <code class="sql plain">business_date,</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql spaces"> </code><code class="sql plain">p.prod_id,</code>
</div>
<div class="line number27 index26 alt2">
<code class="sql spaces"> </code><code class="sql color1">null</code><code class="sql plain">,</code>
</div>
<div class="line number28 index27 alt1">
<code class="sql spaces"> </code><code class="sql color1">null</code><code class="sql plain">,</code>
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql color2">COUNT</code><code class="sql plain">(*) </code><code class="sql keyword">AS</code> <code class="sql plain">VISIT_COUNT </code>
</div>
<div class="line number30 index29 alt1">
<code class="sql spaces"> </code><code class="sql keyword">FROM</code> <code class="sql plain">TB_CALL_STORE a </code><code class="sql keyword">WITH</code><code class="sql plain">(NOLOCK)</code>
</div>
<div class="line number31 index30 alt2">
<code class="sql spaces"> </code><code class="sql color1">cross</code> <code class="sql plain">apply</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number33 index32 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">1 prod_id </code><code class="sql keyword">from</code> <code class="sql plain">TB_PRODUCT </code><code class="sql keyword">with</code><code class="sql plain">(nolock)</code>
</div>
<div class="line number34 index33 alt1">
<code class="sql spaces"> </code><code class="sql plain">)p</code>
</div>
<div class="line number35 index34 alt2">
<code class="sql spaces"> </code><code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">TB_TIME d</code>
</div>
<div class="line number36 index35 alt1">
<code class="sql spaces"> </code><code class="sql keyword">on</code> <code class="sql plain">a.business_date = d.t_date </code>
</div>
<div class="line number37 index36 alt2">
<code class="sql spaces"> </code><code class="sql keyword">where</code> <code class="sql plain">d.</code><code class="sql keyword">section</code> <code class="sql plain">=</code><code class="sql string">'2016-11'</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql spaces"> </code><code class="sql keyword">GROUP</code> <code class="sql keyword">BY</code> <code class="sql plain">customer_id,p.prod_id</code>
</div>
<div class="line number39 index38 alt2">
<code class="sql plain">)v</code>
</div>
<div class="line number40 index39 alt1">
<code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">v.customer_id,</code>
</div>
<div class="line number41 index40 alt2">
<code class="sql spaces"> </code><code class="sql plain">v.prod_id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
由于代码本身比较简单，没办法再进一步简化，而由于连接不了服务器，其他的方法也用不上，甚至没办法分析到底是什么导致运行这么慢。</p>
<p>
想了想，full join 本质上就是 2次left join+union ，无非就是合并数据，于是尝试一下用union all来直接合并数据，现在改成unoin all最后，就不需要full join。</p>
<p>
但是考虑到第2段代码中并没有prod_id这个字段，所以这里在第2段代码加上了cross apply随便取出一个产品的id，这样就有prod_id这个字段，可以合并了。</p>
<p>
修改之后，果然速度降到了10多秒。</p>
<p>
到此这篇关于sql优化实战 把full join改为left join +union all(从5分钟降为10秒)的文章就介绍到这了,更多相关left join +union all内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://blog.csdn.net/sqlserverdiscovery/article/details/53542543</p>
頁:
[1]