SQLSERVER查詢效能最佳化

文章目錄

• 建立高選擇性索引
• 建立多列索引
• 避免對小表建立索引
• 與篩選子句一起使用索引
• 對 ORDER-BY/GROUP-BY/DISTINCT 列建立索引以縮短回應時間
• 使用 Outer JOIN 進行限制

最佳化索引

建立有用的索引是提高查詢效能的最重要方法之一。有用的索引可協助減少在尋找資料時所需使用的磁碟 I/O 運算元和系統資源量。

若要建立有用的索引，您必須瞭解下列知識：資料的使用方式，查詢的類型及其運行頻率，以及查詢處理器如何使用索引快速尋找資料。

當選擇要建立何種索引時，請檢查您的關鍵查詢，其效能將對使用者體驗產生極大的影響。需要建立專門輔助這些查詢的索引。在添加索引後，重新執行查詢以查看是否提高了效能。如果未提高，則刪除索引。

與 大多數效能最佳化方法一樣，此方法也有一些缺點。例如，使用較多索引時，SELECT 查詢的運行速度很可能會更快。但是，DML（INSERT、UPDATE 和 DELETE）操作的速度將顯著減慢，因為對於每個操作都必須維護更多的索引。因此，如果您的查詢主要包含 SELECT 語句，則使用較多的索引是非常有協助的。如果您的應用程式需要執行許多 DML 操作，就有必要控制建立的索引數。

SQL Server Compact Edition 包括對執行程序表的支援，執行程序表可以協助評估和最佳化查詢。除了 SQL Server Compact Edition 使用的只是一部分運算子之外，SQL Server Compact Edition 使用與 SQL Server 2005 相同的執行程序表架構。有關詳細資料，請參閱位於 http://schemas.microsoft.com/sqlserver/2004/07/showplan/ 的 Microsoft 執行程序表架構。

下面幾節介紹有關建立有用索引的其他資訊。

建立高選擇性索引

對 關鍵查詢的 WHERE 子句中使用的列建立索引，通常會提高效能。但是，這取決於索引選擇性的高低。選擇性是指合格行數與總行數之比。如果比率較低，索引就是高選擇性的。 它可以篩選掉大多數行，從而大大減小結果集的大小。因此，這樣的索引就是要建立的有用索引。與之相比，選擇性低的索引就沒有那麼有用。

唯一索引具有最高的選擇性。只有一行可以匹配，這對只希望返回一行的查詢是最有用的。例如，唯一 ID 列的索引可協助您快速尋找特定的行。

通過對 SQL Server Compact Edition 表運行 sp_show_statistics 預存程序，您可以評估索引的選擇性。例如，若要評估兩個列（“Customer ID”和“Ship Via”）的選擇性，可以運行下列預存程序：

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI               0            7                   0

ANATR               0            4                   0

ANTON               0           13                   0

AROUT               0           14                   0

BERGS               0           23                   0

BLAUS               0            8                   0

BLONP               0           14                   0

BOLID               0            7                   0

BONAP               0           19                   0

BOTTM               0           20                   0

BSBEV               0           12                   0

CACTU               0            6                   0

CENTC               0            3                   0

CHOPS               0           12                   0

COMMI               0            5                   0

CONSH               0            4                   0

DRACD               0            9                   0

DUMON               0            8                   0

EASTC               0           13                   0

ERNSH               0           33                   0

(90 rows affected)

和

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1               0            320                   0

2               0            425                   0

3               0            333                   0

(3 rows affected)

結果表明“Customer ID”列的重複程度更低。這意味著其索引的選擇性將比“Ship Via”列的索引的選擇性要高。

有關使用這些預存程序的詳細資料，請參閱 sp_show_statistics (SQL Server Compact Edition)、sp_show_statistics_steps (SQL Server Compact Edition) 和 sp_show_statistics_columns (SQL Server Compact Edition)。

建立多列索引

多列索引是單列索引的自然擴充。對於計算與預先指定的一組鍵列匹配的篩選條件運算式，多列索引是非常有用的。例如，複合式索引 CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 有助於計算下列查詢：

• ... WHERE "Last Name" = 'Doe'
• ... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
• ... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

但是，它對下面的查詢是沒有用的：

• ... WHERE "First Name" = 'John'

在建立多列查詢時，應該將選擇性最高的列放在鍵中的最左端。這樣，在匹配多個運算式時，就可以使索引的選擇性更高。

避免對小表建立索引

小表是指內容只適合一個或幾個資料頁的表。因為進行表掃描通常效率更高，所以要避免對非常小的表建立索引。這會節省載入和處理索引頁的開銷。不對非常小的表建立索引，可以避免最佳化器選擇此類索引。

SQL Server Compact Edition 按 4 Kb 一頁來儲存資料。使用以下公式可以計算出近似的頁數，儘管實際的頁數由於儲存引擎開銷的原因可能會稍微多一些。

