五種提高 SQL 性能的方法
author:Johnny Papa
Data Points Archive
有時(shí)， 為了讓?xiě)贸绦蜻\行得更快，所做的全部工作就是在這里或那里做一些很小調整。啊，但關(guān)鍵在于確定如何進(jìn)行調整！遲早您會(huì )遇到這種情況：應用程序中的 SQL 查詢(xún)不能按照您想要的方式進(jìn)行響應。它要么不返回數據，要么耗費的時(shí)間長(cháng)得出奇。如果它降低了報告或您的企業(yè)應用程序的速度，用戶(hù)必須等待的時(shí)間過(guò)長(cháng)，他們就會(huì )很不滿(mǎn)意。就像您的父母不想聽(tīng)您解釋為什么在深更半夜才回來(lái)一樣，用戶(hù)也不會(huì )聽(tīng)你解釋為什么查詢(xún)耗費這么長(cháng)時(shí)間。（“對不起，媽媽?zhuān)沂褂昧颂嗟?LEFT JOIN。”）用戶(hù)希望應用程序響應迅速，他們的報告能夠在瞬間之內返回分析數據。就我自己而言，如果在 Web 上沖浪時(shí)某個(gè)頁(yè)面要耗費十多秒才能加載（好吧，五秒更實(shí)際一些），我也會(huì )很不耐煩。
為了解決這些問(wèn)題，重要的是找到問(wèn)題的根源。那么，從哪里開(kāi)始呢？根本原因通常在于數據庫設計和訪(fǎng)問(wèn)它的查詢(xún)。在本月的專(zhuān)欄中，我將講述四項技術(shù)，這些技術(shù)可用于提高基于 SQL Server? 的應用程序的性能或改善其可伸縮性。我將仔細說(shuō)明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的檢索。請記住，根本沒(méi)有神奇的解決方案。調整您的數據庫及其查詢(xún)需要占用時(shí)間、進(jìn)行分析，還需要大量的測試。這些技術(shù)都已被證明行之有效，但對您的應用程序而言，可能其中一些技術(shù)比另一些技術(shù)更適用。
本頁(yè)內容
從 INSERT 返回 IDENTITY
內嵌視圖與臨時(shí)表
避免 LEFT JOIN 和 NULL
靈活使用笛卡爾乘積
拾遺補零
從 INSERT 返回 IDENTITY
我決定從遇到許多問(wèn)題的內容入手：如何在執行 SQL INSERT 后檢索 IDENTITY 值。通常，問(wèn)題不在于如何編寫(xiě)檢索值的查詢(xún)，而在于在哪里以及何時(shí)進(jìn)行檢索。在 SQL Server 中，下面的語(yǔ)句可用于檢索由最新在活動(dòng)數據庫連接上運行的 SQL 語(yǔ)句所創(chuàng )建的 IDENTITY 值：
SELECT @@IDENTITY
這個(gè) SQL 語(yǔ)句并不復雜，但需要記住的一點(diǎn)是：如果這個(gè)最新的 SQL 語(yǔ)句不是 INSERT，或者您針對非 INSERT SQL 的其他連接運行了此 SQL，則不會(huì )獲得期望的值。您必須運行下列代碼才能檢索緊跟在 INSERT SQL 之后且位于同一連接上的 IDENTITY，如下所示：
INSERT INTO Products (ProductName) VALUES (‘Chalk‘)
SELECT @@IDENTITY
在一個(gè)連接上針對 Northwind 數據庫運行這些查詢(xún)將返回一個(gè)名稱(chēng)為 Chalk 的新產(chǎn)品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 應用程序中，可以運行以下語(yǔ)句：
Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _
(ProductName) VALUES (‘Chalk‘);SELECT @@IDENTITY")
lProductID = oRs(0)
此代碼告訴 SQL Server 不要返回查詢(xún)的行計數，然后執行 INSERT 語(yǔ)句，并返回剛剛為這個(gè)新行創(chuàng )建的 IDENTITY 值。SET NOCOUNT ON 語(yǔ)句表示返回的記錄集有一行和一列，其中包含了這個(gè)新的 IDENTITY 值。如果沒(méi)有此語(yǔ)句，則會(huì )首先返回一個(gè)空的記錄集（因為 INSERT 語(yǔ)句不返回任何數據），然后會(huì )返回第二個(gè)記錄集，第二個(gè)記錄集中包含 IDENTITY 值。這可能有些令人困惑，尤其是因為您從來(lái)就沒(méi)有希望過(guò) INSERT 會(huì )返回記錄集。之所以會(huì )發(fā)生此情況，是因為 SQL Server 看到了這個(gè)行計數（即一行受到影響）并將其解釋為表示一個(gè)記錄集。因此，真正的數據被推回到了第二個(gè)記錄集。當然您可以使用 ADO 中的 NextRecordset 方法獲取此第二個(gè)記錄集，但如果總能夠首先返回該記錄集且只返回該記錄集，則會(huì )更方便，也更有效率。
此方法雖然有效，但需要在 SQL 語(yǔ)句中額外添加一些代碼。獲得相同結果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 語(yǔ)句，并將 SELECT @@IDENTITY 語(yǔ)句放在表中的 FOR INSERT 觸發(fā)器中，如下面的代碼片段所示。這樣，任何進(jìn)入該表的 INSERT 語(yǔ)句都將自動(dòng)返回 IDENTITY 值。
CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
SELECT @@IDENTITY
GO
觸發(fā)器只在 Products 表上發(fā)生 INSERT 時(shí)啟動(dòng)，所以它總是會(huì )在成功 INSERT 之后返回一個(gè) IDENTITY。使用此技術(shù)，您可以始終以相同的方式在應用程序中檢索 IDENTITY 值。
返回頁(yè)首
內嵌視圖與臨時(shí)表
某些時(shí)候，查詢(xún)需要將數據與其他一些可能只能通過(guò)執行 GROUP BY 然后執行標準查詢(xún)才能收集的數據進(jìn)行聯(lián)接。例如，如果要查詢(xún)最新五個(gè)定單的有關(guān)信息，您首先需要知道是哪些定單。這可以使用返回定單 ID 的 SQL 查詢(xún)來(lái)檢索。此數據就會(huì )存儲在臨時(shí)表（這是一個(gè)常用技術(shù)）中，然后與 Products 表進(jìn)行聯(lián)接，以返回這些定單售出的產(chǎn)品數量：
CREATE TABLE #Temp1 (OrderID INT NOT NULL, _
OrderDate DATETIME NOT NULL)
INSERT INTO #Temp1 (OrderID, OrderDate)
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o ORDER BY o.OrderDate DESC
SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM #Temp1 t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY p.ProductName
DROP TABLE #Temp1
這些 SQL 語(yǔ)句會(huì )創(chuàng )建一個(gè)臨時(shí)表，將數據插入該表中，將其他數據與該表進(jìn)行聯(lián)接，然后除去該臨時(shí)表。這會(huì )導致此查詢(xún)進(jìn)行大量 I/O 操作，因此，可以重新編寫(xiě)查詢(xún)，使用內嵌視圖取代臨時(shí)表。內嵌視圖只是一個(gè)可以聯(lián)接到 FROM 子句中的查詢(xún)。所以，您不用在 tempdb 中的臨時(shí)表上耗費大量 I/O 和磁盤(pán)訪(fǎng)問(wèn)，而可以使用內嵌視圖得到同樣的結果：
SELECT p.ProductName,
SUM(od.Quantity) AS ProductQuantity
FROM (
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o
ORDER BY o.OrderDate DESC
) t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY
p.ProductName
ORDER BY
p.ProductName
此查詢(xún)不僅比前面的查詢(xún)效率更高，而且長(cháng)度更短。臨時(shí)表會(huì )消耗大量資源。如果只需要將數據聯(lián)接到其他查詢(xún)，則可以試試使用內嵌視圖，以節省資源。
返回頁(yè)首
避免 LEFT JOIN 和 NULL
當然，有很多時(shí)候您需要執行 LEFT JOIN 和使用 NULL 值。但是，它們并不適用于所有情況。改變 SQL 查詢(xún)的構建方式可能會(huì )產(chǎn)生將一個(gè)花幾分鐘運行的報告縮短到只花幾秒鐘這樣的天壤之別的效果。有時(shí)，必須在查詢(xún)中調整數據的形態(tài)，使之適應應用程序所要求的顯示方式。雖然 TABLE 數據類(lèi)型會(huì )減少大量占用資源的情況，但在查詢(xún)中還有許多區域可以進(jìn)行優(yōu)化。SQL 的一個(gè)有價(jià)值的常用功能是 LEFT JOIN。它可以用于檢索第一個(gè)表中的所有行、第二個(gè)表中所有匹配的行、以及第二個(gè)表中與第一個(gè)表不匹配的所有行。例如，如果希望返回每個(gè)客戶(hù)及其定單，使用 LEFT JOIN 則可以顯示有定單和沒(méi)有定單的客戶(hù)。
此工具可能會(huì )被過(guò)度使用。LEFT JOIN 消耗的資源非常之多，因為它們包含與 NULL（不存在）數據匹配的數據。在某些情況下，這是不可避免的，但是代價(jià)可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多，所以如果您可以重新編寫(xiě)查詢(xún)以使得該查詢(xún)不使用任何 LEFT JOIN，則會(huì )得到非?？捎^(guān)的回報（請參閱圖 1 中的圖）。
圖 1 查詢(xún)
加快使用 LEFT JOIN 的查詢(xún)速度的一項技術(shù)涉及創(chuàng )建一個(gè) TABLE 數據類(lèi)型，插入第一個(gè)表（LEFT JOIN 左側的表）中的所有行，然后使用第二個(gè)表中的值更新 TABLE 數據類(lèi)型。此技術(shù)是一個(gè)兩步的過(guò)程，但與標準的 LEFT JOIN 相比，可以節省大量時(shí)間。一個(gè)很好的規則是嘗試各種不同的技術(shù)并記錄每種技術(shù)所需的時(shí)間，直到獲得用于您的應用程序的執行性能最佳的查詢(xún)。
測試查詢(xún)的速度時(shí)，有必要多次運行此查詢(xún)，然后取一個(gè)平均值。因為查詢(xún)（或存儲過(guò)程）可能會(huì )存儲在 SQL Server 內存中的過(guò)程緩存中，因此第一次嘗試耗費的時(shí)間好像稍長(cháng)一些，而所有后續嘗試耗費的時(shí)間都較短。另外，運行您的查詢(xún)時(shí)，可能正在針對相同的表運行其他查詢(xún)。當其他查詢(xún)鎖定和解鎖這些表時(shí)，可能會(huì )導致您的查詢(xún)要排隊等待。例如，如果您進(jìn)行查詢(xún)時(shí)某人正在更新此表中的數據，則在更新提交時(shí)您的查詢(xún)可能需要耗費更長(cháng)時(shí)間來(lái)執行。
避免使用 LEFT JOIN 時(shí)速度降低的最簡(jiǎn)單方法是盡可能多地圍繞它們設計數據庫。例如，假設某一產(chǎn)品可能具有類(lèi)別也可能沒(méi)有類(lèi)別。如果 Products 表存儲了其類(lèi)別的 ID，而沒(méi)有用于某個(gè)特定產(chǎn)品的類(lèi)別，則您可以在字段中存儲 NULL 值。然后您必須執行 LEFT JOIN 來(lái)獲取所有產(chǎn)品及其類(lèi)別。您可以創(chuàng )建一個(gè)值為“No Category”的類(lèi)別，從而指定外鍵關(guān)系不允許 NULL 值。通過(guò)執行上述操作，現在您就可以使用 INNER JOIN 檢索所有產(chǎn)品及其類(lèi)別了。雖然這看起來(lái)好像是一個(gè)帶有多余數據的變通方法，但可能是一個(gè)很有價(jià)值的技術(shù)，因為它可以消除 SQL 批處理語(yǔ)句中消耗資源較多的 LEFT JOIN。在數據庫中全部使用此概念可以為您節省大量的處理時(shí)間。請記住，對于您的用戶(hù)而言，即使幾秒鐘的時(shí)間也非常重要，因為當您有許多用戶(hù)正在訪(fǎng)問(wèn)同一個(gè)聯(lián)機數據庫應用程序時(shí)，這幾秒鐘實(shí)際上的意義會(huì )非常重大。
返回頁(yè)首
靈活使用笛卡爾乘積
對于此技巧，我將進(jìn)行非常詳細的介紹，并提倡在某些情況下使用笛卡爾乘積。出于某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責，開(kāi)發(fā)人員通常會(huì )被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，從而無(wú)法高效使用。但是像 SQL 中的任何工具一樣，如果正確使用，它們也會(huì )很有價(jià)值。例如，如果您想運行一個(gè)返回每月數據（即使某一特定月份客戶(hù)沒(méi)有定單也要返回）的查詢(xún)，您就可以很方便地使用笛卡爾乘積。 圖 2 中的 SQL 就執行了上述操作。
雖然這看起來(lái)好像沒(méi)什么神奇的，但是請考慮一下，如果您從客戶(hù)到定單（這些定單按月份進(jìn)行分組并對銷(xiāo)售額進(jìn)行小計）進(jìn)行了標準的 INNER JOIN，則只會(huì )獲得客戶(hù)有定單的月份。因此，對于客戶(hù)未訂購任何產(chǎn)品的月份，您不會(huì )獲得 0 值。如果您想為每個(gè)客戶(hù)都繪制一個(gè)圖，以顯示每個(gè)月和該月銷(xiāo)售額，則可能希望此圖包括月銷(xiāo)售額為 0 的月份，以便直觀(guān)標識出這些月份。如果使用 圖 2 中的 SQL，數據則會(huì )跳過(guò)銷(xiāo)售額為 0 美元的月份，因為在定單表中對于零銷(xiāo)售額不會(huì )包含任何行（假設您只存儲發(fā)生的事件）。
圖 3 中的代碼雖然較長(cháng)，但是可以達到獲取所有銷(xiāo)售數據（甚至包括沒(méi)有銷(xiāo)售額的月份）的目標。首先，它會(huì )提取去年所有月份的列表，然后將它們放入第一個(gè) TABLE 數據類(lèi)型表 (@tblMonths) 中。下一步，此代碼會(huì )獲取在該時(shí)間段內有銷(xiāo)售額的所有客戶(hù)公司的名稱(chēng)列表，然后將它們放入另一個(gè) TABLE 數據類(lèi)型表 (@tblCus-tomers) 中。這兩個(gè)表存儲了創(chuàng )建結果集所必需的所有基本數據，但實(shí)際銷(xiāo)售數量除外。 第一個(gè)表中列出了所有月份（12 行），第二個(gè)表中列出了這個(gè)時(shí)間段內有銷(xiāo)售額的所有客戶(hù)（對于我是 81 個(gè)）。并非每個(gè)客戶(hù)在過(guò)去 12 個(gè)月中的每個(gè)月都購買(mǎi)了產(chǎn)品，所以，執行 INNER JOIN 或 LEFT JOIN 不會(huì )返回每個(gè)月的每個(gè)客戶(hù)。這些操作只會(huì )返回購買(mǎi)產(chǎn)品的客戶(hù)和月份。
笛卡爾乘積則可以返回所有月份的所有客戶(hù)。笛卡爾乘積基本上是將第一個(gè)表與第二個(gè)表相乘，生成一個(gè)行集合，其中包含第一個(gè)表中的行數與第二個(gè)表中的行數相乘的結果。因此，笛卡爾乘積會(huì )向表 @tblFinal 返回 972 行。最后的步驟是使用此日期范圍內每個(gè)客戶(hù)的月銷(xiāo)售額總計更新 @tblFinal 表，以及選擇最終的行集。
如果由于笛卡爾乘積占用的資源可能會(huì )很多，而不需要真正的笛卡爾乘積，則可以謹慎地使用 CROSS JOIN。例如，如果對產(chǎn)品和類(lèi)別執行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 來(lái)篩選出大多數行，那么使用 INNER JOIN 會(huì )獲得同樣的結果，而且效率高得多。如果需要為所有的可能性都返回數據（例如在您希望使用每月銷(xiāo)售日期填充一個(gè)圖表時(shí)），則笛卡爾乘積可能會(huì )非常有幫助。但是，您不應該將它們用于其他用途，因為在大多數方案中 INNER JOIN 的效率要高得多。
返回頁(yè)首
拾遺補零
這里介紹其他一些可幫助提高 SQL 查詢(xún)效率的常用技術(shù)。假設您將按區域對所有銷(xiāo)售人員進(jìn)行分組并將他們的銷(xiāo)售額進(jìn)行小計，但是您只想要那些數據庫中標記為處于活動(dòng)狀態(tài)的銷(xiāo)售人員。您可以按區域對銷(xiāo)售人員分組，并使用 HAVING 子句消除那些未處于活動(dòng)狀態(tài)的銷(xiāo)售人員，也可以在 WHERE 子句中執行此操作。在 WHERE 子句中執行此操作會(huì )減少需要分組的行數，所以比在 HAVING 子句中執行此操作效率更高。HAVING 子句中基于行的條件的篩選會(huì )強制查詢(xún)對那些在 WHERE 子句中會(huì )被去除的數據進(jìn)行分組。
另一個(gè)提高效率的技巧是使用 DISTINCT 關(guān)鍵字查找數據行的單獨報表，來(lái)代替使用 GROUP BY 子句。在這種情況下，使用 DISTINCT 關(guān)鍵字的 SQL 效率更高。請在需要計算聚合函數（SUM、COUNT、MAX 等）的情況下再使用 GROUP BY。另外，如果您的查詢(xún)總是自己返回一個(gè)唯一的行，則不要使用 DISTINCT 關(guān)鍵字。在這種情況下，DISTINCT 關(guān)鍵字只會(huì )增加系統開(kāi)銷(xiāo)。
您已經(jīng)看到了，有大量技術(shù)都可用于優(yōu)化查詢(xún)和實(shí)現特定的業(yè)務(wù)規則，技巧就是進(jìn)行一些嘗試，然后比較它們的性能。最重要的是要測試、測試、再測試。在此專(zhuān)欄的將來(lái)各期內容中，我將繼續深入講述 SQL Server 概念，包括數據庫設計、好的索引實(shí)踐以及 SQL Server 安全范例。
本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請
點(diǎn)擊舉報。
