【譯】SQL 指引：如何寫出更好的查詢

臨書發表於2017-09-20
SQL

SQL 指引：如何寫出更好的查詢

結構化查詢語言（SQL）是資料科學行業的一種不可或缺的技能，一般來說，學習這項技能是相當簡單的。然而大多數人都忘記 SQL 不僅僅是寫查詢語句，這只是第一步。確保查詢高效能，或者符合上下文語意又完全是另外一回事了。

這就是為什麼本篇 SQL 教程要引導你，可以通過以下步驟來評估你的查詢：

  • 首先，你將以資料科學工作中學習 SQL 的重要性的簡要概述為開始。
  • 接著，你將學習更多有關如何 SQL 查詢處理和執行，這樣你才能夠正確地理解編寫高效能查詢的重要性：更具體地說，你會看到查詢被解析，重寫，優化和最終被執行；
  • 考慮到這一點，你不僅可以複習初學者編寫查詢時的一些反模式查詢，而且還可以學習關於針對那些可能出現的錯誤的替代和解決方案，你還將學習更多有關基於集合還是程式方法進行查詢的內容。
  • 你還將看到這些出於效能問題考慮的反模式，除了“手動”方法改進 SQL 查詢之外，你還可以通過使用一些其他可幫助你檢視查詢計劃的工具，以更加結構化，深入的方式分析你的查詢；而且，
  • 在執行查詢之前，你將簡要了解時間複雜度和大 O 符號來在你執行查詢之前瞭解執行計劃的時間複雜度；最後，
  • 你將簡要地瞭解如何進一步調整你的查詢。

你對 SQL 課程感興趣嗎？那就來學習 DataCamp 的資料科學的 SQL 簡介課程吧！

為什麼我應該為資料科學學習 SQL？

SQL 遠未消亡：無論你是申請資料分析師，資料工程師，資料科學家還是任何其他職位，你都可以從資料科學行業的職位描述中發現 SQL 是最需要的技能之一。參加 O'Reilly 資料科學工資調查報告的 70％ 的受訪者證實了這一點，他們表示他們會在專業場景中使用 SQL。而且，在本次調查中，SQL（70%）遠勝於 R（57％）和 Python（54％）程式語言。

你得知一個情況：當你正在努力找資料科學行業的工作時，SQL 是一項必須具備的技能。

對於一個20世紀70年代初開發的語言來說，還不錯，對吧？

但是為什麼被使用的如此頻繁？為什麼 SQL 不會消失，即使它已經存在了很長時間了？

有幾個原因：第一個原因是大多數公司將資料儲存在關係型資料庫管理系統（RDBMS）或關係資料流管理系統（RDSMS）中，你需要 SQL 才能訪問這些資料。 SQL 是資料的通用語言：它使你能夠與幾乎任何資料庫進行互動，甚至可以在本地建立自己的資料庫！

如果這還不夠，請記住有很多 SQL 的實現在供應商之間不相容，並不一定遵守標準。因而，瞭解標準 SQL 是你在（資料科學）行業中找到一條路的要求之一。

除此之外，可以肯定地說，SQL 也被更新的技術所接受，例如 Hive，用於查詢和管理大型資料集的類 SQL 查詢語言介面，或可用於執行 SQL 查詢的 Spark SQL。雖然你發現標準可能與你已知的有所不同，但學習曲線將會更加容易。

如果你想做一個比較，認為它和學線性代數一樣：通過把所有的精力放在這個主題上，你甚至可以使用它來掌握機器學習！

簡而言之，這就是為什麼你應該學習這門查詢語言：

  • 即使對於新手它也是相當容易學習的。學習曲線是相當容易和平滑的，以至於在學習的任何階段你都能寫出查詢。
  • 遵循“一旦學習，處處適用”的原則，所以這是一個對你時間的偉大投資！
  • 它是對程式語言的極好補充; 在某些情況下，編寫查詢甚至比編寫程式碼更為優先，因為它效能更高！
  • …

你還在等什麼呢？

SQL 處理 & 查詢執行

