MyBatis实现MySql的批量插入，以及flushCache、useCache的配置

• 上次遇到一个插入效率的问题，同一个事务下，插入1000条数据，主键为递增序列，下面演示：

1、新增两张表

• 记录序列表

CREATE TABLE `sequence`  (
 `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `current_value` int(11) NOT NULL,
 `increment_value` int(11) NOT NULL DEFAULT 1,
 PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

• 测试插入记录表

CREATE TABLE `test_batch`  (
  `ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `GMT_CREATE` timestamp(0) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• 自定义MySql的主键增长，通过函数来写：

CREATE DEFINER=`goms`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
	 DECLARE VALUE INTEGER;
	  SET VALUE = 0;
	  SELECT current_value INTO VALUE
	  FROM sequence
	  WHERE NAME = seq_name;
	  RETURN VALUE;
    END

CREATE DEFINER=`goms`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
	   UPDATE  sequence
	   SET  current_value = CASE current_value WHEN 999999999 THEN 100000000 ELSE current_value + increment_value END
	   WHERE NAME = seq_name;
	   RETURN currval(seq_name);
END

2、搭建测试项目

• 在此不多说，这是我平时写demo的一个项目
  
  
  

3、基础代码

public interface GomsSequenceMapper {
	long getSeq4ID();
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.tools.dao.test.GomsSequenceMapper">

<!-- flushCache="true" useCache="false" -->
	<select id="getSeq4ID" resultType="java.lang.Long" >
		SELECT nextval('SEQ_ID');
	</select>
</mapper>

public interface TestBatchMapper {
  int insertSelective(TestBatch record);
  void insertBatch(List<TestBatch> list);
}

public class TestBatch {
    private String id;

    private String name;

    private Date gmtCreate;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public Date getGmtCreate() {
        return gmtCreate;
    }

    public void setGmtCreate(Date gmtCreate) {
        this.gmtCreate = gmtCreate;
    }
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.tools.dao.test.TestBatchMapper" >
  <resultMap id="BaseResultMap" type="org.tools.domain.order.dto.TestBatch" >
    <id column="ID" property="id" jdbcType="VARCHAR" />
    <result column="NAME" property="name" jdbcType="VARCHAR" />
    <result column="GMT_CREATE" property="gmtCreate" jdbcType="TIMESTAMP" />
  </resultMap>

 <insert id="insertSelective" parameterType="org.tools.domain.order.dto.TestBatch" >
    insert into test_batch
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        ID,
      </if>
      <if test="name != null" >
        NAME,
      </if>
      <if test="gmtCreate != null" >
        GMT_CREATE,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="gmtCreate != null" >
        #{gmtCreate,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>

<!-- 批量插入数据 -->
	<insert id="insertBatch" parameterType="java.util.List" >
		insert into test_batch (ID, NAME, GMT_CREATE)
		values 
	    <foreach collection="list" item="item" index="" separator=",">   
			(#{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, NOW())
	    </foreach>
	</insert>
 
</mapper>

4、进入主题

@Service
public class TestBatchInsert {

	@Autowired
	private GomsSequenceMapper gomsSequenceDAO;
	
	@Autowired
	private TestBatchMapper testBatchMapper;

	final class Interval{
		public long start;
		public long end;
		public long interval;
	}
	

	@Transactional
	public void test1() {
		try {
			Interval timeout = new Interval();
			timeout.start = System.currentTimeMillis();
			System.err.println(new Date(timeout.start));
			for (int i = 0 ; i < 100; i++) {
				long id = gomsSequenceDAO.getSeq4ID();
				
				TestBatch test = new TestBatch();
				test.setId(id+"");
				test.setName(id+"----");
				
				testBatchMapper.insertSelective(test);
			}
			timeout.end = System.currentTimeMillis();
			timeout.interval = timeout.end-timeout.start;
			System.err.println(String.valueOf(timeout.interval));
			System.err.println(new Date(timeout.end));
		} catch (Exception e) {
			e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
		}
	}

@Transactional
	public void test2() {
		try {
			Interval timeout = new Interval();
			timeout.start = System.currentTimeMillis();
			System.err.println(new Date(timeout.start));
			List<TestBatch> lists = new ArrayList<TestBatch>();
			
			for (int i = 0 ; i < 100; i++) {
				long id = gomsSequenceDAO.getSeq4ID();
				
				TestBatch test = new TestBatch();
				test.setId(id+"");
				test.setName(id+"----");
				
				lists.add(test);
			}
			testBatchMapper.insertBatch(lists);
			timeout.end = System.currentTimeMillis();
			timeout.interval = timeout.end-timeout.start;
			System.err.println(String.valueOf(timeout.interval));
			System.err.println(new Date(timeout.end));
		} catch (Exception e) {
			e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
		}
	}
}

- 执行junittest：

执行test1，大家注意这个是注释的：


Tue Oct 15 15:36:31 CST 2019
168
Tue Oct 15 15:36:31 CST 2019
- 执行test2：

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException就是违反数据库完整性约束

• 在MyBatis中有flushCache、useCache这两个配置属性，分为下面几种情况：
  （1）当为select语句时：
  flushCache默认为false，表示任何时候语句被调用，都不会去清空本地缓存和二级缓存。
  useCache默认为true，表示会将本条语句的结果进行二级缓存。
  （2）当为insert、update、delete语句时：
  flushCache默认为true，表示任何时候语句被调用，都会导致本地缓存和二级缓存被清空。
  useCache属性在该情况下没有。
• 我们来改下xml，不缓存，flushCache=“true”, useCache=“false”
  
  
  Tue Oct 15 15:35:29 CST 2019
  126
  Tue Oct 15 15:35:29 CST 2019
• 结果很明显，一条一条插入使用了168ms，而批量插入用了126ms。
• 关注公众号"双城人"，搬砖过程遇到的问题，大家一起探讨，资源共享