大資料量的處理

最近做的項目中涉及到大資料量的問題，具體問題是：監測數字電視的訊號，對傳輸的碼流進行指標監測，每秒監測到20000個流，每個流對應著20多個指標，每秒儲存一次將這20000流儲存起來，需要儲存24小時的資料。

這個問題研究了好幾天：

一、檔案寫入儲存：但是如果將一天的17億條記錄都寫入到一個檔案裡，沒試過，相信會很慢，而且查詢的時候會更慢。如果寫入到多個檔案，按照流ID可以將資料拆成20000個分類，同時對20000個檔案執行寫入操作也不現實。

二、資料庫儲存：檔案儲存體的方式pass掉了之後開始考慮資料庫儲存

1、首先我用的Oracle進行效能測試：

將表按照流ID進列表分區，分為20000個區，然後每個分區記憶體儲86400條資料（也就是該流從一天的第1秒到86400秒對應的指標資料），需要有索引，主鍵是全域索引，其餘的列我又建了4個分區索引。

第一步建立6個資料表空間,保證每個資料表空間都能拓展到32GB大小（Oracle的資料表空間最大能拓展到32GB）

第二步要建立這個分區表：

-- Create tablecreate table AAA(  ID             number(8),  StreamID       number(8),  StreamType     number(1),  FAvailability  number(5),  Bandwidth      number(4),  ValidBandwidth number(4),  MDI_DF         number(5),  MDI_MLR        number(5),  Delay_Time     number(5),  IPInterval     number(5),  IPJitter       number(5),  Time           date,  MLT15          number(5),  MLT24          number(5),  MLS            number(5),  SliceNum       number(5),  CachedTime     number(5),  StuckTime      number(5),  GetSliceErr    number(5),  RetransmitRate number(5),  RepeatRate     number(5),  SecondsFlag    number(5))partition by list(SecondsFlag)  (     partition p1 values(1) tablespace tbs_haicheng   );  

第三步再為t_stream表建立19999個分區：

DECLAREparName varchar2(100);sql_str varchar2(500);BEGIN  FOR  I  IN 2..20000 LOOP    parName:='p'||I;    sql_str:='ALTER TABLE aaa ADD partition'||' p'||I|| ' VALUES('||I||')';    execute immediate sql_str;    END LOOP;  END; 

第四步為t_stream建立4個分區索引：

-- Create/Recreate indexes create index LOCAL_INDEX_REPEATRATE on AAA (REPEATRATE);create index LOCAL_INDEX_SECONDSFLAG on AAA (SECONDSFLAG);create index LOCAL_INDEX_STREAM on AAA (STREAMID);create index LOCAL_INDEX_TIME on AAA (TIME);

第五步建立一個表結構與t_stream相似的表：

create table a(  ID             number(8),  StreamID       number(8),  StreamType     number(1),  FAvailability  number(5),  Bandwidth      number(4),  ValidBandwidth number(4),  MDI_DF         number(5),  MDI_MLR        number(5),  Delay_Time     number(5),  IPInterval     number(5),  IPJitter       number(5),  Time           date,  MLT15          number(5),  MLT24          number(5),  MLS            number(5),  SliceNum       number(5),  CachedTime     number(5),  StuckTime      number(5),  GetSliceErr    number(5),  RetransmitRate number(5),  RepeatRate     number(5),  SecondsFlag    number(5))

partition by list (SECONDSFLAG)(  partition P1 values (1)    tablespace IPVIEW1    pctfree 10    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    )

);

alter table AAA  add constraint ID primary key (ID)  using index   tablespace TBS_HAICHENG  pctfree 10  initrans 2  maxtrans 255  storage  (    initial 64K    minextents 1    maxextents unlimited  );

第六步向表A中插入86400條資料:

declarebegin  for i in 1..86400 loop  insert into a  (id, streamid, streamtype, favailability, bandwidth, validbandwidth, mdi_df, mdi_mlr, delay_time, ipinterval, ipjitter, time, mlt15, mlt24, mls, slicenum, cachedtime, stucktime, getsliceerr, retransmitrate, repeatrate)values  (seq_aaa.nextval, 111, 1, 1111, 1111, 1111, 1111, 1111, 1111, 1111, 1111, SYSDATE, 1111, 1111, 1111, 1111, 1111, 1111, 1111, 1111, 1111);   end loop;  end ;

