?
This document uses PHP Chinese website manual Release
我們可以在一定程度上用明確的 JOIN 語法控制查詢規(guī)劃器。 要明白為什么有這茬事,我們首先需要一些背景知識(shí)。
在簡單的連接查詢里,比如
SELECT * FROM a,b,c WHERE a.id = b.id AND b.ref = c.id;
規(guī)劃器可以按照任何順序自由地連接給出的表。 比如,它可以生成一個(gè)查詢規(guī)劃先用WHERE條件a.id = b.id把 A 連接到 B, 然后用另外一個(gè)WHERE條件把 C 連接到這個(gè)表上來,或者也可以先連接 B 和 C 然后再連接 A,同樣得到這個(gè)結(jié)果。 或者也可以連接 A 到 C 然后把結(jié)果與 B 連接,不過這么做效率比較差, 因?yàn)楸仨毶赏暾?A 和 C 的迪卡爾積 而在查詢里沒有可用的WHERE子句可以優(yōu)化該連接(PostgreSQL執(zhí)行器里的所有連接都發(fā)生在兩個(gè)輸入表之間, 所以在這種情況下它必須先得出一個(gè)結(jié)果)。 重要的一點(diǎn)是這些連接方式給出語義上相同的結(jié)果,但在執(zhí)行開銷上卻可能有巨大的差別。 因此,規(guī)劃器會(huì)對它們進(jìn)行檢查并找出最高效的查詢規(guī)劃。
如果查詢只涉及兩或三個(gè)表,那么在查詢里不會(huì)有太多需要考慮的連接。 但是潛在的連接順序的數(shù)目隨著表數(shù)目的增加程指數(shù)增加的趨勢。 當(dāng)超過十個(gè)左右的表以后,實(shí)際上根本不可能對所有可能做一次窮舉搜索,甚至對六七個(gè)表都需要相當(dāng)長的時(shí)間進(jìn)行規(guī)劃。 如果有太多輸入的表,PostgreSQL 規(guī)劃器將從窮舉搜索切換為基因概率搜索, 以減少可能性數(shù)目(樣本空間)。 切換的閾值是用運(yùn)行時(shí)參數(shù)geqo_threshold設(shè)置的。 基因搜索花的時(shí)間少,但是并不一定能找到最好的規(guī)劃。
當(dāng)查詢涉及外部連接時(shí),規(guī)劃器就不像對付普通(內(nèi)部)連接那么自由了。 比如,看看下面這個(gè)查詢
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
盡管這個(gè)查詢的約束和前面一個(gè)非常相似,但它們的語義卻不同, 因?yàn)槿绻?A 里有任何一行不能匹配B和C的連接里的行,那么該行都必須輸出。 因此這里規(guī)劃器對連接順序沒有什么選擇:它必須先連接 B 到 C,然后把 A 連接到該結(jié)果上。 因此,這個(gè)查詢比前面一個(gè)花在規(guī)劃上的時(shí)間少。 在其它情況下,規(guī)劃器就有可能確定多種連接順序都是安全的。 比如,對于:
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
將 A 首先連接到 B 或 C 都是有效的。 當(dāng)前,只有FULL JOIN完全強(qiáng)制連接順序。 大多數(shù)LEFT JOIN或RIGHT JOIN都可以在某種程度上重新排列。
明確的連接語法(INNER JOIN,CROSS JOIN,或無修飾的JOIN)語義上和FROM中列出輸入關(guān)系是一樣的, 因此我們沒有必要約束連接順序。
即使大多數(shù)JOIN并不完全強(qiáng)迫連接順序, 但仍然可以明確的告訴PostgreSQL查詢規(guī)劃器JOIN子句的連接順序。 比如,下面三個(gè)查詢邏輯上是等效的
SELECT * FROM a,b,c WHERE a.id = b.id AND b.ref = c.id; SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id; SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
但如果我們告訴規(guī)劃器遵循JOIN的順序,那么第二個(gè)和第三個(gè)還是要比第一個(gè)花在規(guī)劃上的時(shí)間少。 這個(gè)作用對于只有三個(gè)表的連接而言是微不足道的,但對于數(shù)目眾多的表,可能就是救命稻草了
要強(qiáng)制規(guī)劃器遵循準(zhǔn)確的 JOIN連接順序, 我們可以把運(yùn)行時(shí)參數(shù)join_collapse_limit設(shè)置為 1(其它可能的數(shù)值在下面討論)。
你完全不必為了縮短搜索時(shí)間來約束連接順序, 因?yàn)樵谝粋€(gè)簡單的FROM列表里使用JOIN操作符就很好了。 比如考慮:
SELECT * FROM a CROSS JOIN b,c,d,e WHERE ...;
如果設(shè)置join_collapse_limit= 1,那么這句話就相當(dāng)于強(qiáng)迫規(guī)劃器先把A連接到B, 然后再連接到其它的表上,但并不約束其它的選擇。 在本例中,可能的連接順序的數(shù)目減少了 5 倍。
按照上面的想法考慮規(guī)劃器的搜索問題是一個(gè)很有用的技巧,不管是對減少規(guī)劃時(shí)間還是對引導(dǎo)規(guī)劃器生成好的規(guī)劃都很有幫助。 如果缺省時(shí)規(guī)劃器選擇了一個(gè)糟糕的連接順序,你可以用 JOIN 語法強(qiáng)迫它選擇一個(gè)更好的(假設(shè)知道一個(gè)更好的順序)。 所以我們建議多試驗(yàn)。
一個(gè)非常相近的影響規(guī)劃時(shí)間的問題是把子查詢壓縮到它們的父查詢里面。 比如,考慮下面的查詢
SELECT * FROM x,y, (SELECT * FROM a,b,c WHERE something) AS ss WHERE somethingelse;
這個(gè)情況可能在那種包含連接的視圖中出現(xiàn); 該視圖的SELECT規(guī)則將被插入到引用視圖的場合,生成非常類似上面的查詢。 通常,規(guī)劃器會(huì)試圖把子查詢壓縮到父查詢里,生成
SELECT * FROM x,y,a,b,c WHERE something AND somethingelse;
這樣通常會(huì)生成一個(gè)比獨(dú)立的子查詢更好些的規(guī)劃 比如,外層的WHERE條件可能先把X連接到 A 上,這樣就消除了 A 中的許多行, 因此避免了形成全部子查詢邏輯輸出的需要。但是同時(shí),我們增加了規(guī)劃的時(shí)間; 在這里,我們有一個(gè)用五路連接代替兩個(gè)獨(dú)立的三路連接的問題,這樣的差距是巨大的,因?yàn)榭赡艿囊?guī)劃數(shù)的是按照指數(shù)增長的。 規(guī)劃器將在父查詢可能超過from_collapse_limit個(gè)FROM項(xiàng)的時(shí)候, 不再壓縮子查詢,以此來避免巨大的連接搜索數(shù)。 你可以通過調(diào)整這個(gè)運(yùn)行時(shí)參數(shù)來在規(guī)劃時(shí)間和規(guī)劃質(zhì)量之間作出平衡
from_collapse_limit和join_collapse_limit名字類似是因?yàn)樗麄冏龅氖虑閹缀跸嗤? 一個(gè)控制規(guī)劃器何時(shí)把子查詢"平面化",另外一個(gè)控制何時(shí)把明確的連接平面化。 通常,你要么把join_collapse_limit設(shè)置成和from_collapse_limit一樣(明確連接和子查詢的行為類似), 要么把join_collapse_limit設(shè)置為 1(如果你想用明確連接控制連接順序)。 但是你可以把它們設(shè)置成不同的值,這樣你就可以在規(guī)劃時(shí)間和運(yùn)行時(shí)間之間進(jìn)行仔細(xì)的調(diào)節(jié)。