SQL查詢提速秘訣，防止鎖死資料庫的資料庫程式碼

由於資料庫領域仍相對不成熟，每個平台上的 SQL 開發人員都在苦苦掙扎，一次又一次犯下同樣的錯誤。當然，資料庫廠商正在取得一些進展，並且持續在竭力處理較重大的問題。

無論 SQL 開發人員在 SQL Server、Oracle、DB2、Sybase、MySQL，或是在其他任何關聯式資料庫平台上編寫程式碼，並發性、資源管理、空間管理和運作速度都仍困擾著他們。

問題的一方面是，不存在什麼靈丹妙藥；針對幾乎每條最佳實踐，我都可以舉出至少一個例外。

我們說調優資料庫既是門藝術，又是門科學，這是有道理的，因為很少有全面適用的硬性規則。你在一個系統上解決的問題在另一個系統上不是問題，反之亦然。

說到調優查詢，沒有正確的答案，但這並不意味著就此應該放棄。你可以遵循以下一些原則，有望收到很好的效果。

不要用 UPDATE 取代 CASE

這個問題很常見，卻很難發覺，許多開發人員常常忽略這個問題，原因是使用 UPDATE 再自然不過，這似乎合乎邏輯。

以這個場景為例：你把資料插入一個暫存表中，如果另一個值存在，需要它顯示某個值。

也許你從 Customer 表中提取記錄，想把訂單金額超過 100000 美元的客戶標記為「Preferred」。

因而，你將資料插入表中，執行 UPDATE 語句，針對訂單金額超過 100000 美元的任何客戶，將 CustomerRank 這一列設為「Preferred」。

問題是，UPDATE 語句記入日誌，這意味著每次寫入到表中，要寫入兩次。

解決方法：在SQL 查詢中使用內嵌CASE 語句，這檢驗每一行的訂單金額條件，並向表寫入「Preferred」標記之前，設定該標記，這樣處理效能提升幅度很驚人。

不要盲目地重複使用程式碼

這個問題也很常見，我們很容易拷貝別人寫的程式碼，因為你知道它能取得所需的資料。

問題是，它常常獲取過多你不需要的數據，而開發人員很少精簡，因此到頭來是一大堆數據。

這通常表現為 WHERE 子句中的一個額外外連接或額外條件。如果你根據自己的確切要求精簡重用的程式碼，就能大幅提升效能。

需要幾列，就提取幾列

這個問題類似第 2 個問題，但這是列所特有的。很容易用 SELECT* 來寫所有查詢程式碼，而不是把列逐一列出來。

問題同樣是，它提取過多你不需要的數據，這個錯誤我見過無數次了。開發人員對一個有 120 列、數百萬行的表執行 SELECT* 查詢，但最後只用到其中的三五列。

因此，你處理的資料比實際需要的多很多，查詢回傳結果是個奇蹟。你不僅處理過多不需要的數據，也奪走了其他流程的資源。

不要查詢兩次（double-dip）

這是我看到好多人犯的另一個錯誤：寫入預存過程，從一個有數億行的表中提取資料。

開發人員想提取住在加州，年收入高於 4 萬美元的客戶資訊。於是，他查詢住在加州的客戶，把查詢結果放到一個臨時表。

然後再來查詢年收入高於 4 萬美元的客戶，把那些結果放到另一個臨時表中。最後他連接這兩張表，獲得最終結果。

你在逗我吧？這應該用一次查詢來完成，相反你對一個超大表查詢兩次。別傻了：大表盡量只查詢一次，你會發現預存程序執行起來快多了。

一種略有不同的場景是，某個流程的幾個步驟需要大表的子集時，這導致每次都要查詢大表。

想避免這個問題，只需查詢這個子集，並將它持久化儲存到別處，然後將後面的步驟指向這個比較小的資料集。

知道何時使用臨時表

這個問題解決起來要麻煩一點，但效果顯著。在許多情況下可以使用臨時表，例如防止對大表查詢兩次。也可以使用臨時表，大幅減少連接大表所需的處理能力。

如果你必須將一個表連接到大表，該大表上又有條件，只需將大表中所需的那部分資料提取到臨時表中，然後再與該臨時表連接，就可以提升查詢效能。

