作者：京東物流 向往

一、背景

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

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

二、方法與技巧

1.子查詢與謂詞下推

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

// -------------------- 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主要有兩個(gè)問(wèn)題：

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

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

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

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

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

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

審核編輯 黃宇