常用SQL语句优化技巧总结【经典】
<p>本文实例总结了常用SQL语句优化技巧。分享给大家供大家参考，具体如下：</p>
<p>
除了建立索引之外，保持良好的SQL语句编写习惯将会降低SQL性能问题发生。</p>
<p>
<strong>①通过变量的方式来设置参数</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_716985">
<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 plain">stringsql = </code><code class="sql string">"select * from people p where p.id = ? "</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_548258">
<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 plain">stringsql = </code><code class="sql string">"select * from people p where p.id = "</code><code class="sql plain">+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>
数据库的SQL文解析和执行计划会保存在缓存中，但是SQL文只要有变化，就得重新解析。</p>
<p>
“…where p.id = ”+id的方式在id值发生改变时需要重新解析，这会耗费时间。</p>
<p>
<strong>②不要使用select *</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_666836">
<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 plain">stringsql = </code><code class="sql string">"select people_name,pepole_age from people "</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_50655">
<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 plain">stringsql = </code><code class="sql string">"select * from people "</code><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>
使用select *的话会增加解析的时间，另外会把不需要的数据也给查询出来，数据传输也是耗费时间的，</p>
<p>
比如text类型的字段通常用来保存一些内容比较繁杂的东西，如果使用select *则会把该字段也查询出来。</p>
<p>
<strong>③谨慎使用模糊查询</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_12587">
<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 plain">stringsql = </code><code class="sql string">"select * from people p where p.id like 'parm1%' "</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_262625">
<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 plain">stringsql = </code><code class="sql string">"select * from people p where p.id like '%parm1%' "</code><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>
当模糊匹配以%开头时，该列索引将失效，若不以%开头，该列索引有效。</p>
<p>
<strong>④不要使用列号</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_351891">
<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 plain">stringsql = </code><code class="sql string">"select people_name,pepole_age from people order by name,age"</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_507193">
<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 plain">stringsql = </code><code class="sql string">"select people_name,pepole_age from people order by 6,8"</code><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>
使用列号的话，将会增加不必要的解析时间。</p>
<p>
<strong>⑤优先使用UNION ALL，避免使用UNION</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_745892">
<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 plain">stringsql = </code><code class="sql string">"select name from student union all select name from teacher"</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_373128">
<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 plain">stringsql = </code><code class="sql string">"select name from student union select name from teacher"</code><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>
UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况，如果业务上能够确保不会出现重复记录。</p>
<p>
<strong>⑥在where语句或者order by语句中避免对索引字段进行计算操作</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_517714">
<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 plain">stringsql = </code><code class="sql string">"select people_name,pepole_age from people where create_date=date1 "</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_434893">
<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 plain">stringsql = </code><code class="sql string">"select people_name,pepole_age from people where trunc(create_date)=date1"</code><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>
当在索引列上进行操作之后，索引将会失效。正确做法应该是将值计算好再传入进来。</p>
<p>
<strong>⑦使用not exist代替not in</strong></p>
<p>
好：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_507264">
<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 plain">stringsql = </code><code class="sql string">"select * from orders where customer_name not exist (select customer_name from customer)"</code><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>
坏：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_787742">
<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 plain">stringsql = </code><code class="sql string">"select * from orders where customer_name not in(select customer_name from customer)"</code><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>
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。</p>
<p>
<strong>⑧ exist和in的区别</strong></p>
<p>
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。因此，in用到的是外表的索引， exists用到的是内表的索引。</p>
<p>
如果查询的两个表大小相当，那么用in和exists差别不大。</p>
<p>
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：</p>
<p>
例如：表A（小表），表B（大表）</p>
<p>
1：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_255438">
<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">A </code><code class="sql keyword">where</code> <code class="sql plain">cc </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">cc </code><code class="sql keyword">from</code> <code class="sql plain">B)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
效率低，用到了A表上cc列的索引；</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_730991">
<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">A </code><code class="sql keyword">where</code> <code class="sql plain">exists(</code><code class="sql keyword">select</code> <code class="sql plain">cc </code><code class="sql keyword">from</code> <code class="sql plain">B </code><code class="sql keyword">where</code> <code class="sql plain">cc=A.cc)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
效率高，用到了B表上cc列的索引。</p>
<p>
2：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_368504">
<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">B </code><code class="sql keyword">where</code> <code class="sql plain">cc </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">cc </code><code class="sql keyword">from</code> <code class="sql plain">A)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
效率高，用到了B表上cc列的索引；</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_647329">
<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">B </code><code class="sql keyword">where</code> <code class="sql plain">exists(</code><code class="sql keyword">select</code> <code class="sql plain">cc </code><code class="sql keyword">from</code> <code class="sql plain">A </code><code class="sql keyword">where</code> <code class="sql plain">cc=B.cc)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
效率低，用到了A表上cc列的索引。</p>
<p>
<strong>⑨避免在索引列上做如下操作：</strong></p>
<p>
◆避免在索引字段上使用<>，!=<br>
◆避免在索引列上使用IS NULL和IS NOT NULL<br>
◆避免在索引列上出现数据类型转换（比如某字段是String类型，参数传入时是int类型）</p>
<p>
当在索引列上使用如上操作时，索引将会失效，造成全表扫描。</p>
<p>
<strong>⑩复杂操作可以考虑适当拆成几步</strong></p>
<p>
有时候会有通过一个SQL语句来实现复杂业务的例子出现，为了实现复杂的业务，嵌套多级子查询。造成SQL性能问题。对于这种情况可以考虑拆分SQL，通过多个SQL语句实现，或者把部分程序能完成的工作交给程序完成。</p>
<p>
希望本文所述对大家数据库程序设计有所帮助。</p>
頁:
[1]