Java 百万数据秒级导出到Excel中

出自:

腾讯课堂 700多分钟干货实战Java多线程高并发高性能实战全集 , 我学习完了之后, 我给 老师在课上说的话做了个笔记,以及视频的内容,还有代码敲了一遍,然后添加了一些注释,把执行结果也整理了一下, 做了个笔记

1.案例背景

某个MySQL服务器的user表有100万条数据,请运用多线程并发编程等相关基础知识,将这100万条数据秒级(8秒内)导出到若干Excel文件.

实际需求如果产品经理说要弄到一个Excel文件的话,你可以跟产品经理要提建议的,因为你100万条数据往一个Excel里面保存的话那么你的文件会很大,而且看数据也不方便,也会很卡.

如果你把一百万条数据导出到多个Excel里面,一个Excel里面有两万条数据,这样看起来也稍微方便点.

2.百万数据串行从数据库导出到Excel中会产生什么问题?

数据量比较大,串行的话耗时比较长,百万级别数据放到一个List集合里面可能会出现内存溢出的问题.就是list数据结构里面还没装到100W条数据之后就内存溢出了.

所以就需要并行分批次处理数据, 比如说一次拉取2W条数据处理,处理完了快速的释放掉内存,这样防止内存溢出,

3.如何秒级将百万数据并发写到Excel的文件里面

如果是串行的去做的话,肯定是很慢的,就得批量并行去做,就需要多线程了,每个线程处理若干笔数据,比如说每个线程处理2万条数据.这样处理起来就快很多了.

4.如何从架构角度优化性能,如何解决整体性能瓶颈

答案是:功能分层

上面是三个流程,只有每一次性能都很好,整体性能才能好,如果有一层性能不好的话,那么整体性能也不会太好.

比如说即使数据库和Java应用的性能好,但是你配置的数据库连接池配置的不到位,那么整体的性能就不好.

如果你数据库性能好,数据库连接池配置的到位,但是你Java应用性能不好,那么整体的性能也不会太好.

所以数据库层 数据库连接池 Java应用,这几层的性能都要好.

MySQL的SQL语句要有索引,数据库连接池再调优一下,Java应用这里代码再用多线程处理一下,就很快了.

如果哪一层慢的话,就每一层代码上下打个日志,看日志的时间,来确定哪里比较慢.

5.MySQL数据库核心参数优化,Druid相关参数调优

MySQL 的 my.cfg参数调优:

innodb_thread_concurrency 参数调整

druid相关参数调优:

# 配置连接池的参数
initialSize=50
# 连接池的最大数据库连接数。设为0表示无限制。
maxActive=200
# 最大建立连接等待时间。如果超过此时间将接到异常。设为-1表示无限制。
maxWait=600000
# 连接池中的最小空闲连接数，Druid会定时扫描连接池的连接，如果空闲的连接数大于该值，则关闭多余的连接，反之则创建更多的连接以满足最小连接数要求。
minIdle=5

活跃数越高,性能就越高

6.代码使用方式

执行sql脚本

在D盘创建一个名字叫 excel 的文件夹.

执行ExcelExporter类即可

代码

sql

CREATE TABLE `test`.`user` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `createdTime` timestamp NULL,
  `updatedTime` timestamp NULL,
  PRIMARY KEY (`id`))
COMMENT = '用户测试表';

ALTER TABLE `test`.`user`
ADD INDEX `index` (`id` ASC);

ExcelExporter

package com.yrxy.thread.case2;

import com.yrxy.thread.common.PageHelper;

import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ExcelExporter {

   public static void main(String[] args) {
      createExcelFile();
   }

   public static void createExcelFile() {
      ExecutorService pool = Executors.newFixedThreadPool(100);

      //获取总条数
      int count = UserHandler.queryCount();

       // 获取总页数
      final int totalPageCount = PageHelper.getTotalPageCount(count);
      String tableName = "user";

      final long start = System.currentTimeMillis();
      for (int currentPageNum = 0; currentPageNum < totalPageCount; currentPageNum++) {

         String pageSql = PageHelper.getPageSql(tableName, currentPageNum);

         final List userList = UserHandler.queryUserList(pageSql);
         final int finalCurrentPageNum = currentPageNum;
         Runnable run = () -> {
            ExcelUtil.CreateExcel(finalCurrentPageNum, userList);
            if (finalCurrentPageNum == (totalPageCount - 1)) {
               System.out.println("  export data to excel, it  has spent " + (System.currentTimeMillis() - start) + "  ms");
            }
         };
         pool.execute(run);

      }
   }


}

ExcelUtil

