如何編寫更好的SQL查詢：終極指南-第二部分

如何編寫更好的SQL查詢：終極指南-第二部分

在興國等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都做網(wǎng)站、網(wǎng)站制作 網(wǎng)站設(shè)計制作定制網(wǎng)站開發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,網(wǎng)絡(luò)營銷推廣,成都外貿(mào)網(wǎng)站建設(shè)公司,興國網(wǎng)站建設(shè)費用合理。

上一篇文章中，我們學(xué)習(xí)了 SQL 查詢是如何執(zhí)行的以及在編寫 SQL 查詢語句時需要注意的地方。

下面，我進一步學(xué)習(xí)查詢方法以及查詢優(yōu)化。

 

基于集合和程序的方法進行查詢

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

  • 查詢的程序方法是一種非常類似于編程的方法：你告訴系統(tǒng)需要做些什么以及如何做。例如上一篇文章中的示例，通過執(zhí)行一個函數(shù)然后調(diào)用另一個函數(shù)來查詢數(shù)據(jù)庫，或者使用包含循環(huán)、條件和用戶定義函數(shù)（UDF）的邏輯方式來獲得最終查詢結(jié)果。你會發(fā)現(xiàn)通過這種方式，一直在請求一層一層中數(shù)據(jù)的子集。這種方法也經(jīng)常被稱為逐步或逐行查詢。

  • 另一種是基于集合的方法，只需指定需要執(zhí)行的操作。使用這種方法要做的事情就是，指定你想通過查詢獲得的結(jié)果的條件和要求。在檢索數(shù)據(jù)過程中，你不需要關(guān)注實現(xiàn)查詢的內(nèi)部機制：數(shù)據(jù)庫引擎會決定最佳的執(zhí)行查詢的算法和邏輯。

由于 SQL 是基于集合的，所以這種方法比起程序方法更加有效，這也解釋了為什么在某些情況下，SQL 可以比代碼工作地更快。

基于集合的查詢方法也是數(shù)據(jù)挖掘分析行業(yè)要求你必須掌握的技能！因為你需要熟練的在這兩種方法之間進行切換。如果你發(fā)現(xiàn)自己的查詢中存在程序查詢，則應(yīng)該考慮是否需要重寫這部分。

 

從查詢到執(zhí)行計劃 

反向模式不是靜止不變的。在你成為 SQL 開發(fā)者的過程中，避免查詢反向模型和重寫查詢可能會是一個很艱難的任務(wù)。所以時常需要使用工具以一種更加結(jié)構(gòu)化的方法來優(yōu)化你的查詢。

對性能的思考不僅需要更結(jié)構(gòu)化的方法，還需要更深入的方法。

然而，這種結(jié)構(gòu)化和深入的方法主要是基于查詢計劃的。查詢計劃首先被解析為“解析樹”并且準確定義了每個操作使用什么算法以及如何協(xié)調(diào)操作過程。

 

查詢優(yōu)化

在優(yōu)化查詢時，很可能需要手動檢查優(yōu)化器生成的計劃。在這種情況下，將需要通過查看查詢計劃來再次分析你的查詢。

要掌握這樣的查詢計劃，你需要使用一些數(shù)據(jù)庫管理系統(tǒng)提供給你的工具。你可以使用以下的一些工具：

  • 一些軟件包功能工具可以生成查詢計劃的圖形表示。

  • 其它工具能夠為你提供查詢計劃的文本描述。

請注意，如果你正在使用 PostgreSQL，則可以區(qū)分不同的 EXPLAIN，你只需獲取描述，說明 planner 如何在不運行計劃的情況下執(zhí)行查詢。同時 EXPLAIN ANALYZE 會執(zhí)行查詢，并返回給你一個評估查詢計劃與實際查詢計劃的分析報告。一般來說，實際執(zhí)行計劃會切實的執(zhí)行這個計劃，而評估執(zhí)行計劃可以在不執(zhí)行查詢的情況下，解決這個問題。在邏輯上，實際執(zhí)行計劃更為有用，因為它包含了執(zhí)行查詢時，實際發(fā)生的其它細節(jié)和統(tǒng)計信息。

接下來你將了解 XPLAIN 和 ANALYZE 的更多信息，以及如何使用這兩個命令來進一步了解你的查詢計劃和查詢性能。要做到這一點，你需要開始使用兩個表： one_million 和 half_million 來做一些示例。

你可以借助 EXPLAIN 來檢索 one_million 表的當前信息：確保已將其放在運行查詢的首要位置，在運行完成之后，會返回到查詢計劃中：

EXPLAINSELECT *
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 ，行數(shù)是1025082，列寬是36。

同時，也可以借助 ANALYZE 來更新統(tǒng)計信息  。

ANALYZE one_million;
EXPLAINSELECT *
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 來檢索實際執(zhí)行時間：

EXPLAIN ANALYZESELECT *
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 的缺點就是需要實際執(zhí)行查詢，這點值得注意！

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

還有一些其它算法的示例：

EXPLAIN ANALYZESELECT *
FROM one_million JOIN half_millionON 
(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

我們可以看到查詢優(yōu)化器選擇了 Hash Join。請記住這個操作，因為我們需要使用這個來評估查詢的時間復(fù)雜度。我們注意到了上面示例中沒有 half_million.counter 索引，我們可以在下面示例中添加索引  ：

CREATE INDEX ON half_million(counter);
EXPLAIN ANALYZESELECT *
FROM one_million JOIN half_millionON 
(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)

通過創(chuàng)建索引，查詢優(yōu)化器已經(jīng)決定了索引掃描時，如何查找 Merge join。

請注意，索引掃描和全表掃描（順序掃描）之間的區(qū)別：后者（也稱為“表掃描”）是通過掃描所有數(shù)據(jù)或索引所有頁面來查找到適合的結(jié)果，而前者只掃描表中的每一行。

 

教程的第二部分內(nèi)容，就介紹到這里。后續(xù)還會有《如何編寫更好的SQL查詢》系列的最后一篇文章，敬請期待。

原文鏈接：http://www.kdnuggets.com/2017/08/write-better-sql-queries-definitive-guide-part-2.html

轉(zhuǎn)載請注明出自：葡萄城控件

本文名稱：如何編寫更好的SQL查詢：終極指南-第二部分
文章出自：http://muchs.cn/article24/pidsce.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、云服務(wù)器、用戶體驗、搜索引擎優(yōu)化、網(wǎng)站設(shè)計公司、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

h5響應(yīng)式網(wǎng)站建設(shè)