SQL查詢優(yōu)化基礎(chǔ)知識

查詢優(yōu)化是在合理利用系統(tǒng)資源和性能指標(biāo)的基礎(chǔ)上，定義最有效、最優(yōu)化的方式和技術(shù)，以提高查詢性能的過程。查詢調(diào)整的目的是找到一種方法來減少查詢的響應(yīng)時間，防止資源的過度消耗，并識別不良的查詢性能。

在查詢優(yōu)化的背景下，查詢處理通過分析查詢的執(zhí)行步驟、優(yōu)化技術(shù)和其他關(guān)于查詢的信息，確定如何更快地從SQL Server中檢索數(shù)據(jù)。

12個查詢優(yōu)化技巧以提高性能

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

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

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

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

方法2：避免使用SELECT DISTINCT

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

SELECT DISTINCT column_name FROM table_name;

方法3： 用INNER JOIN（不是WHERE）創(chuàng)建連接

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

讓我們看看如何用INNER JOIN優(yōu)化一個特定例子的SQL查詢。我們將從HumanResources.Department和HumanResources.EmployeeDepartmentHistory這兩個表中檢索數(shù)據(jù)，其中DepartmentIDs是相同的。首先，用INNER JOIN類型執(zhí)行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

當(dāng)你需要從幾個表中檢索多個列時，建議取消使用OR操作，或?qū)⒉樵兎?成小的子查詢。相反，它評估了OR操作的每個組件，而這又可能導(dǎo)致性能不佳。

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

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

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

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

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子句來執(zhí)行相同的查詢，結(jié)果集將檢索僅限于指定條件的數(shù)據(jù)，因此，避免了任何額外的掃描和步驟。

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

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

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

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

SELECT TOP命令是用來設(shè)置從數(shù)據(jù)庫中返回的記錄數(shù)的限制。為了確保你的查詢能夠輸出所需的結(jié)果，你可以使用這個命令來獲取幾條記錄作為樣本。例如，以上一節(jié)的查詢?yōu)槔诮Y(jié)果集中定義了5條記錄的限制，SSMS默認返回1000條數(shù)據(jù)，其實這是沒有必要的。

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

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

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

方法9：添加索引

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

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

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

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

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

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

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

寫入、修改、刪除或?qū)氪罅繑?shù)據(jù)可能會影響查詢性能，甚至在需要更新和操作數(shù)據(jù)、為查詢添加索引或檢查約束、處理觸發(fā)器等時，會阻塞表。此外，寫入大量的數(shù)據(jù)會增加日志文件的大小。因此，大量的寫操作可能不是一個巨大的性能問題，但你應(yīng)該意識到它們的后果，并在出現(xiàn)意外行為時做好準(zhǔn)備。

方法12：多表查詢

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

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

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