《SQL语句执行效率及分析》由会员分享，可在线阅读，更多相关《SQL语句执行效率及分析（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 的的效率，但但同时也也降低了了 innserrt 及及 uppdatte 的的效率，因因为 iinseert 或 uupdaate 时有可可能会重重建索引引，所以以怎样建建索引需需要慎重重考虑，视视具体情情况而定定。一个个表的索索引数最最好不要要超过66个，若若太多则则应考虑虑一些不不常使用用到的列列上建的的索引是是否有必必要。 16.应尽可可能的避避免更新新 cllusttereed 索索引数据据列，因因为 cclussterred 索引数数据列的的顺序就就是表记记录的物物理存储储顺序，一一旦该列列值改变变将导致致整个表表记录的的顺序的的调整，会会耗费相相当大的的资源。若若应用系系统需要要频繁更更新 cclussterred
