主子表是數(shù)據(jù)庫最常見的關(guān)聯(lián)關(guān)系之一，最典型的包括合同和合同條款、訂單和訂單明細(xì)、保險保單和保單明細(xì)、銀行賬戶和賬戶流水、電商用戶和訂單、電信賬戶和計費清單或流量詳單。當(dāng)主子表的數(shù)據(jù)量較大時，關(guān)聯(lián)計算的性能將急劇降低，在增加服務(wù)器負(fù)載的同時嚴(yán)重影響用戶體驗。作為面向過程的結(jié)構(gòu)化數(shù)據(jù)計算語言，集算器 SPL 可通過有序歸并的方法，顯著提升大主子表關(guān)聯(lián)計算的性能。

成都創(chuàng)新互聯(lián)主營臨潭網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,App定制開發(fā),臨潭h5微信小程序定制開發(fā)搭建,臨潭網(wǎng)站營銷推廣歡迎臨潭等地區(qū)企業(yè)咨詢

一、        原理解釋

所謂主子表關(guān)聯(lián)計算，就是針對主表的每條記錄，按關(guān)聯(lián)字段找到子表中對應(yīng)的一批記錄。以訂單（主表）和訂單明細(xì)（子表）為例，兩者以訂單ID為關(guān)聯(lián)字段。下圖顯示了關(guān)聯(lián)計算過程中對主表中一條記錄的處理情況，紅色箭頭代表沒找到對應(yīng)記錄（不可關(guān)聯(lián)），綠色箭頭代表找到了對應(yīng)記錄（可關(guān)聯(lián)）：

假設(shè)訂單（主表）有m條記錄，訂單明細(xì)（子表）有n條記錄，在不考慮優(yōu)化算法時，主表中每一條記錄的關(guān)聯(lián)都需要遍歷子表，相應(yīng)的時間復(fù)雜度為O(n)。而主表一共有m條記錄，所以整個計算的復(fù)雜度就是O(m*n)，顯然過高。雖然數(shù)據(jù)庫一般會采用hash方案來優(yōu)化，但在數(shù)據(jù)量較大或較多表關(guān)聯(lián)時，仍然會面臨時難以并行、使用外存緩存數(shù)據(jù)的問題，性能依舊會急劇下降。

而對于集算器來說，針對大主子表關(guān)聯(lián)算法，可以通過兩步來實現(xiàn)顯著優(yōu)化：數(shù)據(jù)有序化、歸并關(guān)聯(lián)。

l   數(shù)據(jù)有序化

對主表和子表，首先分別按照關(guān)聯(lián)字段排序，形成有序數(shù)據(jù)。

l   歸并關(guān)聯(lián)

首先在主表和子表上分別用指針指向第一條記錄，然后開始比對，對于主表的第一條記錄，如果子表遇到匹配的記錄，則表示可以關(guān)聯(lián)，記錄后子表指針前移；如果遇到不匹配的記錄，表示主表第一條記錄的關(guān)聯(lián)計算完成，此時子表指針不動，主表指針下移一位，指向第二條記錄。以此類推……

優(yōu)化后，單條記錄的關(guān)聯(lián)計算可用下圖示意：

可以看到，經(jīng)過優(yōu)化，主表中單條記錄的關(guān)聯(lián)只需比對部分?jǐn)?shù)據(jù)，不再需要遍歷子表。事實上，對主表所有記錄的關(guān)聯(lián)，才會遍歷一次子表，也就是復(fù)雜度為O(n)。再加上主表本身會遍歷一次，因此整個計算的復(fù)雜度就是O(m+n)。

這樣，經(jīng)過集算器優(yōu)化后，算法的時間復(fù)雜度變?yōu)榫€性，而且不再需要生成落地的中間數(shù)據(jù)，性能自然得到大幅提升。

