性能测试之：会引起全表扫描的几种Sql

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">模糊查询效率很低：</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span style="">原因：</span><span lang="EN-US"><span style="font-family: Times New Roman;">like</span></span><span style="">本身效率就比较低，应该尽量避免查询条件使用</span><span lang="EN-US"><span style="font-family: Times New Roman;">like</span></span><span style="">；对于</span><span lang="EN-US"><span style="font-family: Times New Roman;">like ‘%...%’</span></span><span style="">（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span style="">解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即</span><span lang="EN-US"><span style="font-family: Times New Roman;">like ‘…%’</span></span><span style="">，是会使用索引的；左模糊</span><span lang="EN-US"><span style="font-family: Times New Roman;">like</span></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">‘%...’</span></span><span style="">无法直接使用索引，但可以</span><strong><span style="">利用<span lang="EN-US">reverse + function index </span>的形式，变化成<span lang="EN-US"> like </span></span></strong><strong><span style="font-weight: normal; color: #222222;" lang="EN-US"><span style="font-family: Times New Roman;">‘…%’</span></span></strong><strong><span style="">；全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。</span></strong><strong><span style="font-weight: normal; color: #222222;" lang="EN-US"></span></strong></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">查询条件中含有</span><span lang="EN-US"><span style="font-family: Times New Roman;">is null</span></span><span style="">的</span><span lang="EN-US"><span style="font-family: Times New Roman;">select</span></span><span style="">语句执行慢</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span style="">原因：</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle 9i</span></span><span style="">中，查询字段</span><span lang="EN-US"><span style="font-family: Times New Roman;">is null</span></span><span style="">时单索引失效，引起全表扫描。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span style="">解决方法：</span><span lang="EN-US"><span style="font-family: Times New Roman;">SQL</span></span><span style="">语法中使用</span><span lang="EN-US"><span style="font-family: Times New Roman;">NULL</span></span><span style="">会有很多麻烦，</span><span style="">最好索引列都是<span lang="EN-US">NOT NULL</span>的；对于<span lang="EN-US">is null</span>，可以建立组合索引，<span lang="EN-US">nvl(</span>字段<span lang="EN-US">,0),</span>对表和索引<span lang="EN-US">analyse</span>后，<span lang="EN-US">is null</span>查询时可以重新启用索引查找<span lang="EN-US">,</span>但是效率还不是值得肯定；<span lang="EN-US">is not null </span>时永远不会使用索引。一般数据量大的表不要用<span lang="EN-US">is null</span>查询。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">查询条件中使用了不等于操作符（</span><span lang="EN-US"><span style="font-family: Times New Roman;">&lt;&gt;</span></span><span style="">、</span><span lang="EN-US"><span style="font-family: Times New Roman;">!=</span></span><span style="">）的</span><span lang="EN-US"><span style="font-family: Times New Roman;">select</span></span><span style="">语句执行慢</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span style="">原因：</span><span lang="EN-US"><span style="font-family: Times New Roman;">SQL</span></span><span style="">中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 18pt;"><span style="font-size: small;"><span style="">解决方法：通过把不等于操作符改成</span><span lang="EN-US"><span style="font-family: Times New Roman;">or</span></span><span style="">，可以使用索引，避免全表扫描。例如，把</span><span lang="EN-US"><span style="font-family: Times New Roman;">column&lt;&gt;’aaa’</span></span><span style="">，改成</span><span lang="EN-US"><span style="font-family: Times New Roman;">column&lt;’aaa’ or column&gt;’aaa’</span></span><span style="">，就可以使用索引了。</span></span></p>
<pre style=""><span style=""><span style="font-size: 10.5pt;" lang="EN-US">6. </span><span style="font-size: 10.5pt;">使用组合索引，如果查询条件中没有前导列，那么索引不起作用，会引起全表扫描；但是从<span lang="EN-US">Oracle9i</span>开始，引入了索引跳跃式扫描的特性，<span style="color: #323432;">可以允许优化器使用组合索引，即便索引的前导列没有出现在<span lang="EN-US">WHERE</span>子句中。例如：<span lang="EN-US">create index skip1 on emp5(job,empno);<span style=""> </span></span>全索引扫描 <span lang="EN-US">select count(*) from emp5 where empno=7900;<span style=""> </span></span></span>索引跳跃式扫描 <span style="color: #323432;" lang="EN-US">select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900; </span><span style="color: #323432;">前一种是全表扫描，后一种则会使用组合索引。<span lang="EN-US"></span></span></span></span></pre>
<pre><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US">7. or</span><span style="font-size: 10.5pt; color: #323432;">语句使用不当会引起全表扫描<span lang="EN-US"></span></span></span></pre>
<pre style=""><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US"><span style=""> </span></span><span style="font-size: 10.5pt; color: #323432;">原因：<span lang="EN-US">where</span>子句中比较的两个条件，一个有索引，一个没索引，使用<span lang="EN-US">or</span>则会引起全表扫描。例如：<span lang="EN-US">where A=</span>：<span lang="EN-US">1 or B=</span>：<span lang="EN-US">2</span>，<span lang="EN-US">A</span>上有索引，<span lang="EN-US">B</span>上没索引，则比较<span lang="EN-US">B=</span>：<span lang="EN-US">2</span>时会重新开始全表扫描。<span lang="EN-US"></span></span></span></pre>
<pre style=""><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US">8. </span><span style="font-size: 10.5pt; color: #323432;">组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：<span lang="EN-US">create index skip1 on emp5(job,empno</span>，<span lang="EN-US">date);<span style=""> </span>select job</span>，<span lang="EN-US">empno from emp5 where job=’manager’and empno=’10’ order by job,empno,date desc; </span>实际上只是查询出符合<span lang="EN-US">job=’manager’and empno=’10’</span>条件的记录并按<span lang="EN-US">date</span>降序排列，但是写成<span lang="EN-US">order by date desc</span>性能较差。<span lang="EN-US"></span></span></span></pre>
<pre style=""><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US">9. Update </span><span style="font-size: 10.5pt; color: #323432;">语句，如果只更改<span lang="EN-US">1</span>、<span lang="EN-US">2</span>个字段，不要<span lang="EN-US">Update</span>全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。<span lang="EN-US"></span></span></span></pre>
<pre style=""><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US">10.</span><span style="font-size: 10.5pt; color: #323432;">对于多张大数据量（这里几百条就算大了）的表<span lang="EN-US">JOIN</span>，要先分页再<span lang="EN-US">JOIN</span>，否则逻辑读会很高，性能很差。<span lang="EN-US"></span></span></span></pre>
<pre style=""><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US">11.select count(*) from table</span><span style="font-size: 10.5pt; color: #323432;">；这样不带任何条件的<span lang="EN-US">count</span>会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。<span lang="EN-US"></span></span></span></pre>
<pre style=""><span style=""><span style="font-size: 10.5pt; color: #323432;" lang="EN-US">12.sql</span><span style="font-size: 10.5pt; color: #323432;">的<span lang="EN-US">where</span>条件要绑定变量，比如<span lang="EN-US">where column=</span>：<span lang="EN-US">1</span>，不要写成<span lang="EN-US">where column=</span>‘<span lang="EN-US">aaa</span>’，这样会导致每次执行时都会重新分析，浪费<span lang="EN-US">CPU</span>和内存资源。<span lang="EN-US"></span></span></span></pre>