經(jīng)濟普查全國數(shù)據(jù)庫優(yōu)化方案

1、經(jīng)濟普查全國數(shù)據(jù)庫優(yōu)化方案隨著各省、自治區(qū)、直轄市（以下簡稱：各省級單位）的第一次全國經(jīng)濟普查（以下簡稱：經(jīng)濟普查）數(shù)據(jù)上報工作接近尾聲，國家級數(shù)據(jù)處理工作正大規(guī)模地展開，經(jīng)濟普查全國數(shù)據(jù)庫的建設也被提上日程。國家級數(shù)據(jù)處理的主要任務包括下面幾項： 1給各省級單位報送的數(shù)據(jù)建立處理環(huán)境，執(zhí)行統(tǒng)一的審核、匯總程序，并將結果與同時上報的審核錯誤清單和匯總數(shù)據(jù)進行比較，如果兩者不同或有其他問題，通知原報送單位重新報送；2將各省級單位報送的數(shù)據(jù)合并到一個處理環(huán)境中，執(zhí)行各專業(yè)要求的審核、匯總程序，并由各專業(yè)做進一步的審核、查詢得出最終確定的數(shù)據(jù)集。將來在此數(shù)據(jù)集基礎上可以構建全國基本單位名錄庫和其他專

2、業(yè)的全國數(shù)據(jù)庫，提供給各級政府統(tǒng)計部門、其他政府部門和科研機構使用，即建立經(jīng)濟普查全國數(shù)據(jù)庫。3按處理地從全國處理環(huán)境中合并導出各省級單位數(shù)據(jù)并建立獨立的處理環(huán)境，再次分別執(zhí)行統(tǒng)一的審核、匯總程序，并由各專業(yè)確認無誤后反饋各地區(qū)。 國家級數(shù)據(jù)處理的流程和省級、地（市）級沒有本質(zhì)的差別，國家級和省級處理的最明顯差別是數(shù)據(jù)量上的差別，填報目錄（法人單位產(chǎn)業(yè)活動單位）記錄超過了700萬條，其他30余張專業(yè)基層表的記錄從幾十萬到數(shù)百萬不等。因此，實現(xiàn)快速地從如此大容量的數(shù)據(jù)庫中提取數(shù)據(jù)（查詢）、分析、統(tǒng)計以及提取數(shù)據(jù)后進行數(shù)據(jù)展示，已成為亟待解決的難題。經(jīng)濟普查數(shù)據(jù)匯集到國家級的時候，數(shù)據(jù)庫的性質(zhì)已經(jīng)

3、逐漸地發(fā)生了改變，從一個聯(lián)機事務處理（OLTP）系統(tǒng)轉(zhuǎn)變?yōu)橐粋€決策分析支持（DSS）系統(tǒng)。聯(lián)機事務處理系統(tǒng)有大量的用戶同時連接，并發(fā)操作很多，有大量的數(shù)據(jù)增刪改，而每次更改涉及的記錄數(shù)較少，對系統(tǒng)的響應時間要求較高。決策分析支持系統(tǒng)是大數(shù)據(jù)量的查詢，大批量的數(shù)據(jù)導入和導出，涉及的記錄數(shù)很多，對系統(tǒng)的響應時間要求不太高，但是對一個長時間操作耗費的總時間要求提高。由于兩種類型系統(tǒng)應用特點的巨大差異，在聯(lián)機事務處理系統(tǒng)中有效率的設計在決策分析支持系統(tǒng)中變得不再有效率，需要進行分析、調(diào)整、優(yōu)化。一、減少數(shù)據(jù)冗余在數(shù)據(jù)采集階段，調(diào)查對象的數(shù)據(jù)的一些統(tǒng)計特征，例如某專業(yè)基層表的填滿率，數(shù)據(jù)量地區(qū)分布等是未

