淺談海量數(shù)據(jù)處理技巧

張占杰

天津天士力國際營銷控股有限公司，天津 300402

1 數(shù)據(jù)源的處理

1.1 文本數(shù)據(jù)導入到SQL Server數(shù)據(jù)庫中

主要有以下3種方式：

方式一：通過程序從文本格式的數(shù)據(jù)中每整理出一條記錄就執(zhí)行insert語句插入到SQL數(shù)據(jù)庫中；

方式二：通過程序從文本格式的數(shù)據(jù)中整理出多條記錄，再批量執(zhí)行insert語句插入到SQL數(shù)據(jù)庫中；

方式三：編寫存儲過程，把這些從文本格式的數(shù)據(jù)通過存儲過程導入到SQL數(shù)據(jù)庫中。因為，存儲過程提供了許多標準SQL語言中所沒有的高級特性。其傳遞參數(shù)和執(zhí)行邏輯表達式的功能，有助于應用程序設計者處理復雜任務。另外，這些過程存儲在本地SQL服務器上，減少了執(zhí)行該過程所需的網(wǎng)絡傳輸帶寬和執(zhí)行時間。

1.2 數(shù)據(jù)庫的設計

1.2.1 數(shù)據(jù)的分表存儲

在諸多應用系統(tǒng)的數(shù)據(jù)庫中，例如，淘寶中店家的銷售記錄、買家的購買記錄、北美貿(mào)易數(shù)據(jù)、醫(yī)院醫(yī)生的接診數(shù)據(jù)等等，這些數(shù)據(jù)都有時間這一維度。以北美貿(mào)易數(shù)據(jù)為例，每月的北美海關貿(mào)易數(shù)據(jù)至少上百萬條，一年的數(shù)據(jù)量至少在千萬條以上。試想，假如系統(tǒng)要搜尋某個月份，某一HS號的產(chǎn)品出口信息，實際最后所得的數(shù)據(jù)量可能僅僅是幾百條甚至更少只有幾條。那么，如果從一百萬條數(shù)據(jù)里搜尋這些，和在上千萬條數(shù)據(jù)里搜尋，他們的返回結(jié)果時間必然是不同的。同樣，修改記錄時，數(shù)據(jù)庫也是要先找到滿足條件的記錄，然后再進行update操作。所以，建議處理這類數(shù)據(jù)時候，應該按照時間這一維度，把數(shù)據(jù)分表存儲。這樣，可以減少大量的查詢時間，數(shù)據(jù)量越龐大，效果越明顯。

1.2.2 對海量數(shù)據(jù)進行分區(qū)操作

對海量數(shù)據(jù)進行分區(qū)操作十分必要，例如針對按年份存取的數(shù)據(jù)，我們可以按年進行分區(qū)。例如SQL Server的數(shù)據(jù)庫分區(qū)是將不同的數(shù)據(jù)存于不同的文件組下，而不同的文件組存于不同的磁盤分區(qū)下，這樣將數(shù)據(jù)分散開，減小磁盤I/O，減小了系統(tǒng)負荷，而且還可以將日志、索引存放于不同的分區(qū)下。

1.2.3 創(chuàng)建索引

1）索引的優(yōu)點和局限索引可以提高查詢的效率，但會降低dml操作的效率。所以建立索引時需要權(quán)衡。對于dml操作比較頻繁的表，索引的個數(shù)不宜太多；

2）什么樣的列需要建索引經(jīng)常用于查詢、排序和分組的列（即經(jīng)常在where、order或group by子句中出現(xiàn)的列）；

3）主鍵索引和復合索引對于一張表的主鍵，系統(tǒng)會自動為其建立索引。如果一張表的幾列經(jīng)常同時作為查詢條件，可為其建立復合索引；

4） 建 立 索 引 的 語 句 create index i_staff on staff (empno)；create index i_agent on agent (empno, start_date)；

5）刪除索引的語句 drop index I_staff；drop index I_agent；

6）索引的一些特點：

（1）不同值較多的列上可建立檢索，不同值少的列上則不要建。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。

（2）如果在索引列上加表達式，則索引不能正常使用

例如：b1是表b的索引列

select * from b where b1/30< 1000 ；這是不正確的寫法where子句中如果使用in、or、like、!=，均會導致索引不能正常使用

例如：select * from b where b1=30 or b1=40；

1.2.4 創(chuàng)建索引表

除對數(shù)據(jù)表的相應列上創(chuàng)建索引、復合索引，我們還可以給數(shù)據(jù)表創(chuàng)建 “索引表”。

例如，當需要進行模糊查詢的時候，我們一般采取的解決辦法是 執(zhí)行sql語句 like

