百万数据级快速查询优化本领[MSSQL防范]

1.对查询举行优化,应尽大概避免全表扫描,首先应考虑在 where 及 order by 触及的列上成立索引.

2.应尽大概避免在 where 子句中对字段举行 null 值判断,不然将招致引擎放弃利用索引而举行全表扫描,如:

select id from t where num is null

可以在num上设置默许值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

3.应尽大概避免在 where 子句中利用!=或<>操作符,不然将引擎放弃利用索引而举行全表扫描.

4.应尽大概避免在 where 子句中利用 or 来衔接条件,不然将招致引擎放弃利用索引而举行全表扫描,如:

select id from t where num=10 or num=20

可以这样查询:

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用,不然会招致全表扫描,如:

select id from t where num in(1,2,3)

关于持续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3

6.下面的查询也将招致全表扫描:

select id from t where name like '%abc%'

若要提高效率,可以考虑全文检索.

7.假如在 where 子句中利用参数,也会招致全表扫描.因为SQL只有在运行时才会解析部分变量,但优化程序不能将拜候筹划的挑选推迟到运行时;它必须在编译时举行挑选.但是,假如在编译时成立拜候筹划,变量的值还是未知的,因而无法作为索引挑选的输入项.以下面语句将举行全表扫描:

select id from t where num=@num

可以改成强迫查询利用索引:

select id from t with(index(索引名)) where num=@num

8.应尽大概避免在 where 子句中对字段举行表达式操作,这将招致引擎放弃利用索引而举行全表扫描.如:

select id from t where num/2=100

应改成:

select id from t where num=100*2

9.应尽大概避免在where子句中对字段举行函数操作,这将招致引擎放弃利用索引而举行全表扫描.如:

select id from t where substring(name,1,3)='abc'--name以abc开首的id

select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

应改成:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的"="左边举行函数、算术运算或其他表达式运算,不然系统将大概无法精确利用索引.

11.在利用索引字段作为条件时,假如该索引是复合索引,那么必须利用到该索引中的第一个字段作为条件时才能保证系统利用该索引,不然该索引将不会被利用,并且应尽大概的让字段次序与索引次序相一致.

12.不要写一些没有意义的查询,如需求生成一个空表构造:

select col1,col2 into #t from t where 1=0

这类代码不会返回任何后果集,但是会损耗系统资源的,应改成这样:

create table #t(...)

13.很多时刻用 exists 替换 in 是一个好的挑选:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

14.并非全部索引对查询都有效,SQL是按照表中数据来举行查询优化的,当索引列有大量数据反复时,SQL查询大概不会去操纵索引,如一表中有字段sex,male、female几近各一半,那么即便在sex上建了索引也对查询效率起不了作用.

15.索引并非越多越好,索引当然可以提高呼应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有大概会重建索引,所以怎样建索引需求慎重考虑,视具体情形而定.一个表的索引数最好不要超越6个,若太多则应考虑一些不常利用到的列上建的索引能否有必要.

16.应尽大概的避免更新 clustered 索引数据列,因为 clustered 索引数据列的次序就是表记录的物理存储次序,一旦该列值改变将招致整个表记录的次序的调整,会耗费相当大的资源.若利用系统需求频繁更新 clustered 索引数据列,那么需求考虑能否应将该索引建为 clustered 索引.

17.尽大概利用数字型字段,若只含数值信息的字段尽大概不要计划为字符型,这会降低查询和衔接的性能,并会增添存储开销.这是因为引擎在处理查询和衔接时会一一对比字符串中每一个字符,而关于数字型而言只需求对比一次就够了.

18.尽大概的利用 varchar/nvarchar 替换 char/nchar ,因为首先变长字段存储空间小,可以节俭存储空间,其次关于查询来说,在一个相对较小的字段内搜索效率明显要高些.

19.任何地方都不要利用 select * from t ,用具体的字段列表替换"*",不要返回用不到的任何字段.

20.尽大概利用表变量来替换暂时表.假如表变量包含大量数据,请注意索引非常有限（只有主键索引）.

21.避免频繁成立和删除暂时表,以削减系统表资源的损耗.

22.暂时表并非不可以利用,适本地利用它们可以使某些例程更有效,比方,当需求反复引用大型表或常用表中的某个数据集时.但是,关于一次性事件,最好利用导出表.

23.在新建暂时表时,假如一次性插入数据量很大,那么可以利用 select into 替换 create table,避免造成大量 log ,以提高速度;假如数据量不大,为了和缓系统表的资源,应先create table,然后insert.

24.假如利用到了暂时表,在存储历程的最后务必将全部的暂时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定.

25.尽大概避免利用游标,因为游标的效率较差,假如游标操作的数据超越1万行,那么就应当考虑改写.

26.利用基于游标的办法或暂时表办法之前,应先探求基于集的办理筹划来办理问题,基于集的办法普通更有效.

27.与暂时表一样,游标并非不可以利用.对小型数据集利用 FAST_FORWARD 游标普通要优于其他逐行处理办法,特别是在必须引用几个表才能得到所需的数据时.在后果集合包含"合计"的例程普通要比利用游标履行的速度快.假如开辟时间答应,基于游标的办法和基于集的办法都可以尝试一下,看哪一种办法的效果更好.

28.在全部的存储历程和触发器的开始处设置 SET NOCOUNT ON ,在完毕时设置 SET NOCOUNT OFF .无需在履行存储历程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息.

29.尽大概避免大事件操作,提高系统并发本领.

30.尽大概避免向客户端返回大数据量,若数据量过大,应当考虑呼应需求能否公道. 　　以上是“百万数据级快速查询优化本领[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：