謂詞下推

謂詞下推

什么是謂詞

在SQL中，謂詞是返回boolean值(即true和false)的函數(shù)，或者是隱式轉(zhuǎn)換為bool的函數(shù)。SQL中的謂詞主要有l(wèi)ike、between、is null、is not null、in、exists等。

什么是謂詞下推

謂詞下推，即將用來過濾的表達(dá)式，盡可能的移動(dòng)至靠近數(shù)據(jù)源的位置，以使真正執(zhí)行的時(shí)候能夠直接跳過無關(guān)的數(shù)據(jù)。

傳統(tǒng)數(shù)據(jù)庫

在傳統(tǒng)數(shù)據(jù)庫中，謂詞下推作為優(yōu)化手段很早就出現(xiàn)了，謂詞下推的目的就是通過將一些過濾條件盡可能的在最底層執(zhí)行，以減少每一層交互的數(shù)據(jù)量，從而提升查詢性能。

例如：

select count(1) from A join B on A.id = B.id where A.a > 10 and B.b<100;

在處理Join操作前，需要先對A和B執(zhí)行TableScan操作，然后再進(jìn)行Join，再進(jìn)行過濾，最后計(jì)算聚合函數(shù)返回。但是，如果把過濾條件 A.a>10 和 B.b<100 分別移到A表的TableScan和B表的TableScan的時(shí)候執(zhí)行，可以大大降低Join操作的輸入數(shù)據(jù)量。

優(yōu)化后的語句如下：

select count(1) from (select * from A where A.a>10) A1 join (select * from B where B.b<100) B1 on A1.id = B1.id;

無論是行式存儲(chǔ)還是列式存儲(chǔ)，都可以將過濾條件在讀取一條記錄后執(zhí)行以判斷是否需要返回給調(diào)用者，在Parquet中做了更進(jìn)一步的優(yōu)化，優(yōu)化的方法是，對每一個(gè)Row Group的每一個(gè)Column Chunk在存儲(chǔ)時(shí)都計(jì)算相應(yīng)的統(tǒng)計(jì)信息，包括該Column Chunk的最大值、最小值和空值個(gè)數(shù)。通過這些統(tǒng)計(jì)值和該列的過濾條件可以判斷該Row Group是否需要掃描。另外Parquet還將增加Bloom Filter和Index優(yōu)化等特性，更加有效的完成謂詞下推。

Hive中的謂詞下推

Predicate Pushdown

在謂詞下推后，過濾條件在map端執(zhí)行，減少了map端的輸出，降低了數(shù)據(jù)在集群上傳輸?shù)牧?，?jié)約了集群的資源，也提升了任務(wù)的性能。

對應(yīng)的參數(shù)配置為：

hive.optimize.pdd = true

參數(shù)默認(rèn)為true，即開啟謂詞下推。

PDD(Predicate Pushdown)規(guī)則

規(guī)則1：

• During Join predicates cannot be pushed past Preserved Row tables.

? ?join條件過濾不能下推到保留行表中。

select s1.key, s2.key from src s1 left join src s2 on s1.key > '2';

在上述SQL語句中，left join中左表s1為保留行表，所以on條件（join過濾條件）不能下推到s1中，而s2表不是保留行表，所以s2.key>2條件可以下推到s2表中：

select s1.key, s2.key from src s1 left join src s2 on s2.key > '2';

規(guī)則2：

• After Join predicates cannot be pushed past Null Supplying tables.

? ?where條件過濾不能下推到NULL補(bǔ)充表。

select s1.key, s2.key from src s1 left join src s2 where s1.key > '2';

在上面的SQL語句中，left join中的右表s2為NULL補(bǔ)充表，s1.key>2可以下推到s1，而由于s2為NULL補(bǔ)充表，所以s2.key>2過濾條件不能下推到s2中:

select s1.key, s2.key from src s1 left join src s2 where s2.key > '2';

總結(jié)

• 對于Join、Full Outer Join，條件寫在on后面還是where后面，性能上沒有區(qū)別

• 對于left join，右側(cè)表的寫在on后面，左側(cè)的表寫在where后面，性能有提高

• 對于right join，左側(cè)的表寫在on后面，右側(cè)的表寫在where后面，性能有提高

• 所謂下推，即謂詞過濾是在map端執(zhí)行；所謂不下推，即謂詞過濾在reduce端執(zhí)行

• 需要注意的是，如果表達(dá)式中含有不確定函數(shù)，整個(gè)表達(dá)式的謂詞都不會(huì)被下推，比如：

  select a.* 
  from a join b on a.id = b.id
  where a.ds = '2019-10-09' and a.create_time = unix_timestamp();

  因?yàn)閡nix_timestamp是不確定函數(shù)，在編譯的時(shí)候無法得知，所以，整個(gè)表達(dá)式不會(huì)被pushed，即ds='2019-10-09'也不會(huì)被提前過濾。類似的不確定函數(shù)還有rand()等。