328
王者榮耀
如何編寫更好的SQL查詢：終極指南（上）
結構化查詢語言（SQL）是數據挖掘分析行業不可或缺的一項技能，總的來說，學習這個技能是比較容易的。對於SQL來說，編寫查詢語句隻是第一步，確保查詢語句高效並且適合於你的數據庫操作工作，才是最重要的。這個教程將會提供給你一些步驟，來評估你的查詢語句。
首先，應該了解學習SQL對於數據挖掘分析這個工作的重要性;
接下來，應該先學習SQL查詢語句的處理和執行過程，以便可以更好的了解到，編寫高質量的查詢有多重要。具體說來就是，應該了解查詢是如何被解析、重寫、優化和最終評估的;
掌握了上麵一點之後，你不僅需要重溫初學者在編寫查詢語句時，所使用的查詢反向模型，而且還需要了解有關可能發生錯誤的替代方案和解決方案。同時還應該了解更多查詢工作中的基於集合的程序方法。
在性能方麵也需要關注反向模型，除了手動提高SQL查詢的方法外，還需要以更加結構化和深入的方式來分析你的查詢，以便使用其它工具來完成整個查詢工作。
在執行查詢之前，還需要更加深入的了解執行查詢計劃的時間複雜度。
最後，應該了解如何進一步的調整你的查詢語句。
尋找數據挖掘分析行業的工作，SQL是最需要的技能之一，不論是申請數據分析工作、數據引擎工作、數據挖掘分析或者其它工作。在O'Reilly發布的《2016數據科學從業者薪酬報告》中，有70％的受訪者證實了這一點，表示他們需要在專業環境中使用SQL。此外，本次調查中，SQL遠勝於R（57％）和Python（54％）等編程語言。所以在數據挖掘分析領域，SQL是必備技能。
我們分析一下SQL從1970s早期開發出，到現在還經久不衰的原因：
一、公司基本都將數據存儲在關係數據庫管理係統（RDBMS）或關係數據流管理係統（RDSMS）中，所以需要使用SQL來實現訪問。SQL是通用的數據語言，可以使用SQL和幾乎其它任何數據庫進行交互，甚至可以在本地建立自己的數據庫！
二、隻有少量的SQL實現沒有遵循標準，在供應商之間不兼容。因此，了解SQL標準是在數據挖掘分析行業立足的必要要求。
三、最重要的是SQL也被更新的技術所接受，例如Hive或者Spark SQL。Hive是一個用於查詢和管理大型數據集的類似於SQL的查詢語言界麵；Spark SQL可用於執行SQL查詢。
簡而言之，以下就是為什麼你應該學習這種查詢語言：
即使對於新手來說，SQL也很容易學習。學習曲線很平緩，編寫SQ查詢幾乎不花費時間。
SQL遵循“學習一次，隨時隨地可用”的原則，所以花費時間學習SQL很劃算！
SQL是對編程語言的一種極好的補充；在某些情況下，編寫查詢甚至比編寫代碼更為優先！
...
為了提高SQL查詢的性能，首先需要知道，運行查詢時，內部會發生什麼。
查詢執行的過程：
首先，將查詢解析成“解析樹”; 分析查詢是否滿足語法和語義要求。解析器將會創建一個輸入查詢的內部表示，然後將此輸出傳遞給重寫引擎。
然後，優化器的任務是為給定的查詢，尋找最佳執行或查詢計劃。執行計劃準確地定義了每個操作所使用的算法，以及如何協調操作的執行。
最後，為了找到最佳的執行計劃，優化器會列舉所有可能的執行計劃，並確定每個計劃的質量或成本，以便獲取有關當前數據庫狀態的信息，最後選擇最佳的執行計劃。由於查詢優化器可能不完善，因此數據庫用戶和管理員有時需要手動檢查並調整優化器生成的計劃，以便獲得更好的性能。
現在你已經清楚了什麼才是好的執行計劃。
正如前麵了解到的，計劃的成本質量起著重要的作用。更具體地說，評估計劃所需的磁盤I / O數量，計劃的CPU花銷以及數據庫客戶端的整體響應時間和總執行時間等因素至關重要。這就是時間複雜性的概念。後麵還將繼續了解。
接下來，執行所選擇的查詢計劃，由係統的執行引擎進行評估，並返回查詢結果。
需要進一步說明的是，垃圾回收原則（GIGO）原本就是表達在查詢處理和執行之中：製定查詢的人，同時也決定著SQL查詢的性能。
這意味著在編寫查詢，有些事情可以同步去做。就像文章開始時介紹的，編寫查詢需要遵循兩個標準：首先，編寫的查詢需要滿足一定的標準，其次還應該應對查詢中可以出現的性能問題。
總的來說，有四個分句和關鍵字，方便新手考慮性能問題：
WHERE 分句
INNER JOIN 和 LEFT JOIN 關鍵字
HAVING 分句
雖然這種做法簡單而天真，但對於一個初學者來說，這些方法卻是一個很好的指引。這些地方也是你剛開始編寫時，容易發生錯誤的地方，這些錯誤也很難發現。
同時，要想提升性能，使其變得有意義，就不能脫離上下文：在考慮SQL性能時，不能武斷的認為上麵的分句和關鍵字不好。使用WHERE 或 HAVING的分句也可能是很好的查詢語句。
通過下麵的章節來來進一步了解編寫查詢時反向模型和代替方法，並將這些提示和技巧作為指導。如何重寫查詢和是否需要重寫查詢取決於數據量，以及數據庫和執行查詢所需的次數等。這完全取決於你的查詢目標，事先掌握一些有關數據的知識是非常重要的！
1. 僅檢索你需要的數據
在編寫SQL查詢時，並不是數據越多越好。因此在使用SELECT 語句、DISTINCT分句和LIKE操作符時，需要謹慎。
SELECT聲明
在編寫完查詢語句之後，首先需要做的事情就是檢查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;
DISTINCT分句
SELECT DISTINCT 語句用於返回不同的值。 DISTINCT 是一個分句，能不用盡量不用，因為如果將DISTINCT添加到查詢語句中，會導致執行時間的增加 。
LIKE運算符
在查詢中使用LIKE運算符時，如果模式是以% 或_開始，則不會使用索引。它將阻止數據庫使用索引（如果存在的話）。當然，從另一個角度來看，你也可以認為，這種類型的查詢可能會放寬條件，會檢索到許多不一定滿足查詢目標的記錄。
另外，你對存儲在數據中數據的了解，可以幫助你製定一個模式，使用該模式可以對所有數據進行正確的過濾，以便查找到你最想要的數據。
2. 縮小查詢結果
如果無法避免使用 SELECT語句時，可以考慮通過其它方式縮小查詢結果。例如，使用LIMIT 分句和數據類型轉換的方法。
TOP，LIMIT和ROWNUM分句
可以在查詢中添加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;
應該使用最小的數據類型，因為小的數據類型更加有效。
當查詢中需要進行數據類型轉化，會增加執行時間，所以盡可能的避免數據類型轉換的發生；
如果不能避免的話，需要謹慎的定義數據類型的轉換。
反向模型中隱含的事實是，建立查詢時基於集合和程序的方法之間存在著不同。
查詢的程序方法是一種非常類似於編程的方法：你告訴係統需要做些什麼以及如何做。例如上一篇文章中的示例，通過執行一個函數然後調用另一個函數來查詢數據庫，或者使用包含循環、條件和用戶定義函數（UDF）的邏輯方式來獲得最終查詢結果。你會發現通過這種方式，一直在請求一層一層中數據的子集。這種方法也經常被稱為逐步或逐行查詢。
另一種是基於集合的方法，隻需指定需要執行的操作。使用這種方法要做的事情就是，指定你想通過查詢獲得的結果的條件和要求。在檢索數據過程中，你不需要關注實現查詢的內部機製：數據庫引擎會決定最佳的執行查詢的算法和邏輯。
由於 SQL 是基於集合的，所以這種方法比起程序方法更加有效，這也解釋了為什麼在某些情況下，SQL 可以比代碼工作地更快。
基於集合的查詢方法也是數據挖掘分析行業要求你必須掌握的技能！因為你需要熟練的在這兩種方法之間進行切換。如果你發現自己的查詢中存在程序查詢，則應該考慮是否需要重寫這部分。
反向模式不是靜止不變的。在你成為 SQL 開發者的過程中，避免查詢反向模型和重寫查詢可能會是一個很艱難的任務。所以時常需要使用工具以一種更加結構化的方法來優化你的查詢。
對性能的思考不僅需要更結構化的方法，還需要更深入的方法。
然而，這種結構化和深入的方法主要是基於查詢計劃的。查詢計劃首先被解析為“解析樹”並且準確定義了每個操作使用什麼算法以及如何協調操作過程。
在優化查詢時，很可能需要手動檢查優化器生成的計劃。在這種情況下，將需要通過查看查詢計劃來再次分析你的查詢。
要掌握這樣的查詢計劃，你需要使用一些數據庫管理係統提供給你的工具。
你可以使用以下的一些工具：
一些軟件包功能工具可以生成查詢計劃的圖形表示。
其它工具能夠為你提供查詢計劃的文本描述。
請注意，如果你正在使用 PostgreSQL，則可以區分不同的 EXPLAIN，你隻需獲取描述，說明 planner 如何在不運行計劃的情況下執行查詢。同時 EXPLAIN ANALYZE 會執行查詢，並返回給你一個評估查詢計劃與實際查詢計劃的分析報告。一般來說，實際執行計劃會切實的執行這個計劃，而評估執行計劃可以在不執行查詢的情況下，解決這個問題。在邏輯上，實際執行計劃更為有用，因為它包含了執行查詢時，實際發生的其它細節和統計信息。
接下來你將了解 XPLAIN 和 ANALYZE 的更多信息，以及如何使用這兩個命令來進一步了解你的查詢計劃和查詢性能。要做到這一點，你需要開始使用兩個表： one_million 和 half_million 來做一些示例。
你可以借助 EXPLAIN 來檢索 one_million 表的當前信息：確保已將其放在運行查詢的首要位置，在運行完成之後，會返回到查詢計劃中：
EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
_______________________________________
Seq Scan on one_million
(cost=0.00..18584.82 rows=1025082 width=36)
(1 row)
在以上示例中，我們看到查詢的 Cost 是0.00..18584.82 ，行數是1025082，列寬是36。
同時，也可以借助 ANALYZE 來更新統計信息。
ANALYZE one_million; EXPLAIN SELECT * FROM one_million; QUERY PLAN______________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (1 row)
除了 EXPLAIN 和 ANALYZE，你也可以借助 EXPLAIN ANALYZE 來檢索實際執行時間：
EXPLAIN ANALYZE
SELECT *
FROM one_million;
QUERY PLAN
___________________________________________
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(actual time=0.015..1207.019 rows=1000000 loops=1)
Total runtime: 2320.146 ms
(2 rows)
使用 EXPLAIN ANALYZE 的缺點就是需要實際執行查詢，這點值得注意！
到目前為止，我們看到的所有算法是順序掃描或全表掃描：這是一種在數據庫上進行掃描的方法，掃描的表的每一行都是以順序（串行）的順序進行讀取，每一列都會檢查是否符合條件。在性能方麵，順序掃描不是最佳的執行計劃，因為需要掃描整個表。但是如果使用慢磁盤，順序讀取也會很快。
還有一些其它算法的示例：
我們可以看到查詢優化器選擇了 Hash Join。請記住這個操作，因為我們需要使用這個來評估查詢的時間複雜度。我們注意到了上麵示例中沒有 half_million.counter 索引，我們可以在下麵示例中添加索引：
通過創建索引，查詢優化器已經決定了索引掃描時，如何查找 Merge join。
請注意，索引掃描和全表掃描（順序掃描）之間的區別：後者（也稱為“表掃描”）是通過掃描所有數據或索引所有頁麵來查找到適合的結果，而前者隻掃描表中的每一行。
原文發布時間為：2017-09-29
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲”微信公眾號
最後更新：2017-09-30 08:03:50