基于AFC系統數據統計優化設計

雷麗芳　李麗芬

摘 要：目前，南京市地鐵基于AFC系統的數據統計主要是通過中央報表完成的，而報表的開發主要是以BO軟件為開發工具。在使用過程中我們發現，報表的使用效率越來越低，報表的刷新速度隨著數據量的增加越來越緩慢。通過多方面分析得出，報表使用效率低的根本原因是數據庫聚集和數據庫性能問題引起的。分析了報表系統和數據庫聚集調優等方面存在的問題，并提出了解決措施。

關鍵詞：BO報表；數據庫；索引；SQL語句

中圖分類號：TP311.13 文獻標識碼：A DOI：10.15913/j.cnki.kjycx.2016.03.088

報表有助于從現有數據中匯總出所需要的信息。南京市地鐵AFC中央系統報表以BO軟件為開發系統，主要包括維修類、收益類、客流類、庫存類、操作類五類報表。提高南京市地鐵AFC中央系統報表的性能，首先要從報表的數據源著手，提高數據庫的性能和聚集效率。

1 BO簡介

BO，全稱Business Objects，主要功能是對數據倉庫中的數據進行前臺展示。BO是一個多層、瘦客戶決策支持系統（決策支持系統），為非技術最終用戶提供了對存儲在數據庫、數據倉庫、數據市場和商業應用軟件包中的數據進行隨機查詢和分析的功能。

BO報表的優點在于：①用戶可以在一定程度上定制、修改報表；②報表與統計圖之間可以互相任意轉換；③報表可以對疑問數據進行一定層次內的追蹤分析；④報表可以存為多種格式，可以被用戶在Word文檔中引用；⑤用戶可以使用日常術語來訪問數據，從而取代開發商自行進行一些日常維護；⑥報表可以對數據方便地自動進行各類匯總計算和分組排序。

2 BO報表存在的問題

2.1 聚集量過大

線路報表查詢條件復雜、聚集量大，數據庫每日、每刻和每分鐘都有聚集運作，所產生的聚集數據一直堆積在數據庫中，數據庫中往往保存了3個月以上的聚集數據。初期數據刪除程序未正常使用，數據一直未得到清理，使Repquarter、Repdaily等數據庫表內容繁雜、查找困難，一些以這類數據庫表為源表的報表也查詢緩慢，最終導致報表刷新超時，無法顯示。為預防此類現象出現，首先要檢查數據庫中聚集報表數據保存的天數，刪減陳舊記錄，例如日報表保存1個月的數據，月報表保存3個月的數據；然后檢查數據刪除程序是否正常運作，保證數據庫中只保留一定期限的數據。

從目前中央系統數據庫設計來看，未建立聚集索引是導致數據庫聚集效率低的一個重要因素。聚集索引能夠高效確定表中數據的物理順序，其類似于電話簿按姓氏排列數據。由于聚集索引規定了數據在表中的物理存儲順序，因此一個表只能包含一個聚集索引，但該索引可以包含多個列（組合索引），就像電話簿按姓氏和名字組織排列一樣。

聚集索引對于那些經常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行后，便可以確保包含后續索引值的行與其物理相鄰。例如，如果應用程序執行的一個查詢任務經常檢索某一日期范圍內的記錄，則使用聚集索引便可以迅速找到包含開始日期的行，然后檢索表中所有相鄰的行，直到到達結束日期。這樣有助于提高此類查詢的效率。同樣，如果對表中檢索的數據進行排序時經常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都要進行排序，從而節省時間。

當索引值唯一時，使用聚集索引查找特定的行效率也很高。例如，使用唯一交易ID列TransactionID查找特定交易最快速的方法，是在TransactionID列上創建聚集索引或PRIMARY KEY約束。

2.2 不合理的索引設計

索引（index）是各種關系數據庫系統中最常見的一種邏輯單元，是數據庫系統重要的組成部分，對提高數據檢索速度有著至關重要的作用。索引的原理是根據索引值得到行指針，然后快速定位到數據庫記錄。目前來看，AFC中央系統數據的使用效率和資源開銷都需要得到進一步的優化，以提高數據查詢速度，為交易、日志、事件、基礎數據表建立合理的索引。數據庫表更新大量數據后，不合適的索引嚴重影響了查詢速度，大大降低了數據庫的使用效率。

建立“適當”的索引是實現查詢優化的首要前提。索引是除表之外另一重要的、用戶定義的存儲在物理介質上的數據結構。當根據索引碼的值搜索數據時，索引提供了對數據的快速訪問。事實上，沒有索引，數據庫也能根據SELECT語句成功檢索到結果，但隨著表變得越來越大，使用“適當”的索引能明顯提高查詢效率。

2.3 不合理的數據分區設計

