SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化
<p>在数据库的应用中，我们经常需要对数据库进行多表查询，然而当数据量非常大时多表查询会对执行效率产生非常大的影响，因此我们在使用JOIN和LEFT JOIN 和 RIGHT JOIN语句时要特别注意；</p>
<p>
SQL语句的join原理：</p>
<p>
数据库中的join操作，实际上是对一个表和另一个表的关联，而很多错误理解为，先把这两个表来一个迪卡尔积，然后扔到内存，用where和having条件来慢慢筛选，其实数据库没那么笨的，那样会占用大量的内存，而且效率不高，比如，我们只需要的一个表的一些行和另一个表的一些行，如果全表都做迪卡尔积，这开销也太大了，真正的做法是，根据在每一个表上的条件，遍历一个表的同时，遍历其他表，找到满足最后的条件后，就发送到客户端，直到最后的数据全部查完，叫做嵌套循环查询。</p>
<p>
<strong>1、LEFT JOIN 和 RIGHT JOIN优化</strong></p>
<p>
在MySQL中，实现如 A LEFT JOIN B join_condition 如下:</p>
<p>
1、表B依赖赖与表A及所有A依赖的表</p>
<p>
2、表A依赖于所有的表，除了LEFT JOIN 的表(B)</p>
<p>
3、join_condition决定了怎样来读取表B，where条件对B是没有用的</p>
<p>
4、标准的where会和LEFT JOIN联合优化</p>
<p>
5、如果在A中的一行满足where和having条件，B中没有，会被填充null</p>
<p>
RIGHT JOIN 与LEFT JOIN类似，这个位置是可以互换的</p>
<p>
LEFT JOIN 与 正常JOIN之间的转换原则上当where条件，对于生成的null行总返回false时，可以直接转化为正常的join</p>
<p>
如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_970079">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">t1 </code><code class="sql color2">LEFT</code> <code class="sql color1">JOIN</code> <code class="sql plain">t2 </code><code class="sql keyword">ON</code> <code class="sql plain">(column1) </code><code class="sql keyword">WHERE</code> <code class="sql plain">t2.column2=5;</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>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_489000">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">t1, t2 </code><code class="sql keyword">WHERE</code> <code class="sql plain">t2.column2=5 </code><code class="sql color1">AND</code> <code class="sql plain">t1.column1=t2.column1;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
注：因为设置了条件t2.column2 = 5,那么对于所有的生成的t2为null的行都是不成立的</p>
<p>
这样的优化将非常快速，因为这样相当于把外连接转换为等值连接，少了很多行的扫描和判断。</p>
<p>
嵌套循环JOIN算法----Nested-Loop Join</p>
<p>
简单的嵌套循环算法就是从一个表开始，通过对表的条件找到一行，然后找下一个表的数据，找完后，又回到第一个表来寻找满足条件的行</p>
<p>
例如，有三个表t1, t2, t3，他们的join类型为：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_543314">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Table</code> <code class="sql color1">Join</code> <code class="sql plain">Type</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">t1 range</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">t2 ref</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">t3 </code><code class="sql color1">ALL</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>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_787448">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t1 matching range { </code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t2 matching reference </code><code class="sql keyword">key</code> <code class="sql plain">{ </code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t3 { </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">if row satisfies </code><code class="sql color1">join</code> <code class="sql plain">conditions, </code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">send </code><code class="sql keyword">to</code> <code class="sql plain">client </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">}</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
即，t1表通过范围扫描，t2关联t1，t3为全表扫描</p>
<p>
注：先根据对t1表的条件范围找到一行，和t2匹配，然后寻找t3的满足条件的行</p>
<p>
<strong>块嵌套循环JOIN算法 ---- Block Nested-Loop Join</strong></p>
<p>
这个算法的应用为：由于之前的嵌套算法每读一个表的一行后，就会读下表，这样内部的表会被读很多次，所以，数据库利用了join缓存(join buffer)来存储中间的结果，然后读取内部表的时候，找到一行，都和这个缓存中的数据比较，以此来提高效率。例如：一次从外表读10行，然后读内部表时，都和这10行数据进行比较。</p>
<p>
<strong>MySQL使用join buffer的条件为：</strong></p>
<p>
1、join_buffer_size系统变量决定了每个join使用的buffer大小</p>
<p>
2、join类型为index或all时，join buffer才能被使用</p>
<p>
3、每一个join都会分配一个join buffer，即一个sql可能使用多个join buffer</p>
<p>
4、join buffer 不会分配给第一个非常量表</p>
<p>
5、只有需要引用的列会被放到join buffer中，不是整行</p>
<p>
最终生成伪代码为：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_898112">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t1 matching range { </code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t2 matching reference </code><code class="sql keyword">key</code> <code class="sql plain">{ </code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">store used columns </code><code class="sql keyword">from</code> <code class="sql plain">t1, t2 </code><code class="sql color1">in</code> <code class="sql color1">join</code> <code class="sql plain">buffer </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">这里将t1和t2使用的列存到</code><code class="sql color1">join</code> <code class="sql plain">buffer中 </code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">if buffer </code><code class="sql keyword">is</code> <code class="sql keyword">full</code> <code class="sql plain">{ </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t3 { </code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each t1, t2 combination </code><code class="sql color1">in</code> <code class="sql color1">join</code> <code class="sql plain">buffer { </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">if row satisfies </code><code class="sql color1">join</code> <code class="sql plain">conditions, </code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">send </code><code class="sql keyword">to</code> <code class="sql plain">client </code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql plain">empty buffer </code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">} </code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">if buffer </code><code class="sql keyword">is</code> <code class="sql color1">not</code> <code class="sql plain">empty { </code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each row </code><code class="sql color1">in</code> <code class="sql plain">t3 { </code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql keyword">for</code> <code class="sql plain">each t1, t2 combination </code><code class="sql color1">in</code> <code class="sql color1">join</code> <code class="sql plain">buffer { </code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql plain">if row satisfies </code><code class="sql color1">join</code> <code class="sql plain">conditions, </code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql plain">send </code><code class="sql keyword">to</code> <code class="sql plain">client </code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number23 index22 alt2">
<code class="sql spaces"> </code><code class="sql plain">} </code>
</div>
<div class="line number24 index23 alt1">
<code class="sql plain">}</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
注：在第二个循环才把数据存在join buffer中，这正好印证了上面的第4点</p>
頁:
[1]