為了提高你 SQL 查詢的效能，當你按快捷方式執行查詢時，你首先需要知道內部發生了什麼。

首先，查詢被解析成“解析樹”；分析查詢，看是否符合語法和語義要求。解析器建立輸入查詢的內部表示。然後將輸出傳遞給重寫引擎。

然後，優化器的任務是找到給定查詢的最佳執行或查詢的計劃。執行計劃準確地定義了每個操作使用什麼演算法，以及如何協調操作的執行。

為了找到最佳的執行計劃，優化器列舉所有可能的執行計劃，確定每個計劃的性質或成本，獲取有關當前資料庫狀態的資訊，然後選擇其中最佳的一個作為最終的執行計劃。由於查詢優化器可能並不完善，因此資料庫使用者和管理員有時需要手動檢查並調整優化器生成的計劃以獲得更好的效能。

現在你可能想知道什麼是一個“好的查詢計劃”。

如你所見，一個計劃的質量在查詢中起著重要的作用。更具體地說，評估計劃所需的磁碟 I/O，CPU成本和資料庫客戶端可以觀察到的總體響應時間以及總執行時間等因素至關重要。這就涉及到了時間複雜度的概念，在後面你將會看到更多與此相關的內容。

接下來，執行所選擇的查詢計劃，由系統的執行引擎進行評估並返回查詢結果。

在上節中描述的可能不是很清楚的是，Garbage In, Garbage Out（GIGO）原則在查詢處理和執行中會自然地顯現：制定查詢的人掌握著你 SQL 查詢效能的關鍵，如果優化器得到的是一個不好的查詢語句，那麼那麼它也只能做到這麼多...

這意味著你在編寫查詢時可以執行一些操作。如你在介紹中所見，責任是雙重的：它不僅僅是寫出符合一定標準的查詢，而且還涉及收集查詢中效能問題可能潛伏在哪裡的意識。

一個理想的出發點是在你的查詢中考慮可能會潛入問題的“地方”。新手通常會在以下四個子句和關鍵字中遇到效能問題。

  • WHERE 子句
  • 任何 INNER JOIN 或 LEFT JOIN 關鍵字; 還有，
  • HAVING 子句；

當然，這種方法簡單而原始，但作為初學者，這些子句和宣告是很好的指引，而且確切地說，當你剛開始時，這些地方就是容易出錯的地方，更諷刺的是這些錯誤很難被發現。

然而，你也應該意識到，效能只有在實際場景中才有意義：只是單純的說這些子句和關鍵字是不好的沒有任何意義。當然，查詢中有 WHERE 或 HAVING 子句不一定意味著這是一個壞的查詢...

檢視以下內容，瞭解更多有關的構建查詢的反模式和可替代的方法。這些提示和技巧可作為指導。如何重寫以及是否真的需要重寫取決於資料量，資料庫，以及查詢所需的次數等等。它完全取決於你查詢的目標，並且有一些你要查詢的資料庫的之前的瞭解也是至關重要的！

1. 僅檢索你需要的資料

當編寫 SQL 查詢時，「資料越多越好」的思維方式是不應該的：獲取比你實際需求更多的資料不僅會有看錯的風險，而且效能可能會因為查詢太多資料而受到影響。

這就是小心處理 SELECT 語句，DISTINCT 子句和 LIKE 運算子是個不錯的主意。

當你寫好你的查詢時，你能檢查的第一件事情就是 SELECT 語句是否已經是最緊湊了。你的目標應該是從 SELECT 中刪除不必要的列。這樣，你強制自己只提取符合查詢目的的資料。

如果具有 EXISTS 的相關子查詢，則應嘗試在該子查詢的 SELECT 語句中使用常量，而不是選擇實際列的值。當你只檢查資料是否存在時，這是特別方便的。

記住相關子查詢是使用外部查詢中的值的子查詢。注意，儘管 NULL 可以在此上下文中當作“常量”使用，但是這會令人非常困惑！

考慮下面這個例子，並理解使用常量的意義在哪：