對于一些超大型的表，分區是非常有用的。分區是一種邏輯概念，一個表就是一個整體，當發生數據訪問的時候，也是對整個表或整個表的索引進行訪問。所謂“分區”，通俗點講，就是把表按一定的規律劃分成更小的邏輯單位，當發生訪問的時候，不以表為單位進行訪問，而是先在表的基礎上判斷數據在哪個分區，然后對特定的分區進行訪問。正確的分區有利于提高查詢效率。

2.4 SQL語句條件過多，查詢復雜

目前，南京市地鐵AFC系統報表效率過低的另一重要原因就是聚集中使用的SQL語句只關注所得結果是否正確，而忽略了不同的實現方法之間可能存在的性能差異。一個SQL語句大約要經過三個階段，即編譯優化、執行和取值。大部分情況下，第一階段要花掉60%的時間，所以綁定變量是很重要的。SQLserver有緩存區，用以存放最近使用的SQL語句，當有一條SQL語句到達數據庫服務器時，數據庫會首先搜索緩存區，看它是否存在可以重用的SQL語句。如果存在，則無需編譯優化，因為緩存區的SQL語句都是編譯優化好的，可以直接執行，節省了相當多的時間；如果沒有發現該語句，則必須要經歷語句編譯分析、優化計劃和安全檢查等過程。這不僅耗費了大量的CPU功率，而且還在相當長的一段時間內鎖住了一部分數據庫緩存。這樣執行SQL語句的人越多，等待的時間越長，系統的性能就會大幅降低。

3 解決措施

表的聚集量過大、不合適的表索引設計、不合適的數據庫分區、效率低下的SQL語句都會影響到報表的使用。下面我們將針對目前南京市地鐵中央系統數據庫現行的報表系統處理過慢、效率過低這一問題提出解決措施。

3.1 減少聚集量

目前，每日數據庫計劃任務中的聚集信息包括各類設備的交易、事件、審計和日志等大量信息，而我們報表中用到的只有交易和審計，所以我們可以考慮在每日的聚集工作中只匯總交易和審計類信息，這樣可以大大降低聚集工作的工作量。此外，由于聚集信息量過大，所需時間過長，這樣在某段時間內就會與其他計劃任務沖突，從而使聚集任務失敗，而當再次執行聚集任務時，就會產生聚集信息重復的問題。對此，可以在聚集中添加事務回滾機制，從而提高報表的準確性。

3.2 建立合理的索引

索引表的優勢主要體現在數據查詢上，而且這個優勢非常明顯。索引表能夠獲得比標準表更快的查詢速度，即使這張標準表已經建立了合適的索引。這與索引表的存儲結構是分不開的。因為索引表的數據在存儲的時候，所有的行記錄都與排序過的主鍵列一起存儲在數據庫系統中，所以在查詢的時候，只需找到主鍵，就可以查詢到整條記錄信息。索引表的使用減少了數據查詢過程中的中間環節，避免了額外的數據塊讀取操作。目前，中央系統數據庫AFCCOOKED各類交易信息表中已建立了一定的索引，例如，ObjTransaction是AFC系統中記錄交易信息的表，觀察其在不同索引下的查詢運行狀態，并測試其在C/S環境下的運行效果。以下為引用的內容：

select count（*） from ObjTransaction where

TransactionDateTime>20100630and DeviceID= ‘50465025

不建任何索引所需查詢時間為120 s左右，在ObjTransaction上建立非聚簇索引查所需詢時間為157 s。從查詢效果分析，索引的有無、建立方式的不同將會導致不同的查詢效果，選擇什么樣的索引基于用戶對數據的查詢條件，這些條件體現在where從句和join表達式中。所以，應在ObjTransaction表的主鍵列上建立聚簇索引，這樣可以大大提高數據庫的使用效率。

3.3 建立合理的數據分區

目前，南京市地鐵AFC系統的交易表是一個非常大的表，存儲了各種票卡的交易信息。現在查詢總是按照交易日期和交易類型來執行，每天有大量的交易記錄，通常我們也只是查詢這個數據集中的一個相當小的數據，例如交易金額、進出站代碼、交易日期等。檢索某一交易信息時，這個索引可能指向無數個記錄，而這種執行索引范圍是非常大的。為了完成查詢任務，系統需要執行全表掃描，必須掃描幾萬條記錄，其中絕大部分不是我們所需的信息。使用智能分區方案，就可以將交易表常用信息與非常用信息進行分區存儲。這樣根據需要到某個特定區域內查找數據，能夠大大提高查詢效率。

3.4 合理的查詢語句

要提高報表的效率，就要使用報表產生的SQL語句對查詢進行優化，盡量避免全表掃描。首先應考慮在where和order by涉及的列上建立索引，以免在where子句中對字段進行null值判斷，導致引擎放棄使用索引而進行全表掃描。

4 總結

本文通過解決聚集量過大的問題，設計合理的表索引、數據庫分區，提高SQL語句的使用效率，大大提高了報表的使用效率。

〔編輯：王霞〕