[[202756]]

基于集合和程序的方法進(jìn)行查詢

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

• 查詢的程序方法是一種非常類(lèi)似于編程的方法：你告訴系統(tǒng)需要做些什么以及如何做。例如上一篇文章中的示例，通過(guò)執(zhí)行一個(gè)函數(shù)然后調(diào)用另一個(gè)函數(shù)來(lái)查詢數(shù)據(jù)庫(kù)，或者使用包含循環(huán)、條件和用戶定義函數(shù)(UDF)的邏輯方式來(lái)獲得最終查詢結(jié)果。你會(huì)發(fā)現(xiàn)通過(guò)這種方式，一直在請(qǐng)求一層一層中數(shù)據(jù)的子集。這種方法也經(jīng)常被稱為逐步或逐行查詢。
• 另一種是基于集合的方法，只需指定需要執(zhí)行的操作。使用這種方法要做的事情就是，指定你想通過(guò)查詢獲得的結(jié)果的條件和要求。在檢索數(shù)據(jù)過(guò)程中，你不需要關(guān)注實(shí)現(xiàn)查詢的內(nèi)部機(jī)制：數(shù)據(jù)庫(kù)引擎會(huì)決定最佳的執(zhí)行查詢的算法和邏輯。

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

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

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

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

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

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

查詢優(yōu)化

在優(yōu)化查詢時(shí)，很可能需要手動(dòng)檢查優(yōu)化器生成的計(jì)劃。在這種情況下，將需要通過(guò)查看查詢計(jì)劃來(lái)再次分析你的查詢。

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

• 一些軟件包功能工具可以生成查詢計(jì)劃的圖形表示。
• 其它工具能夠?yàn)槟闾峁┎樵冇?jì)劃的文本描述。

請(qǐng)注意，如果你正在使用 PostgreSQL，則可以區(qū)分不同的 EXPLAIN，你只需獲取描述，說(shuō)明 planner 如何在不運(yùn)行計(jì)劃的情況下執(zhí)行查詢。同時(shí) EXPLAIN ANALYZE 會(huì)執(zhí)行查詢，并返回給你一個(gè)評(píng)估查詢計(jì)劃與實(shí)際查詢計(jì)劃的分析報(bào)告。一般來(lái)說(shuō)，實(shí)際執(zhí)行計(jì)劃會(huì)切實(shí)的執(zhí)行這個(gè)計(jì)劃，而評(píng)估執(zhí)行計(jì)劃可以在不執(zhí)行查詢的情況下，解決這個(gè)問(wèn)題。在邏輯上，實(shí)際執(zhí)行計(jì)劃更為有用，因?yàn)樗藞?zhí)行查詢時(shí)，實(shí)際發(fā)生的其它細(xì)節(jié)和統(tǒng)計(jì)信息。

接下來(lái)你將了解 XPLAIN 和 ANALYZE 的更多信息，以及如何使用這兩個(gè)命令來(lái)進(jìn)一步了解你的查詢計(jì)劃和查詢性能。要做到這一點(diǎn)，你需要開(kāi)始使用兩個(gè)表： one_million 和 half_million 來(lái)做一些示例。

你可以借助 EXPLAIN 來(lái)檢索 one_million 表的當(dāng)前信息：確保已將其放在運(yùn)行查詢的首要位置，在運(yùn)行完成之后，會(huì)返回到查詢計(jì)劃中：

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

同時(shí)，也可以借助 ANALYZE 來(lái)更新統(tǒng)計(jì)信息 。

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 來(lái)檢索實(shí)際執(zhí)行時(shí)間：

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 的缺點(diǎn)就是需要實(shí)際執(zhí)行查詢，這點(diǎn)值得注意!

到目前為止，我們看到的所有算法是順序掃描或全表掃描：這是一種在數(shù)據(jù)庫(kù)上進(jìn)行掃描的方法，掃描的表的每一行都是以順序(串行)的順序進(jìn)行讀取，每一列都會(huì)檢查是否符合條件。在性能方面，順序掃描不是最佳的執(zhí)行計(jì)劃，因?yàn)樾枰獟呙枵麄€(gè)表。但是如果使用慢磁盤(pán)，順序讀取也會(huì)很快。

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

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  

我們可以看到查詢優(yōu)化器選擇了 Hash Join。請(qǐng)記住這個(gè)操作，因?yàn)槲覀冃枰褂眠@個(gè)來(lái)評(píng)估查詢的時(shí)間復(fù)雜度。我們注意到了上面示例中沒(méi)有 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)  

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

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