終極指南－如何寫出更好的SQL查詢？

基於集合和程式的方法進行查詢

反向模型中隱含的事實是，建立查詢時基於集合和程序的方法之間存在著不同。

• 查詢的程式方法是一種非常類似於程式設計的方法：你告訴系統需要做些什麼以及如何做。例如上一篇文章中的範例，透過執行函數然後呼叫另一個函數來查詢資料庫，或使用包含循環、條件和使用者定義函數（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
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
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
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
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
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
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 的缺點就是需要實際執行查詢，這一點值得注意！

到目前為止，我們看到的所有演算法都是順序掃描或全表掃描：這是一種在資料庫上進行掃描的方法，掃描的表的每一行都是以順序（串行）的順序進行讀取，每一列都會檢查是否符合條件。在效能方面，順序掃描不是最佳的執行計劃，因為需要掃描整個表格。但是如果使用慢磁碟，順序讀取也會很快。

還有一些其它演算法的範例：

EXPLAIN ANALYZE
SELECT *
FROM one<span class="hljs-emphasis">_million JOIN half_</span>million
ON (one<span class="hljs-emphasis">_million.counter=half_</span>million.counter);
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
(actual time=1241.471..5912.553 rows=500000 loops=1)
Hash Cond: (one<span class="hljs-emphasis">_million.counter = half_</span>million.counter)
<span class="hljs-code">    -> Seq Scan on one_million</span>
<span class="hljs-code">    (cost=0.00..18334.00 rows=1000000 width=37)</span>
<span class="hljs-code">    (actual time=0.007..1254.027 rows=1000000 loops=1)</span>
<span class="hljs-code">    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)</span>
<span class="hljs-code">    (actual time=1241.251..1241.251 rows=500000 loops=1)</span>
<span class="hljs-code">    Buckets: 4096 Batches: 16 Memory Usage: 770kB</span>
<span class="hljs-code">    -> Seq Scan on half_million</span>
<span class="hljs-code">    (cost=0.00..7213.00 rows=500000 width=5)</span>
(actual time=0.008..601.128 rows=500000 loops=1)
Total runtime: 6468.337 ms

我們可以看到查詢優化器選擇了 Hash Join。請記住這個操作，因為我們需要使用這個來評估查詢的時間複雜度。我們注意到了上面範例中沒有 half_million.counter 索引，我們可以在下面範例中新增索引  ：

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">ON</span> half_million(counter);
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">ANALYZE</span>
<span class="hljs-keyword">SELECT</span> *
<span class="hljs-keyword">FROM</span> one_million <span class="hljs-keyword">JOIN</span> half_million
<span class="hljs-keyword">ON</span> (one_million.counter=half_million.counter);
QUERY PLAN
______________________________________________________________
<span class="hljs-keyword">Merge</span> <span class="hljs-keyword">Join</span> (<span class="hljs-keyword">cost</span>=<span class="hljs-number">4.12</span>.<span class="hljs-number">.37650</span><span class="hljs-number">.65</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">42</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.033</span>.<span class="hljs-number">.3272</span><span class="hljs-number">.940</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
<span class="hljs-keyword">Merge</span> Cond: (one_million.counter = half_million.counter)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> one_million_counter_idx <span class="hljs-keyword">on</span> one_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.32129</span><span class="hljs-number">.34</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">1000000</span> width=<span class="hljs-number">37</span>)
    (actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.011</span>.<span class="hljs-number">.694</span><span class="hljs-number">.466</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500001</span> loops=<span class="hljs-number">1</span>)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> half_million_counter_idx <span class="hljs-keyword">on</span> half_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.14120</span><span class="hljs-number">.29</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">5</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.010</span>.<span class="hljs-number">.683</span><span class="hljs-number">.674</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
Total runtime: <span class="hljs-number">3833.310</span> ms
(<span class="hljs-number">5</span> <span class="hljs-keyword">rows</span>)

透過建立索引，查詢最佳化器已經決定了索引掃描時，如何找到 Merge join。

請注意，索引掃描和全表掃描（順序掃描）之間的區別：後者（也稱為「表掃描」）是透過掃描所有資料或索引所有頁面來查找到適合的結果，而前者只掃描表中的每一行。

教學的第二部分內容，就介紹到這裡。後續還會有《如何寫出更好的SQL查詢》系列的最後一篇文章，敬請期待。

轉載請註明出自：葡萄城控制

以上是終極指南－如何寫出更好的SQL查詢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！