SELECT driverslicensenr, name
FROM Drivers
WHERE EXISTS (SELECT '1' FROM Fines
              WHERE fines.driverslicensenr = drivers.driverslicensenr);複製程式碼

提示：可以很方便知道，使用相關子查詢通常不是一個好主意。你應該考慮使用 INNER JOIN 重寫來避免它們：

SELECT driverslicensenr, name
FROM drivers
INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr;複製程式碼

SELECT DISTINCT 語句是用來返回不同的值的。如果可以，你應該你要儘量避免使用 DISTINCT 這個子句；就像你在其他例子中看到的一樣，如果你把這個子句新增到你的查詢中，執行時間肯定會增加。因此，經常考慮是否真的需要 DISTINCT 操作來獲取想要的結果是一個好主意。。

當你在一個查詢中使用 LIKE 操作符時，如果匹配模式以 % 或者 _ 開始，那麼是不會使用索引的。它將阻止資料庫使用索引（如果存在）。當然，在另一個方面看，這種型別的查詢會潛在地返回過多的記錄，這不一定滿足你的查詢目標。

再次，你對儲存在資料庫中的資料的瞭解程度可以幫助你制訂一個模式，這可以幫助你從所有資料中正確過濾出和你的查詢真正相關的行。

2. 不要輸出太多結果

當你不能過濾掉 SELECT 語句中的列時，你可以考慮用其他方法限制你的結果。以下是 LIMIT 語句和資料型別的轉換方法。

你可以通過為查詢新增 LIMIT 或者 TOP 子句來為查詢結果設定最大行數。這兒是一些例子：

SELECT TOP 3 * FROM Drivers;複製程式碼

注意 你可以進一步指定 PERCENT，比如，你可以通過 SELECT TOP 50 PERCENT * 這個查詢語句來替換第一行。

SELECT driverslicensenr, name FROM Drivers LIMIT 2;複製程式碼

此外，你還可以新增 ROWNUM 子句，這相當於在查詢中使用 LIMIT：

SELECT *
FROM Drivers
WHERE driverslicensenr = 123456 AND ROWNUM <= 3;複製程式碼

你應該始終使用最有效的，也就是最小的資料型別。當小的資料型別已經足夠的時候你提供一個巨大的資料型別總是有風險的。

然而，當你將資料型別轉換新增到查詢中時，你肯定增加了它的執行時間。

一個替代方案是儘量避免資料型別轉換。但是還要注意，資料型別轉換不是總能從查詢中被刪除或者省略的，而且當你在查詢語句包含它們的時候一定要注意，你可以在執行查詢之前測試新增它們的影響。

3. 不要讓查詢比需求更復雜

資料型別轉換將你帶到了下一個關鍵點：你不應該過度設計你的查詢。試著保持簡單高效。作為一個提示，這可能看起來太簡單或者愚蠢了，特別是在查詢可能變得複雜的情況下。

然而，你將會在下一部分提到的示例中看到，你可以很輕鬆的把本應更復雜的查詢變得簡單。

當你在你的查詢裡使用 OR 操作符時，很可能你沒有使用索引。

記住索引是一種資料結構，可以提高資料庫表中的資料檢索速度，但它是有代價的：它需要額外的寫入和額外的儲存空間來維護索引結構。索引用來快速定位或查詢資料而無需在每次訪問資料庫時查詢每一行。索引可以使用資料庫表中的一列或多列來建立。

如果你不使用資料庫包含的索引，你的查詢會花費更長的時間來執行。這就是為什麼最好在查詢中找到使用 OR 運算子的替換方案；

考慮以下查詢：

SELECT driverslicensenr, name
FROM Drivers
WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678;複製程式碼

你可以將運算子替換為：

SELECT driverslicensenr, name
FROM Drivers
WHERE driverslicensenr IN (123456, 678910, 345678);複製程式碼
  • 包含 UNION 的兩個 SELECT 語句。