<列的總大小（位元組）> * <行數>

<頁數> = -----------------------------------------------------------------

4096

例如，假設一個表的架構如下：

列名	類型（大小）
訂單 ID	INTEGER（4 位元組）
產品識別碼	INTEGER（4 位元組）
單價	MONEY（8 位元組）
數量	SMALLINT（2 位元組）
折扣	REAL（4 位元組）

此表包含 2820 行。按照公式，儲存該表資料需要大約 16 頁：

<頁數> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15.15 頁

選擇要建立索引的對象

建議始終對主鍵建立索引。另外，對外鍵建立索引通常也非常有用。這是因為通常使用主鍵和外鍵來聯結表。建立這些鍵的索引後，最佳化器可以使用效率更高的索引聯結演算法。如果您的查詢使用其他列來聯結表，由於相同的原因，對這些列建立索引通常也非常有用。

在建立主鍵和外鍵的約束後，SQL Server Compact Edition 將自動為其建立索引並在最佳化查詢時加以利用。請記住使用較小的主鍵和外鍵。這樣，聯結的運行速度會更快。

與篩選子句一起使用索引

索引可以用於提高某些類型的篩選子句的計算速度。雖然所有篩選子句都會減小查詢的最終結果集，但是某些篩選子句還可以協助減小所需掃描的資料量。

搜尋參數 (SARG) 可指定精確匹配、值的範圍或由 AND 聯結的兩項或多項的串連，因此能夠限制搜尋範圍。搜尋參數採用以下格式之一：

• 列 運算子 <常量或變數>

• <常量或變數> 運算子 列

SARG 運算子包括 =、>、<、>=、<=、IN、BETWEEN，有時還包括 LIKE（在進行首碼匹配時，如 LIKE 'John%'）。SARG 可以包括由 AND 聯結的多個條件。SARG 還可以是匹配特定值的查詢，例如：

• "Customer ID" = 'ANTON'
• 'Doe' = "Last Name"

SARG 也可以是匹配一定範圍的值的查詢，例如：

• "Order Date" > '1/1/2002'
• "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
• "Customer ID" IN ('ANTON', 'AROUT')

不 使用 SARG 運算子的運算式無法提高效能，因為 SQL Server Compact Edition 查詢處理器必須評估所有行，以確定是否滿足篩選子句。因此，對於不使用 SARG 運算子的運算式，索引是沒有用的。非 SARG 運算子包括 NOT、<>、NOT EXISTS、NOT IN、NOT LIKE 和內建函式。

使用查詢最佳化工具

在 確定基表的存取方法時，SQL Server Compact Edition 最佳化器將確定是否存在 SARG 子句的索引。如果存在索引，最佳化器將通過計算返回多少行來評估索引。然後，最佳化器會估計通過使用索引尋找合格行的開銷。如果使用索引的開銷比表掃描 的開銷低，最佳化器將選擇索引訪問。如果在 SARG 中使用索引的第一列或預先指定的一組列，且 SARG 指定了限制搜尋範圍的下限、上限或同時執行了這兩者，則索引很可能是有用的。

瞭解回應時間與總時間

響 應時間是查詢返回第一條記錄所用的時間。總時間是查詢返回所有記錄所用的時間。對於互動式應用程式，回應時間是很重要的，因為這決定了使用者要等待多長時 間，才能通過看到第一條返回記錄確定查詢正在處理中。對於批處理應用程式，總時間反映了總體輸送量。您必須確定應用程式和查詢的效能標準，然後才能相應地 進行設計。

例如，假設查詢返回 100 條記錄，前五條記錄用來填充一個列表。在此情況下，您不關心返回全部 100 條記錄所用的時間。相反，您只希望查詢快速返回前幾條記錄，以便填充列表。

可 以執行許多查詢操作，而無需儲存中間結果。這些操作稱為管道操作。管道操作的樣本是映射、選擇和聯結。通過這些操作執行的查詢可以立即返回結果。其他動作 （如 SORT 和 GROUP-BY）需要使用所有輸入資訊，才可以將結果返回到父操作。這就是我們所說的要求具體化的操作。由於具體化的原因，通過這些操作執行的查詢通常 會有初始延遲。在此初始延遲之後，此類查詢通常也可以很快地返回記錄。

具有回應時間要求的查詢應避免具體化。例如，與使用排序相比，使用索引執行 ORDER-BY 的回應時間更短。下一節將對此進行詳細說明。

對 ORDER-BY/GROUP-BY/DISTINCT 列建立索引以縮短回應時間