第七步：向t_stream表中copy資料

declarebegin  FOR I IN 1..20000 LOOP  insert into aaa         select seq_aaa.nextval, streamid, streamtype, favailability, bandwidth, validbandwidth, mdi_df, mdi_mlr, delay_time, ipinterval, ipjitter, time, mlt15, mlt24, mls, slicenum, cachedtime, stucktime, getsliceerr, retransmitrate, repeatrate,I from a;    commit;    END LOOP;  end;

注意：實際上，這一部分我是將1-20000分成20份  ，開了20個線程同時執行，每個線程負責向1000個分區中copy資料（向每個分區錄入86400條），這時候明白我為什麼要建立表A了吧！

然後，就不管他了，玩遊戲看電影去了，兩天假結束，想起來去看了一眼插入到什麼程度了，發現磁碟有的線程還在執行，有的線程由於資料表空間寫滿到32Gb無法再拓展而終止了。

看了一下序列已經被調用到6億多，說明插入進去了6億多條是資料。

首先是資料佔用的空間問題，與估算的相差太多，我開始插入了上百萬的資料，通過查看這上百萬資料佔用的空間估算出17億資料佔用的空間在180G左右，，而我準備出將近200G的磁碟空間以為足夠了呢，結果差了這麼多，分析下原因，最主要的一點是索引佔用的空間：

我原來在預估的時候忘記了為表建立索引，以為沒什麼大影響，有10G空間足夠索引佔用了，可是事實大錯特錯了，通過下面的語句查看了下空間的佔用情況：

1、表佔用空間(0.008G   這是A表裡的86400條資料佔用的空間)select segment_name, sum(bytes)/1024/1024/1024 GB from user_segments where segment_type='TABLE'  group by segment_name;2、索引佔用空間（17.24GB）select segment_name ,sum(bytes)/1024/1024/1024 GB from user_segments where segment_type IN('INDEX PARTITION','INDEX') group by segment_name;3、分區表TABLE PARTITION佔用空間（63.5GB）select segment_name,sum(bytes)/1024/1024/1024 GB from user_segments where segment_type='TABLE PARTITION' group by segment_name;

結果分別如下：

註：第三個圖中的SEGMENT_NAME的值為T_STREAM   是上文建立的那個分區表。

我們看到結果發現，實際上表資料佔用的空間是64GB，跟原來估算的幾乎一致，多出來的部分是被索引佔了，總共佔用了將近100GB的空間，嚇死哥了

緣何索引佔用了這麼多的空間？可能是我建立索引的方式不對？後續研究補充！

我們的程式採用的策略是首先將17億條記錄手動錄入到資料庫中，然後當監測到流指標時候對響應的資料進行update操作，也就是一般每秒執行20000個update語句，測試下效能：

declarej  number ;begin  for i in 2000000..2020000 loopupdate t_stream   set        streamid = 2,       streamtype = 2,       favailability = 2,       bandwidth = 2,       validbandwidth = 2,       mdi_df = 2,       mdi_mlr = 2,       delay_time = 2,       ipinterval = 2,       ipjitter = 2,       time = sysdate,       mlt15 = 2,       mlt24 = 2,       mls = 2,       slicenum = 2,       cachedtime = 2,       stucktime = 2,       getsliceerr = 2,       retransmitrate = 2,       repeatrate = 2        where  id = i ;  end loop;  end ;

這種單純以主鍵進行修改的時候他要進行全表掃描（所有的分區需要掃描到），效率很低，大約70s執行完，這才只是6億資料。

所以我們要讓他在執行update語句的時候盡量掃描單個分區，也就是說把那個分區欄位當參數傳遞過來，如下語句所示：

declarej  number ;begin  j:=1;  for i in 2000000..2020000 loopupdate aaa   set        streamid = 2,       streamtype = 2,       favailability = 2,       bandwidth = 2,       validbandwidth = 2,       mdi_df = 2,       mdi_mlr = 2,       delay_time = 2,       ipinterval = 2,       ipjitter = 2,       time = sysdate,       mlt15 = 2,       mlt24 = 2,       mls = 2,       slicenum = 2,       cachedtime = 2,       stucktime = 2,       getsliceerr = 2,       retransmitrate = 2,       repeatrate = 2        where  id = i ;        j:=j+1;  end loop;  end ;