當(dāng)然，需要注意的是，有序化本身也會耗費時間，因此這種優(yōu)化方法不適合只做一次的關(guān)聯(lián)算法。但在實際業(yè)務(wù)中，關(guān)聯(lián)算法通常會反復(fù)執(zhí)行，這時有序化的開銷就是一次性的，完全可以忽略不計。

二、        具體實現(xiàn)

下面還是以訂單和訂單明細(xì)為例，說明集算器優(yōu)化大主子表關(guān)聯(lián)的方法。

首先進(jìn)行數(shù)據(jù)有序化（注意，這是一次性動作）。集算器腳本“數(shù)據(jù)有序化.dfx”如下：

A1連接Oracle數(shù)據(jù)源，A5關(guān)閉數(shù)據(jù)源。集算器可連接大部分常用數(shù)據(jù)源，包括數(shù)據(jù)庫、Excel、阿里云、SAP等等。

A2、B2：用SQL語句分別取訂單和訂單明細(xì)，并按關(guān)聯(lián)字段排序。由于數(shù)據(jù)量較大，無法一次性讀入內(nèi)存，因此這里用到了游標(biāo)函數(shù)cursor。

A3、B3：分別創(chuàng)建組表文件“訂單.ctx”和“訂單明細(xì).ctx”，用于存儲有序化之后的數(shù)據(jù)。這里需要指定字段名，其中帶#號的字段是主鍵，。數(shù)據(jù)將按主鍵排序，且主鍵的值不可重復(fù)。

A4-B4：將游標(biāo)追加寫入組表文件。

其次，對于通常會反復(fù)執(zhí)行的關(guān)聯(lián)算法，可以用集算器腳本“歸并關(guān)聯(lián).dfx”實現(xiàn)如下：

A1、B1：讀入組表文件“訂單.ctx”和“訂單明細(xì).ctx”。注意組表默認(rèn)為列式存儲，因此只需讀入后續(xù)計算需要的字段，從而大幅降低I/O。

A2：對有序游標(biāo)A1、B1進(jìn)行歸并關(guān)聯(lián)，其中“主表”、“子表”是別名，方便后續(xù)引用，如果省略別名，后續(xù)可以通過默認(rèn)別名_1、_2引用。注意，函數(shù)joinx默認(rèn)進(jìn)行內(nèi)關(guān)聯(lián)，可用選項@1指定左關(guān)聯(lián)，或者@f指定全關(guān)聯(lián)。如果有多個游標(biāo)都要與A1關(guān)聯(lián)，可用分號依次隔開。

A3：對關(guān)聯(lián)結(jié)果進(jìn)行后續(xù)計算，例如匯總產(chǎn)品數(shù)量。事實上后續(xù)計算可以支持任意算法，也不是本文的討論范圍了。

上面介紹了集算器SPL腳本的寫法，而在實際執(zhí)行時，還需要部署集算器的運行環(huán)境。有兩種部署方式可供選擇：內(nèi)嵌部署和獨立部署。

l   內(nèi)嵌部署

內(nèi)嵌部署時，集算器的用法類似內(nèi)嵌數(shù)據(jù)庫，應(yīng)用系統(tǒng)使用集算器驅(qū)動（JDBC）執(zhí)行同一個JVM下的集算器腳本。

下面是Java調(diào)用“歸并關(guān)聯(lián).dfx”的代碼

在上述JAVA代碼中，集算器腳本以文件的形式保存，調(diào)用語法類似存儲過程。而如果腳本很簡單，也可以不保存腳本文件，直接書寫表達(dá)式，調(diào)用語法類似SQL，這時第5行可以寫成：

這篇文章詳細(xì)介紹了JAVA調(diào)用集算器的過程：http://doc.raqsoft.com.cn/esproc/tutorial/bjavady.html

除了使用Java代碼，也可以通過報表訪問集算器，這時按照訪問一般數(shù)據(jù)庫的方法即可，具體可參考《讓Birt報表腳本數(shù)據(jù)源變得既簡單又強(qiáng)大》。

