如何編寫更好的SQL查詢:終極指南-第二部分
上一篇文章中,我們學習了 SQL 查詢是如何執行的以及在編寫 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
_________________________________________________
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 的缺點就是需要實際執行查詢,這點值得注意!
到目前為止,我們看到的所有演算法是順序掃描或全表掃描:這是一種在資料庫上進行掃描的方法,掃描的表的每一行都是以順序(序列)的順序進行讀取,每一列都會檢查是否符合條件。在效能方面,順序掃描不是最佳的執行計劃,因為需要掃描整個表。但是如果使用慢磁碟,順序讀取也會很快。
還有一些其它演算法的示例:
EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN
_____________________________________________________________ Hash Join (cost=15417.00..68831.00 rows=500000 width=42) (actual time=1241.471..5912.553 rows=500000 loops=1) Hash Cond: (one_million.counter = half_million.counter)
-> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1)
-> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB
-> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.008..601.128 rows=500000 loops=1) Total runtime: 6468.337 ms
我們可以看到查詢最佳化器選擇了 Hash Join。請記住這個操作,因為我們需要使用這個來評估查詢的時間複雜度。我們注意到了上面示例中沒有 half_million.counter 索引,我們可以在下面示例中新增索引 :
CREATE INDEX ON half_million(counter); EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN ______________________________________________________________ Merge Join (cost=4.12..37650.65 rows=500000 width=42) (actual time=0.033..3272.940 rows=500000 loops=1) Merge Cond: (one_million.counter = half_million.counter)
-> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1)
-> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5) (actual time=0.010..683.674 rows=500000 loops=1) Total runtime: 3833.310 ms (5 rows)
透過建立索引,查詢最佳化器已經決定了索引掃描時,如何查詢 Merge join。
請注意,索引掃描和全表掃描(順序掃描)之間的區別:後者(也稱為“表掃描”)是透過掃描所有資料或索引所有頁面來查詢到適合的結果,而前者只掃描表中的每一行。
教程的第二部分內容,就介紹到這裡。後續還會有《如何編寫更好的SQL查詢》系列的最後一篇文章,敬請期待。
原文連結:
轉載請註明出自:葡萄城控制元件
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29734436/viewspace-2147709/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何編寫更好的SQL查詢:終極指南-第三部分SQL
- 如何編寫更好的SQL查詢:終極指南-第一部分SQL
- 【譯】SQL 指引:如何寫出更好的查詢SQL
- [譯] Go 終極指南:編寫一個 Go 工具Go
- 【譯】如何更好的編寫CSSCSS
- 如何更好的編寫async函式函式
- 使用正規表示式編寫更好的 SQLSQL
- 使用正規表示式編寫更好的SQLSQL
- ChatGPT的終極指南概要ChatGPT
- 編寫更好的CSSCSS
- Angular CLI 終極指南Angular
- 編寫一個 SQL 查詢來實現分數排名。SQL
- 必須知道的SQL編寫技巧,多條件查詢不拼字串的寫法SQL字串
- 優化SQL查詢:如何寫出高效能SQL語句優化SQL
- 如何最有效的編寫SQLSQL
- nmap終極使用指南
- Java日誌終極指南Java
- A/B測試終極指南
- Bug Bounty平臺的終極指南
- 【人物弧光編寫討論】消極弧——第二幕
- GitHub終極指南,教你如何在GitHub中“挖礦”Github
- 編寫更好的CSS程式碼CSS
- sql查詢是如何執行的?SQL
- T-SQL進階:超越基礎 Level 2:編寫子查詢SQL
- 編寫 SQL 查詢:讓我們從基礎知識開始SQL
- [譯] 如何使用 Pandas 重寫你的 SQL 查詢以及其他操作SQL
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- SQL查詢的:子查詢和多表查詢SQL
- CSS居中對齊終極指南CSS
- UI設計終極配色指南UI
- FFmpeg - 終極指南 | IMG.LY
- Linux 日誌終極指南Linux
- Android APP 終極瘦身指南AndroidAPP
- oracle資料庫sql查詢檢視第二次查詢很慢Oracle資料庫SQL
- VR進階終極指南:三種級別如何選?VR
- 一文終結SQL 子查詢優化SQL優化
- 編寫更好的C#程式碼C#
- MongoDB 如何支援類 SQL 查詢MongoDBSQL