提示：這兒你需要小心，沒有必要就不要使用 UNION 運算子，因為你會多次查詢同一個表多次，這是不必要的。同時，你必須意識到當你在查詢語句裡使用 UNION 時，執行時間會變長。UNION 操作符的替代是：將所有條件都放在一個 SELECT 結構中，或者使用 OUTER JOIN 替代 UNION 來重新構建查詢。

提示：在這裡也要記住的一點是，儘管 OR 以及下面將要提到的其他運算子可能不使用索引，索引查詢不總是更好的。

就像 OR 運算子一樣，當你的查詢包含 NOT 操作符時，也很可能不使用索引。這將不可避免的減慢你的查詢。如果你不明白這是什麼意思，考慮下以下查詢：

SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980);複製程式碼

這個查詢跑起來肯定比你預料還要慢，主要是因為它構建的太過於複雜了：在這樣的情況下，最好尋找一個替代方案。考慮使用比較運算子替換 NOT，比如 >，<> 或者 !>；上面的例子可能會被重寫為這樣：

SELECT driverslicensenr, name FROM Drivers WHERE year <= 1980;複製程式碼

看起來已經更加整潔了，不是嗎？

AND 是另一個不使用索引的操作符，如果以過於複雜和低效的方式使用，它會減慢你的查詢，就像下面的例子：

SELECT driverslicensenr, name
FROM Drivers
WHERE year >= 1960 AND year <= 1980;複製程式碼

最好使用 BETWEEN 運算子重寫這個查詢：

SELECT driverslicensenr, name
FROM Drivers
WHERE year BETWEEN 1960 AND 1980;複製程式碼

ALL 和 ALL 運算子你也應該小心使用，將他們包含進查詢中會導致不使用索引。替代方法使用聚合功能，在這裡比較方便的方法是使用像 MIN 或者 MAX 的聚合函式。

提示：在你使用所提出的方案的情況下，你應該意識到，所有的聚合函式比如 SUM，AVG，MIN，MAX 在多行的時候會導致很長時間的查詢，在這種情況下，你可以嘗試減少要處理的行數或預先計算這些值。當你決定使用哪個查詢時，最重要的是清楚你的環境和查詢目標。

在使用列進行計算或者列作為標量函式的引數時，也是不會使用索引的。一個特定的解決方案是簡單的隔離這個特殊列，使其不再是計算或者函式的一部分或引數。請考慮一下示例：

SELECT driverslicensenr, name
FROM Drivers
WHERE year + 10 = 1980;複製程式碼

這看起來很有趣，是不？相反，試著重新考慮如何計算，然後像這樣重寫查詢：

SELECT driverslicensenr, name
FROM Drivers
WHERE year = 1970;複製程式碼

4. 不要暴力查詢

最後一個提示，你不應該總是太限制查詢，因為這也會影響效能。特別是 join 語句和 HAVING 子句。

當你對兩個表使用 join 時，考慮你 join 的兩張表的順序是很重要的。如果一張表比另一張大很多，你最好重寫你的查詢讓最大的表最後做 join 操作。

  • 減少 Joins 的條件

當你加了太多的條件到你的 joins 語句，你有義務選擇一個特定的路徑，雖然這個路徑並不總是最高效的那個。

將 HAVING 子句新增進 SQL 是因為 WHERE 關鍵字不能和聚合方法一起使用。HAVING 的典型的用法就是和 GROUP BY 子句來約束分組聚合後的結果，使其滿足一些精確匹配條件。然而，你知道的，使用這個子句是不會用到索引的，會導致查詢不能很好的執行。

如果你在尋找替代的方案，考慮使用 WHERE 子句，請看如下的查詢：

SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state

SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state複製程式碼

第一個查詢使用 WHERE 子句限制需要求和的行數，而第二個查詢對錶中的所有行進行了求和，然後使用 HAVING 子句來捨棄其中的部分。在這種情況下，選擇使用 WHERE 子句顯然是更好的，因為你不會浪費任查詢資源。

你會發現，這並不是限制最終結果集，而是限制查詢中的中間記錄的數量。