對于腳本“數(shù)據(jù)有序化.dfx”，可以用同樣的方法執(zhí)行。不過這個腳本通常只執(zhí)行一次，所以也可以直接在命令行中執(zhí)行，windows用法如下：

Linux下用法類似，可以參考http://doc.raqsoft.com.cn/esproc/tutorial/minglinghang.html

l   獨立部署

獨立部署時，集算器的用法類似遠(yuǎn)程數(shù)據(jù)庫，應(yīng)用系統(tǒng)可以使用集算器驅(qū)動（JDBC或ODBC驅(qū)動）訪問集算服務(wù)器。這種情況下，應(yīng)用系統(tǒng)和集算器服務(wù)器通常部署在不同的機(jī)器上。

例如集算服務(wù)器的IP地址為192.168.0.2，端口號為8281，那么JAVA應(yīng)用系統(tǒng)可以通過如下代碼訪問：

關(guān)于集算服務(wù)器的部署和使用，詳細(xì)內(nèi)容可參考http://doc.raqsoft.com.cn/esproc/tutorial/fuwuqi.html

關(guān)于JDBC和ODBC驅(qū)動的部署方法，可分別參考

http://doc.raqsoft.com.cn/esproc/tutorial/jdbcbushu.html

http://doc.raqsoft.com.cn/esproc/tutorial/odbcbushu.html

三、        多線程優(yōu)化

前面介紹了基本的優(yōu)化思路和實現(xiàn)方法，也就是針對數(shù)據(jù)本身的優(yōu)化。而現(xiàn)實中服務(wù)器都是多核心CPU，因此可以進(jìn)一步對上述算法進(jìn)行多線程優(yōu)化。

多線程優(yōu)化的原理，是將主表和子表各分為N段，使用N個線程同時進(jìn)行關(guān)聯(lián)計算。

原理雖簡單，但真正實現(xiàn)的時候，就會發(fā)現(xiàn)很多難題：

l   分段效率

想把數(shù)據(jù)分為N段，就要先找到每一段的起始行號，如果用遍歷的笨辦法數(shù)行號，顯然會白白消耗大量的I/O資源。

l   數(shù)據(jù)跨段

理論上，關(guān)聯(lián)字段值相同的子表記錄，應(yīng)該分到同一段。如果對子表隨意分段，很可能形成跨段的數(shù)據(jù)。

l   分段對齊

更進(jìn)一步，理論上，子表的第i段數(shù)據(jù)，應(yīng)該與主表的第i段數(shù)據(jù)對齊，也就是主子表關(guān)聯(lián)字段值的范圍應(yīng)該一致。如果兩者各自獨立分段，則可能導(dǎo)致分段數(shù)據(jù)難以對齊。

l   二次計算

如果后續(xù)計算不涉及聚合，例如只是過濾，那么只需將N個線程的計算結(jié)果直接合并。但如果后續(xù)計算涉及聚合，比如sum或分組匯總，那就要單獨再進(jìn)行二次計算聚合。

好在集算器已經(jīng)充分解決了上述難題，分段時不會耗費IO資源、關(guān)聯(lián)字段值相同的記錄會分在同一段、子表和主表會保持對齊、各種二次計算無需單獨實現(xiàn)。

具體來說，首先，數(shù)據(jù)有序化腳本需要做如下修改（紅色字體為修改部分）：

B3：生成“訂單明細(xì)多線程.ctx”時，數(shù)據(jù)按“#訂單ID”分段。這將保證訂單ID相同的記錄，將來會分到同一段。

歸并關(guān)聯(lián)的腳本需修改如下：

A1：@m表示對數(shù)據(jù)分段，形成多線程游標(biāo)（也叫多路并行游標(biāo)）。其中線程數(shù)量是默認(rèn)值，由系統(tǒng)參數(shù)“最大并行數(shù)”決定，也可手工修改。例如希望生成4線程游標(biāo)，A1應(yīng)寫成：

