譯者 | 陳峻

審校 | 重樓

如您所知，SQL多年來一直是開發(fā)和查詢數(shù)據(jù)庫的主要語言。在編程實踐中，人們逐漸積累了各種在使用過程中的小技巧。下面，讓我們來看看有關(guān)如何編寫出更高效的SQL查詢的9種優(yōu)秀實踐。

1.只檢索需要的列

對于那些所謂的數(shù)據(jù)庫開發(fā)老司機而言，他們會有一個常見的SQL習(xí)慣：在編寫查詢代碼時，頻繁地使用SELECT *，一次性列出所有可能需要的數(shù)據(jù)列。顯然，如果查詢一個存儲了一百多列的數(shù)據(jù)表的所有列，您可以想象會發(fā)生什么？畢竟在真實的系統(tǒng)應(yīng)用環(huán)境中，這樣的數(shù)據(jù)表屢見不鮮，而且它們并非總是可以通過重新設(shè)計和優(yōu)化，來合理化其結(jié)構(gòu)。那么，您是否考慮過采取簡單點的方法呢？其實，我們可以只選擇列的子集，以避免在查詢過程中占用不必要的資源，并提高執(zhí)行的效率。

當(dāng)然，在進(jìn)行查詢的原型設(shè)計時，使用SELECT *是沒有太大問題的，但是一旦進(jìn)入生產(chǎn)階段，具體的查詢就應(yīng)該只請求那些實際將會使用到的數(shù)據(jù)列。

2.使用CASE代替UPDATE進(jìn)行有條件的列更新

在編程過程中，開發(fā)人員也會經(jīng)常使用UPDATE ...WHERE，來根據(jù)數(shù)據(jù)表中的某一列的值，設(shè)置另一列的值。例如，UPDATE Users SET Users.Status="遺留" WHERE Users.ID<1000。不可否認(rèn)，這種方法既簡單又直觀，但是它有時也會增加不必要的步驟。例如，如果您需要先向某個表中插入數(shù)據(jù)，然后使用UPDATE來更改數(shù)據(jù)，那么這便是兩個獨立的事務(wù)。不過，當(dāng)你有數(shù)百萬行數(shù)據(jù)時，此類“徒增”的額外事務(wù)就會產(chǎn)生大量不必要的操作。

對于一些大規(guī)模操作而言，更好的解決方案是：在查詢中使用內(nèi)聯(lián)CASE語句，在插入操作過程中設(shè)置列的值。如此，我們便可以一次性地同時處理初始插入和修改數(shù)據(jù)了。

3.盡量減少大表查詢

就系統(tǒng)開銷而言，對于任何體量數(shù)據(jù)表的查詢，都不是“免費”的。而對于那些擁有數(shù)億、甚至數(shù)十億行的數(shù)據(jù)表的查詢，更是如此。為此，我們需要盡可能地將那些對于大體量數(shù)據(jù)表的查詢，合并為最少的離散操作。例如，如果我們想對一個數(shù)據(jù)表先按照某一列進(jìn)行查詢，然后再按照另一列予以查詢。那么我們便可以首先將其合并為一個查詢，然后確保你后續(xù)要查詢的列擁有了覆蓋索引（Covering Index）。

如果您發(fā)現(xiàn)自己必須從一張大的數(shù)據(jù)表中獲取相同的數(shù)據(jù)子集，并需要對其運行較小的查詢，那么您可以將其子集持久化到其他地方，并對其進(jìn)行查詢，從而為當(dāng)前和后續(xù)其他操作提速。這也將引出下一項優(yōu)秀實踐。

4.為數(shù)據(jù)設(shè)置預(yù)分級（Pre-stage）

假設(shè)您或組織中的其他人經(jīng)常需要執(zhí)行報表或存儲過程。而這些報表或存儲過程又需要通過連接幾張大的數(shù)據(jù)表，來匯總大量的數(shù)據(jù)。那么，您與其每次都重新運行連接，不如將其預(yù)分級到專門用于此目的的數(shù)據(jù)表中。據(jù)此，報表或程序便可以針對該表一次性地共同完成其操作，從而為自己（和他人）節(jié)省大量的工作。此外，如果您有足夠的資源，而且數(shù)據(jù)庫也能夠提供支持的話，也可以使用內(nèi)存表，來進(jìn)一步實現(xiàn)加速。

5.分批進(jìn)行刪除和更新

試想，您需要在一張數(shù)十億行級的數(shù)據(jù)表中清除數(shù)百萬行。雖然最簡單的方法莫過于在事務(wù)中運行DELETE。但這樣一來，整張表就會在此過程中被鎖定，直至事務(wù)完成。

而復(fù)雜一些的方法是分批執(zhí)行刪除（或更新）操作。此類操作可以與其他事務(wù)交錯進(jìn)行。由于每個事務(wù)都會變得更小，更易于管理，因此其他事務(wù)也可以在該操作前后或操作期間“見縫插針”地執(zhí)行。

在實際應(yīng)用中，此舉將成為任務(wù)隊列的良好用例。它不但可以跟蹤跨會話操作的進(jìn)度，而且允許其以低優(yōu)先級的狀態(tài)，在后臺被操作執(zhí)行。

