⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

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

📁 想学oracle_sql优化的程序员有福了
💻 HTM
📖 第 1 页 / 共 5 页
字号:
🔍
12345 
  href="http://blog.csdn.net/gprime/archive/2007/07/13/1687930.aspx#737734">支持支持楼主！！！！</A>
  <LI class=listitem>Qim：<A title=点击查看《回复:收集十七项网页恶意代码(转)》 
  href="http://blog.csdn.net/gprime/archive/2007/07/19/1698886.aspx#622717">我晕.打开页面,mcafee就发现有毒,啥意思?</A></LI></UL></SPAN></SPAN><BR><BR></DIV>
<DIV id=main>
<DIV class=Tag>
<SCRIPT language=javascript 
src="如何让你的SQL运行得更快 - gprime的专栏 - CSDNBlog.files/urltag.aspx"></SCRIPT>

<DIV style="CLEAR: both"></DIV></DIV><SPAN class=PreAndNext 
id=viewpost.ascx_PreviousAndNextEntriesUp>
<DIV align=center><A 
href="http://blog.csdn.net/gprime/archive/2007/07/13/1688509.aspx">上一篇:&nbsp;Enterprise 
Library 2.0 -- Logging Application Block</A>&nbsp;|&nbsp;<A 
href="http://blog.csdn.net/gprime/archive/2007/07/11/1685041.aspx">下一篇:&nbsp;VSTO 
服务器缓存文档</A></DIV></SPAN><BR>
<SCRIPT>function StorePage(){d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();}</SCRIPT>

<DIV class=post>
<DIV class=postTitle>
<SCRIPT src="如何让你的SQL运行得更快 - gprime的专栏 - CSDNBlog.files/vote.js"></SCRIPT>
<A href="http://blog.csdn.net/gprime/archive/2007/07/13/1687930.aspx"><IMG 
height=13 src="如何让你的SQL运行得更快 - gprime的专栏 - CSDNBlog.files/authorship.gif" 
width=15 border=0>&nbsp;如何让你的SQL运行得更快</A>&nbsp;&nbsp;
<SCRIPT src="如何让你的SQL运行得更快 - gprime的专栏 - CSDNBlog.files/count.htm"></SCRIPT>
 </DIV>
<DIV class=postText>&nbsp;
<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">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><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">OLTP</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">DSS</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">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">where</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></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></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">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><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">&lt; 1</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">HP LH II---- </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">330MHZ---- </SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">内存：</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">128</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">Operserver5.0.4----</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">Sybase11.0.3</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></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">例：表</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">record</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">有</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">620000</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">---- 1.</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></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 (25</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(55</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 count(*) from record where date &gt;'19990901' 
and place in ('BJ','SH') (27</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 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">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">时</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">---- 2.</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></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 </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 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
12345

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -