《软件开发性能优化系列》之Sql性能优化(一)

1、参数化sql

        对于一般简单查询，数据库能自动参数啊以重用计划缓存，如：

SELECT * FROM table WHERE id=1;

SELECT * FROM table WHERE id=4

 

     在sqlserver内部能自动参数化这个查询，SELECT * FROM table WHERE id=@1

 

     但是一旦sql语句中带有join、union、top……等关键字，sqlserver内部将不会自动参数化。

 

     在sql2005中，通过alter database XXX set paramenterization forced的强制参数化命令能够将所有sql中的常量参数化，但是强制参数化会因为常量类型不一致造成查询结果误差。

 

2、使用查询中的索引有效

   a）、单列索引使用原则

 

     单列索引能响应大部分的简单比较，包括等价和不等价。对于like操作无前置通配符也是有效的。如：

能有效使用索引的条件语句：

[col1]=1
[col1]>100
[col1] between 0 and 100
[col1] like 'abc%'

下列条件语句不会有效利用索引:

ABS([col1])=1

[col1]+1>100 

[col1]+10 between 0 and 100 

[col1] like '%abc%' 

 

   b)、避免在WHERE字句中对字段进行函数或表达式操作

 

     看一下下面效率低下的例子和其解决方法

效率低下的写法：

SELECT     *
FROM  Production.Product
WHERE SafetystockLevel/2=100

高效写法：

SELECT     *
FROM  Production.Product
WHERE SafetystockLevel=2*100

 

     下面是SQLServer2005的优化报告

 
      类似的例子：

效率低下的写法	高效的写法
SELECT     * FROM  Production.Product WHERE SubString(name,1,3)=’abc’	SELECT     * FROM  Production.Product WHERE name like ’abc%’
SELECT     * FROM  Production.Product WHERE datediff(day ,modifiedDate,’2005-11-30’)=0	SELECT     * FROM  Production.Product WHERE modifiedDate>=’2005-11-30’ andmodifiedDate<’2005-12-1’

 

 

      任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

 

      避免使用！=或<>、is null 或is not null、 in、not in等这样的操作符，因为这会是系统无法使用索引，而只能直接搜索表中数据。

      例如：

      select id from employee where id!=’B%’

      优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。

 

   c)、多列索引使用原则

 

     则应考虑列的顺序。用于等于(=)、大于(>)、小于(<)或between搜索条件的where 字句或者参与联接的列应该放在最前面。其它列应该基于其非重要级别进行排序，就是说，从最不重复的列到最重复的列。

 

     例如：

     如果表中存在索引定义为LastName、FirstName,则该索引在搜索条件为where LastName=’Smith’或where LastName=Smith and FirstName like ’j%’时将很有用。不过，查询优化器不会将此索引用于基于FirstName(where FirstName=’Jane’)而搜索的查询。