4、知的，盡管可以從歷史數(shù)據(jù)中獲得某些信息，但全國的統(tǒng)計特征信息不一定適用于地方，因此數(shù)據(jù)采集系統(tǒng)中不需要考慮數(shù)據(jù)的統(tǒng)計特征。數(shù)據(jù)匯集到國家級后，即使個別數(shù)據(jù)還會進行訂正、增補，但總體來說，數(shù)據(jù)的整體特征已經(jīng)固定，不會有大的改變。為了提高進一步處理的效率，就得針對既有數(shù)據(jù)的統(tǒng)計特征進行數(shù)據(jù)結構的調(diào)整，其中最首要的，是減少數(shù)據(jù)冗余。所謂冗余數(shù)據(jù)，有兩種含義，第一種，是指在數(shù)據(jù)庫中多個地方重復存儲的數(shù)據(jù)，第二種，指的是基層沒有填寫，而由于應用程序設計的原因在數(shù)據(jù)庫表中填充并遺留下的大量空白。減少數(shù)據(jù)冗余并不應該隨著硬件系統(tǒng)處理能力、運算速度和存儲容量的提高而被忽視，相反，重視并減少冗余更能發(fā)揮硬件系統(tǒng)

5、的能力。通過對幾張定長二維表的統(tǒng)計，我們發(fā)現(xiàn)它們均存在第二種冗余，冗余的比例從60%至80%不等。以規(guī)模以上工業(yè)企業(yè)能源購進、消費及庫存表的二維子表(下面簡稱606表)為例，參加填報的單位約有27萬，共530萬條記錄，而其中至少一個有效字段(不包括uuid和數(shù)據(jù)項行代碼)有數(shù)的記錄僅95.4萬，冗余比率達到了82%。而恰恰是606表，其導出文件長度和導入耗費時間均列第一批上報的各表的首位。經(jīng)過測試，我們用數(shù)據(jù)庫的SQL命令刪除冗余記錄后，應用程序的執(zhí)行沒有發(fā)生錯誤，而無論是審核、匯總、導入、導出還是查詢時間都大幅度下降。原因有以下幾方面，物理存儲數(shù)據(jù)塊的減少使I/O訪問的次數(shù)減少，記錄數(shù)的減少

6、一方面使表掃描行數(shù)和疊加計算的次數(shù)減少，另一方面使索引文件的長度變小，維護開銷降低。也許開發(fā)人員會提出異議，606表在業(yè)務規(guī)則中是定長二維表，刪除冗余記錄后就變成了不定長表，這不是違背了業(yè)務的需求？這種擔心是有道理的，但不是不可解決的，我們完全可以在數(shù)據(jù)展示上給用戶呈現(xiàn)一張定長二維表，后臺存儲格式是用戶不關心的，但對應用程序的執(zhí)行性能卻是關鍵的。事實上，ePras程序已經(jīng)做到了將不定長表存儲格式數(shù)據(jù)展示成為定長二維表。只是按不定長表存儲定長二維表在數(shù)據(jù)導入時需要和不定長表一樣考慮空行覆蓋等問題。606表產(chǎn)生如此巨大的冗余，這是由企業(yè)生產(chǎn)經(jīng)營情況決定的，大部分企業(yè)都只購進、消費及庫存了22種能源

7、的少數(shù)幾種，這個比例就是1減去上面給出的82%，即18%。607，612，621表也都存在和606表相同的第二種冗余，可以用同一種辦法加以優(yōu)化。除了定長二維表，不定長表也存在數(shù)據(jù)冗余，不過主要是第一種，其影響也不如上述各表大。以規(guī)模以上工業(yè)企業(yè)產(chǎn)品生產(chǎn)、銷售、庫存表為例(下面簡稱603表)，603表的字段設計完全與表樣一致，除了保存產(chǎn)品代碼外，還保存了產(chǎn)品名稱和計量單位，實際上，產(chǎn)品代碼本身就涵蓋了產(chǎn)品名稱和計量單位信息，在工業(yè)企業(yè)產(chǎn)品目錄中一一對應。ePras程序也正是通過檢索錄入的產(chǎn)品代碼，從產(chǎn)品目錄中取得對應產(chǎn)品名稱和計量單位填充到相應的字段的。這種冗余在數(shù)據(jù)采集階段，對數(shù)據(jù)展示有一定程

