謝振東+陳衛(wèi)國(guó)+徐鋒+何建兵+張景奎+羅鳴鳴

摘要：為更好地解決目前普遍存在的數(shù)據(jù)清分過(guò)程中的查重技術(shù)效率不高的問(wèn)題，達(dá)到全數(shù)據(jù)查重流程優(yōu)化的目的，從查詢字段設(shè)計(jì)、存儲(chǔ)過(guò)程同步、數(shù)據(jù)庫(kù)優(yōu)化等方面對(duì)全數(shù)據(jù)查重技術(shù)進(jìn)行深入研究和分析，提出一套高效、準(zhǔn)確的全數(shù)據(jù)查重方法及應(yīng)用方案。實(shí)驗(yàn)結(jié)果表明，采用該查重方法能大幅提升大數(shù)據(jù)條件下的查重效率，有效緩解由于大量數(shù)據(jù)掃描導(dǎo)致系統(tǒng)IO占用過(guò)高，從而引發(fā)的系統(tǒng)性能下降。因此，該研究將為大數(shù)據(jù)背景下的一卡通數(shù)據(jù)清分管理系統(tǒng)提供安全、可靠和快速的數(shù)據(jù)查重，為企業(yè)、商戶、客戶提供準(zhǔn)確的清算結(jié)果和報(bào)表奠定重要基礎(chǔ)。

關(guān)鍵詞：交通一卡通；大數(shù)據(jù)清分；全數(shù)據(jù)查重；數(shù)據(jù)庫(kù)優(yōu)化

DOIDOI：10.11907/rjdk.172242

中圖分類號(hào)：TP301

文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)文章編號(hào)：16727800（2018）001003503

Abstract：In order to better solve the problem of low efficiency in the process of sorting data， and realize the purpose of the full data checking process optimization， This study will conduct indepth study and analysis on the whole data check technology from the aspects of query field design， stored procedure synchronization and database optimization， and presents a set of efficient and accurate data checking method and application.The experimental results show that this method can greatly improve the efficiency of inspection under the condition of large data， and effectively alleviate the system IO occupancy due to the large amount of data scanning， which leads to the performance degradation of the system.Therefore， this study will provide a safe， reliable and fast data check for the traffic card data sorting system in the context of large data， and lay an important foundation for the enterprise， businesses， customers to provide accurate accounting results and reports.

Key Words：IC card； big data clearing； data checking； database optimization

0引言

城市交通一卡通清分管理系統(tǒng)在交易數(shù)據(jù)清算過(guò)程中，若需要對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)作全量查重，必須先進(jìn)行全量數(shù)據(jù)的核查及歷史同步兩個(gè)關(guān)鍵步驟。通過(guò)對(duì)一卡通數(shù)據(jù)需求的調(diào)研分析可知，一卡通歷史數(shù)據(jù)具有以下兩大特點(diǎn)：①數(shù)據(jù)量龐大。以廣東嶺南通卡為例，目前約有320億條歷史數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)中，成為企業(yè)數(shù)據(jù)分析的重要基礎(chǔ)資源；②數(shù)據(jù)存儲(chǔ)方式具有多樣性。根據(jù)不同的業(yè)務(wù)應(yīng)用需求，歷史數(shù)據(jù)分別存儲(chǔ)于數(shù)據(jù)庫(kù)和文件中。

根據(jù)以上兩個(gè)特點(diǎn)，在數(shù)據(jù)清分管理機(jī)制設(shè)計(jì)上需進(jìn)行以下考慮：對(duì)歷史數(shù)據(jù)進(jìn)行實(shí)時(shí)清分時(shí)，要求與全量數(shù)據(jù)進(jìn)行實(shí)時(shí)查重核對(duì)，防止重復(fù)記錄。考慮到未來(lái)一卡通業(yè)務(wù)的增長(zhǎng)性，清分系統(tǒng)設(shè)計(jì)必須達(dá)到一定的數(shù)據(jù)處理能力，如10W/min（每分鐘10萬(wàn)條記錄）。若按照原有清分方案，即把歷史查重?cái)?shù)據(jù)存放在同一數(shù)據(jù)庫(kù)中，將導(dǎo)致查詢性能大幅下降。

因此，在新的清分系統(tǒng)中，必須采用合理的存儲(chǔ)方案，以提高查詢性能，并且支持橫向擴(kuò)展。另外，在存儲(chǔ)結(jié)構(gòu)設(shè)計(jì)上采用單一化方式，對(duì)不同存儲(chǔ)方式的歷史數(shù)據(jù)進(jìn)行全量同步，這就要求全量歷史交易數(shù)據(jù)按統(tǒng)一格式構(gòu)建查重字段，存放于數(shù)據(jù)庫(kù)查重表中。