注意 這兩個子句之間的區別在於，WHERE 子句引入了單行的條件，而 HAVING 子句引入了一個選擇集合或結果的條件，比如 MIN，MAX，SUM，… 這些都已經從多行生成了的。

你看，當你想以儘可能的提高效能為前提的時候，評估語句質量，構建查詢還有改寫查詢並不是一件容易的工作；當你構建執行在專業環境中的查詢的時候，避免反模式和考慮替代方案也將成為你責任的一部分。

這個清單只是一些小的反模式的概述和技巧，可能對新手有些幫助；如果你想了解更多高階開發人員常見的反模式，檢視 stackoverflow 的這個討論。

基於集合與程式方法的查詢

上述反模式隱含的點實際上歸結為基於集合與程式方法構建查詢的差異。

程式方法的查詢是一種很像程式設計的一種查詢方式：你告訴系統做什麼，怎麼做。

一個例子是你使用冗餘的連線操作或者濫用 HAVING 子句的情況下，就像上面的例子，你可以通過執行一個函式呼叫另一個函式來查詢資料庫，或者使用包含迴圈，使用者定義方法，遊標等，來獲取最終結果。在這個方法中，你會經常發現你自己請求一個資料的子集，然後再請求這個資料的子集等等。

毫不奇怪，這個方法經常被稱為「逐步」或者「逐行」查詢。

另一種方法是基於集合的方法，你只需要指定做什麼。你的職責包含從查詢中指定要獲得的結果集的條件或要求。至於你的資料是如何獲取到的，這取決於內部決定查詢實現的機制：讓資料庫引擎來確定查詢最好的演算法和執行邏輯。

由於 SQL 是基於集合的，這種方法（基於集合）比程式方法更有效幾乎不會讓人感到驚訝，這也是一個驚喜，也解釋了為什麼在某些情況下，SQL 可以比程式碼更快的工作。

提示 在查詢中基於集合的方法也是資料科學行業最頂級的僱主所要求你掌握的方法！你經常需要在這兩種方法之間切換。

注意 如果你發現你自己有程式型別的查詢，你應該考慮重寫或者重構它。

從查詢到執行計劃

-------------知道反模式不是靜態的，而是隨著你做為 SQL 開發者的成長而演進，當你考慮替代方案的時候也意味著你正在避免反模式查詢和重寫查詢的這個事實，這是一個十分困難的任務。任何幫助都可以派上用場，這就是為什麼使用一些工具通過更結構化的方式來優化你的查詢或許是個不錯的選擇。

注意 還有一些上一節提到的反模式源於效能的問題的考慮，比如 AND，OR 和 NOT 操作符缺少索引的使用。對效能的思考不僅需要結構化的方法，還需要更多的深入的方法。

然而可能的是，這種結構化和深入的方法更多是基於查詢計劃的，即首先被解析為「解析樹」，然後在確定每個操作具體使用什麼演算法，還有如何使執行操作更協調。

正如你在介紹中讀到的，你可能需要手動檢查優化器的生成計劃。在這種情況下，你將需要通過檢視查詢計劃來再次分析你的查詢。

要掌握這種查詢計劃，你將需要使用資料庫管理系統為你提供工具，你可以使用的工具如下：

  • 生成查詢計劃的圖形表示的一些工具包，看以下這個例子：

  • 其他工具將能夠為你提供查詢計劃的文字描述。一個例子是 Oracle 中的 EXPLAIN PLAN 語句，但指令的名稱根據你使用的 RDBMS 而有所不同。在其他資料庫，你可能會看到 EXPLAN（MySQL，PostgreSQL）或者 EXPLAIN QUERY PLAN（SQLite）。

注意如果你平時使用 PostgreSQL，你可以在 EXPLAIN 之間做出區分，這裡你只得到了一個描述，它是說明還未執行的查詢計劃會如何執行，而 EXPLAIN ANALYZE 實際上執行了查詢然後返回對預期與實際的查詢計劃的分析。一般來說，一個實際的執行計劃就是一個實際的查詢計劃，雖然在邏輯上是等價的，一個實際的執行計劃更為有用，因為它包含執行查詢時實際發生的其他細節和統計資訊。

