基于AFC系統(tǒng)數(shù)據(jù)統(tǒng)計(jì)優(yōu)化設(shè)計(jì)

雷麗芳　李麗芬

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

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

中圖分類號(hào)：TP311.13 文獻(xiàn)標(biāo)識(shí)碼：A DOI：10.15913/j.cnki.kjycx.2016.03.088

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

1 BO簡(jiǎn)介

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

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

2 BO報(bào)表存在的問題

2.1 聚集量過大

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

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

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

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

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

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

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

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

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

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

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

3 解決措施

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

3.1 減少聚集量

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

3.2 建立合理的索引

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

select count（*） from ObjTransaction where

TransactionDateTime>20100630and DeviceID= ‘50465025

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

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

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

3.4 合理的查詢語句

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

4 總結(jié)

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

〔編輯：王霞〕