一般而言，一卡通交易數(shù)據(jù)查重是由以下4個(gè)字段組成：交易設(shè)備編號(hào)（PID）、票卡邏輯卡號(hào)（LCN）、脫機(jī)交易流水號(hào)（PSN）和交易認(rèn)證碼（TAC）。為提升查重效率，系統(tǒng)將不會(huì)直接以這4個(gè)字段為查詢關(guān)鍵字，而是將這些字段拼接成一個(gè)完整字段再進(jìn)行查詢。

1全量查重流程設(shè)計(jì)

圖1從總體設(shè)計(jì)和構(gòu)建了存量歷史數(shù)據(jù)、查重同步程序及清分程序等3個(gè)階段的交互方式，展示了歷史交易數(shù)據(jù)同步到全量查重表的方式，以及數(shù)據(jù)清分過(guò)程中對(duì)數(shù)據(jù)進(jìn)行歷史查重的設(shè)計(jì)流程。

1.1歷史查重?cái)?shù)據(jù)同步

在全量查重之前，首先需要同步以往所有的交易數(shù)據(jù)，生成符合要求的待查重?cái)?shù)據(jù)，并保存到查重?cái)?shù)據(jù)表中，如圖2所示。這里所指的歷史數(shù)據(jù)主要來(lái)源于3方面，即以往保存的歷史交易文件、數(shù)據(jù)倉(cāng)庫(kù)保存的歷史交易記錄以及其它非規(guī)則文件。根據(jù)數(shù)據(jù)來(lái)源結(jié)構(gòu)不同，分別采取不同的同步方法：

（1）對(duì)于交易文件，其記錄格式是固定的，前期可采取SqlLoad快速批量導(dǎo)入。

（2）對(duì)于數(shù)據(jù)倉(cāng)庫(kù)交易數(shù)據(jù)，需在清分?jǐn)?shù)據(jù)庫(kù)中建立Dblink后，創(chuàng)建存儲(chǔ)過(guò)程完成查重?cái)?shù)據(jù)同步。

（3）對(duì)于非規(guī)則數(shù)據(jù)，利用日志分析引擎讀取并分析、查找交易數(shù)據(jù)，通過(guò)數(shù)據(jù)清洗及結(jié)構(gòu)化后生成查重?cái)?shù)據(jù)文件，最后利用SqlLoad同步到查重表。endprint

1.2清分過(guò)程歷史查重

在清分過(guò)程中，數(shù)據(jù)預(yù)處理是對(duì)每條數(shù)據(jù)逐一進(jìn)行處理，通過(guò)文件、關(guān)鍵字段檢查后才會(huì)對(duì)交易數(shù)據(jù)作歷史查重。而歷史查重的關(guān)鍵是對(duì)表數(shù)據(jù)的查詢，因此系統(tǒng)必須對(duì)查重表進(jìn)行優(yōu)化。為解決海量異構(gòu)數(shù)據(jù)存儲(chǔ)的問(wèn)題，可采用一種基于本體概念模型與NoSQL數(shù)據(jù)庫(kù)的中間數(shù)據(jù)存儲(chǔ)模型方案[12]。

通過(guò)對(duì)城市一卡通在公交、地鐵的歷史消費(fèi)記錄分析得出，同一張交通卡可能出現(xiàn)多筆交易記錄。針對(duì)這種情況，可把同一張票卡數(shù)據(jù)組合成一條SQL語(yǔ)句，一次性完成數(shù)據(jù)查詢，以降低數(shù)據(jù)庫(kù)服務(wù)器的連接開(kāi)銷，如圖3所示。

對(duì)于輸入中的交易數(shù)據(jù)，在經(jīng)過(guò)以上步驟后已完成了按LCN的排序，因此在LCN分組緩存環(huán)節(jié)中能快速聚合同一票卡的所有交易記錄，將同一票卡的交易數(shù)據(jù)構(gòu)建成一條SQL語(yǔ)句，并同時(shí)發(fā)送到清分?jǐn)?shù)據(jù)庫(kù)和NoSQL數(shù)據(jù)庫(kù)中查詢，得到查重結(jié)果[34]。