package com.yrxy.thread.case2;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import java.io.FileOutputStream;
import java.util.List;

public class ExcelUtil {
   /**
    *
    * @param currentPageNum 当前页数 -1
    * @param userList  数据
    */
   public static void CreateExcel(int currentPageNum, List userList)  {
       // 第一步，创建一个webbook，对应一个Excel文件
       HSSFWorkbook wb = new HSSFWorkbook();
       // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
       HSSFSheet sheet = wb.createSheet("用户信息");
       // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
       HSSFRow row = sheet.createRow((int) 0);
       // 第四步，创建单元格，并设置值表头 设置表头居中
       HSSFCellStyle style = wb.createCellStyle();
       style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

       HSSFCell cell = row.createCell((short) 0);
       cell.setCellValue("id");
       cell.setCellStyle(style);
       cell = row.createCell((short) 1);
       cell.setCellValue("姓名");
       cell.setCellStyle(style);
       cell = row.createCell((short) 2);

       // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

       for (int i = 0; i < userList.size(); i++)
       {
           row = sheet.createRow((int) i + 1);
           User stu = (User) userList.get(i);
           // 第四步，创建单元格，并设置值
           row.createCell((short) 0).setCellValue(stu.getId());
           row.createCell((short) 1).setCellValue(stu.getName());

       }
       // 第六步，把文件存到指定位置
       try
       {
           //为了测试方便，写死了路径，建议大家可以手工建D://excel路径，不然会报错
           FileOutputStream fout = new FileOutputStream("D://excel/user-"+(currentPageNum+1)+".xls");
           wb.write(fout);
           fout.close();
       }
       catch (Exception e)
       {
           e.printStackTrace();
       }


   }


}

User

package com.yrxy.thread.case2;

import java.sql.Timestamp;

public class User {
    private int id;
    private String name;
    private Timestamp createdTime;
    private Timestamp updatedTime;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Timestamp getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(Timestamp createdTime) {
        this.createdTime = createdTime;
    }

    public Timestamp getUpdatedTime() {
        return updatedTime;
    }

    public void setUpdatedTime(Timestamp updatedTime) {
        this.updatedTime = updatedTime;
    }
}

UserHandler

package com.yrxy.thread.case2;