ORDER- BY、GROUP-BY 和 DISTINCT 操作都是排序類型的操作。SQL Server Compact Edition 查詢處理器使用兩種方法進行排序。如果記錄已經按索引進行排序，則處理器只使用索引。否則，處理器必須先使用臨時工作表對記錄進行排序。在 CPU 較慢並且記憶體較低的裝置上，這樣的預先排序會造成顯著的初始延遲，因此，在回應時間比較重要時，應該避免預先排序。

在使用多列索引的情況下，為了使 ORDER-BY 或 GROUP-BY 處理特定索引，ORDER-BY 或 GROUP-BY 列必須與預先指定的一組索引列匹配，而且順序要完全相同。例如，索引 CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 有助於最佳化下列查詢：

• ... ORDER BY / GROUP BY "Last Name" ...
• ... ORDER BY / GROUP BY "Last Name", "First Name" ...

但對下列查詢，它並沒有協助：

• ... ORDER BY / GROUP BY "First Name" ...
• ... ORDER BY / GROUP BY "First Name", "Last Name" ...

為了使 DISTINCT 操作處理多列索引，映射列表必須與所有索引列匹配，儘管順序不必完全相同。上面的索引有助於最佳化下列查詢：

• ... DISTINCT "Last Name", "First Name" ...
• ... DISTINCT "First Name", "Last Name" ...

但對下列查詢，它並沒有協助：

• ... DISTINCT "First Name" ...
• ... DISTINCT "Last Name" ...

注意：
如果您的查詢始終返回唯一行，請不要指定 DISTINCT 關鍵字，因為它只會增加開銷。

重寫子查詢以使用 JOIN

有 時可以重寫子查詢以使用 JOIN，從而提高效能。建立 JOIN 的好處是，可以按與查詢所定義順序不同的順序評估表。使用子查詢的好處是，通常不必掃描子查詢中的所有行就可以計運算元查詢運算式。例如，EXISTS 子查詢可以在找到第一個合格行時就返回 TRUE。

注意：
SQL Server Compact Edition 查詢處理器始終會重寫 IN 子查詢以使用 JOIN。對包含 IN 子查詢子句的查詢，不必使用此方法。

例如，若要確定包含至少一個折扣率大於或等於 25% 的項的所有訂單，可以使用下面的 EXISTS 子查詢：

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

您也可以使用 JOIN 對其進行重寫：

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

使用 Outer JOIN 進行限制

OUTER JOIN 與 INNER JOIN 的處理方式是不同的：對於 INNER JOIN 表，最佳化器會嘗試重新排列聯結順序，而對於 OUTER JOIN 表則不會。外部表格（LEFT OUTER JOIN 中的左表和 RIGHT OUTER JOIN 中的右表）將首先訪問，然後才會訪問內部表。這一固定的聯結順序可能會導致執行計畫不能達到最優。

注意：
SQL Server Compact Edition 查詢處理器假定 INNER JOIN 包括的列上有索引（由使用者或資料庫建立）。

使用參數化查詢

如果應用程式啟動並執行一系列查詢僅有一些常量是不同的，則可以通過使用參數化查詢來提高效能。例如，若要按不同的客戶返回訂單，可以運行下面的查詢：

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

通 過只編譯一次查詢然後多次執行編譯好的計劃，參數化查詢可以提供更好的效能。從編程上講，必須始終保持包含緩衝查詢計劃的命令對象。如果破壞以前的命令對 象再建立新的命令對象，會破壞緩衝的計劃。這要求重新編譯查詢。如果必須交替運行多個參數化查詢，則可以建立幾個命令對象，每個命令對象都包含一個參數化 查詢的緩衝執行計畫。這樣，就可以有效避免重新編譯所有這些查詢了。

僅在必要時進行查詢

SQL Server Compact Edition 查詢處理器是查詢關聯式資料庫中儲存的資料的強大工具。但是，任何查詢處理器都會造成一定程度的內部開銷。在開始真正地執行計畫之前，查詢處理器必須編譯、 最佳化和產生執行計畫。對於很快完成的簡單查詢，尤其是這樣。因此，您自己執行查詢有時可以大大提高效能。如果每一毫秒對您的關鍵組件都很重要，建議您考慮 自己執行簡單查詢這一替代方法。對於複雜的大型查詢，這種工作最好還是留給查詢處理器去做。

例如，假設您希望尋找按訂單 ID 排列的一系列訂單的客戶 ID。有兩種方法可以實現此操作。第一種方法是對於每一次尋找都執行下列步驟：

1. 開啟 Orders 基表
2. 使用特定的“Order ID”尋找行
3. 檢索“Customer ID”

或者，對於每一次尋找都發出以下查詢：

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

與 手動解決方案相比，基於查詢的解決方案更簡單但速度更慢，因為 SQL Server Compact Edition 查詢處理器需要將 SQL 聲明語句轉換為與手動執行時相同的三個操作。隨後，這三個步驟將按順序執行。選用哪種方法將取決於在應用程式中簡易性和效能哪方面更重要。