SQL查詢優化基礎知識

 查詢優化是在合理利用系統資源和性能指標的基礎上，定義最有效、最優化的方式和技術，以提高查詢性能的過程。查詢調整的目的是找到一種方法來減少查詢的響應時間，防止資源的過度消耗，并識別不良的查詢性能。

在查詢優化的背景下，查詢處理通過分析查詢的執行步驟、優化技術和其他關于查詢的信息，確定如何更快地從SQL Server中檢索數據。

12個查詢優化技巧以提高性能

監測指標可用于評估查詢運行時間，檢測性能隱患，并顯示如何改進。例如，它們包括

• 執行計劃。一個SQL Server查詢優化器一步一步地執行查詢，掃描索引以檢索數據，并在查詢執行過程中提供詳細的指標概覽。
• 輸入/輸出統計。用于確定查詢執行過程中的邏輯和物理讀取操作的數量，幫助用戶檢測緩存/內存容量問題。
• 緩沖區緩存。用于減少服務器上的內存使用。
• 延遲。用于分析查詢或操作的持續時間。
• 索引。用來加速在SQL服務器上的閱讀操作。
• 內存優化的表。用于在內存中存儲表數據，使讀寫操作運行得更快。

方法1：用select字段，不要用select all

SELECT語句是用來從數據庫中檢索數據的。在大型數據庫的情況下，不建議檢索所有的數據，因為這將在查詢巨大的數據量時占用更多資源，并且我們在業務中能夠使用到的所有字段的情況是極其小的。

方法2：避免使用SELECT DISTINCT

SQL DISTINCT操作符用于只選擇列的唯一值，從而消除重復的值。它的語法如下。

SELECT DISTINCT column_name FROM table_name;

方法3： 用INNER JOIN（不是WHERE）創建連接

INNER JOIN語句從連接的表中返回所有匹配的記錄，而WHERE子句則根據指定的條件過濾結果的記錄。

讓我們看看如何用INNER JOIN優化一個特定例子的SQL查詢。我們將從HumanResources.Department和HumanResources.EmployeeDepartmentHistory這兩個表中檢索數據，其中DepartmentIDs是相同的。首先，用INNER JOIN類型執行SELECT語句。

SELECT
  d.DepartmentID
 ,d.Name
 ,d.GroupName
FROM HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
  ON d.DepartmentID = edh.DepartmentID

SELECT
  d.Name
 ,d.GroupName
 ,d.DepartmentID
FROM HumanResources.Department d
    ,HumanResources.EmployeeDepartmentHistory edh
WHERE d.DepartmentID = edh.DepartmentID

方法4：避免在JOIN謂詞中使用多個OR

當你需要從幾個表中檢索多個列時，建議取消使用OR操作，或將查詢分成小的子查詢。相反，它評估了OR操作的每個組件，而這又可能導致性能不佳。

方法5： 使用WHERE而不是HAVING來定義過濾器

WHERE子句用于在從表中檢索數據時或通過與多個表連接時對所選列定義過濾條件。

HAVING子句用于指定對GROUP BY子句創建的組的過濾條件。此外，HAVING在所有行被選中后對行進行過濾，并在WHERE子句之后執行。

讓我們仔細看看下面這個查詢。

SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName

FROM Person.Person p
INNER JOIN Person.EmailAddress ea
ON p.BusinessEntityID = ea.BusinessEntityID
GROUP BY p.BusinessEntityID
,p.FirstName
,p.LastName
,ea.EmailAddressID
HAVING ea.EmailAddressID BETWEEN 100 AND 200

如果我們用WHERE子句而不是HAVING子句來執行相同的查詢，結果集將檢索僅限于指定條件的數據，因此，避免了任何額外的掃描和步驟。

SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName

FROM Person.Person p
INNER JOIN Person.EmailAddress ea
ON p.BusinessEntityID = ea.BusinessEntityID
WHERE ea.EmailAddressID BETWEEN 100 AND 200
GROUP BY p.BusinessEntityID
,p.FirstName
,p.LastName
,ea.EmailAddressID

