mysql批量插入1000万条数据优化方式

方式一、mybatis实现

public  void  testBatchInsertUser()  throws  IOException {    
  InputStream  resourceAsStream  =             Resources.getResourceAsStream("sqlMapConfig.xml");    
  SqlSessionFactory  sqlSessionFactory  =  new  SqlSessionFactoryBuilder().build(resourceAsStream);    
  SqlSession  session  =  sqlSessionFactory.openSession();    
  System.out.println("===== 开始插入数据 =====");    
  long  startTime  =  System.currentTimeMillis();    
  int  waitTime  =  10;    
  try 
  {        
    List < User >  userList  =  new  ArrayList < > ();        
    for (int  i  =  1;  i  <=  1000000;  i++) 
    {            
      User  user  =  new  User();            
      user.setId(i);            
      user.setUsername("i "  +  i);            
      user.setAge((int) (Math.random()  *  100));            
      userList.add(user);            
      if (i  %  1000  ==  0) 
      {                
        session.insert("batchInsertUser",  userList);                
        // 每 1000 条数据提交一次事务                
        session.commit();                
        userList.clear();                
        // 等待一段时间                
        Thread.sleep(waitTime  *  10);            
      }        
    }        
    // 最后插入剩余的数据        
    if(!CollectionUtils.isEmpty(userList)) 
    {            
      session.insert("batchInsertUser",  userList);            
      session.commit();        
    }        
    long  spendTime  =  System.currentTimeMillis() - startTime;        
    System.out.println("成功插入 100 万条数据,耗时：" + spendTime + "毫秒");    
  } 
  catch (Exception  e) 
  {        
    e.printStackTrace();    
  } 
  finally 
  {        
    session.close();    
  }
}

这里循环插入注意两点一是调整合适的等待时间，防止出现内存占用过高问题，第二点调整配置文件设置合理的连接池和数据库参数；cpu和磁盘性能足够则直接批处理即可

方式二、JDBC插入

public  void  testJDBCBatchInsertUser()  throws  IOException 
{    
  Connection  connection  =  null;    
  PreparedStatement  preparedStatement  =  null;    
  String  databaseURL  =  "jdbc:mysql://localhost:3306/t_test";    
  String  user  =  "root";    
  String  password  =  "123456";    
  try 
  {        
    connection  =  DriverManager.getConnection(databaseURL,  user,  password);        
    // 关闭自动提交事务，改为手动提交        
    connection.setAutoCommit(false);        
    System.out.println("===== 开始插入数据 =====");        
    long  startTime  =  System.currentTimeMillis();        
    String  sqlInsert  =  "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";        
    preparedStatement  =  connection.prepareStatement(sqlInsert);        
    Random  random  =  new  Random();        
    for (int  i  =  1;  i  <=  300000;  i++) 
    {            
      preparedStatement.setString(1,  "i"  +  i);            
      preparedStatement.setInt(2,  random.nextInt(100));            
      // 添加到批处理中            
      preparedStatement.addBatch();            
      if (i  %  1000  ==  0) 
      {                
        // 每1000条数据提交一次                
        preparedStatement.executeBatch();                
        connection.commit();                
        System.out.println("成功插入第 " +  i + " 条数据");            
      }        
    }         // 处理剩余的数据        
    preparedStatement.executeBatch();        
    connection.commit();        
    long  spendTime  =  System.currentTimeMillis() - startTime;        
    System.out.println("成功插入 100 万条数据,耗时：" + spendTime + "毫秒");    
  } 
  catch (SQLException  e) 
  {        
    System.out.println("Error: "  +  e.getMessage());    
  } 
  finally 
  {        
    if (preparedStatement  !=  null) 
    {            
      try 
      {                
        preparedStatement.close();            
      } 
      catch (SQLException  e) 
      {                
        e.printStackTrace();            
      }        
    }        
    if (connection  !=  null) 
    {            
      try 
      {                
        connection.close();            
      } 
      catch (SQLException  e) 
      {                
        e.printStackTrace();            
      }        
    }    
  }
}

//具体步骤

• 获取数据库连接
• 创建statement对象
• 定义sql语句，使用preparedStatment对象
• 执行批处理操作
• 处理剩余数据
• 关闭Statement和Connection对象

 

总结

1. 批处理，调整批量提交的数量，降低网络传输和处理开销、减少与数据库交互次数。在Java中可以使用Statment或者preparedStatment的addBatch()语句，一次性执行批量sql；设置合理等待时间。

2. 索引，在大量数据插入前暂时去掉索引，最后再打上，这样可以大大减少写入时候的更新索引的时间。

3. 数据库连接池，使用数据库连接池可以减少数据库连接建立和关闭的开销，提高性能。在没有使用数据库连接池的情况，记得在finally中关闭相关连接