PostgreSQL7.0手冊(cè)-用戶手冊(cè)-17. 理解性能
2019-09-08 23:33:07
供稿：網(wǎng)友
第十七章. 理解性能
查詢性能可能受許多因素的影響．這些因素里面有一些是可以由用戶控制的，而其它因素取決于他所用的（數(shù)據(jù)庫(kù)）系統(tǒng)．
有一些性能因素，比如索引的創(chuàng)建和海量數(shù)據(jù)的裝載在其他地方討論．本章將討論 EXPLAIN 命令，然后將展示一個(gè)查詢的細(xì)節(jié)是如何影響查詢規(guī)劃進(jìn)而影響整體性能的．
使用 EXPLAIN
作者：由 Tom Lane 寫(xiě)做，源于 2000-03-27 的電子郵件．
規(guī)劃分析是一門(mén)值得寫(xiě)一個(gè)教程的學(xué)問(wèn)，而我沒(méi)有足夠的時(shí)間寫(xiě)呢么一個(gè)．這里是我寫(xiě)的一些簡(jiǎn)短的不完善的解釋?zhuān)?
目前被 EXPLAN 引用的數(shù)字是：
預(yù)計(jì)的啟動(dòng)開(kāi)銷(xiāo)（在輸出掃描開(kāi)始之前消耗的時(shí)間，比如，在一個(gè) SORT 節(jié)點(diǎn)里做排續(xù)的時(shí)間）．
預(yù)計(jì)的總開(kāi)銷(xiāo)（如果所有的記錄都被檢索的話，不過(guò)很可能不是這樣 --- 比如 LIMIT 將在總開(kāi)銷(xiāo)的一小部分就停止）．
預(yù)計(jì)的這個(gè)規(guī)劃節(jié)點(diǎn)輸出的行數(shù)．
預(yù)計(jì)的這個(gè)規(guī)劃節(jié)點(diǎn)的行的平均寬度（以字節(jié)計(jì)算）．
開(kāi)銷(xiāo)是以磁盤(pán)頁(yè)面的存取為單位計(jì)算的．（預(yù)計(jì)的 CPU 處理用一些非常隨意的捏造的權(quán)值被轉(zhuǎn)換成磁盤(pán)頁(yè)面單位。如果你想試驗(yàn)這些東西，請(qǐng)參閱 SET 的手冊(cè)頁(yè)。）有一點(diǎn)很重要：那就是一個(gè)上層節(jié)點(diǎn)的開(kāi)銷(xiāo)包括它的所有子節(jié)點(diǎn)的開(kāi)銷(xiāo)。還有一點(diǎn)也很重要：就是這個(gè)開(kāi)銷(xiāo)只反映規(guī)劃器/優(yōu)化器關(guān)心的東西。尤其是開(kāi)銷(xiāo)沒(méi)有把結(jié)果記錄傳遞給前端的時(shí)間考慮進(jìn)去 --- 這個(gè)時(shí)間可能在真正的總時(shí)間里面占據(jù)相當(dāng)重要的位置，但是被規(guī)劃器忽略了，因?yàn)樗鼰o(wú)法通過(guò)修改規(guī)劃來(lái)改變之。（我們相信，每個(gè)正確的規(guī)劃都將輸出同樣的記錄集。）
輸出行有一點(diǎn)小技巧，因?yàn)檩敵龅男胁皇潜徊樵兲幚?掃描過(guò)的行 --- 通常輸出行會(huì)少一些，反映應(yīng)用于此節(jié)點(diǎn)上的任意 WHERE 子句的選擇性計(jì)算。
平均寬度是相當(dāng)虛的東西，因?yàn)樗鼘?shí)際上對(duì)變長(zhǎng)度列沒(méi)有任何認(rèn)識(shí)。我正在考慮將來(lái)改進(jìn)這些東西，但是也可能不值得這樣做，因?yàn)閷挾扔玫牟皇呛芏唷！?
下面是幾個(gè)例子（用的是經(jīng)過(guò)清理（vacuum）分析后的蛻變測(cè)試數(shù)據(jù)庫(kù)以及接近完成的 7.0 代碼）：
regression=# explain select * from tenk1;
NOTICE: QUERY PLAN:
Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)
這個(gè)例子就象例子本身一樣直接了當(dāng)。如果你做一個(gè)
select * from pg_class where relname = 'tenk1';
你會(huì)發(fā)現(xiàn) tenk1 有 233 磁盤(pán)頁(yè)面和 10000 行記錄。因此開(kāi)銷(xiāo)計(jì)算為 233 塊讀取，定義為每塊 1.0，加上 10000 * cpu_tuple_cost，目前是 0.01 （用命令 show cpu_tuple_cost 查看）。
現(xiàn)在讓我們修改查詢并增加條件子句：
regression=# explain select * from tenk1 where unique1 < 1000;
NOTICE: QUERY PLAN:
Seq Scan on tenk1 (cost=0.00..358.00 rows=1000 width=148)
輸出行的計(jì)算降低了，因?yàn)橛小HERE 子句。（這里的讓人驚訝的預(yù)計(jì)計(jì)算只是因?yàn)椤enk1 是一個(gè)非常簡(jiǎn)單的例子 --- unique1 列有 10000 條獨(dú)立的值，范圍從 0 到 9999，因此計(jì)算器在列數(shù)值的最大值和最小值之間的線性插值沒(méi)什么用。）不過(guò)，這次掃描仍然需要訪問(wèn)所有 10000 行，因此開(kāi)銷(xiāo)沒(méi)有降低；實(shí)際上還增加了一些，以反映為了檢查 WHERE 條件多用的 CPU 時(shí)間。
把查詢修改為限制條件更嚴(yán)格：
regression=# explain select * from tenk1 where unique1 < 100;
NOTICE: QUERY PLAN:
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.35 rows=100 width=148)
這時(shí)你會(huì)看到，如果我們把 WHERE 條件變得足夠有選擇性，規(guī)劃器將最終決定一次索引掃描將比一次順序掃描快。因?yàn)樗饕@個(gè)規(guī)劃將只需要訪問(wèn) 100 條記錄，因此盡管每條記錄單獨(dú)的抓取開(kāi)銷(xiāo)比較大，它（這個(gè)查詢規(guī)劃）還是勝出。
向條件里面增加另外一個(gè)條件：
regression=# explain select * from tenk1 where unique1 < 100 and
regression-# stringu1 = 'xxx';
NOTICE: QUERY PLAN:
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.60 rows=1 width=148)
新增的子句 "stringu1 = 'xxx'" 減少了預(yù)計(jì)的輸出行，但是沒(méi)有減少開(kāi)銷(xiāo)，因?yàn)槲覀內(nèi)匀恍枰L問(wèn)相同的記錄集。
讓我們?cè)囍褂梦覀兩厦嬗懻摰臄?shù)據(jù)域聯(lián)接兩個(gè)表：
regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..144.07 rows=100 width=296)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..89.35 rows=100 width=148)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..0.53 rows=1 width=148)
在這個(gè)嵌套循環(huán)聯(lián)接里，外層掃描和我們上面舉例的是一樣的，因此它的開(kāi)銷(xiāo)和行數(shù)是一樣的，因?yàn)槲覀儗?duì)那個(gè)節(jié)點(diǎn)應(yīng)用了 "unique1 < 100" WHERE 子句。"t1.unique2 = t2.unique2" 這時(shí)還不相關(guān)，因此它沒(méi)有影響外層掃描的行計(jì)數(shù)。對(duì)于內(nèi)層掃描，目前的外層掃描的記錄的 unique2 值被插入到內(nèi)層索引掃描以生成一個(gè)象 "t2.unique2 = constant" 這樣的索引查詢。因此我們得到與我們想要的， 類(lèi)似 "explain select * from tenk2 where unique2 = 42" 這個(gè)查詢同樣的內(nèi)層掃描規(guī)劃和開(kāi)銷(xiāo)。然后再以外層掃描的開(kāi)銷(xiāo)為基礎(chǔ)設(shè)置循環(huán)節(jié)點(diǎn)的開(kāi)銷(xiāo)，加上一個(gè)為每個(gè)外層掃描重復(fù)的內(nèi)層掃描（這里是 100 * 0.53），加上一點(diǎn)點(diǎn)處理聯(lián)接的 CPU 時(shí)間。
在這個(gè)例子里，循環(huán)的輸出行數(shù)與兩個(gè)掃描的行數(shù)的乘積相同，但是通常并不是這樣的，因?yàn)橥ǔＤ銜?huì)有提及兩個(gè)關(guān)系的 WHERE 子句，因此只影響聯(lián)接的部分，而對(duì)兩者的輸入掃描均不影響。例如，如果我們加一條 "WHERE ... AND t1.hundred < t2.hundred"，將減少輸出行數(shù)，但是不會(huì)修改任何一個(gè)輸入掃描。
我們可以看一下我們強(qiáng)制規(guī)劃器忽視它認(rèn)為優(yōu)秀的（掃描）策略（這還是相當(dāng)原始的工具，但是它是我們目前能夠用上的東西）的結(jié)果：
regression=# set enable_nestloop = off;
SET VARIABLE
regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE: QUERY PLAN:
Hash Join (cost=89.60..574.10 rows=100 width=296)
-> Seq Scan on tenk2 t2
(cost=0.00..333.00 rows=10000 width=148)
-> Hash (cost=89.35..89.35 rows=100 width=148)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..89.35 rows=100 width=148)
這個(gè)規(guī)劃仍然試圖用同樣的索引掃描從 tenk1 里面取出感興趣的 100 行，把它們藏在一個(gè)在內(nèi)存里的散列（哈希）表里，然后對(duì) tenk2 做一次順序掃描，在每一條 tenk2 記錄上檢測(cè)上面的散列（哈希）表尋找可能匹配 "t1.unique2 = t2.unique2" 的記錄。讀取 tenk1 和建立散列表是此哈希聯(lián)接的全部啟動(dòng)開(kāi)銷(xiāo)，因?yàn)槲覀冊(cè)陂_(kāi)始讀取 tenk2 之前不可能獲得任何輸出記錄。這個(gè)聯(lián)接的總的預(yù)計(jì)時(shí)間同樣還包括相當(dāng)重的檢測(cè)散列（哈希）表 10000 次的 CPU 時(shí)間。不過(guò)，請(qǐng)注意，我們不需要對(duì) 89.35 乘 10000；散列（哈希）表的在這個(gè)規(guī)劃類(lèi)型中只需要設(shè)置一次。
-----------------------------------------------------------------------------
