mybatis的批处理(效率)之rewriteBatchedStatements和allowMultiQueries

mybatis的批处理效率小结

内容说明：

1. mysql数据库为例
2. rewriteBatchedStatements=true：批量将数据传给mysql
3. allowMultiQueries=true：允许一次性执行多条sql
4. 更多配置项 ==> 资料入口

---

---

一：说明rewriteBatchedStatements

1、如何配置

在配置MySQL连接地址的时候加上rewriteBatchedStatements=true

#完整的url
jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false

#说明
# rewriteBatchedStatements=true : 将数据批量传输给mysql
# useSSL=false :  MySQL在高版本需要指明是否进行SSL连接。
# serverTimezone=Asia/Shanghai : 时区配置
# useUnicode=true&characterEncoding=utf8 ：编码

2、引入依赖

 <dependency>
     <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
  </dependency>

3、测试案例（使用原生jdbc测试）

1. 创建个UserTest.java
2. 数据库test01
3. 表user（3字段：id、username、password）
4. 主键不自增
5. 准备50万条数据，一次行插入5万条
6. 小编是插入主键，所有记得清空表，在测试情况2

package sqy.test;

import java.sql.*;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * @author suqinyi
 * @Date 2021/5/20
 * 测试mysql批处理速度
 *
 * 没有使用批处理  ==>  每次50000条 耗时: 33秒
 * 使用批处理     ==>  每次50000条  耗时: 2秒
 */
public class UserTest {

    public static void main(String[] args) {

        try {
            Connection conn = null;
            // MYSQL驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 情况1：没有使用批处理 ==> 每次50000条 耗时: 33秒
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false", "root", "root");

            // 情况2：使用批处理 ==> rewriteBatchedStatements=true  ==> 每次50000条  耗时: 2秒
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false", "root", "root");

            //批量插入50000
            int batchSize = 50000;
            // 总条数5000000
            int count = 500000;
            //设置自动提交为false
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("insert into user (id) values (?)");
            Long t1 = System.currentTimeMillis();
            System.out.println("========开始运行=========");
            for (long i = 1; i < count; i++) {
                //设置第一个参数的值为i
                ps.setLong(1, i);
                //将该条记录添加到批处理中
                ps.addBatch();
                if (i % batchSize == 0) {
                    //执行批处理
                    ps.executeBatch();
                    //提交
                    conn.commit();
                    System.out.println(i + ":添加" + batchSize + "条");
                }
            }
            if ((count + 1) % batchSize != 0) {
                ps.executeBatch();
                conn.commit();
            }
            ps.close();
            Long t2 = System.currentTimeMillis();
            System.out.println("总条数:"+count + "条  每次插入" + batchSize + "条   " +"  每次耗时:"+ (t2 - t1) / 1000 + "秒");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

4、运行结果：

4.1 情况1：没有使用rewriteBatchedStatements运行结果：

========开始运行=========
50000:添加50000条
100000:添加50000条
150000:添加50000条
200000:添加50000条
250000:添加50000条
300000:添加50000条
350000:添加50000条
400000:添加50000条
450000:添加50000条
总条数:500000条  每次插入50000条     每次耗时:33秒

4.2 情况2：使用rewriteBatchedStatements运行结果：

========开始运行=========
50000:添加50000条
100000:添加50000条
150000:添加50000条
200000:添加50000条
250000:添加50000条
300000:添加50000条
350000:添加50000条
400000:添加50000条
450000:添加50000条
总条数:500000条  每次插入50000条     每次耗时:2秒

得出：效率提升15倍左右

4、注意事项和小结：

原因如下：
MySQL Jdbc驱动在默认情况下会无视executeBatch()语句
把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，直接造成较低的性能

1. rewriteBatchedStatements=true:数据库会更高性能的执行批量处理（并保证5.1.13以上版本的驱动，才能实现高性能的批量插入）
2. 即使rewriteBatchedStatements=true, batchDelete()和batchUpdate()也不一定会走批量,但是有文章说：INSERT/UPDATE/DELETE都有效，更新和删除大家可以自行测下，改下sql就可以了
3. 当batchSize <= 3时，驱动会宁愿一条一条地执行SQL

二、说明allowMultiQueries

1、如何配置

#在url后面继续拼接上

rewriteBatchedStatements=true

#例如：
jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false

#注：上面参数解释过了，这里就不过多说明

2、如何使用

在mybaitis的配置文件中用" ；"隔开sql语句

例如：select … ; select ; insert … ;

<!--
      这边由有个driud的大坑，需要自己写个congig的配置，sql监控和多sql不能在配置文件中共存(yml里面)
      这个第二条sql最好是用Insert into select这种方式来写
      本质上也就是：其实就是批量更新 批量添加的语法而已
      bug：这边第二条sql会插入俩条，或者改下sql就好了。这边就是不测试了
-->

<select id="insertData"  parameterType="io.renren.modules.foot.entity.NewsEntity">
        INSERT INTO tb_news ( title, content, video_url, create_date, creator )
        VALUES  ( #{title},#{content},#{videoUrl},#{createDate},#{creator});

        INSERT INTO tb_message ( title, status, type, create_time, create_date, is_delete , asso_id)
        VALUES (
            #{title},1,2,#{createDate},NOW(),0,
            (SELECT id FROM tb_news WHERE title= #{title} AND create_date=#{createDate})
            );
</select>

<!--    
	说明：
	01.外面的标签是看需要，随便用也行
	02.不一定都是同一类型的操作，可以是先更新在查询，也可以先插入在查询在删除，等...
	03.但是这个只能返回一个结果集！！！
-->

3、注意事项非常重要

1. 好处：减少数据库连接次数，执行速度会更快
2. 执行多条的sql语句，但是只能返回一个结果集
3. 小编得出的结论（不同意见可以留言）：执行多条sql不能动态拼接，也就是where、if那一系列的标签
4. 如果是driud连接池报错：multi-statement not allow ==>参考这篇博文。亲测有效

---

完结