資料加載的性能測試

在資料倉庫（DW）項目中ETL是必不可缺少的工具。所謂ETL指的是資料抽取（Extract）、轉換（Transform）、清洗（Cleansing）、裝載（Load）。大多數接觸ETL工具設計的人可能覺得轉換（Transform）是設計的重點。在處理轉換（Transform）環節的時候采用局部優化的政策，比如并發線程來提高轉換速度，其實我個人觀點并不贊同，在我接觸過移動倉庫項目中轉換（Transform）通常隻不過是一些substr與trim操作，現在硬體CPU的緩存基本都在2M以上,假設有100個這樣的操作在緩存裡也隻不過才300幾k,而用線程産生的時間片所占的比率則大了幾倍去了。

個人認為ETL重要的環節依次排列下來是：清洗（Cleansing）、裝載（Load）、抽取（Extract）、轉換（Transform）.

清洗（Cleansing）是保證資料品質的一個環節。資料品質問題具體表現為正确性、完整性、一緻性、完備性、有效性、時效性和可擷取性等幾個特性。呵呵這是理論叙述說的具體一點就是：

空值處理可捕獲字段空值，進行加載或替換為其他含義資料，并可根據字段空值實作分流加載到不同目标庫。

　　規範化資料格式 可實作字段格式限制定義，對于資料源中時間、數值、字元等資料，可自定義加載格式。

　　驗證資料正确性 可利用Lookup及拆分功能進行資料驗證。例如，主叫号861084613409，進行區域碼和電話号碼分解後，可利用Lookup傳回主叫網關或交換機記載的主叫地區，進行資料驗證。

　　資料替換對于因業務因素，可實作無效資料、缺失資料的替換。

　　Lookup 查獲丢失資料 Lookup實作子查詢，并傳回用其他手段擷取的缺失字段，保證字段完整性。

　　這裡先概括的說到這裡。

其實在ETL裡面最讓人頭疼的就是裝載（Load），因為這裡牽扯到一個關鍵的問題就是性能。怎樣讓資料Load的最快才是ETL工具設計成功與否的一個标準。于是我把目前我所知道的資料加載方式做了個測試實驗。下面是測試方式與結果：

測試環境：

OS : Windows XP SP2

DataBase: Mysql 4.1

Program Language:Java (JVM 1.6 b108)

首先我先在Mysql下的Test資料庫中建立了2個表如下：

mysql> show tables;

+----------------+

| Tables_in_test |

| testspeed1     |

| testspeed2     |

2個表的結構都一樣包含3個字段無索引、主鍵。

mysql> desc testspeed;

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

| Filed1 | varchar(20) | YES  |     | NULL    |       |

| Filed2 | varchar(20) | YES  |     | NULL    |       |

| Filed3 | varchar(20) | YES  |     | NULL    |       |

然後寫了一個java程式往testspeed1中插入200w條測試資料：

程式如下：

//通過JDBC批量加載資料

public static void main(String[] args){

......

 begin = System.currentTimeMillis();

 PreparedStatement stmt = conn.prepareStatement("insert into

    TestSpeed" +

    "(filed1,filed2,filed3) values (?,?,?)");

    for(int i=0;i<2000000;i++)

    {

    stmt.setString(1,i+"");

    stmt.setString(2,i+"");

    stmt.setString(3,i+"");

    stmt.executeUpdate();

   }

end = System.currentTimeMillis();

System.out.println("Cost Time: "+(end - begin) / 1000.0);

}

運作結果 ： 372.189 （秒） 約 6分12秒

然後利用mysql将資料直接到出成文本存到D:/Date.txt

mysql> select * into outfile "D:/Date.txt" fields terminated   ->  by ',' enclosed by

    ->  '"' lines terminated by '/r/n' from TestSpeed1;

Query OK, 2000000 rows affected (5.59 sec)

約 5.6秒

接着我用JDBC直接讀取資料來比較上面用mysql直接導出的性能差異

Statement stmt = conn.createStatement();

  rs = stmt.executeQuery("select * from TestSpeed1;");

    while (rs.next()){    

   }//這裡不做任何操作

運作結果：9.39　秒

再接着我用Mysql的Load Data來與JDBC批量加載資料做比較

這次我将上面導出的資料導回到TestSpeed2中

mysql> LOAD DATA LOCAL INFILE 'D:/Date.txt' INTO

-> TABLE   TestSpeed2 FIELDS termina

    ->ted by ',' enclosed by '"' lines terminated by '/r/n';

Query OK, 2000000 rows affected (7.20 sec)

Records: 2000000  Deleted: 0  Skipped: 0  Warnings: 0

約 7.3  秒

最後将結果總結以下：

操作   Java JDBC     mysql 

查詢     9.39秒            5.6秒 

插入    6分12秒         7.3秒 

可見JDBC來操作資料其實并不快，在資料裝載（Load）過程中用資料庫的Load過程可能更好些。