import com.yrxy.thread.common.DataSourceUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserHandler {

//     public final static long pageSize=10000;

  public static void main(String[] args) {
    System.out.append("count is  : " + queryCount());
  }

  /**
   * 封装查询操作
   *
   * @param pageSql
   * @return
   */
  public static List queryUserList(String pageSql) {
    List userList = new ArrayList();
    Connection conn = DataSourceUtils.getConnection();
    ResultSet rst = null;
    User user;

    try {
      PreparedStatement pst = conn.prepareStatement(pageSql);
      rst = pst.executeQuery();
      while (rst.next()) {
        user = new User();
        user.setId((Integer) rst.getObject("id"));
        user.setName((String) rst.getObject("name"));
        user.setCreatedTime((Timestamp) rst.getObject("createdTime"));
        user.setUpdatedTime((Timestamp) rst.getObject("updatedTime"));
        userList.add(user);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return userList;
  }

  /**获取总条数
   */
  public static int queryCount() {
    String countSql = "SELECT count(*) as count  from user";
    ResultSet rst = null;
    Long count = null;
    Connection conn = null;
    try {
      conn = DataSourceUtils.getConnection();
      PreparedStatement pst = conn.prepareStatement(countSql);
      rst = pst.executeQuery();
      while (rst.next()) {
        count = (Long) rst.getObject("count");
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        rst.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return count.intValue();
  }

}

PageHelper

package com.yrxy.thread.common;

/**
 * Filename: PageHelper.java
 * 
 * @function:分页对象，用作数据的批量处理
 */

public class PageHelper {

//    public static final int pageSize = 100000;
      public static final int pageSize = 10000;

    private int currentPageNum;
    private int lastPageNum;
    private int totalRecordCount;

    public static void main(String[] args) {
        String tableName="user";
        int index=1;
        long currentPageNum=0;
        String pageSql=PageHelper.getPageSql( tableName,  currentPageNum);
        System.out.println("  pageSql  is  : "+pageSql);

    }

    /**
     *获取总页数
     * @param totalRecordCount 总条数
     * @return 总页数
     */
    public static int getTotalPageCount(int totalRecordCount) {
        if (totalRecordCount == 0) {
            return 0;
        }

        int lastPageCount = totalRecordCount % pageSize;
        int totalPageCount;
        if (lastPageCount > 0) {
            // 如果 余数大于零的话
            totalPageCount = totalRecordCount / pageSize + 1;
        } else {
            //如果余数为零的话
            totalPageCount = totalRecordCount / pageSize;
        }
        return totalPageCount;
    }


    /**
     *拼接查询sql,根据id偏移量进行查询的
     * @param currentPageNum 当前页数-1
     * @return sql
     */
    public static String getPageSql(String tableName,long currentPageNum){

        return "select * from "+ tableName +
                " where id>=" +(1+(currentPageNum * pageSize)) +
                " and id<="+(currentPageNum+1) * pageSize;
    }


    /**
     * 
     * @param tableName
     * @param updateTimeLabel
     * @param lastMaxUpdateTime
     * @param interval
     * @param currentPageNum
     * @param lastPageNum
     * @return
     */
    public String getPageSql(String tableName, String updateTimeLabel, String lastMaxUpdateTime, String interval, int currentPageNum, int lastPageNum) {
        String pageSql;
        if (lastMaxUpdateTime.indexOf(Constants.sysdate) != -1) {
            pageSql = "select * from(select rownum AS rowno, a.* from " + tableName + " a where " + updateTimeLabel + " >=" + lastMaxUpdateTime + "-" + interval + " and rownum <=" + currentPageNum * pageSize + " order by "+updateTimeLabel+ " ) b where b.rowno >" + lastPageNum * pageSize;

        } else {
            pageSql = "select * from(select rownum AS rowno, a.* from " + tableName + " a where " + updateTimeLabel + ">=to_date('" + lastMaxUpdateTime + "','yyyy-MM-dd HH24:mi:ss')" + "-" + interval + " and rownum <=" + currentPageNum * pageSize + " order by "+updateTimeLabel+  ") b where b.rowno >" + lastPageNum * pageSize;

        }
        return pageSql;
    }

    /**
     * 
     * @param basicSql
     * @return
     */
    public String getTotalRecordsCountSql(String basicSql) {
        String totalRecordsCountSql = "select count(*) from " + "(" + basicSql + ")";
        return totalRecordsCountSql;
    }

    /**
     * 
     * @param tableName
     * @param updateTimeKey
     * @param updateTimeLable
     * @param lastMaxUpdateTime
     * @param interval
     * @return
     */
    public String buildDynamicSyncSql(String tableName, String updateTimeKey, String updateTimeLable, String lastMaxUpdateTime, String interval) {
        String sql;
        if (lastMaxUpdateTime.indexOf(Constants.sysdate) != -1) {
            sql = "SELECT  * FROM  " + tableName + "   where " + updateTimeLable + ">=" + lastMaxUpdateTime + "-" + interval;
        } else {
            sql = "SELECT  * FROM  " + tableName + "   where " + updateTimeLable + ">=to_date('" + lastMaxUpdateTime + "','yyyy-MM-dd HH24:mi:ss')" + "-" + interval;
        }

        return sql;
    }

    public int getCurrentPageNum() {
        return currentPageNum;
    }

    public void setCurrentPageNum(int currentPageNum) {
        this.currentPageNum = currentPageNum;
    }

    public int getLastPageNum() {
        return lastPageNum;
    }

    public void setLastPageNum(int lastPageNum) {
        this.lastPageNum = lastPageNum;
    }

    public int getTotalRecordCount() {
        return totalRecordCount;
    }

    public void setTotalRecordCount(int totalRecordCount) {
        this.totalRecordCount = totalRecordCount;
    }

    public static int getPagesize() {
        return pageSize;
    }



}

DataSourceUtils

package com.yrxy.thread.common;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DataSourceUtils {

    public static void main(String[] args){
      Connection conn=  DataSourceUtils.getConnection();
      System.out.println("conn is  :  "+conn);
    }

    //创建一个成员变量
    private static DataSource ds;

    /**
     * 加载的代码写在静态代码块中
     */
    static {
        try {
            Properties info = new Properties();
            //加载类路径下，即src目录下的druid.properties这个文件
            info.load(DataSourceUtils.class.getResourceAsStream("/druid.properties"));

            //读取属性文件创建连接池
            ds = DruidDataSourceFactory.createDataSource(info);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 得到数据源
     */
    public static DataSource getDataSource() {
        return ds;
    }

    /**
     * 得到连接对象
     */
    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 释放资源
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt!=null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static void close(Connection conn, Statement stmt) {
        close(conn, stmt, null);
    }


}

druid.properties

initialSize=50
maxActive=200
maxWait=600000
minIdle=5

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://zjj101:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false
username=root
password=root

代码Git地址

​​https://gitee.com/zjj19941/mutil-thread.git​​

看case2