通配符作為單詞和短語的占位符，可以加在它們的開頭/結尾。為了使數據檢索更加有效和快速，你可以在SELECT語句中在短語的末尾使用通配符。例如%通配符、_占位符等。

SELECT
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.Title
FROM Person.Person p
WHERE p.FirstName LIKE 'And%';

方法7：使用TOP對查詢結果進行采樣

SELECT TOP命令是用來設置從數據庫中返回的記錄數的限制。為了確保你的查詢能夠輸出所需的結果，你可以使用這個命令來獲取幾條記錄作為樣本。例如，以上一節的查詢為例，在結果集中定義了5條記錄的限制，SSMS默認返回1000條數據，其實這是沒有必要的。

SELECT TOP 5
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.Title
FROM Person.Person p
WHERE p.FirstName LIKE 'And%';

方法8：在非高峰期運行查詢大型運算

比如我們ERP或者其他業務系統中的日報流水、月報流水匯總等，常常設計到數百萬條數據的查詢、計算、合并等，這類運算回占據大量內存和運行時間，此類運算最好放到系統資源最寬裕的時候。

方法9：添加索引

在SQL Server中，當你執行一個查詢時，優化器可以生成一個執行計劃。如果它檢測到可能被創建的缺失索引以優化性能，執行計劃會在警告部分提出這個建議。有了這個建議，它就會告知你當前的SQL應該為哪些列建立索引，以及完成后的性能可以提高多少。

讓我們運行 dbForge Studio for SQL Server 中的查詢分析器，看看它是如何工作的：

缺失的索引并不能保證一定會提升更好的性能，它只能提供一個概率。在SQL Server中，你可以使用以下動態管理視圖，這些視圖可能會幫助你深入了解基于查詢執行歷史的索引的使用情況：

• sys.dm_db_missing_index_details。提供有關建議的缺失索引的信息，空間索引除外。
• sys.dm_db_missing_index_columns: 返回有關不包含索引的表列的信息。
• sys.dm_db_missing_index_group_stats。返回有關缺失索引組的摘要信息，如查詢成本、avg_user_impact（告知你通過增加缺失索引可以提高多少性能），以及其他一些衡量有效性的指標。
• sys.dm_db_missing_index_groups: 提供包括在一個特定的索引組中的缺失索引的信息。

方法10：盡量減少對任何查詢提示的使用

當你面臨性能問題時，你可以使用查詢提示來優化查詢。它們在T-SQL語句中被指定，并使優化器根據該提示選擇執行計劃。通常，查詢提示包括NOLOCK、Optimize For和Recompile Merge/Hash/Loop。然而，你應該仔細考慮它們的用法，因為有時它們可能會引起更多意想不到的副作用、不良影響，甚至在試圖解決這個問題時破壞業務邏輯。例如，你為提示寫了額外的代碼，這些代碼在一段時間后可能不適用或過時。這意味著你應該始終監控、管理、檢查并保持提示的更新。

方法11：盡量減少大量的寫操作

寫入、修改、刪除或導入大量數據可能會影響查詢性能，甚至在需要更新和操作數據、為查詢添加索引或檢查約束、處理觸發器等時，會阻塞表。此外，寫入大量的數據會增加日志文件的大小。因此，大量的寫操作可能不是一個巨大的性能問題，但你應該意識到它們的后果，并在出現意外行為時做好準備。

方法12：多表查詢

當你向一個查詢添加多個表時，你可能會超載。此外，大量的表可能會導致執行計劃的效率低下。在生成計劃時，SQL查詢優化器需要確定這些表是如何連接的，以何種順序，如何以及何時應用過濾器和聚合。

對于SQL查詢的優化，你可以把一個查詢分成幾個獨立的查詢，這些查詢以后可以被連接起來，刪除不必要的連接、子查詢、表等。

dbForge Studio for SQL Server是SSMS以外的更好的數據庫管理工具，除了更好的開發體驗以外，還能更簡潔的分析SQL開發中的性能問題，官方下載點擊這里。