sql索引的作用(超详细)

1、 SQLServerSQLSERVERclusteredindexnonclusteredinde，xanzhang63672页，检字表，页面是390 二、何时使用聚集索引或非聚集索引2004112004101 三、结合实际，谈索引使用的误区 # #SQLSERVER # #IDGidID1。我们的这个办公自动SQLSERVERID # IDIDIDID号来进行ID where绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。ID

2、合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(32511.Selectgid,fariqi,neibuyonghu,titlefromtgongwen用时：128470毫秒(即：128秒)fariqselectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqidateadd(day,-90,getdate()用时：53763毫秒(54秒)fariqiselectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqidateadd(day,-90,getdate()用

3、时：2423毫秒(2秒)25建立在日期列时的差异。事实上，如果您的数据库真的有1000ID12IDselect1.declareddatetimesetd=getdate()select)=datediff(ms,d,getdate()1.select语句执行花费时间(毫秒2、只要建立索引就能显著提高查现上面的例子中，第2、3fariqi1000fariqi5003 # #compoundindex 25fariqineibuyonghu1.（1）selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqi2004-5-5查询速度：2513

4、毫秒1.（2）selectgid,fariqi,neibuyonghu,titlefromTgongwen2.wherefariqi2004-5-5andneibuyonghu=办公室查询速度：2516毫秒1.（3）selectgid,fariqi,neibuyonghu,titlefromTgongwenwhereneibuyonghu二办公室查询速度：60280毫秒1、2四、其他书上没有的索引使用经验总结1、用聚合索引比用不是聚合索引的主键速度快下面是实例语句：（都是提取25万条数据）1.selectgid,fariqi,neibuyonghu,reader,titlefromTgongw

5、enwherefariqi=2004-9-1633261.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwheregid2004-1-163431001.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi2004-6-63170501.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-16用时：3326毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于1.s

6、electgid,fariqi,neibuyonghu,reader,titlefromTgongwen2.wherefariqi2004-1-1andfariqi2004-1-1orderbyfariqi用时：6390毫秒selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi100001.select*fromtable1wheretID10000andname=zhangsantID句仅仅从表的10000name=zhangsantID10000SQLSERVERwherewhereSARGSARGAND常数或变量操

7、作符列名Name二价格50005000价格Name二and5000SARGSQLSERVERWHERESARGSARGSARG1LikeSARGnamelike%SARGnamelike%,SARG原因是通配符%在字符串的开通使得索引无法2、or会引起全表扫描Name=and价格5000SARGName=or5000 #SARGor SARGSARGNOT、!= # #!NOTEXISTSNOTINNOTLIKESARGABS()5000SQLSERVERSARGSQLSERVERWHERE价格2500/2SQLSERVEROR4、INSelect*fromtable1wheretidin(2

8、,3)Select*fromtable1wheretid=2ortid=3tidNOT6、existsin # #notexistsexistsinnotnotin SQLSERVERpubs # #SQLSERVERstatisticsI/O(1)selecttitle,pricefromtitleswheretitle_idin(selecttitle_idfromsaleswhereqty30)表sales。扫描计数18，逻辑读56次，物理读0次，预读0次。表titles。扫描计数1，逻辑读2次，物读0次，预读0次。1.(2)selecttitle，pricefromtitles2.wh

9、ereexists(select*fromsales3.wheresales.title_id=titles.title_idandqty30)sales18辑读56次，物理读0次，0titles1辑读2次，物理读0次，预读0次existsin7charindex()%LIKELIKE%charindex()LIKE1.selectgid,title,fariqi,readerfromtgongwenwherecharindex(刑侦支队，reader)0andfariqi2004-5-574715500selectgid，title，fariqi，readerfromtgongwen+%an

10、dwherereaderlike%+刑侦支队fariqi2004-5-54，逻辑读71550次，预读0 # #8unionor orwhereunionor1.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwen2.wherefariqi=2004-9-16orgid99900006814040082833921631.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-162.unionselectgid,fariqi,neibuyonghu,re

11、ader,titlefromTgongwenwheregid9990000时：9秒。扫描计数8，逻辑读67489次，2167499unionororunionorunionor1.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-16orfariqi=2004-2-5用时：6423毫秒。扫描214726171761.selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-162.union3.selectgid,f

12、ariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-2-5用时：11640毫秒。扫描814806108预读1144select*1.selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddesc用时：4673毫秒1.selecttop10000gid,fariqi,titlefromtgongwenorderbygiddesc用时：1376毫秒1.selecttop10000gid,fariqifromtgongwenorderbygiddesc8010count(

13、*)count()某些资料上说：用*会统计所有列，显然要比一个世界的列名效率低。这种说法其1.selectcount(*)fromTgongwen15001.selectcount(gid)fromTgongwen用时：1483毫秒1.selectcount(fariqi)fromTgongwen用时：3140毫秒1.selectcount(title)fromTgongwen用时：52050毫秒count(*)count()count(*)count(*)SQLSERVERcount()11orderbygidfariqi1.selecttop10000gid,fariqi,reader,t

14、itlefromtgongwen用时：196毫秒。扫描计数1，逻辑读289次，物理读1次，预读1527次。1.selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygidasc用时：4720毫秒。扫描计数1，逻辑读41956次，物理读0次，预读1287次。1.selectdesctop10000gid,fariqi,reader,titlefromtgongwenorderbygid4736毫秒。扫描计数1，逻辑读55350次，物理读10次，预读775次1.selectasctop10000gid,fariqi,reader,title

15、fromtgongwenorderbyfariqi173毫秒。扫描计数1，逻辑读290次，物理读0次，预读0次。1.selectdesctop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqi156毫秒。扫描计数1，逻辑读289次，物理读0次，预读0次。orderbyorderby12、TOPI/Oselectselecttop10*from(top10000gid,fariqi,titlefromtgongwen3.whereneibuyonghu二办公室4.orderbygiddesc)asa5.orderbygidascTOP100

16、0010I/OI/OTOPSQLSERVERTOPORACLEORACLErownumberTOP16 SQLWebSQLServer # ADO:ADO01.CREATEprocedurepagination102.（pagesizeint,-页面大小，如每页存储20条记录03.pageindexint-当前页码04.）05.as06.07.setnocounton08.09.begindeclareindextabletable(idintidentity(1,1),nidint)-定义表变量declarePageLowerBoundint-定义此页的底码declarePageUpperB

17、oundint-定义此页的顶码setPageLowerBound=(pageindex-1)*pagesizesetPageUpperBound=PageLowerBound+pagesizesetrowcountPageUpperBound16.insertintoindextable(nid)selectgidfromTGongwenwherefariqidateadd(day,-365,getdate()orderbyfariqidescselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,indextabletwhereO

18、.gid=t.nidandt.idPageLowerBoundandt.id=PageUpperBoundorderbyt.idend22.23.setnocountoffSQLSERVERCREATETABLE#TempSQLSERVERADO # m条的记录：1.从publish表中取出第n条到第2.SELECTTOPm-n+1*FROMpublishWHERE(idNOTIN(SELECTTOPn-1idFROMpublish)7.8.id为publish表的关键字ASP.NET+C#SQLSERVER01.CREATEPROCEDUREpagination202.(O3.SQLnVAR

19、CHAR(4000),不带排序语句的SQL语句04.Pageint,-页码05.RecsPerPageint,-每页容纳的记录数O6.IDVARCHAR(255),需要排序的不重复的ID号07.SortVARCHAR(255)-排序字段及规则08.)09.AS10.DECLAREStrnVARCHAR(4000)12.13.SETStr=SELECTTOP+CAST(RecsPerPageASVARCHAR(20)+*FROM(+SQL+)TWHERET.+ID+NOTIN(SELECTTOP+CAST(RecsPerPage*(1)ASVARCHAR(20)+ID+FROM(+SQL+)T9

20、ORDERBY+Sort+)ORDERBY+Sort16.PRINTStr18.EXECsp_ExecuteSqlStrGO1.SELECTTOP页大小*FROMTable1WHERE(IDNOTIN(SELECTTOP页大小*页数idFROM表ORDERBYid)3.ORDERBYIDNOTIN1.SELECTTOP页大小*2.FROMTable1WHEREnotexistsfromtable1orderbyid)b(select*from(selecttop(页大小*页数)*whereb.id=a.id)4.orderbyidnotexistsnotinTOPNOTINnotexistsS

21、QLSERVERTOPTOPTOPTOPNOTINTOPNOTINNOTINmax()min()min作或“2001.selecttop页大小*2.fromtable13.whereid4.(selectmax(id)from5.(selecttop(页码-1)*页大小)6.)7.orderbyid1000max符“idfromtable1orderbyid)asTGID(GIDgid,fariqi,title11010050010001102550页码方案1方案2方案316030761046166310010767201305005401294383100017110470250100002

22、4796450014010000038326422831553250000281401287202330500000121686127846716810010001SQLSERVERSQLWEBSQL01.CREATEPROCEDUREpagination302.tblNamevarchar(255),-表名9 11. 03.strGetFieldsvarchar(1000)04.fldNamevarchar(255)=,05.PageSizeint=10,-页尺寸06.PageIndexint=1,-页码07.doCountbit=0,-返回记录总数08.OrderTypebit=0,-设置

23、排序类型09.strWherevarchar(1500)=*,-需要返回的列-排序的字段名,非0值则返回,非0值则降序-查询条件（注意:不要加where）AS11.declarestrSQLvarchar(5000)-主语句declarestrTmpvarchar(110)-临时变量declarestrOrdervarchar(400)-排序类型15.16.ifdoCount!=017.begin18.ifstrWhere!=19.setstrSQL=selectcount(*)asTotalfrom+tblName+where+strWhere20.elsesetstrSQL=selectc

24、ount(*)asTotalfrom+tblName+enddoCountdoCount01.else2.begin3.ifOrderType!=0beginsetstrTmp=(selectmax05.setstrOrder=orderby+fldName+asc06.end07.08.ifPageIndex=109.begin10.ifstrWhere!=setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+where+strWhere+strOrderelse15.setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+strOrder1.end2.else3.begin-以下代码赋予strSQLSQL01.setstrSQL=selecttop+str(PageSize)+strGetFields+from02.+tblName+where+fldName+strTmp+(+fldName+)03.from(selecttop+str(PageIndex-1)*PageSiz