基于周二的文章
《如何掌握openGauss數據庫核心技術(shù)？秘訣一：拿捏SQL引擎（1）》（詳戳），我們今日繼續連載。
目錄openGauss數據庫SQL引擎
openGauss數據庫執行器技術(shù)
openGauss存儲技術(shù)
openGauss事務(wù)機制
openGauss數據庫安全
openGauss數據庫SQL引擎
三、查詢(xún)優(yōu)化
SQL語(yǔ)句在編寫(xiě)的過(guò)程中，數據庫應用開(kāi)發(fā)人員通常會(huì )考慮以不同的形式來(lái)編寫(xiě)SQL，來(lái)達到提升執行性能的目的，那么為什么還需要查詢(xún)優(yōu)化器來(lái)對SQL進(jìn)行優(yōu)化呢？這是因為一個(gè)應用程序可能會(huì )涉及到大量的SQL語(yǔ)句，而且有些SQL語(yǔ)句的邏輯極為復雜，數據庫開(kāi)發(fā)人員很難面面俱到的寫(xiě)出高性能語(yǔ)句，而查詢(xún)優(yōu)化器則具有一些獨特的優(yōu)勢：
§ 查詢(xún)優(yōu)化器和數據庫應用程序開(kāi)發(fā)人員之間的信息不對稱(chēng)，查詢(xún)優(yōu)化器在優(yōu)化的過(guò)程中會(huì )參考數據庫統計模塊自動(dòng)產(chǎn)生的統計信息，這些統計信息從各個(gè)角度來(lái)描述數據的分布情況，查詢(xún)優(yōu)化器會(huì )綜合考慮統計信息中的各種數據，從而能夠得到一個(gè)比較好的執行方案，而數據庫用戶(hù)一方面無(wú)法全面的了解數據的分布情況，另一方面也很難通過(guò)統計信息構建一個(gè)精確的代價(jià)模型來(lái)對執行計劃進(jìn)行篩選。
§ 查詢(xún)優(yōu)化器和數據庫應用程序開(kāi)發(fā)人員之間的時(shí)效性不同，數據庫中的數據瞬息萬(wàn)變，一個(gè)在A(yíng)時(shí)間點(diǎn)執行性能很高的執行計劃，在B時(shí)間點(diǎn)由于數據內容發(fā)生了變化，它的性能可能就很低，查詢(xún)優(yōu)化器則隨時(shí)都能根據數據的變化調整執行計劃，而數據庫應用程序開(kāi)發(fā)人員則只能手動(dòng)的調整SQL語(yǔ)句，和查詢(xún)優(yōu)化器相比，它的時(shí)效性比較低；
§ 查詢(xún)優(yōu)化器和數據庫應用程序開(kāi)發(fā)人員的計算能力不同，目前計算機的計算能力已經(jīng)大幅提高，在執行數值計算方面和人腦相比具有巨大的優(yōu)勢，查詢(xún)優(yōu)化器對一個(gè)SQL語(yǔ)句進(jìn)行優(yōu)化時(shí)，可以從成百上千個(gè)執行方案中選擇一個(gè)最優(yōu)方案，而人腦要計算這幾百種方案需要的時(shí)間要遠遠長(cháng)于計算機；
因此，查詢(xún)優(yōu)化器是提升查詢(xún)效率的非常重要的一個(gè)手段，雖然一些數據庫也提供了人工干預執行計劃生成的方法，但是通常而言，查詢(xún)優(yōu)化器的優(yōu)化過(guò)程對數據庫開(kāi)發(fā)人員是透明的，它自動(dòng)進(jìn)行邏輯上的等價(jià)變換、自動(dòng)進(jìn)行物理執行計劃的篩選，極大的提高了數據庫應用程序開(kāi)發(fā)人員的“生產(chǎn)力”。
依據優(yōu)化方法的不同，優(yōu)化器的優(yōu)化技術(shù)可以分為：
§ 基于規則的查詢(xún)優(yōu)化（Rule Based Optimization，RBO）：根據預定義的啟發(fā)式規則對SQL語(yǔ)句進(jìn)行優(yōu)化。
§ 基于代價(jià)的查詢(xún)優(yōu)化（Cost Based Optimization，CBO）：對SQL語(yǔ)句對應的待選執行路徑進(jìn)行代價(jià)估算，從待選路徑中選擇代價(jià)最低的執行路徑作為最終的執行計劃。
§ 基于機器學(xué)習的查詢(xún)優(yōu)化（AI Based Optimization，ABO）：收集執行計劃的特征信息，借助機器學(xué)習模型獲得經(jīng)驗信息，進(jìn)而對執行計劃進(jìn)行調優(yōu)，獲得最優(yōu)的執行計劃。
在早期的數據庫中，查詢(xún)優(yōu)化器通常采用啟發(fā)式規則進(jìn)行優(yōu)化，這種優(yōu)化方式不不夠靈活，往往難以獲得最優(yōu)的執行代價(jià)，而基于代價(jià)的優(yōu)化則能夠針對大多數場(chǎng)景都高效篩選出性能較好的執行計劃，但面對用戶(hù)千人千面，日趨復雜的實(shí)際查詢(xún)場(chǎng)景，普適性的查詢(xún)優(yōu)化由于難以捕捉到用戶(hù)特定的查詢(xún)需求、數據分布、硬件性能等特征，難以全方位滿(mǎn)足實(shí)際的優(yōu)化需求。
近年來(lái)AI技術(shù)，特別是在深度學(xué)習領(lǐng)域，發(fā)展迅速，基于機器學(xué)習的優(yōu)化器在建模效率、估算準確率和自適應性等方面都有很大優(yōu)勢，有望打破RBO和CBO基于靜態(tài)模型的限制，通過(guò)對歷史經(jīng)驗的不斷學(xué)習，將目標場(chǎng)景的模式進(jìn)行抽象化，形成動(dòng)態(tài)的模型，自適應地針對用戶(hù)的實(shí)際場(chǎng)景進(jìn)行優(yōu)化。openGauss采用基于CBO的優(yōu)化技術(shù)，另外在A(yíng)BO方面也在進(jìn)行積極探索。
Ⅰ、查詢(xún)重寫(xiě)
查詢(xún)重寫(xiě)利用已有語(yǔ)句特征和關(guān)系代數運算來(lái)生成更高效的等價(jià)語(yǔ)句，在數據庫優(yōu)化器中扮演關(guān)鍵角色，尤其在復雜查詢(xún)中，能夠在性能上帶來(lái)數量級的提升，可謂是“立竿見(jiàn)影”的“黑科技”。本節介紹查詢(xún)重寫(xiě)的基本概念、常見(jiàn)的查詢(xún)重寫(xiě)技術(shù)、查詢(xún)重寫(xiě)面臨的挑戰。
1. 查詢(xún)重寫(xiě)的概念
SQL語(yǔ)言是豐富多樣的，非常的靈活，不同的開(kāi)發(fā)人員依據經(jīng)驗的不同，手寫(xiě)的SQL語(yǔ)句也是各式各樣，另外還可以通過(guò)工具自動(dòng)生成。SQL語(yǔ)言是一種描述性語(yǔ)言，數據庫的使用者只是描述了想要的結果，而不關(guān)心數據的具體獲取方式，輸入數據庫的SQL語(yǔ)言很難做到是以最優(yōu)形式表示的，往往隱含了一些冗余信息，這些信息可以被挖掘用來(lái)生成更加高效的SQL語(yǔ)句。查詢(xún)重寫(xiě)就是把用戶(hù)輸入的SQL語(yǔ)句轉換為更高效的等價(jià)SQL，查詢(xún)重寫(xiě)遵循兩個(gè)基本原則：
§ 等價(jià)性：原語(yǔ)句和重寫(xiě)后的語(yǔ)句，輸出結果相同。
§ 高效性：重寫(xiě)后的語(yǔ)句，比原語(yǔ)句在執行時(shí)間和資源使用上更高效。
2. 關(guān)系代數等價(jià)變換
查詢(xún)重寫(xiě)主要是基于關(guān)系代數式的等價(jià)變換，關(guān)系代數的變換通常滿(mǎn)足交換律、結合律、分配率、串接率等，如表2所示。
等價(jià)變換
內容
交換律
A × B == B × A
A ?B == B ? A
A ?F B == B ?F A -- F是連接條件
Π p(σF (B)) == σF (Π p(B)) –- F∈p
結合律
(A × B) × C==A × (B × C)
(A ? B) ? C==A ? (B ? C)
(A ?F1 B) ?F2 C==A ?F1 (B ?F2 C) -- F1和F2是連接條件
分配律
σF(A × B) == σF(A) × B -- F ∈ A
σF(A × B) == σF1(A) × σF2(B) -- F = F1 ∪ F2，F1∈A, F2 ∈B
σF(A × B) == σFX (σF1(A) × σF2(B)) -- F = F1∪F2∪FX，F1∈A, F2 ∈B
Π p,q(A × B) == Π p(A) × Π q(B) -- p∈A，q∈B
σF(A × B) == σF1(A) × σF2(B) -- F = F1 ∪ F2，F1∈A, F2 ∈B
σF(A × B) == σFx (σF1(A) × σF2(B)) -- 其中F = F1∪F2∪Fx，F1∈A, F2 ∈B
串接律
Π P=p1,p2,…pn(Π Q=q1,q2,…qn(A)) == Π P=p1,p2,…pn(A) -- P ? Q
σF1(σF2(A)) == σF1∧F2(A)
表2 關(guān)系代數等價(jià)變換
表2中的等價(jià)變換規則并不能把所有的情況都列舉出來(lái)，例如，如果對σF1(σF2(A)) == σF1∧F2(A)繼續推導，那么就可以獲得：
σF1(σF2(A)) == σF1∧F2(A) == σF2∧F1(A) == σF2(σF1(A))因此，在熟悉了關(guān)系代數的操作之后，就可以靈活的利用關(guān)系代數的等價(jià)關(guān)系進(jìn)行推導，獲得更多的等價(jià)式。這些等價(jià)的變換一方面可以用來(lái)根據啟發(fā)式的規則做優(yōu)化，這樣能保證等價(jià)轉換之后的關(guān)系代數表達式的執行效率能夠獲得提高而非降低，例如借助分配率可以將一個(gè)選擇操作下推，這樣能降低上層結點(diǎn)的計算量，另一方面還可以用來(lái)生成候選的執行計劃，候選的執行計劃再由優(yōu)化器根據估算的代價(jià)進(jìn)行篩選。
3. 常見(jiàn)的查詢(xún)重寫(xiě)技術(shù)
介紹下openGauss幾個(gè)關(guān)鍵的查詢(xún)重寫(xiě)技術(shù)：常量表達式化簡(jiǎn)、子查詢(xún)優(yōu)化、選擇下推和等價(jià)推理等。
1）常量表達式化簡(jiǎn)
常量表達式即用戶(hù)輸入SQL語(yǔ)句中包含運算結果為常量的表達式，分為算數表達式、邏輯運算表達式、函數表達式，查詢(xún)重寫(xiě)可以對常量表達式預先計算以提升效率。例如：
示例1：該語(yǔ)句為典型的算數表達式查詢(xún)重寫(xiě)，經(jīng)過(guò)重寫(xiě)之后，避免了在執行時(shí)每條數據都需要進(jìn)行1+1運算。
SELECT * FROM t1 WHERE c1=1+1;??SELECT * FROM t1 WHERE c1=2;示例2：該語(yǔ)句為典型的邏輯運算表達式，經(jīng)過(guò)重寫(xiě)之后，條件永遠為false，可以直接返回0行結果，避免了整個(gè)語(yǔ)句的實(shí)際執行。
SELECT * FROM t1 WHERE 1=0 AND a=1;
??SELECT * FROM t1 WHERE false;
示例3：該語(yǔ)句包含函數表達式，由于函數的入參為常量，經(jīng)過(guò)重寫(xiě)之后，直接把函數運算結果在優(yōu)化階段計算出來(lái)，避免了在執行過(guò)程中逐條數據的函數調用開(kāi)銷(xiāo)。
SELECT * FROM t1 WHERE c1 = ADD(1,1);??SELECT * FROM t1 WHERE c1=2;2） 子查詢(xún)優(yōu)化
由于子查詢(xún)表示的結構更清晰，符合人的閱讀理解習慣，用戶(hù)輸入的SQL語(yǔ)句往往包含了大量的子查詢(xún)。子查詢(xún)有幾種分類(lèi)方法，根據子查詢(xún)是否可以獨立求解，分為相關(guān)子查詢(xún)和非相關(guān)子查詢(xún)。
§ 相關(guān)子查詢(xún)：相關(guān)子查詢(xún)是指子查詢(xún)中有依賴(lài)父查詢(xún)的條件，例如：SELECT * FROM t1 WHERE EXISTS (SELECT t2.c1 FROM t2 WHERE t1.c1=t2.c1);
語(yǔ)句中子查詢(xún)依賴(lài)父查詢(xún)傳入t1.c1的值。
§ 非相關(guān)子查詢(xún)：非相關(guān)子查詢(xún)是指子查詢(xún)不依賴(lài)父查詢(xún)，可以獨立求解，例如：
SELECT * FROM t1 WHERE EXISTS (SELECT t2.c1 FROM t2);
語(yǔ)句中子查詢(xún)沒(méi)有依賴(lài)父查詢(xún)的條件。
其中，相關(guān)子查詢(xún)需要父查詢(xún)執行出一條結果，然后驅動(dòng)子查詢(xún)運算，這種嵌套循環(huán)的方式執行效率較低。如果能把子查詢(xún)提升為父查詢(xún)同級別，那么可以子查詢(xún)中的表就能和父查詢(xún)中的表直接做Join操作，由于Join操作可以有多種實(shí)現方法，優(yōu)化器就可以從多種實(shí)現方法中選擇最優(yōu)的一種，就有可能提高查詢(xún)的執行效率，另外優(yōu)化器還能夠應用Join Reorder優(yōu)化規則對不同的表的連接順序進(jìn)行交換，進(jìn)而有可能產(chǎn)生更好的執行計劃。
示例4：該語(yǔ)句為典型的子查詢(xún)提升重寫(xiě)，重寫(xiě)之后利用Hash Join可以提升查詢(xún)性能。
SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2); ?? SELECT * FROM t1 Semi Join t2 ON t1.c1 = t2.c1;3） 選擇的下推和等價(jià)推理
選擇的下推能夠極大的降低上層算子的計算量，從而達到優(yōu)化的效果，如果選擇條件有存在等值操作，那么還可以借助等值操作的特性來(lái)實(shí)現等價(jià)推理，從而獲得新的選擇條件。
例如，假設有兩個(gè)表t1、t2分別包含[1,2,3,..100]共100行數據，那么查詢(xún)語(yǔ)句SELECT t1.c1, t2.c1 FROM t1 JOIN t2 ON t1.c1=t2.c1 WHERE t1.c1=1的則可以通過(guò)選擇下推和等價(jià)推理進(jìn)行優(yōu)化，如圖5。
圖4 查詢(xún)重寫(xiě)前后對比圖
圖5 自底向上模式
如圖4-(1)所示，t1、t2表都需要全表掃描100行數據，然后再做Join，生成100行數據的中間結果，最后再做選擇操作，最終結果只有1行數據。如果利用等價(jià)推理，我們可以得到{t1.c1, t2.c1, 1}的是互相等價(jià)的，從而推導出新的t2.c1=1的選擇條件，并把這個(gè)條件下推到t2上，從而得到圖4-（4）重寫(xiě)之后的邏輯計劃?？梢钥吹?，重寫(xiě)之后的邏輯計劃，只需要從基表上面獲取1條數據即可，Join時(shí)內、外表的數據也只有1條，同時(shí)省去了在最終結果上的過(guò)濾條件，性能大幅提升。
4） 外連接消除
外連接和內連接的主要區別是對于不能產(chǎn)生連接結果的元組需要補NULL值，如果SQL語(yǔ)句中有過(guò)濾條件符合空值拒絕的條件（即會(huì )將補充的NULL值再過(guò)濾掉），則可以直接消除外連接。
示例5：外連接轉成內連接之后，便于優(yōu)化器應用更多的優(yōu)化規則，提高執行效率。
SELECT * FROM t1 FULL JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 > 5 AND t2.c3 < 10;?? SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c2 WHERE t1.c2 > 5 AND t2.c3 < 10;5） DISTINCT消除
DISTINCT列上如果有主鍵約束，則此列不可能為空，且無(wú)重復值，因此不需要DISTINCT操作，減少計算量。
示例6：c1列上有的主鍵屬性決定了無(wú)需做DISTINCT操作。
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);SELECT DISTINCT(c1) FROM t1;??SELECT c1 FROM t1;6） IN謂詞展開(kāi)
示例7：將IN操作符改寫(xiě)成等值的過(guò)濾條件，便于借助索引減少計算量。
SELECT * FROM t1 WHERE c1 IN (10,20,30);?? SELECT * FROM t1 WHERE c1=10 or c1=20 OR c1=30;7） 視圖展開(kāi)
視圖從邏輯上可以簡(jiǎn)化書(shū)寫(xiě)SQL的難度，提高查詢(xún)的易用性，而視圖本身是虛擬的，因此在查詢(xún)重寫(xiě)的過(guò)程中，需要對視圖展開(kāi)。
示例7：可以將視圖查詢(xún)重寫(xiě)成子查詢(xún)的形式，然后再對子查詢(xún)做簡(jiǎn)化。
CREATE VIEW v1 AS (SELECT * FROM t1,t2 WHERE t1.c1=t2.c2);SELECT * FROM v1;??SELECT * FROM (SELECT * FROM t1,t2 WHERE t1.c1=t2.c2) as v1;?? SELECT * FROM t1,t2 WHERE t1.c1=t2.c2;未完待續......
