譯者 | 陳峻

審校 | 重樓

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

1.只檢索需要的列

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

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

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

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

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

3.盡量減少大表查詢

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

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

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

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

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

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

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

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

6.使用臨時(shí)表提高指針性能

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

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

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

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

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

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

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

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

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

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

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

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

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

譯者介紹

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

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