如果預存程序中有幾個查詢需要對同一個表執行類似的連接，這同樣大有幫助。

預先暫存資料

這是我最愛聊的話題之一，因為這是一種常被人忽略的老方法。

如果你有一個報表或預存程序（或一組）要對大表執行類似的連接操作，透過提前連接表，並將它們持久儲存到一個表中來預暫存數據，就可以對你大有幫助。

現在，報表可以針對該預暫存表來執行，避免大連線。你並非總是可以使用這個方法，但一旦用得上，你會發現這絕對是節省伺服器資源的好方法。

請注意：許多開發人員避開這個連線問題的做法是，將注意力集中在查詢本身上，根據連線建立唯讀視圖，那樣就不必一次又一次鍵入連線條件。

但這種方法的問題是，仍要為需要它的每個報表執行查詢。如果預暫存數據，你只要執行一次連線（比如說報表前 10 分鐘），別人就可以避免大連線了。

你不知道我有多喜歡這一招，在大多數環境下，有些常用表一直被連接起來，所以沒理由不能先預暫存起來。

批量刪除和更新

這是另一個經常被忽視的技巧，如果你操作不當，刪除或更新來自大表的大量資料可能是一場噩夢。

問題是，這兩個語句都被當作單一事務來運作。如果你需要終結它們，或者它們在執行時系統遇到了問題，系統必須回滾（roll back）整個事務，這要花很長的時間。

這些操作在持續期間還會阻塞其他事務，實際上為系統帶來了瓶頸，解決辦法就是，小批量刪除或更新。

這透過幾個方法來解決問題：

無論交易因什麼原因而被終結，它只有少量的行需要回滾，那樣資料庫聯機返回快得多。

當小批量交易被提交到磁碟時，其他事務可以進來處理一些工作，從而大大提高了並發性。

同樣，許多開發人員一直固執地認為：這些刪除和更新作業必須在同一天完成。事實並非總是如此，如果你在歸檔更是如此。

如果你需要延長該操作，可以這麼做，小批量有助於實現這點；如果你花更長的時間來執行這些密集型操作，切忌拖慢系統的運行速度。

使用臨時表來提高遊標效能

如果可能的話，最好避免遊標。遊標不僅有速度問題，而速度問題本身是許多操作的一大問題，還會導致你的操作長時間阻塞其他操作，這大大降低了系統的並發性。

然而無法總是避免使用遊標，避免不了使用遊標時，可以改而對臨時表執行遊標操作，以此擺脫遊標引發的效能問題。

不妨以查閱一個表，基於一些比較結果來更新幾個欄位的遊標為例。你也許可以將該資料放入臨時表中，然後針對臨時表進行比較，而不是針對活動表進行比較。

然後你可以針對小得多，鎖定時間很短的活動表執行單一的 UPDATE 語句。

進行這樣的資料修改可大幅提高並發性。最後我要說，你根本不需要使用遊標，總是會有一個基於集合的解決方法。

使用表格值函數

這是一直以來我最愛用的技巧之一，因為它是只有專家才知道的那種秘訣。

在查詢的 SELECT 清單中使用標量函數時，該函數會因結果集中的每一行而被調用，這會大幅降低大型查詢的效能。

然而可以將標量函數轉換成表值函數，然後在查詢中使用 CROSS APPLY，就可以大幅提升效能，這個奇妙的技巧可以顯著提升效能。

不要對同一批次的許多表執行大型操作

這個似乎很明顯，但實則不然。我會用另一個鮮活的例子，因為它更能說明問題。

我有一個系統存在大量的阻塞，眾多操作處於停滯狀態。結果查明，每天執行幾次的刪除例程在刪除明確交易中 14 個表格的資料。處理一個事務中的所有 14 個表意味著，鎖定每個表，直到所有刪除完成。

解決方法就是，將每個表的刪除分解成單獨的事務，以便每個刪除事務只鎖定一個表。

這解放了其他表，緩解了阻塞，讓其他操作得以繼續運作。你總是應該把這樣的大事務分解成單獨的小事務，以防阻塞。

不要使用觸發器