若查詢結(jié)果顯示數(shù)據(jù)沒(méi)有重復(fù)，則數(shù)據(jù)須第一時(shí)間被緩存至NoSQL當(dāng)天的交易數(shù)據(jù)表，以備下一條數(shù)據(jù)的查重。當(dāng)完成數(shù)據(jù)包清分后，NoSQL當(dāng)天的交易數(shù)據(jù)須轉(zhuǎn)成查重?cái)?shù)據(jù)文件，并定時(shí)導(dǎo)入到清分?jǐn)?shù)據(jù)庫(kù)查重表中。

2數(shù)據(jù)庫(kù)優(yōu)化設(shè)計(jì)

為進(jìn)一步提升全量數(shù)據(jù)的查詢效率，對(duì)數(shù)據(jù)庫(kù)進(jìn)行了一定優(yōu)化。本文從以下3個(gè)維度進(jìn)行探討：范式優(yōu)化、索引優(yōu)化和查詢優(yōu)化。首先通過(guò)范式優(yōu)化從中取優(yōu)的方式設(shè)計(jì)一個(gè)邏輯，經(jīng)過(guò)多重模式競(jìng)爭(zhēng)，構(gòu)建一種更為合理的邏輯結(jié)構(gòu)。在數(shù)據(jù)庫(kù)的物理設(shè)計(jì)上，利用索引優(yōu)化對(duì)數(shù)據(jù)庫(kù)的各個(gè)屬性及相關(guān)組合屬性進(jìn)行全面優(yōu)化，從而得到較為緊密的物理結(jié)構(gòu)。最后一步是數(shù)據(jù)庫(kù)查詢工作，通過(guò)優(yōu)化查詢語(yǔ)句，提升數(shù)據(jù)查詢執(zhí)行效率[56]。

3實(shí)驗(yàn)案例分析

以廣東嶺南通卡為樣本進(jìn)行實(shí)例分析。以下分析所提到CPU卡是指卡片上的芯片含有一個(gè)微處理器，可配置COS系統(tǒng)，具有容量大、金融安全級(jí)別高的特點(diǎn)；M1卡是一種前期常用的非接觸式IC卡，是菲利浦下屬子公司恩智浦出品的芯片縮寫，全稱為NXP Mifare1系列，因價(jià)格便宜而獲得了廣泛應(yīng)用，但又因安全性不足逐漸被CPU卡取代。CPU卡與M1卡的存儲(chǔ)格式和處理機(jī)制有所不同。為將數(shù)據(jù)分析和查詢難度降低，本文采用了物理分表和邏輯分表兩種方式進(jìn)行處理。

（1）物理分區(qū)。將CPU卡和M1卡的交易數(shù)據(jù)獨(dú)立分開(kāi)，將數(shù)據(jù)存儲(chǔ)到不同的表中，初步減少無(wú)關(guān)數(shù)據(jù)的查詢。若按320億數(shù)據(jù)3∶2的比率預(yù)估，CPU為190億，M1為128億。

區(qū)分了CPU和M1后，各自數(shù)據(jù)仍十分巨大，再按票卡的發(fā)行時(shí)間（或首次交易時(shí)間）取得年份，按格式“T_LNT_CPU_KEYS_年份”形成表名，將該年的交易數(shù)據(jù)存儲(chǔ)至此表下。按5年交易時(shí)間累計(jì)，獲得CPU卡交易存放的數(shù)據(jù)表有T_LNT_CPU_KEYS_2012～T_LNT_CPU_KEYS_2016共5張表，每張表的數(shù)據(jù)約為38億，而M1為25.6億，如表1所示。

物理分表分區(qū)后表結(jié)構(gòu)如表2所示。

（2）邏輯分區(qū)。物理分表以后，CPU卡和M1卡的交易查重?cái)?shù)據(jù)量雖然明顯減少，但對(duì)于幾十億數(shù)據(jù)的查詢?nèi)源嬖诓簧偬魬?zhàn)。為進(jìn)一步減少查詢量級(jí)，現(xiàn)對(duì)各分表建立分區(qū)?？紤]分區(qū)數(shù)據(jù)最好均勻，因此采用HASH分區(qū)，按票卡號(hào)（LCN）劃分256個(gè)分區(qū)，數(shù)據(jù)如表3所示。

按近5年交易年限來(lái)算，3 000萬(wàn)CPU卡的交易數(shù)據(jù)在物理分表分區(qū)后，同一張CPU票卡全量查詢的數(shù)據(jù)范圍為2.5萬(wàn)=1 485萬(wàn)/（3 000萬(wàn)/5），同理M1為2.5萬(wàn)，查詢數(shù)據(jù)范圍得到縮小。

