基于關(guān)系型代數(shù)的 SQL 等價改寫

看過我那篇《SQL 數(shù)據(jù)庫小白，從入門到精通》的朋友，一定不會陌生，SQL 的數(shù)學(xué)原理，就是集合運(yùn)算。

集合運(yùn)算, 排名第一的交換律，是這樣的：

交換律（Commutative Laws）：

• A ∪ B = B∪A，
• A ∩ B = B ∩ A

數(shù)學(xué)就這么妙！她把復(fù)雜的邏輯，抽象成簡單的符號，收斂住精美。

當(dāng)然，用純數(shù)學(xué)理論來解釋SQL,我想我會被罵成狗頭。我的目的，是還原精簡的符號，用實(shí)例來演繹背后的邏輯。

這里的A,B，是集合表達(dá)式?？梢钥闯?SQL 的 where 驅(qū)動出的數(shù)據(jù)集。

比如有同學(xué)表如下:

CREATE TABLE dbo.STUDENTS(
    STUDENT_ID INT 
,   STUDENT_NAME NVARCHAR(256)
,   STUDENT_GENDER NVARCHAR(6)
    )

字段分別代表：

• STUDENT_ID: 學(xué)號
• STUDENT_NAME:姓名
• STUDENT_GENDER:性別

假使 A 邏輯是 ：

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'
 

B 邏輯是:

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'女' 

那么

A ∪ B = B∪A，

則可以表達(dá)為 :

--A ∪ B 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男' OR STUDENT_GENDER=N'女'
 
 
--B∪A
SELECT * 
FROM dbo.STUDENTS 
WHERE  STUDENT_GENDER=N'女' OR STUDENT_GENDER = N'男' 

或者表達(dá)為：

--A ∪ B 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'
 
UNION 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER=N'女'


--B∪A

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER=N'女'

 
UNION 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'


 

再或者：

--A ∪ B 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'
 
UNION ALL 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER=N'女'
 
 
--B∪A

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER=N'女'

 
UNION ALL 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'
 
  

這 3 對(Or，Union, All Union ) 2 組，都是用來抓取全部的同學(xué)，那么有什么不一樣嗎？為什么可以有六種寫法

聰明如你一定能想到，其實(shí)我這么寫出來，肯定是有不一樣的地方。

本質(zhì)上，這 6 條語句，完成同一件事，但寫法的復(fù)雜度，肉眼可見的遞增。性能，也是逐個漸好。這一點(diǎn)，與大多數(shù)初學(xué)者的直覺相反。

沒錯，這才是本文要講的重點(diǎn)，基于關(guān)系型代數(shù)的SQL等價改寫

我記得，有一次做報表，肯茲肯茲寫了一下午的 SQL ，死摳了各種業(yè)務(wù)細(xì)節(jié)，精簡了各類邏輯表達(dá)，自認(rèn)為方方面面都考慮周全，無可挑剔。

雖然用了二十多個 UNION ALL, 代碼長達(dá) 800 多行，但整體代碼排版合理，邏輯清晰可見，一是一，二是二，閱讀體驗(yàn)特別棒。這么完美的一個報表 SQL，自己看著都要給自己磕頭。

但，就怕人比人！直到我看到另一個同事寫的SQL，區(qū)區(qū)2，30行，結(jié)果居然一樣的。便羞恥得驚掉下巴。怎么會這樣？！

我忍不住從 Code Repo 里面 Clone 下來，仔細(xì)把玩，哦不，品讀。

細(xì)看，這段SQL，版面清潔光滑，短小耐看，邏輯還不失完整。我不由得連連佩服，這樣清秀的代碼，簡直把我摁在地上，摩擦了幾條街。

從此，我便開始注意代碼的凝練，就像寫作般克制。于是就有了那篇《如何寫好 5000 行的 SQL 代碼》。

總體來說，寫 SQL 或者其他代碼，反復(fù)修改或重構(gòu)，是提升自己的不二之法。

自那以后，我放棄了一遍就寫好代碼的妄想，刻意在每次寫完之后，都反復(fù)修改 2-3 遍，直到自己心里說“ 對了，就是這樣！”，才敢簽入代碼庫。

就像現(xiàn)在我寫文一樣，越是害怕，越是難以下筆。唯有鼓足勇氣，多讀，多修改，內(nèi)心的糾結(jié)與痛苦，才得以緩解。

你猜對了，我為最近的難產(chǎn)，找到一個好借口！

如此小心翼翼，卻始終也還擔(dān)心，再次遇到這位朋友，恐怕他的造詣又上升了幾個段位。

有時，真被自己見賢思齊的心態(tài)，折騰得夠嗆。夢回午夜，經(jīng)常感嘆，自己的智商，技藝如此之低，競爭力何在啊。

扯遠(yuǎn)了，拉回到那 3對2組的 SQL 上來！

組之間，完成的是 A ∪ B 與 B∪A的 轉(zhuǎn)換。所以他們之間并沒有不同。但“對之間”，差異就很大。

這就是 SQL 等價改寫的魅力所在！

運(yùn)行第一對，看其執(zhí)行計劃：

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男' OR STUDENT_GENDER=N'女'

Table Scan 這個物理操作，代表的是訪問表的方式。在這里，Table Scan 執(zhí)行了全表掃描的操作。

Table Scan 這是一個非常危險的操作，需要優(yōu)化

