数据加载的性能测试

在数据仓库（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过程可能更好些。