對(duì)同一張票卡，所使用的SQL查詢語(yǔ)句如下：

SELECT COUNT（1） FROM T_LNT_CPU_KEYS_2016

WHERE LCN=？ AND SEQ_KEY=？

通過(guò)大量數(shù)據(jù)測(cè)試及分析，對(duì)分區(qū)表中聯(lián)合查重鍵（SEQ_KEY）、邏輯票卡（LCN）建立分區(qū)索引，查詢速率更高。

如果查詢表上只有主鍵而沒(méi)有索引，那么很可能會(huì)引發(fā)全表掃描，特別是當(dāng)數(shù)據(jù)量較大時(shí)，性能問(wèn)題將成為瓶頸，導(dǎo)致磁盤IO過(guò)高，降低系統(tǒng)吞吐量[79]。

多達(dá)數(shù)百億條的歷史交易數(shù)據(jù)若在一個(gè)表中完成查重，必然導(dǎo)致查找速度十分緩慢[1011]。為提升效率，必須將數(shù)據(jù)合理切分。本研究得出的建議是：對(duì)于查重表可采用聯(lián)合查看鍵、邏輯卡號(hào)這兩字段，而對(duì)于交易數(shù)據(jù)，可通過(guò)SEQ_KEY查詢。SEQ_KEY是由本次交易設(shè)備編號(hào)（PID）+票卡邏輯卡號(hào)（LCN）+脫機(jī)交易流水號(hào)（PSN）+交易認(rèn)證碼（TAC）拼接而成的。

4結(jié)語(yǔ)

為更好地適應(yīng)公共交通領(lǐng)域一卡通快捷交易的需求，城市交通一卡通終端系統(tǒng)通常設(shè)置為脫機(jī)交易，非實(shí)時(shí)的脫機(jī)交易數(shù)據(jù)在清分時(shí)需要提供全數(shù)據(jù)查重功能。因此，這些歷史海量數(shù)據(jù)和新增數(shù)據(jù)需要一套穩(wěn)定、高效、快速的全數(shù)據(jù)查重算法技術(shù)，以及硬件設(shè)備在物理效率上提供支持，使城市一卡通業(yè)務(wù)的安全性得到保證。

參考文獻(xiàn)：

[1]唐洪奎，張程，劉驥.基于NoSQL的物聯(lián)網(wǎng)數(shù)據(jù)本體模型存儲(chǔ)技術(shù)研究與實(shí)現(xiàn)[J].軟件，2017（3）：2733.

[2]張德山，李海浩.海量數(shù)據(jù)存儲(chǔ)管理方法的研究[J].信息化研究，2011，37（4）：47.

[3]常廣炎，李邐.大數(shù)據(jù)查詢與分析技術(shù)——SQL on Hadoop[J].軟件導(dǎo)刊，2016，15（4）：1315.

[4]沈嘯.基于Oracle數(shù)據(jù)庫(kù)海量數(shù)據(jù)的查詢優(yōu)化研究[J].無(wú)線互聯(lián)科技，2016（10）：106107.

[5]孟瑞軍.數(shù)據(jù)庫(kù)優(yōu)化策略分析[J].探索科技，2016（12）：5556.

[6]曾明霏，劉強(qiáng).基于SQL和表設(shè)計(jì)的Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)審計(jì)研究[J].軟件導(dǎo)刊，2016，15（12）：136138.

[7]劉陽(yáng)成，周儉，謝玉.海量數(shù)據(jù)存儲(chǔ)管理技術(shù)研究[J].網(wǎng)絡(luò)新媒體技術(shù)，2011，32（10）：3336.

[8]王銘坤，袁少光，朱永利，等.基于Storm的海量數(shù)據(jù)實(shí)時(shí)聚類[J].計(jì)算機(jī)應(yīng)用，2014，34（11）：30783081.

[9]侯建，帥仁俊，侯文，等.基于云計(jì)算的海量數(shù)據(jù)存儲(chǔ)模型[J].通信技術(shù)，2011，44（5）：163165.

[10]周顯春，肖衡.Spark 2.0平臺(tái)在大數(shù)據(jù)處理中的應(yīng)用研究[J].軟件導(dǎo)刊，2017，16（5）：149151.

[11]張昕，曾鵬，張瑞，等.交通大數(shù)據(jù)的特征及價(jià)值[J].軟件導(dǎo)刊，2016，15（3）：130132.

（責(zé)任編輯：黃?。〆ndprint