21. Mybatis 的三种执行器

た入场券 2024-04-17 14:59 86阅读 0赞

在企业开发中, 对数据库的批量操作, 是一个非常常见的操作, Mybatis提供了批量执行器, 来支持批量操作.

## 1. Mybatis sql执行器 ##

Mybatis 支持全局修改执行器, 参数名为: defaultExecutorType. 但是笔者并不推荐这种方式,笔者建议在获取sqlSession对象时设置. Mybatis 共有三种执行器:

*  SIMPLE: 默认的执行器, 对每条sql进行预编译->设置参数->执行等操作
 *  BATCH: 批量执行器, 对相同sql进行一次预编译, 然后设置参数, 最后统一执行操作
 *  REUSE: REUSE 执行器会重用预处理语句（prepared statements

### 1.1 局部设置 ###

在获取sqlSession时设置, 需要注意的时, 如果选择的是批量执行器时, 需要手工提交事务.

// 获取指定执行器的sqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)
    
    // 获取批量执行器时, 需要手动提交事务
    sqlSession.commit();

### 1.2 全局配置 ###

可在全局配置文件中配置, 但是笔者不推荐这种方式, 了解即可.

## 2. 三种执行器测试 ##

*  对于单挑sql执行, 不同的执行器没有太大的差异, 所以笔者使用批量插入操作来测试不同执行器的不同行为方式.
 *  需要特别注意的时, 当选择批量执行器时, 纵使在获取sqlSession时, 设置了自动提交事务, 也需要手动提交事务

### 2.1 映射文件 ###

为保证测试条件尽可能一致, 笔者写里一个清空表的方法, 在每个测试用例执行之前, 先清空表.

<mapper namespace="org.zongf.learn.mybatis3.l01.mapper.StudentMapper">
    
        
        <delete id="clear">
            truncate table t_student
        </delete>
    
        
        <insert id="save" useGeneratedKeys="true" keyProperty="id">
            insert into t_student  values (null , #{name}, #{age}, #{sex}, #{birth})
        </insert>
    
    </mapper>

### 2.2 测试用例 ###

public class TestStudentMapper {
    
        // 批量保存方法
        private void batchSave(StudentMapper mapper) {
    
            // 初始化10000个对象
            List<StudentPO> list = new ArrayList<>();
            for (int i = 0; i < 10000; i++) {
                list.add(new StudentPO("zhangsan_" + i, "M",20 + i % 10,  LocalDate.now()));
            }
    
            // 批量执行
            long start = System.currentTimeMillis();
            for (StudentPO studentPO : list) {
                mapper.save(studentPO);
            }
            long end = System.currentTimeMillis();
    
            // 输出执行耗时
            System.out.println("耗时:" + (end - start) + " ms!");
        }
    
        // 每次执行前,请空表
        @Before
        public void setUp(){
            StudentMapper studentMapper = SqlSessionUtil.getMapper(StudentMapper.class);
            studentMapper.clear();
        }
    
        // 默认执行器
        @Test
        public void test_SIMPLE(){
            // 获取自动提交事务的Maper
            StudentMapper mapper = SqlSessionUtil.getMapperAutoTx(StudentMapper.class);
    
            // 执行批量保存
            batchSave(mapper);
        }
    
        // 重用预编译执行器
        @Test
        public void test_REUSE(){
            // 获取批量保存sqlSession
            SqlSession sqlSession = SqlSessionUtil.openSession(ExecutorType.REUSE, true);
    
            // 获取Mapper 对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
            // 执行批量保存
            batchSave(mapper);
    
        }
    
        // 批量执行器
        @Test
        public void test_BATCH(){
            // 获取批量保存sqlSession
            SqlSession sqlSession = SqlSessionUtil.openSession(ExecutorType.BATCH, true);
    
            // 获取Mapper 对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
            // 执行批量保存
            batchSave(mapper);
    
            sqlSession.commit();
        }
    }

## 3. 批量测试日志分析 ##

从测试结果可以看出, 在做批量操作时, 使用批量执行器, 性能会有很大的提升.

### 3.1 SIMPLE 方式 ###

从执行日志可以看出, 每次插入操作, 都会执行编译, 设置参数, 执行sql操作.

[2019-06-13 11:30:38:812][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
    [2019-06-13 11:30:38:819][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:30:38:824][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
    
    [2019-06-13 11:30:38:827][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
    [2019-06-13 11:30:38:828][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:30:38:832][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
    
    [2019-06-13 11:30:38:833][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
    [2019-06-13 11:30:38:839][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:30:38:841][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
    ...
    耗时:21575 ms!

### 3.2 REUSE 方式 ###

从执行日志可以看出, 只有第一次插入操作, 执行了sql编译步骤, 对其它插入操作执行了设置参数, 执行sql的操作.

[2019-06-13 11:31:11:752][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
    
    [2019-06-13 11:31:11:757][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:31:11:759][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
    
    [2019-06-13 11:31:11:761][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:31:11:764][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
    
    [2019-06-13 11:31:11:776][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:31:11:778][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
    ...
    耗时:19322 ms!

### 3.3 BATCH ###

从执行日志可以看出, 只对第一次插入操作执行了sql编译操作, 对其它插入操作仅执行了设置参数操作, 最后统一执行.

[2019-06-13 11:31:29:270][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
    [2019-06-13 11:31:29:276][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:31:29:277][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2019-06-13(Date)
    [2019-06-13 11:31:29:277][main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2019-06-13(Date)
    ...
    耗时:835 ms!