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

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

style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">28</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT color=#0000ff><FONT face="Comic Sans MS"><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select count(*) from record where date &gt;'19990901' 
and place in ('BJ','SH')</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">14</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">---- </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">围</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">---- 3.</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">place</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">date</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">amount</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 count(*) from record where date &gt;'19991201' 
and date &lt; '19991214' and amount &gt;2000 </SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">26</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 date,sum(amount) from record group by 
date</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">27</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 count(*) from record where date &gt;'19990901' 
and place in ('BJ, 'SH')</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">---- </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">place</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">place</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">place</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">---- 4.</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">date</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">place</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">amount</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 color=#0000ff><FONT face="Comic Sans MS"><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select count(*) from record where date &gt;'19991201' 
and date &lt; '19991214' and amount &gt;2000(&lt; 1</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 color=#0000ff><FONT face="Comic Sans MS"><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select date,sum(amount) from record group by 
date</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 color=#0000ff><FONT face="Comic Sans MS"><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select count(*) from record where date &gt;'19990901' 
and place in ('BJ','SH')</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">---- </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">date</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">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">---- 5.</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">预测</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></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">between, &gt;,&lt; </SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">&gt;=,&lt; =</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">）和</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">order by</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">、</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">group by</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">可考</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">繁的列。</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