B1：同樣生成多線程游標(biāo)，并與A1的多線程游標(biāo)對齊。

A2-A3：歸并關(guān)聯(lián)，再執(zhí)行后續(xù)算法。這兩步寫法上沒變化，但底層會自動進(jìn)行多線程合并和二次計算，從而降低了程序員的編程難度。

四、        結(jié)構(gòu)優(yōu)化

在前面算法的基礎(chǔ)上，還可以進(jìn)一步提升計算性能，那就是以層次結(jié)構(gòu)存儲數(shù)據(jù)，直接記錄關(guān)聯(lián)關(guān)系。

具體來說，先用“結(jié)構(gòu)優(yōu)化有序化.dfx”生成組表文件：

B4：在主表的基礎(chǔ)上附加子表，命名為訂單明細(xì)。與主表不同的是，子表默認(rèn)繼承了主表的主鍵，因此可以省略訂單ID，只需要寫另一個主鍵產(chǎn)品ID。這樣，2個表寫在了一個組表文件中，從而才能形成層次結(jié)構(gòu)。

B5：向子表寫入數(shù)據(jù)。

此時，組表“多層訂單.ctx”將按層次結(jié)構(gòu)存儲，邏輯示意圖如下：

可以看到，每條主表記錄與對應(yīng)的子表記錄，在邏輯上已經(jīng)緊密相關(guān)，無需額外關(guān)聯(lián)，這樣便可大幅提高關(guān)聯(lián)算法的性能。

進(jìn)行關(guān)聯(lián)計算時，使用以下腳本“結(jié)構(gòu)優(yōu)化歸并關(guān)聯(lián).dfx”：

A1、B1：打開主表，以及附加在主表上的子表。

A2、B2：以多線程方式分別讀取主表和子表。需要注意的是，多層組表里的實表之間天然具備相關(guān)性，因此無需特意指定子表和主表的分段關(guān)系，代碼比之前更清晰簡單。

A3，A4：歸并關(guān)聯(lián)并執(zhí)行后續(xù)算法，這兩步?jīng)]變化。

五、        數(shù)據(jù)更新

前面的優(yōu)化方式都基于庫表全量導(dǎo)出為組表文件的情況，但實際業(yè)務(wù)中數(shù)據(jù)庫表總會發(fā)生變化，因此需要考慮數(shù)據(jù)更新的問題，也就是要將變化的數(shù)據(jù)定時更新到組表文件中。

顯然，更新數(shù)據(jù)應(yīng)選擇在無人查詢組表文件時進(jìn)行，一般都是半夜或凌晨。而更新的頻率，則需要按照數(shù)據(jù)實時性要求來設(shè)定，例如每天一次或每周一次。至于更新的方式，需要按照數(shù)據(jù)的變化規(guī)律來考慮，最常見的是數(shù)據(jù)追加，有時也會遇到增刪改。

下面先看數(shù)據(jù)追加：

訂單和訂單明細(xì)每天都會產(chǎn)生新記錄，假設(shè)需要在每天凌晨2點將昨天新增的記錄追加到組表文件中。下圖顯示了2018/11/23新增記錄的情況，注意，有些訂單（訂單ID：20001）并沒有對應(yīng)的訂單明細(xì)：

把主子表追加到組表文件中的腳本 “追加組文件.dfx”如下：

A2、B2：計算昨天的起止時間，以便查詢新增數(shù)據(jù)。函數(shù)now獲取當(dāng)前時間點，理論上應(yīng)該是2018-11-24 02:00:00。A2是昨天的起始時間點，即2018-11-22 00:00:00。B2是終止時間點，即2018-11-23 00:00:00。之所以在集算器中計算起止時間，主要是為了增加可讀性和移植性。實際上也可以在SQL中計算。

A4：取出新增的主表和子表記錄。這里用一句SQL取兩張表的數(shù)據(jù)，主要是為了提高效率。由于有些訂單并沒有對應(yīng)的訂單明細(xì)，因此用訂單左關(guān)聯(lián)訂單明細(xì)，且將對應(yīng)不上的訂單明細(xì)置空。計算結(jié)果如下：

