SQL Server查询点滴

在SQL Server中，优化器根据表或者索引结构以及表中数据动态的确定两个表之间成本低的连接策略。SQL Server有Nested Loop Join、Merge Join、Hash Join三种连接策略。

Nested Loop Join：数据量较小的表被优化器选择做为outer table，数据量较大的做为inner table ,而且要求做为inner table的表必须有适当的可用索引。

Merge Join：连接的两个表在连接列上都进行了排序，而且都有可用索引。

Hash Join：连接的两个表在连接列上都没有索引，或者是被优化器选做inner table表上没有可用索引或者一个表远远小于另一个表。

Spool：作为复杂查询临时保存中间结果集的表，存在于tempdb中，此表只在查询的生命期内存在。

Seek：只能用在聚集索引或者非聚集索引上。Seek操作使用索引直接找到满足where子句的行，而不是索引中的所有行都检测一遍。如果where子句具有较高的选择性，seek的效率会更高，因为where子句已经把表中数据排除了很大一部分。

Scan：可以用在表或者索引上。和seek不同，scan将表或者索引中的所有行都与where子句进行评估，如果评估结果为true，则返回该行。表或者索引中有多少行数据，就要进行多少次检测。而seek使用索引只选择那些满足条件的行。

下面整理一些查询优化建议：

1、  避免隐式转换

隐式转换发生在为检索参数提供的参数同对应的列有着兼容的但并不完全相同的数据类型。用下面脚本测试下（来自sql server2005 性能调优）：

--CreditCardApprovalCode字段类型[varchar](15)
DECLARE @CreditCardApprovalCode_UNICODE NVARCHAR(30)
SET @CreditCardApprovalCode_UNICODE = N'539435Vi62867'

SELECT CreditCardApprovalCode FROM Sales.SalesOrderHeader
WHERE CreditCardApprovalCode = @CreditCardApprovalCode_UNICODE --发生数据转换 varchar要转换为nvarchar
GO
DECLARE @CreditCardApprovalCode VARCHAR(30)
SET @CreditCardApprovalCode = '539435Vi62867'

SELECT CreditCardApprovalCode FROM Sales.SalesOrderHeader
WHERE CreditCardApprovalCode = @CreditCardApprovalCode --无数据转换

有转换的语句成本：

表'SalesOrderHeader'。扫描计数1，逻辑读取704 次

CPU 时间= 15 毫秒，占用时间= 15 毫秒。

执行计划：

无转换的语句成本：

表'SalesOrderHeader'。扫描计数1，逻辑读取704 次，

CPU 时间= 15 毫秒，占用时间= 13 毫秒。

执行计划：

2、  避免WHERE子句列上的函数

Where子句的列上使用函数可以阻止优化器使用该列上的索引（一般情况应该避免Where子句的列上使用函数，不过最好是根据实际数据测试后做决定）。运行如下脚本：

select d.Name
from HumanResources.Department as d
where SUBSTRING(d.name,1,1)='F'

select d.Name
from HumanResources.Department as d
where d.name like 'F%'

执行计划：

这个脚本我测试的时候statistic io 和times输出一样，具体哪个性能好还要针对具体数据进行测试。不过，一般情况下还是要避免在WHERE子句列上使用函数。

日期比较，在做程序开发中经常会用到类似下面的查询。

先建一测试索引

CREATE NONCLUSTERED INDEX [ix_test] ON [Sales].[SalesOrderHeader] 
(
    [OrderDate] ASC
)
select soh.SalesOrderID,soh.OrderDate
from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID=sod.SalesOrderID
where DATEPART(yy,soh.OrderDate)=2002
and DATEPART(mm,soh.OrderDate)=4

执行成本：

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次

表'SalesOrderDetail'。扫描计数1，逻辑读取228 次，物理读取0 次

表'SalesOrderHeader'。扫描计数1，逻辑读取61 次，物理读取2 次

CPU 时间= 16 毫秒，占用时间= 198 毫秒。

执行计划：

上面形式的查询阻止优化器使用索引，可以把上面的查询改成如下形式：

select soh.SalesOrderID,soh.OrderDate
from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID=sod.SalesOrderID
where soh.OrderDate>='2002-04-01'
and soh.OrderDate<'2002-05-01'

执行成本：

表'SalesOrderDetail'。扫描计数244，逻辑读取814 次，物理读取0 次

表'SalesOrderHeader'。扫描计数1，逻辑读取3 次，物理读取0 次

CPU 时间= 0 毫秒，占用时间= 101 毫秒。

执行计划：

3、  使用exists代替count(*)验证数据存在

Count(*)必须扫描表中的所有行。Exists只需扫描到第一个匹配条件的记录即停止扫描。

4、  使用set nocount

在批或者存储过程中的每个查询执行之后，服务器报告所影响的行数，如

<number> row(s) affected

这个信息返回给应用程序增加网络开销，可以用以下方法避免这个开销

Set nocount on

<sql queries>

Set nocount off

5、  减少索开销

默认情况下，select,insert,update,delete都使用数据库锁而且是行级锁。对于大量行的查询，在所有单独的行上请求行锁为锁管理进程增加了很大的开销。可以减少锁的粒度来减少锁开销。

在WEB应用程序中，最好用以下形式进行查询

Select * from <tablename> with(nolock)