mybatis插入数据性能研究

最近在开发的时候,经常遇到插入和搜索的效率问题,由于自己本身没有接触过较大的数据量,于是来试一试

在尝试之前,先建个表,表名为person,里面只有三个字段"id",“name”,“age”,id为主键自增

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of person
-- ----------------------------

mybatis的插入

逐条插入

这个办法对于大批量的数据肯定是不推荐的,但是既然可以这么做,就试试
mapper.xml中为

  <insert id="insertSingle" parameterType="com.example.insertandselect.entity.PersonEntity" >
    insert into person ( name, age
      )
    values ( #{name,jdbcType=VARCHAR}, #{age,jdbcType=VARCHAR}
      )
  </insert>

我使用了一万条数据进行插入,效果不是很好

 */
    @Test
    public void singleInsert() {
        long startList = System.currentTimeMillis();
        ArrayList<PersonEntity> personList = new ArrayList<>();
        for(int i =0;i<10000;i++){
            PersonEntity personEntity = new PersonEntity();
            personEntity.setAge("1");
            personEntity.setName(UUID.randomUUID().toString());
            personService.insertSingle(personEntity);
        }
        long stopList = System.currentTimeMillis()-startList;
        System.out.println("所花时间为:"+stopList/1000+"秒");
    }

最后的结果为

本来准备拿10万条试试的,想想觉得没必要了,性能太差

foreach插入

一万条数据

该部分的mapper.xml如下

  <insert id="insertMany" parameterType="com.example.insertandselect.entity.PersonEntity">
    insert into person(name,age) values
    <foreach collection="list" item="person" separator=",">
      (#{person.name},#{person.age})
    </foreach>
  </insert>

插入代码如下

    @Test
    public void contextLoads() {
        long startList = System.currentTimeMillis();
        ArrayList<PersonEntity> personList = new ArrayList<>();
        for(int i =0;i<10000;i++){
            PersonEntity personEntity = new PersonEntity();
            personEntity.setAge("1");
            personEntity.setName(UUID.randomUUID().toString());
            personList.add(personEntity);
        }
        long stopList = System.currentTimeMillis();
        long addTime = stopList-startList;
        System.out.println(addTime);
        long startAdd = System.currentTimeMillis();
        personService.insertMany(personList);
        long stopAdd = System.currentTimeMillis()-startAdd;
        System.out.println("添加所花时间:"+stopAdd+"毫秒");
    }

这次我用的是毫秒作为单位,一万条数据插入时间为

十万条数据

接下来试试10万条,十万条在插入的时候,如果我们的mysql配置没有更改过,则会报一个错误
Packet for query is too large (5,900,035 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

这个错误的意思是你一次新增的数据太多了,需要修改mysql的配置或者将十万条分批传输,我采用修改mysql的配置,由于我不记得自己的mysql安装在哪了,所以首先进入数据库

mysql -h localhost -u root -p123456

然后敲入

show variables like "%char%"

便可得到位置

找到我们的my.ini文件,打开并找到错误提示中的max_allowed_packet,默认为4M,我们可以改大一点

然后重启mysql,重启可以win+r,输入services.msc打开服务面板,找到电脑上的mysql服务,右键停止,再右键启动

一切完成,我们来测试插入十万条,十万条数据我试着添加了两次
第一次

第二次

效率能接受

一百万条数据

效果也不算太好,但是勉强能用

batch插入

官方还有一个推荐的批量插入方法,就是开启batch插入
先在springboot的application.properties中修改一下mysql的连接
在spring.datasource.druid.url末尾加上rewriteBatchedStatements=true表示开启批量插入
该部分代码如下

public void batchInsert() {
        // 新获取一个模式为BATCH，自动提交为false的session
        // 如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交，可能导致内存溢出
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        PersonEntityMapper mapper = sqlSession.getMapper(PersonEntityMapper.class);
        //mapper.insertMany();
        long startList = System.currentTimeMillis();
        ArrayList<PersonEntity> personList = new ArrayList<>();
        try {
            for (int i = 0; i <= 1000000; i++) {
                PersonEntity personEntity = new PersonEntity();
                personEntity.setAge("1");
                personEntity.setName(UUID.randomUUID().toString());
                mapper.insertSingle(personEntity);
                //一千个提交一次
                if (i % 100000 == 0 || i == 100000  - 1) {
                    sqlSession.commit();
                    //清理缓存，防止溢出
                    sqlSession.clearCache();
                }
            }
            long stopList = System.currentTimeMillis()-startList;
            System.out.println("所花时间为:"+stopList/1000+"秒");
        }catch (Exception e) {
            //没有提交的数据可以回滚
            sqlSession.rollback();
            } finally{
            sqlSession.close();
            }
        }
    }

insertSingle这个方法对应的xml为

  <insert id="insertSingle" parameterType="com.example.insertandselect.entity.PersonEntity" >
    insert into person ( name, age
      )
    values ( #{name,jdbcType=VARCHAR}, #{age,jdbcType=VARCHAR}
      )
  </insert>

我们直接测试一百万条数据的插入,运行后结果为

多次运行,结果基本都在6-7秒之间,如果你不开启mysql的批量插入,这个时间就要很久很久了.
这个运行时间可以通过多少个提交来优化,比如我在一次提交一千个时候,一百万插入时间为10秒
我如果一次提交一万个或者十万个,那就会快上3-4秒，所以建议批量插入时还是使用batch模式

jdbc原生插入

突然想到了还有一个最原始的插入方法,就是不利用框架

public void insertByJdbc() throws SQLException {
            //做数据
            ArrayList<PersonEntity> personList = new ArrayList<>();
            for(int i =0;i<1000000;i++){
                PersonEntity personEntity = new PersonEntity();
                personEntity.setAge("1");
                personEntity.setName(UUID.randomUUID().toString());
                personList.add(personEntity);
            }
            Connection connection = jdbcTemplate.getDataSource().getConnection();
            connection.setAutoCommit(false);
            String sql = "insert into person(name,age) values(?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            long startList = System.currentTimeMillis();
            for(int i=0;i<personList.size();i++){
                ps.setString(1,personList.get(i).getName());
                ps.setString(2,personList.get(i).getAge());
                ps.addBatch();
                //每1000次提交一次
                if ((i != 0 && i % 100000 == 0) || i==personList.size()-1) {
                    ps.executeBatch();
                    connection.commit();
                    ps.clearBatch();
                }
            }
            long endList = System.currentTimeMillis()-startList;
            System.out.println("花费时间"+endList);
            ps.close();
            connection.close();
        }

试了一下性能和batch相差无几,他的插入时间也可以根据一次提交的数量来控制

如果不在乎这点性能的话,可以用框架,比较方便,但要注意batch和原生jdbc的事务问题,因为他是分批次提交,如果加到一半出问题了,是不会回滚的

mybais的搜索

搜索其实没啥好说的,正常来说都是在索引上下功夫,提升查询速度