8、度的幫助，不必每次顯示每個產(chǎn)品都查表了，能提高顯示速度。但對于國家數(shù)據(jù)處理，上文已經(jīng)提及，只對個別基層數(shù)據(jù)還會進行訂正、增補，顯示的響應時間不是那么重要，何況，產(chǎn)品代碼在603表和產(chǎn)品目錄中都是主鍵的一部分，數(shù)據(jù)庫管理系統(tǒng)會利用索引去取數(shù)，效率也不低。當然，匯總表中產(chǎn)品數(shù)量巨大，保存這些冗余信息，以空間換取時間還是值得的。如果我們在603表中不存儲產(chǎn)品名稱和計量單位，將可以節(jié)省1/5的存儲空間。我們用SQL命令更新冗余字段后，經(jīng)過測試，應用程序的執(zhí)行沒有發(fā)生錯誤，而無論是審核、匯總、導入、導出還是查詢時間都大幅度下降(對比見下表)。605，611，613表也都存在和603表相同的第一種冗余，可

9、以用同一種辦法加以優(yōu)化。二、改變主鍵采用uuid作為主鍵或主鍵的一部分的初衷是為了解決基層數(shù)據(jù)重碼覆蓋問題，但到了上報階段，各省上報的數(shù)據(jù)在省內(nèi)都已經(jīng)消除了重碼，采用處理地行政區(qū)劃碼+單位代碼的方式完全可以保證唯一性。這里的“+”號表示將多個字符串合并后填回uuid字段，而不是指用多個字段組合作為主鍵，因此uuid存在的前提變得不再成立，而隨著記錄數(shù)的擴大，由uuid的產(chǎn)生算法帶來的隨機性、長度冗余在查詢中的負面影響日益嚴重。主鍵的隨機性使得大批量導入的時候維護索引的開銷巨大，而32個字符長度包含的信息又很有限，它跟業(yè)務無關，僅僅是區(qū)分一個填報單位，成本和回報不匹配。主鍵的重新設置有多種選擇方

10、案，一種是上文提到的處理地行政區(qū)劃碼+單位代碼，這種編碼的優(yōu)點是對于分地區(qū)查詢、匯總很方便，缺點是分專業(yè)處理不方便，我們也可以采取行政區(qū)劃碼+單位所屬專業(yè)代碼+單位代碼的編碼，這種編碼對于分地區(qū)處理、分專業(yè)查詢、匯總都很方便，缺點是代碼長度更長，有不符合關系數(shù)據(jù)庫規(guī)范化要求的地方。我們應針對業(yè)務的不同需要，采用適當?shù)霓k法重新設置主鍵。經(jīng)過改造以后的主鍵將可以脫離main_table，J601，J602的束縛，在查詢的時候直接分地區(qū)或分專業(yè)處理專業(yè)基層表。更改主鍵的一種應用是抽取一個居委會或村委會的數(shù)據(jù)，過去我們只能先根據(jù)J601，J602的單位所在地行政區(qū)劃碼查出相應的uuid集合,再從各個基

11、層表查詢包含在uuid集合中的uuid單位的數(shù)據(jù)，SQL語句如下：（以603表為例）select b.* from J601 a,J603 b where a.Z01_03 = and a.uuid=b.uuid而現(xiàn)在只要對各個基層表的主鍵進行條件查詢，SQL語句如下：（以603表為例）select * from J603 b where a.uuid like %更改主鍵的另一種應用是給各地反饋數(shù)據(jù)。國家要給各省級反饋處理地屬于該地區(qū)的數(shù)據(jù)（也即是國家審定的該地區(qū)上報的數(shù)據(jù)），如果用應用程序來做，需要3個步驟：1.篩選出屬于該地區(qū)的填報目錄（main_table），設為表A；2.將各基層表和

12、表A通過uuid產(chǎn)生關聯(lián)；3.輸出關聯(lián)成功的單位。而通過Oracle的exp實用程序來做，只需要在命令行加一個參數(shù)query=where uuid like 23%即可。更改主鍵的第三種應用是給表分區(qū)創(chuàng)造了條件，實現(xiàn)表分區(qū)后，某一分區(qū)的數(shù)據(jù)可以獨立地添加、刪除、索引而不影響其它分區(qū)中的數(shù)據(jù)，方便了對特定分區(qū)數(shù)據(jù)的批量處理。批量改變?nèi)珖鴶?shù)據(jù)的uuid，我們可以通過下面辦法實現(xiàn)。1.構造uuid和新id的對照表idtab并設uuid為主鍵。2.更新每個基層表和填報目錄的uuid,注意填報目錄還要對產(chǎn)業(yè)活動單位的par_orgn_code進行替換，否則產(chǎn)業(yè)活動單位將和所屬法人單位失去關聯(lián)。改變uui

