作者：京東物流 向往

一、背景

從事數(shù)據(jù)開發(fā)將近四年，過程中有大量任務(wù)交接或閱讀同事代碼的場景。在這些場景中發(fā)現(xiàn)有些SQL讀起來賞心悅目，可以一目了然地了解業(yè)務(wù)邏輯，一些復(fù)雜的業(yè)務(wù)需求實(shí)現(xiàn)方法也可以做到簡潔優(yōu)雅，同時在性能上也有良好表現(xiàn)。而有些SQL讀起來非常艱難，時常要跨越幾百行尋找WHERE條件或者關(guān)聯(lián)字段，甚至充斥著大量相同的子查詢命名，除了作者可能少有人能快速看懂。

為此，基于個人經(jīng)驗(yàn)、理解與實(shí)踐，我總結(jié)了一些方法和技巧，能讓SQL盡量變得優(yōu)雅，即兼顧代碼可讀性和執(zhí)行性能兩方面的提升。

二、方法與技巧

1.子查詢與謂詞下推

很多同事在寫關(guān)聯(lián)邏輯時，習(xí)慣于直接將原表關(guān)聯(lián)，隨后在最下方用一大段WHERE語句進(jìn)行條件過濾，如下示例：

// -------------------- Bad Codes ------------------------

SELECT
    f1.pin,
    c1.site_id,
    c2.site_name
FROM
    fdm.fdm1 AS f1
LEFT JOIN cdm.cdm1 AS c1
ON
    f1.erp = lower(c1.account_number)
LEFT JOIN cdm.cdm2 AS c2
ON
    c1.site_id = c2.site_code
WHERE
    f1.start_date <= '""" + start_date + """'
    AND f1.end_date > '""" + start_date + """'
    AND f1.status = 1
    AND c1.dt = '""" + start_date + """'
    AND c2.yn = 1
GROUP BY
    f1.pin,
    c1.site_id,
    c2.site_name

這段SQL主要有兩個問題：

1.cdm1和cdm2的條件寫在LEFT JOIN之后，因?yàn)閏dm1和cdm2是NULL補(bǔ)充表（NULL 補(bǔ)充表: 右表被稱為 NULL 補(bǔ)充表，意味著它的存在是為了補(bǔ)充左表中可能缺失的值。即使在右表中沒有與左表匹配的行，左表中的行仍然會被返回，右表的相關(guān)列會填充為 NULL），那么19和20行無法進(jìn)行謂詞下推，這會導(dǎo)致關(guān)聯(lián)時fdm1和cdm1,cdm2先進(jìn)行全表關(guān)聯(lián)，再按照WHERE條件過濾分區(qū)。如果cdm1是每天全量的表，先關(guān)聯(lián)全表所掃描的數(shù)據(jù)量可想而知是相當(dāng)大的。

2.全表關(guān)聯(lián)時沒有對關(guān)聯(lián)鍵進(jìn)行NULL值處理，如果相關(guān)表的對應(yīng)字段存在大量NULL值，會引起數(shù)據(jù)傾斜。

第一個問題涉及SQL的謂詞下推，即寫條件時，應(yīng)該在不影響結(jié)果的情況下，盡量將過濾條件下推到j(luò)oin之前進(jìn)行（“下推”指將條件推到靠近數(shù)據(jù)源的位置而不是SQL語句的方位）。謂詞下推后，過濾條件在map端執(zhí)行，減少了map端的輸出，降低了數(shù)據(jù)在集群上傳輸?shù)牧?，?jié)約了集群的資源，也可以提升任務(wù)的性能。

對于常用的INNER JOIN和LEFT OUTER JOIN，謂詞下推規(guī)則如下：

如果使用上述示例的寫法，主要關(guān)注的是LEFT OUTER JOIN時WHERE語句里的條件是否會引起謂詞不下推。如果不想記這些看起來很復(fù)雜的規(guī)則怎么辦？可以如下所示直接使用子查詢：

// -------------------- Good Codes 

審核編輯 黃宇