【性能优化】续一

      之前写了一篇【性能优化】的博客，里面只是简单介绍一下sql性能优化的编写习惯与规则，本文还是讨论sql方面的优化吧。这篇文章中知识点是同事ZhaoL整理总结出来的，还是比较有实际意义，和大家分享一下。（里面牵涉到公司平台优化的内容已经去掉）

 

     

1 索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段。索引不能建得太多和太大。索引是把“双刃剑”，查询快，增删改操作就会慢，而且有可能影响其他sql. 一般的20%的代码占据了80%的资源，优化的重点是这些慢的地方。索引的使用要恰到好处，其使用原则如下：    

   ●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。    

   ●在频繁进行排序或分组（即进行group   by或order   by操作）的列上建立索引。    

   ●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。    

   ●如果待排序的列有多个，可以在这些列上建立复合索引（compound   index）。

●group   by或order   by子句中列的次序与索引的次序尽量相同

● 避免非开始的子串，如zipcode[2，3]   >“80” ；避免困难的正规表达式

2 如果是使用like进行查询的话，简单的使用索引是不行的，但是全文索引，耗空间。

3 注意使用DISTINCT，在没有必要时不要用，它同union一样会使查询变慢。没有必要时不要用ORDER BY

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

5 用select top 100 / 10 Percent 来限制用户返回的行数。(这个效果非常明显，特别对大表)

6 不要在where字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：where SUBSTRING(firstname,1,1) = ’m’改为where firstname like ’m%’（索引扫描），一定要将函数和列名分开。

7 NOT IN会多次扫描表，使用exists、NOT exists ，IN , LEFT OUTER join 来替代，特别是左连接,而exists比IN更快，最慢的是NOT操作.现在2000的优化器能够处理IS NULL，"NOT", "NOT exists", "NOT IN"，而"<>", "!=", "!>", "!<"等还是不能优化，用不到索引。

8 OR用不到索引。可以用 union来代替。关键的问题是否用到索引.

1 SELECT   ＊   FROM   CM_meterial   WHERE   (cMatCode=104   AND   IMaxPrice>100.0)   OR   cMatCode=105    

2  SELECT   ＊   FROM   CM_meterial   WHERE   (cMatCode =104   AND   IMaxPrice >100.0  

  UNION    

  SELECT   ＊   FROM   orders   WHERE   cMatCode =105

  注意这里“IMaxPrice >100.0”因为IMaxPrice是Numeric类型，数字最好写成100.0而不是100后者在数据库实际执行时会认为成整型还会做convert操作

9 Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。

select * from chineseresume where title in (’男’,’女’)

select * from chineseresume where title between ’男’ and ’女’ 是一样的。由于in会在比较多次，所以有时会慢些。

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

11 一般在GROUP BY个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。尽量在where中剔除最多条件。

12 一次更新多条记录比分多次更新每次一条快,批处理好 

13 避免相关子查询。一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

 

14 查询条件中不要包含运算

这些运算包括字符串连接（如：select * from Users where UserName + ‘pig’ = ‘张三pig’），通配符在前面的Like运算（如：select * from tb1 where col4 like ‘%aa’），使用其他用户自定义函数、系统内置函数、标量函数等等（如：select * from UserLog where datepart(dd, LogTime) = 3）。

         SQLServer在处理以上语句时，一样没办法估算开销。最终结果当然是clustered index scan或者table scan了。

15 查询条件中不要包含同一张表内不同列之间的运算

所谓的“运算”包括加减乘除或通过一些function（如：select * from tb where col1 – col2 = 1997），也包括比较运算（如：select * from tb where col1 > col2）。这种情况下，SQLServer一样没办法估算开销。不论col1、col2上都有索引还是创建了col1、col2上的覆盖索引还是创建了col1 include col2的索引。

但是这种查询有解决办法，可以在表上多创建一个计算字段，其值设置为你的“运算”结果，再在该字段上创建一个索引，就Ok了。

（结果集/总行数）被称为选择性，比值越大，选择性就越高。

16 解释计划中能看到这两个：

低效：Index Scan(索引扫描)：就全扫描索引（包括根页，中间页和叶级页） 

高效：Index Seek(索引查找)：

17 写sql应该小表（记录少）在前，大表（记录多）在后；特别是主表，明细表，一定主表在前，明细表在后。驱动表是在使用多表嵌套连接时，会先全表扫描该驱动表，然后驱动表返回的结果集中一行一行去匹配被驱动表（可以利用索引），所以我们会选择小表做为驱动表，而被驱动表使用索引进行连接。

18 合并算法（必须按相等列分别排序），hash（必须列相等连接，而不是类似left(1,3)函数相等）是有条件限制的，Nested Loops 嵌套循环适合小表连大表而且返回值不多时

19 低效
            Index Scan(索引扫描)：就全扫描索引（包括根页，中间页和叶级页）：         

  高效

     Index Seek(索引查找)：通过索引向前和向后搜索 ：

20 select  convert(char(10), st_stkrecord.ddate, 120) as ddate, order by ddate,会导致查询用不到索引，应该在order by中写成 st_stkrecord.ddate。这样才能用到索引，否则只能建立函数索引了

 

21 嵌套的子查询中根据查询条件尽可能过滤过的记录  如select *

  from st_stkrecord s inner join (select * from  where/*v中还用的查询条件尽量在这里将其过滤*/) v on(v.cguid = s.cguid)