這個與前一個大體一樣，但還是值得一提。觸發器的問題：無論你希望觸發器執行什麼，都會在與原始操作同一個的事務中執行。

如果你寫一個觸發器，以便更新 Orders 表中的行時將資料插入到另一個表中，會同時鎖定這兩個表，直到觸發器執行完畢。

如果你需要在更新後將資料插入到另一個表中，要將更新和插入放入到預存程序中，並在單獨的交易中執行。

如果你需要回滾，就很容易回滾，不必同時鎖定這兩個表。像往常一樣，事務要盡量短小，每次不要鎖定多個資源。

不要在 GUID 上聚集

這麼多年後，我難以相信我們居然還在為這個問題而苦惱。但我仍然每年遇到至少兩次聚類 GUID。

GUID（全域唯一識別碼）是一個 16 個位元組的隨機產生的數字。相比使用一個穩定增加的值（例如 DATE 或 IDENTITY），按此列對你表中的資料進行排序導致表碎片化快得多。

幾年前我做過基準測試，我將一堆資料插入到一個帶有聚類 GUID 的表中，將相同的資料插入到另一個帶有 IDENTITY 列的表中。

GUID 表格碎片化極為嚴重，僅過了 15 分鐘，效能就下降了數千個百分點。

5 小時後，IDENTITY 表的效能才下降了幾個百分點，這不僅適用於 GUID，它適用於任何揮發性欄位。

如果只要查看資料是否存在，就不要計數行

這種情況很常見，你需要查看資料存在於表格中，根據這番檢查的結果，你要執行某個操作。

我常常看到有人執行SELECT COUNT（*）FROMdbo.T1來檢查該資料是否存在：

1. SET @CT=(SELECT COUNT(*) FROM

2. dbo.T1); 

3. # If@CT>0  

4. BEGIN  

5.  

6. # END 

這完全沒必要，如果你想檢查資料是否存在，只要這麼做：

1. If EXISTS (SELECT 1 FROM dbo.T1)  

2. BEGIN 

4.  END 

不要計數表中的一切，只要取回你找到的第一行。 SQL Server 聰明得很，會正確使用 EXISTS，第二段程式碼回傳結果超快。

表越大，這方面的差距越明顯。在你的數據變得太大之前做正確的事情。調優資料庫永不太早。

實際上，我只是在我的其中一個生產資料庫上運行這個例子，針對一個有 2.7 億行的表。

第一次查詢用時 15 秒，包含 456197 個邏輯讀取，第二次查詢不到 1 秒就回傳結果，只包含 5 個邏輯讀取。

然而如果你確實需要計數表的行數，表又很大，另一種方法就是從系統表中提取，

SELECT rows fromsysindexes 將為你獲得所有索引的行數。

又因為聚類索引代表資料本身，所以只要加入 WHERE indid = 1，就能取得表格行，然後只要包含表格名稱即可。

所以，最後的詢問是：

1.SELECT rows from sysindexes where object_name(id)='T1'and indexid =1 

#在我2.7億行的表中，不到1 秒就回傳結果，只有6 個邏輯讀取，現在效能不一樣了。

不要進行逆向搜尋

以簡單的查詢 SELECT * FROMCustomers WHERE RegionID 3 為例。你不能將索引與該查詢結合使用，因為它是逆向搜索，需要藉助表格掃描來逐行比較。如果你需要執行這樣的任務，可能發現如果重寫查詢以使用索引，效能會好得多。

該查詢很容易重寫，就像這樣：

1.SELECT * FROM Customers WHERE RegionID

當然，沒有什麼是那麼容易的，也許效能更糟，所以使用之前先試試看。它百分之百管用，雖然涉及太多的因素。

最後，我意識到這個查詢違反了第 4 條規則：不要查詢兩次，但這也表示沒有硬性規則。雖然我們在這裡查詢兩次，但這麼做是為了避免開銷很大的表格掃描。

你無法一直運用所有這些技巧，但如果牢記它們，有一天你會用它們來解決一些大問題。

要記住的最重要一點是，別將我說的話當成教條。在你的實際環境中試一下，同樣的解決辦法不是在每種情況下都管用，不過我排查糟糕的性能時一直使用這些方法，而且屢試不爽。