運(yùn)行第二對，它的執(zhí)行計劃是這樣：

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'
 
UNION 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER=N'女'

除了第一對里出現(xiàn)的 Table Scan, 這里還出現(xiàn)了 Sort(Distinct Sort) 和 Concatenation 操作符。

這兩個操作符，是拜 UNION 所賜，UNION 有一層去重的功能。所以，它的這個功能在本次查詢中，是多余的，可去除。

第三對：

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男'
 
UNION ALL 

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER=N'女'
 
 

比起第二對，UNION ALL 去掉了去重的功能，即上下兩個結(jié)果集，如果有同樣的一條記錄，會在最終的結(jié)果集保留下來

雖然，UNION ALL 會在性能上，優(yōu)于 OR， 需要小心的是，在這里 A 與B 的限制條件互斥，才能改寫，一旦兩者有重合，則會出現(xiàn)重復(fù)記錄，這就與實(shí)際需求不符了。

比如,往 STUDENTS 表里新建一條未知性別的同學(xué)：

INSERT INTO dbo.STUDENTS(STUDENT_NAME,STUDENT_GENDER) 
SELECT 'Test Case' , 'UNKWN' AS STUDENT_GENDER 

再執(zhí)行

SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'男' OR STUDENT_GENDER = N'UNKWN'
UNION ALL 
SELECT * 
FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'女'  OR STUDENT_GENDER = N'UNKWN'
ORDER BY STUDENT_GENDER

就能看到有兩條 UNKWN 性別的記錄；

STUDENT_ID STUDENT_NAME STUDENT_GENDER
33815 Test Case UNKWN
33815 Test Case UNKWN

所以，SQL 轉(zhuǎn)換前提，一定是等價.

上面的例子，是日常開發(fā)或面試常見操作。底下這例，便是體現(xiàn)優(yōu)化功底的騷操作，不曾用過，就真不知道還能這么干。

SELECT *
FROM (

    SELECT A.*
    ,       B.*
    ,       C.*
    ,       D.*
    ,       E.*
    ,       F.*
    FROM A 
        INNER JOIN B ON B.XXX = A.XXX
        INNER JOIN C ON C.ZZZ = B.ZZZ 
        INNER JOIN D ON D.YYY = C.YYY 
        INNER JOIN E ON E.III = E.III
        INNER JOIN F ON F.PPP = E.PPP
) TMP 
    LEFT JOIN G ON G.WWW = TMP.WWW
WHERE TMP.FLD1 = 'SAMSUNG'
    AND TMP.FLD2 = 'KOREA'
    AND TMP.FLDX ='XXXX'

這種多表連接的 SQL，司空見慣?？峙逻B接的表，只有更多。

初學(xué)者，往往能將邏輯理清楚，就已經(jīng)非常吃力了。就像我之前的例子，嘩嘩嘩，一通寫下來，把數(shù)據(jù)找正確，就滿足了。

但，假如 FLD1, FLD2, FLDX，隸屬于 A,B,C,D,E,F，你是否能看出點(diǎn)什么來？

沒錯， A ∩ B = B ∩ A 交集等價轉(zhuǎn)換：

SELECT *
FROM (

    SELECT A.*
    ,       B.*
           
           
           
           
    FROM A 
        INNER JOIN B ON B.XXX = A.XXX
    WHERE A.FLD1 = 'SAMSUNG'
    AND A.FLD2 = 'KOREA'
    AND B.FLDX ='XXXX'
) TMP 
    INNER JOIN C ON C.ZZZ = TMP.ZZZ 
    INNER JOIN D ON D.YYY = C.YYY 
    INNER JOIN E ON E.III = E.III
    INNER JOIN F ON F.PPP = E.PPP
    LEFT JOIN G ON G.WWW = TMP.WWW

前提：FLD1, FLD2, FLDX 隸屬于 A,B 兩表，且不是計算字段

原先的內(nèi)連接，會拋出一個巨大的矩陣：

SELECT A.*
    ,       B.*
    ,       C.*
    ,       D.*
    ,       E.*
    ,       F.*
    FROM A 
        INNER JOIN B ON B.XXX = A.XXX
        INNER JOIN C ON C.ZZZ = B.ZZZ 
        INNER JOIN D ON D.YYY = C.YYY 
        INNER JOIN E ON E.III = E.III
        INNER JOIN F ON F.PPP = E.PPP

而事實(shí)上，基于

 WHERE A.FLD1 = 'SAMSUNG'
    AND A.FLD2 = 'KOREA'
    AND B.FLDX ='XXXX'
    

這樣的條件，只能選出一條或者少量數(shù)據(jù)。那前期做了很多 Join 操作，就變成了無用功，浪費(fèi)了計算資源。

驅(qū)動表最小化，這是優(yōu)化的一條方法。如果優(yōu)化器，做不到謂詞推進(jìn)，那只能人工幫他做選擇。

什么是“謂詞推進(jìn)”？

當(dāng)在 STUDENTS 表上加索引后，

CREATE INDEX IDX_STU_GENDER ON dbo.STUDENTS(STUDENT_GENDER) 

執(zhí)行查詢:

SELECT STUDENT_GENDER FROM dbo.STUDENTS 
WHERE STUDENT_GENDER = N'UNKWN'

標(biāo)記為紅框的部分，就是謂詞表達(dá)式。只有謂詞靠近原表，才能發(fā)揮減少數(shù)據(jù)訪問量的作用。