13、d的實質(zhì)是讓各表的主鍵具有更多對統(tǒng)計活動有用的信息，這種信息原本來自多個基層表，用空間換取時間，達到減少重復取信息、加快處理的目的。當然，批量改變?nèi)珖鴶?shù)據(jù)的uuid是一項耗時巨大的工作，但是正如諺語所說的“磨刀不誤砍柴工”，我們對主鍵一次性的處理以后，為將來的工作帶來極大的利益。由于我們保留了uuid和新id的對照表，如果有必要也可以很容易查到某單位的uuid。三、清除垃圾數(shù)據(jù)垃圾數(shù)據(jù)指的是那些由于各種原因應該從數(shù)據(jù)庫中刪除而未刪除的數(shù)據(jù)，包括下面幾種。1. main_table中有，而其他基層表中沒有的單位，2. main_table中沒有，而其他基層表中有的單位，3. 基層表子表中有，而其

14、對應基層表主表中沒有的單位。4. 按統(tǒng)計制度的要求,屬于某個專業(yè)沒有填應該填報的調(diào)查表,或者填報了不屬于本專業(yè)的表的單位。這些數(shù)據(jù)遺留在數(shù)據(jù)庫中，影響了匯總審核的正確性，有時候還會引起完整性約束方面的問題，產(chǎn)生插入、刪除異常。查找并刪除第一種垃圾數(shù)據(jù)的辦法是：將main_table和相應的基層表按照uuid做左外連接，附加應填報此表的專業(yè)代碼和行業(yè)代碼等條件?；鶎颖淼膗uid為null的main_table 中剩余的uuid即為所求。查找并刪除第二種垃圾數(shù)據(jù)的辦法是：將main_table和相應的基層表按照uuid做右外連接，附加應填報此表的專業(yè)代碼和行業(yè)代碼等條件。main_table 的u

15、uid為null的基層表中剩余的uuid即為所求。查找并刪除第三種垃圾數(shù)據(jù)的辦法是：將基層表子表和相應的基層表主表按照uuid做左外連接。基層表主表 uuid為null的基層表子表中剩余的uuid即為所求。垃圾數(shù)據(jù)被清除后，系統(tǒng)中數(shù)據(jù)關系更加合理，有利于保證各種計算、分析結果正確。四、相關人員的作用數(shù)據(jù)庫系統(tǒng)的優(yōu)化是一個長期動態(tài)的過程，需要符合不同階段的工作需要，系統(tǒng)設計應滿足而不拘泥于業(yè)務需求，充分認識性能和效率問題的重要性并在應用程序中實現(xiàn)。業(yè)務高級管理人員負責制定并重新考察業(yè)務規(guī)則和流程，從而為應用設計提供一種清晰而適當?shù)哪Ｐ汀Ｋ麄儽仨毚_定規(guī)則和流程的特定類型而這將影響到整個系統(tǒng)的性能。應用設計人員必須繞過潛在的系統(tǒng)瓶頸進行設計。此外，他們還應當與系統(tǒng)設計人員進行交流，從而使得每個人都可以理解應用的數(shù)據(jù)流。應用開發(fā)人員必須與其所選擇的實現(xiàn)策略進行充分的交互，使得其在進行語句優(yōu)化的時候可以順利地較快確定模塊和SQL 語句。數(shù)據(jù)庫管理員必須仔細地監(jiān)視系統(tǒng)的活動并將其歸檔，以此來識別和修正異常的系統(tǒng)性能。有時還需與系統(tǒng)管理員就系統(tǒng)配置進行交互從而方便其他人可以高效地設計和管理系統(tǒng)。最終用戶和二次開發(fā)人員也有責任用適當?shù)姆椒▉硎褂媒?jīng)濟普查全國數(shù)據(jù)庫，我們應遵循下面的原則：1 限定（盡量縮?。┎樵兊姆秶?，例如：在銷售額超過10億的商業(yè)企業(yè)中查詢?nèi)珖N售額