SQL语句执行效率及分析3800.docx

《SQL语句执行效率及分析3800.docx》由会员分享，可在线阅读，更多相关《SQL语句执行效率及分析3800.docx（31页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1.关于SQL查询效率，100w数据，查询只要1秒，与您分享:机器情况p4: 2.4内存: 1 Gos: windows 2003数据库: ms sql server 2000目的: 查询性能测试,比较两种查询的性能SQL查询效率 step by step- setp 1.- 建表create table t_userinfo(userid int identity(1,1) primary key nonclustered,nick varchar(50) not null default ,classid int not null default 0,writetime datetime

2、not null default getdate()go- 建索引create clustered index ix_userinfo_classid on t_userinfo(classid)go- step 2.declare i int declare k intdeclare nick varchar(10)set i = 1while i1000000beginset k = i % 10set nick = convert(varchar,i)insert into t_userinfo(nick,classid,writetime) values(nick,k,getdate(

3、)set i = i + 1end- 耗时 08:27 ，需要耐心等待- step 3.select top 20 userid,nick,classid,writetime from t_userinfo where userid not in(select top 900000 userid from t_userinfo order by userid asc)- 耗时 8 秒 ,够长的- step 4.select a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from (select top 900

4、020 userid from t_userinfo order by userid asc) a order by a.userid desc) a inner join t_userinfo b on a.userid = b.userid order by a.userid asc- 耗时 1 秒，太快了吧，不可以思议- step 5 where 查询select top 20 userid,nick,classid,writetime from t_userinfo where classid = 1 and userid not in(select top 90000 userid

5、from t_userinfo where classid = 1order by userid asc)- 耗时 2 秒- step 6 where 查询select a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from (select top 90000 userid from t_userinfowhere classid = 1order by userid asc) a order by a.userid desc) a inner join t_userinfo b on a.userid =

6、b.userid order by a.userid asc- 查询分析器显示不到 1 秒.查询效率分析：子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。如果要用子查询，那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOT IN都是最低效的。因为它对子查询中的表执行了一个全表遍历。建立合理的索引,避免扫描多余数据，避免表扫描！几百万条数据，照样几十毫秒完成查询.2. SQL提高查询效率2008-05-12 21:201.对

7、查询询进行优优化，应应尽量避避免全表表扫描，首首先应考考虑在 wheere 及 oordeer bby 涉涉及的列列上建立立索引。 2.应应尽量避避免在 wheere 子句中中对字段段进行 nulll 值值判断，否否则将导导致引擎擎放弃使使用索引引而进行行全表扫扫描，如如： sseleect id froom tt whheree nuum iis nnulll 可以以在nuum上设设置默认认值0，确确保表中中numm列没有有nulll值，然然后这样样查询： seelecct iid ffromm t wheere numm=0 3.应应尽量避避免在 wheere 子句中中使用!=或操作作符，

8、否否则将引引擎放弃弃使用索索引而进进行全表表扫描。 4.应应尽量避避免在 wheere 子句中中使用 or 来连接接条件，否否则将导导致引擎擎放弃使使用索引引而进行行全表扫扫描，如如： sseleect id froom tt whheree nuum=110 oor nnum=20 可以这这样查询询： sseleect id froom tt whheree nuum=110 uunioon aall sellectt idd frrom t wwherre nnum=20 5.iin 和和 noot iin 也也要慎用用，否则则会导致致全表扫扫描，如如： sseleect id froom

9、 tt whheree nuum iin(11,2,3) 对于连连续的数数值，能能用 bbetwweenn 就不不要用 in 了： sellectt idd frrom t wwherre nnum bettweeen 11 annd 33 6.下面的的查询也也将导致致全表扫扫描： sellectt idd frrom t wwherre nnamee liike %aabc% 若若要提高高效率，可可以考虑虑全文检检索。 7.如如果在 wheere 子句中中使用参参数，也也会导致致全表扫扫描。因因为SQQL只有有在运行行时才会会解析局局部变量量，但优优化程序序不能将将访问计计划的选选择推迟迟到

