Mysql 入库性能优化

mysql 入库性能优化

最近在开发一个数据量比较大的模块，此模块涉及到要把数据存入中间表，因为数据量太大了，无法一次性的加载到内存中分析。关键是分析的结果还要进行分页，排序，因此保存在中间表方便后续操作。曾想把中间结果存到MongoDB，但是数据量太大了，内存耗不起，况且MongoDB 比较适合存放那些原原本本的数据，查询过程中要尽量避免计算，统计等。在我们现有的架构中，还有Solr和Mysql，但是由于Solr比较适合做全文检索，不适合当成数据库使用（一旦SolrQuery复杂一点，感觉Solr的查询速度相比建了索引的Mysql也是较慢的）,况且 MongoDB 和 Solr也不适合做一些复杂的统计。最终选择了Mysql作为存放中间数据的数据库。

我们当前的架构包含了Hibernate，如果使用Hibernate的批量插入那肯定不行啦，所以，首先想到的是最原始的jdbc的批量操作。

附 Hibernate批量操作：

Session session = getCurrentSession();
for(int i=0,size=datas.size(); i<size; i++){
    session.save(datas.get(i));
    if(i%50 == 0){
        session.flush();
        session.clear();
    }
}
session.flush();
session.clear();

原生的JDBC批量插入

getCurrentSession().doWork(new Work(){
    @Override
    public void execute(Connection conn) throws SQLException {
        conn.setAutoCommit(false);
        PreparedStatement pstmt = conn.prepareStatement(insetSQL);
        try {
            for(int i=0,size=datas.size(); i<size; i++){
                pstmt.setString(1,"yourString");
                pstmt.setString(2,"yourString");
                pstmt.setString(3,"yourString");

                pstmt.addBatch();
                if(i%50){
                    pstmt.executeBatch();
                    conn.commit();
                }
            }
            pstmt.executeBatch();
            conn.commit();
        } catch(Exception e){
            logger.error("",e);
        } finally{
            if(pstmt!=null){
                pstmt.close();
                pstmt = null;
            }
        }
    }

});

经过测试，原生的JDBC的批量插入方式也是很慢的，2000条每秒的速度。如果对于一千万的数据，那得等到猴年马月。

为了减少mysql的日志写，以及这样一条一条的插入。查找资料发现，如果将整个文件直接导入给mysql的话速度会快非常多。于是，就开始测试啦。

先测试将数据写到临时文件，这个写入到临时文件，直接用apache的commons-io就可以啦。直接将一个List当成一个集合，使用 FileUtils.writeLines() 方法即可。速度也是比较可观的，在自己电脑上（4G,酷睿i3）可以达到 11万/每秒。

接着测试将整个文件导入到数据库中，测试的结果是 5.X万/每秒。这样的话基本上入库的话每秒可以
3.x万/每秒

load data LOCAL infile '$fileName' into table $tableName
fields TERMINATED by ','
lines TERMINATED by '\r\n';

<<< 捐赠 >>>

转载请注明出处！原文地址： http://webinglin.github.io

Mysql 入库性能优化

留言