SQL语句执行效率及分析VIP专享VIP免费

第1页共16页编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第1页共16页1.关于SQL查询效率，100w数据，查询只要1秒，与您分享:机器情况p4:2.4内存:1Gos:windows2003数据库:mssqlserver2000目的:查询性能测试,比较两种查询的性能SQL查询效率stepbystep--setp1.--建表createtablet_userinfo(useridintidentity(1,1)primarykeynonclustered,nickvarchar(50)notnulldefault'',classidintnotnulldefault0,writetimedatetimenotnulldefaultgetdate())go--建索引createclusteredindexix_userinfo_classidont_userinfo(classid)go--step2.declare@iintdeclare@kintdeclare@nickvarchar(10)set@i=1while@i<1000000beginset@k=@i%10set@nick=convert(varchar,@i)insertintot_userinfo(nick,classid,writetime)values(@nick,@k,getdate())set@i=@i+1end--耗时08:27，需要耐心等待--step3.selecttop20userid,nick,classid,writetimefromt_userinfo第2页共16页第1页共16页编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第2页共16页whereuseridnotin(selecttop900000useridfromt_userinfoorderbyuseridasc)--耗时8秒,够长的--step4.selecta.userid,b.nick,b.classid,b.writetimefrom(selecttop20a.useridfrom(selecttop900020useridfromt_userinfoorderbyuseridasc)aorderbya.useriddesc)ainnerjoint_userinfobona.userid=b.useridorderbya.useridasc--耗时1秒，太快了吧，不可以思议--step5where查询selecttop20userid,nick,classid,writetimefromt_userinfowhereclassid=1anduseridnotin(selecttop90000useridfromt_userinfowhereclassid=1orderbyuseridasc)--耗时2秒--step6where查询selecta.userid,b.nick,b.classid,b.writetimefrom(selecttop20a.useridfrom(selecttop90000useridfromt_userinfowhereclassid=1orderbyuseridasc)aorderbya.useriddesc)ainnerjoint_userinfobona.userid=b.useridorderbya.useridasc--查询分析器显示不到1秒.第3页共16页第2页共16页编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第3页共16页查询效率分析：子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。如果要用子查询，那就用EXISTS替代IN、用NOTEXISTS替代NOTIN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOTIN都是最低效的。因为它对子查询中的表执行了一个全表遍历。建立合理的索引,避免扫描多余数据，避免表扫描！几百万条数据，照样几十毫秒完成查询.2.SQL提高查询效率2008-05-1221:201.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引。2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=03.应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=205.in和notin也要慎用，否则会导致全表扫描，如：selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and36.下面的查询也将导致全表扫描：selectidfromtwherenamelike'%abc%'若要提高效率，可以考虑全文检索。第4页共16页第3页共16页编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第4页共16页7.如果在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：sel...