測試這個代碼塊執行時間為3s，而且雖然現在是6億資料，但是就是17億資料執行時間也差不多是3s的，因為它掃描的永遠只是20000個分區。而且我的電腦才四核處理器，伺服器上24核呢。執行的肯定會比我電腦快多了吧，所以實現預定需求不成問題。

2、後來由於Oracle是收費的，不讓用了，汗一個,接下來研究Mysql。

Mysql在建表以及分區的時候遇到兩個問題：

問題一：建分區的時候總提示法錯誤,無論怎麼改都不讓我建立分區，Mysql這麼火的資料庫不可能不支援分區啊。後來一查才知道Mysq5.0版本不支援分區，是從5.1才開始支援表的分區的，於是把我的資料庫版本更換成5.5的，分區成功建立。

問題二：在Mysql上建20000個分區的過程中發現每次執行到中途就報錯停止了，查詢瞭解到Mysql的表分區數量是有限制的，每個表最多能有1024個分區。

這對我們影響不太大，大不了我就建1000個分區，每個分區存放86400*20條資料，相信每個分區百萬條資料不算什麼。

3、首先sqlite資料庫不支援分區只好建立20000個表，由於sqlite不支援預存程序，我也沒找到sqlite怎樣寫迴圈語句。但是建立20000個表  和 錄入那麼多的資料我們不可能一條一條的去執行寫語句執行，所以需要另想辦法，我的解決過程：

首先我想到可以用調用批次檔的方式插入資料和建表：

建一個 批量建表.bat檔案，檔案內容如下：

@ECHO OFF For /L %%i in (1,1,20000) do (sqlite3.exe hc.db<createTable.bat bbb_%%i) pause 

createTable.bat  內容如下：

create table 1%(ID integer primary key autoincrement,  STREAMID       NUMBER(10),  STREAMTYPE     NUMBER(1),  FAVAILABILITY  NUMBER(5),  BANDWIDTH      NUMBER(4),  VALIDBANDWIDTH NUMBER(4),  MDIDF          NUMBER(5),  MDIMLR         NUMBER(5),  DELAY_TIME     NUMBER(5),  IPINTERVAL     NUMBER(5),  IPJITTER       NUMBER(5),  TIME           DATE,  MLT15          NUMBER(5),  MLT24          NUMBER(5),  MLS            NUMBER(5),  SLICENUM       NUMBER(5),  CACHEDTIME     NUMBER(5),  STUCKTIME      NUMBER(5),  GETSLICEERR    NUMBER(5),  RETRANSMITRATE NUMBER(5),  REPEATRATE     NUMBER(5),  SECONDSFLAG    NUMBER(5),  PART    NUMBER(5));

問題出現了，在執行批量建表.bat的時候提示sqlite語法錯誤。至今也沒找到原因：

問題肯定是出現在傳遞的動態參數上，createTable.bat成功的接到了參數，語句在sqlite中執行不報錯，放在bat裡就報錯。  所以第一次批量建表沒成功。

那就用咱們的老本行，寫JAVA程式：

需要一個驅動包：sqlitejdbc-v033-nested.jar。 

代碼如下：

