交通一卡通清分管理中的全數據查重方法研究

謝振東+陳衛國+徐鋒+何建兵+張景奎+羅鳴鳴

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

關鍵詞：交通一卡通；大數據清分；全數據查重；數據庫優化

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引言

城市交通一卡通清分管理系統在交易數據清算過程中，若需要對數據庫中的數據作全量查重，必須先進行全量數據的核查及歷史同步兩個關鍵步驟。通過對一卡通數據需求的調研分析可知，一卡通歷史數據具有以下兩大特點：①數據量龐大。以廣東嶺南通卡為例，目前約有320億條歷史數據存儲在數據庫中，成為企業數據分析的重要基礎資源；②數據存儲方式具有多樣性。根據不同的業務應用需求，歷史數據分別存儲于數據庫和文件中。

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

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

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

1全量查重流程設計

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

1.1歷史查重數據同步

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

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

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

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

1.2清分過程歷史查重

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

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

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

若查詢結果顯示數據沒有重復，則數據須第一時間被緩存至NoSQL當天的交易數據表，以備下一條數據的查重。當完成數據包清分后，NoSQL當天的交易數據須轉成查重數據文件，并定時導入到清分數據庫查重表中。

2數據庫優化設計

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

3實驗案例分析

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

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

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

物理分表分區后表結構如表2所示。

（2）邏輯分區。物理分表以后，CPU卡和M1卡的交易查重數據量雖然明顯減少，但對于幾十億數據的查詢仍存在不少挑戰。為進一步減少查詢量級，現對各分表建立分區?？紤]分區數據最好均勻，因此采用HASH分區，按票卡號（LCN）劃分256個分區，數據如表3所示。

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

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

SELECT COUNT（1） FROM T_LNT_CPU_KEYS_2016

WHERE LCN=？ AND SEQ_KEY=？

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

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

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

4結語

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

參考文獻：

[1]唐洪奎，張程，劉驥.基于NoSQL的物聯網數據本體模型存儲技術研究與實現[J].軟件，2017（3）：2733.

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

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

[4]沈嘯.基于Oracle數據庫海量數據的查詢優化研究[J].無線互聯科技，2016（10）：106107.

[5]孟瑞軍.數據庫優化策略分析[J].探索科技，2016（12）：5556.

[6]曾明霏，劉強.基于SQL和表設計的Oracle數據庫開發審計研究[J].軟件導刊，2016，15（12）：136138.

[7]劉陽成，周儉，謝玉.海量數據存儲管理技術研究[J].網絡新媒體技術，2011，32（10）：3336.

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

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

[10]周顯春，肖衡.Spark 2.0平臺在大數據處理中的應用研究[J].軟件導刊，2017，16（5）：149151.

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

（責任編輯：黃?。〆ndprint