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)