import java.sql.*;import org.sqlite.JDBC;/** * sqlite建立資料庫以及批量建表 * @time 2014-01-07 * @author HaiCheng * */public class createTable {/** * @param args * @throws Exception  */public static void main(String[] args) throws Exception {try{//1，保證SQLite資料庫檔案的路徑首字元為小寫，否則報錯String thisPath = "e:/haicheng.db";String sql = "jdbc:sqlite://"+thisPath;//windows && linux都適用 //2，串連SQLite的JDBC Class.forName("org.sqlite.JDBC");  //建立一個資料庫名haicheng.db的串連，如果不存在就在目前的目錄下自動建立 Connection conn = DriverManager.getConnection(sql); //3,建立表 Statement stat = conn.createStatement(); for(int i=1 ;i<=20000;i++){ String sql1="  create table bbb"+i+"   " +   " (" +  " ID             INTEGER primary key autoincrement," +  "  STREAMID       NUMBER(10)," +  "  STREAMTYPE     NUMBER(1)," +  "  FAVAILABILITY  NUMBER(5)," +  "  BANDWIDTH      NUMBER(4)," +  "  VALIDBANDWIDTH NUMBER(4)," +  "  MDI_DF         NUMBER(5)," +  "  MDI_MLR        NUMBER(5)," +  "  DELAY_TIME     NUMBER(5)," +  "  IPINTERVAL     NUMBER(5)," +  "  IPJITTER       NUMBER(5)," +  "  TIME           DATE," +  "  MLT15          NUMBER(5)," +  "  MLT24          NUMBER(5)," +  "  MLS            NUMBER(5)," +  "  SLICENUM       NUMBER(5)," +  "  CACHEDTIME     NUMBER(5)," +  "  STUCKTIME      NUMBER(5)," +  "  GETSLICEERR    NUMBER(5)," +  "  RETRANSMITRATE NUMBER(5)," +  "  REPEATRATE     NUMBER(5)," +  "  SECONDSFLAG    NUMBER(5)," +  "  PART    NUMBER(5)" +  " );"; System.out.println(sql1); String sql2="CREATE INDEX index_flag"+i+" ON bbb"+i+"(SECONDSFLAG);"; String sql3="CREATE INDEX index_part"+i+" ON bbb"+i+"(PART);"; stat.executeUpdate( sql1 ); stat.executeUpdate( sql2 ); stat.executeUpdate( sql3 ); } stat.close(); conn.close(); //結束資料庫的串連  } catch( Exception e ) { e.printStackTrace ( ); }}}

import java.sql.*;import org.sqlite.JDBC;/** * 向第一個表中迴圈錄入資料 * @author HaiCheng * */public class insertData {public static void main(String[] args) throws Exception {try{//1，保證SQLite資料庫檔案的路徑首字元為小寫，並且路徑為unix路徑String thisPath = "e:/haicheng.db";String sql = "jdbc:sqlite://"+thisPath;//windows && linux都適用//2，串連SQLite的JDBCClass.forName("org.sqlite.JDBC"); //建立一個資料庫名haicheng.db的串連，如果不存在就在目前的目錄下自動建立Connection conn = DriverManager.getConnection(sql);//4,插入一條資料for(int i=1;i<=86400;i++){ PreparedStatement prep = conn.prepareStatement("insert into bbb1(STREAMID) values (?);");    prep.setInt(1, 0);    prep.addBatch();    conn.setAutoCommit(false);    prep.executeBatch(); } conn.setAutoCommit(true);

 stat.close(); conn.close(); //結束資料庫的串連  System.out.println("資料插入成功"); } catch( Exception e ) { System.out.println("資料插入異常"); e.printStackTrace ( ); }}}

import java.sql.*;import org.sqlite.JDBC;/** * 向其餘19999個表中批量拷貝資料 * @author HaiCheng * */public class copyData {public static void main(String[] args) throws Exception {try{ //1，保證SQLite資料庫檔案的路徑首字元為小寫，並且路徑為unix路徑 String thisPath = "e:/haicheng.db"; String sql = "jdbc:sqlite://"+thisPath;//windows && linux都適用 //2，串連SQLite的JDBC Class.forName("org.sqlite.JDBC");  //建立一個資料庫名haicheng.db的串連，如果不存在就在目前的目錄下自動建立 Connection conn = DriverManager.getConnection(sql); //3,建立表 Statement stat = conn.createStatement(); for(int i=2;i<=20000;i++){ String sql1="insert into bbb"+i+"  select * from  bbb1"; System.out.println(sql1); stat.execute(sql1); } stat.close(); conn.close(); //結束資料庫的串連  System.out.println("資料插入成功"); } catch( Exception e ) { System.out.println("資料插入異常"); e.printStackTrace ( ); }}}

依次執行這三個類，當執行第三個類的時候也就是批量向資料庫中錄入資料的時候，當資料檔案大小達到2G的臨界點的時候（不同方式測試多遍都是這種情況），再繼續寫入資料，那麼資料檔案就會損壞（檔案大小都變了，從2GB變成1MB了）。

分析各種原因：

（1）、正在寫入資料的時候斷電（排除，沒有斷電）

（2）、磁碟有壞道（排除，在磁碟中放些其他的檔案，換一段空間儲存這個資料同樣到2GB崩潰）

（3）、資料檔案所在磁碟空間不足（排除，硬碟空間足夠、sqlite也不像Oracle那樣有著資料表空間的概念）

最終我也沒找到什麼原因，發帖求助。

-------------------------------------------------------------------------------------------------------------------------

上面那些還是年前寫的東西，也沒有寫完。最終是sqlite的問題沒有解決。目前還是用著Mysql