譯者 | 陳峻
審校 | 重樓
如您所知，SQL多年來一直是開發和查詢數據庫的主要語言。在編程實踐中，人們逐漸積累了各種在使用過程中的小技巧。下面，讓我們來看看有關如何編寫出更高效的SQL查詢的9種優秀實踐。
1.只檢索需要的列
對于那些所謂的數據庫開發老司機而言，他們會有一個常見的SQL習慣：在編寫查詢代碼時，頻繁地使用SELECT *，一次性列出所有可能需要的數據列。顯然，如果查詢一個存儲了一百多列的數據表的所有列，您可以想象會發生什么？畢竟在真實的系統應用環境中，這樣的數據表屢見不鮮，而且它們并非總是可以通過重新設計和優化，來合理化其結構。那么，您是否考慮過采取簡單點的方法呢？其實，我們可以只選擇列的子集，以避免在查詢過程中占用不必要的資源，并提高執行的效率。
當然，在進行查詢的原型設計時，使用SELECT *是沒有太大問題的，但是一旦進入生產階段，具體的查詢就應該只請求那些實際將會使用到的數據列。
2.使用CASE代替UPDATE進行有條件的列更新
在編程過程中，開發人員也會經常使用UPDATE ...WHERE，來根據數據表中的某一列的值，設置另一列的值。例如，UPDATE Users SET Users.Status="遺留" WHERE Users.ID<1000。不可否認，這種方法既簡單又直觀，但是它有時也會增加不必要的步驟。例如，如果您需要先向某個表中插入數據，然后使用UPDATE來更改數據，那么這便是兩個獨立的事務。不過，當你有數百萬行數據時，此類“徒增”的額外事務就會產生大量不必要的操作。
對于一些大規模操作而言，更好的解決方案是：在查詢中使用內聯CASE語句，在插入操作過程中設置列的值。如此，我們便可以一次性地同時處理初始插入和修改數據了。
3.盡量減少大表查詢
就系統開銷而言，對于任何體量數據表的查詢，都不是“免費”的。而對于那些擁有數億、甚至數十億行的數據表的查詢，更是如此。為此，我們需要盡可能地將那些對于大體量數據表的查詢，合并為最少的離散操作。例如，如果我們想對一個數據表先按照某一列進行查詢，然后再按照另一列予以查詢。那么我們便可以首先將其合并為一個查詢，然后確保你后續要查詢的列擁有了覆蓋索引（Covering Index）。
如果您發現自己必須從一張大的數據表中獲取相同的數據子集，并需要對其運行較小的查詢，那么您可以將其子集持久化到其他地方，并對其進行查詢，從而為當前和后續其他操作提速。這也將引出下一項優秀實踐。
4.為數據設置預分級（Pre-stage）
假設您或組織中的其他人經常需要執行報表或存儲過程。而這些報表或存儲過程又需要通過連接幾張大的數據表，來匯總大量的數據。那么，您與其每次都重新運行連接，不如將其預分級到專門用于此目的的數據表中。據此，報表或程序便可以針對該表一次性地共同完成其操作，從而為自己（和他人）節省大量的工作。此外，如果您有足夠的資源，而且數據庫也能夠提供支持的話，也可以使用內存表，來進一步實現加速。
5.分批進行刪除和更新
試想，您需要在一張數十億行級的數據表中清除數百萬行。雖然最簡單的方法莫過于在事務中運行DELETE。但這樣一來，整張表就會在此過程中被鎖定，直至事務完成。
而復雜一些的方法是分批執行刪除（或更新）操作。此類操作可以與其他事務交錯進行。由于每個事務都會變得更小，更易于管理，因此其他事務也可以在該操作前后或操作期間“見縫插針”地執行。
在實際應用中，此舉將成為任務隊列的良好用例。它不但可以跟蹤跨會話操作的進度，而且允許其以低優先級的狀態，在后臺被操作執行。
6.使用臨時表提高指針性能
有過開發經驗的程序員都知道：指針的使用會導致應用的速度變慢，甚至會阻礙到其他操作。與此同時，那些依賴指針的操作，幾乎都可以用其他方法來完成。因此，在大多數情況下，我們應該避免使用指針。
話說回來，如果您由于某種原因不得不使用指針的話，臨時表則可以減少由指針帶來的性能問題。例如，如果您需要遍歷某個數據表，并根據計算結果更改某一列的話，則可以將待更新的候選數據放入臨時表中，用指針來遍歷該臨時表，然后在一次性的操作中，應用所有的更新。當然，此方式還可以將指針的某個處理分成多個批次。
7.使用表值（table-valued）函數而非標量（scalar）函數
由于標量函數可以將計算封裝到類似存儲過程的SQL代碼段中，因此開發人員的通常做法是：將標量函數的結果作為SELECT查詢中的某一列去返回。不過，您可以使用表值函數來進行代替，并在查詢中使用CROSS AppLY來獲得更好的性能。
8.使用分區以避免大量數據移動
SQL Server Enterprise提供了一種“分區（partitioning）”功能，可以將數據庫表分割成多個分區。也就是說，如果你有一張表需要經常歸檔到另一個表中，那么就可以避免使用INSERT/DELETE來移動數據，而直接使用SWITCH來代替。
我們可以假想一個場景，如果有一張表需要每天都被清空至一張歸檔表中。那么，我們就可以使用SWITCH，簡單地將日常表中的頁面，分配到該歸檔表中，從而執行清空和復制操作。與手動復制和刪除相比，該切換過程所需的時間要少得多。Cathrine Wilhelmsen提供了如何以這種方式使用分區的精彩教程，您可以通過鏈接--https://www.cathrinewilhelmsen.NET/table-partitioning-in-sql-server-partition-switching/，進行參考。
9.使用存儲過程提高性能，使用ORM帶來便利
ORMs，即：對象關系映射器（object-relational mappers）是一套能以編程的方式生成SQL代碼的軟件工具包。它們允許您使用應用程序的編程語言及其隱喻（Metaphors），來開發和維護查詢。
由于ORM可能產生低效、有時甚至無法被代碼優化，而備受詬病。同時，它們也會降低開發人員學習SQL、以及理解查詢內容的積極性。許多數據庫開發人員原則上并不喜歡ORM，他們在需要通過手動編寫查詢，以獲得最佳性能時，往往無所適從。
相反，對于經常被調用、需要良好性能、不常被更改、以及需要數據庫分析工具對性能進行檢測的查詢而言，使用存儲過程是最為合理的。與臨時查詢相比，大多數數據庫更容易獲得存儲過程的匯總統計信息。數據庫的查詢規劃器也更容易對存儲過程進行優化。
不過，將更多的數據庫邏輯移入存儲過程的缺點是：邏輯與數據庫的耦合更加緊密。存儲過程可能會從性能優勢變為巨大的技術債（Technical Debt）。如果您后續準備遷移到另一種數據庫技術的話，那么更改ORM的目標會比重寫所有存儲過程要容易得多。畢竟應用程序的數據庫部分的編寫方式，與應用邏輯的耦合度不高。相反，ORM倒是能夠使得編寫和維護數據庫代碼更加容易。此外，我們可以檢查由ORM生成的代碼，以進行優化，而且查詢緩存也能夠允許我們重用那些最常被生成的查詢。
總之，如果您覺得應用程序端的可維護性更重要的話，那就請使用ORM；如果您需要在數據庫方面具有更好的性能的話，則請使用存儲過程。
譯者介紹
陳峻（Julian Chen），51CTO社區編輯，具有十多年的IT項目實施經驗，善于對內外部資源與風險實施管控，專注傳播網絡與信息安全知識與經驗。
原文標題：SQL unleashed: 9 ways to speed up your SQL queries，作者：Serdar Yegulalp
