面向開發人員的 SQL 數據庫性能調優

SQL 性能調優可能是一項非常困難的任務，尤其是在處理大規模數據時，即使是最微小的變化也會對性能產生巨大的（正面或負面）影響。

在大中型公司中，大多數 SQL 性能調整將由數據庫管理員 (DBA) 處理。 但請相信我，有很多開發人員必須執行類似 DBA 的任務。 此外，在我見過的許多確實有 DBA 的公司中，他們常常難以與開發人員良好地合作——這些職位只需要不同的問題解決模式，這可能會導致同事之間的分歧。

最重要的是，公司結構也可以發揮作用。 假設 DBA 團隊和他們的所有數據庫都放在 10 樓，而開發人員在 15 樓，或者甚至在完全獨立的報告結構下的不同建築物中——在這種情況下，肯定很難順利合作。

在這篇文章中，我想完成兩件事：

1. 為開發者提供一些開發者端的 SQL 性能調優技巧。
2. 解釋開發人員和 DBA 如何有效地合作。

SQL 性能調優（在代碼庫中）：索引

如果您是數據庫新手，甚至問自己“什麼是 SQL 性能調優？”，您應該知道索引是調優 SQL 數據庫的一種有效方法，但在開發過程中經常被忽視。 基本上，索引是一種數據結構，它通過提供快速隨機查找和有序記錄的高效訪問來提高對數據庫表的數據檢索操作的速度。 這意味著一旦您創建了索引，您就可以比以前更快地選擇或排序您的行。

索引還用於定義主鍵或唯一索引，這將保證沒有其他列具有相同的值。 當然，數據庫索引是一個非常有趣的話題，我不能用這個簡短的描述來公正地說明（但這裡有更詳細的文章）。

如果您不熟悉索引，我建議您在構建查詢時使用此圖：

基本上，目標是索引主要的搜索和排序列。

請注意，如果您的表經常受到INSERT 、 UPDATE和DELETE的影響，則在編制索引時應該小心——最終可能會降低性能，因為在這些操作之後需要修改所有索引。

此外，DBA 經常在執行超過百萬行的批量插入之前刪除他們的 SQL 索引，以加快插入過程。 插入批次後，他們會重新創建索引。 但是請記住，刪除索引會影響該表中運行的每個查詢。 因此，僅在使用單個大插入時才建議使用此方法。

SQL 調優：SQL Server 中的執行計劃

順便說一句：SQL Server 中的執行計劃工具可用於創建索引。

它的主要功能是以圖形方式顯示 SQL Server 查詢優化器選擇的數據檢索方法。 如果您以前從未見過它們，這裡有詳細的演練。

要檢索執行計劃（在 SQL Server Management Studio 中），只需在運行查詢之前單擊“包括實際執行計劃”（CTRL + M）。

之後，將出現名為“執行計劃”的第三個選項卡。 您可能會看到檢測到的缺失索引。 要創建它，只需右鍵單擊執行計劃並選擇“Missing Index Details...”。 就這麼簡單！

（點擊放大）

SQL 調優：避免編碼循環

想像一個場景，其中 1000 個查詢按順序敲擊您的數據庫。 就像是：

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

你應該避免在你的代碼中出現這樣的循環。 例如，我們可以通過使用具有多行和多值的唯一INSERT或UPDATE語句來轉換上述代碼段：

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

確保您的WHERE子句在與現有值匹配時避免更新存儲的值。 這種微不足道的優化可以通過僅更新數百行而不是數千行來顯著提高 SQL 查詢性能。 例如：

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

SQL 調優：避免相關的 SQL 子查詢

相關子查詢是使用來自父查詢的值的查詢。 這種 SQL 查詢傾向於逐行運行，對於外部查詢返回的每一行執行一次，從而降低 SQL 查詢性能。 新的 SQL 開發人員經常會以這種方式構建他們的查詢，因為這通常是最簡單的方法。

這是一個相關子查詢的示例：

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

特別是，問題在於內部查詢（ SELECT CompanyName… ）針對外部查詢（ SELECT c.Name… ）返回的每一行運行。 但是為什麼要對外部查詢處理的每一行一遍又一遍地檢查Company呢？

一種更有效的 SQL 性能調優技術是將相關子查詢重構為連接：

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

在這種情況下，我們在開始時只檢查Company表一次，然後將其與Customer表連接。 從那時起，我們可以更有效地選擇我們需要的值（ co.CompanyName ）。

SQL 調優：謹慎選擇

我最喜歡的 SQL 優化技巧之一是避免SELECT * ！ 相反，您應該單獨包含您需要的特定列。 同樣，這聽起來很簡單，但我到處都能看到這個錯誤。 考慮一個包含數百列和數百萬行的表——如果您的應用程序真的只需要幾列，那麼查詢所有數據是沒有意義的。 這是對資源的巨大浪費。 （有關更多問題，請參閱此處。 ）

例如：

 SELECT * FROM Employees

