SQL查询优化 索引优化【精髓吸收】

转自：http://www.cnblogs.com/zengxiangzhan/archive/2009/12/04/1617186.html

sql语句优化 

常见的简化规则如下：
 
1）不要有超过5个以上的表连接（JOIN）
2）考虑使用临时表或表变量存放中间结果。
3）少用子查询
4）视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

优化建议：
 
1）使用临时表存放t1表的结果,能大大减少logical reads（或返回行数）的操作要优先执行。

4）使用存储过程   可以考虑使用存储过程封装那些复杂的SQL语句或商业逻辑，这样做有几个好处。

一是存储过程的执行计划可以被缓存在内存中较长时间，减少了重新编译的时间。

二是存储过程减少了客户端和服务器的繁复交互。

三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。

 索引优化

创建索引的关键：

优化SQL语句的关键是尽可能减少语句的logical reads。
二、单字段索引，组合索引和覆盖索引

单字段索引是指只有一个字段的索引，而组合索引指有多个字段构成的索引。

1． 对出现在where子句中的字段加索引

查询执行计划和执行效率的语句

set statistics profile on

set statistics io on

go

select .... from tb where ...

go

set statistics profile off

set statistics io off

 

不过set statistics profile输出的是SQL 语句的运行时候真正使用的执行计划， 

而SET SHOWPLAN_ALL输出的是预计（Estimate）的执行计划。
使用SET SHOWPLAN_ALL是后面的语句并不会真正运行。
用了Table Scan，也就是对整个表进行了全表扫描。全表扫描的性能通常是很差的，要尽量避免。

问题5、什么使用聚集索引，什么时候使用非聚集索引 

在SQL Server 中索引有聚集索引和非聚集索引两种。它们的主要差别是前者的索引叶子就是数据本身，而后者的叶子节点包含的是指向数据的书签（即数据行号或聚集索引的key）。

 

如何使一个性能缓慢的系统运行更快更高效，不但需要整体分析数据库系统，找出系统的性能瓶颈，更需要优化数据库系统发出的SQL 语句。 

一旦找出关键的SQL 语句并加与优化，性能问题就会迎刃而解。

《 数据库技术内幕 》

处理百万级以上的数据提高查询速度的方法：

1.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
 2.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
 3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
     select id from t where num is null
     可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
     select id from t where num=0

 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.下面的查询也将导致全表扫描：(不能前置百分号)
     select id from t where name like ‘%abc%’
    若要提高效率，可以考虑全文检索。 6.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

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子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

 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)

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

 18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

 19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

 21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

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

 25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

 29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

 30.尽量避免大事务操作，提高系统并发能力。

16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引  

17、注意UNion和UNion   all   的区别。UNION   all好  

18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的  

19、查询时不要返回不需要的行、列  

21、用select   top   100   /   10   Percent   来限制用户返回的行数或者SET   ROWCOUNT来限制操作的行  

26、MIN()   和   MAX()能使用到合适的索引  

28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作:   方法：Create   procedure   p_insert   as   insert   into   table(Fimage)   values   (@image),   在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

29、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。   select   *   from   chineseresume   where   title   in   ('男','女')   Select   *   from   chineseresume   where   between   '男'   and   '女'   是一样的。由于in会在比较多次，所以有时会慢些。  

33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored   procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。  

34、没有必要时不要用DISTINCT和ORDER   BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION   和UNION   ALL一样的道理。 

35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数  

36、当用SELECT   INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select   INTO.   drop   table   t_lxh   begin   tran   select   *   into   t_lxh   from   chineseresume   where   name   =   'XYZ'   --commit   在另一个连接中SELECT   *   from   sysobjects可以看到   SELECT   INTO   会锁住系统表，Create   table   也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。 

38、一次更新多条记录比分多次更新每次一条快,就是说批处理好 

39、少用临时表，尽量用结果集和Table类性的变量来代替它,Table   类型的变量比临时表好  

41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。以前由于SQL   SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程  

42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快  

43、SELECT   COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别：   select   count(Field   of   null)   from   Table   和   select   count(Field   of   NOT   null)   from   Table   的返回值是不同的。  

 何时需要重建索引：

DBCC showcontig('Send')