6.使用臨時表提高指針性能

有過開發(fā)經(jīng)驗的程序員都知道：指針的使用會導(dǎo)致應(yīng)用的速度變慢，甚至?xí)璧K到其他操作。與此同時，那些依賴指針的操作，幾乎都可以用其他方法來完成。因此，在大多數(shù)情況下，我們應(yīng)該避免使用指針。

話說回來，如果您由于某種原因不得不使用指針的話，臨時表則可以減少由指針帶來的性能問題。例如，如果您需要遍歷某個數(shù)據(jù)表，并根據(jù)計算結(jié)果更改某一列的話，則可以將待更新的候選數(shù)據(jù)放入臨時表中，用指針來遍歷該臨時表，然后在一次性的操作中，應(yīng)用所有的更新。當(dāng)然，此方式還可以將指針的某個處理分成多個批次。

7.使用表值（table-valued）函數(shù)而非標(biāo)量（scalar）函數(shù)

由于標(biāo)量函數(shù)可以將計算封裝到類似存儲過程的SQL代碼段中，因此開發(fā)人員的通常做法是：將標(biāo)量函數(shù)的結(jié)果作為SELECT查詢中的某一列去返回。不過，您可以使用表值函數(shù)來進(jìn)行代替，并在查詢中使用CROSS APPLY來獲得更好的性能。

8.使用分區(qū)以避免大量數(shù)據(jù)移動

SQL Server Enterprise提供了一種“分區(qū)（partitioning）”功能，可以將數(shù)據(jù)庫表分割成多個分區(qū)。也就是說，如果你有一張表需要經(jīng)常歸檔到另一個表中，那么就可以避免使用INSERT/DELETE來移動數(shù)據(jù)，而直接使用SWITCH來代替。

我們可以假想一個場景，如果有一張表需要每天都被清空至一張歸檔表中。那么，我們就可以使用SWITCH，簡單地將日常表中的頁面，分配到該歸檔表中，從而執(zhí)行清空和復(fù)制操作。與手動復(fù)制和刪除相比，該切換過程所需的時間要少得多。Cathrine Wilhelmsen提供了如何以這種方式使用分區(qū)的精彩教程，您可以通過鏈接--https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server-partition-switching/，進(jìn)行參考。

9.使用存儲過程提高性能，使用ORM帶來便利

ORMs，即：對象關(guān)系映射器（object-relational mappers）是一套能以編程的方式生成SQL代碼的軟件工具包。它們允許您使用應(yīng)用程序的編程語言及其隱喻（Metaphors），來開發(fā)和維護(hù)查詢。

由于ORM可能產(chǎn)生低效、有時甚至無法被代碼優(yōu)化，而備受詬病。同時，它們也會降低開發(fā)人員學(xué)習(xí)SQL、以及理解查詢內(nèi)容的積極性。許多數(shù)據(jù)庫開發(fā)人員原則上并不喜歡ORM，他們在需要通過手動編寫查詢，以獲得最佳性能時，往往無所適從。

相反，對于經(jīng)常被調(diào)用、需要良好性能、不常被更改、以及需要數(shù)據(jù)庫分析工具對性能進(jìn)行檢測的查詢而言，使用存儲過程是最為合理的。與臨時查詢相比，大多數(shù)數(shù)據(jù)庫更容易獲得存儲過程的匯總統(tǒng)計信息。數(shù)據(jù)庫的查詢規(guī)劃器也更容易對存儲過程進(jìn)行優(yōu)化。

不過，將更多的數(shù)據(jù)庫邏輯移入存儲過程的缺點是：邏輯與數(shù)據(jù)庫的耦合更加緊密。存儲過程可能會從性能優(yōu)勢變?yōu)榫薮蟮募夹g(shù)債（Technical Debt）。如果您后續(xù)準(zhǔn)備遷移到另一種數(shù)據(jù)庫技術(shù)的話，那么更改ORM的目標(biāo)會比重寫所有存儲過程要容易得多。畢竟應(yīng)用程序的數(shù)據(jù)庫部分的編寫方式，與應(yīng)用邏輯的耦合度不高。相反，ORM倒是能夠使得編寫和維護(hù)數(shù)據(jù)庫代碼更加容易。此外，我們可以檢查由ORM生成的代碼，以進(jìn)行優(yōu)化，而且查詢緩存也能夠允許我們重用那些最常被生成的查詢。

總之，如果您覺得應(yīng)用程序端的可維護(hù)性更重要的話，那就請使用ORM；如果您需要在數(shù)據(jù)庫方面具有更好的性能的話，則請使用存儲過程。

譯者介紹

陳峻（Julian Chen），51CTO社區(qū)編輯，具有十多年的IT項目實施經(jīng)驗，善于對內(nèi)外部資源與風(fēng)險實施管控，專注傳播網(wǎng)絡(luò)與信息安全知識與經(jīng)驗。

原文標(biāo)題：SQL unleashed: 9 ways to speed up your SQL queries，作者：Serdar Yegulalp