對比

SELECT FirstName, City, Country FROM Employees

如果您確實需要每一列，請明確列出每一列。 這與其說是一條規則，不如說是一種防止未來系統錯誤和額外的 SQL 性能調整的方法。 例如，如果您正在使用INSERT... SELECT...並且源表已通過添加新列發生更改，您可能會遇到問題，即使目標表不需要該列，例如：

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

為了避免 SQL Server 出現這種錯誤，您應該單獨聲明每一列：

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

但是請注意，在某些情況下使用SELECT *可能是合適的。 例如，使用臨時表——這將我們引向下一個主題。

SQL 調優：臨時表的明智使用 (#Temp)

臨時表通常會增加查詢的複雜性。 如果您的代碼可以以簡單、直接的方式編寫，我建議避免使用臨時表。

但是，如果您有一個存儲過程，其中包含一些無法通過單個查詢處理的數據操作，則可以使用臨時表作為中介來幫助您生成最終結果。

當您必須連接一個大表並且該表存在條件時，您可以通過將數據傳輸到臨時表中來提高數據庫性能，然後在該表上進行連接。 您的臨時表的行數將少於原始（大）表，因此連接將更快完成！

這個決定並不總是直截了當的，但這個例子會讓你對可能想要使用臨時表的情況有所了解：

想像一個包含數百萬條記錄的客戶表。 您必須加入特定區域。 您可以通過使用SELECT INTO語句然後加入臨時表來實現此目的：

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

（注意：一些SQL開發者也避免使用SELECT INTO創建臨時表，說這個命令會鎖定tempdb數據庫，不允許其他用戶創建臨時表。幸運的是，這在7.0及更高版本中已修復。）

作為臨時表的替代方案，您可以考慮使用子查詢作為表：

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

可是等等！ 第二個查詢有問題。 如上所述，我們應該只在子查詢中包含我們需要的列（即，不使用SELECT * ）。 考慮到這一點：

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

所有這些 SQL 片段都將返回相同的數據。 但是對於臨時表，我們可以，例如，在臨時表中創建一個索引來提高性能。 關於臨時表和子查詢之間的區別，這裡有一些很好的討論。

最後，當您完成臨時表時，將其刪除以清除 tempdb 資源，而不是等待它被自動刪除（就像您與數據庫的連接終止時一樣）：

 DROP TABLE #temp

SQL 調優：“我的記錄存在嗎？”

這種 SQL 優化技術涉及EXISTS()的使用。 如果要檢查記錄是否存在，請使用EXISTS()而不是COUNT() 。 當COUNT()掃描整個表，計算所有符合條件的條目時， EXISTS()將在看到它需要的結果後立即退出。 這將為您提供更好的性能和更清晰的代碼。

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

對比

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

使用 SQL Server 2016 進行 SQL 性能調優

正如使用 SQL Server 2016 的 DBA 可能知道的那樣，該版本標誌著默認值和兼容性管理的重要轉變。 作為主要版本，它當然帶有新的查詢優化，但現在通過sys.databases.compatibility_level簡化了對是否使用它們的控制。

SQL 性能調優（在辦公室）

SQL 數據庫管理員 (DBA) 和開發人員經常在數據和非數據相關問題上發生衝突。 根據我的經驗，這裡有一些關於如何有效相處和合作的技巧（適用於雙方）。

鳴叫

開發人員的數據庫優化：

1. 如果您的應用程序突然停止工作，則可能不是數據庫問題。 例如，也許您有網絡問題。 在指責 DBA 之前先調查一下！

2. 即使您是一名忍者 SQL 數據建模師，也請讓 DBA 幫助您繪製關係圖。 他們有很多東西要分享和提供。

3. DBA 不喜歡快速變化。 這是很自然的：他們需要從整體上分析數據庫，並從各個角度檢查任何更改的影響。 列中的一個簡單更改可能需要一周時間才能實施——但這是因為一個錯誤可能會成為公司的巨大損失。 耐心點！

4. 不要要求 SQL DBA 在生產環境中進行數據更改。 如果您想訪問生產數據庫，您必須對自己的所有更改負責。

SQL Server DBA 的數據庫優化：

1. 如果您不喜歡人們向您詢問有關數據庫的問題，請給他們一個實時狀態面板。 開發人員總是對數據庫的狀態持懷疑態度，這樣的面板可以節省每個人的時間和精力。

2. 在測試/質量保證環境中幫助開發人員。 通過對真實世界數據的簡單測試，可以輕鬆模擬生產服務器。 這對於他人和您自己來說都將大大節省時間。

3. 開發人員整天都在處理業務邏輯經常變化的系統。 試著理解這個世界更加靈活，能夠在關鍵時刻打破一些規則。

4. SQL 數據庫不斷發展。 有一天，您必須將數據遷移到新版本。 開發人員依靠每個新版本的重要新功能。 不要拒絕接受他們的更改，而是提前計劃並為遷移做好準備。