知識星球來自：51CTO技術棧（微訊號：blog51cto）
除非你遵循本文介紹的這些技巧，否則很容易編寫出減慢查詢速度或鎖死資料庫的資料庫程式碼。
由於資料庫領域仍相對不成熟，每個平臺上的 SQL 開發人員都在苦苦掙扎，一次又一次犯同樣的錯誤。
當然，資料庫廠商在取得一些進展，並繼續在竭力處理較重大的問題。
無論 SQL 開發人員在 SQL Server、Oracle、DB2、Sybase、MySQL，還是在其他任何關係資料庫平臺上編寫程式碼，併發性、資源管理、空間管理和執行速度都仍困擾著他們。
問題的一方面是，不存在什麼靈丹妙藥；針對幾乎每條最佳實踐，我都可以舉出至少一個例外。
通常，開發人員找到自己青睞的方法，而懶得研究其他方法。這也許是缺乏教育的表現，或者開發人員沒有認識到自己何時做錯了。也許針對一組本地測試資料，查詢執行起來順暢，但是換成生產級系統，表現就差強人意。
我沒有期望 SQL 開發人員成為管理員，但他們在編寫程式碼時必須考慮到生產級環境的問題。如果他們在開發初期不這麼做，資料庫管理員後期會讓他們返工，遭殃的就是使用者。
我們說調優資料庫既是門藝術，又是門科學，這是有道理的，因為很少有全面適用的硬性規則。你在一個系統上解決的問題在另一個系統上不是問題，反之亦然。
說到調優查詢，沒有正確的答案，但這並不意味著就此應該放棄。你可以遵循以下17條原則，有望收到很好的效果。
不要用 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 陳述句。
進行這樣的資料修改可大大提高併發性。最後我要說，你根本不需要使用遊標，總是會有一種基於集合的解決方法。
不要巢狀檢視
檢視也許很方便，不過使用檢視時要小心。
雖然檢視有助於將龐大查詢遮掩起來、無須使用者操心，並實現資料訪問標準化，但你很容易發現自己陷入這種困境：檢視 A 呼叫檢視 B，檢視 B 呼叫檢視 C，檢視 C 又呼叫檢視 D，這就是所謂的巢狀檢視。
這會導致嚴重的效能問題，尤其是這兩方面：
-
傳回的資料很有可能比你需要的多得多。
-
查詢最佳化器將放棄並傳回一個糟糕的查詢方案。
我遇到過喜歡巢狀檢視的客戶，這個客戶有一個檢視用於幾乎所有資料，因為它有兩個重要的連線。
問題是，檢視傳回的一個列裡面居然有 2MB 大小的檔案，有些檔案甚至更大。
在執行的幾乎每一次查詢中，這個客戶要在網路上為每一行至少多推送 2MB 的資料。自然，查詢效能糟糕透頂。
沒有一個查詢實際使用該列！當然，該列被埋在七個檢視的深處，要找出來都很難。我從檢視中刪除該檔案列後，最大查詢的時間從 2.5 小時縮短至 10 分鐘。
我最後層層解開了巢狀檢視（有幾個不必要的連線和列），並寫了一個普通的查詢，結果同樣這個查詢的時間縮短至不到 1 秒。
使用表值函式
這是一直以來我最愛用的技巧之一，因為它是隻有專家才知道的那種秘訣。
在查詢的 SELECT 串列中使用標量函式時，該函式因結果集中的每一行而被呼叫，這會大幅降低大型查詢的效能。
然而可以將標量函式轉換成表值函式，然後在查詢中使用 CROSS APPLY，就可以大幅提升效能，這個奇妙的技巧可以顯著提升效能。
使用分割槽避免移動大型資料
不是每個人都能利用依賴 SQL Server Enterprise 中分割槽的這個技巧，但是對於能利用它的人來說，這個技巧很棒。
大多數人沒有意識到 SQL Server 中的所有表都是分割槽的。如果你喜歡，可以把一個表分成多個分割槽，但即使簡單的表也從建立那一刻起就分割槽了。
然而，它們是作為單個分割槽建立的。如果你在執行 SQL Server Enterprise，已經可以隨時享用分割槽表的優點了。
這意味著你可以使用 SWITCH 之類的分割槽功能，歸檔來自倉庫載入的大量資料。
舉個實際例子，去年我碰到過這樣一個客戶：該客戶需要將資料從當日的表複製到歸檔表中；那樣萬一載入失敗，公司可以迅速用當日的表來恢復。
由於各種原因，無法每次將表的名稱改來改去，所以公司每天在載入前將資料插入到歸檔表中，然後從活動表刪除當日的資料。
這個過程一開始很順利，但一年後，複製每個表要花 1 個半小時，每天要複製幾個表，問題只會越來越糟。
解決辦法是拋棄 INSERT 和 DELETE 行程，使用 SWITCH 命令。
SWITCH 命令讓該公司得以避免所有寫入，因為它將頁面分配給了歸檔表。
這隻是更改了元資料，SWITCH 執行平均只要兩三秒鐘，如果當前載入失敗，你可以透過 SWTICH 將資料切換回到原始表。
如果你非要用 ORM，請使用儲存過程
ORM 是我經常炮轟的物件之一。簡而言之，別使用 ORM（物件關係對映器）。
ORM 會生成世界上最糟糕的程式碼，我遇到的幾乎每個效能問題都是由它引起的。
相比知道自己在做什麼的人，ORM 程式碼生成器不可能寫出一樣好的 SQL。但是如果你使用 ORM，那就編寫自己的儲存過程，讓 ORM 呼叫儲存過程，而不是寫自己的查詢。
我知道使用 ORM 的種種理由，也知道開發人員和經理都喜歡 ORM，因為它們有助於產品迅速投向市場。但是如果你看一下查詢對資料庫做了什麼，就會發現代價太高了。
儲存過程有許多優點，首先，你在網路上推送的資料少得多。如果有一個長查詢，那麼它可能在網路上要往返三四趟才能讓整個查詢到達資料庫伺服器。
這不包括伺服器將查詢重新組合起來並執行所花的時間；另外考慮這點：查詢可能每秒執行幾次或幾百次。
使用儲存過程可大大減少傳輸的流量，因為儲存過程呼叫總是短得多。另外，儲存過程在 Profiler 或其他任何工具中更容易追蹤。
儲存過程是資料庫中的實際物件，這意味著相比臨時查詢（ad-hoc query），獲取儲存過程的效能統計數字要容易得多，因而發現效能問題、查明異常情況也要容易得多。
此外，儲存過程引數化更一致，這意味著你更可能會重用執行方案，甚至處理快取問題，要查明臨時查詢的快取問題很難。
有了儲存過程，處理邊界情況（edge case），甚至增加審計或變更鎖定行為變得容易多了。儲存過程可以處理困擾臨時查詢的許多工。
幾年前，我妻子理清了 Entity Framework 的一個兩頁長的查詢，該查詢花了 25 分鐘來執行。
她化繁為簡，將這個大型查詢改寫為 SELECT COUNT(*) fromT1，這不是開玩笑。
那些只是要點，我知道，許多 .NET 程式員認為業務邏輯不適宜放在資料庫中，這大錯特錯。
如果將業務邏輯放在應用程式的前端，僅僅為了比較就得將所有資料傳送一遍，那樣不會有好的效能。
我有個客戶將所有邏輯儲存在資料庫的外面，在前端處理一切。該公司將成千上萬行資料傳送到前端，以便能夠運用業務邏輯，並顯示所需的資料。
這個過程花了 40 分鐘，我把儲存過程放在後端，讓它從前端呼叫；頁面在三秒鐘內載入完畢。
當然，有時邏輯適宜放在前端上，有時適宜放在資料庫中，但是 ORM 總是讓我上火。
不要對同一批次的許多表執行大型操作
這個似乎很明顯，但實則不然。我會用另一個鮮活的例子，因為它更能說明問題。
我有一個系統存在大量的阻塞，眾多操作處於停滯狀態。結果查明，每天執行幾次的刪除例程在刪除顯式事務中 14 個表的資料。處理一個事務中的所有 14 個表意味著，鎖定每個表，直到所有刪除完成。
解決辦法就是，將每個表的刪除分解成單獨的事務，以便每個刪除事務只鎖定一個表。
這解放了其他表，緩解了阻塞，讓其他操作得以繼續執行。你總是應該把這樣的大事務分解成單獨的小事務，以防阻塞。
不要使用觸發器
這個與前一個大體一樣，但還是值得一提。觸發器的問題：無論你希望觸發器執行什麼，都會在與原始操作同一個的事務中執行。
如果你寫一個觸發器，以便更新 Orders 表中的行時將資料插入到另一個表中，會同時鎖定這兩個表，直到觸發器執行完畢。
如果你需要在更新後將資料插入到另一個表中，要將更新和插入放入到儲存過程中，併在單獨的事務中執行。
如果你需要回滾，就很容易回滾，不必同時鎖定這兩個表。與往常一樣，事務要儘量短小，每次不要鎖定多個資源。
不要在 GUID 上聚類
這麼多年後，我難以相信我們居然還在為這個問題而苦惱。但我仍然每年遇到至少兩次聚類 GUID。
GUID（全域性唯一識別符號）是一個 16 位元組的隨機生成的數字。相比使用一個穩定增加的值（比如 DATE 或 IDENTITY），按此列對你表中的資料進行排序導致表碎片化快得多。
幾年前我做過一項基準測試，我將一堆資料插入到一個帶聚類 GUID 的表中，將同樣的資料插入到另一個帶 IDENTITY 列的表中。
GUID 表碎片化極其嚴重，僅僅過了 15 分鐘，效能就下降了幾千個百分點。
5 小時後，IDENTITY 表的效能才下降了幾個百分點，這不僅僅適用於 GUID，它適用於任何易失性列。
如果只需檢視資料是否存在，就不要計數行
這種情況很常見，你需要檢視資料存在於表格中，根據這番檢查的結果，你要執行某個操作。
我經常見到有人執行 SELECT COUNT（*）FROMdbo.T1來檢查該資料是否存在：
SET @CT=(SELECT COUNT(*) FROM
dbo.T1);
If@CT>0
BEGIN
END
這完全沒必要，如果你想檢查資料是否存在，只要這麼做：
If EXISTS (SELECT 1 FROM dbo.T1)
BEGIN
END
不要計數表中的一切，只要取回你找到的第一行。SQL Server 聰明得很，會正確使用 EXISTS，第二段程式碼傳回結果超快。
表越大，這方面的差距越明顯。在你的資料變得太大之前做正確的事情。調優資料庫永不嫌早。
實際上，我只是在我的其中一個生產資料庫上執行這個例子，針對一個有 2.7 億行的表。
第一次查詢用時 15 秒，包含 456197 個邏輯讀取，第二次查詢不到 1 秒就傳回結果，只包含 5 個邏輯讀取。
然而如果你確實需要計數表的行數，表又很大，另一種方法就是從系統表中提取，SELECT rows fromsysindexes 將為你獲得所有索引的行數。
又由於聚類索引代表資料本身，所以只要新增 WHERE indid = 1，就能獲得錶行，然後只需包含表名稱即可。
所以，最後的查詢是：
SELECT rows from sysindexes where object_name(id)=’T1’and indexid =1
在我 2.7 億行的表中，不到 1 秒就傳回結果，只有 6 個邏輯讀取，現在效能不一樣了。
不要進行逆向搜尋
以簡單的查詢 SELECT * FROMCustomers WHERE RegionID <> 3 為例。
你不能將索引與該查詢結合使用，因為它是逆向搜尋，需要藉助表掃描來逐行比較。
如果你需要執行這樣的任務，可能發現如果重寫查詢以使用索引，效能會好得多。
該查詢很容易重寫，就像這樣：
SELECT * FROM Customers WHERE RegionID<3 UNION ALL SELECT * FROM Customers WHERE RegionID
這個查詢將使用索引，所以如果你的資料集很大，其效能會遠勝過表掃描版本。
當然，沒有什麼是那麼容易的，也許效能更糟，所以使用之前先試一下。它百分之百管用，雖然涉及太多的因素。
最後，我意識到這個查詢違反了第 4 條規則：不要查詢兩次，但這也表明沒有硬性規則。雖然我們在這裡查詢兩次，但這麼做是為了避免開銷很大的表掃描。
你無法一直運用所有這些技巧，但如果牢記它們，有一天你會用它們來解決一些大問題。
要記住的最重要一點是，別將我說的話當成教條。在你的實際環境中試一下，同樣的解決辦法不是在每種情況下都管用，不過我排查糟糕的效能時一直使用這些方法，而且屢試不爽。
來自：51CTO技術棧（微訊號：blog51cto）
作者：Sean McCown，布加迪編譯標題：SQL unleashed：17 ways to speed your SQL queries
連結：https://www.infoworld.com/article/3209665/sql/sql-unleashed-17-ways-to-speed-your-sql-queries.html?nsdr=true
編輯：陶家龍、孫淑娟
●本文編號283，以後想閱讀這篇文章直接輸入283即可
●輸入m獲取到文章目錄
Web開發
更多推薦《18個技術類公眾微信》
涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。