A5、B5：拆出新增的主子表記錄，結(jié)果示例如下：

A6-B8：將主表和子表追加到組表文件中。

腳本寫完之后，還需要在每天的02:00:00定時執(zhí)行，這可以使用操作系統(tǒng)內(nèi)置的任務(wù)調(diào)度。

在Windows下，建立如下的bat批處理文件，：

再使用windows內(nèi)置的"計劃任務(wù)"，定時執(zhí)行批處理文件即可。

在linux下，建立如下的sh批處理文件，：

再使用crontab命令，定時執(zhí)行批處理文件即可。

當(dāng)然也可使用圖形化工具定時執(zhí)行腳本，比如Quartz。

需要注意的是，大多數(shù)情況下，能夠選擇無人使用組表文件的時候進(jìn)行追加，但有些業(yè)務(wù)中組表文件全天都要使用，而有些項目對容錯要求更高，要求追加失敗時再次追加，這類項目就需要更加細(xì)致的追加方法，詳情可參考《基于文件系統(tǒng)實現(xiàn)可追加的數(shù)據(jù)集市》。

除了追加這種主要的更新方式，業(yè)務(wù)中也會遇到增刪改都存在的情況。

在這種情況下，就需要知道哪些是刪除的記錄，哪些是修改或新增的記錄。如果條件允許，可以在原表中新加“標(biāo)記”字段，并將維護(hù)狀態(tài)記錄在該字段中。如果不方便修改原表，則應(yīng)當(dāng)創(chuàng)建對應(yīng)的“維護(hù)日志表”。例如下面兩張表，分別是訂單和訂單明細(xì)的維護(hù)日志。

根據(jù)維護(hù)日志更新組表文件，可使用下面的腳本：

A2、B2:從數(shù)據(jù)庫查出應(yīng)刪除的記錄

A3、B3:從數(shù)據(jù)查出應(yīng)修改和新增的記錄

A5、B5:對組表進(jìn)行刪除操作。

A6、B6:從組表進(jìn)行修改新增操作。

A7、B7:清空維護(hù)日志表，以便下次繼續(xù)更新數(shù)據(jù)。

六、        T+0實時計算

通過定時追加，能保證組表文件與昨天的數(shù)據(jù)同步，從而實現(xiàn)T+1計算，但有時需要進(jìn)行實時大主表關(guān)聯(lián)，即T+0計算。

對于T+0計算，需要將兩種不同的數(shù)據(jù)源進(jìn)行混合計算，由于SQL或SP的數(shù)據(jù)模型較為封閉，因此難以實現(xiàn)混合計算，而使用集算器就非常簡單。

比如對組表文件定時追加后，數(shù)據(jù)庫當(dāng)天又產(chǎn)生了如下新數(shù)據(jù)：

可使用如下腳本實現(xiàn)T+0實時計算：

A1:算出當(dāng)天的起始時間點，即2018-11-26 00:00:00。

A3:針對數(shù)據(jù)庫當(dāng)天產(chǎn)生的新數(shù)據(jù)，進(jìn)行關(guān)聯(lián)計算。由于當(dāng)天數(shù)據(jù)量較小，因此性能可以接受。

A4-A7:針對組表文件歷史數(shù)據(jù)，進(jìn)行高性能關(guān)聯(lián)計算。

A8:合并當(dāng)天和歷史，并進(jìn)行二次計算，以獲得最終計算結(jié)果。其中符號|表示縱向合并，這是實現(xiàn)混合計算的關(guān)鍵。事實上，這種寫法也表明集算器支持任意數(shù)據(jù)源之間的混合計算，比如Excel與elasticSearch之間。

關(guān)于T+0計算更多的細(xì)節(jié)，可參考相關(guān)文章《實時報表 T+0 的實現(xiàn)方案》