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

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

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

DOIDOI：10.11907/rjdk.172242

中圖分類號：TP301

文獻標識碼：A文章編號文章編號：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ù)清算過程中，若需要對數(shù)據(jù)庫中的數(shù)據(jù)作全量查重，必須先進行全量數(shù)據(jù)的核查及歷史同步兩個關(guān)鍵步驟。通過對一卡通數(shù)據(jù)需求的調(diào)研分析可知，一卡通歷史數(shù)據(jù)具有以下兩大特點：①數(shù)據(jù)量龐大。以廣東嶺南通卡為例，目前約有320億條歷史數(shù)據(jù)存儲在數(shù)據(jù)庫中，成為企業(yè)數(shù)據(jù)分析的重要基礎(chǔ)資源；②數(shù)據(jù)存儲方式具有多樣性。根據(jù)不同的業(yè)務(wù)應(yīng)用需求，歷史數(shù)據(jù)分別存儲于數(shù)據(jù)庫和文件中。

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

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

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

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

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

1.1歷史查重數(shù)據(jù)同步

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

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

（2）對于數(shù)據(jù)倉庫交易數(shù)據(jù)，需在清分數(shù)據(jù)庫中建立Dblink后，創(chuàng)建存儲過程完成查重數(shù)據(jù)同步。

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

1.2清分過程歷史查重

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

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

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

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

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

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

3實驗案例分析

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

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

區(qū)分了CPU和M1后，各自數(shù)據(jù)仍十分巨大，再按票卡的發(fā)行時間（或首次交易時間）取得年份，按格式“T_LNT_CPU_KEYS_年份”形成表名，將該年的交易數(shù)據(jù)存儲至此表下。按5年交易時間累計，獲得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卡的交易查重數(shù)據(jù)量雖然明顯減少，但對于幾十億數(shù)據(jù)的查詢?nèi)源嬖诓簧偬魬?zhàn)。為進一步減少查詢量級，現(xiàn)對各分表建立分區(qū)?？紤]分區(qū)數(shù)據(jù)最好均勻，因此采用HASH分區(qū)，按票卡號（LCN）劃分256個分區(qū)，數(shù)據(jù)如表3所示。

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

對同一張票卡，所使用的SQL查詢語句如下：

SELECT COUNT（1） FROM T_LNT_CPU_KEYS_2016

WHERE LCN=？ AND SEQ_KEY=？

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

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

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

4結(jié)語

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

參考文獻：

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

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

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

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

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

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

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

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

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

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

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

（責(zé)任編輯：黃健）endprint