在本節的剩餘部分，你將會學習到更多關於 EXPLAIN 和 ANALYZE 的資訊，以及如何使用這兩個去了解更多你的查詢計劃和查詢效能的資訊。

提示：如果你想了解更多關於 EXPLAIN 或更詳細的檢視例項，考慮閱讀 Guillaume Lelarge 寫的這本書 “Understanding Explain”。

時間複雜度和大 O

現在你已經簡要的檢查了查詢計劃，你可以在複雜度計算的幫助下開始更深入的研究具體的效能問題。理論電腦科學這一領域著重於根據難度對問題進行分類；這些計算問題可以是演算法，也可以是查詢。

然而，對於查詢，你並不一定是根據他們的困難程度分類，而是根據執行它然後拿到返回結果的時間來分類。這個被叫做時間複雜度，你可以使用大 O 符號來表達和衡量這種複雜性。

使用大 O 符號，輸入任意大時，你可以根據輸入與執行時間的相對增長速度來衡量執行時間。大 O 表示法排除係數和低階的項，以便於你關注查詢執行時間的關鍵部分：增長率。當以這種方式表示時，丟棄係數與低階的項，時間複雜度被認為是漸進式描述的。這意味著輸入會變為無窮大。

在資料庫語言中，複雜度衡量了資料庫表資料增加之後，查詢該表資料所花時間相對增加了多少的過程。

注意你的資料庫大小不僅僅因為表裡儲存的資料增多而變大，索引在其中對大小影響也起了很大的作用。

正如前面所述，執行計劃除了前面所說的以外，還定義了每一步操作使用什麼演算法，這使得每次查詢執行的時間可以在邏輯上表示為查詢計劃中涉及表大小的函式。換句話說，你可以使用大 O 符號和執行計劃預估查詢的複雜性和效能。

在接下來的小節中，你會了解關於四種時間複雜度型別的一般概念，你將會看到一些示例，說明查詢的時間複雜度如何根據你執行它們上下文的不同而有所不同的。

提示：索引是故事的一部分！

注意，因為不同的資料庫有不同型別的索引、不同的執行計劃、不同的實現，所以下面列出的幾個時間複雜度是很通用的，會根據你配置的不同而變化。

更多閱讀在這兒。

總而言之，你可以檢視以下備忘單，以根據時間複雜度以及其執行情況估計查詢的效能：

SQL 調優

考慮到查詢計劃和時間複雜性，你可以考慮進一步調整 SQL 查詢，特別注意以下幾點：

  • 大表的全表掃描替換為索引的掃描；
  • 確保你正在使用最佳的表連線順序；
  • 確保的使用索引優化；還有
  • 快取小表的全表掃描。

祝賀！你已經看到了這篇博文的結尾，這只是幫助你對 SQL 查詢效能的一瞥。你希望對反模式，查詢優化器，審查工具，預估和解釋查詢計劃的複雜性有更多的見解，然而，還有更多的東西等你去發現！如果你想知道更多，可以考慮讀這本由R. Ramakrishnan 和 J. Gehrke 寫的「Database Management Systems」。

最後，我不想錯過這個來自 StackOverFlow 使用者那裡的引用

「我最喜歡的反模式不是測試你的查詢。

這適用於：

  • 你的查詢涉及了不止一張表。

  • 你認為你的查詢有一個優化的設計，但不願意去驗證你的假設。

  • 你會接受第一個成功的查詢，它是否是最優的，你並不清楚。」

如過你想開始使用 SQL，可以考慮學習 DataCamp 的 Intro to SQL for Data Science 課程！

掘金翻譯計劃 是一個翻譯優質網際網路技術文章的社群，文章來源為 掘金 上的英文分享文章。內容覆蓋 Android、iOS、React、前端、後端、產品、設計 等領域，想要檢視更多優質譯文請持續關注 掘金翻譯計劃、官方微博、知乎專欄。

相關文章