雷麗芳　李麗芬

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

關(guān)鍵詞：BO報表；數(shù)據(jù)庫；索引；SQL語句

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

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

1 BO簡介

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

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

2 BO報表存在的問題

2.1 聚集量過大

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

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

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

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

2.2 不合理的索引設(shè)計

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

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

2.3 不合理的數(shù)據(jù)分區(qū)設(shè)計

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

2.4 SQL語句條件過多，查詢復(fù)雜

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

3 解決措施

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

3.1 減少聚集量

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

3.2 建立合理的索引

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

select count（*） from ObjTransaction where

TransactionDateTime>20100630and DeviceID= ‘50465025

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

3.3 建立合理的數(shù)據(jù)分區(qū)

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

3.4 合理的查詢語句

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

4 總結(jié)

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

〔編輯：王霞〕