如何让你的sql运行得更快 - gprime的专栏 - csdnblog.htm

想学oracle_sql优化的程序员有福了

style="FONT-SIZE: 10pt">索引的信息；想看更</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">详细</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">的信息，需用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">sa</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">角色</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">dbcc(3604,310,302)</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">。</SPAN></FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">三、不可</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">优</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">化的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">where</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">子句</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">1.</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">例：下列</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">条件</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">语</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">句中的列都建有恰当的索引，但</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行速度却非常慢：</SPAN></FONT></DIV>
<DIV><FONT face="Comic Sans MS"><FONT color=#0000ff><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select * from record where</SPAN></FONT><FONT 
size=2><SPAN 
style="FONT-SIZE: 10pt">substring(card_no,1,4)='5378'(13</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">秒</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">)</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face="Comic Sans MS"><FONT color=#0000ff><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select * from record where</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">amount/30&lt; 1000</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">11</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face="Comic Sans MS"><FONT color=#0000ff><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select * from record where</SPAN></FONT><FONT 
size=2><SPAN 
style="FONT-SIZE: 10pt">convert(char(10),date,112)='19991201'</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">10</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">分析：</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">where</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">子句中</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">对</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">列的任何操作</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">结</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">果都是在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">运行</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">时</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">逐列</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">计</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">算得到的，因此它不得不</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">进</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行表搜索，而没有使用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">该</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">列上面的索引；</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">如果</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">这</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">些</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">结</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">果在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查询编译时</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">就能得到，那</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">么</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">就可以被</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">优</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">化器</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">优</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">化，使用索引，避免表搜索，因此将</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">重写成下面</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">这样</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">：</SPAN></FONT></DIV>
<DIV><FONT face="Comic Sans MS"><FONT color=#0000ff><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select * from record where card_no 
like'5378%'</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">&lt; 1</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face="Comic Sans MS"><FONT color=#0000ff><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select * from record where amount&lt; 
1000*30</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">&lt; 1</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face="Comic Sans MS"><FONT color=#0000ff><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select * from record where date= 
'1999/12/01'</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">&lt; 1</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">你会</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">发现</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">明</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">显</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">快起来！</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">2.</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">例：表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">stuff</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">有</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">200000</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">id_no</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上有非群集索引，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">请</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">看下面</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">这</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">个</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">：</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">select count(*) from stuff where 
id_no in('0','1')</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">23</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">分析：</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">---- where</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">条件中的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">'in'</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">逻辑</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上相当于</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">'or'</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，所以</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">语</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">法分析器会将</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">in ('0','1')</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">转</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">化</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">为</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">id_no ='0' or id_no='1'</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">来</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行。</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">我</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">们</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">期望它会根据</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">每</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">个</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">or</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">子句分</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">别查</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">找，再将</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">结</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">果相加，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">这样</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">可以利用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">id_no</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上的索引；</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">但</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">实际</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上（根据</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">showplan</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">）</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">,</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">它却采用了</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">"OR</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">策略</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">"</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，即先取出</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">满</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">足</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">每</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">个</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">or</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">子句的行，存入</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">临时</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">数据</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">库</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">的工作表中，再建立唯一索引以去掉重</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">复</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行，最后从</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">这</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">个</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">临时</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表中</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">计</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">算</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">结</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">果。因此，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">实际过</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">程没有利用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">id_no</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上索引，并且完成</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">时间还</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">要受</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">tempdb</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">数据</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">库</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">性能的影响。</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">实</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">践</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">证</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">明，表的行数越多，工作表的性能就越差，当</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">stuff</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">有</SP