优化重点:
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)