10、运行行时；它它必须在在编译时时进行选选择。然然而，如如果在编编译时建建立访问问计划，变变量的值值还是未未知的，因因而无法法作为索索引选择择的输入入项。如如下面语语句将进进行全表表扫描： seelecct iid ffromm t wheere numm=nnum 可以改改为强制制查询使使用索引引： sseleect id froom tt wiith(inddex(索引名名) wheere numm=nnum 8.应应尽量避避免在 wheere 子句中中对字段段进行表表达式操操作，这这将导致致引擎放放弃使用用索引而而进行全全表扫描描。如： seelecct iid ffromm t wheer

11、e numm/2=1000 应改改为: sellectt idd frrom t wwherre nnum=1000*2 9.应应尽量避避免在wwherre子句句中对字字段进行行函数操操作，这这将导致致引擎放放弃使用用索引而而进行全全表扫描描。如： seelecct iid ffromm t wheere subbstrringg(naame,1,33)=abcc-namme以aabc开开头的iid sseleect id froom tt whheree daateddifff(daay,ccreaateddatee,220055-111-300)=0-20005-11-30生成的的id 应

12、改为为: sseleect id froom tt whheree naame likke abcc% sellectt idd frrom t wwherre ccreaateddatee=20005-111-330 andd crreattedaate20005-12-1 10.不要在在 whheree 子句句中的“=”左边边进行函函数、算算术运算算或其他他表达式式运算，否否则系统统将可能能无法正正确使用用索引。 11.在使用用索引字字段作为为条件时时，如果果该索引引是复合合索引，那那么必须须使用到到该索引引中的第第一个字字段作为为条件时时才能保保证系统统使用该该索引，否否则该索索引将不不

13、会被使使用，并并且应尽尽可能的的让字段段顺序与与索引顺顺序相一一致。 12.不要写写一些没没有意义义的查询询，如需需要生成成一个空空表结构构： sseleect coll1,ccol22 innto #t froom tt whheree 1=0 这这类代码码不会返返回任何何结果集集，但是是会消耗耗系统资资源的，应应改成这这样： creeatee taablee #tt(.) 13.很多时时候用 exiistss 代替替 inn 是一一个好的的选择： seelecct nnum froom aa whheree nuum iin(sseleect numm frrom b) 用下面面的语句句替

14、换： seelecct nnum froom aa whheree exxistts(sseleect 1 ffromm b wheere numm=a.numm) 114.并并不是所所有索引引对查询询都有效效，SQQL是根根据表中中数据来来进行查查询优化化的，当当索引列列有大量量数据重重复时，SSQL查查询可能能不会去去利用索索引，如如一表中中有字段段sexx，maale、ffemaale几几乎各一一半，那那么即使使在seex上建建了索引引也对查查询效率率起不了了作用。 15.索引并并不是越越多越好好，索引引固然可可以提高高相应的的 seelecct 的的效率，但但同时也也降低了了 inns

15、errt 及及 uppdatte 的的效率，因因为 iinseert 或 uupdaate 时有可可能会重重建索引引，所以以怎样建建索引需需要慎重重考虑，视视具体情情况而定定。一个个表的索索引数最最好不要要超过66个，若若太多则则应考虑虑一些不不常使用用到的列列上建的的索引是是否有必必要。 16.应尽可可能的避避免更新新 cllusttereed 索索引数据据列，因因为 cclussterred 索引数数据列的的顺序就就是表记记录的物物理存储储顺序，一一旦该列列值改变变将导致致整个表表记录的的顺序的的调整，会会耗费相相当大的的资源。若若应用系系统需要要频繁更更新 cclussterred 索引

16、数数据列，那那么需要要考虑是是否应将将该索引引建为 cluusteeredd 索引引。 117.尽尽量使用用数字型型字段，若若只含数数值信息息的字段段尽量不不要设计计为字符符型，这这会降低低查询和和连接的的性能，并并会增加加存储开开销。这这是因为为引擎在在处理查查询和连连接时会会逐个比比较字符符串中每每一个字字符，而而对于数数字型而而言只需需要比较较一次就就够了。 18.尽可能能的使用用 vaarchhar/nvaarchhar 代替 chaar/nnchaar ，因因为首先先变长字字段存储储空间小小，可以以节省存存储空间间，其次次对于查查询来说说，在一一个相对对较小的的字段内内搜索效效率显然

17、然要高些些。 119.任任何地方方都不要要使用 sellectt * froom tt ，用用具体的的字段列列表代替替“*”，不不要返回回用不到到的任何何字段。 20.尽量使使用表变变量来代代替临时时表。如如果表变变量包含含大量数数据，请请注意索索引非常常有限（只只有主键键索引）。 21.避免频频繁创建建和删除除临时表表，以减减少系统统表资源源的消耗耗。 222.临临时表并并不是不不可使用用，适当当地使用用它们可可以使某某些例程程更有效效，例如如，当需需要重复复引用大大型表或或常用表表中的某某个数据据集时。但但是，对对于一次次性事件件，最好好使用导导出表。 23.在新建建临时表表时，如如果一次

18、次性插入入数据量量很大，那那么可以以使用 sellectt innto 代替 creeatee taablee，避免免造成大大量 llog ，以提提高速度度；如果果数据量量不大，为为了缓和和系统表表的资源源，应先先creeatee taablee，然后后inssertt。 224.如如果使用用到了临临时表，在在存储过过程的最最后务必必将所有有的临时时表显式式删除，先先 trrunccatee taablee ，然然后 ddropp taablee ，这这样可以以避免系系统表的的较长时时间锁定定。 225.尽尽量避免免使用游游标，因因为游标标的效率率较差，如如果游标标操作的的数据超超过1万万行，

19、那那么就应应该考虑虑改写。 26.使用基基于游标标的方法法或临时时表方法法之前，应应先寻找找基于集集的解决决方案来来解决问问题，基基于集的的方法通通常更有有效。 27.与临时时表一样样，游标标并不是是不可使使用。对对小型数数据集使使用 FFASTT_FOORWAARD 游标通通常要优优于其他他逐行处处理方法法，尤其其是在必必须引用用几个表表才能获获得所需需的数据据时。在在结果集集中包括括“合计计”的例例程通常常要比使使用游标标执行的的速度快快。如果果开发时时间允许许，基于于游标的的方法和和基于集集的方法法都可以以尝试一一下，看看哪一种种方法的的效果更更好。 28.在所有有的存储储过程和和触发器

20、器的开始始处设置置 SEET NNOCOOUNTT ONN ，在在结束时时设置 SETT NOOCOUUNT OFFF 。无无需在执执行存储储过程和和触发器器的每个个语句后后向客户户端发送送 DOONE_IN_PROOC 消消息。 29.尽量避避免大事事务操作作，提高高系统并并发能力力。 330.尽尽量避免免向客户户端返回回大数据据量，若若数据量量过大，应应该考虑虑相应需需求是否否合理1、避免将将字段设设为“允允许为空空”2、数数据表设设计要规规范3、深深入分析析数据操操作所要要对数据据库进行行的操作作4、尽尽量不要要使用临临时表55、多多多使用事事务6、尽尽量不要要使用游游标7、避避免死锁锁

21、8、要要注意读读写锁的的使用99、不要要打开大大的数据据集100、不要要使用服服务器端端游标111、在在程序编编码时使使用大数数据量的的数据库库12、不不要给“性性别”列列创建索索引133、注意意超时问问题144、不要要使用SSeleect*155、在细细节表中中插入纪纪录时，不不要在主主表执行行SellecttMAAX(IID)116、尽尽量不要要使用TTEXTT数据类类型177、使用用参数查查询188、不要要使用IInseert导导入大批批的数据据19、学学会分析析查询220、使使用参照照完整性性21、用用INNNERJOIIN和和LEFFTJJOINN代替WWherre/htttp:/n

22、/s/bloog_44b3dd79aa901100006gvv.httml提高SQLL查询效效率（要要点与技技巧）： 技巧一：问题类型：ACCCESSS数据库库字段中中含有日日文片假假名或其其它不明明字符时时查询会会提示内内存溢出出。解决方法：修改查查询语句句sql=sellectt * froom ttabllenaame wheere collumnn liike %&woord&%改为为sqll=sseleect * ffromm taableenammerrs.ffiltter = coolummn llikee %&wwordd&%=技巧二：问题类型：如何用用简易的的办法实实现类似似

23、百度的的多关键键词查询询（多关关键词用用空格或或其它符符号间隔隔）。解决方法：/用空空格分割割查询字字符串cck=sspliit(wwordd, )/得得到分割割后的数数量scck=UUBouund(ck)sql=sellectt * tabblennamee whheree在一个字段段中查询询Forr i = 00 Too scckSQQL = SQQL & teempJJoinnWorrd & ( & _collumnn liike &ck(i)&%)ttemppJoiinWoord = annd Nexxt在二个字段段中同时时查询FFor i = 0 To sckkSQLL = SQLL

24、 & temmpJooinWWordd & ( & _ccoluumn likke &cck(ii)&% or & _collumnn1 llikee &ckk(i)&%)temmpJooinWWordd = aand Neext=技巧三：大大大提高高查询效效率的几几种技巧巧1. 尽量量不要使使用 oor，使使用orr会引起起全表扫扫描，将将大大降降低查询询效率。2. 经过过实践验验证，ccharrinddex()并不不比前面面加%的的likke更能能提高查查询效率率，并且且chaarinndexx()会会使索引引失去作作用（指指sqllserrverr数据库库）3. coolummn lli

25、kee %&wwordd&% 会会使索引引不起作作用coolummn llikee &woord&% 会使使索引起起作用（去去掉前面面的%符符号）（指指sqllserrverr数据库库）4. %&wwordd&% 与与&worrd&% 在查询询时的区区别：比比如你的的字段内内容为 一个容容易受伤伤的女人人%&woord&% ：会会通配所所有字符符串，不不论查“受伤”还是查查“一个”，都会会显示结结果。&wwordd&% ：只通配配前面的的字符串串，例如如查“受伤”是没有有结果的的，只有有查“一个”，才会会显示结结果。5. 字段段提取要要按照“需多少少、提多多少”的原则则，避免免“sellect

26、t *”，尽量量使用“sellectt 字段段1,字字段2,字段33.”。实践践证明：每少提提取一个个字段，数数据的提提取速度度就会有有相应的的提升。提提升的速速度还要要看您舍舍弃的字字段的大大小来判判断。6. orrderr byy按聚集集索引列列排序效效率最高高。一个个sqllserrverr数据表表只能建建立一个个聚集索索引，一一般默认认为IDD，也可可以改为为其它的的字段。7. 为你你的表建建立适当当的索引引，建立立索引可可以使你你的查询询速度提提高几十十几百倍倍。（指指sqllserrverr数据库库） 以下是建立立索引与与不建立立索引的的一个查查询效率率分析：Sqlseerveer

27、索引引与查询询效率分分析。表 Newws字段Id：自动编编号Tiitlee：文章章标题AAuthhor：作者CConttentt：内容容Staar：优优先级AAddttimee：时间间记录：1000万条条测试机机器：PP4 22.8/1G内内存/IIDE硬硬盘=方案1：主主键Idd，默认认为聚集集索引，不不建立其其它非聚聚集索引引sellectt * froom NNewss whheree Tiitlee liike %&woord&% orr Auuthoor llikee %&wwordd&% oordeer bby IId ddescc从字段Tiitlee和Auuthoor中模模糊检索

28、索，按IId排序序查询时时间：550秒=方案2：主主键Idd，默认认为聚集集索引在在Tittle、AAuthhor、SStarr上建立立非聚集集索引sseleect * ffromm Neews wheere Tittle likke &wwordd&% oor AAuthhor likke &wwordd&% oordeer bby IId ddescc从字段Tiitlee和Auuthoor中模模糊检索索，按IId排序序查询时时间：22 - 2.55秒=方案3：主主键Idd，默认认为聚集集索引在在Tittle、AAuthhor、SStarr上建立立非聚集集索引sseleect * ffrom

29、m Neews wheere Tittle likke &wwordd&% oor AAuthhor likke &wwordd&% oordeer bby SStarr deesc从字段Tiitlee和Auuthoor中模模糊检索索，按SStarr排序查查询时间间：2 秒=方案4：主主键Idd，默认认为聚集集索引在在Tittle、AAuthhor、SStarr上建立立非聚集集索引sseleect * ffromm Neews wheere Tittle likke &wwordd&% oor AAuthhor likke &wwordd&%从字段Tiitlee和Auuthoor中模模糊检索索

30、，不排排序查询询时间：1.88 - 2 秒秒=方案5：主主键Idd，默认认为聚集集索引在在Tittle、AAuthhor、SStarr上建立立非聚集集索引sseleect * ffromm Neews wheere Tittle likke &wwordd&%或sellectt * froom NNewss whheree Auuthoor llikee &woord&%从字段Tiitlee 或 Autthorr中检索索，不排排序查询询时间：1秒 如何提高SSQL语语言的查查询效率率?问：请问我我如何才才能提高高SQLL语言的的查询效效率呢？答：这这得从头头说起： 由于于SQLL是面向向结果而

31、而不是面面向过程程的查询询语言，所所以一般般支持SSQL语语言的大大型关系系型数据据库都使使用一个个基于查查询成本本的优化化器，为为即时查查询提供供一个最最佳的执执行策略略。对于于优化器器，输入入是一条条查询语语句，输输出是一一个执行行策略。 一条SQL查询语句可以有多种执行策略，优化器将估计出全部执行方法中所需时间最少的所谓成本最低的那一种方法。所有优化都是基于用记所使用的查询语句中的where子句，优化器对where子句中的优化主要用搜索参数(Serach Argument)。 搜搜索参数数的核心心思想就就是数据据库使用用表中字字段的索索引来查查询数据据，而不不必直接接查询记记录中的的数据

32、。 带带有 =、= 等操操作符的的条件语语句可以以直接使使用索引引，如下下列是搜搜索参数数： eemp_id = 100001 或 sallaryy 30000 或或 a =1 andd c = 77 而而下列则则不是搜搜索参数数： ssalaary = eemp_sallaryy 或 depp_idd != 100 或 sallaryy * 12 = 30000 或或 a=1 oor cc=7 应应当尽可可能提供供一些冗冗余的搜搜索参数数，使优优化器有有更多的的选择余余地。请请看以下下3种方方法： 第第一种方方法： sellectt emmplooyeee.emmp_nnamee,deepa

33、rrtmeent.depp_naame froom ddepaartmmentt,emmplooyeee whheree (eemplloyeee.ddep_id = ddepaartmmentt.deep_iid) andd (ddepaartmmentt.deep_ccodee=001) annd (empployyee.depp_coode=011); 它它的搜索索分析结结果如下下： EEstiimatte 22 I/O ooperratiionss Sccan depparttmennt uusinng pprimmaryy keey ffor rowws wwherre ddep_c

34、odde eequaals 011 EEstiimatte ggetttingg heere 1 ttimees SScann emmplooyeee seequeentiiallly EEstiimatte ggetttingg heere 5 ttimees 第第二种方方法： sellectt emmplooyeee.emmp_nnamee,deeparrtmeent.depp_naame froom ddepaartmmentt,emmplooyeee whheree (eemplloyeee.ddep_id = ddepaartmmentt.deep_iid) andd (ddepaa

35、rtmmentt.deep_ccodee=001); 它它的搜索索分析结结果如下下： EEstiimatte 22 I/O ooperratiionss Sccan depparttmennt uusinng pprimmaryy keey ffor rowws wwherre ddep_codde eequaals 011 EEstiimatte ggetttingg heere 1 ttimees SScann emmplooyeee seequeentiiallly EEstiimatte ggetttingg heere 5 ttimees 第第一种方方法与第第二种运运行效率率相同，但

36、但第一种种方法最最好，因因为它为为优化器器提供了了更多的的选择机机会。 第第三种方方法： sellectt emmplooyeee.emmp_nnamee,deeparrtmeent.depp_naame froom ddepaartmmentt,emmplooyeee whheree (eemplloyeee.ddep_id = ddepaartmmentt.deep_iid) andd (eemplloyeee.ddep_codde=01); 这这种方法法最不好好，因为为它无法法使用索索引，也也就是无无法优化化使用SQLL语句时时应注意意以下几几点： 11、避免免使用不不兼容的的数据类类型

37、。例例如，FFloaat和IInteegerr，Chhar和和Varrchaar，BBinaary和和Lonng BBinaary不不兼容的的。数据据类型的的不兼容容可能使使优化器器无法执执行一些些本可以以进行的的优化操操作。例例如： sseleect empp_naame forrm eemplloyeee wwherre ssalaary 330000; 在在此语句句中若ssalaary是是Flooat类类型的，则则优化器器很难对对其进行行优化，因因为30000是是个整数数，我们们应在编编程时使使用30000.0而不不要等运运行时让让DBMMS进行行转化。 22、尽量量不要使使用表达达式，

38、因因它在编编绎时是是无法得得到的，所所以SQQL只能能使用其其平均密密度来估估计将要要命中的的记录数数。 33、避免免对搜索索参数使使用其他他的数学学操作符符。如： sseleect empp_naame froom eemplloyeee wwherre ssalaary * 112 30000; 应应改为： sseleect empp_naame froom eemplloyeee wwherre ssalaary 2500; 44、避免免使用 != 或 等等这样的的操作符符，因为为它会使使系统无无法使用用索引，而而只能直直接搜索索表中的的数据。 ORACAAL中的的应用一一个16600万

39、万数据表表短短信上行行表TBBL_SSMS_MO结结构：CCREAATE TABBLE TBLL_SMMS_MMO(SMSS_IDD NUUMBEER,MO_ID VARRCHAAR2(50),MOBBILEE VAARCHHAR22(111),SPNNUMBBER VARRCHAAR2(20),MESSSAGGE VVARCCHARR2(1150),TRAADE_CODDE VVARCCHARR2(220),LINNK_IID VVARCCHARR2(550),GATTEWAAY_IID NNUMBBER,GATTEWAAY_PPORTT NUUMBEER,MO_TIMME DDATEE D

40、EEFAUULT SYSSDATTE);CREEATEE INNDEXX IDDX_MMO_DDATEE ONN TBBL_SSMS_MO (MOO_TIIME) PCCTFRREE 10 INNITRRANSS 2 MAAXTRRANSS 2555 STTORAAGE ( IINITTIALL 1MM NEEXT 1M MMINEEXTEENTSS 1 MAXXEXTTENTTS UUNLIIMITTED PCTTINCCREAASE 0 );CREEATEE INNDEXX IDDX_MMO_MMOBIILE ON TBLL_SMMS_MMO (MOBBILEE) PCCTFRREE 10

41、 INNITRRANSS 2 MAAXTRRANSS 2555 STTORAAGE ( IINITTIALL 644K NNEXTT 1MM MIINEXXTENNTS 1 MMAXEEXTEENTSS UNNLIMMITEED PPCTIINCRREASSE 00 );问题：从表中中查询某某时间段段内某手手机发送送的短消消息，如如下SQQL语句句：SELECCT MMOBIILE,MESSSAGGE,TTRADDE_CCODEE,MOO_TIIMEFFROMM TBBL_SSMS_MOWWHERRE MMOBIILE=1330XXXXXXXXXXXANND MMO_TTIMEE BEETWE

42、EEN TO_DATTE(20006-004-001,YYYYY-MM-DD HH224:MMI:SSS) ANND TTO_DDATEE(220066-044-077,YYYYY-MMM-DDD HHH244:MII:SSS)ORDERR BYY MOO_TIIME DESSC返回结果大大约需要要10分分钟，应应用于网网页查询询，简直直难以忍忍受。分析：在PL/SSQL Devvelooperr，点击击“Expplaiin PPlann”按钮（或或F5键键），对对SQLL进行分分析，发发现缺省省使用的的索引是是IDXX_MOO_DAATE。问问题可能能出在这这里，因因为相对对于总数数量16600万万数据来来说，都都mobbilee的数据据是很少少的，如如果使用用IDXX_MOO_MOOBILLE比较较容易锁锁定数据据。如下优化