MySQL数据库批量插入（Spring Boot+Mybatis）

项目需求

从excel中读取几万条记录，存储到MySQL数据库中。因为内存中记录几万条，如果直接遍历所有记录，循环插入数据库，耗时太长，所以计划优化插入速度。

目前可供选择的解决方案如下：

1. mybatis的官方写法
2. 利用mysql特性，拼写insert sql
3. 利用spring的事务，直接执行插入操作

耗时从小到大：mysql特性，mybatis,等
参考：http://blog.csdn.net/baidu_18607183/article/details/51364085

因为本项目是SpringBoot框架+MyBatis技术，从技术便捷性和性能综合考虑，故决定采用第二种方案。

2 批量插入实现

实际上，大数据量插入主要耗时在session的频繁开启。因此一起开启，集中提交会大幅提升数据插入速度。

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;//引入bean

    public void batchInsert(List<Fenshu> fenshuList, Date current, String tableName, String courseName){
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//关闭session的自动提交
        excelMapper = session.getMapper(ExcelMapper.class);//利用反射生成mapper对象
        try {
            int i=0;
            for (Fenshu fs : fenshuList) {
                    excelMapper.saveFenshu(tableName, courseName, current, fs.getXuehao(), fs.getShijuanming(), fs.getDenglushijian(), fs.getJiaojuanshijian(),
                            fs.getShitileixing(), fs.getShitixuhao(), fs.getShititikuhao(), fs.getShitifenzhi(),
                            fs.getXueshengdefen(), fs.getDatiyongshi());
                    if (i % 1000 == 0 || i == fenshuList.size()-1) {
                        //手动每1000个一提交，提交后无法回滚
                        session.commit();
                        session.clearCache();//注意，如果没有这个动作，可能会导致内存崩溃。
                    }
                    i++;
                }
        }catch (Exception e) {
            //没有提交的数据可以回滚
            session.rollback();
        } finally{
            session.close();
        }
    }

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26

下面就几个底层问题说一下自己的理解：

1 代码中的session还是指mybatis的session，用途使用方式应该与MySQL里面的session一致的，但是session对象还是存储在web服务器内存而不是数据库内存。因为session本身就是数据库的客户端对象，所以可以理解为客户端对象存在了web服务器中。

2 本人理解的实现原理应该是这样的：web服务器开启session，此时会新建MySQL数据库的session，web服务器里面的session不断获取批量插入对象存储在web服务器内存，直到session.commit。当commit时，web服务器里面的数据库客户端对象会把批量数据，发送给数据库服务器，然后数据库执行批量插入。也就是说，web服务器内存的对象有可能会在断电后丢失，未能存入数据库服务器。

3 MySQL数据库配置

我们可以在数据库配置文件做一些修改进一步提升批量插入性能，配置文件修改如下：

bulk_insert_buffer_size=120M 
Max_allowed_packet=20M
[mysqldump]
Net_buffer_length=2k

• 1
• 2
• 3
• 4

bulk_insert_buffer_size
官方手册解释如下：

通俗解释就是说：如果我们需要向一个非空表中插入数据，增加这个缓存的大小会提升插入速度。
本项目中，我们修改这个参数，主要是为了改善批量插入大表的性能。

Max_allowed_packet
官方手册解释如下：

这个参数决定着客户端每次向数据库发送的包的大小。
本项目中，为防止我们每次提交批量数据，导致发包的大小超过数据库限制，所以我设置了一个适应我项目的参数大小。

show VARIABLES like '%max_allowed_packet%'
set global max_allowed_packet = 2*1024*1024*10

• 1
• 2

Net_buffer_length

该处参数主要调整服务器接收语句长度的大小，测试中对数据库性能影响不大。

(function () {$\text{(function () {}$('pre.prettyprint code').each(function () {
var lines = (this).text().split(′\n′).length;var$(this).text().split{(}^{\prime }{\text{n}}^{\prime }).length;var$numbering = $('

').addClass('pre-numbering').hide();
(this).addClass(′has−numbering′).parent().append($(this).addClass{(}^{\prime }has-numberin{g}^{\prime }).parent().append($numbering);
for (i = 1; i <= lines; i++) {
numbering.append($numbering.append($('
• ').text(i));
  };
  $numbering.fadeIn(1700);
  });
  });
  

  博文转载自：http://blog.csdn.net/songjianyue12345/article/details/78774011 多谢指教