select * from table where 某列 like ‘%×××××%’，這樣如前所述，即便該列已經(jīng)加了索引，在進行l(wèi)ike查詢時候，索引也起不到任何作用。那么，這種情況，應該怎么處理呢？

我們以搜尋北美數(shù)據(jù)為例，假設一個表中存有一百萬條數(shù)據(jù)，那么我們可以設計這個表有一個pk_id列（長整型）來唯一標識一條記錄。表中存在一列是描述信息列。該列的內(nèi)容都是 英文字母。這樣，我們通過程序，先將該月數(shù)據(jù)進行處理，創(chuàng)建26套索引表，每個索引表有兩個列，一列（sKey）存放關鍵字，一列（sID）存放這些關鍵字在數(shù)據(jù)主表中出現(xiàn)的那些記錄的pk_id（以某一特定分隔符來分割表示。 例如 第 1、3、5 這3條記錄中存在關鍵字“TOY” 那么在 T索引表中有這樣一條記錄，TOY1，3，5）。這樣，如果程序要搜索關鍵字是 “TOY” 的信息記錄。執(zhí)行過程是這樣的：首先從T索引表中，用 “Select top 1 sID from T索引表 where sKey =’TOY’ ” ,然后得到主數(shù)據(jù)表中的 pk_id 為 1、3 、5 這三條記錄 是含有關鍵字“TOY”的記錄。這時，再執(zhí)行 “select * from mainData where pk_id in（1，3，5）”,得到所需數(shù)據(jù)。經(jīng)過實際測試 ，用上述方法，比直接采用 “select * from mainData where 描述信息列 like ‘%TOY%’”方式，系統(tǒng)返回結(jié)果的時間要快十倍以上，特別是在單表數(shù)據(jù)量超過百萬后，效果更佳突出。

2 程序設計的一些技巧

2.1 界面顯示進度

當程序處理大量數(shù)據(jù)的時候，往往系陷入“假死”狀態(tài)。這時，很多用戶會覺得系統(tǒng)很慢、已經(jīng)導致死機等。遇到類似情況，在程序在設計時候，可以在程序界面上，顯示處理的進度。一般采用進度條或處理比例（已處理數(shù)量/總數(shù)量） 這樣的方式展現(xiàn)，并實時刷新數(shù)據(jù)，這樣，用戶從程序界面上，看見有不斷的數(shù)字變化，從心里上有種感覺，程序在飛速處理著數(shù)據(jù)。

對實時刷新數(shù)據(jù)的一個竅門：很多時候，程序設計了實時刷新界面的信息，但由于后臺正在處理大量的數(shù)據(jù)，導致了cpu時間沒有分配給刷新程序界面。這時候程序一樣是假死的狀態(tài)。此時，可以在刷新界面數(shù)據(jù)的語句后面加上轉(zhuǎn)讓控制權(quán)的函數(shù)，讓cpu來處理。例如 doevents 函數(shù)。

2.2 僅顯示出部分信息

此種情況，一般用于搜索查詢顯示。當用戶搜索某一內(nèi)容后，得出的結(jié)果可能會有成千上萬條。如果程序要把這些結(jié)果，都搜索出來并顯示到界面上速度一定很慢。

以大多數(shù)用戶搜索的習慣，一般只會看前十條記錄，之后就會細化搜索的條件或搜索其他內(nèi)容，不會將所有的搜索結(jié)果全部瀏覽。這樣，程序只要搜索出前面的部分記錄就可以，

利用 select top 10 from table where ***** 就可以實現(xiàn)此功能。同時，需要記錄此十條記錄的 pk_id ，如果用戶繼續(xù)查看后續(xù)的內(nèi)容，那么再進行搜索的時候 要排除掉 先前已經(jīng)顯示的記錄。

2.3 數(shù)據(jù)的預處理

對一些固定條件下匯總的數(shù)據(jù)信息，可實現(xiàn)將數(shù)據(jù)匯總，存儲到數(shù)據(jù)庫中。這樣，在程序匯總該數(shù)據(jù)時，只需從數(shù)據(jù)庫中讀取出該條記錄即可，不需要再進行。

2.4 C/S(B/S)架構(gòu)的程序設計：充分利用服務器端處理

對大量的數(shù)據(jù)運算操作，盡量放到服務器端，充分利用服務器高效的處理能力、高速的讀寫能力，來處理客戶端提出的需求。客戶端只用來顯示服務器端處理的結(jié)果和提交處理請求。這樣可以大幅提高程序的處理速度。

[1][美]John Papa，Matthew Shepker，等著.機械工業(yè)出版社，2000，1.

[2]百度文庫 SQL效率之索引.